| Note: | This section does not include the restrictions on the use of DRDA protocol, as that topic is covered in the appendix describing DRDA considerations. |
SQL and Data
Prior to V3R3, CONCAT was not a reserved word in SQL and could therefore be used as an ordinary identifier.
In V3R3, CONCAT is a reserved word, and can be used as an alternative to the concatenation operator (||). Any existing applications that use it as an ordinary identifier will have to be changed before they are preprocessed under V3R3; otherwise 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 V3R3, the REVOKE statement for the UPDATE privilege ignored any column names that might be present as parameters of the UPDATE option -- even though such coding was invalid. (This statement is only done on a table basis, never a column basis.)
In V3R3, such parameters are not allowed. If they are used, SQLCODE -105 (SQLSTATE 37501) will be generated.
Prior to V3R3, columns with a data type of CHAR or VARCHAR accepted numeric data, including FLOAT, on insert or update. For example, the following statements did not create an error:
CREATE TABLE T1 (COL CHAR(8)) CREATE TABLE T2 (COL VARCHAR(8)) INSERT INTO T1 (123) INSERT INTO T2 (123) INSERT INTO T1 (1E1) INSERT INTO T2 (1E1) UPDATE T1 SET COL = 123 UPDATE T2 SET COL = 123 UPDATE T1 SET COL = 1E1 UPDATE T2 SET COL = 1E1
In V3R3, these inserts and updates now generate SQLCODE -408 (SQLSTATE 53021).
If you want to use the value 123, you must now use it as a character literal ('123'). Float literals are no longer allowed for character columns.
Prior to V3R3, when a predicate was being evaluated that contained an operand that was one of the special registers CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP, and one of the other operands was a character column of the correct length but containing a value that was not a valid string representation of a datetime, the application ran successfully. Any row containing such an invalid value was returned if it met the search condition. For example, all invalid date values in column, ORDERDATE, were returned for the following condition:
WHERE CURRENT DATE <> ORDERDATE
In V3R3, SQLCODE -180 (SQLSTATE 22007) is generated under the above condition.
Prior to V3R3, the system internally built a composite table name that included the name of the relational database, based on a certain maximum length.
In V3R3, this length is slightly increased, and the internal process is now common to the SQL/DS and DRDA protocols. As a result, there is a very small probability that some of your SQL statements could exceed an internal limitation of the system and generate an SQLCODE -101 (SQLSTATE 54001).
The more table names you have in a statement, the greater the probability of this occurring. If you experience this error, one possible solution would be to break the statement down into two separate statements.
Application Programming
Prior to V3R3, if field SQLD in the SQLDA area held a greater value than the SQLN field after a DESCRIBE, the system set SQLN to zero.
In V3R3, the value of SQLN is not changed.
If your application tests SQLN for zero to verify successful completion of the DESCRIBE, the logic will have to be revised to test for SQLD > SQLN.
Prior to V3R3, a C input string with a length greater than 1 was treated as a fixed length character host variable. It was not mandatory to have a NUL present in it except when the input host variable length was 255, in which case SQLCODE -426 (SQLSTATE 22523) was generated.
In V3R3, a C input string is no longer treated as fixed length. A NUL must be present on all C NUL-terminated input strings except those with a length of 1; otherwise SQLCODE -302 (SQLSTATE 22001) is generated. SQLCODE -426 (SQLSTATE 22523) is no longer generated.
Prior to V3R3, the NUL byte in a C NUL-terminated string was treated as a blank.
In V3R3, it is treated as a string terminator.
Prior to V3R3, any trailing blanks in a C NUL-terminated string were removed when using the string to update or insert a VARCHAR column or to compare to a VARCHAR column.
In V3R3, these blanks will no longer be removed.
Prior to V3R3, the scalar function, LENGTH, with a C NUL-terminated string as its argument, returned the defined length.
In V3R3, this function now returns the length according to the position of the NUL terminator. (This length excludes the terminator itself.)
Prior to V3R3, an SQL statement string could end with a statement terminator, when used in conjunction with EXECUTE IMMEDIATE, PREPARE, or Extended PREPARE. An example of such a statement is
DROP TABLE T1;
which has a trailing semicolon. This was allowed in application programs, even though such coding was invalid. It was also allowed in ISQL and QMF*, since those facilities also use the above three statements to process interactively issued statements.
In V3R3, this statement terminator is not allowed. If it is used, SQLCODE -104 (SQLSTATE 37501) will be generated.
If you have been using such a terminator for the CREATE VIEW statement, your use of catalog table SYSVIEWS could be affected, as described in the DB2 Server for VSE & VM SQL Reference manual.
Prior to V3R3, a cursor-variable with a defined length greater than 18 was accepted by the preprocessor, even though such variables should only be defined with a length of 18.
In V3R3, the preprocessor traps this condition and generates SQLCODE -324 (SQLSTATE spaces). You will have to change any applications that use these invalid cursor-variable lengths in your extended dynamic statements.
Prior to V3R3, application programs that assumed that hexadecimal constants have a data type of VARGRAPHIC, because they are used in the context of GRAPHIC and VARGRAPHIC data, were accepted.
In V3R3, such constants are considered to be VARCHAR. If used in conjunction with GRAPHIC or VARGRAPHIC data, they will cause a number of specific SQLCODEs and corresponding SQLSTATEs, dependent on individual cases.
This also means that SQLCODE -421 (SQLSTATE 53055), dealing with hexadecimal literals of odd length, is no longer generated.
Prior to V3R3, a user with DBA authority who tried to update a view that was not updatable got an appropriate error, such as SQLCODE -154 (SQLSTATE 56009). A user without DBA authority, however, got an authorization error, SQLCODE -551 (SQLSTATE 59001).
In V3R3, the latter user receives the same error message as the DBA user, instead of the authorization message.
Prior to V3R3, if you tried to INSERT, DELETE, or UPDATE a table or view created by 'SYSTEM', but which was not in the system catalog, SQLCODE -823 (SQLSTATE 53032) was generated, indicating that you lacked proper authorization.
In V3R3, SQLCODE -204 (SQLCODE 52004) is generated instead, indicating that the object could not be found in the system catalog.
Prior to V3R3, folding of lowercase into uppercase in PREP and the DBS Utility was done by adding X'40' to the hexadecimal representation of the lowercase character. Sometimes this resulted in characters being folded incorrectly (for example, in the Katakana character set).
In V3R3, this is done using the 370 built-in Assembler instruction TRANSLATE and the user-specified character translation table, in order to be consistent with how the application server handles this operation. One exception to this is when the DBS Utility processes SCHEMA input files. Folding is no longer done on these files; this makes it consistent with the DBS Utility control file, which only allows uppercase input.
If your applications have built-in dependencies on the previous folding scheme, you could get different results. For example, a Katakana user may have a character in his or her coding scheme that has a hexadecimal value that appears to the database manager as one of the 26 lowercase English letters. Instead of being folded to uppercase English, the Katakana character will now be folded according to the Katakana character translation table.
If you have lowercase in your DBS Utility SCHEMA input file, you will have to change it to uppercase.
Prior to V3R3, the Database Administration manual contained sample table definition and DATALOAD parameters for creating a security audit table and loading trace records into it.
In V3R3, the position of the columns within the table are changed and a new column, EXTLUWID, added. If you have been loading audit trace data using this table definition and a DATALOAD job, you will need to change the DATALOAD job, as documented in the V3R3 Database Administration manual. If you also want to make use of the new EXTLUWID column, you will need to recreate the table as well.
Prior to V3R3, if you attempted to switch databases and did not have connect authority for the new database, SQLCODE -561 (SQLSTATE 42505) was generated as a warning situation. It was possible to continue processing on the original database with a non-CONNECT statement.
In V3R3, this situation is treated as a severe error, SQLWARN0 and SQLWARN6 are set to 'S', and any subsequent non-CONNECT statement results in termination of the application. Only a CONNECT statement is accepted.
Prior to V3R3, either SQLCODE -933 (SQLSTATE 57027) or SQLCODE -948 (SQLSTATE 57027) was returned to the application, when the operator issued a FORCE command to roll back the current logical unit of work.
In V3R3, only SQLCODE -933 (SQLSTATE 57027) is returned.
Prior to V3R3, certain SQLCODEs had associated SQLSTATEs that did not conform to the SAA standards.
In V3R3, these SQLSTATEs are replaced with ones that do conform. See Detailed Notes on V3R3-V3R2 Incompatibilities for a list of these codes, along with their old and new SQLSTATEs.
System Environment
Prior to V3R3, you could use graphic or mixed constants, the VARGRAPHIC scalar function, or you could define columns as GRAPHIC or FOR MIXED DATA, independent of the CHARNAME setting on the application server. Furthermore, you could use graphic or mixed constants, independent of the CHARNAME setting on the application requester.
In V3R3, the above usages result in error conditions such as SQLCODE -640 (SQLSTATE 56031) and SQLCODE -332 (SQLSTATE 57017), if the corresponding CHARNAME does not define a character set with mixed CCSID (that is, if CCSIDMIXED = 0).
Prior to V3R3, if no CHARNAME was specified, SQLSTART defaulted to CHARNAME = ENGLISH.
In V3R3, it defaults to the CHARNAME used on the previous invocation. If the CHARNAME setting does not define a character set with mixed CCSID (that is, if CCSIDMIXED = 0), then the default character subtype (CHARSUB) will be forced to a value of SBCS.
See the V3R3 System Administration manual for the initial default CHARNAME value after installation or migration.
Prior to V3R3, application programs running in single user mode in a VM environment of XA, ESA 1.0 ESA, or ESA 1.1 ESA, as well as any user exits (accounting, datetime, or field procedures) executed in these environments on the database machine, whether single or multiple user mode, only ran in 24-bit addressing mode.
In V3R3, if the database manager is running in 31-bit addressing mode (AMODE 31) on the database machine, the above application programs and user exits will also run in this mode.
If you have application programs or user exits that fit into this category, you must do one of the following:
Prior to V3R3, during the preprocessing of a program, the system allocated a section size for each statement in the package.
In V3R3, due to other design changes, it is necessary to increase the size of these sections for SELECT statements. As a result, when an existing package is subjected to a dynamic repreparation, it may cause the dbspace to become full, generating SQLCODE -946 (SQLSTATE 57025).
If this occurs in your installation, you will have to explicitly prepare the program with the SQLPREP EXEC, making sure that you have a dbspace that can accommodate the revised package.
Also, the larger sections increase the amount of virtual storage required to run the package. For example, if you have many dynamic SELECT statements in a logical unit of work, they will use up more storage than in the previous release.
Prior to V3R3, an object that was created on a database named (for example) DBX could be successfully referenced later by an application, even though the name for that database had been changed (to, say, DBY). All you had to do was use the revised name, DBY, when you established the database for the application by means of the SQLINIT EXEC.
In V3R3, the system maintains the name of the database that was used at the time of the object's creation (DBX in this example), as the first part of the object name, thereby making it a three-part name. If you now establish the database for the application under a different name (for example, DBY) the system uses that name as the new qualifier when you try to reference the object. This results in a mismatch of object names and causes SQLCODE -114 (SQLSTATE 56061) to be generated.
This problem can be avoided by simply not changing the names of your databases.
Prior to V3R3, the class of the hexadecimal values in the table below was
0.
| CHARNAME | Hexadecimal Values |
|---|---|
| ENGLISH | X'5A', X'B0' |
| FRENCH | X'BA', X'BB' |
| GERMAN | X'BA', X'BB' |
| ITALIAN | X'BA', X'BB' |
| KATAKANA | X'5A', X'B0' |
| SPANISH | X'BA', X'BB' |
In V3R3, the class of these hexadecimal characters is changed to 6. This is reflected in the CHARCLASS column values of the SYSTEM.SYSCHARSETS catalog table. This change provides additional special characters that can be used to depict the CONCAT operation and the not equal condition in SQL syntax. This, in turn, provides greater flexibility in the use of these two SQL facilities between application requesters and servers that are assigned different CHARNAMES.
This could affect your applications, if they are dependent on previous reclassifications of any of the above characters from class 0 to class 3, for use in ordinary identifiers. For example, if you had reclassified the explanation mark (!) so that DANGER! could be used as an ordinary identifier, this will no longer work because the explanation mark is one of the characters that is now assigned to class 6.
See the DB2 Server for VM System Administration manual for details on these classifications.
These changes are shown in the following table.
| SQLCODE | Old SQLSTATE | New SQLSTATE | DESCRIPTION |
|---|---|---|---|
| -131 | 53004 | 22019 | Either the LIKE predicate has an invalid escape character, or the string pattern contains an invalid occurrence of the escape character. |
| -551 | 59001 | 42501 | User wwwwww does not have the xxxxxx privilege to perform yyyyyy on zzzzzz. |
| -552 | 59002 | 42502 | xxxxxx is not authorized to yyyyyy. |
| -554 | 59002 | 42502 | You cannot grant a privilege to yourself. |
| -555 | 59002 | 42502 | You cannot revoke an authority or a privilege from yourself. |
| -556 | 59002 | 42502 | An attempt to revoke a privilege from xxxxxx was denied. Either xxxxxx does not have this privilege, or yyyyyy does not have this authority to revoke this privilege. |
| -556 | 59004 | 42504 | An attempt to revoke a privilege from xxxxxx was denied. Either xxxxxx does not have this privilege, or yyyyyy does not have this authority to revoke this privilege. |
| -558 | 59004 | 42504 | You cannot revoke an authority from xxxxxx because xxxxxx has DBA authority. |
| -560 | 59005 | 42505 | A CONNECT statement contains an incorrect password for xxxxxx. |
| -561 | 59005 | 42505 | User xxxxxx does not have CONNECT authority. |
| -566 | 59001 | 42501 | User ID xxxxxx does not have authorization to modify package yyyyyy. |
| -606 | 59002 | 42502 | The COMMENT ON or LABEL on statement failed because the specified table or column is not owned by xxxxxx. |
| -610 | 59002 | 42502 | The statement failed because a user without DBA authority attempted to create a table in a DBSPACE owner by another user or by the system. |
| -708 | 59002 | 42502 | You cannot ALTER, LOCK, or DROP a PUBLIC DBSPACE because you do not have DBA authority. |
| -713 | 37515 | 53015 | Incorrect isolation level value xxxxxx specified. Only values C or R may be used. |
| -801 | 22004 | 22003 | Exception error xxxxxx occurred during yyyyyy operation on zzzzzz data. |
| -802 | 22004 | 22003 | Exception error xxxxxx occurred during yyyyyy operation on zzzzzz data, position nnnnnn. psw1 psw2. |
| -815 | 59005 | 42502 | CONNECT denied by accounting user exit routine. |
| -30053 | 59006 | 42506 | Owner xxxxxx authorization failed. |