MVSFORUMS.com

[20cents]

Hi,

Good day. I don't mean to be rude, but let me get straight to the point.

I have a table which has 4 indexes - ACCTNO, YEAR, MONTH, and TIMEADD

ACCTNO - account number
YEAR - format CCYY
MONTH - format MM
TIMEADD - timestamp

I would like to make use of all 4 indexes to make my processing faster, since the table has billions of records in it, and I may retrieve thousands for a particular account.

here's the problem. I need to get 10 months worth of data for the account. For example, this month 04/2006, for a particular account, I need to get from 06/2005. How do I declare the cursor such that I would get only from 06/2005 to 04/2006?

The only way I could think of is putting the year and month in an array, but then I need to declare-fetch-close 10 times to retrieve the data I need.

Such that it would look like this:

Loop n from 1 to 10:

1. declare cursor where MONTH=:array_month[n] and YEAR=:array_year[n] and ACCTNO=:acctnumber and TIMEADD < current timestamp
2. fetch
3. close cursor

Is there some way I could avoid this?

I hope this explanation is clear enough by you, my masters.

[20cents]

Forgot to include what the array contains:

array_month[1] = 06; array_year[1] = 2005
array_month[2] = 07; array_year[2] = 2005
array_month[3] = 08; array_year[3] = 2005
array_month[4] = 09; array_year[4] = 2005
array_month[5] = 10; array_year[5] = 2005
array_month[6] = 11; array_year[6] = 2005
array_month[7] = 12; array_year[7] = 2005
array_month[8] = 01; array_year[8] = 2006
array_month[9] = 02; array_year[9] = 2006
array_month[10] = 03; array_year[10] = 2006

[kolusu]

20cents,

You don't need an array or any thing. You can set up the host-variables to have the date range and use them in the cursor declaration

[20cents]

thanks, my master.

[20cents]

[kolusu]

20cents,

How are year and month col defined? Another option is to add the timestamp into the sql