Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  How to create a Stored Procedure to count the rows of each table in a database?

    Posted Wed April 05, 2023 07:00 PM

    Dear All:

    I need to create a Stored Procedure that will count the rows of each table in my database, but I can't do it.

    I tried with:

    DROP PROCEDURE "informix".RowCount

    CREATE PROCEDURE "informix".RowCount( ) RETURNING Varchar(50,0), Integer;
    DEFINE vTable VARCHAR(50);
    DEFINE vRows INTEGER;

    FOREACH
    SELECT TabName, Tabid
           INTO vTable, vTabid
           FROM Systables
           WHERE Tabid > 99
           AND TabType = 'T'

          LET vRows = ( SELECT COUNT(*) FROM vTable );

    RETURN vTable, vRows WITH RESUME;

    END FOREACH;

    END PROCEDURE;

    EXECUTE PROCEDURE "informix".RowCount()

    But it throws me an error saying that vTable is not a database table.

    What puzzles me is that if I do the following:

    select 'select count(*) from ' || trim( tabname ) || ';' from systables where tabid > 99 and tabtype = 'T' order by 1

    It shows me the select count(*) with the name of each table.
    It is not feasible for me to use this, since there are more than nine hundred tables.

    That's why I need to do it via a Stored Procedure;

    I would appreciate it if you could give me a hand.

    Oh, by the way, the Informix is a 7.31 TD6

    Greetings!

    Gustavo



    ------------------------------
    Gustavo Echenique
    ------------------------------


  • 2.  RE: How to create a Stored Procedure to count the rows of each table in a database?

    Posted Wed April 05, 2023 07:20 PM

    The problem is that you can't use a variable name as a table name in this fashion within a procedure.  You could prepare a SQL statement  with the table name and run that but I think that is not possible in 7.31.  However, this does seem an odd thing to want to do.

    Have you tried querying the systabinfo table in sysmaster to get ti_nrows?

    select t.tabname, i.ti_nrows nrows
    from systables t, sysmaster:systabinfo i
    where t.partnum = i.ti_partnum
      and t.tabtype = "T"
      and t.tabid > 99

    This is just a quick SQL and won't work for fragmented tables in this simple form, and depends on the logging mode of your database, but this seems a bit simpler than a stored procedure.



    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------

    Original Message


  • 3.  RE: How to create a Stored Procedure to count the rows of each table in a database?

    Posted Thu April 06, 2023 02:19 AM

    Hi Gustavo,

    similar to Mike, but with a select to sysmaster only. You can use procedure variable/paramter for the filter  for example: databasename and/or tablename and/or nrows if you like to make it more flexible with a procedure.

    > echo "select dbsname,tabname,ti_nrows from systabnames,systabinfo  where ti_partnum=partnum order by 1,2"|dbaccess sysmaster

    Regards
    Henri



    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021 2022 2023
    IIUG Board Member
    ------------------------------

    Original Message


  • 4.  RE: How to create a Stored Procedure to count the rows of each table in a database?

    Posted Thu April 06, 2023 08:21 AM

    Hi Gustavo,
    you can also just wrap the query you've mentioned into an UNLOAD statement and generate the queries for all your tables:

    unload to 'getrows.sql' delimiter ';'
    select 'select "' || trim( tabname ) ||'", count(*) from ' || trim( tabname )
    from systables 
    where tabid > 99 and tabtype = 'T'
    order by 1;

    cat getrows.sql

    select "tab1", count(*) from tab1;
    select "tab2", count(*) from tab2;
    select "tab3", count(*) from tab3;
    select "tab4", count(*) from tab4;
    ...



    ------------------------------
    -tz-
    https://www.linkedin.com/in/tomzahr/
    ------------------------------

    Original Message


  • 5.  RE: How to create a Stored Procedure to count the rows of each table in a database?

    Posted Sun April 09, 2023 12:15 AM

    Here is a better procedure. Install it in the sysmaster database. I am making it generic so others can use it for multiple databases.

    CREATE PROCEDURE table_rowcounts( dbs char(128)) returning char(128) AS tab, bigint AS cnt
    DEFINE tbl charĺ128);
    DEFINE cntr bigint;

    FOREACH
      SELECT tabname, sum(nrows)
      INTO tbl, cntr
      FROM systabnames AS st,
                  sysptnhdr AS sp
      WHERE st.partnum = sp.partnum
          AND sp.npdata = 0
          AND st.dbname = dbs
       GROUP BY 1
       ORDER BY 1;

       RETURN tbl, cntr WITH RETURN;
    END FOREACH;
    END PROCEDURE;



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


Related Content