An excellent read on the different ways DBMSs handle sequences and identity columns. While only IBM DB2 supports both, Oracle and SQL Server have their own nooks and crannies which need to be explored in depth. This article does an excellent job at it.

http://www.dbazine.com/db2/db2-disarticles/gulutzan4

If you want to convert an IDENTITY column which is generated by DEFAULT to be generated ALWAYS, you are out of luck. You cant do it using an ALTER statement. The only logical way is to drop and recreate the table as stated here.

How do you retrieve the value of an IDENTITY just inserted into a row? Use the function called IDENTITY_VAL_LOCAL as shown here.

I have been using the DB2 Command Center for quite a while. Not just for simple queries but for advanced procedure and functions as well. Its the simplest of tools and I like it for what it is. Besides it allows me to run multiple queries at the same time. The error messages it gives are quite cryptic but at the same time, suggest possible remedies.

However, as luck (read IBM) would have it, its not perfect. Its got its own set of problems and issues. First up, its a terrible text editor. Using the Ctrl + Arrow keys takes you to places no other text editor would. Tabs get messed up and the font size is too large. Plus there is no color coding.

But thats still something you can live with. After all, looks arent everything, right? It does get the job done, doesnt it? Well, as it turned out, it doesnt get the entire job done. It leaves out the most important part in fact. The comments. So, whenever you go about compiling any source (with comments) for a procedure or a function from the Command Center, it just strips them off and compiles the remaining source!!!

I am still searching for a solution or atleast a work around. DBAs tell me to use the DB2 Development Center instead since it does not strip off comments. How do I tell them that it sucks bigtime? Its even worse than the Command Center and dont know why IBM included it with the DB2 package. Its better to go in for an external Text Editor like GVim or EMacs rather than IBM’s tools.

**************EDITED ON 19th Oct 2006*******************

It seems this problem occurs only on the specific version of DB2 client tools that I was using which were installed by default with DB2 v8.1. Upgrading the tools from the IBM website or with DB2 later releases solves the problem. In fact it vastly improves the quality of both the Command Center, the Control Center and the Development Center.

**************EDITED ON 19th Oct 2006*******************

For almost a month now I have been struggling with using cursors inside a SQL procedure in DB2 v8.1 on a Linux server. The cursors, while being traversed inside a WHILE loop, exited the loop before end-of-cursor (a.k.a NOT FOUND condition) had been encountered. Without getting into the specifics of what I was doing wrong, I am giving here the simplest working method to use cursors in a DB2 SQL Stored Procedure.

I had to remove some of my additional cursor handling (like DECLARE EXIT HANDLER or DECLARE CONTINUE HANDLER) which was probably causing the issues that I was having. Make sure you DO NOT use any condition handlers while using CURSORs, or it could lead to a lot of chaos. The loops run just fine now using the SQLCODE = 0 condition and a CLOSE CURSOR at the end.

A typical example using cursors in DB2 should look like this:


-- Declare the SQLCODE internal variable - THIS IS REQUIRED
DECLARE SQLCODE INTEGER DEFAULT 0;
-- Declare the cursor with a SELECT statement
DECLARE C1 CURSOR FOR SELECT....;
-- Open the Cursor
OPEN C1;
-- Fetch the values from the cursor into local variables
FETCH C1 INTO ....;
-- Run the loop till SQLCODE is zero (which means the last cursor fetch was successful)
WHILE (SQLCODE=0) DO
    -- More processing can be done here
    -- The FETCH must be performed immediately before the END WHILE
    FETCH C1 INTO...;
END WHILE;
--Close the cursor
CLOSE C1;


Remember the key here is to make sure that the FETCH occurs immediately before the END WHILE. If any other statement (like an INSERT or SELECT) occurs between the FETCH and the END WHILE, you could end up with an infinite loop. Every statement that you execute inside the procedure returns and sets the value of the SQLCODE. So checking the SQLCODE immediately after the FETCH is what is needed. An INSERT or a SELECT between the FETCH and the END WHILE will also reset the value of the SQLCODE to a zero (if they are successful, that is), and the WHILE will continue to iterate forever!!! So be extremely careful while handling the cursor FETCHes.

Once you have this running, stored procedures and cursors become so much more useful. And here’s a great link to get to DB2 official documentation from IBM. Click here and add to Favorites NOW!!!

I was looking for something that would provide similar functionality in DB2 as the DBMS_OUTPUT.PUT_LINE function does in Oracle. Simple console output, it seems, is not quite possible using DB2 SQL PL alone. If you want your DB2 stored procedure or function to output something to the screen or application while executing (even if its for debugging purposes), you will have to create an external function based in either C or Java.

I came across this at ITToolbox while searching for a workaround. It might be of help to you just in case you are interested.

Blogged with Flock

IBM is launching a first of its kind Linux based Business Intelligence solution. Called the BCU (Balanced Configuration Unit), it is a complete solution encompassing IBM hardware, Linux OS, and IBM software. This product joins the BCU for AIX, which is widely used today and available as well.

The IBM Data Warehousing Balanced Configuration Unit (BCU) for Linux is a package of:

• DB2 Data Warehouse Edition(DWE)
• eServer 326m (AMD Opteron)
• System Storage DS4800

The BCU integrates these hardware and software components and provides clients with:

• Hardware and database configuration
• Performance optimization
• Systems and Solution testing and client-side integration

Customers can complement the solution with industry-specific data models and BI software, or tools from ISVs such as those from Siebel, SAS, SAP and many others.

IBM today released the latest version of its flagship database product called DB2. Reaching its zenith at version 9, DB2 has come a long way indeed from those days of Mainframes and COBOL. Not only does it run on single processor desktops to multi processor mainframes, it also supports many OSs. Other new features include:

1) XML Native Data Storage
2) Data Compression Technology
3) Close integration with mySAP business applications
4) Information warehousing advantages

In short, DB2 9 features advanced XML management, money-saving storage compression and tight integration to mySAP applications.

Link to the Press Release and the Resource Center.

An excellent resource for all possible methods of concatenating strings on different databases.

Concatenating Strings

Both Oracle and DB2 support the concatenation operator “||” (the double pipe), while SQL Server only supports the plus sign “+”. So remember to convert all the “||” to “+” when migrating stuff from Oracle or DB2 to SQL Server.