All-

I have faced an interview yesterday .i was not able to answer few questions. Its db2 V9 interview ..

1) Your app team complains a slow performance of application. What do you do as a DBA. Tell me the sequence of steps that you perform.

2) I want to load millions of rows into the database ..how do i load them.give me the best ways to load them.

3) I want to delete a huge table which has millions of rows ...How do you do it quickly...?

4) In a partition environment how do you choose which ones are good candidates for the partition key ?
He means before creating tables how do you decide which ones are the best.

I was not able to answer them correctly...
can the experts tell me the correct answers for the above questions.

Some of the answers depend on the platform on which DB2 is used.
Are these questions about DB2 for z/OS? or iSeries? or Unix? or ...?

1) Few points we can check for performance
1) check whether indexes are being used or not using query access plans
2) collect statistics regularly/more frequently(RUNSTATS)
3) perform REORGCHK and see if reorganization of tables/indexes is required.
4) REBINDING SQL statements if required
5) Use tools like design advisor/configuration assistant

2) LOAD utility
4) partitioning key, generally we decide based on data which we want to partition. For eg; If I want to store my quarterly data in seperate partitions and if my queries are based on ranges (like monthly/weekly reports), then I chose date as my partition key.

1) Few points we can check for performance
1) check whether indexes are being used or not using query access plans
2) collect statistics regularly/more frequently(RUNSTATS)
3) perform REORGCHK and see if reorganization of tables/indexes is required.
4) REBINDING SQL statements if required
5) Use tools like design advisor/configuration assistant

2) LOAD utility
4) partitioning key, generally we decide based on data which we want to partition. For eg; If I want to store my quarterly data in seperate partitions and if my queries are based on ranges (like monthly/weekly reports), then I chose date as my partition key.

Would be nice to hear what you have to say first? What is your experience?

3) use REORG with DISCARD (on z/OS)
or
use LOAD REPLACE with empty input data (on LUW)

Other options for 3 :

-- IMPORT FROM empty input data
-- ALTER TABLE NOT LOGGED INITIALLY WITH EMPTY TABLE

Thanks for the reply.

This is for LUW environment.

For the slow performance i have answered in the following way.

1) I will check the OS level by using vmstat , top , sar command and try to see if any process is taking too much of CPU utilitization and see if disk I/O is happening.

2) Then i will see if applications are getting locked , i mean if the applications are in Lock-Wait state because one application holding an exlusive lock on a table.

i have seen No.2 for different tables at the same time.

3) Will the check for runstats , if they are current .
4) Do reorg chck and find out if Reorg is needed.
5) Will take a snapshot of dynamic sql and get the long rnning sqls to tune them

6) and also since overall system is effecting the peformance , i will involve network team and OS team in the analysis .

For slow performance I would personally check if the SQL is written correctly first. many times I have seen lack of joins.

Before doing any reorg or stats I would run an explain on the SQL and see what it is doing? Is it using correct indexes, is it using indexes at all and why not.

You might have locks, but if they are not effecting this particular SQL I would not worry about them this minute. Locks are a natural thing that have to happen sometimes.

Cougar ..you are right...the questions is the whole application is responding slow ..not a particular piece of it . ..In that case how do we decided a particular sql is performing slower.