Monday, August 26, 2013

October 8, 2013 – csDUG Conference

The Czech Republic and Slovakia DB2 Users Group (csDUG) is a new Regional User Group (RUG) that was formed recently.
On October 8th 2013, we will be hosting our FIRST csDUG EVENT, sponsored by CA Technologies and IBM!


This is a full day FREE conference with top speakers from IBM and CA Technologies - an great opportunity DB2 users to meet industry experts !

Agenda of the conference


10.00 - 10.15       Welcome, csDUG info &  agenda             Philippe Dubost & Peter Priehoda
10.15 - 11.00       DB2 v11 technical overview                      Michal Bialecki (IBM)
11.00 - 11.45       ZIIP offloads                                             Robin Hopper (CA)
11.45 - 13.00       LUNCH
13.00 - 14.00       The DB2 V10 catalog – a revolution          Steen Rasmussen (CA)
14.00 - 15.00       A Review of Bind and Rebind Parameters for DB2 for z/OS   Steve Thomas (CA)
15.00 - 16.00       IDAA demo                                              Saso Prek (IBM)
16.00 - ?              Networking


Location


CA lab 3rd floor, building 12
V Parku 12, 148 00 Prague 4, Chodov
Czech Republic

Are you a qualified DB2 specialist? Do you want to discover what DB2 is or what it can do for you? Are you an experienced DBA in environments other databases and need support in DB2 for a new project? Are you interested in what role it can play a DB2 architecture of your business?
     --> Join the October 8 csDUG conference to listen to industry experts and network with other people interested in DB2!

One of the attendees will win a FREE pass for the next IDUG conference in Phoenix , US !

Register to this FREE conference by sending an email to Register.csDUG.8.OCT@gmail.com, please specify your name, job title, and company.

Tuesday, August 20, 2013

Upcoming European DB2 User Group events

A very short post today. Since it’s sometimes difficult to follow all the DB2-related events that happen around the world, here is a probably-non-exhaustive list of upcoming DB2 User Group events in Europe :

User Group Name
Event location
Date(s)
Agenda
SQL Adria
Zagreb
September 27
[link]
csDUG
Prague
October 8
[link]
TDUG
Istanbul
October 8
[link]
Guide DB2 France
Paris
October 10
N/A
IDUG
Barcelona
October 13 à 18
[link]
spDUG
Madrid
October 22
[link]
DB2 YTR
Helsinki
December ?
[link]


I hope this information is useful to you, if you are aware of any event missing in this list, leave a comment, and I will update this post.



Thursday, August 8, 2013

Save cpu by including extra columns to an unique index

Indexes are mainly used to improve the performance of SELECT statements. They however need to be used with caution, because indexes consume a lot of disk space. One could argue that nowadays DASD is relatively cheap, so that’s not a big deal. In fact, there is another big reason why one should be cautious about adding indexes: an index adds 30% of CPU on INSERTs.

IBM DB2 v10 introduces a new feature, the ability to include non-key columns to an unique index [more info on IBM site].

What is it good for ?


The new INCLUDE feature in DB2 v10 allows you to reduce the number of indexes, which results in cost savings in DASD and in CPU.

Let’s see how, through a very simple scenario: a table composed of 2 columns, COL1 is a unique ID, and COL2 is a non-unique column. You obviously need to define an unique index IX1 on COL1 to insure the uniqueness of the rows in COL1. But because a lot of queries gather data from COL1 and COL2 with ORDER BY, you decided (prior DB2 v10), to create another index IX2 on COL1 and COL2, to improve performance of the applications (SORT avoidance).
Now, in DB2 v10, you can simply INCLUDE COL2 as part as IX1 as a non-key column, meaning that X1 can both assure the uniqueness requirement in COL1, and keys for COL2. As a result, X2 became obsolete and can be deleted (saving DASD space and improving INSERTs performance).



Obviously, the unique key of an index can be composed of several columns, but the idea is the same.



In the scenario above, you will want to include COL3, COL4, and COL5 into the unique index IX1, and get rid of IX2.



Note that you can INCLUDE several non-key columns to an unique index, but you can only INCLUDE them one at a time … Meaning that in the above scenario, you will need to execute 3 SQL statements to INCLUDE to 3 non-key columns :
ALTER INDEX IX1 ADD INCLUDE (COL3);
ALTER INDEX IX1 ADD INCLUDE (COL4);
ALTER INDEX IX1 ADD INCLUDE (COL5);

From Theory to Practice …


The above theory is nice, but this scenario might be very difficult to locate manually, in a production DB2 environment composed of thousands of tables and indexes.

The following SQL statement will scan the DB2 catalog to locate some of these cases, namely locating non-unique indexes which contain columns that include the key of an unique index (when the unique index is composed of only 1 column).

SELECT IX.CREATOR,IX.NAME,         
       IX.TBCREATOR,IX.TBNAME,     
       IX.UNIQUERULE,IX.COLCOUNT,  
       KEYS.IXCREATOR,KEYS.IXNAME, 
       KEYS.COLNAME,KEYS.COLNO,    
       IX2.CREATOR,IX2.NAME,       
       IX2.TBCREATOR,IX2.TBNAME,   
       IX2.UNIQUERULE,IX2.COLCOUNT,
       KEYS2.IXCREATOR,KEYS2.IXNAME,
       KEYS2.COLNAME,KEYS2.COLNO   
  FROM SYSIBM.SYSINDEXES IX,       
       SYSIBM.SYSKEYS    KEYS,     
       SYSIBM.SYSINDEXES IX2,      
       SYSIBM.SYSKEYS    KEYS2     
 WHERE IX.UNIQUERULE = 'U'        
   AND IX.CREATOR = KEYS.IXCREATOR
   AND IX.NAME = KEYS.IXNAME      
   AND IX.COLCOUNT = 1            
   AND IX.TBCREATOR = IX2.TBCREATOR
   AND IX.TBNAME = IX2.TBNAME       
   AND IX2.CREATOR = KEYS2.IXCREATOR
   AND IX2.NAME = KEYS2.IXNAME      
   AND IX2.UNIQUERULE = 'D'         
   AND IX.TBCREATOR = IX2.TBCREATOR 
   AND IX.TBNAME = IX2.TBNAME        
   AND KEYS.COLNAME = KEYS2.COLNAME ;

With the result of this SQL statement, you can prepare your ALTER INDEX to INCLUDE non-key columns to the unique indexes listed, and get rid of the corresponding now-superfluous indexes.


If the unique index is composed of several columns, getting such simple output will be much more complex in pure SQL, because of the necessity of some kind of "table transpose" involving 2 UNIONs, and several INNER JOINs - due to the structure of the catalog. That said, if one gets rid (or changes the value) of the AND IX.COLCOUNT = 1 WHERE clause in the above statement, and looks carefully at the output (IX.COLCOUNT and number of IX2 rows matching), locating the other cases should be pretty straight forward.