Implementation Process of batch execution of DB2 SQL scripts

Batch execution of DB2 SQL scripts is often used when we use DB2 databases. How can we implement batch execution of DB2 SQL scripts? After reading the following, you can find the answer.

Batch execution of DB2 SQL scripts:

Environment:
Windows
DB2 client or server

I. Preparations
Run db2cmd or db2cw to open the DB2 command line processor.
Enter the SQL script storage directory
Use the db2 connect to <dbname> user <username> using <password> command to connect to the database
Use db2 set current schema <schema_name> to set the current SCHEMA. This SCHEMA can be uncreated and DB2 will automatically create it.

2. Execute different types of SQL statements
1. Execute simple SQL commands in the command line
Db2 <SQL statement content>

2. Execute the SQL script file on the command line
Db2-td;-cf <SQL script file path>-l <output log file path>

Description:-td; specifies the end mark of the statement. Generally, SQL statements end with a semicolon, so they are written as "-td ;".
-L <output log file path> is optional.

3. Execute the DB2 Stored Procedure file on the command line
Db2-td @-f <SQL process file path>-l <output log file path>

Description:-td; specifies the end mark of the statement. Generally, the SQL process ends with @, so it is written as "-td @".
-L <output log file path> is optional.

Iii. Write a batch processing script
The batch processing script simply writes commands together, saves them as bat files, and executes them.
Before writing a batch, let's review the DB2 Command Options:

C: \ IBM \ SQLLIB \ BIN> db2? Options
Db2 [option...] [db2-command | SQL-statement |
[? [Phrase | message | sqlstate | class-code]
Option:-a,-c,-d,-e {c | s},-finfile,-I,-lhistfile,-m,-n,-o,
-P,-q,-rreport,-s,-t,-td;,-v,-w,-x, and-zoutputfile.

Option description default settings
-------------------------------------------------------------
-A: SQLCA OFF
-C automatically implements ON
-D: Search and display the XML declaration OFF
-E: SQLCODE/SQLSTATE OFF
-F: Read the input file. OFF
-I: display XML data with indentation OFF
-L record the command to the history file. OFF
-M: The number of affected rows is OFF.
-N remove line feed characters OFF
-O display output ON
-P: display the db2 interactive prompt ON
-Q: leave spaces and linefeeds OFF.
-R: Save the output report to the file. OFF
-S: stops running when a command error occurs. OFF
-T: Set the statement termination character to OFF.
-V: Return Current command OFF
-W: display the FETCH/SELECT warning message ON
-X: the column title is not printed. OFF
-Z: Save all outputs to the output file. OFF

Note:
Use the DB2OPTIONS environment variable to customize the default options.
Close the minus sign (-) after the option letter.
Use update command options to change the option settings to interactive or
File input mode ).

Create a batch file xxx. bat Based on command parameters:

Rem-connect to the database
Db2 connect to <dbname> user <username> using <password>

Rem -- set SCHEMA
Db2 set current schema <schema_name>

Rem -- List of executed SQL scripts
Db2-td;-cf <SQL script file path>-l <output log file path>

Rem-List of SQL Execution Processes
Db2-td @-f <SQL process file path>-l <output log file path>

Note: <SQL process file path> can be an absolute or relative path.

Iv. Batch Processing
Run db2cmd or db2cw to open the DB2 command line processor.
If the <SQL process file path> and <SQL script file path> are absolute paths, you can directly execute xxx

If the <SQL process file path> and <SQL script file path> are relative paths, you need to enter the appropriate directory where you can locate the batch processing, and then execute xxx

DB2 Online Export Method

Usage of DB2 Merge statements

Overview of DB2 Flow Control clauses

In-depth discussion of DB2 table connection principles

Common DB2 cycle usage