The open source iscsitarget provides a great way to do SAN on a laptop using VMware. This is good for testing and learning purposes. We use iscsitarget to export devices so that we can use them for the shared database by multiple hosts in a DB2 pureScale environment....

Scott Hayes, CEO of DBI gave me the opportunity to do the IBM DB2 10.1 HADR Presentation on his DB2 Night Show. Follow this link: http://www.dbisoftware.com/blog/db2nightshow.php?id=372

DB2 10.1 pureScale requires an optional but highly recommended SCSI-3 PR capable storage. DB2 10.1 pureScale requires a mandatory tie breaker disk which should be SCSI-3 PR capable. Optionally, you can configure an IP address to be a tie-breaker disk but this is not...

If you accidentlly applied ROLLFORWARD command to the standby HADR database, you can put the database back to the rollforward pending mode by using db2 supplied utility db2rfpen. $ db2rfpen on hadb...

In today's virtualized environment, it is easier to clone a virtual machine from an existing one. When DB2 is already installed with Tivoli SA MP for high availability, we need to also make sure that each host has a unique node id otherwise HA configuration will...

If you are spoiled using BGInfo of Windows to show some useful information on your desktop, you may try this poorly written but working script that does the same thing. This was written for SuSe Linux but can be adapted for other distribution. If you happen to make...

The scripts that I am sharing are used to do the DB2 pureScale install on 'n' number of hosts. One of the issue is to have consistent /etc/hosts file on all nodes. The input file is ip.txt similar to the previous posts.   Script to generate and copy hosts...

You can monitor dynamic SQL statements by using SYSIBMADM.TOP_DYNAMIC_SQL view but you need an event monitor to monitor SQL statements inside a DB2 Stored Procedures as those statemets are static in nature. Let us understand this through a simple exercise: Step-A:...

The default port to use Optim Performance Manager for web console is 55000 and 55001 for SSL. This requires opening up 55000 through firewall and what if you want to use port 80 on the OPM Server. It is little tricky since OPM Web Console runs as non-root user and...

Equivalent of Oracle KEEP can be in DB2 using a nested query OVER (PARTITION BY ..) and then using MAX and MIN on top of it without having any extra sort. It is best explained by an example: Case-1: Oracle query using KEEP function: CREATE TABLE emp(id INTEGER, name...

Steve Rees and Hania El Ayoubi from Toronto Lab have shared this utility function written in SQL PL to browse table data in column value pair. It becomes useful when browsing wide table data. For example, if one looks at the table sysibmadm.mon_db_summary or table...

If a table is created with an IDENTITY column and you need to make sure that your identity column starts from the last max value that you load the data. IBM Data Movement Tool does this automatically but if you are not using the tool and want a way to sync up start...

Packaging for DB2 client has changed. Review this article for detailed information and how to download thin / thick clients

Read Problem determination guide here

If you are using DB2 Control Center on Windows and wondering how to set the code page of your choice, follow these steps. Open a DB2 command window: C:\>SET DB2CODEPAGE=1208 C:\>db2cc The first command sets the environment variable DB2CODEPAGE to 1208 (UTF-8) and...

Steps to configure Oracle database federation from DB2 on zLinux. (Or any kind of *nix) These steps are for Oracle database (any supported version from Oracle) to DB2 9.5 but they should work as it is for DB2 9.7 or later with proper Oracle client install. 1. Install...

Steps to configure SQL Server federation from zLinux. (Or any kind of *nix) These steps are for DB2 9.5 but they should work as it is for DB2 9.7 or later with proper install of IBM branded ODBC drivers. 1. Install DB2 2. Install IBM Branded DataDirect ODBC Drivers....

When you want SP to return message_text and you also want to handle error code as per your choice, follow this simple example. CREATE PROCEDURE myproc( OUT v_msg VARCHAR(300)) LANGUAGE SQL BEGIN DECLARE V_ERROR INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR...

This is by no means an exhaustive list of new features in DB2 9.7. But, it is the list of features that are most important to me as a DBA or a developer. Online Schema Changes See Burt's article on Online Schema changes. CGTT - Created Global Temporary Table Now, it...

Starting version DB2 9.7, you can use following query to detect unused indexes in DB2. SELECT INDSCHEMA, INDNAME, TABNAME FROM SYSCAT.INDEXES WHERE LASTUSED = '01/01/0001'; The column LASTUSED tells when that index was last accessed in DB2. This is not per se a...

I am going to show you an easy way to do online schema changes in DB2. The best to learn things is by doing some simple examples. My intent is to make you an expert in 1/2 hour if you learn the following concepts. Create a database with and without automatic storage...

Finally DB2 implemented a long standing demand for setting the Java application name so that you could see the name through DB2 utilities instead of seeing db2jcc_applica. You set a connection property clientProgramName to your chosen name and the db2 utilities will...

There are DB2 LUW configuration parameters that can be set to AUTOMATIC and managed by the DB2 automatic memory manager (STMM). One advantage of this is that DB2 will automatically adjust the memory it uses to get the work done based on the characteristics of the...

Use this DB2 Stored Procedure to build an INSERT statement. This is very useful when you are developing some Java or .Net program or any other program and need an easy way to do this. Thanks to Serge Rielau of DB2 Toronto lab for sharing this with the DB2 community....

Thanks to Serge Rielau for insight in DB2 MERGE. For example, if you have this Oracle UPDATE statement and wondering how can you use this in DB2. Here is the solution. Oracle UPDATE UPDATE ( SELECT clm.c2 wc1, m.c2 wc2 FROM schema1.table1 m, schema1.table2 clm WHERE...

If you have DB2 scripts that you want to run through your Java programs, you normally use Runtime.getRuntime().exec method to run system commands. The following sample code tells how to run DB2 scripts. The following code shows it for both Windows and Unix systems....

Are you looking for Matt Devlin's Perl Scripts for DB2? Even though, they are old but still they are relevant with new release of DB2 particularly if you know how to modify them. Matt Devlin's website http://mattdevlin.net is no longer available but you can get...

If you use MTK (Migration Toolkit from IBM) to migrate your database to DB2 on Windows platform, the migration goes smooth but you may see problem when you FTP data file and LOAD script to Unix platform if you do not do it right. For example, you chose ASC (ASCII)...

Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA 29 November 2008 Download Scripts Introduction Have you ever run the db2 "call get_dbsize_info(?,?,?,0)" command to get the size of your DB2 LUW database and noticed that the size is much smaller than the space...

Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA 16 November 2008 Introduction If you have application jobs or users that run the DB2 load utility and would like to keep track of this information over time there is valuable information stored in the DB2...

Thanks to Max Petrenko of DB2 Toronto Lab for sharing a very useful script to remove check pending status from the DB2 tables after LOAD or other operations. It is easy to generate a check pending script, but the importance of this script is that it builds the...

Mark Mulligan – (mark.mulligan.sr@sbcglobal.net) DB2 DBA 01 November 2008 A critical part of any database disaster recovery plan is to make sure that the environment surrounding the database is the way it was before the disaster in order to provide the same level of...

Say, you want to concatenate all rows of a column and show it as a single row in the same fashion aggregate function works. $ db2 "create table t1(line# int not null, word varchar(20))" $ db2 "insert into t1 values(1,'The'),(1,'horse'),(1,'is'),(1,'white')" $ db2...

Edin I. Aganagic shares his tips on how to increase incremental refresh/maintenance performance of MQTs in DB2. Create an index on the GROUP BY columns. It helps speed up MQT look-ups needed to decide whether existing row needs to be changed, new one inserted, or last...

Download DB2 Logger from here Make sure that you read Readme.txt file before you compile the tool.

You can always turn off global logging by setting global_logging to N. However, if you want to shave every millisecond from your stored procedure's execution time, comment out all CALL DB2. statements when you are sure that your SQL-PL or external stored procedures...

The logger stored procedures run under the DB2 fenced user. Log in as the DB2 fenced user and try again to remove it. If the DB2 fenced user is nobody, ask your system administrator to free the shared memory using the ipcrm command.

Yes. This is an open source stored procedure logger. Please retain the IBM Copyright message in each source file.

Did you copy the bldrtn and bldapp files from ~/sqllib/samples/c to your directory? Try again and if you still get error, please send me a note and tell me what did you do to fix the errors.

I have not tested this framework on the Solaris platform. Please let me know the errors and I will try to fix them.

This logging framework works on Linux/Unix platforms only at this moment. I am looking for help who can port or rewrite this on Windows. If you are interested, please send me mail at vikram@zinox.com

Verify that the file system containing the splog directory is not full.

The logging framework is designed to debug the stored procedures, and the hardw method is used to debug these logging routines.

This is the most common issue. Look for your log file in the /tmp directory. This is related to the fenced user ID and file permissions on the ~/sqllib/splog directory. Ask your DBA to create a splog directory in ~/sqllib and grant full permissions on this splog...

This is the shared memory location used by the splogger library. When you stop DB2, it will free up shared memory used by DB2. For the splogger shared library, there is no way to determine when to free up this shared memory. You can use this number to free up the...

Set the DB2 instance-level parameter KEEPFENCED=YES using the CLP command UPDATE DBM CFG USING KEEPFENCED YES. After restarting the instance, the external stored procedures library will be loaded in memory by DB2 on first invocation of the stored procedure. These...

This is a normal result from spcat. It uses the DROP PROCEDURE command on the DB2 logging stored procedures, and if they do not yet exist in the database, you will see the error message above. The next time you run spcat, you will not get the error.

The bld script is used to compile splogger as a stand-alone program instead of a shared library, for testing. It makes a splog binary file, which you can use to execute the main program in splogger.sqc.

Modify the makefile and specify values for ALIAS, UID, and PWD. Put a CONNECT statement in spcat and spalter.

There is no connect statement in spcat, as it assumes the implicit connection to the database. How to use the implicit connection? $ db2set DB2DBDFT= $ db2stop force $ db2start $ db2 activate db

The file permissions on bldrtn might have changed, depending on how you copied the files to your system. Try changing the file permissions for bldrtn, embprep, and spcat using the chmod command. For example: $ chmod +x embprep bldrtn spcat bld spalter

Here is a list of common questions and answers that people have asked me while learning or maintaining the framework.

The logging framework routines are written in C, so you can use them directly from your C stored procedures without having to make a call to the logging stored procedures. The example C stored procedure below uses the logging API. To use the logging methods, include...

After you run make successfully, you will notice a file generated known as testsp.sql. The only reason we generate this file instead of giving this file as it is is to calculate the size of logging handle properly for your Linux/Unix platforms. This is due to the fact...

In order for you to set up the logging framework, you might need help from your system and/or database administrator to set up the stored procedure logging directory. Before we delve into this, let us understand how external stored procedures (Our logging framework...

An explanation of logging tokens The first DB2.OPEN_LOG call builds a linked list hash table as shown below from the logging tokens kept in the shared memory. For each logging token read from the shared memory or configuration file on its first invocation, OPEN_LOG...

Motivation You might need to write informational, error, and debug messages to a log file for a number of reasons: To test, analyze and validate business logic. To track and review detailed error messages. To fix business logic bugs encountered in production...

I published an article Log stored procedures messages for DB2 on Linux or Unix on IBM Developerworks website in January 2006. Since then, I have made several bug fixes and enhancements to this logging framework. The enhanced logging framework is documented here with...

If you are migrating your ODBC related code from one database to DB2, you might run into an issue of single quote vs. double quote issue. For example: Your ODBC code might issue a call to a Stored Procedure using double quote as shown below: CALL...

Sometimes, it may be necessary to expose data in your flat file with the relational data. There might be some situations where there is no option to load that data in a table. But still, you need a way to use this flat file data in your SQL statements. DB2 provides...

DB2 provides a way to calculate date from a Julian day. For example: 2008075 should match to the 75th day of 2008 and i.e. 2008-03-15. If you try to do, $ db2 values date(2008075) You will get a date of 12/03/5498 but that is what you do not want. The argument to DATE...

Step-1 : Create Explain tables See this entry http://www.db2ude.com/?q=node/65 for how to create explain tables in DB2. Please note: You need to create explain tables in the USER-ID of the logged-in user. If you created explain plans in the schema name of some userid,...

Improve concurrency with DB2 9.5 optimistic locking New optimistic locking feature to avoid maintaining long-lived locks IBM® DB2®, Version 9.5 for Linux®, UNIX®, and Windows® provides enhanced optimistic locking support, a technique for SQL...

Self-tuning memory was first introduced in IBM ® DB2 ® 9.1 and simplifies the task of memory configuration by automatically setting optimal values for most memory configuration parameters, including buffer pools, package cache, locking memory, sort heap, and total...

Introduction This article discusses DB2 LUW Deep Compression from the perspective of someone who planned, organized, communicated and coordinated the implementation of this in development, test, regression test and production environments for multi-terabyte data...

You have installed DB2 on your database server and now you need to setup your IBM PHP client to connect to DB2. Do you require a full ~500 MB client install? Mostly, system administrator or DBAs grind their teeth when they find out that for a simple connect from PHP,...

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...

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....

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 this

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 sysibm.sysdependencies deps,...

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...

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;...

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 last 24 hours"...

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,...

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....

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)...

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...

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...

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...

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...

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 B.BUFFERPOOLID =...

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...

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...

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...

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

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 index_object_p_size as...

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...

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

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...

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...

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...

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...

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, you want to...

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...

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...

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 RR...

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...

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...