One of less known but very useful feature of DB2 is the post login script after an application makes a connection. DB2 database has a configuration parameter known as CONNECT_PROC which is name of the database procedure which will be executed everytime someone...

After specifying entries in db2dsdriver.cfg, the db2cli validate -dsn >dbname> does not show that the client actually read db2dsdriver.cfg file. For example: A sample db2dsdriver.cfg file: db2psc@node01:~/pot_purescale/04application> db2cli validate -dsn psdb...

Management of my shoe-closet computer center requires that I should take some steps to protect the data by taking a backup. In last 10 years, I never took any backup and luckily I was never in the situation to have lost the data. The servers kept on running and...

Usually enterprise distributions like SLES or Redhat are slow to adopt latest in consumer products such as CUPS used for printing. The version of cups on SLES 11 SP2 is 1.3 when the latest version at the time of writing this is 1.6.2. The older version 1.2 uses older...

http://www.novell.com/support/kb/doc.php?id=7003097  

In today's world, due to some dishonest populace who are like parasites living on somebody's else blood, implemenation of security comes at a heavy price and it has more problems than solutions. It is also a great business model for the companies to exploit...

When taking a DB2 backup, if you get the following error: $ db2 backup database <dbname> to /backup compress SQL1224N The database manager is not able to accept new requests, has terminated all requests in progress, or has terminated the specified request...

When database is converted from circular log to archive log, it will force the backup of the database which is a good thing. But, sometime, we do not want to do it. Say, when we are in test environment like testing upgrade from DB2 ESE to DB2 pureScale. If database is...

Best practice or things that appear to work well in a two nodes cluser or a single node (just with one DB2 and a CF). The cthats subsystem handles heartbeating between nodes. When in steady state, there is a ring formed where each member is sending heartbeats to their...

For example, we have a two (or more) nodes DB2 cluster having a shared database and we want to configure Optim Perfromance Manager (OPM) for the database monitoring. The questions comes - which node should OPM connect to and what if that node is down for any reason....

In DB2 pureScale, when you run the command db2instance -list, it shows the DB2 pureScale topology and which member is up and running and which CF is in PRIMARY mode and which one is in PEER mode. If you want to change the role of CF from PRIMARY to PEER or from PEER...

Work Load Balancing In order to utilize DB2 automatic Work Load Balancing for JBoss application, some changes need to be made in the JBoss JDBC driver manager configuration. Myth - Most people think that work load balancing is done by the DB2 pureScale and it is not...

DB2 timestamp can show micro seconds and I was trying to generate an excel report showing elapsed time between 2 DB2 timestamps and had to work a little bit to write this excel formulae. Timestamp (Cell B3) = 2013-03-04-13.38.26.677483 Timestamp (Cell C3) =...

On AIX, how to find out the uDAPL version? # lslpp -la "*udapl*" Fileset Level State Description ---------------------------------------------------------------------------- Path: /usr/lib/objrepos udapl.rte 7.1.1.15 COMMITTED uDAPL Path: /etc/objrepos...

In DB2 pureScale if a node goes down, it is started on another host in a light mode just to do the crash recovery. When the home host becomes available, the host should fail back to its home host seamlessly. For example: This is a sample output from the db2instance...

After you install DB2 pureScale, it is a good idea to check the GPFS tie breaker and to see what DB2 install did to set the GPFS tie breaker. Run the following command. # db2cluster -cfs -list -tiebreaker The current quorum device is of type Majority Node Set. The...

During DB2 pureScale install, you must have given the tie-breaker disk device name. It is a good practice to check if that disk was in fact used as a tie-breaker disk or not. # db2cluster -cm -list -tiebreaker If the output from the above command is...

While installing DB2 pureScale, if you receive the error: DBT3542E  An instance management task failed because the db2prereqcheck utility encountered an internal error. Reason code: "2", the fix is simple. Edit your /etc/hosts file and check if you have...

As I am exploring new features of DB2 10.1, there are some fun things that I can do. Example-1: A table has rows in it. You want to make those rows invisible to all including you. Do this. $ db2 "select count(*) from ps_table" --> You see rows in this...

There are two types of DBAs Ones who are control freak - This article is not meant for you as you have already taken pains to grant privilges to an application user without granting DBADM. You have a very good mechanism to control how applications connect and what...

This must be read in conjunction with this post. The fast I/O fencing needs to be enabled in DB2 pureScale. This is a feature of GPFS in conjunction with your storage firmware or its driver. In a nutshell, the following procedure must be followed: The file...

This is how I did a setup of two wireless routers with same SSID. Host Router: Linksys WRT54GS (I am replacing that with a Linksys E4200) connect WAN port to the modem. Client Router: Linksys E1000 connected through an ethernet cable with the main router. Do not...

These instructions worked for me to setup 2 wireless routers having same SSID with a single Internet connection. Note: Both routers connect to each other wirelessly so this setup is useful when it is not possible to connect both routers through a wire. My host...

On my new Thinkpad W530 with 32 GB RAM and dual 500GB SSD, I am running multiple virtual machines using VMware 9 workstation. So far, I have tested 6 VMs running with a resonable performance. My aim is to run DB2 pureScale using minimum 4 VMs and 2 VMs for the...

The first thing I noticed in Windows 8 is the missing "Manage Wireless Network". I tried taking help from google but I found same issues raised by other people. I could not connect to my work wireless that uses Cisco LEAP and here are the steps that I...

The Work Load Management (WLM) feature of DB2 is mostly viewed as one of the best but least understood feature. The WLM has two main themes. 1. Use system defined resources such as CPU or I/O usage to do a task faster or slower 2. Use DB2 defined control such as...

After you install Optim Performance Manager 5.1.1.1, start the web console and try to add a database for monitoring. After establishing the connection, you may notice an opmConfigurationAdapter.checkLicense error and you may wonder that you specified the license...

RSCT is pretty smart to take care of automation but when unsual things happen and may be that logic is not yet there, you may have to intervene as a DBA and that is why you have a niche for yourself. Take this case: The output from lssam shows that two members of the...

DB2 pureScale has one of the best features known as WLB, ACR and CA and a brief introduction is here. WLB (Work Load Balancing) - at the unit of work level is the capability unmatched in the industry and built at the driver level where it can distribute the work to...

The status of the db2 pureScale instance is best determined by the db2instance -list and lssam commands. For example: db2psc@node02:~> db2instance -list ID TYPE STATE HOME_HOST CURRENT_HOST ALERT -- ---- ----- --------- ------------ ----- 0 MEMBER STARTED node02...

============================================================== UPDATE for DB2 10.5 Everything holds good for DB2 10.5 except that you need to set the following db2 registry variables after DB2 pureScale instance in created. db2set DB2_SD_SOCKETS_RESTRICTIONS=false...

If you have set up DB2 10.1 Active / Passive or HADR with TSA, the db2haicu command sets up the cluster manager DBM CFG to TSA and there is no way you can unset that by using db2 update dbm cfg command as it can only be done by the application and that is in our case...

In zLinux environment, the DB2 High Availability solution using Active / Passive (with shared database) is very easy to implement as per the previous article. The db2haicu command uses network IP address as the tie-breaker but the better reliable approach is to use a...

The old timers DBA and System Administrators used some form of clustering to have 2 machines (One is Active and another is passive) with a shared database to provide high availability. This was traditinally done by either using IBM HACMP or Veritas Cluster Software...

DB2 10.1 pureScale provides db2cluster command to do things that are related to the RSCT. For example: You can use db2cluster -cm -list -hostfailuredetectiontime to find out the host failure detection time. node02:~ # db2cluster -cm -list -hostfailuredetectiontime The...

In DB2 pureScale, we use a RSCT peer domain and a GPFS domain. These domains have couple of machines networked together. In democracy, we choose someone to be a leader / master / manager to create an order. The same type of democracy exists in RSCT and GPFS where the...

The GPFS logs are in /var/adm/ras and RSCT logs are in /var/ct/<db2domainname>log/mc directory. If you are lazy like me, create an alias in your profile to change the directory alias rlog='cd /var/ct/db2domain*/log/mc' The directory structure looks like:...

Useful commands for day to day work to manage DB2 pureScale with RSCT and GPFS GPFS: Did you wonder why GPFS commands start with mm? The GPFS started as a IBM research project the the early 90s to build a multi-media (music and video) networked file system for some...

Before Install DB2 10.1 pureScale feature uses RSCT, GPFS and Tivoli SA MP which is an application of RSCT. A DB2 DBA can take care of DB2 but there are additional things one must take care particularly on RSCT. In using DB2 pureScale, one is going from a stand alone...

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