Implementation of batch SQL script execution in DB2

The following describes how to implement batchcompute SQL scripts in DB2. If you have encountered similar problems in batchcompute, I believe it will help you learn about the batch execution of DB2.

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

AIX:

Db2-tvf file name. SQL

Online Implementation of changing the DB2 page size

How to reset the silent State of the DB2 tablespace

How to Create a DB2 tablespace in an aix Environment

How to Set garbled characters in the DB2 code page

Implementation of DB2 string connection