IBM Mainframe Forum

by **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 all

SELECT * FROM FROM DB2.T3IDRIF

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

result expected

Select all

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

by **GuyC** » Thu Jul 15, 2010 5:02 pm

something like

Select all

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.

by **leonenapoletano** » Fri Jul 16, 2010 8:54 pm

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

The result was:

Select all

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

IBM Mainframe Forum

Backup DB2 table in "INSERT" instruction.

Backup DB2 table in "INSERT" instruction.

Re: Backup DB2 table in "INSERT" instruction.

Re: Backup DB2 table in "INSERT" instruction.