SQL and Data
Prior to V3R4, CONCAT was not a reserved word in SQL and could therefore be used as an ordinary identifier.
In V3R4, 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 V3R4; 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 V3R4, 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 V3R4, such parameters are not allowed. If they are used, SQLCODE -105 (SQLSTATE 37501) will be generated.
Prior to V3R4, 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 V3R4, 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 V3R4, 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 V3R4, SQLCODE -180 (SQLSTATE 22007) is generated under the above condition.
Prior to V3R4, the system internally built a composite table name that included the name of the relational database, based on a certain maximum length.
In V3R4, this length is slightly increased, and the internal process is the same, whether DRDA server support is involved or not. 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.
Prior to V3R4, the tables used by the EXPLAIN statement had some major differences from the corresponding tables in the DB2* product.
In V3R4, these differences are minimized to enhance the EXPLAIN functions and make them more compatible with those in the DB2 product. As a result, there are significant changes to the design of these tables and the EXPLAIN statement no longer works on the old tables. These changes include new columns dispersed among old ones, the loss of one column, a column data type change, and a column length change.
See the DB2 Server for VSE & VM SQL Reference manual for the new design of these tables.
If you have used the EXPLAIN tables in prior releases, you will have to recreate the revised tables before using the EXPLAIN statement in V3R4. To assist you in this task, a DBSU job file containing the necessary create statements is now included as an A-type member (called ARIXEXP) with the product.
Similarly, if you have applications which depend upon the design of the old EXPLAIN tables, you will need to modify these applications to reflect the new design.
Application Programming
Prior to V3R4, 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 V3R4, 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 V3R4, 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 V3R4, 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 V3R4, the NUL byte in a C NUL-terminated string was treated as a blank.
In V3R4, it is treated as a string terminator.
Prior to V3R4, 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 V3R4, these blanks will no longer be removed.
Prior to V3R4, the SQL/DS scalar function, LENGTH, with a C NUL-terminated string as its argument, returned the defined length.
In V3R4, this function now returns the length according to the position of the NUL terminator. (This length excludes the terminator itself.)
Prior to V3R4, 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 V3R4, 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 V3R4, 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 V3R4, 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 V3R4, a large number of SQLERRD1 codes were associated with SQLCODE -314 (SQLSTATE spaces) at preprocessor time for invalid host variables.
In V3R4, with the introduction of host structures and the associated parsing of declaration statements by the preprocessor, the values of some of these SQLERRD1 codes have changed.
If your application has dependencies on specific SQLERRD1 values, you should look for these changes in the DB2 Server for VM Messages and Codes or DB2 Server for VSE Messages and Codes manual and modify your application accordingly.
Prior to V3R4, there were a number of error situations for structure declarations in the SQL DECLARE SECTION that were not checked by the COBOL and C preprocessors.
In V3R4, these situations are subjected to validation checks, resulting in
the following potential errors, which must be corrected before
compilation:
| SQLCODE | SQLSTATE | Condition |
|---|---|---|
| -107 | 54003 | Host variable name too long |
| -307 | spaces | Duplicate host variable names |
| -314 | spaces | Syntax and semantic errors in a host variable |
Prior to V3R4, 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 V3R4, 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 V3R4, 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 V3R4, the latter user receives the same error message as the DBA user, instead of the authorization message.
Prior to V3R4, 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 V3R4, SQLCODE -204 (SQLCODE 52004) is generated instead, indicating that the object could not be found in the system catalog.
Prior to V3R4, 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 V3R4, 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 SQL/DS system 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 V3R4, the Database Administration manual contained sample table definition and DATALOAD parameters for creating a security audit table and loading trace records into it.
In V3R4, 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 V3R4 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 V3R4, if you used a host variable for the userid or password in a CONNECT statement and the data type of that variable did not satisfy one of the conditions listed below, an error was generated at run time:
In V3R4, these conditions are checked by the preprocessor. If they fail the check, SQLCODE -324 (SQLSTATE spaces) is generated.
Prior to V3R4, the resolution of data types for a parameter marker was dependent on the highest order of the data types of all the operands to the left of the parameter marker. Highest order, in the case of numeric operands, implies FLOAT > DECIMAL > INTEGER > SMALLINT.
In V3R4, this resolution process is changed to become more consistent with the DB2 product. If there is an operand expressed as a column name in a BETWEEN predicate, the data type of any parameter marker is resolved as that of the leftmost such operand. Otherwise, the data type of the parameter marker is resolved as that of the leftmost operand that is not a parameter marker -- whether in a BETWEEN predicate or an IN predicate.
This could cause a different result from previous releases for predicates that can have more than two operands (namely BETWEEN and IN), but only if your application assigns parameter marker values that are inappropriate for your data.
See Detailed Notes on V3R4-V3R2 Incompatibilities for some examples and further discussion.
Prior to V3R4, a bad input record would terminate DATALOAD command processing on multiple tables when the DBS Utility was running in multiple user mode -- whether or not it was preprocessed with the NOBLOCK option. An insert error would be indicated with one of the following codes, followed by message ARI0862E:
In V3R4, such command processing is no longer terminated, if the DBS Utility is preprocessed with the NOBLOCK option. The error indications are still generated, but the processing skips over the bad record and continues.
If you have a dependency in your application on this termination approach prior to V3R4, you may want to address this change in the case of the NOBLOCK option.
Prior to V3R4, when a SELECT DISTINCT was applied to a single column that had a unique index, the system assumed uniqueness within the column, rather than applying a sort. However, this kind of index dependency was not recorded in the package.
In V3R4, this technique now records the index dependency in the package (for system integrity), even though the index is not actually used to access the table. In addition, the technique is extended to column functions that use DISTINCT -- for example, SELECT COUNT(DISTINCT(COL4)), where COL4 has a unique index.
If the index is dropped, the package will now be marked as invalid, causing a dynamic reprep. After the reprep, the application will take longer to execute, because a sort will be needed to process DISTINCT correctly.
Prior to V3R4, certain SQLCODEs had associated SQLSTATEs that did not conform to the SAA standards.
In V3R4, these SQLSTATEs are replaced with ones that do conform. See Detailed Notes on V3R4-V3R2 Incompatibilities for a list of these codes, along with their old and new SQLSTATEs.
System Environment
Prior to V3R4, 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 V3R4, 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 V3R4, if no CHARNAME is specified, SQLSTART defaulted to CHARNAME = ENGLISH.
In V3R4, 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 V3R4 System Administration manual for the initial default CHARNAME value after installation or migration.
Prior to V3R4, user exits and field procedures , executed in a VSE environment, only ran in 24-bit addressing mode.
In V3R4, with VSE/ESA* 1.3 or later releases, they can be executed in 31-bit addressing mode. If the SQL/DS system is running in 31-bit addressing mode (that is, ESA or VMESA supervisor mode) on the application server, then user exits (except accounting) will be executed in 31-bit addressing mode.
If you have user exits (except accounting) that fit into this category, you must do one of the following to avoid any potential problems:
For more information on user exits, see the DB2 Server for VSE System Administration manual.
Prior to V3R4, during the preprocessing of a program, the system allocated a section size for each statement in the package.
In V3R4, 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 V3R4, 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.
In V3R4, 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 V3R4, 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 V3R4, 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 VSE System Administration manual for details on these classifications.
Prior to V3R4, if you specified level 2 trace for the STAT or PA component of the TRACDBSS or TRACRDS parameter, respectively, when starting the SQL/DS system, you received the Working Storage Manager tracing.
In V3R4, you can use the same specifications, but the Working Storage manager tracing is no longer part of the output.
In order to get this part, you must now use the TRACSTG parameter, or select the STG component when using the TRACE operator command. The format from this trace is different.
Prior to V3R4, for ease of reading, only the first line of a multiline message contained the message header identification, as illustrated below:
ARI0418A SQL/DS is not ready. Retry the enable
transaction CIRB after SQL/DS starts.
However, operator console messages which were multiline could not be handled by the VSE Programmed Operator tool, because the system sent such messages one line at a line. The tool could not identify the extra lines.
In V3R4, these operating console messages are sent as one multiline record, so that the VSE Programmed Operator tool can handle them. (For the console operator, there is no change to the appearance of these messages.)
If you have your own application equivalent to the above tool, it could be affected by this change.
In this first example, prior releases would resolve the data type of the parameter marker as DEC(4,2), whereas V3R4 would resolve it as INTEGER (assuming INTEGERCOL is the name of a column with a data type of INTEGER).
23.55 BETWEEN ? AND INTEGERCOL
The next two examples illustrate how these data type differences can produce quite different end results when the SQL statement is executed. In this next example, the predicate would generate SQLCODE -302 (SQLSTATE 22003) in prior releases, when the leftmost parameter marker is assigned a value of 345 and the rightmost parameter marker is assigned a value of 206.7. This error will not occur in V3R4.
EDLEVEL IN (16, ?, 17.3, ?)
This is because the prior releases assign a data type of DEC(3,1) to the rightmost parameter marker, to which the value 206.7 cannot be assigned. V3R4 assigns a data type of SMALLINT to the rightmost parameter marker (based on the column EDLEVEL) and then truncates 206.7 to accommodate this data type.
In the next example, the predicate would generate SQLCODE -302 (SQLSTATE 22001) in V3R4, but not in prior releases, when the parameter marker is assigned a value of 'GHIJKL'.
DEPTNO IN ('ABCDEF', ?, 'ABC')
This is because V3R4 assigns a data type of CHAR(3) to the parameter marker (based on column DEPTNO), to which the value 'GHIJKL' cannot be assigned. Prior releases assign a data type of CHAR(6) to the parameter marker.
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. |