Re: SQL Table Name vs System Table Name

Well, Chuck, that's pretty much the path that we've declared to do. Good
ole fashion 10 alpha names.

Vern, no to be rude and not answer, we are V6R1, PTF level 17 for DB2.

thanks guys
David



David L. Mosley, Jr.
Technical Solutions Architect | 919.379.3730
Dancik International | www.dancik.com
www.facebook.com/dancikinternational



CRPence <CRPbottle@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
09/12/2011 12:29 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
rpg400-l@xxxxxxxxxxxx
cc

Subject
Re: SQL Table Name vs System Table Name






On 12-Sep-2011 08:24 , dmosley@xxxxxxxxxx wrote:

Just wanting to ask if anyone has experienced this problem before.

We have several QTEMP files being created in programs, via SQL
CREATE TABLE. All the files exceed 10 characters, and most begin
with the same prefix. (OrderEntry_Header, OrderEntry_Details,
OrderEntry_Messages, etc).

So, to make a long story short, we experienced the program trying to
access the wrong QTEMP file. Let's say I was trying to do a SELECT
COUNT(*) FROM OrderEntry_Header (that would contain 1 record). Well,
instead of getting a value of 1, we got a value of 13, which was the
contents of OrderEntry_Details.

Now, this was a very sporadic occurrence, but we applied some logging
in the programs, and could visually see the wrong values be
accessed.

So, in an attempt to correct this from happening, we converted the
long SQL names to normal 10< characters, OE@HEADER, etc. And, the
problem stopped occurring.

I know the system, creates truncated system names, when the CREATE
TABLE command is executed, (ie ORDE000001 -> OrderEntry_Header), but
is it possible that the pointers for those files got tangled
somehow.

Trust me, I know the iSeries is perfect (haha), so, I'm gonna guess
gremlins were the culprits

For database files outside of QTEMP the system database
cross-reference tracks long file names for the OS and the SQL
[catalogs]. In order to support long file names in QTEMP however, the
SQL implements what might be called a name-correlation-table in some
persistent storage for the SQL activity in the job.

I have never experienced any problems for any of the very rare cases
that I have ever used long names in QTEMP. To be clear, I actually
explicitly avoided them, specifically because the implementation is
distinct from the normal code path. Based on your comments, I performed
the following web search and found at least the APAR SE47391 with UNPRED
kwd suggesting a possibility of unpredictable results:
http://www.google.com/search?q=apar+qtemp+
"long+names"+OR+"long+file+names"+OR+"long+table+names"

Regards, Chuck