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