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