SQL using lots of CPU

This is the easiest method I've found to use snapshot table functions to help tune SQL. This was a life saver after migrating a client from DB2 on Z/os to Linux.

This method uses snapshot table functions and finds the 10 statements using the most CPU. Then I recommend running the db2 advisor on the biggest users.

----First create this view into the snapshot table function
create view DB2_MON_DYN_SQL as select * from table(snapshot_dyn_sql('',-1)) as sntable;

----You can then run this statement out of a file to view the 10 highest CPU using sql statements.

select int(num_executions) as "Num_Execs",
decimal(total_usr_cpu_time,10,2)as "Usr_CPU_Time",
int(stmt_sorts/num_executions) as "Sorts_Per_Stmt",
total_exec_time/num_executions as "Avg Time (sec)",
rows_read as "Rows_Read",
rows_written as "Rows_Written",
substr(stmt_text,1,25) as "SQL_Stmt"
from db2_mon_dyn_sql
where num_executions > 10
order by 2 desc
fetch first 10 rows only;

error installing DB2 Express-C 9.1 on Centos-5.1

I was getting this error while executing the ./db2_install program.

The following library files could not be loaded by db2langdir in
/home/Owner/Desktop/exp/disk1/db2/Linux/install/.. /bin
--> libstdc++.so.5
--> libstdc++.so.5

This fixed it:

yum -y install compat-libstdc++-33

DB2 health check in Boston

I recently was sent to Boston for a DB2 Health Check on a version 7 database running on a Windows Cluster. Their database was tuned sufficiently for their app. I made the following recommendations to ease their pain points:
Spread tablespaces containers across multiple containers.
Regularly schedule utilities Reorg and Runstats.
Decrease backup time by using Incremental backups during the week and full backups on weekends.
Rather than copying backup images to a file share, backup directly to the fileshare using the UNC path in the backup command. Like this:
db2 backup db sample to \\server\db2share

Easy enough: Here I am at the Boston Garden!

Windows memory error in sunny Florida

I was just at a shop in sunny southern Florida last week. They have a DB2 V7.2 (no longer supported by IBM) database on windows 2k and are getting ready to migrate to V8.2 on Windows 2003 in the near future. Despite my suggestions it looks like they are staying on 32 bit.

I was brought in to do a health check because they are starting to get some very vague memory errors from Windows in the form of a pop-up box when 2 of their scheduled jobs overlap. When they click the 'OK' button on the pop-up box it occasionally causes the instance to shutdown. The only problem is that there is never any information logged into the diag.log nor is there any dump or trap files created. After thorough review and attempts to recreate the problem, I couldn't find anything with the DB2 environment that looked to be the cause.

I suggested some ways to get their jobs running faster and ways they could conserve memory on their system. One suggestion is to set the db2set registry variable of DB2MEMMAXFREE to 2MB which lets the idle agents release most of the memory they have ahold of and keep only 2MB (the default is 8MB). I also showed them how to capture and format a trace when that memory error pops up again and they click the 'OK' button.

One unexpected issue I found was security. They are using AUTHENTICATION = CLIENT and TRUSTALLCLIENTS=YES. I suggested some ways to lock down their security a little better at the authentication layer and the database and table level.

Has anyone out there ever run into a memory error such as this?

Hello DB2 World

Hello DB2 World!

I'm a DB2 consultant for Xtivia Inc. Our company is a Database Consulting group (among other things, check out www.xtivia.com) supporting Informix, DB2, SQL Server, Oracle and Sybase.

Check out http://www.virtual-dba.com/ to see our cool remote DBA services. I've been working with DB2 LUW for 7 years and I get to help shops out with DB2 remotely and at their office. I do health checks, migrations, performance tuning, upgrades, etc. I'll be blogging on my travels and experiences with different DB2 shops. Check back!