DB2 v7.2 on z/OS (embedded SQL in Cobol)

I have a table with 8 date columns and user can base their search criteria on any of the date columns, So now I have 3 ways of doing it:

1) Write 9 different static sqls and run the appropriate one
Select c1, c2 from t1 where date1 = :ws-date1
Select c1, c2 from t1 where date2 = :ws-date2 and so on......

2) write one big static SQL and populate the required host variables while populating the rest of the variables with low and high values (in the sense all '0' and all '9's)
select c1, c2 from t1 where
date1 between :ws-date1L and :ws-date1H and
date2 between :ws-date2L and :ws-date2H.......
if the user specifies date ranges for date2, I populate ws-date2L and ws-date2H and move zeroes to ws-date1L and all 9's to ws-date1H (performance drag I guess!)

3) Dynamic SQL

The table is brand new and has only a few rows, so EXPLAIN/PLAN_TABLE/ DSN_STATEMENT table suggestions may not be good. I would appreciate if anyone could throw some light on which one would be the most efficient (based on query return time and optimal machine performance) way to do it.
Thanks in advance

It depends on a few things:

1. Are there any other predicates in the where clause besides the date(s)?

2. Are the data columns indexed (each with its own index)?

3. What is the normal or average date range in each search expressed at a percentage of the data in the table?

Option 1 is definitely more efficient, but depending on the answers to these questions, it may, or may not, be significantly more efficient.

1. Are there any other predicates in the where clause besides the date(s)? No

2. Are the data columns indexed (each with its own index)? No

3. What is the normal or average date range in each search expressed at a percentage of the data in the table? Not sure about it, Business analyst may know more....

I am weighing between 1 and 3 except for the fact Dynamic SQL does too much stuff at runtime, but with option 1, I got 9 separate cursors to open, fetch and close!!! and it doesn't look cool :-(

If there are no indexes on the dates, then you will be doing a tablespace scan (reading all the rows to determine which ones met the search criteria). In that situation, option 2 will not be much slower than option 1, but it will be slower.

But maybe you should consider the indexes if the table is large enough?

tablespace scan seems to be a given in this case. with option 2, DB2 will have to take every 8 columns and compare it against 16 host variables for every row (even though only 2 of those host variables will valid values at anytime), so I believe it will be a lot slower

This is kind of a "test the waters" project and at the most I expect a few thousand rows in the first year - so I was thinking about worrying about indexes when I get a feel of the data distribution/patterns.

it's been a couple of years since I did Dynamic SQL and I am itching for it too :-) even though this query is more boring than challenging!

Sort of option 2 with a couple of extra things
1) use
(datex = :hvdate or :hv-indx = 1)
and
(datey = :hvdate or :hv-indy = 1)
and ...
Set all the :hv-inds to 1, except the one for the date column you are searching against. Make that one zero - or anything but 1.
2) put an index onto each date column. If most of the date values will be in the past, consider making the sequence DESC.
3) bind with REOPT(VARS)


James Campbell

Thank you James & Marcus, I will try that