Firing up db2 9.5 on Ubuntu Hardy Heron

I work on IBM DB2 at office. So, I was curious to set it up at home in my new lappy. It was fun and I did it successfully.

I found it quite difficult to set it up though, but after a few hick-ups I was able to make it. Even though I ran the command line setup, the engine never started when I gave the db2start statement. After a lot of googling I found a solution ironically on a Russian website. 🙂

De-package

sudo dpkg -i db2exc_9.5.0-1gutsy1_i386.deb

Set the annoying environment variable

sudo echo “kernel.shmmax=1610612736” >>  /etc/sysctl.conf

Run the Set-up

/home/Prabhu/exp $ . ./db2_install

Start the Engine

db2start

Create the SAMPLE database

db2sampl

Refresh

db2stop

db2start

Connect and Enjoy

db2 connect to sample

ftp timeout in AIX

Ever wondered how you could increase the timeout of an ftp connection. The default ftp timeout limit for for an AIX OS would be 15mins. This can be easily increased.

• Edit the file /etc/inetd.conf file.
• Go to the line where ftp configuration is done. (Run a search in vi)
• add the following “-t <time-out limit>”
• for further details please go to the man pages of the ftp daemon. “man ftpd”.

Updating a large table with partitions

I came across a situation where I had to update a very large table. It had 373 million records. A simple query could time out or you would end up with a snapshot too old error.

UPDATE EMP

SET EMP_LOC_C = ’03’

WHERE EMP_LOC = ‘NY’

 

Now if this table has more than a billion records, the query might not succeed.

The ideal solution is to run an update partition by partition. You can get the partitions for your table by querying dba_tab_partitions.

 

SELECT TABLE_NAME,PARTITION_NAME

FROM DBA_TAB_PARTITIONS

WHERE TABLE_NAME LIKE ‘EMP’

P_001

P_002

. . . . .

P_030

Now what you could do is to let the query run against each partitions separately.

UPDATE EMP PARTITION(P_001)

SET EMP_LOC_C = ’03’

WHERE EMP_LOC = ‘NY’

 

 

Moreover if there is an index built on any of the columns you can use that as well and split the query even further.

UPDATE EMP PARTITION(P_001)

SET EMP_LOC_C = ’03’

WHERE EMP_LOC = ‘NY’

AND EMP_I > 8612751936

 

So this way you can split and update the table w.r.t. partitions.

Lookup failing due to huge data volume

 

This is an annoying situation you would come across in a production environment. Occasionally the volume of data would surge and the lookup stage would not be able to process it.

Sparse lookup stage basically fetches all the records from the driver link and the reference link and then loads it onto the RAM prior to performing the join. Definitely if the volume is above a certain extent the RAM would become a bottle neck. There are two ways by which this can be averted.

 

1) Modify the lookup type to “Sparse” from “Normal”. This can be done provided the input link count is considerable less. It should be below 1000 ideally.

2) Another option would be replace the lookup with a join. The join stage does not load the records into the RAM and performs quite well. The obvious question would be why not always use a join stage. Lookup stage is expected to work much faster than a join stage.

That’s it for now. . Happy Warehousing!