Victor Chang’s Whitepaper on Row Compression

Victor Chang and others have published a white paper on row compression on DB2 9. At a high level, authors have achieved significantly better performance running DSS queries in a compressed database using only half as many disks as the uncompressed database. They have...

DB2 Table space containers on SAN

David Sciaraffa explains that DB2 9 uses autonomic algorithm to determine the number of prefetchers (IO_SERVERS) and page-cleaners (IO_CLEANERS) to determine the optimum values and they are described here: Prefetchers: IO_SERVERS Page Cleaners: IO_CLEANERS...

Distribution Statistics

When should you create detailed distribution statistics on indexes in DB2? DB2 9 auto runstat uses following syntax for generating statistics for your table. runstats on table t1 with distribution and sampled detailed indexes all Please note that it is using...

Bitmap Indexes in DB2

Generally, DBAs ask the equivalent of Oracle’s bitmap indexes in DB2. DB2 optimizer takes away the burden of creating / maintaining bitmap indexes as it creates them on the fly when it needs them. How do you know if DB2 optimize is using bitmap index or not?...

Deny Lock Escalation

In DB2 on z/OS, you can deny lock escalation (Allow row level locks to be acquired until resources are consumed and not escalate locks from row level to a table level) by setting LOCKMAX=0 in CREATE or ALTER table space. Can you do same thing on DB2 on distributed...

Connection Pooling and Connection Concentrator

David Marvin explains the difference between Connection Pooling and Connection Concentrator as follows: Connection pooling is the methodology to place a connection for later re-use after an application has disconnected (no longer requires the connection). The next...

String Manipulation Functions in DB2

Igor Nikiforov has contributed various string manipulation functions for DB2 using SQL PL. You may see this link for the original article. You may download the script from this link. The following string manipulation functions are included in above script. AT():...

DB2 9 PureXML®

IBM was little late in the relational database business on Linux, Unix and Windows platforms compared to the Oracle even though IBM was the inventor of RDBMS. With new release of DB2 9, IBM has taken the lead in innovations in relational database with the introduction...

DB2 Stored Procedure Language

Are you new to DB2? Are you making a transition from either Oracle, Sybase or SQL Server? Are you trying to learn DB2 to make your resume look better? One of the immediate challenge for you to is to learn new stored procedure (SQL PL) language quickly. But if you have...

How to rebind packages in DB2?

Whenever you update statistics in your tables or modify any of following configuration parameters or bind options, you need to rebind the packages for updating the new access paths of the SQLs. Configuration Parameters that affect execution of SQL queries AVG_APPLS...

Features that you are using in DB2 9

DB2 9 is a feature packed database with performance features, pureXML, high availability etc. As a DBA, you might want to know which features are being used in your database so that you are not caught with surprise. Run the attached script in your database to find out...

New SYSIBMADM Views in DB2 9

DB2 9 provides a brand new schema SYSIBMADM with many new views in it. This is nothing new as same capability was available through SQL Table functions but this is more elegant. If you worked with Oracle and liked its v$ views so this is very much similar to v$ views...

How to drop constraints in DB2

In my opinion, the quickest and easiest way to learn DB2 is through its control center. Over the number of years, Control Center has come a long way and it provides an easy way to learn DB2. I do not want to invite a flame war on my personal view about the Control...

Hide the body of SQL PL Stored Procedure

As an ISV, you might want to protect your intellectual capital of DB2 Stored Procedures that you created for your product. You might not want your end-users to see how you coded your DB2 stored procedures. These are the steps that you have to take to hide the SQL PL...