Example property values for all SQL Assurance steps


(BMC.DB2.SPE2404) 

This topic contains example property key values that you must configure to perform all steps of SQL Assurance processes. This sample gives a brief explanation for each property key and expected value. For detailed information about each SQL Assurance and Common step, see Creating a configuration file.

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.

 

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