1

What I am trying to do here is to Generate insert scripts for selected tables in my database in DB2 10.5 LUW.

I have modified a procedure available here as below.

CREATE PROCEDURE Create_Inserts (
IN p_Table_Name     VARCHAR(100)
,IN p_where         VARCHAR(500) default null
)
SPECIFIC Create_Inserts
DYNAMIC RESULT SETS 1 
BEGIN
 DECLARE v_Column_Name           VARCHAR(128);--
 DECLARE v_Data_Type             VARCHAR(18);--
 DECLARE SQLCODE                 INT DEFAULT 0;--
 DECLARE v_FirstTimeFlg          INTEGER DEFAULT 1;--
 DECLARE SQLSTATE                CHAR(5) DEFAULT '00000';--
 DECLARE c_datatype              CONDITION FOR SQLSTATE '20000';--
 DECLARE c_noselect_cols         CONDITION FOR SQLSTATE '99901';--
 DECLARE v_dynSQL                VARCHAR(16000);--
 DECLARE v_datSQL                VARCHAR(16000);--
 DECLARE v_MSG2             VARCHAR(1);--
 DECLARE v_colstatement          VARCHAR(16000) DEFAULT ' ';--
 DECLARE v_datstatement          VARCHAR(16000) DEFAULT ' ';--
 DECLARE v_data                  VARCHAR(16000);--
 DECLARE v_start_quotes          VARCHAR(20) DEFAULT '''''''''||';--
 DECLARE v_end_quotes            VARCHAR(20) DEFAULT '||''''''''';--
 DECLARE v_cur_statement         STATEMENT;--
 DECLARE v_col_statement         STATEMENT;--
 DECLARE c_column           CURSOR FOR v_col_statement;--
 DECLARE c_data             CURSOR FOR v_cur_statement;--

SET v_datSQL = 'SELECT colname, typename FROM '
               ||' syscat.columns WHERE '
               ||' TabName = UPPER('''||p_Table_Name||''')'
               ||' ORDER BY colno';--
PREPARE v_col_statement from v_datSQL;--
OPEN c_column;--
FETCH c_column INTO v_Column_Name, v_Data_Type;--
    WHILE (SQLSTATE = '00000') DO
        IF v_FirstTimeFlg = 1 THEN
        SET v_colstatement = v_column_name ;--
            SET v_FirstTimeFlg  = 2;--
        ELSE
            SET v_colstatement = v_colstatement || ' ,' || v_column_name ;--
        END IF;--
        FETCH c_column INTO v_Column_Name, v_Data_Type;--
    END while;--
    IF v_FirstTimeFlg = 1 THEN 
        SIGNAL c_noselect_cols;--
    END IF;--
CLOSE c_column;--
SET v_FirstTimeFlg = 1;--
OPEN c_column;--
FETCH c_column INTO v_Column_Name, v_Data_Type;--
WHILE (SQLSTATE = '00000') DO
        IF v_Data_Type NOT IN ('INTEGER', 'DECIMAL', 'SMALLINT', 'CHARACTER','VARCHAR','DATE','TIME','TIMESTAMP') THEN
            SIGNAL c_datatype;--
        END IF;--

    IF v_FirstTimeFlg = 1 THEN
    SET v_datstatement = v_datstatement ||
                                CASE v_Data_Type
                    WHEN 'INTEGER' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
                                            WHEN 'DECIMAL' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
                    WHEN 'SMALLINT'  THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
                    WHEN 'CHARACTER' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')' 
                    WHEN 'VARCHAR' THEN   'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')' 
                                            WHEN 'DATE' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
                                            WHEN 'TIME' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
                                            WHEN 'TIMESTAMP' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
                    ELSE CHR(9) -- this will never happen
                        END;--

    SET v_FirstTimeFlg  = 2;--
     ELSE
    SET v_datstatement = v_datstatement || ' || '' , '' || ' || 
                                    CASE v_Data_Type
                    WHEN 'INTEGER' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
                                            WHEN 'DECIMAL' THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
                    WHEN 'SMALLINT'  THEN 'coalesce(RTRIM(CHAR('||v_Column_Name||')),''NULL'')'
                    WHEN 'CHARACTER' THEN 'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')' 
                    WHEN 'VARCHAR' THEN   'coalesce(' ||v_start_quotes ||v_Column_Name|| v_end_quotes ||',''NULL'')' 
                                            WHEN 'DATE' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
                                            WHEN 'TIME' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
                                            WHEN 'TIMESTAMP' THEN 'coalesce(' ||v_start_quotes || 'RTRIM(CHAR('||v_Column_Name ||'))'|| v_end_quotes||',''NULL'')'
                    ELSE CHR(9) -- this will never happen
                        END;--
         END IF;--
     FETCH c_column INTO v_Column_Name, v_Data_Type;--
 END while;--
 CLOSE c_column;--
SET v_dynSQL = 'SELECT '||v_datstatement|| ' FROM ' || upper(p_Table_name) || ' ';--
IF p_where is NOT NULL THEN
    SET v_dynSQL = v_dynSQL ||p_where;--
END IF;--
PREPARE v_cur_statement from v_dynSQL;--
OPEN c_data;--
FETCH c_data INTO v_data;--
WHILE (SQLSTATE = '00000') DO


        INSERT INTO TB_INSERTS VALUES('INSERT INTO '|| Upper(p_Table_Name) || ' ( '||v_colstatement|| ')  VALUES( '||v_data ||');');--
        FETCH c_data INTO v_data;--
END while;--
CLOSE c_data;--
BEGIN
        DECLARE c2 CURSOR WITH RETURN TO CLIENT FOR
            SELECT * from TB_INSERTS;--
        OPEN c2;--
END;--
END;

Now since there are more than one tables for which we need insert statements, i call the same from another procedure. Intending to Generate insert scripts for all the necessary procedures in one procedure call.

CREATE PROCEDURE sp_generate_static_inserts ()
DYNAMIC RESULT SETS 100
--WITH RETURN TO CLIENT
  P1: BEGIN

   call create_inserts('TABLE1');--
   call create_inserts('TABLE2');--
     --.....call create_inserts('TABLEn');--

    END P1;

Next we call the proc from CLP. using the below command

   db2 call sp_generate_static_inserts ()  > Inserts.sql

The output is Good. But it Duplicates the insert statement. The insert statement comes as many times as the procedure Create_Inserts is called(in other words 1 Copy for Each Result Set). For this particular call, the Inserts.sql file comes out to like this.

INSERT_STMT
--------------------------------------------------
-------TABLE1
INSERT INTO TABLE1 (COL1) VALUES('ABC');

INSERT_STMT
--------------------------------------------------
-------TABLE2
INSERT INTO TABLE2 (COL1) VALUES('DEF');

INSERT_STMT
--------------------------------------------------
-------TABLE1
INSERT INTO TABLE1 (COL1) VALUES('ABC');

INSERT_STMT
--------------------------------------------------
-------TABLE2
INSERT INTO TABLE2 (COL1) VALUES('DEF');

Notice the inserts coming Two Times for each table. This multiplies for each call I add in the procedure. Is there something that I am doing wrong Or a work around so that I can get a clean Insert File ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.