Building the SCCBSET JCL

Building your SCCBSET job involves creating JCL that includes the following statements:

  • A JOB statement
  • An EXEC statement
  • Data definition statements that specify the use of the following libraries or data sets:
    • SCCBSET and IBM DB2 load libraries
    • Input data sets
    • Output data sets

Related topics

Managing-object-sets-with-SCCBSET

SET-CURRENT-SQLID-for-SCCBSET

CREATE-OBJECTSET-syntax-and-option-descriptions

DELETE-OBJECTSET-syntax-and-option-descriptions

REPORT-OBJECTSET-syntax-and-option-descriptions

Specifying the JOB statement for SCCBSET

The JOB statement starts with a job name and includes standard JOB statement parameters, such as accounting information and a name that identifies the run.

The JOB statement can include the REGION parameter, which specifies the amount of virtual storage that the job requires. If you omit the REGION parameter from the JOB statement, you can include it in the EXEC statement.

BMC recommends that you specify REGION=0M, which automatically makes available the amount of virtual storage necessary to run the SCCBSET job. If REGION=0M is not allowed at your company, specify REGION=4M.

Specifying the EXEC statement for SCCBSET

The EXEC statement has the following format:

//stepname EXEC PGM=SCCBSET,
//             PARM='ssid',
//             REGION=0M

The variable ssid represents the IBM Db2 subsystem that uses the Db2 temporary tables. If you do not provide a subsystem ID, the program uses the subsystem ID indicated in the DSNHDECP module found in the STEPLIB or link list.

Important

The SSID parameter is positional and requires the comma even if you do not enter a specific subsystem ID. If the program cannot find the SSID that you specified or that is listed in the DSNHDECP module, it issues a BMC80583E INVALID PARAMETER FOR SSID message and sets the return code to 8.

Specifying the SCCBSET and Db2 load libraries with STEPLIB DD

The STEPLIB DD statement specifies the SCCBSET load library and IBM DB2 load libraries that you want SCCBSET to use. See the following example:

//STEPLIB   DD DISP=SHR,DSN=SCC.STEPLIB     
//                 DD DISP=SHR,DSN=SYS3.DEJM.DSNEXIT     
//                 DD DISP=SHR,DSN=CSGI.DB2V12M.DSNLOAD  

Specifying the SCCBSET data set DD statements

You specify a ddName (data definition name) for each data set that SCCBSET uses. In the JCL, you must specify all data sets that are not marked as optional.

Data definition

Description

SCCTRACE  DD SYSOUT=*
(optional)

Output for debugging traces

SCCERROR  DD SYSOUT=*

Output for options and run time errors

If compiler errors are detected and SCCERROR is not present in the JCL, the errors are printed in the JES log. You can allocate SCCERROR to SYSOUT or to a data set with a DCB of LRECL=121, RECFM=VB.

SCCPRINT  DD SYSOUT=*

Output for execution messages returned from SCCBSET

SCC also echoes the contents of the SCCIN data set in the SCCPRINT output. You can allocate SCCPRINT to SYSOUT or to a data set with a data control block (DCB) of LRECL=121, RECFM=VB.

To see an example of the output for the execution messages, Sample output for SCCPRINT.

SCCOBJS  DD SYSOUT=*

Output for object-set objects report

The data set can be allocated to SYSOUT or to a data set with a DCB of LRECL=121, RECFM=VB.

SCCSPEC  DD SYSOUT=*

Output for object-set specifications report

SCCHELP  DD SYSOUT=*
(optional)

Output is help about using program SCCBSET

SCCSQL  DD *
(optional)

Data definition for creating object sets with user-defined SQL

For more information, see SCCSQL DD statement and examplelater in this topic.

SCCOPTS  DD *
(optional)

Data definition for program options override

You can override one or more program options using the following SCCOPTS DD statement:

//SCCOPTS DD *
  PLANNAME NGTPLAN
  COLLECTIONID BMCSCC
  BINDQUALIFIER NGT121D
  PUBLICPLAN NO
  TEMPTABLESTOGROUP SYSDEFLT
/*

The following table describes each option and provides the default values:

Value

Description

PLANNAME

Name of the SCCBSET plan for repository and Db2 catalog access
The default plan name is NGTPLAN.

COLLECTIONID

Specifies a collection name on which the user has privileges to run the utility
The default collection ID is BMCSCC.

BINDQUALIFIER

Specifies a qualifier value that the utility uses for the dynamic bind process
The default bind qualifier is NGT131D.

PUBLICPLAN

Determines whether to grant EXECUTE authority to PUBLIC, which enables SCCBSET to dynamically bind or rebind the plan as needed
If NO (default), you must grant authority on the base plan to all specific users that run the utility.

TEMPTABLESTOGROUP

Specifies the storage group used by the utility to store temporary tables
The default storage group is SYSDEFLT.

SCCIN  DD *

Data definition for command syntax that contains one or more control statements

Attributes for this data set must be fixed length records, with a length of 80 (RECFM=F or FB, LRECL=80).

SCCSQL DD statement and example

You can use the SCCSQL data statement to create object sets with user-defined SQL.

Attributes for this data set must be fixed length records, with a length of 80 (RECFM=F or FB, LRECL=80). However, SQL syntax is limited to the first 72 bytes.

The following example shows several ways to set up dynamic SQL object sets with the SCCSQL DD statement:

//* FILE USED TO CREATE OBJECTSET INCLUDE SQL
//SCCSQL    DD *
  SELECT DBNAME, NAME FROM SYSIBM.SYSTABLESPACE
  WHERE DBNAME LIKE 'SCCDB%'
/*
//* OR PDS FILE USED TO CREATE OBJECTSET INCLUDE SQL
//SCCSQL    DD DISP=SHR,DSN=YOURPDS.FILE(YOURSQL)
//* OR SEQ FILE USED TO CREATE OBJECTSET INCLUDE SQL
//SCCSQL    DD DISP=SHR,DSN=YOURSEQ.FILE

To create an object set by partition, select from SYSIBM.SYSTABLEPART or SYSIBM.SYSINDEXPART.

To create an object set that is not by partition, select from SYSIBM.SYSTABLE or SYSIBM.SYSINDEX.

Observe the following rules for SCCSQL and SQL options:

  • Specify TS, IX, or SG as the first variable. SCCBSET  verifies that 'TS', 'IX', or 'SG' follows the SELECT statement.
  • Enter up to 16,000 characters.
  • You can only use one SQL statement.
  • You cannot use semicolons.
  • If not specified, the partition number is set to 0.
  • The IX name length and number of partitions follow the rules of the IBM Db2 version that you are using.

Ensure that the SQL statements exactly match the examples in the following table, and do not deviate except in the WHERE clause:

Object type

SQL statement

TS

SELECT 'TS', DBNAME, NAME FROM SYSIBM.SYSTABLESPACE WHERE DBNAME LIKE ‘QZU%’

SELECT 'TS', DBNAME, TSNAME, PARTITION FROM SYSIBM.SYSTABLEPART WHERE DBNAME='QZUDPT22' AND TSNAME='QZUS0122' AND PARTITION IN (4090, 4092, 4094, 4096)

IX

SELECT 'IX', CREATOR, NAME, CREATOR, NAME FROM SYSIBM.SYSINDEXES WHERE DBNAME = 'R92DB59'

SELECT 'IX', IXCREATOR, IXNAME, IXCREATOR, IXNAME, PARTITION FROM SYSIBM.SYSINDEXPART WHERE IXCREATOR='QZU' AND PARTITION > 100 AND PARTITION <= 200

SG

SELECT ‘SG', CREATOR, NAME, CREATOR, NAME FROM SYSIBM.SYSSTOGROUP WHERE NAME ='SG1234'

You can also add user-defined SQL statements with the INCLUDE SQL command statement.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*