How to generate Accelerator trace from Batch
This document provides step by step instructions to capture an Accelerator trace from batch job. Most of the users wants to initiate this task from the operating system rather than Data Studio. If trace can be initiated from a batch job and store the dataset in USS or a zOS dataset, we then have that trace within the network and then can do a fast upload to IBM's FTP/SFTP server for receiving the cases. Generating the traces in data studio takes longer times because the data must transmit from the zOS, through the client VPN, back to data studio client, and then it must be uploaded from our client back to the case.
Collecting the trace on the zOS and send it out there will improve the trace capturing and storing process.
ACCEL_CONTROL_ACCELERATOR Stored Procedure
For a particular accelerator, this stored procedure retrieves information about the version of the installed components and their status. This stored procedure also offers several functions to control an accelerator. It allows you to set the trace level, reset it to its default, retrieve trace settings, the entire trace content or other information, as well as remove trace data from an accelerator. In addition, by running this stored procedure, you can obtain a list of tasks currently running on the accelerator as well as cancel these tasks.
ACCEL_CONTROL_ACCELERATOR has a subcommand <getTraceData>. Using this tag, you can specify the trace content you want to collect, an outputLocation, etc.
Here is an example:
CALL SYSPROC.ACCEL_CONTROL_ACCELERATOR('TF12',
'<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">
<getTraceData outputLocation="//'AQT.TC1751.TRACE.TGZ'">
<content>APPLIANCE</content>
<content>ACCELERATOR </content>
<content>ADDITIONALSUPPORTINFO </content>
</getTraceData>
</aqt:controlCommand>',
NULL,
NULL
);
Value
|
Description
|
ACCELERATOR
|
Collects accelerator and SQL engine logs, along with system diagnostics from the Docker container in which IBM Db2 Analytics Accelerator and the SQL engine run.
|
APPLIANCE
|
Collects system diagnostics from the physical environment of the Docker container, that is, the IBM Integrated Analytics System.
|
ADDITIONALSUPPORTINFO
|
Retrieves support information collected during online support sessions with a client.
|
These should cover up the most often collected trace content. If support team needs anything else, they'll need to tell you what to do. Sometimes, a further combination of <addTraceProfile> and <component> need to be specified to get other trace contents
Execution of ACCEL_CONTROL_ACCELERATOR
There are two ways to execute stored procedures by using job control language (JCL). You can achieve this by using one of the methods presented here.
Option 1 - Using the Db2 command line processor from UNIX System Services
To start a batch job, you can use the Db2 command line processor from UNIX System Services. In this case, the z/OS BPXBATCH utility is used to invoke the command line processor.
Option 2 - Using the DSN Db2 command processor for TSO
You can also start the batch job from the DSN Db2 command processor for Time Sharing Option (TSO). With the RUN subcommand of the processor, you can start an application that contains SQL CALL statements invoking the stored procedures.
AQTSCALL
To create a suitable batch job, you can copy and customize the sample JCL members, which are included in the SAQTSAMP data set. There are two members, AQTSJI03 and AQTSCALL.
AQTSCALL is a sample application program in the C programming language. A single invocation of AQTSCALL calls a single stored procedure. You specify the procedure (ACCEL_CONTROL_ACCELERATOR) to be called by providing CTRLACCEL as input parameter. The AQTSCALL program reads the input for the stored procedures from the DD names AQTP1, AQTP2, AQTP3 and AQTP4 for up to four input parameters. The DD names are defined in the AQTSJI03 job, which calls the sample application.
AQTSJI03 is a sample JCL that compiles and links the C program AQTSCALL and that invokes it as a subcommand of DSN. It contains a sample invocation for each stored procedure. Instructions on how to customize AQTSJI03 are included in the header of the JCL.
Here is an example:
//TSMCD0VV JOB (YTTT,84097999),'DB2AA-SP',
// NOTIFY=TSMCD0,CLASS=6,MSGCLASS=9
/*JOBPARM S=SYTE
//**************** TRACE OUTPUT TO /u/tsmcd0/idaalogs/ works
//*********************************************************************
//JOBLIB DD DISP=SHR,
// DSN=DB2TPRD.DB21.SDSNLOAD
// DD DSN=SYS1.SCEERUN,DISP=SHR
//*********************************************************************
//* From samplib: DB2TPRD.DB21.DAA750.SAQTSAMP(AQTSJI03)
//* Invoke AQTSCALL for controlling the accelerator
//*https://www.ibm.com/support/knowledgecenter/SS4LQ8_7.5.0/
//*com.ibm.datatools.aqt.doc/sp_msg/SPs/sp_idaa_control_accelerator.html
//CTRLIDAA EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//* parameter #1 for accelerator name
//AQTP1 DD *
IDAAZP1
/*
//* parameter #2 for commands
//* <getAcceleratorInfo includeEncryptionInfo = "true" />
//AQTP2 DD *
<?xml version="1.0" encoding="UTF-8" ?>
<aqttables:controlCommand
xmlns:aqttables="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.4" >
<getTraceData
outputLocation="//'TSMCD0.TRACE.TGZ'">
<content>ACCELERATOR</content>
</getTraceData>
</aqttables:controlCommand>
/*
//* parameter AQTMSGIN for logging output of the stored procedure run
//* KeepTrace=true or OnError
//* level=INFO or DEBUG
//* traceFileSizeInMB will wrap after 10MB or what specified
//* location is your file in USS or a directory for auto gen name
//* Go to 3;17 option and do ea command to edit in ASCII
//AQTMSGIN DD *
<?xml version="1.0" encoding="UTF-8" ?>
<spctrl:messageControl
xmlns:spctrl="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.1">
<compatibilityLevel>4</compatibilityLevel>
<traceConfig location= "/u/tsmcd0/idaalogs/"
keepTrace="true"
traceFileSizeInMB = "10" >
<component name="PROCEDURE" level="DEBUG"/>
</traceConfig>
</spctrl:messageControl>
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB21)
RUN PROGRAM(AQTSCALL) PLAN(AQTSCALL) -
LIB('DB2TPRD.DB21.DAA.SAQTLOAD') PARMS('CTRLACCEL')
END
/*
Sample Job Log is here. Please check SQLCODE is 466 in the joblog
read 80 bytes from DD:AQTP1
content='IDAAZP1'
read 640 bytes from DD:AQTP2
content='<?xml version="1.0" encoding="UTF-8" ?>
<aqttables:controlCommandxmlns:aqttables="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.4" >
<getTraceData outputLocation="//'TSMCD0.TRACE.TGZ'">
<content>ACCELERATOR</content>
</getTraceData>
</aqttables:controlCommand> 'read 800 bytes from DD:AQTMSGIN
content='<?xml version="1.0" encoding="UTF-8" ?> <spctrl:messageControlxmlns:spctrl="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.1">
<compatibilityLevel>4</compatibilityLevel>
<traceConfig location= "/u/tsmcd0/idaalogs/" keepTrace="true" traceFileSizeInMB = "10" >
<component name="PROCEDURE" level="DEBUG"/>
</traceConfig>
</spctrl:messageControl> 'call procedure with MESSAGE input of length 800:
<?xml version="1.0" encoding="UTF-8" ?> <spctrl:messageControlxmlns:spctrl="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.1"> <compatibilityLevel>4</compatibilityLevel> <traceConfig location= "/u/tsmcd0/idaalogs/" keepTrace="true" traceFileSizeInMB = "10" >
<component name="PROCEDURE" level="DEBUG"/> </traceConfig> </spctrl:messageControl>
*** SQLCODE is 466, SQLSTATE is 0100C
message=<?xml version="1.0" encoding="UTF-8" ?><dwa:messageOutput xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">
<message severity="informational" reason-code="AQT10000I"><text>The operation was completed successfully.</text><description>Success
message for the XML MESSAGE output parameter of each stored procedure.</description><action></action></message></dwa:messageOutput>
return code: 0
Using REXX via JCL
Using a REXX Exec to execute a Stored Procedure is a very common method. It can be used to call ACCEL_CONTROL_ACCELERATOR for generating a trace as well.
Following JCL can be used to call a REXX Exec
//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=<Db2 Load Library>
//SYSTSPRT DD SYSOUT=*
//SYSPROC DD DSN=<Rexx Sourcelib>,DISP=SHR
//* REXX NAME
//* |
//* | DB2 SUBSYSTEM
//* | | ACCELNAME
//* | | ¦
//* V V V
//* REXXAPPL DB2 ACCELNAME
//SYSTSIN DD *
GETTRACE DB21 MYIDAA1
/*
//
Following REXX can be used to call ACCEL_CONTROL_ACCELERATOR
ARG DBSSID ACCELNAME
ADDRESS TSO 'SUBCOM DSNREXX'
IF RC <> 0 THEN SUBCC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX 'CONNECT ' DBSSID
SAY 'Get info for: ' ACCELNAME
CommandString = '<?xml version="1.0" encoding="UTF-8" ?> ',
'<aqt:controlCommand version="1.0" ',
'xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011"> ',
'<getTraceData outputLocation="/tmp/acctrace.zip" ',
'keepConfiguration="true"> ',
'<content>ACCELERATOR</content> ',
'<content>APPLIANCE</content> ',
'</getTraceData>',
'</aqt:controlCommand> '
Msg = '<?xml version="1.0" encoding="UTF-8" ?> ',
'<spctrl:messageControl ',
'xmlns:spctrl="http://www.ibm.com/xmlns/prod/dwa/2011" ',
'version="1.0" versionOnly="false" > ',
'</spctrl:messageControl>',
copies(' ',32000)
MsgInd = 1
ResultString = left(' ',1000000)
ResultStringInd = 1
AcceleratorName=ACCELNAME
Address DSNREXX "EXECSQL CALL SYSPROC.ACCEL_CONTROL_ACCELERATOR (",
":AcceleratorName,",
":CommandString,",
":ResultString INDICATOR :ResultStringInd,",
":Msg INDICATOR :MsgInd ) "
if SQLCODE <> 466 then
do
SQLERRORPOSITION = 'Call Control Accelerator Stored Procedure';
if MsgInd >= 0 then
say "Message:" Msg;
call SQLERRORROUTINE
end
else
do
say "Successful Call of Control Accelerator Stored Procedure. Check Message"
if MsgInd >= 0 then
say "Message:" Msg;
end
EXIT
SQLERRORROUTINE:
SAY 'POSITION = ' SQLERRORPOSITION
SAY 'SQLCODE = ' SQLCODE
SAY 'SQLSTATE = ' SQLSTATE
SAY 'SQLERRP = ' SQLERRP
SAY 'TOKENS = ' TRANSLATE(SQLERRMC,',','FF'X)
SAY 'SQLERRD.1 = ' SQLERRD.1
SAY 'SQLERRD.2 = ' SQLERRD.2
SAY 'SQLERRD.3 = ' SQLERRD.3
SAY 'SQLERRD.4 = ' SQLERRD.4
SAY 'SQLERRD.5 = ' SQLERRD.5
SAY 'SQLERRD.6 = ' SQLERRD.6
SAY 'SQLWARN.0 = ' SQLWARN.0
SAY 'SQLWARN.1 = ' SQLWARN.1
SAY 'SQLWARN.2 = ' SQLWARN.2
SAY 'SQLWARN.3 = ' SQLWARN.3
SAY 'SQLWARN.4 = ' SQLWARN.4
SAY 'SQLWARN.5 = ' SQLWARN.5
SAY 'SQLWARN.6 = ' SQLWARN.6
SAY 'SQLWARN.7 = ' SQLWARN.7
SAY 'SQLWARN.8 = ' SQLWARN.8
SAY 'SQLWARN.9 = ' SQLWARN.9
SAY 'SQLWARN.10 = ' SQLWARN.10
ADDRESS DSNREXX 'EXECSQL ROLLBACK'
IF SQLCODE <> 0 THEN
DO
SAY 'ROLLBACK SQLCODE : ' SQLCODE
END
EXIT 8
Sending Trace to IBM Support
Once the Trace file is generated and written to a dataset, SFTP put of the file to testcase.boulder.ibm.com. It took about 19 minutes to send the 2.8GB trace file. Prior uploads from laptops took between 40 minutes and 3 hours. It is much faster. With that, you may have the ability to automatically trigger traces if the system senses an event in the appliance.
Following step can be added to Stored Procedure invocation for automatic FTP Process.
//*********************************************************************
//*** Send to IBM testcase *****
//*********************************************************************
//SFTP EXEC PROC=SFTPPROC
//SYSPRINT DD SYSOUT=*
//STDOUT DD SYSOUT=*
//STDERR DD SYSOUT=*
//SFTPIN DD *
# ==============cozsftp batch job template ===================
pwdsn="TSMCD0.PW.IDAA"
user=<userid>
host=testcase.boulder.ibm.com
sftp_opts="$sftp_opts -oPubkeyAuthentication=no"
sftp_opts="$sftp_opts -oStrictHostKeyChecking=no"
sftp_opts="$sftp_opts -oUserKnownHostsFile=/dev/null"
# ============================================================
# ------------- Start of SFTP command block -------------
. $script_dir/sftp_connect.sh << EOB
lzopts servercp=ISO8859-1
lzopts clientcp=IBM-1047
lzopts loglevel=I
lzopts mode=binary
lzopts lrecl=1024,recfm=fb,space=cyl.1.1
# --------------------------------------------------------
# List the current directory:
pwd
cd /toibm/
ls -al
cd im
ls -al
# Command below to send file to IBM:
# * The \ character is for line continuation on get or puts
# *
put //TSMCD0.TRACE.D060720.P2.TGZ \
Cust-TS0012345678.TGZ
ls -al
# --------------------------------------------------------
quit
EOB
# ------------- End of SFTP command block ---------------
#
//
#Db2forz/OS#IDAA