tag:blogger.com,1999:blog-7606540225152513782023-11-15T17:52:52.215+01:00DB2 Knowledge Base Rafblog to keep track of DB2 related issues I experienced and how I solved them, less intended for the public, more to use with google when the problem reoccurs...raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-760654022515251378.post-38068292829252802362015-04-01T10:31:00.001+02:002015-04-01T10:31:50.235+02:00find the complete sql of a really long sql and explain itI had a system engineer responisble for an application contact me about a problematic sql statement,<br />
he wanted me to have a look an explain it,<br />
in his management software the statement was truncated.<br />
<br />
I noted the tablenames mentioned in the truncated statement and decided that i would have a look on the DB2 level,<br />
<br />
I thought of the table function mon_get_pkg_cache_stmt,<br />
I ran a query filtering on the tablenames ,<br />
<br />
db2 "select STMT_TEXT from table(mon_get_pkg_cache_stmt('D',null,null,-2)) where STMT_TEXT like '%TABLE1%TABLE2%TABLE3%'"<br />
<br />
the output came back with the correct statement , but including this warning:<br />
<span style="background-color: white;">SQL0445W:</span><a href="http://www-01.ibm.com/support/knowledgecenter/?lang=en#!/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00445w.html?cp=SSEPGG_9.7.0%2F2-6-27-0-287">the warning in knowledge center</a><span style="background-color: #dadada;"> </span><br />
<span style="background-color: #dadada;"><br /></span>
<span style="background-color: white;">I tried to query the sysibmadm.mon_current_sql administrative view, to check whether I could see the complete sql statement,</span><br />
<span style="background-color: white;">but the value for the stmt_text was truncated as well.</span><br />
<span style="background-color: white;"><br /></span>
<span style="background-color: white;">Next I had a look in db2top,</span><br />
<span style="background-color: white;">and after some fiddling I used the following steps to find the complete sql statement ( 41kb long )</span><br />
<span style="background-color: white;"><br /></span>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
</div>
<ol>
<li><span style="text-indent: -18pt;">1.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">Start db2top</span></li>
<li><span style="text-indent: -18pt;">2.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">Type V ( upper case letter v )</span></li>
<li><span style="text-indent: -18pt;">3.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">Enter the default schema => the query was using not fully qualified tablenames</span></li>
<li><span style="text-indent: -18pt;">4.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">Locate the agent_id for the connection executing
the sql ( this was identified with the earlier mon_current_sql view )</span></li>
<li><span style="text-indent: -18pt;">5.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">Type lower case a</span></li>
<li><span style="text-indent: -18pt;">6.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">Enter the agent_id</span></li>
<li><span style="text-indent: -18pt;">7.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">You will now see more information on this
connection…</span></li>
<li><span style="text-indent: -18pt;">8.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">Type Upper case L, this will zoom in on the sql
being executed and allow some extra commands</span></li>
<li><span style="text-indent: -18pt;">9.</span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;">
</span><span style="text-indent: -18pt;">Type lower case e to launch a dynamic explain of
the query</span></li>
<li><span style="text-indent: -18pt;">1. </span><span style="text-indent: -18pt;">A file is opened in vi containing the
query, the explain plan and more information on the execution plan and indexes
being used</span></li>
<li><span style="font-stretch: normal; text-indent: -18pt;"> </span><span style="font-size: 7pt; font-stretch: normal; text-indent: -18pt;"> </span><span style="text-indent: -18pt;">You can save this file be typing :w <full
path for new file>/filename</span></li>
</ol>
<div>
<br /></div>
<div>
from the file generated above I had both the explain information and the complete sql,</div>
<div>
and this allowed me to communicate the full sql to the system engineer, adn look into the issue myself .</div>
<!--[if !supportLists]--><o:p></o:p><br />
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<br />
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<o:p></o:p></div>
<span style="background-color: white;"><br /></span>
<br />raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-72855864338032303332015-03-17T11:23:00.002+01:002015-03-17T11:23:52.637+01:00Determing whether a reorg could benefit performance without using reorgchk<br />
<br />
<br />
the scripts below can be used to determine whether over a certain interval ,<br />
how much overflow accesses occur for table in that interval.<br />
<br />
<br />
I created this script after reading the blog article on reorg and reorgchk by Ember Crooks,<br />
and the comment by Scott hayes on this article.<br />
<a href="http://ember%27s%20article%20on%20reorg/">Ember's Article on Reorg</a><br />
<a href="http://www.dbisoftware.com/blog/db2_performance.php?id=116">Scott hayes' company website where I learned the formula below</a><br />
<br />
<br />
thanks to both people mentioned above, I was able to write this script that might help you.<br />
<br />
Step1 create the baseline table<br />
Step2 insert the baseline into the baseline table<br />
Step3 wait the interval<br />
Step 4 determine the delta values over the interval<br />
Step5 evaluate the metrics<br />
step6 reorg the table<br />
<br />
<br />
#!/usr/bin/ksh<br />
#<br />
# Usage :<br />
#<br />
# Description :<br />
#<br />
# Pre-requisites:<br />
#<br />
# Author : Albert Mukendi Raf Mathijs<br />
#<br />
# Version : 1.1<br />
#<br />
# Date : 2012-XX-XX<br />
#<br />
# Information :<br />
#<br />
#<br />
# Begin Funcions<br />
#<br />
<br />
function Usage<br />
{<br />
echo "---------------------------------------------------------------------------------------------------------"<br />
echo "Usage : $(basename $0) DATABASE INTERVAL_in_seconds"<br />
echo<br />
}<br />
<br />
# Load common functions<br />
. /db2scripts/common_script/ksh_functions<br />
<br />
# Check unintialized variables<br />
set -u<br />
<br />
# Check input parameter<br />
readonly NbInputParmReq=2<br />
[[ $# != ${NbInputParmReq} ]] && Usage && FatalError "The script expects ${NbInputParmReq} input parameters and receives $#"<br />
<br />
# Initialisation and declaration of variables<br />
readonly BASEPATH=<br />
readonly LOGFILE=$(basename $0)_${DB2INSTANCE}_$(hostname)_$(date +%Y-%m-%d-%H.%M.%S).log<br />
integer RC_CMD=0<br />
readonly DATABASE=$1<br />
readonly INTERVAL=$2<br />
<br />
# Action<br />
{<br />
MsgStart<br />
db2 connect to ${DATABASE}<br />
print "create baseline tables"<br />
db2 -v "declare global temporary table session.mon_table_baseline as (select current timestamp as /dev/null<br />
print " delete data from baseline table ( just in case )"<br />
db2 -x "delete from session.mon_table_baseline" > /dev/null<br />
print "insert baseline data into baseline table"<br />
db2 -v "insert into session.mon_table_baseline select current timestamp as snapshot_timestamp, a.<br />
print " wait the given interval ( ${INTERVAL} seconds )"<br />
sleep ${INTERVAL}<br />
print " done sleeping"<br />
#print " collect the delta information and return the wanted information"<br />
print " Overflow Related Metrics "<br />
print " ------------------------ "<br />
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"<br />
<br />
MsgSuccess "End of the script - `date`"<br />
#} > ${LOGFILE}<br />
} | tee ${LOGFILE}<br />
#}<br />
<br />
<br />
# History:<br />
# --------<br />
# 1.1 2012-XX-XX Creation<br />
<br />
# To do:<br />
# ------<br />
<div>
<br /></div>
raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-25675935811661152122015-03-16T16:01:00.001+01:002015-03-16T16:34:46.742+01:00In memory metrics of db2, working with baseline tablesin my previous blog entry I rewrote some scripts that scott hayes published in a blog post on the IDUG website.<br />
<br />
my version of those scripts would give you feedback on the metrics based on the data collected since the start of the instance.<br />
<br />
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.<br />
<br />
#!/usr/bin/ksh<br />
<div>
<br /></div>
<div>
<div>
function Usage</div>
<div>
{</div>
<div>
echo "---------------------------------------------------------------------------------------------------------"</div>
<div>
echo "Usage : $(basename $0) DATABASE INTERVAL_in_seconds"</div>
<div>
echo</div>
<div>
}</div>
</div>
<div>
<br /></div>
<div>
readonly LOGFILE=$(basename $0)_${DB2INSTANCE}_$(hostname)_$(date +%Y-%m-%d-%H.%M.%S).log</div>
<div>
<div>
readonly DATABASE=$1</div>
<div>
readonly INTERVAL=$2</div>
</div>
<div>
<br /></div>
<div>
{db2 -v " declare global temporary table session.mon_get_table_baseline as (select current timestamp as snapshot_timestamp, a.* <span style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13.1999998092651px; line-height: 18.4799995422363px;">from table(mon_get_table(null,null,-2)) a ) with no data on commit preserve rows"</span><br />
<span style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13.1999998092651px; line-height: 18.4799995422363px;"><br /></span>
<span style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13.1999998092651px; line-height: 18.4799995422363px;">db2 -v "insert into </span>session.mon_get_table_baseline select current timestamp as snapshot_timestamp, a.* <span style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13.1999998092651px; line-height: 18.4799995422363px;">from table(mon_get_table(null,null,-2)) a"</span></div>
<div>
<span style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13.1999998092651px; line-height: 18.4799995422363px;"><br /></span>
<span style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13.1999998092651px; line-height: 18.4799995422363px;">WAIT THE TIMEFRAME YOU WANT TO ANALYZE</span><br />
<span style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13.1999998092651px; line-height: 18.4799995422363px;">sleep $INTERVAL</span><br />
<span style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13.1999998092651px; line-height: 18.4799995422363px;"><br /></span></div>
<div>
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="background-color: white; font-size: 13.1999998092651px; line-height: 18.4799995422363px;">db2 -v "</span><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">with delta_values as (</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">select substr(a.tabschema,1,20) as TABSCHEMA,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;"><br /></span></span>
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">substr(a.tabname,1,25) as TABNAME,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">--a.member,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">sum(a.rows_read) - sum(b.rows_read) as delta_rows_read,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">((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</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">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</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">group by a.tabschema,a.tabname)</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;"><br /></span></span>
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">select substr(e.tabschema,1,20) as TABSCHEMA, </span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;"><br /></span></span>
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">substr(e.tabname,1,25) as TABNAME,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">--e.member,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;"><br /></span></span>
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">e.delta_rows_read,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">e.delta_total_rows_read,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">decimal(e.delta_rows_read,31,4)/e.delta_total_rows_read * 100 as pct_of_rows_read</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">from delta_values e</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">order by </span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">--pct_of_rows_read,</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">e.delta_rows_read desc fetch first 20 rows only"</span></span><br />
<span style="color: #666666; font-family: Trebuchet MS, Trebuchet, Verdana, sans-serif;"><span style="font-size: 13.1999998092651px; line-height: 18.4799995422363px;">} | tee ${LOGFILE}</span></span></div>
raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-62924960308509670382015-03-12T15:38:00.001+01:002015-03-12T15:56:15.842+01:00DB2 Performance Queries: Stolen from Scott hayes and changed to use mon_get_functions<a href="http://www.idug.org/p/bl/et/blogid=278&blogaid=351">scott hayes DB2 performance article on IDUG </a><br />
<br />
<br />
first script to check which tables are read the most:<br />
<h3 style="background: rgb(51, 51, 51); border-color: rgb(204, 204, 204); border-style: solid; border-width: 1px 0px; color: white; font-family: Arial, Verdana, geneva, sans-serif; font-size: 1em; margin: 0px; outline: 0px; padding: 0.2em; text-align: center; vertical-align: baseline;">
Figure 1 – Table Weights</h3>
select substr(a.tabschema,1,20) as TABSCHEMA,<br />
<br />
substr(a.tabname,1,25) as TABNAME,<br />
<br />
a.rows_read as RowsRead,<br />
<br />
CAST((((A.ROWS_READ) * 100.0)<br />
<br />
/ (Select (SUM(Z.ROWS_READ) + 1.0)<br />
<br />
FROM table(mon_get_table(null,null,-2)) Z<br />
<br />
WHERE A.MEMBER = Z.MEMBER<br />
<br />
)) AS DECIMAL(5,2)) AS PCT_DB_TB_ROWSREAD<br />
<br />
from table(mon_get_table(null,null,-2)) a<br />
<br />
order by a.rows_read desc fetch first 20 rows only;<br />
<br />
<h3 style="background: rgb(51, 51, 51); border-color: rgb(204, 204, 204); border-style: solid; border-width: 1px 0px; color: white; font-family: Arial, Verdana, geneva, sans-serif; font-size: 1em; margin: 0px; outline: 0px; padding: 0.2em; text-align: center; vertical-align: baseline;">
Figure 4 – SQL Heavy CPU Consumers</h3>
SELECT<br />
<br />
CAST(<br />
<br />
A.TOTAL_CPU_TIME<br />
<br />
<br />
/ A.NUM_EXECUTIONS<br />
<br />
AS DECIMAL (15,0)) AS AVG_CPU_TIME,<br />
<br />
CAST (A.NUM_EXECUTIONS AS INTEGER) AS NUM_EXECS,<br />
<br />
CAST(((<br />
<br />
A.TOTAL_CPU_TIME<br />
* 100.0)<br />
<br />
/ Coalesce((Select SUM(TOTAL_CPU_TIME)<br />
<br />
FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) B<br />
<br />
WHERE A.MEMBER = B.MEMBER and Member=0<br />
<br />
),1)) AS DECIMAL(5,2)) AS PCT_CPU_TIME,<br />
<br />
SUBSTR(A.STMT_TEXT,1,110) AS CPU_SUCKING_SQL<br />
<br />
FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) A<br />
<br />
WHERE A.NUM_EXECUTIONS > 0<br />
<br />
ORDER BY A.MEMBER ASC, 3 DESC, 1 DESC FETCH FIRST 25 ROWS ONLY<br />
<h3 style="background: rgb(51, 51, 51); border-color: rgb(204, 204, 204); border-style: solid; border-width: 1px 0px; color: white; font-family: Arial, Verdana, geneva, sans-serif; font-size: 1em; margin: 0px; outline: 0px; padding: 0.2em; text-align: center; vertical-align: baseline;">
Figure 5 – SQL Heavy I/O Consumers</h3>
SELECT CAST (A.NUM_EXECUTIONS AS INTEGER) AS NUM_EXECS,<br />
<br />
CAST( (A.ROWS_READ + 0.001) / (A.NUM_EXECUTIONS + 0.001)<br />
<br />
AS DECIMAL (13,4)) AS AVG_ROWS_READ,<br />
<br />
CAST((((A.ROWS_READ) * 100.0)<br />
<br />
/ (Select (SUM(B.ROWS_READ) + 1.0)<br />
<br />
FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) B<br />
<br />
WHERE A.MEMBER = B.MEMBER<br />
<br />
)) AS DECIMAL(5,2)) AS PCT_ROWS_READ,<br />
<br />
SUBSTR(A.STMT_TEXT,1,110) AS HEAVY_READER_SQL<br />
<br />
FROM table(mon_get_pkg_cache_stmt('D',null,null,-2)) A<br />
<br />
WHERE A.ROWS_READ > 0 AND A.NUM_EXECUTIONS > 0<br />
<br />
ORDER BY A.MEMBER ASC, 3 DESC, 2 DESC FETCH FIRST 25 ROWS ONLY;<br />
<br />
I'll work on the other scripts too...<br />
<br />
<br />
<br />
<br />raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-51243945087762719782014-12-30T11:29:00.001+01:002014-12-30T11:29:06.661+01:00monitor DB2 reorg with db2pd + awkIf you want to monitor the progress of reorgs running in db2, you can use the following command:<br />
<br />
db2pd -db tmdb -reorgs | awk '$5=="n/a"&&$4!="n/a" {print $2 " : " $9/$10*100 " %"}'<br />
<br />
output is:<br />
<br />
TABLENAME : Percentage complete %<br />
<br />
<br />raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-49902370254458245282014-05-08T09:47:00.002+02:002014-05-08T14:22:12.039+02:00Stored Procedure wlm_set_client_info gives error sql1131nWe 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:<br />
<a href="http://www.ibm.com/developerworks/community/wikis/form/anonymous/api/wiki/0fc2f498-7b3e-4285-8881-2b6c0490ceb9/page/67738be7-6e58-4c6e-96aa-fe5d0f7e9b11/attachment/c967f5ca-36a2-472a-8e3c-4b55210c2842/media/DB2BP_WLM_Setting_Client_Info_Fields.pdf">DB2BP_WLM_Setting_Client_Info_Fields.pdf</a><br />
<br />
This allows us to link the executing sql with a report on the microstrategy level,<br />
facilitating the communication between the Microstrategy administrator and the DBA team,<br />
this works for other multier environments as well, and is very useful in my opinion,<br />
<br />
The issue described in the subject occurred when our system engineer was setting up a drp method for our datawarehouse,<br />
during this time our file systems were unmounted and remounted,<br />
<br />
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,<br />
<br />
after some investigation we discovered that not only this stored procedure , but ALL stored procedures showed the same error message, SQL1131N<br />
<a href="http://sql1131n%20error%20code%20in%20the%20information%20center/">SQL1131N error code in the information center</a><br />
<br />
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,<br />
db2 started and worked correctly except for the stored procedures that were to be executed by the fenced user,<br />
<br />
once the fenced user was created again with the correct UID,<br />
the system behaved normally as before.<br />
<br />
<br />
<br />raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-72175919109988050542014-05-07T15:23:00.001+02:002014-05-07T15:43:44.050+02:00Check if the next value of a Identity column is lower than max value of that column in table on DB2/Zcompare result of query:<br />
select a.DCREATOR as tabschema,a.dname as tabname, a.dcolname as column_name,a.dtype as TYPE_OF_DEPENCY,<b>maxassignedval </b>as last_value from sysibm.syssequencesdep a join sysibm.syssequences b on a.bsequenceid=b.sequenceid where dtype='I'<br />
<br />
with:<br />
<br />
select max(<column name>) from <table schema>.<table name>;<br />
<br />
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 columnraf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-55716618049842750972013-10-25T14:40:00.000+02:002013-10-25T14:40:12.441+02:00sysibmadm.dbmcfg gives error sql0444n reason code 4whilst developping a script to collect database manager and database configuration values from all our servers, we ran into an issue on 2 of our servers recently migrated to DB2 9.7 fp8.<br />
<br />
when we accessed sysibmadm.dbmcfg we received error:<br />
SQL0444N Routine "*_GET_CFG" (specific name "DBM_GET_CFG") is implemented<br />
with code in library or path ".../sqllib/bin/routine/db2dbroutext", function<br />
"*m_get_cfg" which cannot be accessed. Reason code: "4". SQLSTATE=42724<br />
<br />
if we accessed sysibmadm.dbcfg , this administrative view worked fine,<br />
<br />
when we compared the two views / routines behind ...<br />
<br />
db2 "select routinename,FENCED from syscat.routines where ROUTINENAME like '%GET_CFG'"<br />
<br />
ROUTINENAME FENCED<br />
-------------------------------------------------------------------------------------------------------------------------------- ------<br />
DBM_GET_CFG Y<br />
DB_GET_CFG N<br />
<br />
2 record(s) selected.<br />
<div>
<br /></div>
<div>
we noticed that the DBMCFG routing is fenced and the DBCFG one is not fenced.</div>
<div>
<br /></div>
<div>
after some time we investigated the path to the library ( as you can find in the doc for reason code 4 )</div>
<div>
and we noticed the real path( not symlink in sqllib ) was accessible to the instance owner, but not to the fenced user.</div>
<div>
<br /></div>
<div>
i.e <db2 instance owner home folder>/sqllib/bin/routine/db2dbroutext = OK</div>
<div>
<db2_install_path>/bin =NOK</div>
<div>
<br /></div>
<div>
granting rights for the groups and for all users to the path containing the db2 binaries,</div>
<div>
chmod 775 db2_install_path</div>
<div>
<br /></div>
<div>
after this the fenced user could access the library path containing the routine definition,</div>
<div>
and we could use sysibmadm.dbmcfg </div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-90112472100096489852013-10-24T14:35:00.000+02:002013-10-24T15:11:55.933+02:00fcm_parallelism + sql6031nafter monitoring our DPF database for our datawarehouse I noticed that a lot of wait time was spent in fcm communication, so I decided to try out the new fcm_parallelism parameter that exists since DB2 9.7 fp6<br />
<br />
<a href="http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.wn.doc/doc/c0060572.html?resultof=%22%66%63%6d%5f%70%61%72%61%6c%6c%65%6c%69%73%6d%22%20">FCM parallelism support added</a><br />
<br />
to test this out I performed the following actions:<br />
db2 get dbm cfg | grep -i fcm_parallelism<br />
Inter-node comm. parallelism (FCM_PARALLELISM) = 1<br />
<div>
<br /></div>
db2 attach to <instance><br />
db2 update dbm cfg using FCM_PARALLELISM 2<br />
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed<br />
successfully.<br />
SQL1362W One or more of the parameters submitted for immediate modification<br />
were not changed dynamically. Client changes will not be effective until the<br />
next time the application is started or the TERMINATE command has been issued.<br />
Server changes will not be effective until the next DB2START command.<br />
<div>
<br /></div>
<div>
as stated above you have to restart the instance to activate this parameter,</div>
<div>
so I force all connections and stopped db2:</div>
<div>
db2 force applications all</div>
<div>
<div>
> db2stop</div>
<div>
24/10/2013 14:08:43 0 0 SQL1064N DB2STOP processing was successful.</div>
<div>
24/10/2013 14:08:45 2 0 SQL1064N DB2STOP processing was successful.</div>
<div>
24/10/2013 14:08:45 3 0 SQL1064N DB2STOP processing was successful.</div>
<div>
24/10/2013 14:08:45 4 0 SQL1064N DB2STOP processing was successful.</div>
<div>
24/10/2013 14:08:46 6 0 SQL1064N DB2STOP processing was successful.</div>
<div>
24/10/2013 14:08:46 7 0 SQL1064N DB2STOP processing was successful.</div>
<div>
24/10/2013 14:08:47 5 0 SQL1064N DB2STOP processing was successful.</div>
<div>
24/10/2013 14:08:47 8 0 SQL1064N DB2STOP processing was successful.</div>
<div>
24/10/2013 14:08:49 1 0 SQL1064N DB2STOP processing was successful.</div>
<div>
SQL1064N DB2STOP processing was successful.</div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
when I tried to restart with fcm_parallelism set to 2 I received the following error,</div>
<div>
<br /></div>
<div>
<div>
> db2start</div>
<div>
SQL6031N Error in the db2nodes.cfg file at line number "6". Reason code "12".</div>
</div>
<div>
<br /></div>
<div>
<div>
> db2 "? SQL6031N"</div>
</div>
<div>
<br /></div>
<div>
<div>
SQL6031N Error in the db2nodes.cfg file at line number "<line>". Reason</div>
<div>
code "<reason-code>".</div>
<div>
<br /></div>
<div>
Explanation:</div>
<div>
<br /></div>
<div>
The statement cannot be processed because of a problem with the</div>
<div>
db2nodes.cfg file, as indicated by the following reason codes:</div>
</div>
<div>
<br /></div>
<div>
<div>
12</div>
<div>
<br /></div>
<div>
The port value at line "<line>" of the db2nodes.cfg file in the</div>
<div>
sqllib directory is not in the valid port range defined for</div>
<div>
your DB2 instance id in the services file (/etc/services on</div>
<div>
UNIX-based systems).</div>
<div>
<br /></div>
</div>
<div>
<div>
12</div>
<div>
<br /></div>
<div>
Ensure that you only use port values that are specified in the</div>
<div>
services file (/etc/services file on UNIX-based systems) for</div>
<div>
your instance.</div>
<div>
<br /></div>
</div>
<div>
So I sent a request to our sysadmins to add extra ports to the /etc/services file,</div>
<div>
hopefully this will allow us to exploit the fcm parallelism...</div>
<div>
<br /></div>
<div>
previously we had 9 ports defined in /etc/services,<br />
AIX team increased this to 18,<br />
then db2 will start with fcm_parallelism =2<br />
<br />
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-18919897201799749332013-10-16T10:49:00.000+02:002013-10-16T10:55:08.188+02:00DB2ATS + Transaction log full + backup fails I had the backup team contact me because a backup had failed because of a transaction log full.<br />
<br />
when I investigated this transaction log issue, the connection was held by a job launch by the DB2ATS ( db2 automatic task scheduler )<br />
<br />
I tried to follow the action suggested in the db2diag.log trying to force the connection mentioned there,<br />
but for some reason you are not allowed to force ATS connections ( I feel this is not good , If I decide to kill a connection it's my responsibility :)).<br />
<br />
I tried deactivating the automated task scheduler using db2set<br />
<br />
db2set DB2_ATS_ENABLE=NO<br />
<br />
however this did not solve my problem,<br />
<br />
after some time I decided to see if db2pd could help me,<br />
and rememberd some searches we did in the past for deadlatch situations when a database was hanging,<br />
the database was actually hanging but I was out of options.<br />
<br />
so I launched db2pd -latches<br />
and I saw a huge list of latches:<br />
<br />
all of them of this type:<br />
SQLO_LT_sqlerFmpRow__ipcLatch<br />
<br />
I did a search on google an luckily a support link came out;<br />
<a href="http://www-01.ibm.com/support/docview.wss?uid=swg1IC76825">IC76825: THREADED DB2FMP PROCESS LOOPS IN ITS SIGNAL HANDLER WHEN IT RECEIVES NESTED SIGNALS</a><br />
<br />
in there they said to check for a db2fmp process consuming CPU,<br />
<br />
so I did a ps -ef | grep db2fmp | grep <instance owner><br />
<br />
and then did a db2fmpterm <pid of the db2fmp of my instance><br />
<br />
after this the database got into a more normal state, all of the latches had dissappeared,<br />
and backup worked as it should ...<br />
<br />
<br />
<div>
<br /></div>
raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-1677416807120179322013-10-15T11:18:00.000+02:002013-10-15T11:18:01.867+02:00the networker config file<h2>
the networker config file</h2>
<div>
<ol>
<li>for backups</li>
<ol>
<li>all you need from the db2 command line is the backup server and backup client ( if you schedule your backup from the networker server or want to use nsrdb2sv /nsrdasv, you need more parameters)</li>
<ol>
<li>NSR_SERVER=backupservername</li>
<li>NSR_CLIENT=nameoftheclientdefined in networker</li>
<li>it's better to have a different log and data volume to avoid tape mount conflict that blcok the backup from completion </li>
</ol>
<li>if you forget an '@' sign you will get error sql2062n reason code 3</li>
</ol>
<li>for log archiving</li>
<ol>
<li>don't forget @ in front of the config file when configuring logarchopt1</li>
<li>if you change the config file, the new values are not taken up immediately,it's better to restart the db2vend processes (i.e. kill them )</li>
</ol>
</ol>
<div>
<br /></div>
</div>
raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-41905740081059010522013-10-11T15:44:00.003+02:002013-10-11T15:58:54.855+02:00nsrdb2rlog does not work with error message related to file privilegesnsrdb2rlog doe snot work with error message related to privileges<br />
<br />
<br />
<br />
nsrdb2rlog -a dbname -d . -C 1 -E 13867 -S 13856 -s networker_server<br />
Unable to create temporary configuration file please check permissions<br />
<div>
<br /></div>
<div>
<br /></div>
<div>
solution:</div>
<div>
<br /></div>
<div>
use truss to see which system call cause the error:</div>
<div>
truss nsrdb2rlog -a dbname -d . -C 1 -E 13867 -S 13856 -s networker_server</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
near the end of the output you can see that a certain file is not being created due to privileges issues,</div>
<div>
then you can correct the privileges on the folder to make it writable for the user you sue for the nsrdb2rlog command</div>
<div>
<br /></div>
<div>
<br /></div>
<br />raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-51246972886970317622013-10-11T14:08:00.000+02:002013-10-11T15:57:21.168+02:00DB2 restore seems to hang using backup saved on networkerDB2 restore seems to hang using backup saved on networker.<br />
<br />
check that the nsrladb is setup correctly ( no reference to a different server than the server you are trying to restore on)<br />
<br />
solution:<br />
<br />
<ol>
<li>stop networker client</li>
<li>rename /nsr/res/nsrladb</li>
<li>restart networker client</li>
</ol>
<div>
now the restore command should start processing.</div>
<br />
<br />
<br />raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0tag:blogger.com,1999:blog-760654022515251378.post-26219272979318777882013-09-24T16:25:00.001+02:002013-09-24T16:31:14.018+02:00nsrdb2rlog => Initialized session but failed to get object : due to file system fullwhen using nsrdb2rlog to retrieve logfiles from another host on a system, make sure the directory you write to is not full , all you get is a generic error which does not point to the file system full<br />
nsrdb2log -a <database name> -C logchain -d <destination> -E last logfile -N <node/member/partition number> -S start logfile -s <networker server><br />
with the logfiles stripped of extension.LOG and prefix S000..<br />
<blockquote class="tr_bq">
Initialized session but failed to get object</blockquote>
<br />
in my case this was the home folder of the instance owner of the instance I restored into...raf bloghttp://www.blogger.com/profile/04201001668198271549noreply@blogger.com0