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 ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: Date fields

  1. 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


  2. 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:

    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
    >




  3. 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:

    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




  4. 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.






  5. 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

    >
    >




  6. 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.


+ Reply to Thread