Stored procedure in Db2 to generate headers

generateHeaders.sql

--#SET TERMINATOR @

SET SERVEROUTPUT ON@

/**
 * Creates a temporary table with the headers of a given query.
 * A user temporary tablespace is necessary. This is useful when exporting data.
 * The table with the header is called: session.header.
 *
 * IN query
 *   Query to analyze.
 */
CREATE OR REPLACE PROCEDURE DBA.GENERATE_HEADERS(
  IN query VARCHAR(32000)
  )
 BEGIN
  DECLARE at_end BOOLEAN; -- End of the cursor.
  DECLARE result1 RESULT_SET_LOCATOR VARYING;
  DECLARE stmt_crt VARCHAR(32000); -- Statement for create.
  DECLARE stmt_insert VARCHAR(32000); -- Statement for delete.
  -- All columns returned from describe.
  DECLARE col_1 VARCHAR(128);
  DECLARE col_2 VARCHAR(128);
  DECLARE col_3 VARCHAR(128);
  DECLARE col_4 VARCHAR(128);
  DECLARE col_name VARCHAR(128);
  DECLARE col_6 VARCHAR(128);
  DECLARE col_7 VARCHAR(128);
  DECLARE col_8 VARCHAR(128);
  DECLARE cont INTEGER;  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE TABLE_NOT_FOUND CONDITION FOR SQLSTATE '42704';
  DECLARE CONTINUE HANDLER FOR TABLE_NOT_FOUND BEGIN END;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET at_end = TRUE;

  -- Drops any previous temporal table with the same name.
  EXECUTE IMMEDIATE 'DROP TABLE session.header';

  -- Calls the describe command via stored procedure and captures the result sets.
  --CALL DBMS_OUTPUT.PUT_LINE('Starting');
  CALL SYSPROC.ADMIN_CMD('describe ' || query);
  ASSOCIATE RESULT SET LOCATORS(result1)
       WITH PROCEDURE SYSPROC.ADMIN_CMD;
  ALLOCATE rsCur CURSOR FOR RESULT SET result1;

  --CALL DBMS_OUTPUT.PUT_LINE('Result set captured');

  -- Starts the dynamic statements.
  SET stmt_crt = 'DECLARE GLOBAL TEMPORARY TABLE header (';
  SET stmt_insert = 'INSERT INTO session.header VALUES (';

  SET cont = 1;
  SET at_end = FALSE;

  -- Gets the first column metadata.
  --CALL DBMS_OUTPUT.PUT_LINE('First column');
  FETCH FROM rsCur INTO col_1, col_2, col_3, col_4, col_name, col_6, col_7, col_8;
  SET stmt_crt = stmt_crt || 'col' || cont || ' VARCHAR(32)' ;
  SET stmt_insert = stmt_insert || '''' || col_name || '''';

  -- Processes the rest of the columns.
  FETCH FROM rsCur INTO col_1, col_2, col_3, col_4, col_name, col_6, col_7, col_8;
  --CALL DBMS_OUTPUT.PUT_LINE('Before loop');
  WHILE (at_end = FALSE) DO
   --CALL DBMS_OUTPUT.PUT_LINE('In the loop');
   SET cont = cont + 1;
   --CALL DBMS_OUTPUT.PUT_LINE(cont);
   --CALL DBMS_OUTPUT.PUT_LINE(col_name);
   SET stmt_crt = stmt_crt || ', col' || cont || ' VARCHAR(32)' ;
   SET stmt_insert = stmt_insert || ', ''' || col_name || '''';

   FETCH FROM rsCur INTO col_1, col_2, col_3, col_4, col_name, col_6, col_7, col_8;
  END WHILE;
  --CALL DBMS_OUTPUT.PUT_LINE('After loop');
  SET stmt_crt = stmt_crt || ') ON COMMIT PRESERVE ROWS';
  SET stmt_insert = stmt_insert || ')';

  --CALL DBMS_OUTPUT.PUT_LINE('Dynamic executions');
  --CALL DBMS_OUTPUT.PUT_LINE(stmt_crt);
  --CALL DBMS_OUTPUT.PUT_LINE(stmt_insert)
  EXECUTE IMMEDIATE stmt_crt;
  EXECUTE IMMEDIATE stmt_insert;

  --CALL DBMS_OUTPUT.PUT_LINE('Finishing');
 END
 @

-- Example of how to use this stored procedure.
CALL DBA.GENERATE_HEADERS('select * from syscat.tables') @
EXPORT TO myfile_header OF DEL SELECT * FROM SESSION.header @
EXPORT TO myfile_body OF DEL SELECT * FROM SYSCAT.TABLES @
!cat myfile_header myfile_body > myfile @