DB2 Log Extraction Prerequisites
This topic briefs about the DB2 log extraction prerequisites.
In This Topic:
Introduction
This document provides brief information about the query log extraction from different DB2 versions.
DB2 Versions:
- DB2 Database (Linux) V11.5
- DB2 for z/OS (Mainframe based) V11.5
DB2 Database does not contain any SQL trace (SQL history) table. Administrator must create event monitor to capture the SQL trace for all queries.
CREATE EVENT MONITOR
Following steps allows administrator to create event monitor:
#Login in to DB2 server from Putty.
- connect to db
db2 connect to tpcds
- Create event monitor- make sure you’ve got write permissions to stated folder
db2 “CREATE EVENT MONITOR stmon FOR STATEMENTS WRITE TO FILE ‘/tmp'”
- Turn on event monitor
db2 SET EVENT MONITOR stmon STATE = 1
- Translate event monitor into readable file
db2evmon -path /tmp > /tmp/query_log.log
- Validate generated file
vi /tmp/query_log.log
- Turn off event monitor
db2 SET EVENT MONITOR stmon STATE 0
- Delete event monitor
db2 DROP EVENT MONITOR stmon
Export the generated readable file to local or any shared location. Refer to the below sample event log file.
DB2 for z/OS contains a SQL trace table called SYSIBM.SYSQUERY.
Following is the sample query to extract required information:
select ‘idwwm’ ||’~~’||
tb1.CLIENT_APPLNAME ||’~~’||
tb2.APPLICATION_ID ||’~~’||
tb1.CLIENT_USERID ||’~~’||
tb1.BINDTIME ||’~~’||
‘ ‘ ||’~~’||
tb2.TOTAL_CPU_TIME ||’~~’||
tb2.QUERY_COST_ESTIMATE ||’~~’||
tb2.DIRECT_READS + tb2.DIRECT_WRITES ||’~~’||
‘ ‘ ||’~~’||
‘ ‘ ||’~~’||
tb1.QUERYID ||’~~’||
‘ ‘ ||’~~’||
‘ ‘ ||’~~’||
‘ ‘ ||’~~’||
‘ ‘ ||’~~’||
tb2.elapsed_time_sec ||’~~’||
tb1.SCHEMA ||’~~’||
tb1.QUERYNO ||’~~’||
‘ ‘ ||’~~’||
tb1.SOURCE ||’~~’||
‘ ‘ ||’~~’||
tb1.STMTTEXT
from
SYSIBM.SYSQUERY tb1
join sysibmadm.mon_current_sql tb2
on (trim(tb1.STMTTEXT) = trim(tb2.stmt_text))
Note that, this query is not tested on DB2 for z/OS as we don’t have mainframe based DB2. This Query will be enhanced further to add more fields.
Total I/O Usage by Days
Save the results of this query in a CSV file:
select tb1.BINDTIME,
sum(tb2.DIRECT_READS + tb2.DIRECT_WRITES) AS TOTALIOREADS
from
SYSIBM.SYSQUERY tb1
join sysibmadm.mon_current_sql tb2
on (trim(tb1.STMTTEXT) = trim(tb2.stmt_text))
WHERE tb1.BINDTIME BETWEEN ‘2017/01/01’ AND ‘2017/05/29’ group by tb1.BINDTIME;
Replace ‘2017/01/01’ AND ‘2017/05/29’ with actual export assessment start and end date.
Database Object Count
Save its results in a CSV file.
select
DBNAME as databasename,
TYPE as tablekind,
count(name)
from SYSIBM.SYSTABLES
group by DBNAME,TYPE order by DBNAME;
Database Volume
This SQL will collect databases with volume equal or above 10GB. Save its results in a CSV file.
select a.TABNAME ,
a.CARD*(sum(b.AVGCOLLEN)+10)/1024/1024/1024 as Tablesize_in_GB
from
syscat.tables as a, syscat.columns as b
where
a.TABNAME = b.TABNAME
group BY a.TABNAME, a.CARD
ORDER BY 2 DESC;
Databases and Users
This query provides the total number of databases and users. Save its results in a CSV file.
select type,count(*) from SYSIBM.SYSDATABASE group by type
Leaplogic