Column Name ColNo Datatyp Length
----1----v----2----v----3----v----4----v-
RQST_ID 1 CHAR 18
ACCT_ID 2 CHAR 18
FLAG_ATR 3 CHAR 1
TIME_STAMP 4 TIMESTAMP 26
TRANS_TXT 5 CHAR 80

and a batch process using the below cursor as its driver.
........
........
EXEC-SQL
DECLARE MAIN-CURSOR FOR
SELECT
RQST_ID,
ACCT_ID,
FLAG_ATR,
TIME_STAMP,
TRANS_TXT
FROM AUDIT_TABLE
WHERE
FLAG_ATR = 'A'
ORDER BY ACCT_ID, TIME_STAMP
FOR FETCH ONLY
END-EXEC
The Audit_Table has a total of 12 million rows and 10 million of them have a value of 'A' for FLAG_ATR field. The only available index on the table is on RQST_ID.

Could u plz suggest to optimize the query ?

Attention please : This will retrieve 10 million records out of 12 ..Is there any other way to optimize without creating index on FLAG_ATR coloumn

Hi,
always try to put indexes on 'where' conditions. So putting index on flag_atr could help.

You can also try to and index on all columns in select list: first on where condition and all other followed. This is all column in index approach. To optimize the SQL you need to add more where conditions to the query or using fetch first n rows only. If you are using static SQL you also need to rebind the packages. Have you executed statistics and reorganization on table?

By the way what are you doing with 2 millions of records? Are you using some kind of report or some think like that. If so try using "fetch first n rows only" - use number of rows instead of n. Try explaining what are you doing, because this is little strange to use 2 millions or rows in query. Please also provide db2 version and operating system and version you are using.

Hope this helps,
Grofaty

You could try to put an index on FLAG_ATR, but if 2 million (out of 12 million) of the rows have 'A', then it probably will not help if the 'A' rows are randomly distributed throughout the table.

Marcus_A, I agree. Using such an index on randomly distributed data could retrieve all pages from table. DB2 could also decide no index is used. But creating index I would try the first...

Plz see the changes ...We have 8 million records with flag_attr='A'..Also we
don't want to create index .. Any possible to optimize ?

If 8 million records have flag_attr = 'A', then an index on that column will do no good. If you can avoid the order by, then that would make it run faster.

Most of the time is taken for fetching the randomly distributed data. Since there is no index it will go for tablespace scan. Removing Order by would save little bit and not much.
You cannot optimise the query without changing the conditions on where predicate. Thats where you control the resources. Somehow try to bring RQST_ID in the where predicate if possible.

I do see that this is a static SQL used in COBOL program. So the time taken is while opening the cursor and it is uncommited read. Once it is opened it should not be an issue.but not sure what is the recordlength and how much buffer area it would take.

first of all, I would remove the column FLAG_ATR from the SELECT-list.

Due to the WHERE-condition, that value is always 'A'. there is no need to retrieve ( and SORT !! ) this data.

sramesh, can you please provide the reason you need to retrieve 8 million rows. What kind of application is this? Is it data warehouse application, OLTP application...

Can you please provide execution time and what is desired execution time you are looking for?

You could run the query throught the DB2 Design Advisor to get some further suggestions.

stolze, if I remember correctly Design Advisor (from Control Center) will not advise how to rewrite SQL. But according to the simplicity of SQL, I can't see any rewrite option beside umayer suggestion. So Design Advisor will suggest some indexes.

Hope this helps,
Grofaty

Indexes and MDCs are suggested by the Advisor (and probably a few other things).