Backup DB2 table in "INSERT" instruction.



IBM's flagship relational database management system

Backup DB2 table in "INSERT" instruction.

Postby leonenapoletano » Thu Jul 15, 2010 3:36 pm

Hello,
before all, sorry for my English, i'm Italian.

I was looking for a way to create, from the contents of a table, a "SPUFI" file that contains all "INSERT INTO" instructions, for each row in the table.

EXAMPLE:-----------------------------------------------------------------------------------------
start situation
SELECT * FROM FROM DB2.T3IDRIF

                                             
      COD  FL                      DT 
      BK   VAL    PROGVAL          RIF
 --------  -----  -------  -----------
      104  S            0     20091231
      104  N            0     20101231



result expected

INSERT INTO DB2.T3IDRIF
 ( COD_BK
 , FL_VAL
 , PROGVAL
 , DT_RIF )
VALUES
 ( 104
 , 'S'
 , 0
 , 20091231 );

INSERT INTO DB2.T3IDRIF
 ( COD_BK
 , FL_VAL
 , PROGVAL
 , DT_RIF )
VALUES
 ( 104
 , 'N'
 , 0
 , 20101231 );


END-EXAMPLE ----------------------------------------------------------------------------------

The target is naturally to create a backup file, that works for different environment.

Thank you.

Massimo
leonenapoletano
 
Posts: 2
Joined: Thu Jul 15, 2010 2:42 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Backup DB2 table in "INSERT" instruction.

Postby GuyC » Thu Jul 15, 2010 5:02 pm

something like
select 'INSERT INTO TAB1 (' !! char(col_bk) !!  ',''' !! flcal !! ''',' !! char(DT_RIF) !! ');'
from tab1

would do the trick.
But why don't you unload the table to a CSV-file ? most DBMSs support a kind of import/export from CSV.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Backup DB2 table in "INSERT" instruction.

Postby leonenapoletano » Fri Jul 16, 2010 8:54 pm

Thanks for the answer, it is the result I expected.

The result was:

INSERT INTO DB2.T3IDRIF ( 00000000000104. , 'S' , 0000000000000, 20091231. )
INSERT INTO DB2.T3IDRIF ( 00000000000104. , 'N' , 0000000000000, 20101231. )


And I was "worried" for the ' . ' at the end of decimal fields.
After I run this query, and I saw that DB2 accept this decimal format.
I don't know yet if this "INSERT INTO" file works on others DB...

So, just run an unload step, using the query you posted.

Thanks a lot.

Massimo
leonenapoletano
 
Posts: 2
Joined: Thu Jul 15, 2010 2:42 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post