JCL statements and parameters

Your ACKESQL JCL should include the following statements:

• JOB statement
• EXEC statement with the appropriate execution parameters
• STEPLIB statement that includes the following libraries, unless they are included in your system’s LINKLIST or in a JOBLIB statement:
  • load library or libraries that contain the files for the following products and technologies:
    • CHECK PLUS
    • BMCSORT
    • BMC Db2 Solution Common Code (SCC)
      • Libraries that contain any Db2 user exits (EDITPROCs, FIELDPROCs)
      • Db2 load library
    • DD statements for the input and output data sets

EXEC statement

Specify ACKESQL for the program name in the EXEC statement along with the following execution parameters:

• Db2 subsystem ID or group attachment name
• CHECK PLUS plan name
• Commit frequency value (optional)
• Restart record number (optional)
• Statement delimiter (optional)
• Reset/lock value (optional)
• SPUFI parameter (optional)

The following syntax shows the format of the EXEC statement:

The ACKESQL parameters are positional. If you do not specify a value for an optional parameter (thus allowing the default value), you must substitute a comma for that parameter if additional parameters follow. The comma indicates that a parameter was omitted.

Db2 subsystem identifier (SSID)

This parameter is the 4-character Db2 subsystem ID that indicates where the data resides.

The ACKESQL program supports the Db2 group attachment name capability. The program determines the actual Db2 SSID from within that group to use for the job.

Plan name

This parameter is the CHECK PLUS plan name specified during installation.

Commit frequency

This integer value indicates the frequency with which the program performs a commit. For example, a commit frequency of 50 indicates that you want to perform a commit for every 50 statements processed. The default value is 100.

If you also specify LOCK or RESETLOCK, the program ignores any value that you specify for the commit frequency and performs a commit at the end of delete processing.

Restart record number

When an error occurs and you want to restart the program, this parameter indicates the record number at which you want to resume the deletion process. The default value is 1.

Use the CHECK PLUS messages that report commit activity, the number of statements executed, and the number of the last record executed to determine the record number following the last commit.

Statement delimiter

This parameter indicates the character that delimits the SQL statements in the file that you are processing. The default value is ';'.

The discard files that CHECK PLUS generates use a semicolon as the statement delimiter. However, you can use the ACKESQL program to execute any file that contains SQL statements other than SELECT statements. If this file uses a delimiter other than a semicolon, include that delimiter in single quotation marks in the ACKESQL program’s EXEC statement.

 Reset/lock parameter

This parameter tells the program to reset CHKP status, lock the affected tables, or do both. For ACKESQL to use this parameter, the following values must have been in effect during the CHECK PLUS job:

• DISCARDFMT SQLDEL on the CHECK DATA command
• DSER=YES in the installation options module, or DISCARDSERIALIZE YES on the CHECK DATA command

Use the following table as a guide in determining whether to specify the RESETLOCK, RESET, or LOCK parameter value. The sections that follow describe these values in more detail.

^a We recommend using the RESETLOCK parameter to minimize the amount of time that the table is available between resetting CHKP status and requesting the lock.

RESET

This value tells the program to reset the CHKP status of the affected objects before executing the DELETE statements. In most cases, if the ACKESQL job fails, CHKP status is reset to any partition for which CHKP status was reset at the beginning of the job.

LOCK

This value tells the program to lock the affected table. Note the following considerations when you specify this value:

• You must reset CHKP status before running the ACKESQL job.
• The program ignores the commit frequency parameter and performs a commit at the end of delete processing.
• If the ACKESQL job fails for any reason, the locks that are in effect at the time are released, and all uncommitted deletes are rolled back. 

RESETLOCK

This value, which is the default when the appropriate CHECK PLUS options are in effect, performs the operations of both the RESET and LOCK values. The lock is requested immediately following the status reset operation. Note the following considerations when you specify this value:

• The program ignores the commit frequency parameter and performs a commit at the end of delete processing.
• If the ACKESQL job fails for any reason, the locks that are in effect at the time are released, and all deletes are rolled back.

SPUFI parameter

This parameter tells the ACKESQL program that you specified DISCARDFMT SPUFI on your CHECK PLUS job. When you specify this parameter, the program parses any long DELETE statements (longer than 72 bytes) correctly for SPUFI processing.

To use this parameter, specify the value SPUFI in this position.

Input and output DD statements

The following sections describe the DD statements that are required for the program input and output.

SYSIN

CHECK PLUS always requires the SYSIN data set. This input data set either contains the SQL DELETE statements from the SYSDISC discard data set or references the data set. The data set’s attributes must be specified as fixed length and blocked records (RECFM=FB).

SYSPRINT

CHECK PLUS always requires the SYSPRINT data set. This output data set contains the messages that detail the delete activity.

Example execution

This example illustrates the discard data set in SQLDEL format:

This example shows an example of the JCL for using the SQL batch execution program (ACKESQL) to execute the SQL DELETE statement in the example discard data set. You can find a copy of the JCL for this example in member ACKEXSQL in the HLQ.LLQCNTL installation data set (where HLQ is the high-level qualifier specified during installation, and LLQ is the low-level qualifier or prefix set during installation).

Example JCL for SQL batch execution

This shows the output from running the JCL. The options to enable discard serialization were set in the CHECK PLUS job and, because the EXEC statement did not specify a reset/lock parameter, ACKESQL used the RESETLOCK default.

ACKESQL output