CREATE TABLE RESULT(text CLOB(2M));

DROP PROCEDURE insertscript;

--#SET TERMINATOR @
CREATE PROCEDURE insertscript(IN ptabschema  VARCHAR(128),
                              IN ptabname    VARCHAR(128),
                              IN pbatchsize  INTEGER,
                              IN pterminator VARCHAR(2))
BEGIN
  DECLARE SQLCODE      INTEGER;
  DECLARE SQLSTATE     CHAR(5);
  DECLARE inserttext   CLOB(2M);
  DECLARE selecttext   CLOB(2M);
  DECLARE insertheader CLOB(2M);
  DECLARE colname      VARCHAR(128);
  DECLARE colcount     INTEGER;
  DECLARE colno        INTEGER;
  DECLARE rowcount     INTEGER;
  DECLARE insertcount  INTEGER;
  DECLARE colvalue     CLOB(128); 
  DECLARE typeid       SMALLINT;
  DECLARE done         SMALLINT;
  DECLARE selectstmt   STATEMENT;
  DECLARE selectcur    CURSOR WITH HOLD FOR selectstmt;
  
  IF NOT EXISTS(SELECT 1 FROM SYSCAT.TABLES 
                 WHERE tabschema = ptabschema
                   AND tabname = ptabname) THEN
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Table not found (remember case!)';
  END IF; 
  
  DELETE FROM result;  
  SET insertheader = 'INSERT INTO "' || ptabschema || '"."'
                   || ptabname || '" VALUES ';
  
  SET selecttext = 'SELECT colno, colvalue, typeid FROM "'
                 || ptabschema || '"."' || ptabname || '" AS T, LATERAL(VALUES ';
  FOR column AS SELECT colno, colname, typename, codepage FROM SYSCAT.COLUMNS 
                 WHERE tabschema = ptabschema
                   AND tabname   = ptabname
                 ORDER BY colno
  DO
    SET typeid = CASE WHEN typename IN ('SMALLINT', 'INTEGER', 'BIGINT',
                                        'FLOAT', 'DOUBLE', 'REAL', 'DECFLOAT',
                                        'DECIMAL') THEN 1
                      WHEN typename IN ('CHAR', 'VARCHAR', 'GRAPHIC', 'VARGRAPHIC',
                                        'CHARACTER', 'CLOB', 'DBCLOB') THEN
                            CASE WHEN codepage = 0 THEN 3 ELSE 2 END
                      WHEN typename = 'BLOB' THEN 4
                      ELSE RAISE_ERROR('78000', 'Unsupported type') END; 
    SET selecttext = selecttext 
                   || CASE WHEN colno > 0 THEN ',' ELSE '' END 
                   || '(SMALLINT(' || CHAR(colno)
                   || '), ' || CASE WHEN typeid = 1
                                    THEN 'TRIM(CHAR(T."' || colname || '")), '
                                    WHEN typeid = 3 
                                    THEN 'HEX(T."' || colname || '"), ' 
                                    WHEN typeid = 4
                                    THEN 'HEX(VARCHAR(T."' || colname || '")), '
                                    ELSE 'VARCHAR(T."' || colname || '"), ' END 
                   || CHAR(typeid) || ')';
    SET colcount = colno;
  END FOR;
  SET selecttext = selecttext || ') AS S(colno, colvalue, typeid)';
  PREPARE selectstmt FROM selecttext;

  SET rowcount    = 0;
  SET insertcount = 0;
  SET done        = 0;
  OPEN selectcur;
mainloop: WHILE rowcount < pbatchsize DO
    IF rowcount = 0 THEN
      SET inserttext = insertheader;
    END IF;
      
subloop: LOOP
      FETCH selectcur INTO colno, colvalue, typeid;
      IF SQLCODE = 100 THEN 
        SET done = 1;
        LEAVE subloop; 
      END IF;
      SET inserttext = inserttext 
                     || CASE WHEN colno = 0 AND rowcount = 0 THEN '('
                             WHEN colno = 0                  THEN '), ' || CHR(10) || chr(13) || '('
                             ELSE                                 ',' END
                     || CASE WHEN colvalue IS NULL THEN 'NULL'
                             WHEN typeid = 1       THEN colvalue
                             WHEN typeid = 2       THEN '''' || colvalue || ''''
                             WHEN typeid = 4       THEN 'BLOB(x''' || colvalue || ''')'
                             ELSE 'x''' || colvalue || '''' END;
      IF colno = colcount THEN
        SET rowcount = rowcount + 1;
      END IF;
      
      IF rowcount >= pbatchsize THEN LEAVE subloop; END IF;
    END LOOP;        
    SET inserttext = inserttext || ')' || pterminator;
    IF rowcount > 0 THEN 
      INSERT INTO RESULT VALUES inserttext;
      SET rowcount = 0;
      SET insertcount = insertcount + 1;
      IF MOD(insertcount, 100) = 0 THEN
        COMMIT;
      END IF; 
    ELSE 
      LEAVE mainloop;
    END IF; 
  END WHILE;
END
@
--#SET TERMINATOR ;
CALL insertscript('myschema', 'mytab', 10, ';');

