Re: Query Optimizer? Reset

I'm with Vern...something is being cached or something :)

I'm wondering if perhaps the SQE has an open cursor or otherwise isn't
re-resolving the object by name.

I'd suggest doing WRKOBJLCK on the old object prior to renaming it and
making sure nothing is there...

Charles


On Wed, Mar 13, 2013 at 9:51 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.