Running the Db2 command line processor in batch mode

When you run the Db2 command line processor in batch mode, you can run all SQL statements in the batch as a single transaction.

Before you begin

Your z/OS® UNIX System Services .profile file and CLPPROPERTIES files need to be set up so that you can use the Db2 command line processor.
See Setting up the Db2 command line processor for more information. Perform the optional steps as well as the required step in the procedure.
To enable you to run the example in this topic, you need to make the following changes to the Db2 command line processor environment:
- Include the alias MYALIAS01 for your connection information in the Db2 command line processor properties file. A line of the following form must exist in the Db2 command line processor properties file.
```
MYALIAS01=connection-url,user-id,password
```
  CONNECT (Db2 command line processor) describes the syntax for connection-url,user-id, and password.

About this task

To run the Db2 command line processor in batch mode, you provide the input from a file. When you run in batch mode, you can specify options to direct the Db2 command line processor to issue all of the SQL statements in the file in a single transaction. The transaction does not succeed unless all SQL statements succeed. If one or more SQL statements receive a negative SQLCODE, all SQL work is rolled back to the previous commit point.

To commit all SQL work in the input file, you include a COMMIT or TERMINATE statement at the end of the file. If there is no COMMIT or TERMINATE statement at the end of the file, all work is rolled back to the previous commit point.

Important: In the following situations that involve calls to stored procedures, a set of SQL statements are not executed as a single transaction because of COMMIT statements in the stored procedures or stored procedure definitions:

A CALL statement in the input file calls a stored procedure that contains a COMMIT statement.
A CALL statement in the input file calls a stored procedure that is defined as COMMIT ON RETURN YES.

In both cases, any SQL statements in the input file before the CALL statement are also committed.

Important: If you call a stored procedure that does work other than issuing SQL statements, such as issuing Db2 commands or utilities, the failure of SQL statements has no effect on the command or utility work. If the command or utility work completes successfully, that work is not rolled back if an SQL statement fails.

Procedure

To execute a set of SQL statements as a single transaction, follow these steps.

Create the Db2 command line processor input file in z/OS UNIX System Services.
Change to the directory that contains the input file that you created in the previous step.
Start the Db2 command line processor with a command like this one:
```
db2 +c -s -f file-name
```
- file-name is the input file that you created in step 1.
- +c specifies that the SQL statements in the input file are not to be automatically committed.
- -s specifies that the Db2 command line processor stops processing input and exits if an error occurs during execution of an SQL statement or a Db2 command line processor command.

Example

Create a file named test.sql file with the following contents.

-- Input file for running Db2 command line processor
-- in batch mode.
--
-- Issue a UNIX System Services cat command by 
-- specifying ! before the command text
!cat test.sql
-- On the system that you set up to run the Db2 command line
-- processor, create a file with this content called test.sql.
-- Issue db2 +c -s -f test.sql to run it.
CONNECT TO MYALIAS01
LIST COMMAND OPTIONS
!echo CREATE DATABASE TESTDB
CREATE DATABASE TESTDB
CREATE TABLESPACE TESTSP IN TESTDB
CREATE TABLE TEST(COL1 INT NOT NULL, \
 COL2 VARCHAR(15), PRIMARY KEY(COL1)) IN TESTDB.TESTSP
CREATE UNIQUE INDEX INDEX1 ON TEST(COL1)
INSERT INTO TEST VALUES(1, 'ROW01')
INSERT INTO TEST VALUES(2, 'ROW02')
INSERT INTO TEST VALUES(3, 'ROW03')
INSERT INTO TEST VALUES(4, 'ROW04')
SELECT * FROM TEST
UPDATE TEST SET COL2='ROW000002' WHERE COL1=2
UPDATE TEST SET COL2='ROW000004' WHERE COL1=4
SELECT * FROM TEST 
UPDATE TEST SET COL1=21 WHERE COL1=1
UPDATE TEST SET COL2='ROW0000021' WHERE COL1=1
SELECT * FROM TEST 
-- The following statement will fail because the table name
-- does not exist (It is TSET instead of TEST.) 
-- All SQL work in this file will be rolled back.
DELETE FROM TSET WHERE COL1=21
SELECT * FROM TEST
TERMINATE

Change to the directory that contains the test.sql file, and run the Db2 command line processor with test.sql as input:

db2 +c -s -f test.sql

Because the DELETE statement fails, all SQL work is rolled back.

Comment out the DELETE statement, and rerun Db2 command line processor with test.sql as input. All SQL statements are committed when the TERMINATE command runs.