Monthly Archives: March 2014

#DB2-6 The DB2Night Show #128: DB2’s GOT TALENT Top 10 Finalists Compete

This post is based on ‘Performance Tuning in Federated systems‘ presentation in DB2’s Got Talent 2014 at db2nightshow. This was my first presentation on federated systems. I worked closely on the federated systems so I wanted to share my experience with all the db2 community. Since there were too many things to tell I ran very fast while talking but overall I took it very good at the end. Here is an overview of Performance Tuning in Federated Systems.

What is Federation ?

1. Federation allows the users and applications to access the data from more than one RDBMS in the single           requests.

2. Its a special type of distributed database management system that allows the distributed request across            the multiple databases at the same time.

3. Federation consists of a DB2 instance, a database or federated database and one or more remote                     databases of same or different type. I work on the federated system with ORACLE as a remote data                  source in the federation.

How Federation Works:


Application can communicate with Federated server with any supported interface like JDBC or ODBC etc.

Read the rest of this entry »

1 Comment

Posted by on March 23, 2014 in db2nightshow


#DB2-5 DB2Night Show #125: DB2’s GOT TALENT Contestant Search #2

This article is based on the “Online Tablespace Migration Using ADMIN_MOVE_TABLE Procedure” presentation for the The DB2Night Show #125: DB2’s GOT TALENT Contestant Search #2 held on Friday, 14th February 2014. I made it through the qualifying round but frankly speaking it was heavy competition out there in the Round 1 itself. Total 7 ideas presented and all of them had done their job extremely well. 3 of us – Michael, Raja and myself got a chance to present into the finals and rest of the contestants got chance to present again on 28th February 2014. Here are few outlines of my presentation in Round 1.

Table with more than 100K records needs to be migrated from 8K tablespace to 16K with minimum downtime and minimum risk of data loss.

I found out that to resolve this problem we have two possible solutions
In this we need to export all the data and then drop the table and re-create the table in new tablespace and re-create the constraints,indexes, views and MQTs referring to that table. And for the DB2 versions prior to DB2 9.7, probably this is the only solution we have.

But here are few Bottlenecks:
a. Migration needs to be OFFLINE.
b. There is always a risk of data loss.
Regarding data loss, I have this experience on our UAT environment. Since we have constraints on our disk space on database server. Developer executed the script by this method and due to disk space only half of the records got exported and those many only imported into the new table which cause a real trouble for me at work. 😦
So to avoid such mishaps, there is another way to all those who are using DB2 versions 9.7 and higher.

Here are the steps how this will work.
a. Drop MQTs, referential constraints and Views referencing the subject table.
b. Migrate the table to 16K tablespace using SYSPROC.ADMIN_MOVE_TABLE procedure.
c. Re-create the MQTs, referential constraints and Views.

Schema for this routine is SYSPROC.

db2 “CALL SYSPROC.ADMIN_MOVE_TABLE (<schemaname>, <tablename> , <data_tbsp>, <index_tbsp>,
<lob_tbsp>, <mdc_col_list>, <hash_key_list>,<range_part_key_list>, <column-definitions>,<options-list>,’MOVE’)”

For online tablespace migration we can ignore the few input parameters and pass them as NULL. Here is sample example:

—db2 “CALL SYSPROC.ADMIN_MOVE_TABLE (<schemaname>, <tablename>, <data_tbsp>, <index_tbsp>, <lob_tbsp>, NULL, NULL, NULL, NULL , <options-list>,’MOVE’)”

Read the rest of this entry »


Posted by on March 15, 2014 in DB2


Tags: , , , , ,

#DB2-4 DB2NightShow – Delicious Dinner For Fridays

This post is about my experience at db2nightshow as a participant for DB2’s Got Talent 2014. I am into the finals of the contest and my journey ahead solely depends on the votes of DB2 Lovers all over the World. Check out my presentations of DB2’s Got Talent at the DB2 Night Show Replays and vote me and my friends to move ahead. Here is my experience and thoughts on db2nightshow and DB2’s Got Talent contest.

The day I came to know about this show, I never miss a single episode of it. New Ideas, New concepts, new technologies…this is what db2nightshow is all about. It’s a perfect training place for any newbie in the world of DB2. Here we meet new people,experts in the DB2 World. I met many such experts like @ember_crooks @srhayes @MKrafick @susvis @klaas_brant and many more.

I came across this wonderful show when I was hunting for one of my issue on Online Tablespace Migration and guess what I got the gem.  You won’t believe but the db2nightshow changed my routine on weekends at some point. I am a big lazy man and love sleeping on the weekends unlike all. But When I came to know about this, every weekend I spend in watching all my pending episodes of db2nightshow and I hope I will complete the backlog very soon. One more change in me is I am getting Twitter addicted instead of Facebook..Hahaha

Diverse topics, Limited words but Precise solutions, interactive graphics while presentation these are the stuffs that impressed me a lot. And yes miss one important thing- Scott’s commentary. He makes the show soo interactive and watchful. Thanks to Scott for hosting such an amazing show. I like the tag line of the db2nightshow: Educate – Inform – Entertain.

Every year db2nightshow conducts a DB2 Olympics called DB2’s Got Talent. I participated in the Contest this year and happy to be a part of this contest. I enjoyed many presentations on DB2 Performance, DB2 Utilities, Many unknown topics for me like Column masking, Temporal Tables etc. I presented at the show twice once during the qualification round on Online Tablespace Migration Using ADMIN_MOVE_TABLE utility and in the first round of Finals on Query Tuning in the Federated Systems. Along with the DB2, I learned many more things here in this Olympics of DB2 like presenting your point precisely in limited time of 4 minutes, how to improve the presentation skills. Helpful comments by expert judges always help us to improve the presentation and present a good show next time. Typical comments on topic contents, presentation graphics is going to help not only in the finals of the contest but at many more incidents coming in my future. The most important part I loved after presenting any presentation at db2nightshow is the most wonderful comments and the analysis by Ember on every presentation of the episode on – Big Warehouse of the DB2.

I am not sure what’s gonna happen in the upcoming Finals of the contest but I am going to give my best try everytime and whatever are the results I am going to be here again and again. I like the t-shirt of Mariana @db2nightshow. It’s tag line is – KEEP CALM AND USE IBM DB2. I love db2nightshow and Love IBM DB2. I will come up with more details about the topics I presented in coming posts.

Leave a comment

Posted by on March 8, 2014 in Memoir