how can I print results from sql procedure?

Question

I'm writing a procedure to count rows in every table in my database. It so far looks like this:

create or replace procedure count_database_rows()
dynamic result sets 1
P1: begin atomic
DECLARE stmt CHAR(40);--
  FOR v1 AS
      c1 CURSOR FOR
   SELECT TABLE_SCHEMA, TABLE_NAME FROM sysibm.tables
    DO
       SET stmt = 'SELECT COUNT(*) FROM '||TABLE_SCHEMA||'.'||TABLE_NAME;--
       PREPARE s FROM stmt;--
       EXECUTE s;--
  END FOR;--
end P1
~

however, when I run it:

db2 -ntd~ -f script.sql > dump.csv

all I'm getting is:

DB20000I  The SQL command completed successfully.

how can I print all results instead?

Answer 1

Just for demonstration. I assume, that it's some educational task, and it's Db2 for LUW.

For non-DPF Db2 for LUW systems only

--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION COUNT_DATABASE_ROWS()
RETURNS TABLE (P_TABSCHEMA VARCHAR(128), P_TABNAME VARCHAR(128), P_ROWS BIGINT)
BEGIN
  DECLARE L_STMT VARCHAR(256);
  DECLARE L_ROWS BIGINT;

  FOR V1 AS 
    SELECT TABSCHEMA, TABNAME 
    FROM SYSCAT.TABLES 
    WHERE TYPE IN ('T', 'S')
    FETCH FIRST 10 ROWS ONLY
  DO
    SET L_STMT = 'SET ? = (SELECT COUNT(*) FROM "'||V1.TABSCHEMA||'"."'||V1.TABNAME||'")';
    PREPARE S FROM L_STMT;
    EXECUTE S INTO L_ROWS;
    PIPE(V1.TABSCHEMA, V1.TABNAME, L_ROWS);
  END FOR;
  RETURN;
END@

SELECT * FROM TABLE(COUNT_DATABASE_ROWS())@

For any Db2 for LUW systems

A little bit tricky for DPF systems, but doable as well. We have to wrap the code which is not allowed in the inlined compound statement into the stored procedure.

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE COUNT_DATABASE_ROWS_DPF(OUT P_DOC XML)
READS SQL DATA
BEGIN 
  DECLARE L_STMT VARCHAR(256);
  DECLARE L_ROWS BIGINT;
  DECLARE L_NODE XML;

  SET P_DOC = XMLELEMENT(NAME "DOC");

  FOR V1 AS 
    SELECT TABSCHEMA, TABNAME 
    FROM SYSCAT.TABLES 
    WHERE TYPE IN ('T', 'S')
    FETCH FIRST 10 ROWS ONLY
  DO
    SET L_STMT = 'SET ? = (SELECT COUNT(*) FROM "'||V1.TABSCHEMA||'"."'||V1.TABNAME||'")';
    PREPARE S FROM L_STMT;
    EXECUTE S INTO L_ROWS;
    SET L_NODE = XMLELEMENT
    (
      NAME "NODE"
    , XMLELEMENT(NAME "TABSCHEMA", V1.TABSCHEMA)
    , XMLELEMENT(NAME "TABNAME", V1.TABNAME)
    , XMLELEMENT(NAME "ROWS", L_ROWS)
    );
    SET P_DOC = XMLQUERY
    (
      'transform copy $mydoc := $doc modify do insert $node as last into $mydoc return $mydoc'
      passing P_DOC as "doc", L_NODE as "node"
    );
  END FOR;
END@

CREATE OR REPLACE FUNCTION COUNT_DATABASE_ROWS_DPF()
RETURNS TABLE (P_TABSCHEMA VARCHAR(128), P_TABNAME VARCHAR(128), P_ROWS BIGINT)
BEGIN ATOMIC
  DECLARE L_DOC XML;

  CALL COUNT_DATABASE_ROWS_DPF(L_DOC);
  RETURN
  SELECT *
  FROM XMLTABLE ('$D/NODE' PASSING L_DOC AS "D" COLUMNS 
    TYPESCHEMA VARCHAR(128) PATH 'TABSCHEMA'
  , TABNAME    VARCHAR(128) PATH 'TABNAME'
  , LENGTH     BIGINT       PATH 'ROWS'
  );
END@

-- Usage. Either CALL or SELECT:
CALL COUNT_DATABASE_ROWS_DPF(?)@
SELECT * FROM TABLE(COUNT_DATABASE_ROWS_DPF())@

Answer 2

If your Db2-server runs on Linux/Unix/Windows then you can use the DBMS_OUT.PUT_LINE function to send diagnostic output from SQL routines to the console. The idea is that in your routine, you assign to a variable some text (example, the table name and its count), then call DBMS_OUTPUT.PUT_LINE(...) to cause that text to appear on the console. The disadvantage of this approach is that the output will only appear once the routine has completed. This is often not what you want, sometimes you want to see the row-counts as they become available, so consider instead alternative approaches, as shown below.

To see DBMS_OUTPUT.PUT_LINE output with the Db2 CLP (or db2cmd.exe) you first need to use set serveroutput on before calling the procedure.

But for simple stuff like this, a stored procedure might be unsuitable, because you can use the CLP to do the work in two steps after connecting to the database. This is often more convenient for scripting purposes. The idea is that you make a file to generate the queries, which when you run with CLP creates a second file, and you execute the second file to get the desired results.

Example

Create file gen_counts.sql containing the query that generates the real queries, for example gen_counts.sql might contain

select 'select count(*) from '||rtrim(tabschema)||'.'||rtrim(tabname)||' with ur;' from syscat.tables;

Then you can do these steps:

db2 connect to $database
db2 -txf gen_counts.sql > count_queries.sql
db2 -tvf count_queries.sql > count_results.txt

Note that the output file (in this case count_results.txt) is readable via another shell session while the script continues to run. You can also pipe the output to concurrent jobs if required.

However, experienced DBAs might avoid row-counting all tables in this manner, and might choose instead to ensure that the runstats are always up-to-date for all tables, and accept recent estimates of row counts, which are visible in SYSCAT.TABLES.CARD once runstats are completed. If the stats are up to date, the CARD count is often good enough for many purposes. If exact counts are required, they are often valid only for a specific timestamp if the database is live.