| 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 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 a MACRO file (called ARISEXP) 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, 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 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, it was not necessary to qualify the name of a field or subfield in an SQL statement, when that field or subfield name had been duplicated in more than one data structure.
In V3R4, you must qualify these names as follows:
The preprocessor needs this information in order to interpret the reference. If the qualifier is missing, a preprocessor ARI5370E message is generated.
Prior to V3R4, when the database manager referenced an RPG structure as a host variable, one of two things happened:
| Note: | Individual subfields within a structure can still be directly referenced as valid host variables. There is no change to this. |
If your application references RPG structures as host variables, you will have to change either the declaration section or the SQL statements affected.
Prior to V3R4, if an application program was connectable but in an unconnected state as a result of a severe error (SQLWARN6 = S) and issued a non-connect SQL statement, the database manager initiated an abend of the application.
In V3R4, SQLCODE -900 (SQLSTATE 51018) is generated and the abend does not occur. If your application is dependent on the abend scenario in this situation, you will have to change it. Otherwise, it may enter an infinite loop.
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-V3R3 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.
System Environment
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 database manager, 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, if there was a loading error in a DBCS data conversion routine, SQLCODE -332 (SQLSTATE 57017) was generated with reason code 9. If there was a dropping error in a DBCS data conversion routine, SQLCODE -901 (SQLSTATE 58004) or SQLCODE -30020 (SQLSTATE 58009) was generated.
In V3R4, the above codes are replaced with SQLCODE -674 (SQLSTATE 57011) with a separate reason code for each specific error.
Prior to V3R4, you could install into saved segments during the installation process (with the I5688103 EXEC), or at post installation time.
In V3R4, this step is no longer in the I5688013 EXEC. Installing into saved segments must be done afterwards.
If you have automated the running of this EXEC by providing an input file containing the answers to the prompts (rather than submitting them from the console), the EXEC will fail when trying to process your input to the removed saved segment step. You will have to modify your answer file accordingly.
Prior to V3R4, the COLDLOG reconfiguration function erased the log contents before starting the database manager. No warning was given if there were any logical units of work in the log that were needed for recovery processing.
In V3R4, the log content is not erased until after startup, and the user is warned beforehand if the log content is needed for recovery.
If you have automated the COLDLOG function in some way by providing a predetermined set of answers to the prompts (rather than submitting them from the console), the SQLLOG EXEC will fail. You will have to modify your automated process to accommodate the change. See the DB2 Server for VM System Administration manual for more information on this function.
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.