SQL and Data
Prior to V3R2, nonexposed table names (those that have an associated correlation name in the FROM clause) could be referenced within the SQL statement containing such a name.
In V3R2, this is no longer the case. Any application code that makes such a reference will have to be changed to reference the associated correlation name instead. Otherwise, SQLCODE -201 (SQLSTATE 52003) will be generated.
For example, if both tables in the FROM clause
FROM TABLE1, TABLE2 A
have a column named DESCR, any reference in the query to this column for the second table would have to be written as A.DESCR, not TABLE2.DESCR, because TABLE2 is a nonexposed table name.
Prior to V3R2, a DISTINCT column function was allowed in conjunction with a dyadic operator in the predicate of a HAVING clause. (A dyadic operator is an operator having two operands.) For example, the following would be accepted as valid:
SELECT JOB, AVG(SALARY), AVG(BONUS) FROM EMPLOYEE GROUP BY JOB HAVING AVG(DISTINCT BONUS) + 50 > 100
In V3R2, as part of the product's compliance with SQL-89 in the introduction of unary minus in DISTINCT column functions, this code is no longer allowed. Using it will generate SQLCODE -112 (SQLSTATE 37507).
Prior to V3R2, SOME was not a reserved word in SQL and could therefore be used as an ordinary identifier.
In V3R2, SOME is a reserved word that is used in quantified predicates as a synonym for ANY, so any existing applications that use it as an ordinary identifier will have to be changed before they are preprocessed under V3R2. Id SOME is used as an ordinary identifier, SQLCODE -105 (SQLSTATE 37501) will be generated.
You can address this incompatibility by changing this ordinary identifier to use a nonreserved word, or you can retain the original name by redefining it as a delimited identifier.
Prior to V3R2, applications that compared character data type columns to an unquoted numeric, represented invalid SQL code that was accepted. For example,
WHERE C1 = 3
where C1 was defined as CHAR(1).
In V3R2, this is no longer accepted. Doing this comparison will generate SQLCODE -401 (SQLSTATE 53018).
Prior to V3R2, applications that used a second argument for the CHAR scalar function, when the first argument was a timestamp expression, represented invalid SQL code that was accepted. The second argument was ignored.
In V3R2, this is no longer accepted. Using this argument will generate SQLCODE -171 (SQLSTATE 53015).
Prior to V3R2, applications that used a column function within a HAVING clause with no explicit column name in its argument, represented invalid SQL code that was accepted. For example:
HAVING MIN(1) > 30
In V3R2, this is no longer accepted. Using this function will generate SQLCODE -111 (SQLSTATE 56001).
Prior to V3R2, columns that were specified with even-numbered precision were rounded up to the next odd-numbered precision, when creating or altering a table. For example, DECIMAL(6,2) became DECIMAL(7,2) at CREATE time.
Similar rounding up is also performed for arithmatic expressions found inside statements. For example, the expression 99.9999/12*(12+3) will become 099.9999/12*(12+3) during processing.
In V3R2, this rounding is no longer done. In the above example, any application code that relies on such rounding in order to store seven digits in the column will require a redefinition of the column to DECIMAL(7,2), if the table gets recreated in V3R2. Otherwise, one of the following error conditions (depending on where the mismatch between column and length of the variable occurs) will be generated: SQLCODE -302 (SQLSTATE 22003), SQLCODE -405 (SQLSTATE 53020), or SQLCODE -413 (SQLSTATE 22003).
Arithmatic expression that relies on such rounding to obtain enough precision to accomodate the result of the calculation will need modification. In the above example, the 99.9999 in the expression 99.9999/12*(12+3) must be changed to 099.9999 in order to accomodate the result which is 124.99988. Otherwise, SQLCODE -802 (SQLSTATE 22003) will be generated.
| Date/time Durations: | Date and time durations are specified as DECIMAL(8,0) and DECIMAL(6,0) respectively, but if stored in the database prior to V3R2, they became DECIMAL(9,0) and DECIMAL(7,0) columns. Because of this, V3R2 still accepts the odd-numbered precision for these durations, when they are used as input. |
| Performance of Assembler Programs: | Assembler does not support even-numbered precision. If such table columns are referenced in a predicate containing a comparative host variable in an Assembler program, the latter must be declared with a precision one higher than the column. This leads to inefficient processing. You should consider redefining such table columns to odd-numbered precision to avoid this reduction in performance. |
Prior to V3R2, there was a certain range of floating-point values that went beyond the allowable values for the database and if encountered, would generate SQLCODE -405 (SQLSTATE 53020).
In V3R2, because of a necessary change in the checking algorithm for floating-point constants, the following two narrow ranges have been added to the original range and will now also trigger SQLCODE -405 when encountered:
Prior to V3R2, an arithmetic operation involving decimal columns, such as COL1*COL2/100 or SUM(COL3), allowed a precision of up to 15 digits, unless the SQL query specified something less.
In V3R2, with the enhancement of decimal precision, this allowable precision is now expanded to 31 digits. As a result, it is possible for a query that has been migrated from V3R1 to generate SQLCODE -101 (SQLSTATE 54001) with a value of 'ARIXECK' in the SQLERRP field of the SQLCA. This error indicates that the maximum allowable size (255) of an internal sort key has been exceeded. This can only occur if the query is fairly complex and requires an internal sort.
| Note: | Queries that use internal sorts are typically those that use ORDER BY, UNION, or DISTINCT. |
If you experience this error, you can reduce the precision of the arithmetic operations in the select list of your query by applying the DECIMAL scalar function. This, in turn, may reduce the internal sort key to an acceptable length.
Prior to V3R2, null values in quantified predicates (ALL, ANY) were not handled according to the FIPS standard.
In V3R2, the FIPS standard applies. As a result, the truth value of these predicates is different from previous releases for some cases involving null values.
See Detailed Notes on V3R2-V3R1 Incompatibilities for a discussion on these cases and examples to illustrate the incompatibilities.
Application Programming
Prior to V3R2, the use of negative indicator variables in predicates was limited to the basic equal-to (=) predicate in static, dynamic, and extended dynamic SQL.
In V3R2, the use of negative indicator variables in predicates is extended in some areas and restricted in others. This use is now allowed in all predicates of static SQL and extended dynamic SQL when a descriptor is specified on the PREPARE statement. SQLCODE -309 (SQLSTATE 22512) is generated when a negative indicator variable is used in any predicate within dynamic SQL, or extended dynamic SQL when no descriptor is specified on the PREPARE statement.
Prior to V3R2, existing application programs that used indicator variables declared with a data type other than the equivalent of SMALLINT were accepted.
In V3R2, these programs are no longer accepted. For FORTRAN programs, error message ARI0550E is generated at preprocessing time; for Assembler, C, COBOL, and PL/I programs, SQLCODE -326 (SQLSTATE spaces) is generated at preprocessor time.
Prior to V3R2, incorrect data could get inserted into the database from a variable length host variable that had a length value greater than the maximum that was defined at preprocessing time.
In V3R2, this is prevented. If it is attempted, SQLCODE -311 (SQLSTATE 22501) will be generated.
Prior to V3R2, incorrect string representations of date/time values generated errors at preprocessor time.
In V3R2, warning messages are issued instead; then if the string representations are not corrected, they will result in errors at run time.
Prior to V3R2, if a COBOL program contained a hyphen (-) in the declaration of a host variable name, this hyphen could be represented as an underscore (_) where the name was used within an SQL statement.
In V3R2, the preprocessor no longer accepts this substitution within the program.
If you have any such substitutions in your COBOL source code, they will have to be converted to hyphens before preprocessing under V3R2.
Prior to Version 3, applications containing any of the SQL statements SELECT, SELECT INTO, UPDATE, INSERT, or DELETE, could be preprocessed from a user machine on one release of the product to a database machine on another release of the same version.
In V3R2, there is a change to the validation of host variables for these statements. As a result, this preprocessing fails when the two releases involved are V3R1 and a later release of Version 3. To circumvent this problem, you must preprocess the application from a user machine at the same release level as the database machine on which you would like the package created before compiling, linking, and executing the application from the user machine.
Application Programming (VM Only)
Prior to V3R2, the backslash was not recognized as a continuation character in SQL statements embedded in the C language. For example, the following two-line SQL statement ends its first line with a backslash followed by three blanks, each denoted as :
EXEC SQL INSERT INTO T1 VALUES('abcd\
efg', 2);
In these earlier releases, the first value inserted into the table would be:
'abcd\ efg'
In V3R2, the blackslash is recognized as a continuation character in SQL statements embedded in the C language. As a result, the first value inserted into the table, using the above example, becomes:
'abcdefg'
Prior to V3R2, if an SQL statement was followed by a C statement, C comment, or another SQL statement on the same line, this follow-on information was ignored by the preprocessor.
In V3R2, this follow-on information is processed by the preprocessor, with one exception: if the first SQL statement on the line is an INCLUDE statement (other than INCLUDE SQLCA or INCLUDE SQLDA), warning message ARI5406I is issued and the follow-on information is not processed.
Prior to V3R2, if a host variable was declared just one byte too short to hold the NUL byte, the system did not insert the NUL byte into the host variable and no truncation of the data occurred. SQLWARN1 in SQLCA was set to 'N' in such cases.
In V3R2, the database manager interprets a character string in C that has a length greater than 1 as a NUL-terminated string. It puts a NUL byte at the end of the string, even though this may cause truncation. This applies when the data type from the database is either character or date/time. In the case of the host variable being declared just one byte short of the actual length of the data, SQLWARN1 is no longer set to N. It is treated the same as other truncation cases: SQLWARN1 is set to W, and the last byte of the declared length of the host variable becomes a NUL byte.
Prior to V3R2, statement names and cursor names in SQL statements embedded in C were case-sensitive (that is, a letter coded in lowercase would not be equal to the same letter coded in uppercase).
In V3R2, these names are folded into uppercase.
This will require a change to your application code, if you have used multiple statements or cursor names that differ only by their case sensitivity.
Those cases for which your applications will give different results than they did in earlier releases can be divided into three types, as described below. The accompanying examples are based on these two tables, where the question mark represents a null value:
Table T1: C1 C2 Table T2: C3
-- -- --
? 1 ?
2 2 2
Recalling that a quantified predicate involves the structure
<expression> <quantifier> <subquery>
the three types can be described as follows:
the truth value of the quantified predicate was UNKNOWN.
In V3R2, the truth value is TRUE if the quantifier is ALL, and FALSE if the quantifier is ANY.
In the example below, the second row of T1 is returned by any release of the database manager, but the first row of T1 is only returned by V3R2.
SELECT * FROM T1 WHERE C1 > ALL (SELECT C3 FROM T2 WHERE C3 > 2)
or when
the truth value of the quantified predicate was FALSE, except when the expression was NULL.
In V3R2, the truth value is UNKNOWN.
| Note: | This change will only affect the results of queries in which a NOT has been applied to the quantified predicate in the situations described above. When a NOT is applied, the truth value is TRUE for prior releases, but is UNKNOWN for V3R2. |
In the example below, both rows of T1 are returned by previous releases, but only the first row of T1 is returned by V3R2:
SELECT * FROM T1 WHERE NOT C2 = ALL (SELECT C3 FROM T2)
See the following references for performance implications of queries similar to those shown in the above examples:
the truth value of the quantified predicate was TRUE, except when the expression was NULL.
In V3R2, the truth value is UNKNOWN.
In the example below, the second row of T1 is not returned by any release of the database manager. However, the first row of T1 is returned by previous releases, but not by V3R2.
SELECT * FROM T1 WHERE C2 + 1 = ALL (SELECT C3 FROM T2)
| Comparison of types (b) and (c): | Type (c) is really a subset of the more general case outlined in type (b), by virtue of its extra condition about the expression. However, type (c) is included separately here, because it represents an exception to the more general case. The exception lies in the fact that these two types generated different results for the truth value prior to V3R2. In V3R2, however, this exception disappears, because their results are now the same (truth value = UNKNOWN). |