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...
by Vikram Khatri | Dec 28, 2007
Generally people try to tune DB2 backup by manipulating PARALLELISM parameter but there is no need to manipulate this parameter if you are looking for a least possible time to backup the database. Let DB2 figure out the optimal value for you for PARALLELISM. But, you...
by Vikram Khatri | Dec 28, 2007
If you are using LOAD utility to load 100s of tables, you can use following SQL to find out the tables which are in LOAD pending status. SELECT TABSCHEMA, TABNAME, LOAD_STATUS FROM SYSIBMADM.ADMINTABINFO WHERE LOAD_STATUS IS NOT NULL
by Vikram Khatri | Dec 28, 2007
The new version of DB2 9.5 has enhanced DB2 audit facility significantly and now it makes a perfect sense for the DBAs to start using it. The traditional notion on any audit (DB2 or Oracle) is that it is very expensive. That is not the case now as you have the options...
by Vikram Khatri | Dec 28, 2007
You have setup your databases in HADR operation and you are wondering how my DB2 LOAD will transfer from one machine to another since DB2 LOAD operation is not logged. Well, the DB2 LOAD operation is of course logged but not the actual contents. So, how do I get my...
by Vikram Khatri | Dec 28, 2007
If you have enabled HADR between 2 databases, the following value of parameters might give you better performance. Database configuration parameters LOGFILSIZ = 6000 LOGBUFSZ = 512 SOFTMAX = 125 DB2 Registry variables db2set DB2TCP_CLIENT_RCVTIMEOUT=20 db2set...
by Vikram Khatri | Dec 28, 2007
DB2 9.5 deletes previous backup images, log files and copies of load images automatically. This alleviates lots of pain for maintaining DB2 backups and log files. Prior to DB2 9.5, these tasks were usually attained by the operating systems scripts. For example, Taking...
by Vikram Khatri | Dec 13, 2007
On windows platform when you create a DB2 database as per following syntax, it gets created in C:\DB2\NODE0000 location where DB2 is the name of the instance. C:\>db2 “CREATE DB TESTDB AUTOMATIC STORAGE YES ON C: USING CODESET UTF-8 TERRITORY US” What if,...
by Vikram Khatri | Dec 13, 2007
DBAs have to comply with the licensing requirements for DB2. As a DBA, how do you know if you are using a particular feature of DB2 or not? I wrote about this long time back at here. In addition to above, you can also use this SYSIBMADM view to find out if you are...
by Vikram Khatri | Dec 11, 2007
Lots of you use DB2 on SAN storages either from IBM or EMC or other third party. Most of SAN storages have their own caching which is to enhance the performance. After working with many DB2 users, I have found that these file system caching is not used properly to use...
by Vikram Khatri | Dec 4, 2007
The db2batch utility is a powerful utility to time the SQL queries. You should be careful in using the db2batch utility during peak time in your production system just for one issue – db2batch utility uses RR isolation level. For long running SQL statements, the...
by Vikram Khatri | Nov 13, 2007
If you want to convert a 2 digit hex code to a single char code, here is the function that you can use. DROP FUNCTION HEX2CHAR; CREATE FUNCTION HEX2CHAR (hexcode varchar(2)) RETURNS CHAR(1) SPECIFIC hex2char LANGUAGE SQL CONTAINS SQL EXTERNAL ACTION DETERMINISTIC...
by Vikram Khatri | Nov 12, 2007
You might come across a need to simulate collation sequence of DB2 on mainframe (or MVS or z/OS) as it is on DB2 LUW (Linux, Unix and Windows) platform if you want your application to show the order of the data exactly same way as it is on the mainframe. The main...
by Vikram Khatri | Oct 3, 2007
If you are connected to Linux via SSH and you want to bump up number of max file open either for DB2 or for your any program, you might run into issues where it does not work for you. Say for example: You login as root and do this modification to...
by Vikram Khatri | Oct 3, 2007
Follow these steps if you want to create MTK Java UDFs manually. 1. Locate mtkoradrop.udf in your MTK directory and drop MTK UDFs $ db2 connect to sample $ db2 -td\! -f mtkoradrop.udf 2. Remove Jar if it was installed already. You can look at the ~/sqllib/function/jar...
