Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA
16 November 2008
Introduction
If you have application jobs or users that run the DB2 load utility and would like to keep track of this information over time there is valuable information stored in the DB2 diagnostic log file that can help you accomplish this. Detail information about each table load and index build is recorded in the DB2 diagnostic log file.
This article describes the table load and index build information available and a script that will extract this information and insert it into a table that will help you monitor table load and index build performance over time. This script also extracts DB2 memory management message information (STMM) and imports that into another table.
Pros and cons
The advantage of having table load and index build information over time is that it can help you monitor and manage performance and answer questions about the performance of loads run by applications and users. If you use the DB2 automatic memory manager (STMM) then you will have information showing how this behaves over time as well.
One disadvantage of this approach is that the script relies on the current availability and format of information from the DB2 diagnostic log file and if IBM changes the format or content of this file in a future release, as has been done in the past, then this script will have to be modified or may become completely obsolete if the information is no longer available.
Description
The DB2 diagnostic log file named db2diag.log for a DB2 instance contains messages with information about table loads and index builds that are run against a database. Listed below are examples of the start and completion of a table load and the start and completion of the index build phase of the load. The script I developed will capture the server, instance, database, authid, table load or index build type, starting timestamp, completed timestamp and table name involved with the load. It will also capture information about what the DB2 automatic memory manager is doing during the day if you use that feature.
Having this table load and index build information available in a table named OPERATIONS.DB2DIAG_LOAD_INFORMATION, will allow you to see all of the table loads and index builds that run during a day. You will also be able to compare the performance of loads into tables from one day to the next. Having automatic memory management information available in a table named OPERATIONS.DB2DIAG_STMM_INFORMATION will give you insight into what the automatic memory manager is doing in your environments as well.
One example of how I use the table load and index build information is to help me identify scheduling problems that result in poor performance for other users or jobs. If a load job is running, other applications might wait to select information from the table and eventually time out and roll back. Being able to tell application teams and users that some other application or user was running a load at the time can help them understand the importance of proper scheduling and coordination of work activities.
When a load runs longer than normal and about the same number of rows are involved with the load, this information may show that the index build phase of the load ran longer. If loads are usually run with the incremental index build option, there are times when the database will override this and do a complete index rebuild and the longer index build times may indicate this.
The great thing about having this information available in a table is that you can provide physical evidence about what happened when and not just have to ask other people to take your word for it. If you make this table information available in an online dash board for application support people, users and management, then they can see for themselves what is going on and trend graphs can be developed using this information to show how loads are performing over time.
2008-11-20-00.19.46.435158-300 I13347A492 LEVEL: Warning PID : 4976964 TID : 1 PROC : db2lfrm0 0 INSTANCE: edwp02 NODE : 000 APPHDL : 0-718 APPID: 10.145.33.23.39186.081120051946 AUTHID : PP0342DL FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:311 DATA #1 : String, 131 bytes LOADID: 6336514.2008-11-20-00.19.46.264479.0 (35;4) Starting LOAD phase at 11/20/2008 00:19:46.427305. Table MDDB .TRNCTN_ALRT 2008-11-20-00.19.46.522220-300 I14733A465 LEVEL: Warning PID : 106784 TID : 1 PROC : db2lrid 0 INSTANCE: edwp02 NODE : 000 APPHDL : 0-718 APPID: 10.145.33.23.39186.081120051946 AUTHID : PP0342DL FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:311 DATA #1 : String, 105 bytes LOADID: 6336514.2008-11-20-00.19.46.264479.0 (35;4) Completed LOAD phase at 11/20/2008 00:19:46.517440. 2008-11-20-00.19.46.528250-300 I15199A465 LEVEL: Warning PID : 106784 TID : 1 PROC : db2lrid 0 INSTANCE: edwp02 NODE : 000 APPHDL : 0-718 APPID: 10.145.33.23.39186.081120051946 AUTHID : PP0342DL FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:311 DATA #1 : String, 105 bytes LOADID: 6336514.2008-11-20-00.19.46.264479.0 (35;4) Starting BUILD phase at 11/20/2008 00:19:46.522519. 2008-11-20-00.19.46.943711-300 I15665A466 LEVEL: Warning PID : 106784 TID : 1 PROC : db2lrid 0 INSTANCE: edwp02 NODE : 000 APPHDL : 0-718 APPID: 10.145.33.23.39186.081120051946 AUTHID : PP0342DL FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:311 DATA #1 : String, 106 bytes LOADID: 6336514.2008-11-20-00.19.46.264479.0 (35;4) Completed BUILD phase at 11/20/2008 00:19:46.940636.
Environment
The script in this article was tested on a server running the AIX operating system and DB2 versions 9.1 and 9.5. The KORN shell script should work on any server that has a UNIX-like operating system that supports these types of scripts. (Linux and Linux/390 come to mind.) This script is designed to run in a native UNIX environment and is not intended to run under the DB2 Script Center Client Window. This script may be run under any id that has access to a DB2 environment via the db2profile and that has the authority to run the db2 get dbm cfg command. This command is used to determine the default location of the db2diag.log file. The script requires that a DB2 instance id and database name be passed to it and has other parameters you may use to have it run against old copies of db2diag.log files if you move these off to another path daily like I do.
Before you get started, a couple of words of caution:
Practice running the script and verify the results.
Create the table before running the script. The DDL for this is provided below.
Create tablespaces for the table and it’s index.
Make sure the id you use to run the script has privileges to run the db2 get dbm cfg command and to be able to connect to the database and to insert and update rows in the OPERATIONS.DB2DIAG_LOAD_INFORMATION table and to import rows into the OPERATIONS.DB2DIAG_STMM_INFORMATION table.
Schedule this script to run in cron or some other scheduling system daily under a DB2 instance id and you will have a history of the table load and index build information for you to use over time.
WARNING: If you keep your db2diag.log file and never move it to another path and append a date timestamp to it then this script will run longer and longer every day and try to insert and update rows it processed the day before. I have a script that moves our db2diag.log files to a /dbawork/instance/db2dump path daily that renames the file to db2diag_CCYYMMDD*.log where CCYYMMDD* is a date timestamp. This script is designed to allow you to pass this path as the third parameter. If you just pass the path then it will process all db2diag*.log files. If you pass it the db2diag_CCYYMMDD*.log at the end of the path as shown in the crontab example below then the script will only process yesterday’s db2diag.log file.
NOTE: If you run the script without the third parameter, then the script will run db2 get dbm cfg to determine the location of the current db2diag.log file and then process that file.
WARNING: If IBM changes the format of the DB2 diagnostic log messages this script may need to be re-written or may become completely obsolete if the information is no longer available in the db2diag.log file.
CRONTAB schedule example
00 01 * * * /db2util/scripts/db2diag_extract_information.ksh edwp02 edwp02
/dbawork/edwp02/db2dump db2diag_CCYYMMDD*.log >/dev/null 2>&1
The script
#!/bin/ksh
#-----------------------------------------------------------------------
# Program : db2diag_extract_information.ksh
# Description : This script extracts information from db2diag
# : log files.
# Author : Mark Mulligan
# Date : 06/10/2008
#-----------------------------------------------------------------------
ScriptVer='06/10/2008'
ScriptName='db2diag_extract_information'
Server=$(hostname)
if [ "$1" = "" ]
then echo ""
echo "Parm 1 Instance (required)"
echo "Parm 2 Database (required)"
echo "Parm 3 DB2 Diagnostic Log Path (optional) Defaults to DIAGPATH"
echo ' May be a path where you put your db2diag_CCYYMMDDHHMMSS.log files'
echo ' For example, "/dbawork/edwp02/db2dump"'
echo "Parm 4 DB2 Diagnostic Log File (optional) Defaults to db2diag*.log"
echo ' May be db2diag_CCYYMMDD*.log'
echo "Parm 5 Script Output Directory Path (optional) Defaults to /dbawork/reports"
echo ""
return 8
else Instance=`echo $1 | tr '[A-Z]' '[a-z]'`
fi
if [ "$2" = "" ]
then echo ""
echo "Parm 2 Database (required)"
echo "Parm 3 DB2 Diagnostic Log Path (optional) Defaults to DIAGPATH"
echo ' May be a path where you put your db2diag_CCYYMMDDHHMMSS.log files'
echo ' For example, "/dbawork/edwp02/db2dump"'
echo "Parm 4 DB2 Diagnostic Log File (optional) Defaults to db2diag*.log"
echo ' May be db2diag_CCYYMMDD*.log'
echo "Parm 5 Script Output Directory Path (optional) Defaults to /dbawork/reports"
echo ""
return 8
else Database=`echo $2 | tr '[a-z]' '[A-Z]'`
fi
if [ "$3" = "" ]
then db2 get dbm cfg | grep DIAGPATH | read diagnostic data directory path diagpath equals DiagPath
Path="${DiagPath}"
else Path=${3}
fi
if [ "$4" = "" ]
then File="db2diag*.log"
else if [ "$4" = "db2diag_CCYYMMDD*.log" ]
then CCYYMMDD="`date +%Y%m%d`"
File="db2diag_${CCYYMMDD}*.log"
else File=${4}
fi
fi
if [ "$5" = "" ]
then OutputDir="/dbawork/reports"
else OutputDir="${5}"
fi
. $HOME/sqllib/db2profile
Datetime="`date +%Y%m%d%H%M%S`"
FileName=${OutputDir}/${ScriptName}_${Server}_${Instance}_${Database}_${Datetime}
OutputFile=${FileName}.txt
echo "$ScriptName Vers: $ScriptVer started on " `uname -n` on `date` | tee $OutputFile
echo "" | tee $OutputFile
echo "ls ${Path}/${File}" | tee -a $OutputFile
ls ${Path}/${File} > $FileName.ls
echo "" | tee $OutputFile
cat $FileName.ls | tee -a $OutputFile
cat $FileName.ls | while read filename
do
echo "Processing db2diag information in $filename" | tee -a $OutputFile
cat $filename | while read a b c d e f g h i j k l m n o p q r s t u v w x y z
do
# 2008-06-19-06.27.59.170148-240 I1503054A494 LEVEL: Warning
if [ "$c" = "LEVEL:" ]
then echo ${a} | sed 's/-/ /g' | read ccyy mo dd time extra
db2diagTimestamp="${ccyy}-${mo}-${dd}-${time}"
# echo $db2diagTimestamp
fi
# PID : 6807552 TID : 1 PROC : db2lfrm0 0
if [ "$a" = "PID" ]
then PID=$c
fi
# INSTANCE: edwp02 NODE : 000
if [ "$a" = "INSTANCE:" ]
then INSTANCE=$b
NODE=$e
fi
# APPHDL : 0-1738 APPID: 10.145.33.23.46241.080619102739
if [ "$a" = "APPHDL" ]
then APPHDL=$c
APPID=$e
fi
# AUTHID : EDWP02
if [ "$a" = "AUTHID" ]
then AUTHID=$c
fi
# LOADID: 3264648.2008-08-12-04.22.39.825964.0 (5;15)
if [ "$a" = "LOADID:" ]
then LOADID=$b
fi
# Starting LOAD phase at 06/19/2008 06:27:58.562570. Table MDDB .PIF_RPRT_DY_SM
if [[ "$a" = "Starting" && "$b" = "LOAD" ]]
then Type=$b
StartingLOADTimestamp=$db2diagTimestamp
TableName=$h$i
echo $TableName | sed 's/\./ /g' | read TabSchema TabName
# echo "$TabSchema"
# echo "$TabName"
db2 -x "insert into operations.db2diag_load_information values \
('${LOADID}','${Type}','${Server}','${Instance}','${Database}',${NODE},\
'${AUTHID}',${PID},'${APPHDL}','${APPID}',\
timestamp('${StartingLOADTimestamp}'), \
timestamp('${StartingLOADTimestamp}'), \
'${TableName}','${TabSchema}','${TabName}')" > /dev/null 2>&1
fi
if [[ "$a" = "Completed" && "$b" = "LOAD" ]]
then Type=$b
CompletedLOADTimestamp=$db2diagTimestamp
db2 -x "update operations.db2diag_load_information set completed_timestamp = \
timestamp('${CompletedLOADTimestamp}') where loadid = '${LOADID}' \
and type = '${Type}' and node = ${NODE}" > /dev/null 2>&1
fi
# Starting BUILD phase at 06/19/2008 06:28:02.441206.
if [[ "$a" = "Starting" && "$b" = "BUILD" ]]
then Type=$b
StartingBUILDTimestamp=$db2diagTimestamp
db2 -x "select tablename,tabschema,tabname from operations.db2diag_load_information \
where loadid = '${LOADID}' and type = 'LOAD' and node = ${NODE}" | \
read TableName TabSchema TabName
db2 -x "insert into operations.db2diag_load_information values \
('${LOADID}','${Type}','${Server}','${Instance}','${Database}',\
${NODE},'${AUTHID}',${PID},'${APPHDL}','${APPID}',\
timestamp('${StartingBUILDTimestamp}'),\
timestamp('${StartingBUILDTimestamp}'),\
'${TableName}','${TabSchema}','${TabName}')" > /dev/null 2>&1
fi
# Completed BUILD phase at 06/19/2008 06:28:05.446418.
if [[ "$a" = "Completed" && "$b" = "BUILD" ]]
then Type=$b
CompletedBUILDTimestamp=$db2diagTimestamp
db2 -x "update operations.db2diag_load_information set completed_timestamp = \
timestamp('${CompletedBUILDTimestamp}') where loadid = '${LOADID}' \
and type = '${Type}' and node = ${NODE}" > /dev/null 2>&1
fi
# CHANGE : STMM CFG DB EDWP02: "Pckcachesz" From: "4696" To: "4664"
if [[ "$a" = "CHANGE" && "$c" = "STMM" ]]
then CHANGESTMMTimestamp=$db2diagTimestamp
DATABASE=`echo $f | sed 's/://g'`
Type=`echo $g | sed 's/"//g'`
From=`echo $i | sed 's/"//g'`
To=`echo $l | sed 's/"//g'`
echo '"'$CHANGESTMMTimestamp'","'$Server'","'$Instance'","'$DATABASE'",'$NODE', \
"'$AUTHID'",'$PID',"'$APPHDL'","'$APPID'","'$Type'","FROM",\
'$From >> $FileName.stmm
echo '"'$CHANGESTMMTimestamp'","'$Server'","'$Instance'","'$DATABASE'",'$NODE', \
"'$AUTHID'",'$PID',"'$APPHDL'","'$APPID'","'$Type'","TO",\
'$To >> $FileName.stmm
fi
done
done
db2 -v connect to $Database | tee -a $OutputFile
db2 -v "import from $FileName.stmm of del messages $FileName.stmm.msg \
NP.insert_update into OPERATIONS.DB2DIAG_STMM_INFORMATION" | tee -a $OutputFile
echo "$ScriptName Vers: $ScriptVer ended on " `uname -n` on `date` | tee -a $OutputFile
echo "Output located in" | tee -a $OutputFile
ls $FileName*
The Tables
------------------------------------------------
-- DDL Statements for table "OPERATIONS"."DB2DIAG_LOAD_INFORMATION"
------------------------------------------------
CREATE TABLE "OPERATIONS"."DB2DIAG_LOAD_INFORMATION" (
"LOADID" CHAR(40) NOT NULL WITH DEFAULT ' ' ,
"TYPE" CHAR(5) NOT NULL WITH DEFAULT ' ' ,
"SERVER" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"INSTANCE" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"DATABASE" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"NODE" SMALLINT NOT NULL WITH DEFAULT 0 ,
"AUTHID" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"PID" BIGINT NOT NULL WITH DEFAULT 0 ,
"APPHDL" CHAR(8) NOT NULL WITH DEFAULT ' ' ,
"APPID" CHAR(31) NOT NULL WITH DEFAULT ' ' ,
"STARTING_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
"COMPLETED_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
"TABLENAME" VARCHAR(256) NOT NULL WITH DEFAULT '' ,
"TABSCHEMA" VARCHAR(128) NOT NULL WITH DEFAULT '' ,
"TABNAME" VARCHAR(128) NOT NULL WITH DEFAULT '' )
DISTRIBUTE BY HASH("LOADID")
IN "TSOPERATIONS01" INDEX IN IXOPERATIONS01 NOT LOGGED INITIALLY ;
-- DDL Statements for primary key on Table "OPERATIONS"."DB2DIAG_LOAD_INFORMATION"
ALTER TABLE "OPERATIONS"."DB2DIAG_LOAD_INFORMATION"
ADD CONSTRAINT "DB2DIAG_LOAD_IX01" PRIMARY KEY
("LOADID",
"TYPE",
"NODE");
------------------------------------------------
-- DDL Statements for table "OPERATIONS"."DB2DIAG_STMM_INFORMATION"
------------------------------------------------
CREATE TABLE "OPERATIONS"."DB2DIAG_STMM_INFORMATION" (
"CHANGE_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT ,
"SERVER" CHAR(8) NOT NULL WITH DEFAULT ,
"INSTANCE" CHAR(8) NOT NULL WITH DEFAULT ,
"DATABASE" CHAR(8) NOT NULL WITH DEFAULT ,
"NODE" SMALLINT NOT NULL WITH DEFAULT ,
"AUTHID" CHAR(8) NOT NULL WITH DEFAULT ,
"PID" BIGINT NOT NULL WITH DEFAULT ,
"APPHDL" CHAR(8) NOT NULL WITH DEFAULT ,
"APPID" CHAR(31) NOT NULL WITH DEFAULT ,
"TYPE" CHAR(40) NOT NULL WITH DEFAULT ,
"FROM_OR_TO" CHAR(4) NOT NULL WITH DEFAULT ,
"MEMORY_SIZE" BIGINT NOT NULL WITH DEFAULT )
DISTRIBUTE BY HASH("CHANGE_TIMESTAMP")
IN "TSOPERATIONS01" INDEX IN IXOPERATIONS01 NOT LOGGED INITIALLY ;
-- DDL Statements for primary key on Table "OPERATIONS"."DB2DIAG_STMM_INFORMATION"
ALTER TABLE "OPERATIONS"."DB2DIAG_STMM_INFORMATION"
ADD CONSTRAINT "DB2DIAG_STMM_IX01" PRIMARY KEY
("CHANGE_TIMESTAMP",
"SERVER",
"INSTANCE",
"DATABASE",
"NODE",
"AUTHID",
"PID",
"APPHDL",
"APPID",
"TYPE",
"FROM_OR_TO");
Example
If you run the script in foreground without passing it any parameters, it will echo display the parameters that the script uses. The first two parameters for the script are required so that the common reusable DBA script will know what DB2 instance and database to use. The third parameter is optional. You may pass any path that indicates the location of the DB2 diagnostic log files the script should read or the script will default to the DIAGPATH value by running the db2 get dbm cfg command to obtain this. The fourth parameter is optional and defaults to the db2diag.log file name. If you move your db2diag.log file to another path daily and rename it to db2diag_CCYYMMDDHHMMSS.log where CCYYMMDDHHMMSS is a date timestamp like I do then you may pass this value as the parameter and the script will only run against yesterday’s diagnostic log file. Otherwise, it will run against all db2diag*.log files in the path.
Moving your db2diag.log files to another path daily and renaming it to have a date timestamp in the name will help this script only process new information. This db2diag.log save technique can also help you keep your db2diag.log file to a manageable size daily and when you have to open a PMR with IBM and run db2support to send the db2diag.log file and other information, the files you have to send IBM will be smaller and easier for them to analyze. You can keep your DB2 diagnostic log file history or remove these files over time to prevent file systems from filling up.
db2diag_extract_information.ksh
Parm 1 Instance (required)
Parm 2 Database (required)
Parm 3 DB2 Diagnostic Log Path (optional) Defaults to DIAGPATH
May be a path where you put your db2diag_CCYYMMDDHHMMSS.log files
For example, "/dbawork/edwp02/db2dump"
Parm 4 DB2 Diagnostic Log File (optional) Defaults to db2diag*.log
May be db2diag_CCYYMMDD*.log
Parm 5 Script Output Directory Path (optional) Defaults to /dbawork/reports
When you run the script in foreground and pass it a DB2 instance id and database, you should see output displayed that is similar to the example shown below.
db2diag_extract_information.ksh edwp02 edwp02
db2diag_extract_information Vers: 06/10/2008 started on edwasp on Thu Nov 20 09:45:12 EST 2008
ls /db2home/edwp02/sqllib/db2dump/db2diag*.log
/db2home/edwp02/sqllib/db2dump/db2diag.log
Processing db2diag information in /db2home/edwp02/sqllib/db2dump/db2diag.log
connect to EDWP02
Database Connection Information
Database server = DB2/AIX64 9.1.4
SQL authorization ID = U0H005
Local database alias = EDWP02
import from /dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.stmm
of del messages /dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.stmm.msg
insert_update into OPERATIONS.DB2DIAG_STMM_INFORMATION
Number of rows read = 1386
Number of rows skipped = 0
Number of rows inserted = 1386
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1386
db2diag_extract_information Vers: 06/10/2008 ended on edwasp on Thu Nov 20 09:48:25 EST 2008
Output located in
/dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.ls
/dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.stmm.msg
/dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.stmm
/dbawork/reports/db2diag_extract_information_edwasp_edwp02_EDWP02_20081120094512.txt
Select Example
The output created by this script is stored in DB2 tables. You may develop SQL or a script that runs different types of SQL that will help you use this information to help you track table load and index build performance over time. This information can also be used to help identify what is running when and this may help you identify contention between different applications and users. You can also use this information to determine when DB2 is allowing a table load to incrementally add information to indexes and when it has switched over to completely rebuild indexes during a load. Here is one example of SQL that lists load information from yesterday.
select a.server,a.instance,a.database,a.node,a.authid,a.loadid,a.type,a.starting_timestamp,
a.completed_timestamp,char(a.tablename,40) as tablename,char(b.tbspace,20) as tablespace
from operations.db2diag_load_information a,syscat.tables b
where a.authid like '%%'
and a.tabschema=b.tabschema
and a.tabname=b.tabname
and ( date(a.starting_timestamp)= current date - 1 day or
date(a.completed_timestamp)= current date - 1 day or
current date -1 day between date(a.starting_timestamp)
and date(a.completed_timestamp)
)
order by a.starting_timestamp
SERVER INSTANCE DATABASE NODE AUTHID LOADID TYPE STARTING_TIMESTAMP COMPLETED_TIMESTAMP TABLENAME TABLESPACE
-------- -------- -------- ------ -------- ---------------------------------------- ----- -------------------------- -------------------------- ---------------------------------------- --------------------
edwasp edwp02 EDWP02 3 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.825346 2008-11-19-00.02.20.496011 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 9 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.829924 2008-11-19-00.02.20.528872 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 1 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.830849 2008-11-19-00.02.20.449521 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 6 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.839059 2008-11-19-00.02.20.492698 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 4 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.843122 2008-11-19-00.02.20.493764 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 10 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.19.844478 2008-11-19-00.02.20.499503 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 7 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.20.083580 2008-11-19-00.02.20.459874 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 2 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.20.195231 2008-11-19-00.02.20.454922 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 5 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.20.222706 2008-11-19-00.02.20.459483 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 8 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 LOAD 2008-11-19-00.02.20.222869 2008-11-19-00.02.20.868135 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 1 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.460441 2008-11-19-00.02.20.791962 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 2 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.489992 2008-11-19-00.02.20.661057 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 7 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.495294 2008-11-19-00.02.20.873008 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 5 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.496070 2008-11-19-00.02.20.742382 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 6 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.513766 2008-11-19-00.02.20.685750 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 4 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.526393 2008-11-19-00.02.20.675156 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 10 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.531163 2008-11-19-00.02.20.829576 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 9 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.537341 2008-11-19-00.02.20.713257 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 3 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.839878 2008-11-19-00.02.21.084649 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 8 PP03A8DL 6090858.2008-11-19-00.02.18.570277.0 BUILD 2008-11-19-00.02.20.883066 2008-11-19-00.02.21.143827 PLCY_RPT.RV_VIF_SM TSPCY70
edwasp edwp02 EDWP02 1 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.229639 2008-11-19-00.03.00.979805 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 10 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.238083 2008-11-19-00.03.00.983704 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 6 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.248511 2008-11-19-00.03.00.973224 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 4 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.250261 2008-11-19-00.03.00.971232 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 7 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.542755 2008-11-19-00.03.00.964926 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 3 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.549672 2008-11-19-00.03.00.967582 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 9 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.585876 2008-11-19-00.03.00.974125 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 2 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.660424 2008-11-19-00.03.00.981729 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 5 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.679167 2008-11-19-00.03.01.121790 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 8 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 LOAD 2008-11-19-00.03.00.680724 2008-11-19-00.03.00.966425 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 7 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.979796 2008-11-19-00.03.01.064096 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 8 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.980672 2008-11-19-00.03.01.091855 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 3 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.981723 2008-11-19-00.03.01.067041 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 4 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.983988 2008-11-19-00.03.01.064742 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 6 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.986718 2008-11-19-00.03.01.096628 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 9 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.989214 2008-11-19-00.03.01.085788 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 10 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.00.999050 2008-11-19-00.03.01.102833 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 5 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.01.132103 2008-11-19-00.03.01.191272 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 1 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.01.362555 2008-11-19-00.03.01.427459 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 2 PP03A8DL 4923404.2008-11-19-00.02.58.885675.0 BUILD 2008-11-19-00.03.01.472588 2008-11-19-00.03.01.542501 PLCY_RPT.RV_CIF_SM TSPCY70
edwasp edwp02 EDWP02 0 PP0342DL 5718288.2008-11-19-00.14.15.876530.0 LOAD 2008-11-19-00.14.16.169307 2008-11-19-00.14.16.305622 MDDB.TRNCTN_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0342DL 5718288.2008-11-19-00.14.15.876530.0 BUILD 2008-11-19-00.14.16.312567 2008-11-19-00.14.16.569363 MDDB.TRNCTN_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0342DL 2039862.2008-11-19-00.14.57.185249.0 LOAD 2008-11-19-00.14.57.360736 2008-11-19-00.14.57.417611 MDDB.PRCS_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0342DL 2039862.2008-11-19-00.14.57.185249.0 BUILD 2008-11-19-00.14.57.424661 2008-11-19-00.14.57.442925 MDDB.PRCS_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0341DL 4551084.2008-11-19-03.34.11.855921.0 LOAD 2008-11-19-03.34.12.694610 2008-11-19-03.34.13.120171 MDDB.TRNCTN_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0341DL 4551084.2008-11-19-03.34.11.855921.0 BUILD 2008-11-19-03.34.13.138966 2008-11-19-03.34.13.538798 MDDB.TRNCTN_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0341DL 6910400.2008-11-19-03.35.17.182007.0 LOAD 2008-11-19-03.35.17.456812 2008-11-19-03.35.17.551005 MDDB.PRCS_ALRT TSALR01
edwasp edwp02 EDWP02 0 PP0341DL 6910400.2008-11-19-03.35.17.182007.0 BUILD 2008-11-19-03.35.17.575893 2008-11-19-03.35.17.605481 MDDB.PRCS_ALRT TSALR01
You may want to develop SQL select statements to run against the OPERATIONS.DB2DIAG_STMM_INFORMATION table to gain insight into what DB2 automatic memory management is doing as well.
Conclusion
If you do not have sophisticated database monitoring software that captures information about DB2 table loads and index builds, you can capture this yourself from the DB2 diagnostic log files by using this script. This valuable source of information can be used by a DBA as evidence to show when table loads and index builds have run and how long they take to process. If the load step is just one part of a job then you will know how much time the database is spending on the load and how much time the job is spending doing other things. If you develop a dash board for application teams, users and management that gives them visibility into this information then they can monitor load history and performance over time and gain a better understanding about what is running and when.
If you have any questions related to this article, feel free to contact me, Mark Mulligan, at mark.mulligan.sr@sbcglobal.net and I will be glad to try to answer your questions.
About the author
Mark Mulligan is a DBA living in the Dallas-Fort Worth area of Texas. He has a Bachelor of Science Degree in Business Administration from Merrimack College in North Andover, Massachusetts, and 30 years of experience working with computer software design, development, maintenance, enhancement and support. He has 10 years of experience working with DB2 on servers running UNIX-like operating systems and 15 years of experience working with DB2 on Z/OS. Mark can be contacted at mark.mulligan.sr@sbcglobal.net.