ZWSTECHNOTE : Tracing to help resolve issues with DB2 historical reporting batch archive job

NOTE:  If the DB2 is on z/OS, the values to use for "ppppp" and "dddd" come right from the master DB2 started task

in the JESMSGLG for example:

DSNL004I  -DCI4 DDF START COMPLETE  175           
           LOCATION  QXPWSI4DCI4                  
           LU        AUIBMQXP.WSI1DCI4            
           GENERICLU -NONE                        
           DOMAIN    hclwsi4.prod.hclpnp.com      
           TCPPORT   5009                         
           SECPORT   0                            
           RESPORT   5010                         
           IPNAME    -NONE                        
           OPTIONS:                               
            PKGREL = COMMIT          

LOCATION should be used for dddd  and TCPPORT for ppppp  (if the DB2 is on z/OS only)               

One of the most common configuration issues seen (especially when the DB2 is not on z/OS) is that
the IP address/port number of the DBURL is incorrect or cannot be reached from the z/OS side due
to a firewall setting.

To check for this issue a TELNET command from the mainframe side to the port specified in the DBURL parameter and the IP address in the DBURL parameter for example:
.
TSO TELNET n.nn.nnn.nnn pppp

If this fails you need to check with the DB2 administrator. For a non-z/OS DB2 the port
value in the DBURL should normally be the same port that was used when the dbsetup script was run (see section "Creating the database in the distributed environment" in the Managing the Workload manual). Until this TELNET command works successfully there is no need to try any of the other techniques listed in this technote.

It is normally useful to review the contents of the profile dataset- pointed to by DD STDENV
in the batch job, as any errors or "typos" in this dataset can prevent the archiving job from working
correctly.

Also in the batch archiving job it is a good idea to specify the following in INPPARM to enable
Java tracing:

JVPARMS(+T)

If running with DBOPT TRACELEVEL(5) and INPPARM JVPARMS(+T) still does not help
pinpoint the cause of the problem there are some other traces that may help- specifically the
DB2 driver trace and the classloading trace.

Also if you run with TRACELEVEL(5) or higher, a file with a name like TWSZ950_EqqArchiver.log   

should be created in the WRKDIR/java directory (where WRKDIR is the parameter value from DBOPTS).

This file is useful for debugging purposes.  If you do NOT see this file being created, it could indicate

that your JAVA version is too low.  For example, this file will not be created for Java version 1.8.0_211 but

it will be created for Java version 1.8.0_301.  Also note that if your Java version is 255 or higher you

need to apply the PTF for APAR PH34316 or the DB2 archiving job will fail.  (PTFs are UI74169 for 9.3 and
UI74170 for 9.5).    To see your java version in the output of the EQQRXARC job (assuming you have

the java tracing enabled) look in the SYSOUT for a message like this:

JVMJZBL1012I Java Virtual Machine created. Version information follows:    
java version "1.8.0_301"                                                   


DB2 DRIVER TRACE

This is something that would be done on the mainframe side. First you
need to create a file in an OMVS directory to hold the trace, in this example

/u/sviola/twstrace

Create a file in this directory named jcc.properties which will have
the following contents:
.
BROWSE /u/sviola/twstrace/jcc.properties

********************************* Top of Data *******
db2.jcc.traceDirectory=/u/sviola/twstrace
db2.jcc.traceFile=trace
db2.jcc.traceFileAppend=false
db2.jcc.traceLevel=-1
******************************** Bottom of Data *****

Change the file name in the first line to be the one you are using,
the rest of the file contents will be the same.

Next edit the env.profile pointed to by STDENV in the batch job
to have the lines shown here at the end:

EDIT /u/var/ZWSA10/JAVAUTL/java/env.profile

000026 # Configure JVM trace options
000027 IJO="-Ddb2.jcc.propertiesFile=/u/sviola/twstrace/jcc.properties"
000028 export IBM_JAVA_OPTIONS="$IJO"
****** **************************** Bottom of Data ***********

Again on line 27 as shown here change the file name to match
the jcc.properties file that you are using.

Then run the batch job that updates the historical DB2 database.

The output of the batch job and the trace file (either a tar of the directory containing
the trace or a tersed copy of the trace file if you use the OGETcommand to copy the OMVS
file to an MVS file).can then be analyzed.

Open a case with zWS  support for this if you do not already have a case open.

END DB2 DRIVER TRACE

CLASSLOADING TRACE

There is also a classloading trace that can be enabled by
adding this to the env.profile:

# Configure JVM trace options
IJ2="-Dibm.cl.verbose=com.ibm.tws.zarch.cp.Archiver"
export IBM_JAVA_OPTIONS="$IJ2"

The classloading trace and the driver trace can be run at the
same time using the examples above, as the variable names (IJ0/IJ2)
have intentionally been made different.

END CLASSLOADING TRACE

Other suggestions:

A display from TSO OMVS of the BIN directory you are
using (the APP_HOME parameter of your env.profile) may
be useful in spotting certain problems.

When applying maintenance via SMP/E which affects the jar files used for
historical reporting, the SMPE APPLY modifies the EQQZARCH member
in the BIN/IBM directory which is then copied to the ZArchiver.jar file in the
BIN directory. The sizes of these two files (EQQZARCH and ZArchiver.jar)
should be the same and the size of the ZArchiver.jar file in the APP_HOME
directory should match also if you copy the file after applying SMPE maintenance.
If you have different file sizes then you may be pointing to the wrong ZArchiver.jar
file. This can happen for example if after applying SMPE maintenance you copy
the EQQZARCH file to your "production" environment but neglect to copy the
ZArchiver.jar file as well.