Trace sql statements in DB2 database

DB2 trace sql - how can I set up a trace to capture all sql statements? Use this process to record all sql statements for a give period of time on DB2. In other words , a DB2 trace #connect to db #1-Create event monitor- make sure you've got write permissions to stated folder db2 "CREATE EVENT MONITOR stmon FOR STATEMENTS WRITE TO FILE '/tmp'" #2 Turn on event monitor db2 SET EVENT MONITOR stmon STATE = 1 #3. (Do stuff to the database that you want to monitor) #4. Turn off event monitor db2 SET EVENT MONITOR stmon STATE... Read more →

DB2 - table sizes

The idea of table sizes in DB2 , can spawn into a wide ranging conversation . This is a quicj script tha I use to output the DB2 table sizes, comparing used and allocated space. select char(date(t.stats_time))||' '||char(time(t.stats_time)) as statstime ,substr(t.tabschema,1,4)||'.'||substr(t.tabname,1, 24) as tabname , card as rows_per_table , decimal(float(t.npages)/ ( 1024 / (b.pagesize/1024)),9,2) as used_mb , decimal(float(t.fpages)/ ( 1024 / (b.pagesize/1024)),9,2) as allocated_mb from syscat.tables t , syscat.tablespaces b where t.tbspace=b.tbspace with ur; Ref:Jack Vamvas(http://www.dba-db2.com) Read more →

Linux - find largest files

Quite often I get a monitoring message stating the drive space is filling , I'll execute some DB2 clean up scripts , but still the problem persists. This little script quicly finds the largest 20 files on a Linux server , execute it from the command line. du -a /my_folder | sort -n -r | head -n 20 Related Posts DB2 Linux history command db2diag.log file delete on Linux and DB2 Read more →

DB2 Health Monitor-Monitor Heap Utilization

On a production system running DB29.5 on Red Hat Linux 4.6 . If you want to keep the health monitor running, you can increase the size of MON_HEAP_SZ to about 2048. But I decided to turn the Health Monitors Off, due to overconsumption of memory. These are OLTP databases. Since I've turned off the health monitor the system is running smoothly Problem -------------------------------- DM10500E Health indicator "Monitor Heap Utilization" ("db2.mon_heap_util") breached the "upper" alarm threshold of"95 %" with value "150%" on "instance" "db2instp". Calculation: "((db2.mon_heap_cur_size/db2.mon_heap_max_size)*100);" "((589824/ 393216) * 100)"= "150 %". History (Timestamp, Value, Formula): " (16/11/200912:03:30.820296, 83, ((327680 /... Read more →

SQL1768N Unable to start HADR. Reason code = "1".

I was asked to troubleshoot a problem related to a HADR start and issued the following db2 command: $ db2 start hadr on db MY_DB as standby I got back the following error description SQL1768N Unable to start HADR. Reason code = "1". It turns out the the users had attempted to set HADR up with circular logging , archive logging is required to run HADR Read More on DB2 high availability TSAMP Cheat Sheet for DBA managing DB2 clustering Read more →