Batch Processor for Db2 for z/OS SQL output, audit messages or return codes written to a dataset
Article ID: 54467
Updated On:
Products
Issue/Introduction
When SQL is being executed with Batch Processor for Db2 for z/OS (RBP) how can SQL output be directed to a dataset?
When the Batch Processor executes there are options available to direct sections of the output to various destinations.
The sections of output include:
- The SQL itself
- The return codes and audit messages from Db2
- The return codes and audit messages from Batch Processor
- The headings and data returned by Db2.
Resolution
Batch execution with Batch Processor
In batch, the input SQL to batch processor is read from DD card "BPIIPT". The control cards for batch processor are read from DD card "BPIOPT".
The DD card "PTISELDD" receives.....
1. The SQL SELECT statements along with the data
2. Headings returned along with a count of records selected
3. The error or warning message if the query fails
if the DD is allocated prior to the execution.
If PTISELDD is not specified, the data returned by the SELECT statements is not displayed. No return codes are listed here.
The batch processor .LIST command (found in DD BPIIPT) is able to be used to direct the Batch Processor, DB2 audit messages and return codes, error and
warning messages and all SQL statements to a particular SYSOUT class, printer, DD or a dataset.
To a dataset : .LIST FILE(dataset name)
To SYSOUT A : .LIST SYSOUT(A)
To a specific DD : .LIST DDNAME(LISTDD)
To held output : .LIST SYSOUT(A,,X)
To a printer : .LIST SYSOUT(A,,prntid)
The JES messages, JCL and System Messages continue to go to DD's JESMSGLG, JESJCL and JESYSMSG.
Note: The audit trail messages and return codes for all statements go to SYSOUT PTISELDD. In addition, it receives the execution results and data just for select statements.
PTISELDD can be of use when a user wants the data returned from data retrieval by select statements into a dataset. There is no facility to suppress the column
headings relating to the data returned by SQL select statements.
Output to SYSOUT in a batch execution of Batch Processor.
A method for sending all the output to one output location in your SYSOUT is to do the following in the JCL.
//PTISELDD DD SYSOUT=*,DCB=(LRECL=4092,BLKSIZE=4096,RECFM=VB)
//SYSOUT DD SYSOUT=*,DCB=(LRECL=4092,BLKSIZE=4096,RECFM=VB)
.
.
.
.
.LIST DDNAME(PTISELDD).......<<<inside the BPIOPT DD input cards.
Output to a dataset in a batch execution of Batch Processor.
A method for sending all the output to one output location in a dataset is to add the following in the JCL.
//PTISELDD DD DSN=datasetname,
// RECFM=VB,LRECL=4092,BLKSIZE=4096,DSORG=PS,
// DISP=(NEW,CATLG,DELETE),SPACE=(CYL,(1,1))
This places all the SQL, DB2 Return codes, Batch Processor Return codes , audit messages, error and warning messages along with the data returned by
the select statements in the PTISELDD DD dataset.
Output to a dataset in a batch execution of Batch Processor.
A method for sending all the output to one output location in a dataset is to add the following in the Batch Processor BPIIPT with the SQL being executed.
.ALLOC FI(PTISELDD) +
DA('datasetname') +
UNIT(SYSDA) +
SPACE(1,1) CYL RLSE +
LRECL(4092) +
BLKSIZE(4096) +
RECFM(V,B) +
MOD CATALOG
SELECT CREATOR, NAME, TYPE, TSNAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'authid1';
This places all the SQL, DB2 Return codes, Batch Processor Return codes , audit messages, error and warning messages along with the data returned
by the select statements in the PTISELDD DD dataset.
Online execution of SQL with Batch Processor
When running Batch Processor online a user can still utilize the DD PTISELDD by adding this code in front of any SQL being executed online.
This will preallocate and assign the DD before the SQL is executed.
.ALLOC FI(PTISELDD) DA('datasetname') +
NEW CATALOG UNIT(SYSDA) SPACE(01,01) CYL RLSE +
LRECL(4092) BLKSIZE(4096) RECFM(V,B)
SELECT * FROM SYSIBM.SYSTABLES WHERE NAME = 'xxxxx';
Related information
The ISQL product in the Value Pack:
In ISQL when specifying "Output to Dataset === > Y" it generates a .ALLOC statement for PTISELDD for the user in front of the SQL to be executed.
It can generate the whole JCL for you to run in batch or online.
Additional Information
Feedback
