find the complete sql of a really long sql and explain it

I had a system engineer responisble for an application contact me about a problematic sql statement,
he wanted me to have a look an explain it,
in his management software the statement was truncated.

I noted the tablenames mentioned in the truncated statement and decided that i would have a look on the DB2 level,

I thought of the table function mon_get_pkg_cache_stmt,
I ran a query filtering on the tablenames ,

db2 "select STMT_TEXT from table(mon_get_pkg_cache_stmt('D',null,null,-2)) where STMT_TEXT like '%TABLE1%TABLE2%TABLE3%'"

the output came back with the correct statement , but including this warning:
SQL0445W:the warning in knowledge center 

I tried to query the sysibmadm.mon_current_sql administrative view, to check whether I could see the complete sql statement,
but the value for the stmt_text was truncated as well.

Next I had a look in db2top,
and after some fiddling I used the following steps to find the complete sql statement ( 41kb long )

1. 1.       Start db2top
2. 2.       Type V ( upper case letter v )
3. 3.       Enter the default schema => the query was using not fully qualified tablenames
4. 4.       Locate the agent_id for the connection executing the sql ( this was identified with the earlier mon_current_sql view )
5. 5.       Type lower case a
6. 6.       Enter the agent_id
7. 7.       You will now see more information on this connection…
8. 8.       Type Upper case L, this will zoom in on the sql being executed and allow some extra commands
9. 9.       Type lower case e to launch a dynamic explain of the query
10. 1.    A file is opened  in vi containing the query, the explain plan and more information on the execution plan and indexes being used
11.       You can save this file be typing :w <full path for new file>/filename

     from the file generated above I had both the explain information and the complete sql,

      and this allowed me to communicate the full sql to the system engineer, adn look into the issue myself .

Determing whether a reorg could benefit performance without using reorgchk

the scripts below can be used to determine whether over a certain interval ,
how much overflow accesses occur for  table in that interval.


I created this script after reading the blog article on reorg and reorgchk by Ember Crooks,
and the comment by Scott hayes on this article.
Ember's Article on Reorg
Scott hayes' company website where I learned the formula below


thanks to both people mentioned above, I was able to write this script that might help you.

Step1 create the baseline table
Step2 insert the baseline into the baseline table
Step3 wait the interval
Step 4 determine the delta values over the interval
Step5 evaluate the metrics
step6 reorg the table


#!/usr/bin/ksh
#
# Usage         :
#
# Description   :
#
# Pre-requisites:
#
# Author        : Albert Mukendi Raf Mathijs
#
# Version       : 1.1
#
# Date          : 2012-XX-XX
#
# Information   :
#
#
# Begin Funcions
#

function Usage
{
   echo "---------------------------------------------------------------------------------------------------------"
   echo "Usage : $(basename $0) DATABASE INTERVAL_in_seconds"
   echo
}

# Load common functions
. /db2scripts/common_script/ksh_functions

# Check unintialized variables
set -u

# Check input parameter
readonly NbInputParmReq=2
[[ $# != ${NbInputParmReq} ]] && Usage && FatalError "The script expects ${NbInputParmReq} input parameters and receives $#"

# Initialisation and declaration of variables
readonly BASEPATH=
readonly LOGFILE=$(basename $0)_${DB2INSTANCE}_$(hostname)_$(date +%Y-%m-%d-%H.%M.%S).log
integer RC_CMD=0
readonly DATABASE=$1
readonly INTERVAL=$2

# Action
{
MsgStart
db2 connect to ${DATABASE}
print "create baseline tables"
db2 -v "declare global temporary table session.mon_table_baseline as (select current timestamp as                                                                                                           /dev/null
print " delete data from baseline table ( just in case )"
db2 -x "delete from session.mon_table_baseline"  > /dev/null
print "insert baseline data into baseline table"
db2 -v "insert into session.mon_table_baseline select current timestamp as snapshot_timestamp, a.
print " wait the given interval ( ${INTERVAL} seconds )"
sleep ${INTERVAL}
print " done sleeping"
#print " collect the delta information and return the wanted information"
print " Overflow Related Metrics "
print " ------------------------ "
db2 -v " select timestampdiff(2,char(timestamp(current timestamp)-b.snapshot_timestamp)) as snaps                                                                                                            TABNAME,c.DATA_OBJECT_L_PAGES as Logical_pages_on_disk,c.OVERFLOW_ACCESSES-b.OVERFLOW_ACCESSES a                                                                                                            /( c.rows_read - b.rows_read) as PCT_OVERFLOW_ACCESSES_ROWS_READ from table(mon_get_table(NULL,N                                                                                                           R=b.MEMBER where c.rows_read-b.rows_read > 0  order by 5,6,7 desc fetch first 50 rows only"

MsgSuccess "End of the script - `date`"
#} > ${LOGFILE}
} | tee ${LOGFILE}
#}


# History:
# --------
# 1.1 2012-XX-XX Creation

# To do:
# ------

In memory metrics of db2, working with baseline tables

in my previous blog entry I rewrote some scripts that scott hayes published in a blog post on the IDUG website.

my version of those scripts would give you feedback on the metrics based on the data collected since the start of the instance.

in this entry I will put the same scripts but modified so they give you the metrics based on a baseline you setup in advance.

#!/usr/bin/ksh

function Usage

{

   echo "---------------------------------------------------------------------------------------------------------"

   echo "Usage : $(basename $0) DATABASE INTERVAL_in_seconds"

   echo

}

readonly LOGFILE=$(basename $0)_${DB2INSTANCE}_$(hostname)_$(date +%Y-%m-%d-%H.%M.%S).log

readonly DATABASE=$1

readonly INTERVAL=$2

{db2 -v " declare global temporary table session.mon_get_table_baseline as (select current timestamp as snapshot_timestamp, a.*  from table(mon_get_table(null,null,-2)) a ) with no data on commit preserve rows"

db2 -v "insert into session.mon_get_table_baseline select current timestamp as snapshot_timestamp, a.*  from table(mon_get_table(null,null,-2)) a"

WAIT THE TIMEFRAME YOU WANT TO ANALYZE
sleep $INTERVAL

db2 -v "with delta_values as (
select substr(a.tabschema,1,20) as TABSCHEMA,

substr(a.tabname,1,25) as TABNAME,
--a.member,
sum(a.rows_read) - sum(b.rows_read) as delta_rows_read,
((select sum(c.rows_read) from table(mon_get_table(null,null,-2)) c ) - (select sum(d.rows_read) from session.mon_get_table_baseline d)) as delta_total_rows_read
from table(mon_get_table(null,null,-2)) a join  session.mon_get_table_baseline b on a.tabschema=b.tabschema and a.tabname=b.tabname and a.member=b.member and a.DATA_PARTITION_ID=b.DATA_PARTITION_ID
group by a.tabschema,a.tabname)

select substr(e.tabschema,1,20) as TABSCHEMA, 

substr(e.tabname,1,25) as TABNAME,
--e.member,

e.delta_rows_read,
e.delta_total_rows_read,
decimal(e.delta_rows_read,31,4)/e.delta_total_rows_read * 100 as pct_of_rows_read
from delta_values e
order by 
--pct_of_rows_read,
e.delta_rows_read desc fetch first 20 rows only"
} | tee ${LOGFILE}

DB2 Performance Queries: Stolen from Scott hayes and changed to use mon_get_functions

scott hayes DB2 performance article on IDUG 


first script to check which tables are read the most:

Figure 1 – Table Weights

select substr(a.tabschema,1,20) as TABSCHEMA,

substr(a.tabname,1,25) as TABNAME,

a.rows_read as RowsRead,

CAST((((A.ROWS_READ) * 100.0)

/ (Select (SUM(Z.ROWS_READ) + 1.0)

FROM table(mon_get_table(null,null,-2)) Z

WHERE A.MEMBER = Z.MEMBER

)) AS DECIMAL(5,2)) AS PCT_DB_TB_ROWSREAD

from table(mon_get_table(null,null,-2)) a

order by a.rows_read desc fetch first 20 rows only;

Figure 4 – SQL Heavy CPU Consumers

SELECT

CAST(

A.TOTAL_CPU_TIME


/ A.NUM_EXECUTIONS

AS DECIMAL (15,0)) AS AVG_CPU_TIME,

CAST (A.NUM_EXECUTIONS AS INTEGER) AS NUM_EXECS,

CAST(((

A.TOTAL_CPU_TIME
* 100.0)

/ Coalesce((Select SUM(TOTAL_CPU_TIME)

FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) B

WHERE A.MEMBER = B.MEMBER and Member=0

),1)) AS DECIMAL(5,2)) AS PCT_CPU_TIME,

SUBSTR(A.STMT_TEXT,1,110) AS CPU_SUCKING_SQL

FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) A

WHERE A.NUM_EXECUTIONS > 0

ORDER BY A.MEMBER ASC, 3 DESC, 1 DESC FETCH FIRST 25 ROWS ONLY

Figure 5 – SQL Heavy I/O Consumers

SELECT CAST (A.NUM_EXECUTIONS AS INTEGER) AS NUM_EXECS,

CAST( (A.ROWS_READ + 0.001) / (A.NUM_EXECUTIONS + 0.001)

AS DECIMAL (13,4)) AS AVG_ROWS_READ,

CAST((((A.ROWS_READ) * 100.0)

/ (Select (SUM(B.ROWS_READ) + 1.0)

FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) B

WHERE A.MEMBER = B.MEMBER

)) AS DECIMAL(5,2)) AS PCT_ROWS_READ,

SUBSTR(A.STMT_TEXT,1,110) AS HEAVY_READER_SQL

FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) A

WHERE A.ROWS_READ > 0 AND A.NUM_EXECUTIONS > 0

ORDER BY A.MEMBER ASC, 3 DESC, 2 DESC FETCH FIRST 25 ROWS ONLY;

I'll work on the other scripts too...

monitor DB2 reorg with db2pd + awk

If you want to monitor the progress of reorgs running in db2, you can use the following command:

db2pd -db tmdb -reorgs | awk '$5=="n/a"&&$4!="n/a" {print $2 " : " $9/$10*100 " %"}'

output is:

TABLENAME : Percentage complete %

Stored Procedure wlm_set_client_info gives error sql1131n

We are using the stored procedure wlm_set_client_info to identify which reports are running on our datawarehouse, we've setup Microstrategy following the guide on the DB2 best practices website:
DB2BP_WLM_Setting_Client_Info_Fields.pdf

This allows us to link the executing sql with a report on the microstrategy level,
facilitating the communication between the Microstrategy administrator and the DBA team,
this works for other multier environments as well, and is very useful in my opinion,

The issue described in the subject occurred when our system engineer was setting up a drp method for our datawarehouse,
during this time our file systems were unmounted and remounted,

after this intervention our microstrategy administrator complained that reports no longer executed and gave the error message when the call to the stored procedure wlm_set_client_info was mad by Microstrategy,

after some investigation we discovered that not only this stored procedure , but ALL stored procedures showed the same error message, SQL1131N
SQL1131N error code in the information center

in the end we discovered that during the system intervention, the system engineer assumed that the fenced user of our db2 instance was not used, so he removed this user,
db2 started and worked correctly except for the stored procedures that were to be executed by the fenced user,

once the fenced user was created again with the correct UID,
the system behaved normally as before.

Check if the next value of a Identity column is lower than max value of that column in table on DB2/Z

compare result of query:
select a.DCREATOR as tabschema,a.dname as tabname, a.dcolname as column_name,a.dtype as TYPE_OF_DEPENCY,maxassignedval as last_value from sysibm.syssequencesdep  a join sysibm.syssequences b on a.bsequenceid=b.sequenceid where dtype='I'

 with:

select max(<column name>) from <table schema>.<table name>;

If the first query it's value is lower than the second value, this could indicate that you will encounter primary key conflict in this column