Date fields - IBM AS400

This is a discussion on Date fields - IBM AS400 ; Hello,
We have a legacy file that we're accessing via SQL. The legacy file
has separate fields for the date components (e.g, CC,YY, MM, DD) and
I'd like to be able to run queries against it asking for rows between
...

Date fields

Hello,

We have a legacy file that we're accessing via SQL. The legacy file
has separate fields for the date components (e.g, CC,YY, MM, DD) and
I'd like to be able to run queries against it asking for rows between
2 dates. Are there any tricks w/ logical files and/or indexes to keep
the SQL simple and provide fast access?

Thanks,
Jim

Re: Date fields

If your dates span two centuries, then you need to construct a 10-character
string that looks like '2006-12-31' using concatenation and whatever other
functtion are necessary, and use this in the date function to turn it into a
date. At which point you can do comparisons.

If the dates are all in one century, then you can just concatenate or
multiply until you get a number representing a complete date.

Either way, the SQL is not all that clean and queries are unlikely to
utilize existing indexes.

If you are going to do a lot of queries based on date, you should try to
create a view the includes the constructed date field, but you can't, as far
as I know, create an index on a view.

If you have 4, 2-char fields of cc, yy, mm, and dd, something like this:

"jimo" wrote in message
news:1193077095.645794.195080@k35g2000prh.googlegr oups.com...
> Hello,
>
> We have a legacy file that we're accessing via SQL. The legacy file
> has separate fields for the date components (e.g, CC,YY, MM, DD) and
> I'd like to be able to run queries against it asking for rows between
> 2 dates. Are there any tricks w/ logical files and/or indexes to keep
> the SQL simple and provide fast access?
>
> Thanks,
> Jim
>

Re: Date fields

Thanks for the reply, Sam.

I'm able to convert the fields to a date that can be used in my
queries but the SQL gets a little messy if I want to use an index that
is built over the individual date fields. So I was thinking that I
might be able to create an index over the concatenated fields.

I tried the to create the following logical file using a concatenated
key:

However, when I query against it using, say, select XDATE from PFILE
where XDATE > 20071010 the CQE optimizer uses a table scan instead of
the logical file because of (CPI432E), "... the data type, digits,
decimal length ... of ODATE..." doesn't match.

I looked at the definition for the field and it's Numeric,8,0, so I
also tried

select XDATE from PFILE where XDATE > CAST(20071010 as NUMERIC(8,0))

Same optimizer message. Any ideas?

Thanks,
Jim

On Oct 22, 8:44 pm, "Saml" wrote:
> If your dates span two centuries, then you need to construct a 10-character
> string that looks like '2006-12-31' using concatenation and whatever other
> functtion are necessary, and use this in the date function to turn it into a
> date. At which point you can do comparisons.
>
> If the dates are all in one century, then you can just concatenate or
> multiply until you get a number representing a complete date.
>
> Either way, the SQL is not all that clean and queries are unlikely to
> utilize existing indexes.
>
> If you are going to do a lot of queries based on date, you should try to
> create a view the includes the constructed date field, but you can't, as far
> as I know, create an index on a view.
>
> If you have 4, 2-char fields of cc, yy, mm, and dd, something like this:
>
> create view mytable as
> select date(cc || yy|| '-' || mm || '-' || dd) as mydate, a.*
> from sometable a
>
> Sam
>
> "jimo" wrote in message
>
> news:1193077095.645794.195080@k35g2000prh.googlegr oups.com...
>
> > Hello,
>
> > We have a legacy file that we're accessing via SQL. The legacy file
> > has separate fields for the date components (e.g, CC,YY, MM, DD) and
> > I'd like to be able to run queries against it asking for rows between
> > 2 dates. Are there any tricks w/ logical files and/or indexes to keep
> > the SQL simple and provide fast access?
>
> > Thanks,
> > Jim

Re: Date fields

>>> However, when I query against it using, say, select XDATE from PFILE
where XDATE > 20071010 the CQE optimizer uses a table scan instead of
the logical file because of (CPI432E), "... the data type, digits,
decimal length ... of ODATE..." doesn't match. <<<<

You will always take a big performance hit if you write SQL over a DDS
logical file. This automatically kicks the query into CQE, and as
you see, it often can't use the logical anyway. You would be better
to create a SQL view and try running across that. Use Visual Explain
to see what indexes are suggested.

Another option, depending on how this file is used, might be to create
a MQT. This table is built like a SQL view, but can have indexes
built over it. Downside is the data is stagnant - it has to be
refreshed to get updated data. For a history file this works well,
or for a file that is updated overnight. Upside is that once the
refresh is done, its very very fast.

Re: Date fields

If you are on V5R3 you can installed the new SQE via PTF. I have found the
SQE to be a much better optimizer for the queries I tend to run. It is
inclined to give up when it finds select/omits and the odd things you can do
in DDS, but you can force it's use by using the IGNORE_DERIVED_INDEX option
in the QAQQINI file. You might want to try using that.

It would be quite impressive if SQE is smart enough to know to use indexes
over the fields that made up your XDATE field, but think it will just ignore
the logical. It might be clever enough to look at indexes over the
individual fields.

I don't know why you are getting the CPI432E, but it mentions field ODATE,
not XDATE.

Remember, too, that a table scan isn't always a bad thing. There is a
threshold on the number of row you are going to select where it results in
fewer IOs to do a table scan.

Sam

"jimo" wrote in message
news:1193140990.456120.199400@z24g2000prh.googlegr oups.com...
> Thanks for the reply, Sam.
>
> I'm able to convert the fields to a date that can be used in my
> queries but the SQL gets a little messy if I want to use an index that
> is built over the individual date fields. So I was thinking that I
> might be able to create an index over the concatenated fields.
>
> I tried the to create the following logical file using a concatenated
> key:
>
> A R PFILEF1 PFILE(*LIBL/
> PFILE)
> A XDATE CONCAT(CC YY MM DD)
> A K XDATE DESCEND
> A
>
> However, when I query against it using, say, select XDATE from PFILE
> where XDATE > 20071010 the CQE optimizer uses a table scan instead of
> the logical file because of (CPI432E), "... the data type, digits,
> decimal length ... of ODATE..." doesn't match.
>
> I looked at the definition for the field and it's Numeric,8,0, so I
> also tried
>
> select XDATE from PFILE where XDATE > CAST(20071010 as NUMERIC(8,0))
>
> Same optimizer message. Any ideas?
>
> Thanks,
> Jim
>
> On Oct 22, 8:44 pm, "Saml" wrote:
>> If your dates span two centuries, then you need to construct a
>> 10-character
>> string that looks like '2006-12-31' using concatenation and whatever
>> other
>> functtion are necessary, and use this in the date function to turn it
>> into a
>> date. At which point you can do comparisons.
>>
>> If the dates are all in one century, then you can just concatenate or
>> multiply until you get a number representing a complete date.
>>
>> Either way, the SQL is not all that clean and queries are unlikely to
>> utilize existing indexes.
>>
>> If you are going to do a lot of queries based on date, you should try to
>> create a view the includes the constructed date field, but you can't, as
>> far
>> as I know, create an index on a view.
>>
>> If you have 4, 2-char fields of cc, yy, mm, and dd, something like this:
>>
>> create view mytable as
>> select date(cc || yy|| '-' || mm || '-' || dd) as mydate, a.*
>> from sometable a
>>
>> Sam
>>
>> "jimo" wrote in message
>>
>> news:1193077095.645794.195080@k35g2000prh.googlegr oups.com...
>>
>> > Hello,
>>
>> > We have a legacy file that we're accessing via SQL. The legacy file
>> > has separate fields for the date components (e.g, CC,YY, MM, DD) and
>> > I'd like to be able to run queries against it asking for rows between
>> > 2 dates. Are there any tricks w/ logical files and/or indexes to keep
>> > the SQL simple and provide fast access?
>>
>> > Thanks,
>> > Jim
>
>

Re: Date fields

To expand on Saml comments: If you query a LF, the system will not
use the (newer and faster) SQE engine, no matter what. If you query
a PF, and there is even 1 LF that has include/omit specs, it will
start the SQE, then switch back to CQE (automatic 15% penalty here).
You can override this penalty by using the "IGNORE_DERIVED_INDEX"
option as suggested. I would think the "created" field in the LF
would count as derived, but I'm not sure on that. If so, you just
forced EVERY query on that PF to use the old CQE engine if you don't
use the IGNORE option.

BTW: Table scans are VERY fast, and will usually be used if the
optimizer expects around 50% (maybe less) of the file to be read. If
might actually use the index to decide that a table scan will be
faster.