Long running of DB2 Queries

[karpalmera]

Hello! If there’s anyone who can help with this problem…

We created two working environments, creating the same table (TABLEX), same indexes (INDEX1, INDEX2 & INDEX3) and executing the same SQL Statements (SQL1, SQL2 & SQL3) thru spufi. Here is exactly what we did.

For both environments, these SQL statements were created. The ‘where clause’ of the first SQL statement is patterned after INDEX1. The ‘where clause’ of the second SQL statement is patterned after INDEX2. The ‘where clause’ of the third SQL statement is patterned after INDEX3.

Environment 1: We created three indexes (INDEX1, INDEX2 & INDEX3). Then we ran SQL1, this ran for 5 seconds and TMONDB2 displayed INDEX1. Then we ran SQL2, this ran for 5 seconds and TMONDB2 displayed INDEX2. Then we ran SQL3, this also ran for 5 seconds and TMONDB2 displayed INDEX3.

Environment 2: At first, we did not create any index, then we ran the three SQLs. SQL1 ran for 28 seconds, SQL2 for 26 seconds and SQL3 for 30 seconds.

Then we created INDEX3. SQL1 ran for 38 seconds, TMONDB2 displayed INDEX3. SQL2 ran for 28 seconds, TMONDB2 displayed INDEX3. SQL3 ran for 5 seconds, TMONDB2 displayed INDEX3.

Then we created INDEX2. So, we now have INDEX2 & INDEX3. SQL1 ran for 26 seconds, TMONDB2 displayed INDEX2 & INDEX3. SQL2 ran for 28 seconds, TMONDB2 displayed INDEX2 & INDEX3. SQL3 ran for 9 seconds, TMONDB2 ONLY displayed INDEX3.

Then we created INDEX1. Now, we have all indexes. SQL1 ran for 7 seconds, TMONDB2 displayed INDEX1. SQL2 ran for 58 seconds, TMONDB2 displayed INDEX1. SQL3 ran for 41 seconds, TMONDB2 displayed INDEX1.

The following are the SQL statements:
1. SELECT *
FROM TABLEX
WHERE BANKCD = 12345
AND MONTHENDDT = 20070131
AND BATCHSYS = ‘SYS’
AND ACCTCD = 123

2. SELECT *
FROM TABLEX
WHERE BANKCD = 12345
AND BATCHSYS = ‘SYS’
AND CLIENT = 12345
AND ACCTCD = 123
AND MONTHENDDT = 20070131

3. SELECT *
FROM TABLEX
WHERE MONTHENDDT = 20070131
AND UNIT = 5423
AND ACCTOFF = 111112
AND BATCHSYS = ‘SYS’

Why is the runtime of environment 2 different from environment 1 when all the indexes are created? Why is the display of TMONDB2 different from environment 1 and environment 2 after creating the three indexes? What could be the other factors that may affect this difference?

[Ben Neffendorf]

Not sure, but at a guess I'd say Environment 2 is attempting to cluster by index 3, where-as env 1 would be clustering by index 1 (assuming you created them in 1-2-3 order). At least I think that's how db2 works - unless you specify one of the indexes as clustering, it will pick the 1st one created. Even if runstats aren't collected, I guess that would alter the access paths.

[Snib]

Ben is correct in what he has stated. If you check the the following URL:

Index Design for Performance

Under the section titled "Recommendations" it confirms that DB2 will select the first index added to the table as the clustering index if that is the only index being added at that time.

What is not clear from your statements is if/when you ran a RE-ORG, when you loaded the tables and whether you ran RUNSTATs.

If you are creating a new table which you are going to load rows onto after it is created (i.e. you are not starting off with an empty table and then adding rows via an application as they are created), then one option is as follows:

1/ Define the table structure
2/ Define the clustering index
3/ Load the data (use the DB2 load facility as this will ensure the rows are sorted in the clustering index order as they are stored on the data portion of the table)
4/ Define second index
5/ Define third index
6/ Execute RUN-STATs
7/ If the SQL is being executed from a program and not as dynamic SQL then execute a REBIND to allow DB2 to re-analyse the most efficient access paths for the SQL.

If you load the data onto a table before creating the primary index then it will be loaded in the order of the input file used. Even when you create the clusting index if you do not RE-ORG the table the data stays in this order on the data portion of the table. If you do not execute RUNSTATs then the details used during the REBIND/BIND will not be up-to-date and may again impact performance.

There are a number of fields that affect the access paths for "static" SQL - I susgest you runs some searches on the Internet to find the list of the latest ones for your version of DB2.

One thing to remember - if you do not RE-ORG and RUNSTAT then access paths may not be as you expected. If you do not specify the clustering index for a TABLE then access paths may not be as you expected.

I suspect that if you dropped the indexes from Environment 2, create them again but define Index 1 as clustering, execute a RE-ORG, execute RUNSTATS, BIND the SQL if it is static and in a program and then run the SQL again you will finds things are bettter.

If they are still not the same as Env 1 then I would suggest you repeat the same process against Env 1 to ensure the indexes, runstats and access paths are as up-to-date as possible.

Regards

Snib

[karpalmera]

thank you so much, snib and ben...!!!!
i'l update you, guys...thank you!!!

[karpalmera]

hello...i just found out that both environments were clustered with INDEX1.
:)

[Snib]

OK, so does that mean you still have a problem or is everything no working ok?

If you still have a problem it would help if you could run an EXPLAIN for each piece of SQL against each environment and then post the details from the PLANTABLE. We can then review exactly what order the SQL is being executed in and the indexes DB2 has selected.

One other thing, before you run the EXPLAIN ensure you have executed RUNSTATS and is the SQL is in a program then execute a REBIND. If you do a REBIND you can request this to produce the EXPLAIN output at the same time.

Regards

Snib

[karpalmera]

Yes! it worked for both development and production environments..

Here is what we did....
in our development environment:

1. we dropped all indexes and tables
2. created the table and defined the primary index with clustering
3. restored all data
4. created other indexes
5. RUNSTATS
6. rebind programs
7. run programs

in our production environment:

1. we dropped all indexes
2. defined the primary index with clustering
3. REORG
4. created other indexes
5. RUNSTATS
6. run programs

thanks a lot!