Re: Query Optimizer? Reset

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Troy

So far as I know, there is no way that the new file "remembers" the old 
key field length. This feels like a library list issue.

Is the new table created with SQL at first? When it doesn't seem to work?

Also, is there any kind of caching at the client? I don't see how, but I 
just wonder.

This doesn't make much sense, does it? Whew!!

Vern

On 3/12/2013 1:46 PM, Troy Hyde wrote:
> Sorry in advance for the verbose email.
>
> I received a question from another programmer in my shop.  I'll give a
> little background and then provide his question.
>
> When performing an upgrade from one major release to another, our process is
> to rename the existing data library and then restore the new package version
> of that library.  We then migrate the data from the old library to the new.
> For some tables the data is copied with a CPYF and for some it is copied via
> programmatically.
>
> Here's Matt's question:
>
> I have a file that was created using DDS. A new version was created but a
> key field length was changed from 2 to 4. I have a java program that queries
> that file using the jt400 toolkit. When I run a query against the new file,
> I retrieve the ResultSet metadata to get the field names and lengths.
> However, the metadata returned from the query still gives the old length of
> the key field that was changed from 2 to 4. If I change query slightly, the
> correct metadata is returned. I believe it is the query optimizer that is
> returning the incorrect metadata. How do I reset the query optimizer so that
> the new metadata is returned for the query?
>
>
> Nearly all of our tables are created with DDS.  In this case, the table PEPX
> contains only three columns:
> PETELR   2A in the old library and 4A in the new library
> PEXDYS   5P 0 in both libraries
> PEPASS  16A in both libraries.
>
> The key is PETELR, PEXDYS.
>
>
> So to reiterate:
> In DATALIB we have the table with the two byte key.
> We rename DATALIB to DATALIBOLD.
> RSTLIB DATALIB which has the table with the four byte key.
> CPYF DATALIBOLD/PEPX DATALIB/PEPX MBROPT(*REPLACE) FMTOPT(*MAP)
> When he runs his query, the resultset metadata says the column PETELR is two
> bytes.
>
> If we rename the table, use CRTPF to rebuild it from DDS and CPYF from the
> renamed version to the new version, the problem is resolved.
>
> Any thoughts?
> Cheers, Troy