Re: Query Optimizer? Reset

I have thought all of the same things as well.

There are no locks on the table in either library.

Because the following is true I don't think it's a library list issue:
The table is keyed uniquely, and if his application does an insert, the data appears (viewed through dsppfm) in the new table.
His application then queries the table and sees the data he just inserted. But the first two bytes of the four byte column populate the first field in his application and the next two bytes move to the first two positions in the next column. The meta data still reports that the column is a 2 byte field.

We can fix the problem in one of two ways:
1. Rename the table, run his application and it will get an error that the table doesn't exist. Rename it back and everything works properly.
or
2. Rename the table, use CRTPF to re-create the table and copy the data from the renamed one to the new one.

I agree that it appears to be caching of some sort, but I'm thinking caching done on the System i server, not on the client. I've loaded his application on various clients and they all have the problem, even those that have never connected to the server before. And once it is fixed for one, it is fixed for all.

For whatever reason the CRTPF and the query running against a non-existent table does whatever is necessary to reset something. Also, once it is fixed, we cannot get it to fail again.

Our clients' servers are running V5R3, V5R4 and V6R1. It fails only on about 30% of our upgrades and doesn't seem to be release dependent.


-----Original Message----- From: Charles Wilt ; Charles Wilt
Sent: Wednesday, March 13, 2013 7:58 AM
To: Midrange Systems Technical Discussion
Subject: 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.