iSeries DB2 - SQL DB conversion.

[Huddy]

Hi

I have managed to create a tool to export our ageing iSeries database to a flat file on a destination server using CPYTOIMPF.. Works a treat but we've quite an number of files to transfer.

Is there anyway of automatically creating SQL DDL files to create the tables(s) based on the DB2 metadata so I can migrate the the file to the SQL DB?

I'm not sure if this will help?

http://db2guideonline.blogspot.com/2014/06/how-to-create-duplicate-or-copy-table.html

Any ones help or advice would be greatly appreciated.

Many thanks

[Jim_IT]

https://www.ibm.com/developerworks/community/wikis/home/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.GENERATE_SQL()%20procedure

I've used this to build DDL on all objects in a library outputting the data to source file.

Jim

[Huddy]

Hi Jim_T.. that's fantastic. works a treat, thanks you. One question though.. I can run the following from inside an SQL command prompt not a problem:

call qsys2/generate_sql('SDF99P', 'BLBDAYLIB','TABLE','QSQLSRC','GRLIB','SDF99P', statement_formatting_option => '0', naming_option => ' SQL', ccsid_option => '0', trigger_option => '0', message_level => '30', privileges_option =>'0')

but I can't get in working from within a CLP either directly or using RUNSQL?

Any ideas?
Paul

[Jim_IT]

Paul,

It took me a little while to get the GENERATE_SQL procedure to work.

I originally figured it out on a 7.1 partition which does not allow named parameters but now we have a couple of 7.3 partitions.

It seems the procedure call does not like STATEMENT_FORMATTING_OPTION parameter name.

Try the old way.

runsqlibm sql('call qsys2.generate_sql(''SDF99P'',''BLBDAYLIB'',''TABL E'',''QSQLSRC'',''GRLIB'',''SDF99P'',39,''1'',''0' ',''ISO'',''-'',''ISO'',''.'',''SQL'',''.'',''0'',''0'',30,''1' ',''1'',''1'',''0'',''1'',''1'',''0'',''0'',''0'', ''0'',X''00'',X''00'',''0'',''0'',''0'')')

Jim