Creating script for multiple sql files in DB2 iseries

Question

I'd like to create an sql file to execute multiple other sql files.
In order to run or "deploy" the files correctly.

I usually (in oracle) did something like:

@/location/table1.sql  
@/location/table1triggers.sql  
@/location/table1index's.sql  
@/location/table2.sql  
@/location/table2triggers.sql  
@/location/table2index's.sql  
@/location/table1constraints.sql (FK for table 2)

And then just executed that whole file, is there anything like that for db2 iseries / ibm?

Do you plan to submit the scripts from a Windows/Linux client, or natively on i-Series ? Give details. — mao
– mao, Commented Apr 19, 2018 at 13:40
@mao I use the "IBM i Access Client Solutions" to develope in, so i guess submitting from Windows yes. — Anders Metnik
– Anders Metnik, Commented Apr 20, 2018 at 11:25
For Windows, any scripting language (python, perl, php, CMD, powershell etc) can be used against remote Db2-databases, once properly configured. So choose your scripting method and get busy. Alternatively script on iSeries directly as per jmarkmurphy answer. Depends on skills and deployment-requirements. — mao
– mao, Commented Apr 20, 2018 at 11:51
@mao so I'm required to do another scripting language than SQL. Okay thanks, not what I was hoping though. — Anders Metnik
– Anders Metnik, Commented Apr 20, 2018 at 11:52
You have mentioned that you wish to use IBM i Access Client Solutions (ACS). I am suggesting that you use its command line switches to kick off exactly the sql script you require, automatically, from a Windows command prompt /or/ from a .bat file which you can sequence as you wish. Hope that helps. — MandyShaw
– MandyShaw, Commented Apr 30, 2018 at 11:33

jmarkmurphy · Accepted Answer · 2018-04-19 16:00:14Z

3

On IBM i, I would do that by creating a CL Program that runs each script like this:

RUNSQLSTM SRCSTMF('/location/table1.sql')
RUNSQLSTM SRCSTMF('/location/table1triggers.sql')
RUNSQLSTM SRCSTMF('/location/table1indexes.sql')
RUNSQLSTM SRCSTMF('/location/table2.sql')
RUNSQLSTM SRCSTMF('/location/table2triggers.sql')
RUNSQLSTM SRCSTMF('/location/table2indexes.sql')
RUNSQLSTM SRCSTMF('/location/table1constraints.sql') /* FK for table 2 */

This would then be compiled into a program using the CRTCLPGM command, then executed using the CALL command.

Though there is an INCLUDE statement in the DB2 for i syntax, that can only be embedded in programs written RPG, COBOL, or C.

answered Apr 19, 2018 at 16:00

jmarkmurphy

11.6k37 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating script for multiple sql files in DB2 iseries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related