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
);

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 ---------------
#
//