by Vikram Khatri | Mar 27, 2008
You are trying to figure out the best approach to do the case in-sensitive search in DB2. You must first read these 3 excellent articles written on this topic by 3 IBMers. Blair Adamache Doug Doole Knut Stolze In a nutshell, you can take one of the approach as...
by Vikram Khatri | Mar 27, 2008
You are new to ibm_db2 or db2 and PHP driver available with thin ODBC-CLI client do not match with your version of PHP. The following steps take drudgery out of complex compilation process for those DBAs or system administrator who are averse to compilation process....
by Vikram Khatri | Mar 18, 2008
Yes, db2 has a top like utility called db2top written by 2 IBMers Subho Chatterjee and Jacques Milman. It uses DB2 snapshot APIs to extract information from DB2 engine and shows in a format that you are familiar with top utility on Unix systems. Click at...
by Vikram Khatri | Mar 6, 2008
Use following SQL to find out the ISOLATION LEVEL used by DB2 packages. SELECT ‘SHOW_RSETS’ “STORED PROCEDURE”, SUBSTR(PKGNAME,1,18) PACKAGE, “ISOLATION” FROM syscat.packages WHERE pkgname = (SELECT deps.bname PACKAGE FROM...
by Vikram Khatri | Mar 6, 2008
If you have been using UNIQUE_IDENTIFIER as a data type and use newguid as a default in your SQL Server table definition, you can use a UDF called from a trigger to simulate same behavior in DB2. This work around is due to the fact that DB2 does not yet support...
by Vikram Khatri | Mar 4, 2008
There are several ways one can do DB2 JDBC tracing and it all really depends how creative you are. Check version of JCC Driver $ java com.ibm.db2.jcc.DB2Jcc -version Simple nonsense solution to use JCC Tracing import java.sql.Connection; import java.sql.DriverManager;...
by Vikram Khatri | Mar 4, 2008
You can put this script as a cron job so that you get DB2 severe messages through email or to your pager. #!/bin/bash # To display severe errors logged for the last 1 day: db2diag -gi “level=severe” -H 1d | /bin/mail -s “DB2DIAG severe messages in...
by Vikram Khatri | Mar 1, 2008
In Oracle world, DBAs generally say that Oracle can do static and dynamic SQL. When they say this, it amuses a DB2 DBA since Oracle way of static and dynamic SQL as perceived by an Oracle DBA is no way near to what is meant by static and dynamic SQL in DB2. In Oracle,...
by Vikram Khatri | Feb 28, 2008
This is a common practice to bump the size of all buffer pools in DB2 as that gives the maximum performance boost. It is true but as a DBA you will like to monitor the buffer pool hit ratio and use of the buffer pool to make sure that you are not wasting your memory....
by Vikram Khatri | Feb 28, 2008
As a DBA, your efforts is to make sure that you have as much asynchronous read activity on your database. The intentions are good but how do I know the amount of asynchronous activity going one in my database. This SQL will give you the Asynchronous read ratio (ARR)...
by Vikram Khatri | Feb 28, 2008
DB2 snapshot command or use of the snapshot administration views or SQL table snapshot functions require latches and use of engine resources to return the information but db2pd tool does not require those latches etc to return the information. You should use db2pd...
by Vikram Khatri | Feb 28, 2008
Sometime back, I wrote about how to know about a fenced user id in DB2 at http://www.db2ude.com/?q=node/30. The other day, someone asked me how do I change the fenced user id in DB2? This is very easy but will require an outage if you are already in production. I...
by Vikram Khatri | Feb 28, 2008
Did you wonder why your isolation level escalated to CS from UR? For this discussion, you might want to read about unambiguous cursor at DB2 at http://db2ude.com/?q=node/41. So even if you open a cursor with UR isolation level, there is a possibility that this might...
by Vikram Khatri | Feb 28, 2008
Did you know about P, B or L locks in DB2? They are internal locks in DB2. As a DBA, it is good to know about them so that we know how to deal with them or avoid them. P Locks These are package level locks and you will see them when any application is executing a...
by Vikram Khatri | Feb 25, 2008
You can use following query to find out the name of the bufferpool associated with a table space. SELECT SUBSTR(BPNAME,1,18) “BUFFER POOL”, SUBSTR(TBSPACE,1,18) “TABLE SPACE”, B.PAGESIZE FROM SYSCAT.BUFFERPOOLS B, SYSIBM.SYSTABLESPACES T WHERE...
by Vikram Khatri | Feb 20, 2008
Most of the GUI tools create EXPLAIN tables automatically for you but if you need to create them, you can use one of the following approach. DB2 provides EXPLAIN.DDL in ~/sqllib/MISC directory and you can execute this file $ db2 connect to sample $ db2 -tf...
by Vikram Khatri | Jan 30, 2008
You are a DBA and you want your users to use an isolation level of UR who are doing adhoc queries or typing SQLs using DB2 CLP or some form of the applications. By using DB2 CLP Use SET CURRENT ISOLATION LEVEL command db2 connect to sample db2 set current isolation...
by Vikram Khatri | Jan 30, 2008
Through a application server where you open dedicated connections to database, you get information about your SQL using applications server user-id only. How do you tie those information back to the end user who initiated the transaction? One option is to use DB2...
by Vikram Khatri | Jan 25, 2008
LAST_DAY(SYSDATE) ==> values (current date + 1 month) – day(current date +1 month) days FIRST_DAY(SYSDATE) ==> values (current date – day(current date + 1 month) days) + 1 DAY
by Vikram Khatri | Jan 3, 2008
How do you get index space usage by a table? DB2 provides a Table function ADMIN_GET_TAB_INFO to retrieve information about a table. For example – To find index space used by a non-partition table’s indexes, you can use following SQL: SELECT...
