SQL output, audit messages or return codes written to a Dataset

Document ID : KB000054467
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

When SQL is being executed with Batch Processor am I able to direct the SQL output 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:

  1. The SQL itself
  2. The return codes and audit messages from DB2
  3. The return codes and audit messages from Batch Processor
  4. The headings and data returned by DB2.

Solution:

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 the SQL SELECT statements along with the data and headings returned along with a count of records selected if it 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 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 in 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.
One 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.

This places all the SQL, DB2 Return codes, Batch Processor Return codes and audit messages along with the data returned by the select statements in the PTISELDD DD on SYSOUT.
This can then be downloaded using SYSVIEW or SDSF to a 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 = 'TBEMP';

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.