An Encounter with chain of lockwait!!!

I have been aways from blogging for quite a long time, the office hours have been really hectic and then some personal engagements. The weather is getting awesome with Maunsoon approaching.

So here I am back with all my energy to share some piece of knowledge which I myself acquired during this hectic office schedule. So let me start with "a story'.

There was some part of application which was facing lock timeout issues, people blamed the application code and tried to figure out/locate the piece of code which may be causing this, no one could succeed. Later it could be observed in other places as well. Now it was more important to find out the set of queries which may be causing this.

To find out details of lockwaits you can query the view SYSIBMADM.MON_LOCKWAITS to get details of locks which may be causing lockwaits...
These details though will be only level one, and will give only immediate blocked query, if the actual query which is suffering is in-turn dependent on this query, you will have to again query the same view. There are very good chances that by the time you get to second level of query the query would have either committed/forcefully rolledback (in case of a deadlock victim) or timed out.

So I decided to write a piece of SQL which can give me details of the lock and chain of lock wait.
So I wrote a view (please download the definition from here).


So I tried to test the dead lock scenario. I created below tables:

Tables : TEST and TEST2

create table test2 (col1 int, col2 int); create unique index ind_test2 on test2 (col1); commit;

create table test (col1 int, col2 int); create unique index ind_test on test (col1); commit;

Once the tables were created, I fired some queries from three different session so that each one would block other:

Session1	Session2	Session3
insert into test2 (col1 , col2) values (1,2);	insert into test (col1 , col2) values (1,2); insert into test2 (col1 , col2) values (1,2);	insert into test (col1 , col2) values (1,2);

Then from a forth session I fired "select * from lockChain_INFO"
Which gave an output similar to below one:





Then from session1 I fired one more query which was:
insert into test (col1 , col2) values (1,2);

After this when I again queried the same view I got below result:





These are insert statements and are static ones, and can easily give you the cause of locking (i.e. clashing index keys). Imagine if you are trying to update the same row from two session (and that in application which uses dynamic query so you are not sure of the row).

To help you there is a function available which will provide you the lock info: MON_FORMAT_LOCK_NAME


SELECT SUBSTR(NAME,1,20) AS NAME, SUBSTR(VALUE,1,50) AS VALUE FROM TABLE(MON_FORMAT_LOCK_NAME('LOCK_ID')) as LOCK_DTL;


when lock_id is BLOCKING_LOCK received from view. This query gave descriptive info about the lock as in which row_id and which table etc:




This granular info helped me figure out that the issue was not actually in application or database, there was an issue out of configuration created by the client, which forced usage/update of same row for multiple purpose.

Once this issue was identified, we suggested configuration changes which smoothed the things.

hope this will be equally useful for you guys! :-)

I had worked on couple of more similar snapshot views, which I will be sharing in blogs to come!

Download View:

lockChain_INFO

DB2 10 is here: What's new? The performance aspect!!!

Hi guys... So DB2 10 is here and is available for download...

I was involved in DB2 10 beta EAP and you can listen to my podcast with industry experts (which quite an honor)...

Well lets see what's new in v10 which can help in improving product performance...

It has an improved index scan which is termed as jump scan, for people from Oracle world this is similar to skip scan... I tried this out during EAP and have seen significant improvement in CPU timings and if you have wisely chosen your tablespace sizing e.g. extentsize and pagesize I/O also will decrease... This feature is specifically helpful if you have an index on two columns and you have queries on second key of the index... This wisely chooses the index keys which shall be evaluated for the match!

Second improvement is in terms of I/O, Smart Prefetching... This is particularly helpful for data spread over a highly clustered disk or if index pages have low density... In such cases it can automatically switch from sequential to readahead (a new type of prefetching) prefetch... This is more efficient and automatically switched to readahead if optimizer see that sequential prefetch is wasting I/O...


There are a lot of new SQL optimization enhancements, these are automatically taken care by optimizer when needed... For example now hash joins will be used by optimizer even if data types do not match, Early Aggregation of data, early removal of distincts, improvement in estimation of performance cost... There are various new indicators introduced in EXPLAIN_TABLES and EXPLAIN info generated by db2expln, which shall indicate if any of these features have been used or now... More on that latter...


Another good improvement is that RUNSTATS will now support index sampling, which shall avoid the need to scan the whole index for collecting statistic... Also, the new parameter auto_sampling will enable sapling for all background statistics gathering... Another improvement from statistics gathering perspective is the Automatic Stats gather for Statistical view when you set auto_stats_view... Talking of statistical view reminds that there are few other improvements e.g. now actual statistics from expression columns will be respected for generation of a more apt plan...


There are some improvements in intra-parallelism for query... Most catchy one is the ability to do parallel scans on range partitioned tables and indexes... Also the intra parallelism can be controlled from application by calling ADMIN_SET_INTRA_PARALLEL... Also degree of parallelism now can be controlled for a particular workload by using MAXIMUM DEGREE option in ALTER WORKLOAD...



There is an important improvement in STORAGE and I/O i.e. introduction of STORAGE GROUPS... Earlier till v9.7 if I was chosing ASM tablespaces, I had to loose on distribution of I/O... i.e. all the tablespaces with ASM were created in the path designated for automatic storage (which is provided at the time of DB creation or later by alter DB)... But now one has to create storage groups and attach one or more storage path to a group... At the time of tablespace creation one will have to attach them to a storage group... So now I can create all my ASM tablespaces on different disks... So if you have already read my previous posts on tablespace design (1, 2, and 3) then you will know how good is this feature...

I will wind up this post now and may be will try to put up all these features in little detail...

Till then keep optimizing...

So all those people who are running on v9 or prior should definitely plan to migrate to this new version...

Oh yeah, the compatibility as well has been improved and is as good as 98%... well do not trust me??? read for yourself this post by Serge Rielau...


Other posts/blogs regarding the new release:

Serge Rielau has done a series of post on new features... Follow him on twitter to read more...


DB2Night Show hosted by Scott Hayes has done an episode on same and there is a sequel to follow...

A few tips for faster Bulk Refresh of a Table

So I am back with some tips to improve insert performance... I am wondering why I did not choose this for my 2nd chance @ db2night show's DB2 has talent...
Well a lot has happened in DB2 world meanwhile... There was a new LUW release and I shall be soon writing some stuff about that..
So there are various scenarios when you have to do a bulk insert and I too had to deal with one of those... The story and various solutions which came to me may bore you, but stick with it so that you know what you should not do and I will share some piece of code at the end... :)

There are obviously other tips for improvement of bulk inserts or any type inserts but those are more to do with your DB CFG/ DB physical design... i will here share more on how you can handle it programatically...


So there was this very complex query which read data from 6-7 tables all of which had around 4-5 million of records... With all the filters and everything the record returned by the query was around 6-7million... Obviously you can not process this chunk of data at once at your server and hence you pagenate the data and read only in bits... But again reading page by page means the view query is fired again and again and then sorting also happens for every new page read...
So a better way is to either use a MQT or a just a table which is loaded with this data and rowsequence and then you can read in bits based on rowsequence... We had used the table approach in our scenario...

I believe usage of MQT is good when you do not have to access MQT directly and your queries written over under-lying tables automatically use MQT...
But the problem was the problem was the insert of data in the temporary table was taking a huge amount of time... Also we had indexes on a few columns which were accessed during processing... This is was not a single case and there were few more cases of such processes which did bulk delete and bulk insert which were slow... Moreover these temporary tables were to be marked/updated so that one know how the failure recovery should happen in case the process is Aborted/terminated abnormally... Another problem which complicated it, whatever solution I had to give should be generic and should work on Oracle and SQL Server as well (well that comes with my job)...

Someone suggested that one can create a MQT for the query which populates data in the temporary table (and counter parts Materialized View and Indexed in Oracle and SQL Server respectively)...
The problem was update of MQT counterparts in Oracle and SQL Server is either difficult/not possible/require special license...

So the suggestion was extended to use this MQT (and MV and IV in Oracle and SQL Server) to load data in temporary table...
But I discarded it...
You first load 7million records in one table (MQT is essentially a table) and then load a table from this table... As a DBA you are doing your best to screw yourself down the line...


So I thought if MQT is also maintained as a Table and DB2 internally populates data when doing a full refresh, so lets try to think how DB2 would be optimizing this process...

So I thought
1) May be it truncates the table instead of deleting the records...
So i created a stored proc to truncate the table but retain the storage (I can't just use the truncate statement cause I had to support Oracle as well, where syntax is different, BTW anyone has an idea why DB2 mandates the use of IMMEDIATE keyword?)

CREATE OR REPLACE PROCEDURE TESTPROC
( TABLENAME VARCHAR(30), SCHEMANAME VARCHAR(15))
LANGUAGE SQL
BEGIN
     DECLARE STATEMENT1 VARCHAR(1024);--
     SET STATEMENT1='SET CURRENT_SCHEMA=' || SCHEMANAME; --
     EXECUTE IMMEDIATE STATEMENT1;--

     set CNT=1;--
     SET STATEMENT1='TRUNCATE TABLE ' ||
                      TRIM(SCHEMANAME) ||
                       '.' ||
                       TRIM(TABLENAME) ||
                       ' REUSE STORAGE IMMEDIATE';--
     commit;--
     EXECUTE IMMEDIATE STATEMENT1;--
     commit;--
END;



But this still resolved issue of deletion... Inserts were still taking alot of time... So second thought which came to my mind was...
2) May be it will drop the indexes on MQT before populating data(disable index is a requirement which DB2 team should start looking at)

So modified the above stored proc...
CREATE OR REPLACE PROCEDURE TESTPROC
( TABLENAME VARCHAR(30),  SCHEMANAME VARCHAR(15), COLUMNLIST VARCHAR(1024), INSERTVAL VARCHAR(1024))
SPECIFIC TESTPROC
LANGUAGE SQL
BEGIN
  DECLARE STATEMENT1  VARCHAR(1024);--
  DECLARE STATEMENT2 VARCHAR(2048);--
  DECLARE INDEX_STMT WASADMIN.VARCHAR_ARR;--
  DECLARE CNT INTEGER;--
  
  SET STATEMENT1='SET CURRENT_SCHEMA=' || SCHEMANAME; --
  EXECUTE IMMEDIATE STATEMENT1;--
  
-- Truncate the table before loading/refreshing with new data
  set CNT=1;--
  SET STATEMENT1='TRUNCATE TABLE ' || TRIM(SCHEMANAME) ||  '.' || TRIM(TABLENAME) || ' REUSE STORAGE IMMEDIATE';--
  commit;--
  EXECUTE IMMEDIATE STATEMENT1;--

-- Get a copy of index creation statements...

  FOR CR1 AS a CURSOR
  WITH HOLD
  FOR
     (SELECT INDNAME FROM SYSCAT.INDEXES WHERE TABNAME=TABLENAME)
  DO
    SET INDEX_STMT[CNT]='CREATE INDEX ' ||
                          CR1.INDNAME ||
                            ' ON ' ||
                              TABLENAME ||
                                  '( ';--
    
    FOR CR2 AS a CURSOR WITH HOLD FOR
    (
        SELECT I.INDNAME,
        IC.COLNAME ||
            CASE WHEN  IC.COLORDER='A' THEN ' ASC ,' ELSE ' DESC ,' END AS COL
        , IC.COLSEQ  
        FROM SYSCAT.INDEXES I JOIN SYSCAT.INDEXCOLUSE IC ON I.INDNAME=IC.INDNAME
        AND I.INDSCHEMA=IC.INDSCHEMA
        AND I.INDNAME=CR1.INDNAME
        AND I.TABNAME=TABLENAME
        ORDER BY IC.COLSEQ
    )
    DO
      SET INDEX_STMT[CNT]=INDEX_STMT[CNT] || CR2.COL ;--
    END FOR;--

    set INDEX_STMT[CNT]=SUBSTR(INDEX_STMT[CNT],1,length(INDEX_STMT[CNT])-1);--

    set INDEX_STMT[CNT]=INDEX_STMT[CNT] || ')';--

    SET CNT=CNT+1;--

-- Drop the index once its definition is stored...

    set STATEMENT1='drop index ' || CR1.INDNAME;--
    EXECUTE IMMEDIATE STATEMENT1;--
    commit;--
  END FOR;--
  

-- Once definition is captured for all indexes (and they are dropped) insert the data in the table using the
             -- Column list provided in the arguement
             -- and values
              --(which can be VALUES (ValueFor_Column1, Val_ForColumn2, .... Val_forColumnN)
              --or can be a select stmt

  set STATEMENT2='INSERT INTO '
                      || trim(SCHEMANAME) ||
                           '.' || trim(TABLENAME) ||
                               ' ( ' || COLUMNLIST ||
                                      ' ) ' ||
                                        INSERTVAL;--
  EXECUTE IMMEDIATE STATEMENT2;--
  commit;--
  
-- Now re-create all the indexes...
  FOR CR3 as a CURSOR
    WITH HOLD
    for
      ( SELECT T.STMT FROM UNNEST(INDEX_STMT) WITH ORDINALITY AS T(STMT,ID))
  DO
    EXECUTE IMMEDIATE STMT;--
  END FOR;--
END;



I could have used files to store the index creation instead of variable, but i wanted to make it generic so that it works even when new indexes are added and wanted to avoid I/O.


Now a tip to ensure that for such tables, when cardinality shoots high during a bulk insert, you can mark them as VOLATILE by

"ALTER TABLE TABLNAME VOLATILE"

Further performance gains can be achieved if you are ready to compromise on recovery of the table being loaded. In that case you can mark the table not logged just before the insert:

"ALTER TABLE TABLENAME NOT LOGGED INITIALLY"

If anyone can help me figure out what other optimizations are done by MQT for refreshing a table, I can try to write a piece of code for that as well...

Keep optimizing...

Virtual Indexes: My 2nd Chance in DB2 Has talent 2012

I will share the story of my 2nd chance at DB2 has talent... A better experience and managed the time well... But everyone was at their best in that episode and for a moment I thought have a got a wildcard entry in final round of DB2 has talent... Lot of people discussed the interested topics and a few discussed some features/tools which are not well documented... I too had chosen a topic which is not much discussed: "Virtual Indexes"... A way by which you can evaluate your indexes without even creating those... So let me start with it (and I will keep a check on my typos)...

So how many times you have faced an issue of poor performing queries and you feel probably adding an index will help improve your queries performance... But then you realize one index will not be sufficient for all the queries and you are wondering which columns to be combined for indexing... The permutation combination is generally good enough for you to not try actual creation of indexes as that will take some time and if it is not good you need to drop and start over...



So I made up a story... I had a table design, pretty simple one...

Three tables, two of those mapped to each other in the third table by their key columns... The mapping table has a self referencing relationship of parent-child...

I had a query which was being executed on the tables, again a very simple query... Join between 2 tables through the third table and then the third table was also involved in a self-join... But still the query was taking a lot of time to be executed... I guessed probably the indexes are missing and though of using index Adviser...

I could have achieved the same by setting my current explain mode=advise indexes and then run the queries and see what are the new indexes advised in ADVISE_INDEX table... But I was surprised to see the result in the table...

I was confused, every other column in my select list or in my where clause made a place in one or the other advised index...

I was not able to figure out which index, I shall use and which one I shall ignore... The major problem was creating all these indexes will slow down performance for my CRUD operations... A dba won't have all the time in this world to try out all the indexes in every possible permutation and go into the loop of "create index"-"generate plan"-"check usage"-"drop index"-"repeat"...

I was wondering if there is a way to force optimizer to assume as if an index is there and then generate a plan using the index if possible... Then I came across an option in db2advis and a value for "current explain mode"...

Once you have got the index suggestions, you can set your current explain mode to evaluate indexes and then run the queries which you think will be benefited by suggested indexes... then use db2exfmt to format the explain info... Alternatively using -p option in db2advis you can force the adviser to store explain info for the suggested changes... Then use db2exfmt you can generated the explain plan and see if suggested indexes are used or not... But I was not satisfied with the plan generated for any of those suggestions, specially when I ran the update queries for those tables in "evaluate indexes" as current explain mode, I was more convinced not to create all of them...

I took a backup of suggested indexes and then deleted the rows from ADVISE_INDEX and repopulated them from backup in various permutations to check which combination is most effective but none of them were as convincing... So I decided to insert my own rows in ADVISE_INDEX indexes i thought will benefit the select queries without having much impact on updates and inserts...

Something which I could not discuss during the presentation is I had a some challenges in trying out my own indexes, cause initially the optimizer ignored the indexes I imagined (or virtually created)... Then I had to work out the proper values for NLEAFS i.e. no of leaf nodes for your index tree (roughly no of distinct values in your column combination), NLEVELS means no of level in your index tree (is one of most difficult parameters to decide), FULLKEYCARD i.e. distinct key values in your index (a little easy compared to other a select distinct should do), FIRSTKEYCARD/FIRST2KEYCARD/FIRST3KEYCARD are for cardinality in first column, 2nd column and 3rd column in your index (again a little easy)... NEVELS can be decided by equation 2^x=no of distinct values where x is nlevels... SYSSTAT views can help you in deciding these parameters... COLCOUNT defines the no of column in the index...COLNAMES will define the columns which are part of index... INDEXTYPE (CLUS/REG/DIM/BLOK ) defines the type of index...

Finally with a little struggle and little time I could decide the best indexes for my tables... On exploring more about this feature I realized that I can virtually drop an index and check how the query performance is impacted before I actually drop it... All I have to do is insert a row for an existing index USE_INDEX column set to ‘N’ and EXISTS as ‘Y’...
I wrapped up with putting a slide for advantages of this (I will just put the slide here and will not be explaining)...

To end this blog I will just mention that my DB2 z/OS folks can also use the same feature DSN_VIRTUAL_INDEXES ... Oracle user can also try the feature of Virtual indexes by using NOSEGMENT clause in index creation... I will sign off this blog entry... Might take a few days to return, I have to enhance my skills on HADR and DB2 Internals (process and memory model)... Might have a short blog for insert enhancements and piece of code that you can probably re-use...

My Experience @ DB2 Has Talent: DB Design Basics

Hi people, I recently participated in DB2 has talent and it is still going on [now am out of it :( ]...
Make a point to register for rest of the episodes of this, I assure you of great DB2 tips...
I will be sharing my first presentation for the show!!! Which was about "db physical design"... I will avoid putting much text here, as this is more or less based on my blog series "Optimize your Tablespaces and Bufferpools"... You can check them here

Optimize your Tablespaces and Bufferpools-1: Tablespace Design design

Optimize your Tablespaces and Bufferpools-2: Tablespace Sizing

Optimize your Tablespaces and Bufferpools-3: Bufferpool Optimization

I could not complete the presentation for obvious reasons (it was a bad choice, a topic you have yourselves spread over 3 blogs)... Additionally there were too many typos (which I do quite often in my blogs as well)... You can read Susan's blog to get more specific presentation tips!!! I will share the slides I had used for presentation...

Be critical of what you did n be Logical to what you gonna do

This is going to be an offbeat post!!! You might not find a very good technical tip in this but I hope this will help you improve your attitude towards performance tuning...

Nothing n trust me nothing can subsidize a poorly designed application...

I will just discuss some basic mistakes which people commit, which should be revisited:

1) indexing
If you have not indexed properly you should not expect too much out of a processor even if it is say 10GHz!!! You have to live with skewed I/O

2) Placement of Tables
If you have not segregated your tables as per their nature and volume of data, using too many container is not going to help you

3) Level of Normalization
Some redundancy won't ruin your application esp if the data is write once, read always... I know this is going to be debated...
I heard someone saying "It is better to have something which works rather than something Architecturally correct and does not work!!!"

4) access queries
do you expect a magical h/w solution to improve performance of a query "select * from my_Blogs where upper(Title) like '%CRITICAL%LOGICAL%' "
No hardware can help you scale when actual problem is with your Application and Database design...

5) Designing at a per-mature phase in Development Life cycle
An even more basic problem... You did not care to understand business, and screwed up the db design and now you have to fire a count(*) to get count of records for a blog reads from a given ACCOUNT, if you knew business may be you had planned this better...

6)improper molding of business scenarios in your architecture
Business defines requirements and not other way round... As a DBA understand business and it is absolutely normal to question business requirements...
My user requirement might ask me for a new functionality, but I need to see how it fits in my current solution and its architecture...
Just do not try to solve a problem with an advanced feature, which could have been solved in a much simpler way... For Instance many people think that Partitioning or MDC (on a column which has even distribution of data) is gonna help them get an awesome performance for tables which has huge volume of data...

7)Get out of conventional notions...
I have seen well experienced people thinking that Stored Procs will perform better compared to a prepared statement wrapped in a java code... or application caching will always help... These are myth... Stored procs are definitely preferable in certain instances, but not always... Putting too much load on application cache leaves too less memory with your application for itself and for any data which is not cached and will be read online...


Please guys, these were just examples and I do not want people to carry the notion that I have committed these blunders...

I really pity people who think that performance is more of an infrastructure and hardware issue... Or going for some advanced features of a s/w will help them... If you are suggesting these to your clients, BEWARE your client will curse you first for providing a bad solution and second for increasing its IT cost to support a poorly designed application...

I hope this post will help think more rationally... So when you hit a performance bottleneck, be critical of what you have already developed and be logical about what solution you are going to propose...

MERGE: MAKE Your UPSERTs quick!!!

Hi guys... I am back... this time as a Advanced DB2 9.7 DBA... Thanks to Sathy for helping me prepare for the exam and Beth Flood for giving me a free voucher (otherwise I never had guts to put 5k on stake for my capabilities)... But very soon an all new DB2 is expected I am hope it will have hell lot of new features and I will have to upgrade or take the certification exams from scratch...

Well recently I have been working with some scenarios where people came to me with an update statement which was working very slow... When I looked at it, I said "I need to tell people there is something like MERGE"... There was one thing common, all the statements were having correlated sub-queries for set value... I replaced their queries with a MERGE and it worked like wonder...

Let's see some example...

UPDATE Table1
SET T1Col1=(select T2Col1 from Tables2 where Table1.T1IDPK=T2IDPK);

So the problem with this query is the inner query will be executed for every row of table Table1...

So I wrote them a query MERGE, which shall first create a HASH JOIN and then do an RID based update:

MERGE INTO Table1
USING Table2 on (Table1.T1IDPK=Table2.T2IDPK)
WHEN MATCHED THEN UPDATE
SET Table1.T1Col1=Table2.T2Col1;

Another scenario was of a process where a scheduled process will pick up rows from a table (which has designated pagenumbers/rownumers) in batches and after processing certain number of rows in every batch the scheduled process will sleep... Remaining rows shall be renumbered and partitioned in batches...

UPDATE PagedTab t1
set SEQNUM=(select row_number() over(order by PagedTab_IDPKColumn) from PagedTab t2 where t1.PagedTab_IDPKColumn=t2.PagedTab_IDPKColumn);

So I just changed it to below:

MERGE INTO PagedTab t1
USING (select PagedTab_IDPKColumn,row_number() over(order by PagedTab_IDPKColumn) as rnum from PagedTab ) t2
on t1.PagedTab_IDPKColumn=t2.PagedTab_IDPKColumn
WHEN MATCHED THEN UPDATE
SET t1.SEQNUM=t2.rnum;

I have simulated the below scenrio to make it more easy to be understood as the actual was more complex...
Third place was where there was a Mater-Detail kind (Dependent Entity) kind of relationship and some details were stored in Master Table and for every IDPK in master table one and only one record existed in detail table... ACCOUNTID and LASTACCESSDATE and STATUS were some of the details... some one wanted to first find out the LATEST record (based on date) from detail table for every ACCOUNTID and then find out primary key for that record and then for that PK whatever status is found in detail had to bee copied to master... So here goes the query people will write in first attempt...

UPDATE MasterTable outTab
SET STATUS=
(select STATUS from DetailsTable t2 join
(select ACCOUNTID, MAX(LASTACCESSDATE) DT from DetailTable group by ACCOUNTID)t1
on t1.ACCOUNTID=t2.ACCOUNTID and t2.LASTACCESSDATE=t1.DT and t2.IDPK=outTab.IDPK
)temp;


Actually the query which was written had used one more IN clause which I have converted to JOIN to avoid more confusion...

I modified it...

MERGE into MasterTable t1
USING
(select IDPK, DENSE_RANK() over(PARTITION BY ACCOUNTID order by LASTACCESSDATE) rn, STATUS from DetailsTable) t2
ON (t1.IDPK=t2.IDPK and rn=1)
WHEN MATCHED THEN UPDATE
SET STATUS=t2.STATUS;

And this avoided a join and sort which was being done for row... Did a single sort a hash join and an update based on RID... Wow!!!

One final instance where I saw something like this:

UPDATE TableX X
SET
Col1=(select Col1 from TableY Y where X.IDPK=Y.IDPK),
Col2=(select Col2 from TableY Y where X.IDPK=Y.IDPK),
Col3=(select Col3 from TableY Y where X.IDPK=Y.IDPK),
Col4=(select Col4 from TableY Y where X.IDPK=Y.IDPK)
;

and then

INSERT INTO TableX (COl1, COl2, COl3, COl4)
Select COl1, COl2, COl3, Col4 from TableY Y
where not exists (select 1 from TableX X where X.IDPK=Y.IDPK);


and finally...

DELETE FROM TableX X where not exists (select 1 from TableY where X.IDPK=Y.IDPK);

I will leave it upto the readers to interpret the purpose of these statements... I will just give an alternate query...


MERGE INTO TableX X
USING TableY Y on (X.IDPK=Y.IDPK)
WHEN MATCHED then UPDATE
SET
X.COl1=Y.COl1,
X.COl2=Y.COl2,
X.COl3=Y.COl3,
X.COl4=Y.COl4
WHEN NOT MATCHED THEN INSERT(COl1, COl2, COl3, COl4)
VALUES (Y.COl1, Y.COl2, Y.COl3, Y.COl4)
;

This has combined the UPDATE and INSERT statements and is much more efficient cause the hash join is executed once as opposed to execution of 4 correlated sub-queries for every row in TableX for Update and then for INSERT an anti-join is performed...

The delete can not be optimized or combined in merge which is a little disappointing and I hope to see this feature in DB2 very soon... A clause of "WHEN NOT MATCHED BY SOURCE" exists in SQL Server using which I could have just said:


MERGE INTO TableX X
USING TableY Y on (X.IDPK=Y.IDPK)
WHEN MATCHED then UPDATE
SET
X.COl1=Y.COl1,
X.COl2=Y.COl2,
X.COl3=Y.COl3,
X.COl4=Y.COl4
WHEN NOT MATCHED THEN INSERT(COl1, COl2, COl3, COl4)
VALUES (Y.COl1, Y.COl2, Y.COl3, Y.COl4)
WHEN NOT MATCHED BY SOURCE THEN DELETE
;


Well this blog is bloated too much and I do not want to bore people, you can have multiple WHEN MATCHED and WHEN NOT MATCEHD clauses and combine with another condition (AND col1=col2) and have different different action for every branch, hence you will write one single statement instead of writing one update for every condition... I will try to put some examples sometime...

BTW thanks to all my readers... I feel lucky and honored to be listed in "My Favorite DB2 Blogs" by Troy Coleman @ db2tutor

If you are reading this Mr. Coleman, trust me db2tutor and db2geek are my frist reference points for any query... :) and thanks for putting me on the list...

SQL Anti Logic: Usage of CASE Statement

Hello all... Reviewing queries while development and fixing performance issues in SQLs are part of my job... I get lot of queries where people have written a really bad query and thank god we have this process in place to tune such queries... You can take this as a sequel to my Post on "Anti SQL Logic"

So someone had a requirement of getting Account Statistics from a table storing statistics for every account (I have tweaked the requirement and actual query, a little for abstraction and a little to highlight the significance of improvements done in query) ... Stats are stored in periodical fashion and I can configure a period code to get statistics for a specific period say monthly or say yearly... Additionally I want stats for my current period as well as for my previous period...

Probably a naive approach that come first in mind i below:

1) I get a union of all the possible combinations for previous period and current period. Below is basic query for that...


SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'Q' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 3 +1 //for current period
--AND {?FORMONTH} <= (AST.STATSPERIOD-1) * 3 //for previous period AND AST.FORYEAR = '{?FORYEAR}'

UNION

SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'H' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 6 +1 //for current period
AND {?FORMONTH} <= AST.STATSPERIOD * 6 // for previous period AND AST.FORYEAR = {?FORYEAR}

UNION

SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'Y' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND AST.FORYEAR ={?FORYEAR} //for curr period
-- AND AST.FORYEST={?FORYEAR}-1 //for prev period

2) I also get a result of last month/quarter and 2nd-half of previous year result sets (so that if I pass 1st month as my input then I shall get previous years last month ;-o )

3) I join these results sets

4) Use an inner query to get what is the configured period code and then I get stats only for that period code...

Well this is quite procedural and algorithmic... SQLs are not procedural (and anyways I am personally very much against usage of PLs codes with RDBMS especially the cursor based things, well that is debated topic)...

Well I wrote a Structured Query to get what was required making the best I can make of CASE:

1) Select ALL the columns for statitics twice
i) Once actual value if it your period value (e.g. 4th quarter) else select it as 0
ii) Select stats column as 0 if it matches your period value else select actual value
This will help you categorize stats in column wise fashion where current period columns will be holding actual value and 2nd set of columns will be holding data for other periods.

2) now you need to filter the current period and previous data
i) Filtering current period data is pretty easy as you know the period number
ii) For filtering the period number for previous period I used below case logic

(CASE 'M'
WHEN 'M' THEN 2
WHEN 'Q' THEN (2-1/3)+1
WHEN 'H' THEN (2-1/6)+1
WHEN 'Y' THEN AST.STATSPERIOD
END) in
( {PeriodNumber}, //Current Period number
(CASE {?FORMONTH} WHEN 1 THEN //Logic to arrive at previous period
case {PERIODCODE} when 'M' THEN 12 //if monthly then prev month is 12
WHEN 'Q' THEN 4 //if quarterly then prev quarter is 3
WHEN 'H' THEN 2 //if halfyearly then prev period is 2
ELSE {PeriodNumber} //if yearly, prev period number does not matter
END
ELSE {PeriodNumber}-1 end) //finally it should be input period number-1
)
AND AST.FORYEAR IN( {?FORYEAR},
(CASE {?PERIODCODE} WHEN 'Y' THEN {?FORYEAR}-1
else
(case when {PeriodNumber}=1 then {?FORYEAR}-1
//If my input number is 1 then I need to consider prev year stats as well
ELSE {?FORYEAR} END)
END)

3) Now I have got 0 as prev period stats in rows which belong to current period and 0 as current period stats for rows which belong to prev year. All I need to do is sum
SELECT
SUM(temp.CURR_MAXIMUM_BALANCE),
SUM(temp.CURR_MINIMUM_BALANCE),
SUM(temp.CURR_AVG_BALANCE),
SUM(temp.CURR_CREDIT_COUNTER ),
SUM(temp.CURR_INTERESTPAID),
SUM(temp.CURR_CHARGES),
SUM(temp.PREV_MAXIMUM_BALANCE),
SUM(temp.PREV_MINIMUM_BALANCE),
SUM(temp.PREV_AVG_BALANCE),
SUM(temp.PREV_CREDIT_COUNTER ),
SUM(temp.PREV_INTERESTPAID),
SUM(temp.PREV_CHARGES
from
(select
case when AST.STATSPERIOD=2 then MAXIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then MINIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then AVG_BALANCE else 0 END as CURR_AVG_BALANCE,
case when AST.STATSPERIOD=2 then CREDIT_COUNTER else 0 END as CURR_CREDIT_COUNTER ,
case when AST.STATSPERIOD=2 then INTERESTPAID else 0 END as CURR_INTERESTPAID,
case when AST.STATSPERIOD=2 then CURR_CHARGES else 0 END as CURR_CHARGES,
case when AST.STATSPERIOD=2 then 0 ELSE MAXIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE MINIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE AVG_BALANCE END as PREV_AVG_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE CREDIT_COUNTER END as PREV_CREDIT_COUNTER ,
case when AST.STATSPERIOD=2 then 0 ELSE INTERESTPAID END as PREV_INTERESTPAID,
case when AST.STATSPERIOD=2 then 0 ELSE PREV_CHARGES END as PREV_CHARGES,
FROM {?schemaName}.ACCOUNTSTATISTICS AST
[where statement discussed above]
group by ACCOUNTID


I guess I have made it very boring and complex to understand... But I hope it will help people understand how flexible can be usage of CASE statements...

I am looking at putting a content on improving INSERT performance... (this again has come from processing of rows from temp tables, where data is populated in bulk and deleted in bulk)...

An Alternative to truncate for pre v9.7

So many a times people have to truncate a table in run time, i.e. while processing a batch or a temp tabel or while flushing a table which stores some periodic stats etc... DB2 9.7 has an Amazingly fast command which will help you do that in flash of a blink called truncate... But ooops you are not using db2 v9.7... you had upgraded to db2 9 or db2 9.5 recently and can not take the effort required to port your application to new version and do all the testing again... No sweat... I will be sharing a few such tips which will help you imitate truncate table


1) Which is generic for all platforms...

ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
commit;


but I am not very sure if space acquired by table is retained in this case

2) Obviously

ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY;
delete from tablename;
commit;


3) This will be specific to a OS platform and I will not suggest using this if you have to code it in your application:

i) *NIX --> import from /dev/null of del replace into tablename
ii) Windows --> import from nul of del replace into tablename

This operation is though faster than normal delete but is logged and hence will not be as performant as first... Some people argue that second option is not recoverable, but we are looking at an alternative for truncate (which is not recoverable either) and hence recovery is not my priority... Moreover I have committed the transaction as soon as I fire "NOT LOGGED" command for "tablename" hence should not be an issue...

Improvise your Backup-Restore

Somehow I am managing to keep-up with my busy office life and on call support... Which it seems is literally 24X7...

I find myself out of topic while talking to old mates... Office and being single are only topics I could blabber about... This blogs should give me a change... :)

So this is not going to be a HADR tips or how you should decide your backup schedules... It is more on how you can save your time while taking backups and how you can improvise the speed of restores (and hence reduce non-availability time)...

To start with, it is generally advised that you take backups when you system is not being accessed or it having low-traffic... And well while restoring I won't offer the same advice, you don't actually have much of a choice... Your system is not available and best you can do is to reduce the time it takes...

Your throttle the priority by
SET UTIL_IMPACT_PRIORITY FOR utility_id TO num

which will throttle the backup utility or you can set the same by specifying
"UTIL_IMPACT_PRIORITY" clause in the backup command as well...
We should also recollect that if your UTIL_IMPACT_LIM dbm cfg is set too low, no matter how high you set UTIL_IMPACT_PRIORITY itcan not go beyond the threshold set by UTIL_IMPACT_LIM...

To have the backup operation faster it is good to increase your BACKUP BUFFER SIZE/RESTORE BUFFER SIZE "BACKBUFSZ"/"RESTBUFSZ" dbm cfg or you can override it in backup/restore command with "BUFFER buffer_size"... Also you should choose a good (not your lucky number) number for your number of backup/restore buffers to be used... This has to be decided by your db size and RAM available... It can be set with "WITH n BUFFERS"... Multiple buffers specially boost up the performance of backup when using PRALLELISM... Prallelism decides the number of buffer handlers...
Then comes compression, my favorite... If you think compression slows down the speed of backup due to CPU processing lag which is involved in compression algorithm, then you correct about your analysis but not about final conclusion it leads you to...
Compression algorithm takes some extra CPU cycles but you save a great deal on I/O cycle which are more expensive (in terms of time and their availability)... hence compression will not just save space but time as well... and plus your buffers can be utilized easily... Same applies while you are restoring a backup, you spend less time on reads and more data fits in your buffer and hence your restore is faster...
Additionally going by basic h/w an OS concepts having a backup file on disk which is other than one where your db resides/going to be created (during restore) is often faster... Of course there are other reasons as well for which you should not store backups on same disk where DB resides...


Incremental/delta backups helps you take smaller backups which saves time backing up only
what has changed (or delta of change) and while restoring you have to restore a full backup and then changes are to be applied one by one hence makes the restore slower... But if you use backup/restore to shift data periodically (say once a day or week) to a different server incremental/delta backups are best... Cuase they take less time to be backed-up and then less time to be restored (as your restoring only the diff/incremental backup)...

Finally, snapshot backups using Netapp/Tivoli etc might boost up the back up process drastically... Though those are bit expensive to invest into, but if you have very (and I mean very) large volume of database your investments will be well paid off...

TO close this, backup is nothing but making an image of your database in a single file... Database is stored in datafiles which are spread over various blocks in your disk... Now if those blocks were scattered, backup would be quite slow, and hence it is gives your another reason to keep your db well re-orged... Also if your db is spread across various disks/file system it could boost the performance of backups and restores as well...

Optimize your Tablespaces and Bufferpools-3: Bufferpool Optimization

Hi all...

It has rather been quite a busy quarter for me... Office has kept me quite busy and was quite pre-occupied with my job... But here I am back with all the stuffs I wanted to share...
We have already seen how to wisely choose optimum value for various tablespace related parameters and how to decide on tablespace sizing... Now we will proceed to the last thread in this discussion and see how one can optimize bufferpools so that it can match the expectations set by tablespace optimiztions (that we have learned in pervious two posts)...

TO start with as a rule of thumb you should have a separate bufferpool for temporary tablespace and if you have different user temp tablespace(which I strongly recommend) you should have a diff bufferpool for that too... If you do not do this, you will see a lot of sort overflows or low bufferpool utilization in snapshots or high read/write operations @ disk level...

Also you should ideally have different bufferpools for index and data tablespaces... This is help you avoid situations where an index tree forced to flushed out of bufferpool to make space for a table read operation...

More importantly you should have different bufferpools for tablespaces storing transactional/operational (which is generally huge in volumes and cardinality) tables and a separate one for tablespaces which store config data tables... As config data is generally queried again and again e.g. every transaction you do in a bank will fetch transaction code (Cash Deposite or withdrwal etc are transaction codes) for your transaction... If you tablespace storing the table for transaction codes available in system is mapped to same bufferpool which is also mapped to tablepace storing table which logs all the transaction (e.g. your cash withdrawal activity and your friends credit card payment activity etc)... In such a scenario when a user does an enquiry on transactions it will flush the transaction code data it will have to read gain for a new transaction... If a transaction enquiry is frequent activity it will screw up cause of this faulty bufferpool design...
You can keep a watch on bufferpool utilization with sysibmadm.snapbp snapshot view... Ideally I will say table-bufferpool utilization should be above 90% (but 85% might be acceptable in certain cases)... For index bufferpools if it is below 95% then you need to worry, ideally it should be arround 97%...


Generally Even in an OLTP system, transactional data is read sequentially when working with a report... This helps making best of I/O cycles (obviously this decision should be based on queries you have in you system)... In such cases you have an option to specify NUMPAGEBLOCK in bufferpool which means the number of page-blocks that would be kept aside specially for block data... you can keep a watch on this utilization of specification with pages_from_block_ios, unread_prefetch_pages, pool_async_data_read_reqs and prefetch_wait_time monitor elements... Not to mention that it will also help in sequential prefetching...

Unlike other performance improvement techniques e.g. indexes and table-partitioning etc. a need for which could be easily guessed over a period of monitoring, a flaw in tablespace design or bufferpool allocation can not be so easily caught at times... The snapshots or anyother monitor data might not be indicative or directly pointing at it... I will suggest thinking about these well in advance... Many people think that this is more of a production call... But a production-DBA knows less than you (the development DBA, involved in table design) about the number of tables, volume of data and nature of queries...

I shall some time try to sum-up on how to use various monitoring techniques to keep a track of parameters set for your bufferpool/tablespaces...

Optimize your Tablespaces and Bufferpools-2: Tablespace Sizing

Hey people...
So I hope the last post was informative... This post is going to be rather short... Not cause of the topic, but cause of my lack of knowledge on this matter... I could have very well knocked it off, but thought of giving some basics (to which my knowledge is restricted) from where people can carry on with google...

We will discuss sizing of tablespaces and their separation... We will wind up with little on temp tablespaces...

If as a DBA you tend to think, you can allocate any space, on your disk for your tablespaces and allow them to grow unlimited by any fraction as incremental size, and can sit back... You are damn wrong... Things that can cause problems...

1) If you allocate even initial size (which may be too huge) for all the tablespaces, the table spaces which has too frequent writes might face issues like allocation of new extents and which might be too scattered cause initial allocation was not thought properly...

2) Same applies if you think allocation for every tablespace should go at same pace... You might end up killing the space requirements for tablespaces which are too frequently accessed for writes... Moreover you will be facing issues with cost for storage (sooner or latter)...

3) Also you need to consider the fact that, some tables will have very sequential access and the tablespace having those tables, should have a more incremental size otherwise your data is going to scattered and prefetching suffers...

4) Static data and configuraiton data needs to have a medium allocation at starting and the increment window need not be too huge as very rarely you add new configurations to the system and very rarely you access configuration in sequence (well this might not be true always)...

Now wat is the way out??? This is something I have learnt from my seniors at work...

1) List the tables, and list the tablespaces they belog to. List the tables' row size and its cardinality as well

2) Sum up the above data per tablespace and see at what rate the data increases for every table

3) Combine all these inputs to decide, what should be the initial size (do not keep it same as current data's requirement at least keep 10%-50% extra space depending on rate at which data grows) and what should be the increment size...

This will really help you get rid of time lags for your write operations...

Next is to place all such tables together in a tablespace, where you think there will be sequential reads... Prefetching will benefit from this (as long as you remember to schedule a reorg at a proper frequency)... Tables which has static data which hardly gets updated/inserted should go into one tablespace (which should be reorganized at initial setup)... We will see more about bufferpool planing in upcoming posts...

Last... People tend to ignore the importance of system temporary tablespace and user temporary tablespace...

It is important to separate these two and explicitly create a new user temp tablespace, this will really help your sort operation which can have more freedom of "space" in system temp tablespaces... All other temp processing might as well benefit... Also, you should consider keeping a separate bufferpool for user temporary tablespace... Also, if you have not allocated enough space for you temp tablespace, it will try to reallocate/acquire space for sort operations, this will cause a delay in result for your sorting operations, which is quite frequent in application we see these days (patient enquiry sorted by admit date or if you remember the transaction order in your bank statement)...

So the mantra is to think wisely and group your tables in proper tablespaces, then size them and decide proper growth for them... Meanwhile do no forget the reorganization of tables and indexes (frequently) and of tablespaces at times... Meanwhile never ignore the importance of temp tablespaces...

I will be back with some tips on deciding proper strategy for your bufferpools and how to make them do the best according to your tablespace and tables contained by it...

Meanwhile I have got a some content on backup recovery... Planning to write it some time...

Optimize your Tablespaces and Bufferpools-1: Tablespace Design design

hey people... I am back after a long time... Office had kept me busy, and it will continue to do so for next few quarters... After all we have promised ourselves to build a world class product, which got to be best!!!

Well so getting back to db2... In this post I will discussing little bit on the physical database design...

Lets discuss the tablesapces first...

So lets start with pagesize... If u are really concerned about growth of your data, you should better go for a 32K pagesize, offering maximum storage... For configuration data and static data though you can choose to go with a 4k/8k pagesize and you might see some improvement in bufferpool and disk utilization (especially if you have a table with random access for records)...

Next is file-system caching... DB2 always caches the data (and caches it in more usable format for it db-engine) in bufferpools... So needless to say, if your OS is also caching the data, it is wasting both time and memory... So you better keep filesystem caching off (thank god it is default in 9.7)...

Exten Size is something which should be wisely thought out (esp when using automatic storage)... It is the amount of data/index pages which will be written to a container before going to the next container... If it is too low, you have too much of scattered data whereas if it is too high you have too much of I/O load... In case you do not specify extent size while creating the tablespace it will take it from dft_extent_sz db cfg... A thumb of rule is to use following formula:
RAID disk drives * Disk strip size

Prefetch Size is the no of pages for pre-fetching... It should not be smaller then extent size in any case, other wise you end up wasting I/O cycles... Most optimum suggestion is to keep it same as extent size, but if you are using multiplexing of data pages, it might be a good idea to keep it in multiples of extent size...

Overhead and transfer rate are two mostly ignored aspects of tablespace design and people tend to go with defaults... See generally hardware performances changes much rapidly and moreover organizations tend to change the hardware more frequently compared to s/w (at times it is vice versa)... Hence you transfer rate (which conveys to optimizer the speed @ which data will be tranferred from underlying container) and overhead (which conveys the other disk seek and various latencies related to disk) should be set properly... If they are wrong it might impact as much as deciding for a table scan instead of an index scan just cause the optimizer thinks the seek time will be too much for index pages...

At last let's try to answer the question on "If DMS/SMS/ASM???"... Well I will not bore with what's pros n cons of each as you can get lot of content on internet on this, and I do not want to copy paste stuff... :)
Well I advise going for Automatic Storage, as it is more easy to manage and handles parallelism more efficiently... It can utilize RAW devices more efficiently...

Lastly you can enable I/O parallelism with-in a tablespace (if it is spread across multiple containers)... for example If you have RAID 7 array you can set the below registry parameter using db2set

DB2_PARALLEL_IO=*:7

I shall continue the topic in next post... Where I will discuss on sizing and increment for tablespaces and also the temporary tablespaces...

RUNSTATS and REORG-3: An effective REORG policy

Hi guys... so I am back with my blog sequence on reorgs and runstats... So just a recap my last two posts in this sequence had just explained "what is the importance of using RUNSTATs and REORGs" and then I discussed "How to 'effectively' automate RUNSTATS"...

So I will end the "saga" with REORG policy and it could not have come at a better time, when I had just fixed some major I/O anomalies with REORG (how many of you are thinking I am boasting?)...
Ok lets start with a problem statement...

I was told about about two problems, firstly Sudden Spikes in Reads at DB Server and Sudden Spikes in Writes at DB Server...

I really could not justify the Writes (but it struck me much later) but had an idea about reads... Also, at the design I had done for tablespace included some reserve for prefetching/blocked I/O... They were performing poor... Ooopss!!!

What helped me find it out about the bad performance of blocked I/O was this snapshot:

SELECT BP_NAME,
CAST(VECTORED_IOS as decimal(24,10)) / NULLIF((POOL_DATA_P_READS + POOL_INDEX_P_READS), 0) AS VECTORED_IO_FRACTION,
CAST (UNREAD_PREFETCH_PAGES as decimal(24,10)) / NULLIF(PAGES_FROM_BLOCK_IOS,0) AS WASTEFULL_PREFETCH_FRACTION, CAST((POOL_ASYNC_DATA_READ_REQS + POOL_ASYNC_INDEX_READ_REQS) as decimal(24,10)) / NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS) - VECTORED_IOS,0) AS ANSYNC_PREFETCH_EFFICIENCY FROM SNAPBP;

So I had to do some analytic... First thing I did was running the runstats on tables and indexes and then I tried to find the amount of defragmentation:

SELECT I.TABNAME TABLE_NAME, I.INDNAME INDEX_NAME, REPLACE(SUBSTR(I.COLNAMES, 2) ,'+',',') AS MEM_COLS,
T.LASTUSED AS TAB_LASTUSED, I.LASTUSED AS INDX_LASTUSED,
I.NUM_EMPTY_LEAFS/NULLIF(I.NLEAF+NUM_EMPTY_LEAFS,0) INDX_EMPTY_LEAF_FRACTION,
T.FPAGES-T.NPAGES TAB_EMPTY_PAGES_CNT, I.NLEAF+NUM_EMPTY_LEAFS/(num_partitions*extent_size) AS INDX_REORG_NEEDED_IF_GT_1,
T.FPAGES/(num_partitions*extent_size) TAB_REORG_NEEDED_IF_GT_1
FROM SYSCAT.INDEXES I JOIN SYSCAT.TABLES T ON T.TABNAME=I.TABNAME AND T.TABSCHEMA=I.TABSCHEMA AND T.TABSCHEMA IN (schemalist) AND T.TYPE='T';

I guess the column names are pretty self explainatory and I need not explain what I got was huge no of index leaves scattered around and tables which were defragemented... So to explain the problems in terms of observation:

1) Too many disk seeks happening for read (even for index scans)
2) The sequential bloced IO fails miserably as the blocks pertaining to same table/index are scatterd
3) The writes, a record inserted/updated might have to re-balance the indexes, which is scattered so write cost increases
4) Finally the free leaves (for indexes)/blocks (for tables) are not available sequentially...

So I decided to do a REORG.. Most of the times people re-org with "REORG TABLE SCHEMA.TABLENAME" Guys it is hardly gonna help... Actually the one should arrange data in such fashion with will be usefull... So it is always good to reorg based on an index which can help you fetch data faster when you are going by that index (so choose the index you use more often)... Wait that is not enough... Before doing a REORG on your tables, you should have REORGed all your indexes on every table...

So I wrote a stored proc to dynamially select an index for every table and generate re-org statements for "REORG INDEXES ALL FOR TABLE" and "REORG TABLE USING INDEX"... Guess what!!! those momentary spikes were past and we had a good stable bufferpool utilization...

Many people while reading my last post would have thought AUTO_REORG as an equally useful method to do reorgs... i.e. to leave it to the database manager... But you might end up messing your OLTP performance and even your batch jobs which do bulk inserts/deletes... So best way is to keep checking weekly/fortnightly/monthly with the above queries on syscat tables to check if reorg is required or not... I really do not rely much on REORGCHCK utility from DB2... It is also important to run reorg on a regular interval based on about of data inflows and gets modified in your system...

So what next??? Probably some SQL Tuning tips!!! Till then HAPPY PREFETCHING!!!

SQL Anti logic!!!

Well I am just breaking my sequence of RUNSTATS and REORG for a while (I promise my next blog will be having that)...

Actually I have just been back from an interview session... I took a few interviews for filling up vacancies for java developer... I just asked few basic SQLs, the general approach people took for those SQLs raises a smile across my face... Actually those were the most obvious approach that comes to our mind when we try to reach/approach such problems... I have seen such patterns quite often in reports and prepared statement during my reviews (of SQLs used by development)... But if you stretch a little bit, try thinking the Anti conditions (wondering what they are??? Just wait...), you might get a more efficient option...
Let me share a few to guide you thru this...

I will try to skip the table structure etc (unless it is very much reqd) in order to avoid boring you guys/gals...

1) OK, if you have to find out all those customers (from a CUSTOMER table in bank database) whose PINCODE (an integet field) starts with 56 (i.e. of a particular city/state)... The most obvious response is:

select * from customer where cast(pincode as varchar(6)) like '56%'

Weren't you thinking exactly that? But unfortunately that case will avoid an index scan and has a casting overhead... Put your Matriculation Hat on your head... Think of the max and min no having 6 digits (i.e. in INDIA pincodes are 6 digit) which can starts with 56??? Does that help??? So now you got a better query:
select * from customer where pincode between 560000 and 569999
If you thought that off pretty early (before I gave the hint), hats off to you...

2)Now if you have a timestamp/date column (in DB2 9.7 date will store time as well till the seconds) to store the transaction date for a banking application (sorry guys but that is the only domain I have worked with), and you want to list all the transactions that happened on a particular date? Obvious query:

select * from transaction_posting_table where date(txn_posting_date)='2010/04/30'

Some one who has attended an Oracle Usability/Oracle Enablement session for DB2 might think

select * from transaction_posting_table where trunc(txn_posting_date)='2010/04/30'

'2010/04/30' is '2010/04/30' with time 12:00:00.0000AM and trunc will also convert all the date/timestamps to that day's 12:00:00.0000AM... Hence we get all the transactions of '2010/04/30' irrespective of its time...

As these queries go for table scan, people suggest of adding an extra column which is always computed based on date(txn_posting_date) and then index... Ahhh!!! Before that check this out:

select * from transaction_posting_table where txn_posting_date)>='2010/04/30' and txn_posting_date)<'2010/04/30'+1 Kwel???
'2010/04/30' is '2010/04/30' with time 12:00:00.0000AM
and '2010/04/30' +1 is '2010/05/01' with time 12:00:00.0000AM...

So you pick up any time on '2010/04/30' it will satisfy above condition... The trick is to move the computation from LHS (i.e. instead of converting all the date/timestamp to 12 midnight, search for all date/timestamps b/e two midnights, one inclusive and other exclusive )... :)

3) Another famous interview question is (I have never asked this one) "Write a query to find the 2nd max salary from emp table". But I know it will send across a smile on many faces... (Yeah I know that, I did that in my graduation viva!!!...) So is this what you are thinking?

select max(sal) as secnd_max_sal from emp where sal< (select max(sal) from emp)

Yeah??? that is what you said in your graduation viva? Atleast I said that, but in an interview I will say differently... Well the problem is when you generate a plan you see two sort operation (I would frown at a query with one sort...)

select sal as secnd_max_sal from (select dense_rank() over(order by sal) rnk,sal from emp)tmp_rslt wher tmp_rslt.rnk=2;

(more extensible and works with just one sort and an rid scan!!! :) )

4) Another one that I have seen quite often, that people JOIN two table and the JOIN result is joined with third table on a condition where the key of third can be either matched to first or the second table... This happens more commonly when the first query is left outer join and the join condition can be on the key from first table or if it is null then on second column...
so a few optimization that comes to my mind (I will just write the JOIN clause):

>>A LEFT OUTER JOIN B on A.Aid=B.Bid JOIN C on (A.Aid=C.Cid or B.Bid=C.Cid)

Better way to write is:
>>A LEFT OUTER JOIN B on A.Aid=B.Bid JOIN C on (C.Cid COALESCE(A.Aid,B.Bid))

Another scenario is
>>A JOIN B on A.Aid=B.Bid JOIN C on (A.a1=C.Cid or B.b1=C.Cid)

This again might not go for an index scan on either of the column and might go for a hash join. A better way would be to Use IN... Wondering how???
>>A JOIN B on A.Aid=B.Bid JOIN C on C.Cid IN( B.b1,A.a1)


What say??? I hope this blog would have helped people understand negative/anti condition (I had talked just in brief about this in my ppt in DB2 has talent)... I will try to put up more such content if I come across one... Esp a lot of stuffs can be optimized with CASE statements...

So I hope next time a database developer writes a query, he will think that extra mile and a DBA reviews a SQL he will be more critical...

For those who want more before they can do on their own... Spend a few bucks and get this book on SQL Antipatterns... I havent yet read this but some one suggested it is on the same lines (and the Title of the book suggests the same)...

RUNSTATS and REORG-2: How to automate runstats

Hi guys!!! I am back... n back as an IBM Information Champion (yup got selected for 2011 :) ) !!!
Please rate this blog as informative/interesting/cool/all/two of these once you are done... Any comments on improvement are appreciated and will be welcomed...

Well in last post I talked about importance of runstats and reorg for a db2 database...

just a recap, reorg keeps the data pages near by for same table hence reducing disk seek n runstats will keep the stats updated so helping the optimizer make best use of available indexes and generate better plan...

well... automating runstats is pretty simpler... I will take you little deep into this automation n its impact...
Following are the automatic maintainence parameters in db2...
__________________________________________________________________
Automatic maintenance (AUTO_MAINT)
|
|-- Automatic database backup (AUTO_DB_BACKUP)
|
|-- Automatic table maintenance (AUTO_TBL_MAINT)
|
|---- Automatic runstats (AUTO_RUNSTATS)
|
|-------- Automatic statement statistics (AUTO_STMT_STATS)
|
|---- Automatic statistics profiling (AUTO_STATS_PROF)
|
|--------- Automatic profile updates (AUTO_PROF_UPD)
|
|---- Automatic reorganization (AUTO_REORG)
__________________________________________________________________
So it must be clear that above is an hierarchy... To set ON a child you need to set ON the parent first...
Now lets understand the parameters we are concerned about...

1) First one is AUTO_RUNSTATS
If you set it effective (not just ON... guess what i mean by this???), it will automatically keep doing runstats on tables...
And do not worry, this will not impact your other db2 processes by more that 7-10% (no matter what you have set as UTIL_IMPACT_PRIORITY)... and it will not keep doing this every time you do a CRUD operation... It will do a runstats only on those tables whose data has changed more than 10-15%...good news is db2 luw v9.7 will switch it on automatically by default for any new db created...

2) Next will be automatic statement statistics
If you set this ON, it will try to collect the stats synchronously for any CRUD operation you do... In fact at times it may cause your CRUD operation to wait... It is a real time stats gather...
Well here are a few SNAP DB monitor elements, which might help you decide best about these parameters...
i) STATS_FABRICATIONS Vs ASYNC_RUNSTATS+SYNC_RUNSTATS is STATS usage TO STATS GATHER ratio,
ii) SYNC_RUNSTATS vs SYNC_RUNSTATS+ASYNC_RUNSTATS is FRACTION of SYNC RUNSTAT
iii) STATS_FABRICATE_TIME Vs SYNC_RUNSTATS_TIME is usage stats compared to SYNC update of STATS
iv) SYNC_RUNSTATS_TIME Vs ELAPSED_EXEC_TIME_S & ELAPSED_EXEC_TIME_MS will give you impact of SYNC STAT COLLECTION ON STMT execution
BY default his parameter is ON... Well I had used the monitor elements above and was surprised that 22% of txn were getting delayed due to synchronous stats updates... 30% of stats were being updated synchronously...I leave it to you to decide what to do when you run into this...

3) Third is Automatic statistics profiling and Automatic profile updates
Switching on the automatic stats profiling, will turn on stats profile generation...
but you can not set it on if your db cfg section_actual is ON...
Would like to make a point that generally auto_runstats will respect the user profile if user has run a manual runstats with some profile...
Automatic Profile update will update the runstats profile with recommendations...
If it is off profile recommendations generated are stored in opt_feedback_ranking...
Both of these are off by default...
Will suggest keeping them off to let auto_runstats pick up the default profile... or run a manual runstats with a specific profile for a table n auto_runstats will keep it in mind...

One more way of statistics gathering, is to do a run-stats in idle time, with a background scheduled job... A few months ago, I would have preferred that, when my seniors explained me that...

"db2 does it automatically n won't impact the current processes by much fraction... More over if you automate it urself, you will update stats for all the tables, even if they did not change much (or not at all) so its better we leave it to db2..."

So this is all about automating the runstats...
I will get back on auto maint parameters for automatic reorg and how to identify when to do a reorg...

For people interested in exploring more...
--> Go thru this info center page for more content on auto_maint parameters...
--> Go thru this article on dev works... It explains the automatic maintainence parameters for statistics gathering quit well...

RUNSTATS and REORG-1: How important they are

Hi guys...

So I am back... little quick this time...
So this is final post in my pseudo participation blog for DB2 Night Show (I had an early exit :-( after qualifying for finals)...
Congrats to Norberto for winning the competitions... Check his blog for tips...
Congrats to JB, Momi and Thiru as well for making to final...
I am looking forward to more such opportunities in future...

So I have come with some tips for people (people as the DBA community) to understand how important is the usage of two DB2 commands RUNSTATS and REORG, more importantly how you can determine when to REORG/RUNSTATS on which tables...

1) RUNSTATS are important for your tables, as they keep the stats updated for your table. This will always help your optimizer generate a better plan, rather the apt plan for fetching the data. I have seen situation when a filtered fetch goes for a table scan for 0.1-1million records even though column in filter is indexed...

2) REORG is equally important as it helps in putting the data in continous chunks... Generally for better insert performance, the data is put where you have space rather than putting it in a place (or near it) where other data of the table... The placement of data is also dependent on availability of space in nearest extent/block... If your data is too scattered across, there might be too much of disk seek involved and might as well degrade the performance of sequential and concurrent fetch...

Well you can AUTOMATE your task to certain extent and use automatic maintainance database configuration for auto runstats and auto reorg...

I shall come up with more on pros and cons of using those parameters and ways to optimize your RUNSTATS and REORG, in my future blogs...

I would like to thank Susan and Sathey for their continuous support, encouragement and tips for improvement on by blogging...

I will keep my blogs little short and will split the massive overdose of info over several blogs (thanks to Sathey)...

Latter I will also try to split my previous long posts into several blogs, which shall help my new subscriber...

My db2 success story: Five things to check when you see high memory usage on db server

Hi, so I am back with my blog sequence of db2 has talent... I know I have been little late with this one... But can not help it when India is playing cricket world cup... They finally won it... After 28 long years... First host nation to have won it... So had given up everything else for following the team create history...

by thw way the grand finale concluded recently and the last four finalists are waiting for your votes... There are some really cool tips to adopt in your dba life and that can help you lift your quality of work... so do see the replay and vote before 7th april...

I hope Scott and DBI will surely gonna come up with more of this... Some of you might find it interesting that JB (of the contestant who made it to mega finals) has been invited by Susan Visser to write a book... TO get a glimpse of her check this out, she was Susan's guest blogger...

Well let me continue with my would had been presentation in the episode just before the grand finales... All the contestants talked about their success story, where they concentrated on one single thing with DB2 that made them hero (well this is what I assume from all the presentations)...

So, for me being a junior DBA, there had not been many such moments... One i have already shared in my previous blog... Let me attempt to share another one...

We had a client running db2 8.2.8, and we had planned for migration to db2 9.5... But before the transition could kick-off there was a big performance bottleneck... All of a sudden they were reporting high I/O, high memory consumption and very high swap-in swap-out activity...
Eyebrow were raised and much like any other issue, this too made people point towards database... Our team was consulted, and my seniors being on leave on that day, I was involved in lot of mail exchanges that day... Let me share the information I requested for:

First Step: I requested for snapshot of memory usage by db2 instance and db2 database hosting the production db:
db2mtrk -i -d [-v]

It confirmed that the db was not using too much of memory, it was using whatever was allocated to bufferpool and cache-pool and db shared memory...

Second thing I did was a request to check the tablespaces where the tables are lying and the bufferpool used by all the different tablespaces we had suggested

This confirmed that not all the tables are in the same tablespace, and also all tablespaces are not pointing to the same bufferpool. This helped me overcome the doubt that, may be only one bufferpool is being used for all the I/O, other bufferpools have simply reserved there share of memory but are not using it... Moreover one bufferpool being used for all tabels/tablespaces means too many flush events...

third step of my investigation was to check if there is CIO (oncurrent I/O) enabled at the filesystem level and to check if the filesystem chaching is switched off at tablespace level...

the result confirmed the cause for high I/O and too much of memory usage...
we saw that there was filesystem caching enabled for all the tablespaces (shit!!! the db2 8.2.8 version had this as default, so guys take my advice and migrate to new version, where default is to create tablespaces with filesystem level caching off)... Moreover the CIO was disabled at Unix level as well...
If you have filesystem level caching enabled, what happens is your data is once cached by the OS in memory and then database (i.e. DB2 application) will cache is again in the bufferpool area created by db2 in the application memory allocated to it by kernel... firstly it causes to much of memory to be used, and secondly there is more time wasted in double caching :P...

Correcting this helped alot...

fourth thing to be inspected was prefetch size at db and tablespace level, if this value is not apt there might be too much of I/O waits involved (though this was not in the problem definition, i thought it will be good to check it) the monitor element tablespace_prefetch_size shall help you get that info (i had to use the table-function interface, as I was working with 8.2.8 version, it is much easier to get in v9.7)... Ideally 1.5-3 times your block size is a good number for prefetch data...

fifth thing I confirmed was to check if there is a scope given for block I/O or not... Actually you can reserve some space in your bufferpool for blocked I/O while creating the bufferpool... Blocked I/O is when DB2 performs sequential prefetching of pages into the block area of the buffer pool...
you can check the performance of blocked I/O using block_ios and vertored_ios monitor elements belonging to your bufferpool...
For optimal performance, it is recommended (source db2 info center for v8) that you bind tablespaces with the same extent size to a block-based buffer pool with a block size equal to the extent size...

These all things can help you as well, when you see a shooting high increase in memory usage by db2, or high memory usage on server hosting db2 database or a very high I/O or too much of I/O waits...

querying all these parameters regularly and fixing them (even when, rather esp when you are not facing any issues) will help you keep memory and I/O issues away...

My mantra for db2 DBAs in production is a snapshot a day keeps performance issues aways... i.e. run one snapshot a day and work on the results, fix the problems you anticipate out of their results...

Well I am myself looking forward to DBI's most awaited webinar on DB2 LUW Performance: 10 SQL Snapshots You MUST Run Today!
Register Now!!!

To end it I hope DBI won't mind me pretending to be a pseudo contestant despite being out of the show now... With all due regards I apologize, if in any way I have overlooked any term/conditions/legality of this event which is a mega success... there will be many to follow...