Example property values for all SQL Assurance steps
The following figure represents a sample configuration YAML file for all steps in SQL Assurance:
###------------------------------------------------------------------------------------------------------------------------------------
### Config_SQL_Assurance_ALL_Steps.yml (Do not modify the file. Use this as reference only)
###------------------------------------------------------------------------------------------------------------------------------------
# Each line represents a property (key-value pair).
# The property key (left side of colon) is a fixed keyword and should not be altered.
# The property value (right side of colon) is for user input.
# The alignment or spacing for the property key and value should not be changed. It may result in an unexpected error. The YAML file only works with proper alignment.
# For property values with multiple lines, specify the pipe | character after the property key. On the following line, indent the lines with values.
###------------------------------------------------------------------------------------------------------------------------------------
version: 2.0.0 #Version of the configuration file. DO NOT alter the version value unless config file version is upgraded.
authentication: #Step used in the pipeline/workflow for executing this configuration input. DO NOT alter the name.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
hostName: db2b.bmc.com #Mainframe host name in URL format or IP address in IPv4 format.
port: 32208 #Communication medium port used on mainframe TCP/IP network.
authenticationType: userBased #Communication with mainframe using either TSO credential or client certificate. Valid values are: userBased(default) or certBased.
user: USER_ID_AUTH #Mainframe TSO logon ID input as specific name or variable name, like shown here: USER_ID_AUTH which will be replaced by variable definition from pipeline/workflow.
password: USER_PASS_AUTH #Mainframe TSO logon password. For security purposes, variable name should be used, like shown here: USER_PASS_AUTH which will be replaced by variable definition from pipeline/workflow.
certificatePath: CERT_PATH_AUTH #Client certificate path input as specific name or variable name, like shown here: CERT_PATH_AUTH which will be replaced by variable definition from pipeline/workflow.
certificatePassword: CERT_PASS_AUTH #Client certificate password. For security purposes, variable name should be used, like shown here: CERT_PASS_AUTH which will be replaced by variable definition from pipeline/workflow.
symbolicPropertiesFilePath: AMI_DevOps.properties #File path or file name (in case file is present in root directory) for the BMC AMI DevOps plug-in variable file. Default file name is AMI_DevOps.properties.
debugModeAllSteps: false #Valid values are: true or false(default). If set to true, it will override the debugMode property to true in all config steps.
executionIdentifier: PipelineName=PIPELINENAME_VALUE,RunNumber=RUNNUMBER_VALUE,RunBy=RUNBY_VALUE,RunMode=RUNMODE_VALUE #Identifier keys used to trace the executed pipeline/workflow from the submitted JCL at the mainframe. Typically input as variable names and replaced by variable definition from pipeline/workflow. Do NOT alter unless directed by BMC Support.
analyzeStaticSql: #Step represents SQL Assurance - Static SQL. DO NOT alter the name.
- stepName: static_sql_plan #Step details for Static SQL-Plan, user defined step name used in the pipeline/workflow for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
generateJclOnly: false #Valid values are: true or false(default). If set to true, will generate the JCL only without executing the step.
ssid: DEJM #Db2 subsystem ID where the Static SQL statements reside.
violationResponse: warn #Valid values are: warn or fail. If set to warn, it prevents the application step from failing if a violation is encountered. If set to fail, it causes a rule violation to fail the application step.
explainMethod: static #Valid values are: static or dynamic. If set to static, it causes Explain engine to search an existing PLAN_TABLE. If set to dynamic, it dynamically explains every SQL Statement encountered.
objectType: plan #Valid values are: plan or package. Enter plan as the object type for the Explain process.
objectName: <plan name> #Plan name for the Explain process. Leave the field blank if the objectListFilePath will be used.
useObjectListFile: false #Valid values are: true or false(default). If set to true, it performs the Explain process using field objectListFilePath instead of objectName. Enter the objectType prior to the useObjectListFile option.
objectListContinueOnError: false #Valid values are: true or false(default). If set to true, it processes records in the Object List up to the 100 record limit. This option will ignore the invalid records and records beyond the 100 record limit.
objectListFilePath: #If useObjectListFile is set to true, enter the path (directory and/or the file name) of the object list file. The object list file includes a list of all Plans/Packages that SQL Assurance will analyze. The file should be a text file with .txt extension.
jobCard: | #JOB statement to run the JCL. Typically, multiple lines are required.
//COBSSQL JOB ($[acctno]),'STATIC-SQL',
// CLASS=A,MSGLEVEL=(1,1)
//*
sqlExplorerInputStream: | #Contains the DD names, parameters and option override values that the Explain process uses.
//PSSMAIN.SYSIN DD *
--NEWOBJ
OBJECT=PLAN
NAME="${objectName}"
RULES="$[rules]"
RULEDSN="$[ruledsn]"
PLANTBL="${explainMethod}"
CURRENCY="$[currency]"
MSGLEVEL=ALL
--ENDOBJ
jcl: | #The jcl field contains the statements that SQL Assurance needs to analyze the provided objects.
${jobCard}
//*
// JCLLIB ORDER=($[proclib])
//*
//JOBLIB DD DSN=$[joblib1],DISP=SHR
// DD DSN=$[joblib2],DISP=SHR
// DD DSN=$[dsnexit],DISP=SHR
// DD DSN=$[dsnload],DISP=SHR
//***************************************************
//*
// SET RTEHLQ=$[rtehlq]
// SET USRHLQ=$[usrhlq]
//*
// EXPORT SYMLIST=(*)
//*
// SET SSID=${ssid}
// SET PSSPLAN=$[pssplan]
// SET GUDPLAN=$[gudplan]
// SET GENDEBUG='${debugMode}'
// SET LOGMODE='${violationResponse}'
// SET RULSPACE='SPACE=(CYL,(10,10)),'
// SET VTIBSZ=20
//*-----------------------------------------------------------------
//*
//SQLXPLOR EXEC COBPRPSS
${sqlExplorerInputStream}
//RULESOUT.SYSUT2 DD &GENDEBUG
//*-----------------------------------------------------------------
//*
//IFPSS IF (SQLXPLOR.PSSMAIN.RC EQ 4) THEN
//COBLOGER EXEC COBPRLOG
//LOGGER.AS$INPUT DD DSN=&&RULESOUT,
// DISP=(OLD,DELETE)
//IFPSSEND ENDIF
//*
maxJobWaitTime: 15 #Valid values are: 1-60 and default is 15. Maximum length of time in minutes that the application waits for job completion from the mainframe.
- stepName: static_sql_package #Step details for Static SQL-Package, user defined step name used in the pipeline/workflow for executing this configuration input. This sample step uses the Object List File to select package details.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
generateJclOnly: false #Valid values are: true or false(default). If set to true, will generate the JCL only without executing the step.
ssid: DEJM #Db2 subsystem ID where the Static SQL statements reside.
violationResponse: fail #Valid values are: warn or fail. If set to warn, it prevents the application step from failing if a violation is encountered. If set to fail, it causes a rule violation to fail the application step.
explainMethod: dynamic #Valid values are: static or dynamic. If set to static, it causes Explain engine to search an existing PLAN_TABLE. If set to dynamic, it dynamically explains every SQL Statement encountered.
objectType: package #Valid values are: plan or package. Enter package as the object type for the Explain process.
objectName: #Package name for the Explain process. Leave the field blank if the objectListFilePath will be used.
collectionIdPackage: #Package collection ID for the Explain process. Leave the field blank if the objectListFilePath will be used.
versionPackage: #Package version for the Explain process. Leave the field blank if the objectListFilePath will be used.
useObjectListFile: true #Valid values are: true or false(default). If set to true, it performs the Explain process using field objectListFilePath instead of objectName. Enter the objectType prior to the useObjectListFile option.
objectListContinueOnError: false #Valid values are: true or false(default). If set to true, it processes records in the Object List up to the 100 record limit. This option will ignore the invalid records and records beyond the 100 record limit.
objectListFilePath: <file name> #If useObjectListFile is set to true, enter the path (directory and/or the file name) of the object list file. The object list file includes a list of all Plans/Packages that SQL Assurance will analyze. The file should be a text file with .txt extension.
jobCard: | #JOB statement to run the JCL. Typically, multiple lines are required.
//COBSSQL JOB ($[acctno]),'STATIC-SQL',
// CLASS=A,MSGLEVEL=(1,1)
//*
sqlExplorerInputStream: | #Contains the DD names, parameters and option override values that the Explain process uses.
//PSSMAIN.SYSIN DD *
--NEWOBJ
OBJECT=PACKAGE
NAME="${objectName}"
COLLID="${collectionIdPackage}"
VERSION="${versionPackage}"
RULES="$[rules]"
RULEDSN="$[ruledsn]"
PLANTBL="${explainMethod}"
CURRENCY="$[currency]"
MSGLEVEL=ALL
--ENDOBJ
jcl: | #The jcl field contains the statements that SQL Assurance needs to analyze the provided objects.
${jobCard}
//*
// JCLLIB ORDER=($[proclib])
//*
//JOBLIB DD DSN=$[joblib1],DISP=SHR
// DD DSN=$[joblib2],DISP=SHR
// DD DSN=$[dsnexit],DISP=SHR
// DD DSN=$[dsnload],DISP=SHR
//***************************************************
//*
// SET RTEHLQ=$[rtehlq]
// SET USRHLQ=$[usrhlq]
//*
// EXPORT SYMLIST=(*)
//*
// SET SSID=${ssid}
// SET PSSPLAN=$[pssplan]
// SET GUDPLAN=$[gudplan]
// SET GENDEBUG='${debugMode}'
// SET LOGMODE='${violationResponse}'
// SET RULSPACE='SPACE=(CYL,(10,10)),'
// SET VTIBSZ=20
//*-----------------------------------------------------------------
//*
//SQLXPLOR EXEC COBPRPSS
${sqlExplorerInputStream}
//RULESOUT.SYSUT2 DD &GENDEBUG
//*-----------------------------------------------------------------
//*
//IFPSS IF (SQLXPLOR.PSSMAIN.RC EQ 4) THEN
//COBLOGER EXEC COBPRLOG
//LOGGER.AS$INPUT DD DSN=&&RULESOUT,
// DISP=(OLD,DELETE)
//IFPSSEND ENDIF
//*
maxJobWaitTime: 15 #Valid values are: 1-60 and default is 15. Maximum length of time in minutes that the application waits for job completion from the mainframe.
analyzeDynamicSql: #Step represents SQL Assurance - Dynamic SQL. DO NOT alter the name.
- stepName: dynamic_sql #Step details for Dynamic SQL, user defined step name used in the pipeline/workflow for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
generateJclOnly: false #Valid values are: true or false(default). If set to true, will generate the JCL only without executing the step.
ssid: DEJM #Db2 subsystem ID where the SQL statements from the dynamic statement cache will be analyzed.
violationResponse: fail #Valid values are: warn or fail. If set to warn, it prevents the application step from failing if a violation is encountered. If set to fail, it causes a rule violation to fail the application step.
dsnStatementCacheTableCreator: #Table creator name for the DSN_STATEMENT_CACHE_TABLE. This is an optional field. If left BLANK, TSO user ID running the pipeline/workflow is used by default.
clearDsnStatementCacheTable: false #Valid values are: true or false(default). If set to true, it removes all rows from the DSN_STATEMENT_CACHE_TABLE.
extractSqlFromDynamicStatementCache: true #Valid values are: true (default) or false. If set to true, it will copy the SQL statements from the Dynamic Statement Cache to the DSN_STATEMENT_CACHE_TABLE. This function will occur before the SQL Assurance process.
failStepIfWhereClauseReturnsNoResultSet: true #Valid values are: true (default) or false. If set to true, it will fail the build step if no data is returned from the DSN_STATEMENT_CACHE_TABLE query using the defined WHERE clause.
whereClauseStatement: | #WHERE clause predicates for querying against the DSN_STATEMENT_CACHE_TABLE. The entered value must be valid SQL syntax.
PRIMAUTH LIKE 'NAME%' AND CACHED_TS > CURRENT TIMESTAMP - 1 HOUR
jobCard: | #JOB statement to run the JCL. Typically, multiple lines are required.
//COBDSQL JOB ($[acctno]),'DYNAMIC-SQL',
// CLASS=A,MSGLEVEL=(1,1)
//*
extractorInputStream: | #Contains the DD names and parameter values that the Extractor and Explain processes use.
//EXTRACT.PARMIN DD *,SYMBOLS=JCLONLY
SSID=&SSID,
PLAN=&GUDPLAN,
DSNTRUNC=&DSNTRUNC,
DSNEXPL=&DSNEXPL,
DSNQUAL=&DSNQUAL
//EXTRACT.SE$OPTS DD *
${whereClauseStatement}
//EXTRACT.PSS$OPTS DD *
RULES="$[rules]"
RULEDSN="$[ruledsn]"
CURRENCY="$[currency]"
MSGLEVEL=ALL
jcl: | #The jcl field contains the statements that SQL Assurance needs to analyze the provided objects.
${jobCard}
//*
// JCLLIB ORDER=($[proclib])
//*
//JOBLIB DD DSN=$[joblib1],DISP=SHR
// DD DSN=$[joblib2],DISP=SHR
// DD DSN=$[dsnexit],DISP=SHR
// DD DSN=$[dsnload],DISP=SHR
//*
// SET RTEHLQ=$[rtehlq]
// SET USRHLQ=$[usrhlq]
//*
// EXPORT SYMLIST=(*)
//*
// SET SSID=${ssid}
// SET PSSPLAN=$[pssplan]
// SET GUDPLAN=$[gudplan]
// SET DSNQUAL=${dsnStatementCacheTableCreator}
// SET DSNTRUNC=${clearDsnStatementCacheTable}
// SET DSNEXPL=${extractSqlFromDynamicStatementCache}
// SET GENDEBUG='${debugMode}'
// SET LOGMODE='${violationResponse}'
// SET RULSPACE='SPACE=(CYL,(50,10)),'
// SET VTIBSZ=20
//*-----------------------------------------------------------------
//*
//XTRACT EXEC COBPRXTR,PARMDD.EXTRACT=PARMIN
${extractorInputStream}
//XTROUT.SYSUT2 DD &GENDEBUG
//*
//*-----------------------------------------------------------------
//*
//IFXTR IF (XTRACT.EXTRACT.RC LT 4) THEN
//SQLXPLOR EXEC COBPRPSS
//PSSMAIN.SYSIN DD DISP=(OLD,DELETE),DSN=&&XTROUT
//RULESOUT.SYSUT2 DD &GENDEBUG
//*-----------------------------------------------------------------
//*
//IFPSS IF (SQLXPLOR.PSSMAIN.RC EQ 4) THEN
//COBLOGER EXEC COBPRLOG
//LOGGER.AS$INPUT DD DSN=&&RULESOUT,
// DISP=(OLD,DELETE)
//IFPSSEND ENDIF
//IFXTREND ENDIF
maxJobWaitTime: 15 #Valid values are: 1-60 and default is 15. Maximum length of time in minutes that the application waits for job completion from the mainframe.
###--------------------------------------------------------------------------------------
### Define all the steps of the application Universal Connector BMC AMI DevOps Common
###--------------------------------------------------------------------------------------
fileUpload: #Step represents File Transmission to mainframe (upload). DO NOT alter the name.
- stepName: local_to_mainframe #User defined step name, used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
sourceDirectory: . #Path in agent workspace from which a file is transferred. Relative path is used in this example with a period (.) which is for the agent workspace path.
sourceFileName: sample.txt #File on the client that is transferred. File must be a plain text file.
destinationPds: SAMPLE.PDS #Partitioned data set (PDS) on the mainframe from which a member is received.
destinationMember: SAMPLE #Member on the mainframe that is received.
fileDownload: #Step represents File Transmission from mainframe (download). DO NOT alter the name.
- stepName: mainframe_to_local #User defined step name used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
sourcePds: SAMPLE.PDS #Partitioned data set (PDS) on the mainframe where the member is transferred.
sourceMember: SAMPLE #Member on the mainframe that is transferred.
destinationDirectory: . #Directory in the agent workspace that is received. Relative path is used in this example with a period (.) which is for the agent workspace path.
destinationFileName: sample.txt #File in the agent workspace path that is received.
jclExecutionUsingPDSMember: #Step represents JCL Execution Using mainframe PDS Member. DO NOT alter the name.
- stepName: jclExecutionPDS #User defined step name, used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
jclPds: SAMPLE.PDS.JCL #Partitioned data set (PDS) on the mainframe where the JCL member, to be executed, resides.
jclMemberName: SAMPLE #JCL member on the mainframe to be executed.
acceptableReturnCode: 0004 #Valid values are: 0000-9999 and default is 0004. Maximum return code value that communicates that the submitted job was successful.
maxJobWaitTime: 2 #Valid values are: 1-60 and default is 2. Maximum length of time in minutes that the application waits for job completion from the mainframe.
jclExecutionUsingFile: #Step represents JCL Execution Using local File. DO NOT alter the name.
- stepName: jclExecutionFile #User defined step name, used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
jclDirectory: . #JCL directory in agent workspace where the JCL file, to be executed, resides. Relative path is used in this example with a period (.) which is for the agent workspace path.
jclFileName: JclFile.txt #JCL file in the agent workspace path to be executed.
acceptableReturnCode: 0004 #Valid values are: 0000-9999 and default is 0004. Maximum return code value that communicates that the submitted job was successful.
maxJobWaitTime: 2 #Valid values are: 1-60 and default is 2. Maximum length of time in minutes that the application waits for job completion from the mainframe.
jclExecutionUsingFreeForm: #Step represents JCL Execution Using Freeform JCL. DO NOT alter the name.
- stepName: jclExecutionFreeForm #User defined step name, used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
freeFormJcl: | #JCL statements used to execute JCL job. Value will be multiple lines.
//SAMPLEST JOB (5213),'SORT ',
// MSGCLASS=X,NOTIFY=&SYSUID,CLASS=A
//*
//* SORT 1ST 5 CHARS IN ASCENDING ORDER
//*
//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
LINE5
LINE2
LINE1
LINE4
LINE3
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,5,CH,A)
/*
//
acceptableReturnCode: 0004 #Valid values are: 0000-9999 and default is 0004. Maximum return code value that communicates that the submitted job was successful.
maxJobWaitTime: 2 #Valid values are: 1-60 and default is 2. Maximum length of time in minutes that the application waits for job completion from the mainframe.
### Config_SQL_Assurance_ALL_Steps.yml (Do not modify the file. Use this as reference only)
###------------------------------------------------------------------------------------------------------------------------------------
# Each line represents a property (key-value pair).
# The property key (left side of colon) is a fixed keyword and should not be altered.
# The property value (right side of colon) is for user input.
# The alignment or spacing for the property key and value should not be changed. It may result in an unexpected error. The YAML file only works with proper alignment.
# For property values with multiple lines, specify the pipe | character after the property key. On the following line, indent the lines with values.
###------------------------------------------------------------------------------------------------------------------------------------
version: 2.0.0 #Version of the configuration file. DO NOT alter the version value unless config file version is upgraded.
authentication: #Step used in the pipeline/workflow for executing this configuration input. DO NOT alter the name.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
hostName: db2b.bmc.com #Mainframe host name in URL format or IP address in IPv4 format.
port: 32208 #Communication medium port used on mainframe TCP/IP network.
authenticationType: userBased #Communication with mainframe using either TSO credential or client certificate. Valid values are: userBased(default) or certBased.
user: USER_ID_AUTH #Mainframe TSO logon ID input as specific name or variable name, like shown here: USER_ID_AUTH which will be replaced by variable definition from pipeline/workflow.
password: USER_PASS_AUTH #Mainframe TSO logon password. For security purposes, variable name should be used, like shown here: USER_PASS_AUTH which will be replaced by variable definition from pipeline/workflow.
certificatePath: CERT_PATH_AUTH #Client certificate path input as specific name or variable name, like shown here: CERT_PATH_AUTH which will be replaced by variable definition from pipeline/workflow.
certificatePassword: CERT_PASS_AUTH #Client certificate password. For security purposes, variable name should be used, like shown here: CERT_PASS_AUTH which will be replaced by variable definition from pipeline/workflow.
symbolicPropertiesFilePath: AMI_DevOps.properties #File path or file name (in case file is present in root directory) for the BMC AMI DevOps plug-in variable file. Default file name is AMI_DevOps.properties.
debugModeAllSteps: false #Valid values are: true or false(default). If set to true, it will override the debugMode property to true in all config steps.
executionIdentifier: PipelineName=PIPELINENAME_VALUE,RunNumber=RUNNUMBER_VALUE,RunBy=RUNBY_VALUE,RunMode=RUNMODE_VALUE #Identifier keys used to trace the executed pipeline/workflow from the submitted JCL at the mainframe. Typically input as variable names and replaced by variable definition from pipeline/workflow. Do NOT alter unless directed by BMC Support.
analyzeStaticSql: #Step represents SQL Assurance - Static SQL. DO NOT alter the name.
- stepName: static_sql_plan #Step details for Static SQL-Plan, user defined step name used in the pipeline/workflow for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
generateJclOnly: false #Valid values are: true or false(default). If set to true, will generate the JCL only without executing the step.
ssid: DEJM #Db2 subsystem ID where the Static SQL statements reside.
violationResponse: warn #Valid values are: warn or fail. If set to warn, it prevents the application step from failing if a violation is encountered. If set to fail, it causes a rule violation to fail the application step.
explainMethod: static #Valid values are: static or dynamic. If set to static, it causes Explain engine to search an existing PLAN_TABLE. If set to dynamic, it dynamically explains every SQL Statement encountered.
objectType: plan #Valid values are: plan or package. Enter plan as the object type for the Explain process.
objectName: <plan name> #Plan name for the Explain process. Leave the field blank if the objectListFilePath will be used.
useObjectListFile: false #Valid values are: true or false(default). If set to true, it performs the Explain process using field objectListFilePath instead of objectName. Enter the objectType prior to the useObjectListFile option.
objectListContinueOnError: false #Valid values are: true or false(default). If set to true, it processes records in the Object List up to the 100 record limit. This option will ignore the invalid records and records beyond the 100 record limit.
objectListFilePath: #If useObjectListFile is set to true, enter the path (directory and/or the file name) of the object list file. The object list file includes a list of all Plans/Packages that SQL Assurance will analyze. The file should be a text file with .txt extension.
jobCard: | #JOB statement to run the JCL. Typically, multiple lines are required.
//COBSSQL JOB ($[acctno]),'STATIC-SQL',
// CLASS=A,MSGLEVEL=(1,1)
//*
sqlExplorerInputStream: | #Contains the DD names, parameters and option override values that the Explain process uses.
//PSSMAIN.SYSIN DD *
--NEWOBJ
OBJECT=PLAN
NAME="${objectName}"
RULES="$[rules]"
RULEDSN="$[ruledsn]"
PLANTBL="${explainMethod}"
CURRENCY="$[currency]"
MSGLEVEL=ALL
--ENDOBJ
jcl: | #The jcl field contains the statements that SQL Assurance needs to analyze the provided objects.
${jobCard}
//*
// JCLLIB ORDER=($[proclib])
//*
//JOBLIB DD DSN=$[joblib1],DISP=SHR
// DD DSN=$[joblib2],DISP=SHR
// DD DSN=$[dsnexit],DISP=SHR
// DD DSN=$[dsnload],DISP=SHR
//***************************************************
//*
// SET RTEHLQ=$[rtehlq]
// SET USRHLQ=$[usrhlq]
//*
// EXPORT SYMLIST=(*)
//*
// SET SSID=${ssid}
// SET PSSPLAN=$[pssplan]
// SET GUDPLAN=$[gudplan]
// SET GENDEBUG='${debugMode}'
// SET LOGMODE='${violationResponse}'
// SET RULSPACE='SPACE=(CYL,(10,10)),'
// SET VTIBSZ=20
//*-----------------------------------------------------------------
//*
//SQLXPLOR EXEC COBPRPSS
${sqlExplorerInputStream}
//RULESOUT.SYSUT2 DD &GENDEBUG
//*-----------------------------------------------------------------
//*
//IFPSS IF (SQLXPLOR.PSSMAIN.RC EQ 4) THEN
//COBLOGER EXEC COBPRLOG
//LOGGER.AS$INPUT DD DSN=&&RULESOUT,
// DISP=(OLD,DELETE)
//IFPSSEND ENDIF
//*
maxJobWaitTime: 15 #Valid values are: 1-60 and default is 15. Maximum length of time in minutes that the application waits for job completion from the mainframe.
- stepName: static_sql_package #Step details for Static SQL-Package, user defined step name used in the pipeline/workflow for executing this configuration input. This sample step uses the Object List File to select package details.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
generateJclOnly: false #Valid values are: true or false(default). If set to true, will generate the JCL only without executing the step.
ssid: DEJM #Db2 subsystem ID where the Static SQL statements reside.
violationResponse: fail #Valid values are: warn or fail. If set to warn, it prevents the application step from failing if a violation is encountered. If set to fail, it causes a rule violation to fail the application step.
explainMethod: dynamic #Valid values are: static or dynamic. If set to static, it causes Explain engine to search an existing PLAN_TABLE. If set to dynamic, it dynamically explains every SQL Statement encountered.
objectType: package #Valid values are: plan or package. Enter package as the object type for the Explain process.
objectName: #Package name for the Explain process. Leave the field blank if the objectListFilePath will be used.
collectionIdPackage: #Package collection ID for the Explain process. Leave the field blank if the objectListFilePath will be used.
versionPackage: #Package version for the Explain process. Leave the field blank if the objectListFilePath will be used.
useObjectListFile: true #Valid values are: true or false(default). If set to true, it performs the Explain process using field objectListFilePath instead of objectName. Enter the objectType prior to the useObjectListFile option.
objectListContinueOnError: false #Valid values are: true or false(default). If set to true, it processes records in the Object List up to the 100 record limit. This option will ignore the invalid records and records beyond the 100 record limit.
objectListFilePath: <file name> #If useObjectListFile is set to true, enter the path (directory and/or the file name) of the object list file. The object list file includes a list of all Plans/Packages that SQL Assurance will analyze. The file should be a text file with .txt extension.
jobCard: | #JOB statement to run the JCL. Typically, multiple lines are required.
//COBSSQL JOB ($[acctno]),'STATIC-SQL',
// CLASS=A,MSGLEVEL=(1,1)
//*
sqlExplorerInputStream: | #Contains the DD names, parameters and option override values that the Explain process uses.
//PSSMAIN.SYSIN DD *
--NEWOBJ
OBJECT=PACKAGE
NAME="${objectName}"
COLLID="${collectionIdPackage}"
VERSION="${versionPackage}"
RULES="$[rules]"
RULEDSN="$[ruledsn]"
PLANTBL="${explainMethod}"
CURRENCY="$[currency]"
MSGLEVEL=ALL
--ENDOBJ
jcl: | #The jcl field contains the statements that SQL Assurance needs to analyze the provided objects.
${jobCard}
//*
// JCLLIB ORDER=($[proclib])
//*
//JOBLIB DD DSN=$[joblib1],DISP=SHR
// DD DSN=$[joblib2],DISP=SHR
// DD DSN=$[dsnexit],DISP=SHR
// DD DSN=$[dsnload],DISP=SHR
//***************************************************
//*
// SET RTEHLQ=$[rtehlq]
// SET USRHLQ=$[usrhlq]
//*
// EXPORT SYMLIST=(*)
//*
// SET SSID=${ssid}
// SET PSSPLAN=$[pssplan]
// SET GUDPLAN=$[gudplan]
// SET GENDEBUG='${debugMode}'
// SET LOGMODE='${violationResponse}'
// SET RULSPACE='SPACE=(CYL,(10,10)),'
// SET VTIBSZ=20
//*-----------------------------------------------------------------
//*
//SQLXPLOR EXEC COBPRPSS
${sqlExplorerInputStream}
//RULESOUT.SYSUT2 DD &GENDEBUG
//*-----------------------------------------------------------------
//*
//IFPSS IF (SQLXPLOR.PSSMAIN.RC EQ 4) THEN
//COBLOGER EXEC COBPRLOG
//LOGGER.AS$INPUT DD DSN=&&RULESOUT,
// DISP=(OLD,DELETE)
//IFPSSEND ENDIF
//*
maxJobWaitTime: 15 #Valid values are: 1-60 and default is 15. Maximum length of time in minutes that the application waits for job completion from the mainframe.
analyzeDynamicSql: #Step represents SQL Assurance - Dynamic SQL. DO NOT alter the name.
- stepName: dynamic_sql #Step details for Dynamic SQL, user defined step name used in the pipeline/workflow for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
generateJclOnly: false #Valid values are: true or false(default). If set to true, will generate the JCL only without executing the step.
ssid: DEJM #Db2 subsystem ID where the SQL statements from the dynamic statement cache will be analyzed.
violationResponse: fail #Valid values are: warn or fail. If set to warn, it prevents the application step from failing if a violation is encountered. If set to fail, it causes a rule violation to fail the application step.
dsnStatementCacheTableCreator: #Table creator name for the DSN_STATEMENT_CACHE_TABLE. This is an optional field. If left BLANK, TSO user ID running the pipeline/workflow is used by default.
clearDsnStatementCacheTable: false #Valid values are: true or false(default). If set to true, it removes all rows from the DSN_STATEMENT_CACHE_TABLE.
extractSqlFromDynamicStatementCache: true #Valid values are: true (default) or false. If set to true, it will copy the SQL statements from the Dynamic Statement Cache to the DSN_STATEMENT_CACHE_TABLE. This function will occur before the SQL Assurance process.
failStepIfWhereClauseReturnsNoResultSet: true #Valid values are: true (default) or false. If set to true, it will fail the build step if no data is returned from the DSN_STATEMENT_CACHE_TABLE query using the defined WHERE clause.
whereClauseStatement: | #WHERE clause predicates for querying against the DSN_STATEMENT_CACHE_TABLE. The entered value must be valid SQL syntax.
PRIMAUTH LIKE 'NAME%' AND CACHED_TS > CURRENT TIMESTAMP - 1 HOUR
jobCard: | #JOB statement to run the JCL. Typically, multiple lines are required.
//COBDSQL JOB ($[acctno]),'DYNAMIC-SQL',
// CLASS=A,MSGLEVEL=(1,1)
//*
extractorInputStream: | #Contains the DD names and parameter values that the Extractor and Explain processes use.
//EXTRACT.PARMIN DD *,SYMBOLS=JCLONLY
SSID=&SSID,
PLAN=&GUDPLAN,
DSNTRUNC=&DSNTRUNC,
DSNEXPL=&DSNEXPL,
DSNQUAL=&DSNQUAL
//EXTRACT.SE$OPTS DD *
${whereClauseStatement}
//EXTRACT.PSS$OPTS DD *
RULES="$[rules]"
RULEDSN="$[ruledsn]"
CURRENCY="$[currency]"
MSGLEVEL=ALL
jcl: | #The jcl field contains the statements that SQL Assurance needs to analyze the provided objects.
${jobCard}
//*
// JCLLIB ORDER=($[proclib])
//*
//JOBLIB DD DSN=$[joblib1],DISP=SHR
// DD DSN=$[joblib2],DISP=SHR
// DD DSN=$[dsnexit],DISP=SHR
// DD DSN=$[dsnload],DISP=SHR
//*
// SET RTEHLQ=$[rtehlq]
// SET USRHLQ=$[usrhlq]
//*
// EXPORT SYMLIST=(*)
//*
// SET SSID=${ssid}
// SET PSSPLAN=$[pssplan]
// SET GUDPLAN=$[gudplan]
// SET DSNQUAL=${dsnStatementCacheTableCreator}
// SET DSNTRUNC=${clearDsnStatementCacheTable}
// SET DSNEXPL=${extractSqlFromDynamicStatementCache}
// SET GENDEBUG='${debugMode}'
// SET LOGMODE='${violationResponse}'
// SET RULSPACE='SPACE=(CYL,(50,10)),'
// SET VTIBSZ=20
//*-----------------------------------------------------------------
//*
//XTRACT EXEC COBPRXTR,PARMDD.EXTRACT=PARMIN
${extractorInputStream}
//XTROUT.SYSUT2 DD &GENDEBUG
//*
//*-----------------------------------------------------------------
//*
//IFXTR IF (XTRACT.EXTRACT.RC LT 4) THEN
//SQLXPLOR EXEC COBPRPSS
//PSSMAIN.SYSIN DD DISP=(OLD,DELETE),DSN=&&XTROUT
//RULESOUT.SYSUT2 DD &GENDEBUG
//*-----------------------------------------------------------------
//*
//IFPSS IF (SQLXPLOR.PSSMAIN.RC EQ 4) THEN
//COBLOGER EXEC COBPRLOG
//LOGGER.AS$INPUT DD DSN=&&RULESOUT,
// DISP=(OLD,DELETE)
//IFPSSEND ENDIF
//IFXTREND ENDIF
maxJobWaitTime: 15 #Valid values are: 1-60 and default is 15. Maximum length of time in minutes that the application waits for job completion from the mainframe.
###--------------------------------------------------------------------------------------
### Define all the steps of the application Universal Connector BMC AMI DevOps Common
###--------------------------------------------------------------------------------------
fileUpload: #Step represents File Transmission to mainframe (upload). DO NOT alter the name.
- stepName: local_to_mainframe #User defined step name, used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
sourceDirectory: . #Path in agent workspace from which a file is transferred. Relative path is used in this example with a period (.) which is for the agent workspace path.
sourceFileName: sample.txt #File on the client that is transferred. File must be a plain text file.
destinationPds: SAMPLE.PDS #Partitioned data set (PDS) on the mainframe from which a member is received.
destinationMember: SAMPLE #Member on the mainframe that is received.
fileDownload: #Step represents File Transmission from mainframe (download). DO NOT alter the name.
- stepName: mainframe_to_local #User defined step name used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
sourcePds: SAMPLE.PDS #Partitioned data set (PDS) on the mainframe where the member is transferred.
sourceMember: SAMPLE #Member on the mainframe that is transferred.
destinationDirectory: . #Directory in the agent workspace that is received. Relative path is used in this example with a period (.) which is for the agent workspace path.
destinationFileName: sample.txt #File in the agent workspace path that is received.
jclExecutionUsingPDSMember: #Step represents JCL Execution Using mainframe PDS Member. DO NOT alter the name.
- stepName: jclExecutionPDS #User defined step name, used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
jclPds: SAMPLE.PDS.JCL #Partitioned data set (PDS) on the mainframe where the JCL member, to be executed, resides.
jclMemberName: SAMPLE #JCL member on the mainframe to be executed.
acceptableReturnCode: 0004 #Valid values are: 0000-9999 and default is 0004. Maximum return code value that communicates that the submitted job was successful.
maxJobWaitTime: 2 #Valid values are: 1-60 and default is 2. Maximum length of time in minutes that the application waits for job completion from the mainframe.
jclExecutionUsingFile: #Step represents JCL Execution Using local File. DO NOT alter the name.
- stepName: jclExecutionFile #User defined step name, used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
jclDirectory: . #JCL directory in agent workspace where the JCL file, to be executed, resides. Relative path is used in this example with a period (.) which is for the agent workspace path.
jclFileName: JclFile.txt #JCL file in the agent workspace path to be executed.
acceptableReturnCode: 0004 #Valid values are: 0000-9999 and default is 0004. Maximum return code value that communicates that the submitted job was successful.
maxJobWaitTime: 2 #Valid values are: 1-60 and default is 2. Maximum length of time in minutes that the application waits for job completion from the mainframe.
jclExecutionUsingFreeForm: #Step represents JCL Execution Using Freeform JCL. DO NOT alter the name.
- stepName: jclExecutionFreeForm #User defined step name, used in the pipeline/workflow, for executing this configuration input.
debugMode: false #Valid values are: true or false(default). If set to true, will produce additional debug messages in the Console output log.
freeFormJcl: | #JCL statements used to execute JCL job. Value will be multiple lines.
//SAMPLEST JOB (5213),'SORT ',
// MSGCLASS=X,NOTIFY=&SYSUID,CLASS=A
//*
//* SORT 1ST 5 CHARS IN ASCENDING ORDER
//*
//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
LINE5
LINE2
LINE1
LINE4
LINE3
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,5,CH,A)
/*
//
acceptableReturnCode: 0004 #Valid values are: 0000-9999 and default is 0004. Maximum return code value that communicates that the submitted job was successful.
maxJobWaitTime: 2 #Valid values are: 1-60 and default is 2. Maximum length of time in minutes that the application waits for job completion from the mainframe.
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*