DB2 Stored Procedure Maintenance

If you ever thought that you can just create a bunch of DB2 store procedures and left it in the server until it being replaced by newer version without having to spend some efforts to maintain it, you're wrong, dude/dudette.

Dynamic SQL statement is as it's name implied: Dynamic. Dynamic in the sense that the SQL Compiler process the statement when someone execute it and optimize it using the current statistics.

What about Static SQL statement? Haha, does this start to make any sense?

If it haven't ring any bells in your mind, faster go and grab a copy of DB2 book and start your revision.

Static SQL statement's access plan is generated and stored in the database at the moment that you perform the binding, i.e. compile the statement. This means that the access plan is based on the statistics at that moment.

In a large organization with few ten to hundreds line of business servers, usually the DBA don't really bother (or they can't really know) whether they must perform the maintenance on the application database objects.

Remember the DB2 Automated Maintenance Tool in DB2 Control Center? They only help you to backup, reorg and runstats your databases. Frankly speaking, there are more maintenance needs than you possibly can imagine.

Just like human body which you gotta do exercises, body building and drink super boosted tonic to maintain that drop dead gorgeous figure to attract the opposite sex, any enterprise databases desire the same treatments.

I'm going too far, but you get the idea, :p

So, the meat for today's lesson is: YOU HAVE TO REBIND YOUR STORED PROCEDURE, especially those lengthly multipages SQL codes and which involves plenty of data manipulations. One good example is a taxing month-end report generation SP.

To do this, you must either recreate the stored procedure which is I think a stupid way to perform in the long run, or you can use the System Procedure: SYSPROC.REBIND_ROUTINE_PACKAGE like the example below:

CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P','MYSCHEMA.MYBATCH1','ANY');

Of course, there are few calling variants by passing in different set of parameters, but the whole point is you need to rebind the SP.

And..... very important, in case you are new in this field. Make sure you reorganize your tables (i.e. REORG command) and collect the latest statistics (i.e. RUNSTATS command) before you do the rebind, else it won't help much.

DB2, Simple right?

DB2 How to Empty a Table

In data warehouse environment, usually there is a need to clear out the contents of staging tables to prepare for a fresh set of extracted source data. You might also want to housekeep some historical aggregation according to some predefined schedules, which involves copying data from a table to another and subsequently removed all data from the copied table. These are just some of the examples that boiled down to the need of efficiently "truncate" a table.

Microsoft SQL Server and Oracle DBA are definitely enjoying the luxury of built-in table truncation functionality, through command like "TRUNCATE TABLE YourTableName".
Ok fine, but does IBM DB2 UDB, the so-called most scalable and performing RDBMS provide such option?

Before that, let me evaluate some of the options of removing rows from a table.

Option A:

DELETE FROM YourTable

Well, you can delete all rows using this statement. However when involving lot of records, transaction logging causes significant performanc degradation. Still, this option is acceptable if your application requires recovery of deleted rows.


Option B:


(Assume this is within the same transaction)
ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM YourTable;


You managed to escape the bad luck of doing a lot of transaction logging. But wait a minute, constraint checking are still in force (Check yourself by doing explaining a DELETE FROM statement).


Option C:


(Assume this is within the same transaction)
SET INTEGRITY FOR YourTable OFF;
ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM YourTable;


Again, you managed to skip the logging and check constraint and referential constraint checking, datalink integrity checking, and generation of values for generated columns. Primary/Unique Key constraints still enforced.

Option D:


LOAD FROM /dev/null of del REPLACE INTO YourTable


This is by far the most common workaround that I have seen for table truncation. It basically uses the LOAD utility on /dev/null for simulating the loading (replace) of no-data into the designated table. The same concept works for Windows environment.

You can also use similar IMPORT FROM /dev/null of DEL REPLACE INTO YourTable. There are some differences between IMPORT/LOAD.

Option E:

With a little bit of guts, you can drop and recreate the tables. This can be tedious if you got to recreate every constraints/views/etc that dependent on the "new" table.

Option F:


ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE


This is by far my favorite way of doing "TRUNCATE Table" in DB2.

Which ring suits you? You decide.

Be Smart. Get A Row Number !

Imagine there is a table with no primary key or unique constraint and volumes of duplicated rows keep coming in. The only piece of information that differentiates these rows is a timstamp column that stores the date and time where record is inserted.

ID (VARCHAR)	NAME (VARCHAR)	AMOUNT (INT)	LAST_UPDATED (TIMESTAMP)
1	Eddy	80000.00	2006-03-14-00.10.31.999999
1	Eddy	90000.00	2006-03-14-00.09.31.999999
1	Eddy	90000.00	2006-03-14-00.11.31.999999
2	Lee Sin Ti	100.00	2006-03-14-00.10.31.999999
2	Lee Sin Ti	200.00	2006-03-14-00.09.31.999999

Table1


Based on this, your DB2 query is to remove all the outdated records while maintaining only the latest entries.


An idiotic first attempt:

DELETE FROM TABLE1
WHERE
ID || NAME || CAST (AMOUNT AS VARCHAR(32)) || CAST(LAST_UPDATED AS VARCHAR(64))
NOT IN
(
SELECT (ID || NAME || CAST(AMOUNT AS VARCHAR(32)) || CAST(MAX(LAST_UPDATED) AS VARCHAR(64)) ) AS KEY FROM TABLE1 GROUP BY ID, NAME
);

This attempt is definitely a NO-NO. Not only the string concatenations takes a huge amount of processing cycles, it is also UGLY in my point of view. Using a generated random data of 100k records, it takes an unacceptable amount of time to complete the delete query.

Then, my second attempt got to deal with DB2 support of row_number() function, which I greatly appreciated from IBM.


Second attempt:

DELETE FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID, NAME ORDER BY LAST_UPDATED DESC)
FROM TABLE1
) AS X (ROWNUM) WHERE ROWNUM > 1;

WOW, an optimized yet elegant query to achieve my goal. With the same random data set, it only took less than 1 minute to complete the query.