.
Your SQLs as written for V5R4 are probably not compatible with V7R1 (or
V6R1).
We started getting message SQL0338 after upgrade to V7R1.
Here is IBM's fix which allows your old,"incorrectly-written",
deprecated SQLs to continue running.
** Data area Support **
With this data area in place, users can bypass the SQL0338 but have the
possibility of getting incorrect output. The change was needed to
ensure files were being joined correctly and the correct data was
returned. Clients that use the data area take full responsibility and
understand that incorrect output may occur. The data area is not a
permanent solution. Clients need to schedule and make appropriate
changes to their SQL joins to adhere to the changes made at R610 and
remove the data area. Support of the data area may be limited and can
be removed at a later time.
Using the data area is a method to return to V5R4 and earlier releases.
This is an EXTREME measure.
The data area name is QSQJOIN. Upon the first use of a query which
includes a join within a job, we attempt to resolve to QSQJOIN using the
library list. If the data area is found, we will avoid failing with any
of the new IBM i 6.1 SQL0338 join enforcement errors. The decision made
during the first join applies for the life of the job. The data area
support was provided under 5761SS1 PTF SI34508
CRTDTAARA DTAARA(QSQJOIN)
TYPE(*CHAR)
LEN(1)
The data area needs to be put in a library that will be found.
Note: If support for the data area is pulled it will be at a release
boundary and will be put into the MTU.
++++++++++++++
In IBM I 6.1 the query code on the i System was enhanced to adhere to
the latest SQL specifications for implementing joins, this information
was provided in the IBM I 6.1 Version of the Memo to Users document
(
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzaq9/rza
q9.pdf) on page 24 under the heading of Resolving a SQL0338 failure.
Prior to that time joins could be coded in such a manner that their
implementation was ambiguous and IBM made a "best guess" as to what the
customer desired. If SQL made the wrong choice the customer could
receive incorrect data. These failures can surface when a program
containing the join is compiled or when the query is actually executed.
The most common form of this was a mixture of implicit and explicit join
specifications within the same query. This additional checking was added
in the base code; however, after some testing and customer input it was
discovered that not all instances that should be flagged with the
SQL0338 error message were so with the introduction of PTF SI34507 and
SI34508 some customer queries that were functional on IBM I 6.1 will now
fail with the error message. Here is a good, basic explanation of what
the change was implemented to accomplish and why a PTF was required:
Restrictions were added in IBM i 6.1 to issue the sql0338 when columns
in the join clause existed in the table left of the comma. This should
be an error.
Example:
Operating system code has been changed to issue the sql0338 for the
queries such as the following, where a1 is in table t1.
select * from t1, t2 inner join t3 on a1 = c1
midrange.com
