Printing Results from a SQL Procedure in Db2

As a developer, you often find yourself working with stored procedures and functions to perform complex tasks. However, when it comes to printing the results of these procedures, things can get tricky. In this article, we’ll explore how to print the results of a SQL procedure in Db2.

Understanding the Problem

The problem at hand is that when you run a stored procedure in Db2, it returns a cursor object that contains the query result set. However, if you simply execute the procedure and pipe its output to another command, such as db2 -ntd~ -f script.sql > dump.csv, only the success message is printed, and no actual data is generated.

Non-DPF Db2 for LUW Systems

For non-DFP (Dynamic Procedure Facility) systems, we can use a different approach. In this case, we create a stored procedure that returns a table with three columns: TABSCHEMA, TABNAME, and ROWS. The procedure iterates over all tables in the database and uses a dynamic SQL statement to execute a query that counts the number of rows.

Here’s an example code snippet:

--#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())@

This procedure uses a cursor to iterate over all tables in the database and executes a dynamic SQL statement for each table. The result of the query is stored in a variable L_ROWS, which is then piped to the next stage.

Any Db2 for LUW Systems

For DPF systems, things get a bit trickier. In this case, we need to wrap the code that would normally be executed inline into a stored procedure. We create a new procedure called COUNT_DATABASE_ROWS_DPF that takes an output parameter P_DOC, which is an XML document.

Here’s an example code snippet:

--#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@

This procedure uses a call to the COUNT_DATABASE_ROWS_DPF function, which generates an XML document containing all table information. We then use an XMLTABLE function to extract this data into a result set.

Usage

To run either of these procedures, you can simply call it and pass in any required parameters. For example:

CALL COUNT_DATABASE_ROWS_DPF(?)@
SELECT * FROM TABLE(COUNT_DATABASE_ROWS_DPF())@

These examples demonstrate how to print the results of a stored procedure in Db2.

Conclusion

In conclusion, printing the results of a stored procedure in Db2 requires some creativity. For non-DFP systems, we can create a stored procedure that returns a table with three columns: TABSCHEMA, TABNAME, and ROWS. For DPF systems, we need to wrap the code inline into a stored procedure and use an XMLTABLE function to extract this data into a result set.

Last modified on 2023-07-02