DB2 UDB Partitioned Database - Unique Index

In the absence of the DBA that usually works on DB2 UDB, I tried to create an unique index on a table that is defined on a partitioned database. I realized that it's not very straight forward to do so.

From DB2 manual: "There are performance advantages to creating a table across several database partitions in a partitioned database environment. Creating a table that will be a part of several database partitions is specified when you are creating the table. There is an additional option when creating a table in a partitioned database environment: the distribution key. A distribution key is a key that is part of the definition of a table. It determines the database partition on which each row of data is stored.

You must be careful to select an appropriate distribution key because it cannot be changed later. Furthermore, any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)."

This is where I got totally lost "any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)". I was trying create the unique index on a column which was not the hash key and I was getting an SQL0270N Reason Code 1. After including the hash key along with the other key, it worked.

SQLCODE -518

The reason mentioned in the DB2 manual for SQLCODE -518 is "THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT"

You may get this SQLCODE for many reasons. I don't know them all. For me this is what happened. When I tried to connect to a remote DB2 database (on LUW) from a COBOL program on the mainframe, the CONNECT was successful. However when I tried to run an SQL on that database I got this SQLCODE.

After researching for a while, I found that I had an existing SQL statement within EXEC SQL and END-EXEC. That worked good. But when I commented out one of WHERE clause statements (using * at column 7), it gave me this error. I moved the commented statement to the line after END-EXEC and it started working again.

Bottom line is DB2 doesn't like COBOL-like commented code inside EXEC SQL and END-EXEC when you are trying to run the SQL at a remote DB2 database.

Copy Data from DB2 for z/OS to DB2 for LUW (UDB)

One of the applications developer in our shop wanted to know if he can copy data from a mainframe DB2 table to DB2 UDB on LUW. Though there are several ways of doing it, this is the one suggested him assuming the table structure in z/OS and LUW are the same.

1. Some of the tools that can be used to achieve this: Quest Central for DB2, Toad for DB2 or DB2CMD interface

2. Connect to the mainframe database (CONNECT TO ssid) and issue the EXPORT command "EXPORT TO d:\file_name.ixf OF IXF MESSAGES d:\msg_export.txt SELECT *FROM prefix.table_name"
Note: Don't forget to check the message file before moving onto the next step

3. Connect to the LUW database and issue the IMPORT command
"IMPORT FROM d:\file_name.ixf OF IXF MESSAGES d:\msg_import.txt INSERT INTO prefix.table_name"
Note: Don't forget to check the message file

It's that simple. But remember that this works well for relatively smaller tables. For larger tables, you may have to use the LOAD command with proper COMMIT COUNT.

SQL0805N on DB2 package SYSLH203

If you get a SQL0805N on package SYSLH203 then an application is holding a large number of statements open. If this is not an application bug you can bind more packages to allow for more statements open at the same time.

Depending on the type of statement you are executing, DB2 will use a particular package on the server. By default, DB2 creates three packages for each type of package. In this case NULLID.SYSLH2yy is reserved for statements with CURSORHOLD on and isolation level Cursor Stability. The package SYSLH203 means that DB2 is looking for the 4th package (200 is 1st, 201 is 2nd, etc) of this type, but it does not exist. You can create more packages on the server by connecting to the database and issuing the following bind command from the /sqllib/bnd directory:

db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 5

Note: CLIPKG 5 will create 5 large packages, and will give you the package that your application is looking for, as well as one more in this case. This setting only applies to large packages (containing 384 sections). The number of small packages (containing 64 sections) is 3 and cannot be changed.

To bind more CLI packages do the following:

• Find a machine that has the version and fixpack of DB2 that you want to bind. You can map to the machine you're going to bind on and then:
  - Go to the D:\SQLLIB\BND directory in the DB2 Command Line Processor (DB2CMD)
  - Look for the *.BND files in the D:\SQLLIB\BND directory
  - Connect to the host via DB2 connect statement like: db2 connect to dbalias user userid
  - X:\sqllib\bnd>db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 30 to bind the max of 30 CLI packages. Range is 4 to 30.

How to query tables from different DBMSs?

There are times when you need to query tables from different Database Management Systems (DBMSs). For example, one type of information or data may reside in DB2 UDB table and another in DB2 for z/OS or even Oracle table. How will you write queries that joins these tables and fetches the desired results?

The answer is 'federation'. If you have DB2 UDB, and want to join a table from Oracle or DB2 for z/OS, create a federated table image of the table that resides in the other DBMS. Once you create a federated table, you can write queries treating it like a table that resides on the local DB2 UDB server.

For example:
SELECT L.col1, L.col2, F.cola, F.colb
FROM Local_Table L, Federated_Table F
WHERE L.col3 = F.colc

Remember that for "federation" to work, you need DB2 Connect or a similar product.

You can find more details here:
IBM Federated Database Technology