Hi,

I have a stored procedure that returns monthly sum of reading data and seems to be a bit slow. I have created an index on the temp tables but the number of records returned isn't huge so I assume it's just doing a table scan. How can I confirm this? Also, is there a way to force it to use the index no matter what so I can tell if there is any difference in the speed? Not sure what else I can do to make it go faster..Thanks!!

Use db2exfmt if you can extract the sql and execute it from the command line.


Read about optimization profiles.

Guess this is not relevant now, since it's an old post, but for other users, it might help. You could force the index scan by adding the following predicate:

AND COALESCE(INDX_COL, INDX_COL) = INDX_COL

But this won't always guarantee a reduced run-time - ensure you check the plan and execute the statement before finally using this.

It's only one day old



I have a table with only a few rows so db2 will do a table scan for sure, but I can still force it to use the index with the opt profile (if I really want to). By adding coalesce to my simple query, db2 is still doing a table scan (as expected). Can you please explain why adding COALESCE(INDX_COL, INDX_COL) = INDX_COL can force the index scan? It looks like it doesn't do anything.

i'm sorry.. i guess i read the date wrong..

well, i totally agree with you.. but it does depend on the nature of my sql.. using this doesn't or rather wouldn't always result in an indx scan.. and it might not be required at times.. but it does work.. has worked successfully at times with some complex queries.. but not always

here's a sample script.. try it yourself..

create table temp.tab1(c1 int, c2 int, c3 int, c4 int, c5 int);
create index temp.ind1 on temp.tab1(c1);
insert into temp.tab1 with t(val) as (values (1) union all select val + 1 from t where val <100) select val, val, val, val, val from t;
runstats on table temp.tab1 with distribution and indexes all;

select * from temp.tab1; --table scan
select * from temp.tab1 where c1 = coalesce(c1, c1); --table scan
select * from temp.tab1 where 10 in (c1,c2); --table scan
select * from temp.tab1 where (c1 = 10 or c2 = 10); --table scan
select * from temp.tab1 where 10 in (c1,c2) and coalesce(c1, c1) = c1; --index scan

I compared:

#1
select *
from temp.tab1
where (c1 = 10 or c2 = 10)


#2
select *
from temp.tab1
where (c1 = 10 or c2 = 10) and coalesce(c1, c1) = c1


#2 is using an index and the total cost is almost half of #1. Do you know what is it about this coalesce predicate that is making db2 to use the index?

Found the following technote:
IBM - Why the "no-op" predicate COALESCE(X, X) = X can produce a cheaper plan

The result of #1 and #2 may be different, if c1 is nullable.
Because, a row with c1 is null and c2 = 10 will be included in #1 and not included in #2.

Anand.Kaushal created column c1 of the table as nullable.
If adding "or c1 is null" to make the query #2 beeing equivalent to #1,
access path might be changed.

#3
select *
from temp.tab1
where (c1 = 10 or c2 = 10)
and (coalesce(c1, c1) = c1 or c1 is null)

Tonkuma, you're absolutely right.. i'd use this when i'm sure there aren't any nulls in there..

db2girl, that's an excellent find.. thank you.

Hi Anand, can you tell me where you are getting the output that says whether it used a table scan or an index scan? It is from the db2expln command?

Can I use the -stmtfile sql-statement-file option of the db2expln command to check execution of a stored procedure? Or is there another way to do that? Basically I have something like this and want to check how it performs and see if it uses any indexes ( table scan vs index scan) ... thanks!!

db2 "call COLLECT_TOTAL (1, 'Name', 1, 2, '2009-04-01-04.00.00.000000', '2009-05-01-04.00.00.000000', 8, 1, 'SESSION.tmpdata', 0)"

Hmm, seems like I can't use db2expln on global temp tables... basically I want to optimize my temp tables and the index on it so that the sproc returns the results fast..

So after doing some research, I found this link to run explain on stored procedures :


How to create Explain Plan of DB2 Stored Procedures? | db2ude


Has anyone ever tried this? Does it work? Thanks!!