DB2 iSeries中如何创建执行多个SQL文件的部署脚本？

Absolutely! IBM i (formerly iSeries) DB2 offers similar functionality to Oracle's @ syntax for running multiple SQL scripts in sequence—you just need to use the right tools for the job. Here are two common approaches you can use:

1. Use a CL Command Script (.CLP File) #

If you prefer a script that leverages IBM i's native command language, create a CL (Control Language) script that calls the RUNSQLSTM command for each of your SQL files. This is great for adding error handling and transaction control.

Example CL script (saved as a source member in a library, e.g., MYLIB/MYMAINSCRIPT):

-- Create table1 and related objects
RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(TABLE1) COMMIT(*NONE)
RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(TABLE1TRIGGERS) COMMIT(*NONE)
RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(TABLE1INDEXES) COMMIT(*NONE)

-- Create table2 and related objects
RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(TABLE2) COMMIT(*NONE)
RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(TABLE2TRIGGERS) COMMIT(*NONE)
RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(TABLE2INDEXES) COMMIT(*NONE)

-- Add constraints (including table2's foreign keys)
RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(TABLE1CONSTRAINTS) COMMIT(*NONE)

Key Notes: #

• SRCFILE: Specifies the library and source physical file where your SQL scripts are stored (each script is a separate member in this file).
• SRCMBR: The name of the source member containing the SQL for that step.
• COMMIT(*NONE): Adjust this based on your transaction needs—use COMMIT(*CHG) if you want transactional control, or COMMIT(*ALL) to commit all changes at once.

To run the CL script, execute it directly from the IBM i command line:

CALL MYLIB/MYMAINSCRIPT

2. Use INCLUDE in a Master SQL Script #

If you want a pure SQL approach (closer to Oracle's @ syntax), you can use the INCLUDE directive in a master SQL script to reference other SQL files. This works both with source physical file members and IFS (Integrated File System) files.

Example with Source Physical Files #

Create a master SQL member (e.g., MYLIB/MYSQLSRC(MAINSCRIPT)):

INCLUDE MYLIB/MYSQLSRC(TABLE1);
INCLUDE MYLIB/MYSQLSRC(TABLE1TRIGGERS);
INCLUDE MYLIB/MYSQLSRC(TABLE1INDEXES);
INCLUDE MYLIB/MYSQLSRC(TABLE2);
INCLUDE MYLIB/MYSQLSRC(TABLE2TRIGGERS);
INCLUDE MYLIB/MYSQLSRC(TABLE2INDEXES);
INCLUDE MYLIB/MYSQLSRC(TABLE1CONSTRAINTS);

Run it with:

RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(MAINSCRIPT) COMMIT(*NONE)

Example with IFS Files #

If your SQL scripts are stored in the IFS (like regular files on a Unix/Windows system), use the full IFS path in INCLUDE:

INCLUDE '/home/deploy/sql/table1.sql';
INCLUDE '/home/deploy/sql/table1triggers.sql';
-- ... rest of your scripts ...

Run the master script with:

RUNSQLSTM SRCSTMF('/home/deploy/sql/mainscript.sql') COMMIT(*NONE)

Bonus: Add Error Handling #

To make your deployment more robust, add error checking in your CL script. For example, you can catch errors and stop execution or send a notification:

RUNSQLSTM SRCFILE(MYLIB/MYSQLSRC) SRCMBR(TABLE1) COMMIT(*NONE)
-- Catch any error and jump to the ERROR label
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))

-- ... other RUNSQLSTM commands ...

-- If all succeed, skip to the end
GOTO CMDLBL(END)

ERROR:
-- Send an error message to the external message queue
SNDPGMMSG MSG('Failed to execute TABLE1 script') TOPGMQ(*EXT) MSGTYPE(*ESCAPE)

END:

内容的提问来源于stack exchange，提问作者Anders Metnik