Sample pipeline script
You can use SQL Assurance features in Scripted Pipelines and Declarative Pipelines using other jobs (projects). For more information, see Using-BMC-AMI-SQL-Assurance-as-part-of-a-pipeline.
The following figure represents a sample pipeline script for a Scripted Pipeline:
import static com.bmc.db2.bmcclient.BMCClientSA.bmcSA;
node {
/* BMC AMI - SQL Assurance for Db2 */
try {
stage('Common - Authentication') {
/* BMC AMI DevOps Common - Authentication */
bmcAmiAuthentication comtype: 'ZOSMF', debug: true, dpassrun: '${MF_PW}', dpassword: '', dport: '<insertPort#>', dserver: '<insertHostname>', duser: '${MF_USER_ID}', pwdruntime: true, symdir: '${PLUGIN_VAR_FILE}'
parameters:
[string(name: 'MF_USER_ID', value: "${params.MF_USER_ID}"),
string(name: 'PLUGIN_VAR_FILE', value: "${params.PLUGIN_VAR_FILE}"),
password(name: 'MF_PW', value: "${params.MF_PW}")
]
}
stage('Analyze Static SQL') {
/* BMC AMI SQL Assurance for Db2 - Analyze Static SQL */
bmcAmiSqlAssuranceStaticSql collectionId: '<insertCollid>', debug: true, disableBuildStep: false, expMethod: 'Dynamic Explain', failWarnMode: 'Fail', genJcl: false, jcl: '''${Job Card}
//*
// 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=\'SYSOUT=*\'
// SET LOGMODE=\'FAIL\'
// SET RULSPACE=\'SPACE=(CYL,(10,10)),\'
// SET VTIBSZ=20
//*-----------------------------------------------------------------
//*
//SQLXPLOR EXEC COBPRPSS
${SQL Explorer Input Stream}
//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
//*''', jobCardIn: '''//COBSSQL JOB (#acctno#),\'STATIC-SQL\',
// class="A",MSGCLASS=X,MSGLEVEL=(1,1),REGION=0M,
// NOTIFY=&SYSUID
/*ROUTE XEQ BMCPLX1
/*JOBPARM SYSAFF=#lpar1#
//*
//*''', jobWaitTime: '5', objType: 'package', objectName: '<insertPGnm>', sqlExpInParm: '''//PSSMAIN.SYSIN DD *
--NEWOBJ
OBJECT=${Object Type}
NAME="${Object Name}"
COLLID="${Collection ID (Package only)}"
VERSION="${Version (Package only)}"
RULES=#rules#
RULEDSN="#ruledsn#"
PLANTBL=N
CURRENCY=#currency#
MSGLEVEL=ALL
--ENDOBJ''', ssid: '${SSID}', version: '%'
parameters:
[string(name: 'SSID', value: "${params.SSID}")]
}
stage('Common - Clean Up') {
/* clean up workfile */
bmcAmiCleanUp()
}
}
catch (e) {
currentBuild.result = "FAILED"
notifyFailed()
throw e
}
}
def notifySuccessful() {
/* Echo Success */
println "Jenkins Job is successful."
}
def notifyFailed() {
/* Echo Failure and send Email notification to interested parties */
emailext (
mimeType: 'text/html', attachLog: true, body: '''*******************************<br/>*<b>BMC AMI - SQL Assurance for Db2 </b>*<br/>*******************************<br/>
<br/>Jenkins Project: <b>$JOB_NAME</b><br/>
<br/>Build#: <b>$BUILD_NUMBER</b><br/>
<br/>Check console output at "<a href="${BUILD_URL}">${JOB_NAME} [${BUILD_NUMBER}]</a>" to view the results.<br/>
<br/><br/><br/>Regards,<br/><b>QA for BMC AMI SQL Assurance for Db2</b><br/>
''', subject: 'Failure - Notification: $JOB_NAME - $BUILD_NUMBER', to: '<insertEmailAddress>'
)
}
node {
/* BMC AMI - SQL Assurance for Db2 */
try {
stage('Common - Authentication') {
/* BMC AMI DevOps Common - Authentication */
bmcAmiAuthentication comtype: 'ZOSMF', debug: true, dpassrun: '${MF_PW}', dpassword: '', dport: '<insertPort#>', dserver: '<insertHostname>', duser: '${MF_USER_ID}', pwdruntime: true, symdir: '${PLUGIN_VAR_FILE}'
parameters:
[string(name: 'MF_USER_ID', value: "${params.MF_USER_ID}"),
string(name: 'PLUGIN_VAR_FILE', value: "${params.PLUGIN_VAR_FILE}"),
password(name: 'MF_PW', value: "${params.MF_PW}")
]
}
stage('Analyze Static SQL') {
/* BMC AMI SQL Assurance for Db2 - Analyze Static SQL */
bmcAmiSqlAssuranceStaticSql collectionId: '<insertCollid>', debug: true, disableBuildStep: false, expMethod: 'Dynamic Explain', failWarnMode: 'Fail', genJcl: false, jcl: '''${Job Card}
//*
// 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=\'SYSOUT=*\'
// SET LOGMODE=\'FAIL\'
// SET RULSPACE=\'SPACE=(CYL,(10,10)),\'
// SET VTIBSZ=20
//*-----------------------------------------------------------------
//*
//SQLXPLOR EXEC COBPRPSS
${SQL Explorer Input Stream}
//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
//*''', jobCardIn: '''//COBSSQL JOB (#acctno#),\'STATIC-SQL\',
// class="A",MSGCLASS=X,MSGLEVEL=(1,1),REGION=0M,
// NOTIFY=&SYSUID
/*ROUTE XEQ BMCPLX1
/*JOBPARM SYSAFF=#lpar1#
//*
//*''', jobWaitTime: '5', objType: 'package', objectName: '<insertPGnm>', sqlExpInParm: '''//PSSMAIN.SYSIN DD *
--NEWOBJ
OBJECT=${Object Type}
NAME="${Object Name}"
COLLID="${Collection ID (Package only)}"
VERSION="${Version (Package only)}"
RULES=#rules#
RULEDSN="#ruledsn#"
PLANTBL=N
CURRENCY=#currency#
MSGLEVEL=ALL
--ENDOBJ''', ssid: '${SSID}', version: '%'
parameters:
[string(name: 'SSID', value: "${params.SSID}")]
}
stage('Common - Clean Up') {
/* clean up workfile */
bmcAmiCleanUp()
}
}
catch (e) {
currentBuild.result = "FAILED"
notifyFailed()
throw e
}
}
def notifySuccessful() {
/* Echo Success */
println "Jenkins Job is successful."
}
def notifyFailed() {
/* Echo Failure and send Email notification to interested parties */
emailext (
mimeType: 'text/html', attachLog: true, body: '''*******************************<br/>*<b>BMC AMI - SQL Assurance for Db2 </b>*<br/>*******************************<br/>
<br/>Jenkins Project: <b>$JOB_NAME</b><br/>
<br/>Build#: <b>$BUILD_NUMBER</b><br/>
<br/>Check console output at "<a href="${BUILD_URL}">${JOB_NAME} [${BUILD_NUMBER}]</a>" to view the results.<br/>
<br/><br/><br/>Regards,<br/><b>QA for BMC AMI SQL Assurance for Db2</b><br/>
''', subject: 'Failure - Notification: $JOB_NAME - $BUILD_NUMBER', to: '<insertEmailAddress>'
)
}
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*