Running SQL statements from a data set
You can run SQL statements that are stored in a data set.
Procedure
To run SQL statements from a data set:
- On the DB2 Administration Menu (ADB2) panel, specify option 2, and press Enter.
-
On the Execute SQL Statements (ADB22)
panel, specify option
2, and press Enter:
ADB22 min -------------------- Execute SQL Statements ------------------- 01:36 Option ===> 2 1 - Edit/run SQL statements DB2 System: DD1A 2 - Run or Explain SQL statements DB2 SQL ID: ADM001 3 - Build SQL SELECT, INSERT, UPDATE or DELETE prototype 4 - Create/drop/label/comment on objects 5 - Grant/revoke privileges on objects -
On the Run or Explain SQL Statements
(ADB222) panel, specify the data
set name that contains the SQL statements that you want to run:
DB2 Admin ---------------- Run or Explain SQL Statements ---------------- 17:44 Option ===> 1 - Run SQL statements from a data set DB2 System: DD1A EDIT first ===> YES (Yes/No) DB2 SQL ID: ADM001 2 - Run or Explain SQL located in a program Program type ===> (1=COBOL, 2=PL/I) ISPF library: Project ===> Group ===> ===> ===> ===> Type ===> Member ===> (blank for member selection list) Other partitioned or sequential data set: Data Set Name ===> Volume Serial ===> (if not cataloged) Alternative pre-allocated DD name: DD name ===> (use ddname(member) for members)You can specify one of the following types as the input data set:
- An ISPF library
- A partitioned or sequential data set
- A pre-allocated DD name
Restrictions: The following restrictions apply to the input data set:- If the record format (RECFM) is either F or FB and the logical record length (LRECL) is either 79 or 80, Db2 Admin Tool assumes that the last 8 bytes of each record are for sequence numbers. Therefore, do not use the last 8 columns of each record to store SQL statements. Otherwise, if the logical record length (LRECL) is not 79 nor 80, Db2 Admin Tool assumes that all of the columns of each record are for SQL statements.
- If the record format (RECFM) is either V or VB, Db2 Admin Tool checks whether the content in columns 1 through 8 of the first record is numeric. If the content is numeric, Db2 Admin Tool assumes that the first 8 bytes of each record are for sequence numbers. Otherwise, if the content of columns 1 through 8 is not numeric, Db2 Admin Tool assumes that all columns are for SQL statements.
- In the EDIT first field, specify whether you want to edit the data set before running the SQL statements.
-
Specify option 1 (Run SQL statements from a data
set), and press Enter:
ADB222 in ---------------- Run or Explain SQL Statements ---------------- 11:27 Option ===> 1 1 - Run SQL statements from a data set DB2 System: DD1A EDIT first ===> NO (Yes/No) DB2 SQL ID: ADM001 2 - Run or Explain SQL located in a program Program type ===> (1=COBOL, 2=PL/I) ISPF library: Project ===> TS6462 Group ===> SPUFI ===> ===> ===> Type ===> INPUT Member ===> CREATE (blank for member selection list) Other partitioned or sequential data set: Data Set Name ===> Volume Serial ===> (if not cataloged) Alternative pre-allocated DD name: DD name ===> (use ddname(member) for members)If you specified Yes in the EDIT first field, the data set is opened in ISPF edit mode where you can then edit the statements. Press End in the edit session to run the SQL statements.
Otherwise, if you specified No in the EDIT first field, the statements are executed immediately.
After the SQL statement is run, a message is displayed on the top left side of the panel to say that the statement was executed. For example:
ADB222 in ---------------- Run or Explain SQL Statements ---------------- 11:27 Option ===> INSERT stmt executed 1 - Run SQL statements from a data set DB2 System: DD1A EDIT first ===> NO (Yes/No) DB2 SQL ID: ADM001 2 - Run or Explain SQL located in a program Program type ===> (1=COBOL, 2=PL/I) ISPF library: Project ===> TS6462 Group ===> SPUFI ===> ===> ===> Type ===> INPUT Member ===> CREATE (blank for member selection list) Other partitioned or sequential data set: Data Set Name ===> Volume Serial ===> (if not cataloged) Alternative pre-allocated DD name: DD name ===> (use ddname(member) for members)While this message is displayed, press PF1 to see any additional information. For UPDATE, INSERT, and DELETE statements, the number of affected rows is displayed. For example:ADB260I 1 row(s) affected by the INSERT statement