SQL and Data
Prior to V2R2, leading and trailing zeros of decimal constants were removed by the system when calculating their scale and precision.
In V2R2, if the precision of a decimal constant is greater than 15, leading zeros are removed to bring the precision down to 15. Trailing zeros are not removed.
If your current applications provide output from the result table without any intervening formatting, this change has the potential of altering that output. If formatting is involved, you may have to change the formatting logic to obtain the same output.
Similarly, input to the database by means of INSERT or UPDATE may be affected, if a decimal constant is involved.
Prior to V2R2, two select-lists could be successfully UNION'ed even when they contained corresponding items that were host variables of different data types and different lengths. The statement below is an example of this, where host variables :hw and :fw are halfword fixed binary (15) and fullword fixed binary (31), respectively.
SELECT :hw FROM T1 UNION SELECT :fw FROM T1
In V2R2, the above statement is no longer allowed. Issuing it will generate SQLCODE -415.
| Note: | In V3R1, some restrictions on the use of data types within a UNION are removed, including the above incompatibility. |
Application Programming
Prior to V2R2, many types of operational errors (that is, SQL statement errors) against the database caused the system to roll back the entire current logical unit of work (LUW), leaving the application with no control over the status of the LUW.
In V2R2, all operations against the database are now atomic. That is, within an LUW, each operation can succeed or fail separately, with no effect on other operations, provided they do not depend on it. If an operation fails, the application is free to either continue working on the same LUW, or commit the changes made so far, or roll back the LUW. Some system errors, such as deadlocks, still require the entire LUW to be rolled back by the system. Also, atomic operation is not supported for:
As a result of this change, you may want to extend the logic of your LUW processing in your applications.
| Note: | The next incompatibility item contains a special case of atomic operation. |
Prior to V2R2, if an error occurred during a single operation involving multiple row changes to the database, the database was potentially left in an inconsistent state. (This was one of those operational errors that was not rolled back by the system.) Some of the rows were processed; the rest were not. The only practical way to avoid this inconsistency was to have the application roll back the entire current LUW.
There was one exception to this: in the case of a data definition statement, such as CREATE TABLE, the system itself rolled back the LUW to avoid a partial definition of a table in the catalog. The application had no control over the status of the LUW.
In V2R2, with atomic operation in place, the system automatically undoes that portion of the multiple row operation that was processed prior to the error. This eliminates the potential of an inconsistent database resulting from such an operation, and leaves the application free to control the current LUW as it sees fit.
See Detailed Notes on V2R2-V2R1 Incompatibilities for an example.
Prior to V2R2, all floating-point data had to be eight bytes.
In V2R2, it can be four bytes.
This leads to a potential problem in V2R2 for programs that allocate eight bytes when using DESCRIBE on a FLOAT column. When using DESCRIBE, applications should allocate storage based on the SQLLEN of a column (as given in the SQLDA), not the SQLTYPE.
Prior to V2R2, an arithmetic or conversion error terminated processing of the statement and generated SQLCODE -802.
In V2R2, these types of errors are tolerated when they involve a host variable that has an indicator variable. In such cases, processing of the SQL statement continues; SQLCODE +802 is generated; a -2 is placed in the indicator variable; and the associated database variable remains unchanged.
If your application is checking for these errors, this could impact its logic. The types of errors that can now be tolerated are:
For more detail, see the Messages and Codes manual, V2R2 or later, for SQLCODEs +802 and -802.
Prior to V2R2, "WITH GRANT OPTION" in a GRANT statement passed GRANT authority to the user receiving the privilege in question, even when the user was PUBLIC.
In V2R2, when "PUBLIC" and "WITH GRANT OPTION" are used together, the privilege is granted to PUBLIC, but without GRANT authority. In such cases, a warning is given to that effect.
This can impact your current authorization of views or programs, since these objects, which previously could have been grantable (for example, a value of 'G' recorded for a program in catalog table SYSPROGAUTH), will no longer be so (a value of 'Y' now in SYSPROGAUTH) if they depend on PUBLIC access to an object.
For example, if a program contains a static SELECT statement involving table T1, and the owner of the program is dependent on PUBLIC access to T1, then 'Y' is the highest authorization value attainable for that statement -- and therefore for the program. This means that the owner is still able to run the program, but not to grant the RUN privilege on it to others. This, in turn, means that when this program is preprocessed under V2R2, users who previously may have had authority to run it (by virtue of receiving RUN authority from the owner) will no longer have that authority.
System Environment
Prior to V2R2, the ARI message numbers were three digits long and were followed by an action indicator. This identification formed a header for each line of the message text, as illustrated below:
ARI297A RESPONSE TO ARCHIVE PROMPT ARI297A IS NOT VALID.
In V2R2, these message numbers are expanded to four digits to accommodate future expansion of the system. Message numbers existing in the earlier releases now contain a high-order zero. Also, the message header is now only used on the first line of the message. The above example becomes:
ARI0297A RESPONSE TO ARCHIVE PROMPT
IS NOT VALID.
This could impact any automated operating system facility that you may be using (for example, the VM Programmable Operator) to scan the message number and text.
In the following example, the operations are contained in one LUW. The second operation involves multiple row changes to the database.
DELETE FROM SUPPLIER WHERE SUPPNO = 64 UPDATE INVENTORY SET PARTNO = PARTNO + 1 INSERT INTO QUOTATIONS VALUES (64, 221, .25, 5, 100)
The DELETE statement removes a supplier from the SUPPLIER table. The UPDATE statement changes the first two rows of the INVENTORY table, but fails on the third row because the operation would create a duplicate primary key value. 9
Prior to V2R2, the system would have left the new values in the first two rows of INVENTORY, with the rest of the table unchanged. To avoid this undesirable inconsistency, the application would have had to contain logic to recognize this error and roll back the entire LUW, thus undoing the DELETE.
In V2R2, when this error occurs, the system undoes the UPDATE statement by reversing the changes made to the first two rows. Because neither the DELETE nor the INSERT depends on the success of the UPDATE (these operations are atomic), the application has the following options open to it: