Sunday, January 24, 2010

Index Usage For Foreign Key Enforcement by DB2

In this post I would like to check how much flexibility DB2 provides regarding using an existing index for foreign key enforcement.


A foreign key, is a type of constraint which tells DB2 to enforce that every value in a "child / details" table exists in a "parent / master" table. As a result, when making updates to the parent table, DB2 must perform some tests against the child table so no rows in the child table will be left without a matching column in the parent table. In most cases, the child table will be significantly larger than the parent table, and since these test are done as a part of the update statement, this statement might take a very long time. A good practice is to create an index on all foreign keys in order to make these tests run faster.


This post is dedicated to these indexes. How flexible can we be when creating these indexes? Lets find out.



In order to perform the test I have created two tables with a foreign key. Then I had a REXX script running a delete statement against the parent table which will force DB2 to check the child table. After that delete statement, the REXX pauses, allowing me to use OMEGAMON to check if DB2 used the index I created.




I created two table with 4 int columns each. The foreign key was created on (col1, col2, col3). Next I will list the indexes I created, and for each one, if DB2 used it or not. At any given moment, only a single index was defined. I mad sure that DB2 will benefit from using the index (by making the child table large)


The following index was used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD               
  (COL1, COL2, COL3)                                              
  USING STOGROUP SYSDEFLT                                         
  PRIQTY 720                                                      
  SECQTY 720                                                      
  DEFER YES                                                       
  BUFFERPOOL BP2;                                                   

The following index was used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD       
  (COL1, COL2, COL3, COL4)                                
  USING STOGROUP SYSDEFLT                                 
  PRIQTY 720                                              
  SECQTY 720                                              
  DEFER YES                                               
  BUFFERPOOL BP2;                                           

The following index was not used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD    
   (COL1, COL4, COL2, COL3)                             
   USING STOGROUP SYSDEFLT                              
   PRIQTY 720                                           
   SECQTY 720                                           
   DEFER YES                                            
   BUFFERPOOL BP2;                                                                                                    

The following index was not used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD   
  (COL3, COL2, COL1)                                  
  USING STOGROUP SYSDEFLT                             
  PRIQTY 720                                          
  SECQTY 720                                          
  DEFER YES                                           
  BUFFERPOOL BP2;                                       

The following index was not used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD   
  (COL1, COL3, COL2)                                  
  USING STOGROUP SYSDEFLT                             
  PRIQTY 720                                          
  SECQTY 720                                          
  DEFER YES                                           
  BUFFERPOOL BP2;                                       

The following index was not used

CREATE UNIQUE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD 

  (COL1, COL4)                                            
  USING STOGROUP SYSDEFLT                                 
  PRIQTY 720                                              
  SECQTY 720                                              
  DEFER YES                                               
  BUFFERPOOL BP2;                                           

The following index was used

CREATE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD         
  (COL1, COL2, COL3)                                        
  USING STOGROUP SYSDEFLT                                   
  PRIQTY 720                                                
  SECQTY 720                                                
  DEFER YES                                                 
  BUFFERPOOL BP2;                                             

The following index was not used

CREATE UNIQUE INDEX MOMI.MOMIFKIX ON MOMI.MOMI_CHILD      
  (COL1, COL3, COL2)                                            
  USING STOGROUP SYSDEFLT                                       
  PRIQTY 720                                                    
  SECQTY 720                                                    
  DEFER YES                                                     
  BUFFERPOOL BP2;                                         



Conclusions:

DB2 only uses an index that is created exactly like the foreign key, or has a prefix that matches the foreign key column sequence, so think twice when designing compound foreign keys. I personally do not understand why the last index I tried was not used. Since it is a Unique index, the optimizer can tell for sure that only a single row will be deleted, so what more can one ask for?




Just another little thingy before I finish. When I thought about checking this issue, I thought maybe I can just check sysibm.syspackdep in order to check if my index is being used. Since I had to code a delete / update statement in order to force DB2 to check the foreign key, and since every delete statement (or update to the foreign key) causes all the indexes to be updated, all the indexes were listed as referenced by that package and this method was not good enough.


Bye for now


Momi

Tuesday, January 12, 2010

Identity Column Insert Performance

Hi

It has been a while since my last post, but since it was the holiday season, I assumed that nobody was reading blog posts anyway... :-)

This time I want to write about Identity Column insert performance, or to be more precise, the effect of the CACHE parameter of identity columns on insert performance.

The identity column provided by DB2 is a special type of column. You can create only a single identity column in each table, and you can ask DB2 to generate the values for this column. You can specify several parameters regarding value generation; the parameter I want to focus on is CACHE.

The last value that was assigned to an identity column is kept in SYSIBM.SYSSEQUENCES in the column MAXASSIGNEDVAL.
The CACHE parameter tells DB2 if it is allowed to pre-allocate values and store them in memory. This will improve performance since DB2 will need to access the catalog less frequently. If you allow caching of n values, then the MAXASSIGNEDVAL column will be updated once in every n statements that consumes the identity value.

If you are running a Data Sharing system, and you must guarantee that the identity values are generated in the order that they are requested, you should turn off caching.

In order to check the effect of the caching parameter I have created a table that contains a single integer column which is an identity column with the generated always attribute defined. The tablespace was created with locksize any in order to avoid lock contentions. The table had no indexes created on it.

CREATE TABLE MOMI.MOMITEST
(ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 CACHE X)
)

I wrote a REXX script that performs the following statement in a loop:

INSERT INTO MOMI.MOMITEST (ID) VALUES(DEFAULT)

I executed this REXX script in 5 concurrent jobs in order to create a load on the table. Each execution inserted 250k rows with a commit every 500 rows.

Each time I picked a different cache size and tested the average CPU and elapsed time for each INSERT statement.

In order to get the statistics I used the 318 ifcid trace and got the data out of the dynamic statement cache table, using EXPLAIN STMTCACHE ALL.
To make those statistics available to you in the dynamic statement cache table, you should issue the following command
-start trace(mon) ifcid(318)

The statement that I used in order to retrieve the data from the table was:
SELECT STAT_EXEC,
               STAT_GPAG,
               STAT_SYNR,
               STAT_WRIT,
               DEC( STAT_ELAP /STAT_EXEC , 20, 10) AVG_ELPS ,
               DEC( STAT_CPU /STAT_EXEC , 20, 10) AVG_CPU ,
               DEC( (STAT_SUS_SYNIO +
               STAT_SUS_LOCK +
               STAT_SUS_SWIT +
               STAT_SUS_OTHR +
               STAT_SUS_OTHW )/STAT_EXEC , 20, 10) AVG_WAIT
FROM DSN_STATEMENT_CACHE_TABLE
WHERE STMT_TEXT LIKE 'INSERT%'
;

The following table shows the results I got: (all times are in seconds)

Task 1
Task 2
Task 3
Task 4
Task 5
Average
Theoretical Ins/S
Actual Ins / S
No Cache
CPU
0.0000663071
0.0000654882
0.0000656633
0.0000658551
0.0000660197
0.00006586668
Elapsed
0.0077742199
0.0075929553
0.0078071447
0.0077350354
0.0077541955
0.00773271016
647
312
Cache 2
CPU
0.000042866
0.0000428516
0.00004271
0.0000429092
0.000043037
0.00004287476
Elapsed
0.0035098233
0.0035261292
0.0035296884
0.0035264798
0.0035261121
0.00352364656
1419
827
Cache 20
CPU
0.0000189029
0.0000186776
0.0000189927
0.0000188641
0.0000187513
0.00001883772
Elapsed
0.0004911035
0.0004984513
0.0004985346
0.0005029077
0.0005012357
0.00049844656
10031
7000
Cache 200
CPU
0.000017257
0.0000171668
0.0000172071
0.00001717
0.000017321
0.00001722438
Elapsed
0.0001121486
0.0001027187
0.0001122547
0.0001085953
0.0001055655
0.00010825656
46187
15500
Cache 2000
CPU
0.0000178619
0.0000177593
0.0000179487
0.0000178366
0.000017819
0.0000178451
Elapsed
0.0000419019
0.000039692
0.0000413995
0.0000436566
0.0000363546
0.00004060092
123150
20500
Cache 20000
CPU
0.0000177429
0.0000177092
0.0000178616
0.0000176705
0.0000180373
0.0000178043
Elapsed
0.0000380529
0.0000352191
0.0000346786
0.0000313353
0.0000339644
0.00003465006
144300
22000
Cache 250000
CPU
0.0000179855
0.0000178565
0.0000180507
0.0000178743
0.0000182472
0.00001800284
Elapsed
0.0000373096
0.0000436134
0.0000390873
0.0000395342
0.0000415881
0.00004022652
124296
22500

I also plotted the above data into graphs in order to emphasize the improvements.
The first graph shows how the insert rate increases as we increase the number of cached values


The second graph shows how the average time required to perform a single insert operation decreases as the cache value increases




From the above results we can see that the CACHE parameter has a crucial effect on insert performance against tables with identity columns.
If for example, we look on task number 1, performing those 250k inserts took more than 200 times longer than doing the same job against a column with cache 250000 (which means DB2 catalog accessed is minimized).
I would definitely recommend everybody to use the CACHE parameter. According to my results, for CACHE = 2000 we get very good results and increasing the value beyond that does not give much more benefit, but maybe it will be different on your system. Keep in mind that once you turn on caching, if the DB2 system crashes, those cached values are lost, so it might not suite your business needs (also when using caching, one can not rely on the order in which the values are generated).

Hope you found this useful.
Till next time...

Monday, January 4, 2010

Space Reuse Affected By Commit?

Hi

In this post I would like to present to you a strange scenario involving DB2 space reuse which I was not able to reconstruct in a test system.

One day a programmer told me he added a tiny change to one of his batch processes which caused the CPU consumption to increase from 1 minute (1 hour elapsed time) to 22 minutes. The batch process receives a file with client records (usually around 30 records per client but some clients have up to 300 records). Before the change was applied, the client records were read from file upon need. After the change, the records were inserted into a DB2 table, and processing was done using that table.

The logical structure of the program was:
• Insert client records into table
• Do some processing (during which the records are read from the table)
• Delete the records from the table

This process was executed for each client, with a total of about 100K clients.
I started an Omegamon Application Trace when the batch processes executed and I noticed that each fetch from this table took about 0.00500 seconds. Since a fetch in this DB2 system usually takes 0.00001 seconds I figured that this is probably the problem.

The mentioned table resided in a tablespace and was defined in the following way (it was the only table in that tablespace):

CREATE TABLESPACE XXXXX
IN XXX
USING
STOGROUP XXX
PRIQTY 48
SECQTY 48
LOCKSIZE ANY
SEGSIZE 32
COMPRESS NO
CLOSE NO ;

CREATE TABLE XXXX.XXXX (
COL01 DECIMAL(11) NOT NULL,
COL02 DECIMAL(3) NOT NULL,
COL03 DECIMAL(3) NOT NULL,
COL04 DECIMAL(3) NOT NULL,
COL05 DECIMAL(7) NOT NULL,
COL06 DECIMAL(3) NOT NULL,
COL07 DECIMAL(3) NOT NULL,
COL08 DECIMAL(3) NOT NULL,
COL09 DECIMAL(5) NOT NULL,
COL10 DECIMAL(7) NOT NULL,
COL11 DECIMAL(3) NOT NULL,
COL12 DECIMAL(7) NOT NULL,
COL13 DECIMAL(3) NOT NULL,
COL14 DECIMAL(3) NOT NULL,
COL15 DECIMAL(15,2) NOT NULL,
COL16 DECIMAL(5,2) NOT NULL,
COL17 TIMESTAMP NOT NULL,
COL18 DECIMAL(13) NOT NULL(
IN XXX.XXX ;

CREATE TYPE 2 UNIQUE INDEX XXXX.XXXX
ON XXX.XXX
( COL01,
COL02,
COL03,
COL04,
COL05,
COL06,
COL07,
COL08,
COL09,
COL10,
COL11,
COL12,
COL13,
COL18 )
USING STOGROUP XXX
PRIQTY 48
SECQTY 48
CLUSTER
CLOSE NO ;


First thing I did is to look which SQL Statements were executed against this table. There were a total of 3 statements (each executed many time):
1) A cursor that joined this table to another tables. This table was the outer table in a nested loop join (it was accessed first) and access to the table was done using the index with 3 matching columns. The fetch from this cursor took most of the time.
2) A simple insert to that table
3) A searched delete from the table with a where clause on COL02,COL03 which was done using a none matching index scan.

Since the fetch was done using an index, and that table only contained a small number of records, I guessed that there was a problem with the data organization in the table (even though the record length of rows in the table is constant, thus this could not be an issue of overflow records). I executed a REORG against the tablespace and asked the programmer to execute the process again.

Now each fetch took on average 0.00001 seconds. Beautiful.
The problem was that as the process progressed, the average fetch time got worse and worse climbing to 0.00005 seconds, 0.00015 seconds and eventually reached about 0.00250 seconds. Looking at the SQL counts of the batch process I noticed no commits are being taken. I asked the programmer to add a commit once every 100 clients, and executed REORG again.
This solved the problem. The average fetch time was about 0.00005 seconds throughout the entire execution.
In order to verify that this was actually the reason for the improvement I asked the programmer to remove the commit statement, and when he did, the problem occurred again.
Another possible solution might be using a temporary table instead of the above table, but the programmer did not want to test this solution.

My conclusion is that somehow commit influences space reuse in segmented tablespaces. The first though that came to my mind was that in a massive delete scenario, DB2 might not reuse the space in order to enable a fast rollback, but since this program performed a searched delete, this theory is irrelevant.

I tried to duplicate this problem on a table that I have created in a test system with similar definitions but I could not get it to happen again.
I wrote a REXX script that had a loop which performed:
1) Insert records into a table (I tested 30 records and 50 records)
2) Perform select operations from the table with similar access path, that is, using a matching index scan (I tested 30 and 50 select statements)
3) Perform a searched delete from the table

The loop was executed between 100 and 5000 times without a commit, but I couldn’t get the problem to reoccur. My fetch time remained rather constant throughout the entire test.
I find it hard to believe that I could not get this problem to reoccur because of differences between dynamic and static SQL (the original program is coded using COBOL). If anyone thinks otherwise, please post a comment.


Did anyone else ever encounter such a problem?

Till next time
Momi