Calendar of Posts

October 2014
M T W T F S S
« Jan    
 12345
6789101112
13141516171819
20212223242526
2728293031  

Contributors

Paul Mayhew dbmsguy.com

Ivan Beg
A $25 loan can change a life forever

db2pd command script – busiest tables

Have you ever wondered what the busiest tables are in your database?

The db2pd command using -tcbstats can provide a lot of insight about what is happening in your tables.  The below is a simple script that you can run on the command line or put in a ksh script to tell you the busiest tables.  The sample script below will tell you what tables have the most inserts, but it can be adjusted to tell you other pieces of information such as updates, deletes and reads.

You can refer to the manual for db2pd – in the manual, you can see where other stats are you can analyze.   Change the $10 to another value for other stats.

11=Updates
12=Deletes
8=Reads

db2pd -db dbname -tcbstats| awk '/TCB Table Stats/ { x =1} x==1 { print}' | awk '/^0x/ { print $10, $2}' | sort -rn| head -15

DB2 LUW V9.7 CUR_COMMIT database configuration option – buyer beware

While working on a deadlocking problem, I came across the cur_commit optoin that is now available in version 9.7.  This option is part of the ongoing efforts to make DB2 more Oracle compliant. 

After reading about the cur_commit database option, it seemed to be a fair solution to the problem.  We installed/upgraded our databases to v9.7 and enabled the option – our deadlocking problems were over.  Case closed.

Well, maybe not.  Here’s the rub.  If you read about what little information there is about cur_commit, you will find that the option works by reading logs to get the currently committed value if an update has been done.  This works great in theory and even in practice, but I found that during our performance tests, we suffered greatly.  Reading the logs does not seem to be especially efficient for these cases and our system suffered greatly. 

In analyzing system counters, I noted that ‘Log pages read’ (db snapshot) values were very high.  In response to this, I increased LOGBUFSZ value to something very large.  I started off at 4096 and this make no difference what-so-ever.  I am up to 20480 which is 80Mb  and things have mostly stabilized, but I am still seeing a lot of ‘log pages read’ from time to time and when that value goes up, our performance goes down.

So the use of ‘CUR_COMMIT’ option in 9.7 is not a cure-all for deadlocks and should be used with care.  I don’t think it should be turned on without some forethought and a good amount of performance testing.  The CUR_COMMIT option is really a bandaid option.  Most deadlocking problems are caused by bad application design and should be fix from that end if possible.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.config.doc/doc/r0053556.html

db2pd command – get info about db2

The db2pd command is a relatively new command (released with version 8.2) that will tell you a huge amount of information about your db2 system.  I first made myself familiar with it when looking for a way to get the status of HADR without going through a snapshot output.

db2pd -db sample -hadr – will tell you all you need to know about  your  hadr configuration status

The db2pd command can be run in two ways; you can run it interactively by entering db2pd and then whatever commands you want or you can simply enter the db2pd command with whatever parameters you want.  I’m not sure what the advantage of running it interactively is, but it’s there for you.

I find the output from some of the parameters very heavy indeed (some are just memory dumps for the most part).  You will find useful intormation however.

a few of the handier commands in my opinion are:
db2pd -db sample -logs
db2pd -db sample -locks
db2pd -db sample -dbcfg
db2pd -db sample -hadr

here is a really good tutorial on how to use db2pd for locks and the entire manual can be found in the command reference.

sql for sampling data from tables

Some time ago, I had the requirement to sample data from a table to do validation work.  Basically, we wanted to validate syncronized data with a base table, but could not afford to compare every row.  We had to come up with a way to ‘sample’ the data.  Each table we had to sample had a different number of rows and in addition to that were growing.  The mechanizm had to be simple, yet take into account table size.  I did not want to do a custom query for every table.  I wanted something somewhat generic – something that I could say check ‘x’ rows from this table, not check every ‘nth’ row which would mean that the result would grow over time.

The obvious first consideration was to number the rows and do it from that, but considering we were doing this in a program, I needed something a little more elegant.  Here is the sql I came up with. Continue reading → sql for sampling data from tables

Inline database shell scripts using awk

One of the things I find very powerful in my day to day work as a DBA, is the use of inline shell/awk scripts.  I use this technique when I need to do the same thing to many objects.  I often use it when I want something quick and dirty or create a base script to work from.

An example might be when I want to do row counts on every table, or maybe set up a reorg on all the tables in a database.

Here is the command string you can use to do such a thing:

db2 connect to sample

db2 -x “select tabschema, tabname from syscat.tables where tabschema=’DB2INST1′ and type=’T'” | awk ‘BEGIN{print “db2 connect to sample”} {print “db2 -v \” select count(*) from “$1″.”$2″\””}’ | ksh

The above inline script will select all the tables and return tablenames, then piping that into awk, we first print a connect statement and then print all the count statements.  All of this is then piped into a ksh.  The connect must be put there because a new instance of ksh is generated.

The base query can, of course, be customize to whatever suits your needs.

The pipe in to ksh is optional as you could just direct it to an output file and use it later and or make further modifications.

db2 batch scripts – tips and tricks – part 2

I’ve always been a little frustrated with db2 when running scripts and command lines with fact that when a command would run like insert, update or delete, you never knew how many rows you affected.  Some GUI products will tell you, but with command line it would just come back as being successful (or not) and it was up to you to validate what you really did with a subsequent query.

Being a DBA, I always feel the need to validate what I do especially with ad hoc statements to ensure that I have done what I expected.

Enter the m option on the db2 command line.  This is a relatively new switch that will cause db2 to return to you how many rows were affected. The m option was introduced with verison 9.

To do this from the command line:

db2 -m “delete from test” will return

  Number of rows affected : 18
DB20000I  The SQL command completed sucessfully.

To do this from a script:

db2 -tvmf test.db2

By doing this, you will have the number of rows affected at your finger tips, not more hoping or assuming or doing count before/count after!

Happy DBAing