RSS

Tag Archives: db2gottalent

#DB2-9 The DB2Night Show #131: DB2’s GOT TALENT Grand Finale!

This post is reference to my presentation in the Grand Finale of DB2’s Got Talent 2014 at DB2Night Show season. This was my last presentation in this season and the show theme was DB2 Success Story. I have selected one of my favourite topic i.e. Federated Database Performance. I have been working on the Federated setup since last 1.5 years. I faced many performance issues on production for which resolutions were difficult that is the reason I spent most of my time in searching and learning different ways for performance improvement in such distributed systems. I had given the presentation on Federated Query performance before on The DB2Night Show #128. This one is the extension of my previous presentation. You can check out the Federation basics and recap of the Episode #128 presentation at this link.

I discussed how distributed data across multiple databases can be accessed using the Federated database systems. Also specified the components of the federation like wrappers, servers, nicknames etc. Here are the few steps to setup the federated database.

CREATE WRAPPER NET8
LIBRARY ‘libdb2net8.a’ ;

CREATE SERVER ora_server TYPE oracle
VERSION 11.2.0 WRAPPER net8
OPTIONS (NODE ‘ORCL’);

CREATE USER MAPPING FOR userName
SERVER ora_server
OPTIONS (REMOTE_AUTHID ‘username’, REMOTE_PASSWORD ‘pwd) ;

CREATE NICKNAME ora_emp FOR ora_server.scott.emp;

Read the rest of this entry »

Advertisements
 
Leave a comment

Posted by on April 15, 2014 in Databases, DB2, db2nightshow

 

Tags: , , , , , ,

#DB2-8 The DB2Night Show #130: DB2’s GOT TALENT Top 7 Finalists Compete!

This post is based on the Multi-Temperature Storage Management Feature Introduced in DB2 10.1. I presented this topic at DB2Night Show on March 21, 2014. The theme for this episode was Storage. Since I am obsessed with the new fabulous features introduced in this version, without any second thought I chose this topic for this round of Finale.

This episode decided our progression for the Grand Finale and fortunately based on votes I qualified for the Grand Finale.
Here is the quick overview of the Multi-Temperature Storage Management Feature.

Storage Groups:

Storage groups are the logical groupings of the automatic storage paths. The storage paths grouped by a particular are identified by the same storage path characteristics like latency, overhead, transfer-rate etc. Once the storage group is created the, we can assign the Automatic Storage tablespaces to the respective storage group. This association of the tablespaces with the storage group is completely dynamic and we can change it whenever required. Here is the visual illustration of how storage groups are formed.

 

storageGroup

 

 

Multi-Temperature Storage Management:

Read the rest of this entry »

 
Leave a comment

Posted by on April 8, 2014 in Databases, DB2, db2nightshow

 

Tags: , , , , , , ,

#DB2-7 The DB2Night Show #129: DB2’s GOT TALENT Top 9 Finalists Compete

This post is based on one of the ‘Index Jump Scan‘ – one of the terrific feature introduced in DB2 10.1. I presented the topic at DB2NightShow Finals-2. The theme for this episode was ‘DB2 Performance’ and I found this topic at the right time.
This was actually a courageous decision of presenting this topic in front of the Scott – one of the pioneer of this topic.
Still I tried my best to introduce the community with this topic and present a comparative study of the scenario without and with the Index Jump Scan. Here is the quick summary of my presentation.

Index Gap:
Being a DBA I always worked on the long ad-hoc queries which has been written for the reporting tools like actuate reports and Birt reports. It is always painful to tune these queries because of the number of predicate conditions and the composite indexes already designed on the tables involved in such queries. Frankly speaking my face used to be turned down when developers asked me to tune such a long queries for which I need to scroll my editor. 😉
Ideally query predicates should be consistent with that of the composite index on the column. For e.g

Q1: select FIRSTNME,MIDINIT,LASTNAME
           from EMPLOYEE where FIRSTNME=’CHRISTINE’
          and MIDINIT=‘I’ and LASTNAME=‘HAAS’;

I1:  create index EMPLOYEE_ID1 on EMPLOYEE
        ( FIRSTNME ASC,MIDINIT ASC, LASTNAME ASC)  ALLOW REVERSE SCAN;

I have query Q1 in which WHERE condition is specified on the columns FIRSTNME, MIDINIT and LASTNAME. For optimal performance I created a composite index I1 on combination of these columns. Such queries where the query predicates are consistent with that of the columns in the composite indexes such queries are said to be consistent. Such queries will provide optimal performance.
Unfortunately, this is not the case in most of the application scenarios. Queries are in-consistent with that of the composite indexes. Following is the sample example of the in-consistent queries.

Q2: select FIRSTNME, MIDINIT, LASTNAME
from EMPLOYEE where FIRSTNME=’CHRISTINE‘
and LASTNAME=’HAAS‘;

Predicates of the query Q2 are not consistent with index I1 since column MIDINIT is not there in the WHERE clause.
In such case the query is said to be in-consistent and it is said to have Index Gap. In our example, we are having the index gap for query Q2 on MIDINT column.

Read the rest of this entry »

 
Leave a comment

Posted by on April 1, 2014 in Databases, db2nightshow

 

Tags: , , ,

#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.

PROBLEM STATEMENT
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
1. EXPORT AND LOAD METHOD
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.

2. ONLINE MIGRATION USING ADMIN_MOVE_TABLE PROCEDURE
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.

ADMIN_MOVE_TABLE: SYNTAX
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 »

 
2 Comments

Posted by on March 15, 2014 in DB2

 

Tags: , , , , ,