This appendix identifies the incompatibilities that exist between each
release of the product and the previous release, going back to Version 1
Release 3.5. There is a separate section in the appendix for
each release.
Within each section, the incompatibility items are grouped into the
following categories:
For the purpose of this appendix, an "incompatibility" is defined
to be a part of the product that works differently than it did in the previous
release, in such a way that if used in an existing application, it will
produce a different result, necessitate a change to the application, or reduce
performance. In this definition, "application" can apply to a
broad range of things (singly or in combination), such as:
This appendix does not describe incompatibilities where certain
operations in the current release are less likely to generate an error
condition than they did in the previous release, as those changes will only
have a positive impact on your applications. (For example, the SUM and
AVG column functions no longer overflow as easily because they now use a
larger accumulator, and a change to the use of the equal (=) compare predicate
with a negative indicator variable now evaluates to UNKNOWN rather than
generating an error condition.)
Read the appropriate section of this appendix carefully to determine
what changes you will need to make to your applications when migrating from
one release to the next. You may also want to review the chapter in the
manual on migration considerations which discusses some of these
incompatibilities in more detail, plus other considerations for each
release-to-release migration.
This appendix excludes the numerous changes and enhancements for which no
impact on existing applications is anticipated. These are listed in the
Summary of Changes section (included with each manual) of the appropriate release of the
library. Review that section to see where you could make changes to
your existing applications in order to take advantage of some of these
enhancements.
SQL and Data
- Leading and Trailing zeros in Decimal Constants
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.
- Use of Host Variables with UNION
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
- Atomic Operations Against the Database
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:
- Operations on data located in nonrecoverable storage pools
- Operations on data when running without a log (LOGMODE=N).
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.
|
- Multiple Row Changes Within an 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.
- Four-Byte Floating-point Data
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.
- Arithmetic and Conversion Errors
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:
- Fixed point overflow
- Decimal overflow
- Exponent overflow
- Exponent underflow
- Divide exception.
For more detail, see the Messages and Codes manual, V2R2 or later, for SQLCODEs +802 and -802.
- GRANT Authority for PUBLIC
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
- Change to Message Numbers
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.
SQL and Data
- Table Designation Rules
Prior to V3R1, the following set of ANS/ISO SQL rules for table designation
in FROM clauses were not fully enforced:
- Duplicate table or view names in a FROM clause must all have a correlation
name assigned to them.
- Correlation names in a FROM clause must be distinct from each
other.
- Correlation names in a FROM clause must be distinct from the table or view
names in the same clause.
When the application contained ambiguities, such as
SELECT A.COL1
FROM A B, B A
where COL1 appeared in both table A and table B, the system accepted the
statement, employing its own set of rules to resolve the ambiguity.
This example represents only one type of ambiguity that could occur.
In V3R1, the ANS/ISO rules are fully enforced. Any violations
generate SQLCODE -211 (SQLSTATE 52012).
- New Reserved Words
Prior to V3R1, the following were not reserved words in SQL and could
therefore be used as ordinary identifiers:
- CHAR
- CHARACTER
- DOUBLE
- EXECUTE
- FIELDPROC
- GRAPHIC
- LONG
- PACKAGE.
Similarly, the following were not reserved words for the DBS
Utility:
In V3R1, these are reserved words, so an existing application that uses
any words in the SQL group above as an ordinary identifier will have to be
changed before it is preprocessed, or SQLCODE -105 (SQLSTATE 37501) will be
generated. Similarly, the words in the DBS Utility group above can no
longer be used in DBS Utility commands as ordinary identifiers.
You can address this incompatibility by changing these ordinary identifiers
to use nonreserved words, or you can retain the original names by redefining
them as delimited identifiers.
- Significance of Trailing Blanks
Prior to V3R1, trailing blanks were treated as significant in both object
names and VARCHAR and VARGRAPHIC column values.
In V3R1, such trailing blanks are not considered significant.
If your applications must continue to treat trailing blanks as significant,
you may have to undertake some redesign. See Detailed Notes on V3R1-V2R2 Incompatibilities for further discussion and examples.
- Timestamp at the 24th Hour
Prior to V3R1, a timestamp value in which the hour portion was 24 and the
minute, second, or microsecond portion was not zero, was accepted as valid
data for insertion or updating.
In V3R1, an attempt to insert or update a column with such a value
generates SQLCODE -181 (SQLSTATE 22007). When the hour portion is 24,
the other time portions must now be zero.
If you have any of these invalid values in your tables after migrating to
V3R1, they will prevent you from doing a DBS Utility unload/reload operation
or an INSERT using a subselect. You will have to first correct these
values to conform to the rule mentioned above.
Application Programming
- Invalid Pointers in SQLDA and RDIIN
Prior to V3R1, the system checked for invalid pointers in the SQLDA and
RDIIN structures. This checking was extensive, often resulting in poor
performance.
In V3R1, in the interest of better performance, this checking has been
eliminated. It is up to the application programmer to follow the rules
on setting pointers in the SQLDA, as outlined in the chapter "Using Dynamic
Statements" in the V3R1 Application Programming manual. Pointers in the RDIIN must not be changed by the
application. If your application does not satisfy these rules, the
results will be unpredictable.
- Continuation Characters in FORTRAN
Prior to V3R1, the FORTRAN preprocessor ignored any continuation character
located in front of an EXEC SQL on the same line, provided it was not part of
an IF or ELSE statement -- even though such coding was incorrect.
In V3R1, the continuation character is acknowledged and the EXEC SQL is
ignored.
- Missing Comma in COBOL Continuation Lines
Prior to V3R1, if you left out an intended comma from a list of parameters
in an SQL statement embedded in a COBOL program (as illustrated below) and did
not code a continuation character in the next line, the system would assume a
continuation character and misinterpret the parameter list, giving potentially
wrong results.
SELECT *
FROM T1
WHERE COL1 IN ('AB' <--- missing comma
'CD', <--- no continuation character
'EF')
In V3R1, this error is detected and reported at preprocessor time.
- DROP PROGRAM Statement Containing Host Variables
Prior to V3R1, the processing of a DROP PROGRAM statement that contained
host variables required a specific section in the access module. (In
this form of the statement, the name of the owner of the program or the name
of the program or both are expressed as host variables.)
| Note on New Terminology: | As of V3R1, PACKAGE becomes the new reserved word for PROGRAM, the latter
remaining as a synonym. Access modules are now referred to as
packages. This new terminology is used below.
|
In V3R1, the host variable form of the DROP PACKAGE statement no longer
requires a section in the package. All the information required to
execute the statement is sent with the execution-time request. You will
be affected if you have this form of the DROP PACKAGE coded in your
application programs.
If the programs that use these packages are explicitly repreprocessed, they
will have to be recompiled (or reassembled) and relinked in order to execute
successfully. Otherwise, errors will result, since there will be fewer
sections in the new package and this will cause a mismatch between section
numbers in the RDIIN structure and the new package.
- Data Type of String Constants
Prior to V3R1, application programs that assumed that string constants have
a data type of VARGRAPHIC because they are used in the context of GRAPHIC and
VARGRAPHIC data, were accepted.
In V3R1, such constants are considered to be VARCHAR, and if used in
conjunction with GRAPHIC or VARGRAPHIC data will result in an error, such as
SQLCODE -171 (SQLSTATE 53015) or SQLCODE -408 (SQLSTATE 53021).
If the host language is COBOL, PL/I, or C, you should use explicitly coded
graphic constants. See the section of the V3R1 SQL Reference manual that discusses graphic string constants.
- New Options in CREATE PROGRAM Statement
Prior to V3R1, when the following three options:
ISOL({RR|CS|USER})
DATE({ISO|USA|EUR|JIS|LOCAL})
TIME({ISO|USA|EUR|JIS|LOCAL})
were used in conjunction with an extended dynamic access module, the
values for these options were determined when statements referencing the
extended dynamic access module were executed. The values were set based
on the corresponding preprocessing options of the program containing the
extended dynamic statements.
| Note on New Terminology: | As of V3R1, PACKAGE becomes the new reserved word for PROGRAM, the latter
remaining as a synonym of the former. Access modules are now referred
to as packages. This new terminology is used below.
|
In V3R1, these options are added to the CREATE PACKAGE statement, so that
they become preprocessing options. This means that their values are
stored with the package itself, and are enforced when the sections of the
package are executed. Consequently, your programs may now run at a
different isolation level than they did in V2R2.
See Detailed Notes on V3R1-V2R2 Incompatibilities for examples that illustrate how incompatibilities may arise
as a result of this change.
- Views Created from SELECT *
Prior to V3R1, views created as SELECT * FROM T1 required no special
attention when being migrated from release to release, even when columns had
been added to table T1 after the creation of the view.
In V3R1, a necessary change to the system now requires special attention in
the above situation. The first time the system encounters such a view
in an application, it attempts to rebuild the view, and fails with SQLCODE
-835 (SQLSTATE 56049).
To avoid this failure, drop and recreate the view before running the
application on V3R1. Depending on how your application logic is coded,
you may have to change that logic in order to handle the extra columns that
were added to table T1. The best practice is to avoid the use of SELECT
* for view creation, and specify the explicit columns that the application
requires.
- Semicolon Delimiter in SYSVIEW Table
Prior to V3R1, when a view was created through the DBS Utility or by
running a preprocessed program, the CREATE VIEW statement was inserted into
column VIEWTEXT of catalog table SYSVIEWS with a semicolon delimiter.
In V3R1, this delimiter is no longer included.
If your application has a dependency on the existence of this delimiter in
the SYSVIEWS table, you will need to change it accordingly.
- Replacement of Error Message ARI0565E
Prior to V3R1, error message ARI0565E was issued during preprocessing of
FORTRAN programs whenever the input source contained no SQL statements that
required creation of a package.
In V3R1, this message is replaced by information message ARI0565I.
In addition, related message, ARI0598I, dealing with the status of the
package, is modified.
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.
- Replacement of SQLCODE -150
Prior to V3R1, an attempt to modify data through a view based on more than
one table generated SQLCODE -150.
In V3R1, this is replaced with SQLCODE +149 at preprocessor time, and
SQLCODE -149 (SQLSTATE 53007) at run time.
- New Positive SQLCODEs
Prior to V3R1, a number of negative SQLCODEs and associated positive
RDSCODEs were returned during preprocessing to indicate a warning
situation.
In V3R1, new positive SQLCODEs are returned instead, which correspond
identically to the above negative SQLCODEs in code number and (in most cases)
message text and explanation. If the error is not removed, the
corresponding negative SQLCODEs will be issued at run time.
See Detailed Notes on V3R1-V2R2 Incompatibilities for a list of these new positive SQLCODEs.
System Environment
- Uppercase and Mixed Case in Message Text
Prior to V3R1, all message text was in uppercase for all the languages
available in the product except German, which was available only in mixed
case.
| Note: | The uppercase applied to both English language offerings, AMENG and
UCENG. It also applied to the English text embedded in the DBCS
languages Japanese and Korean (for example, "FORCE",
"SQLEND").
|
In V3R1, the message text of three more languages is now changed to mixed
case only. These languages are AMENG (the default language setting),
Italian, and Spanish. If you are using any of these three languages and
you have existing case-sensitive applications that scan for specific message
text in uppercase only, you will have to modify them to detect lowercase as
well. This could impact any automated operating system facility that
you may be using for this purpose (for example, the VM Programmable
Operator).
An alternative approach (for English users only) to modifying your
applications would be to specify UCENG instead of AMENG, through the SET
LANGUAGE command.
- Authorization for Changing System Catalog Tables
Prior to V3R1, certain portions of the catalog could be updated, deleted,
or inserted into, by any user with DBA authority.
In V3R1, the number of columns in the catalog tables for which these
changes are allowed is reduced.
This change may affect the authorization of some of your
applications. See Appendix E of the V3R1 SQL Reference manual for a list of the columns that can now be updated,
deleted, or inserted.
- Modification of Sample Tables and Applications
Prior to V3R1, the sample tables shipped with the product consisted of five
Manufacturing tables and four Organizational-project tables. The sample
applications shipped with the product used the Manufacturing tables.
In V3R1, the Manufacturing tables are not included, but can be installed
optionally. The Organization-project tables are enhanced to provide
more guidance on referential integrity and also consistency across the IBM
relational database products. The enhancements include:
- Two new tables
- A new column in an existing table
- Renaming of a table
- Modification of a foreign key definition.
The sample applications are now modified to use the enhanced
Organization-project tables. They now issue a ROLLBACK instead of a
COMMIT, so that they can be rerun without having to first restore the sample
database.
If you have any applications that use these tables, such as an online
tutorial or a test package for new releases, you will need to upgrade them
accordingly.
System Environment (VM Only)
- Storage of SQLINIT Parameters
Prior to V3R1, the parameters for the SQLINIT EXEC were stored in a
bootstrap module. The VM Resource Adapter (VRA) could access this
module as long as the module was on one of the accessed minidisks.
In V3R1, these SQLINIT parameters are stored in the LASTING GLOBALV file,
which is only accessible by the VRA if this file is on the A-disk. (The
CMS GLOBALV manipulation commands require this file to be on the
A-disk.)
For example, if you have been executing batch SQL applications by
requesting a CMS batch machine to access (as a non-A-disk) the disk where the
program and bootstrap reside, you now have to request the CMS batch machine to
issue SQLINIT explicitly, so that the proper parameters can be appended to the
LASTING GLOBALV file on the batch machine's A-disk.
- Default Name for Database Machine
Prior to V3R1, the default name of the database machine was SQLDBA.
In V3R1, this default name is now SQLMACH, to distinguish it from SQLDBA,
which remains the default name of the database defined on SQLMACH.
This change is only of interest to the system administrator who supplies
the name of the database machine during the set up of the SQLDBN file in the
XA environment.
SQL and Data
- New Reserved Word, CONCAT
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.
- REVOKE UPDATE
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.
- Numeric Data in Character Strings
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.
- Invalid String Representation of Datetime
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.
- Internally Generated Table Names
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
- Setting of SQLN Field
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.
- C NUL-Terminated Strings - Variable Length
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.
- C NUL-Terminated Strings - NUL Byte
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.
- C NUL-Terminated Strings - Trailing Blanks
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.
- C NUL-Terminated Strings - Length
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.)
- SQL Statement String
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 item SYS VIEWS under V3R1 and V2R2 Incompatibilities.
- Preprocessing of Extended Dynamic Statements
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.
- Data Type of Hexadecimal Constants
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.
- Non-updatable View
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.
- SYSTEM Table Missing from the System Catalog
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.
- Folding of Lowercase in PREP and DBSU
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.
- Loading Audit Trace
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.
- Switching Databases without Connect Authority
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.
- SQLCODE Generated by Operator FORCE Command
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.
- SQLSTATE Changes
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
- The Use of DBCS Characters with the CHARNAME Setting
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).
- Setting of CHARNAME
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.
- Addressing Mode 31-Bit
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:
- Ensure that they can accommodate 31-bit addressing mode
- Operate the database machine in 370 mode
- Set the AMODE SQLSTART parameter to 24 to force the database manager to
run in 24-bit addressing mode.
For information on converting your applications to accommodate 31-bit
addressing mode, see the VM/XA* Application Conversion Guide For more information on single user mode and user exits, see the System
Administration manual.
- Section Size in a Package
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.
- Three-Part Object Names
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.
- Special Characters for CONCAT Operation and Not Equal Condition
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.
SQL and Data
- Enhanced EXPLAIN Tables
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
- Reason Codes for Incorrect Host Variable Declarations
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.
- Structured Declarations in COBOL and C
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
|
- Qualified Field Names in RPG
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:
- file-name.field-name
- DS-name.subfield-name
The preprocessor needs this information in order to interpret the
reference. If the qualifier is missing, a preprocessor ARI5370E message
is generated.
- Use of Structures in RPG as Host Variables
Prior to V3R4, when the database manager referenced an RPG structure as a
host variable, one of two things happened:
- If the structure contained one or more subfields, the database manager
accepted the reference. The structure was interpreted as a single
character field with a length equal to the length of the total
structure.
- If the structure contained no subfields, the database manager rejected the
reference, generating an error message.
In V3R4:
- If the structure contains one or more subfields, the reference to it is
now interpreted as a reference to each subfield in the structure, giving
unpredictable results and potential errors at execution time.
- If the structure contains no subfields, the reference to it is now
interpreted as a reference to a fixed length character string with a length
equal to the length of the data structure.
| 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.
- Application Programs in an Unconnected State
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.
- Use of Host Variables in CONNECT Statement
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:
- C programs: C-NUL string of length 9
- Assembler, COBOL, or PL/I programs: fixed length character string of
length 8.
In V3R4, these conditions are checked by the preprocessor. If they
fail the check, SQLCODE -324 (SQLSTATE spaces) is generated.
- Data Types of Parameter Markers in Predicates
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.
- Bad Input Records in DATALOAD
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:
- SQLCODE
- SQLSTATE
- -405
- 53020
- -424
- 22502
- -530
- 23503
- -802
- 22003, 22012, or 22502
- -803
- 23505
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.
- Index Dependency of a Package
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
- Invocation of TRACE for Storage
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.
- DBCS Data Conversion Errors
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.
- Saved Segments in Installation Process
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.
- Enhancement to COLDLOG
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.
SQL and Data
- New Reserved Word, CONCAT
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.
- REVOKE UPDATE
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.
- Numeric Data in Character Strings
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.
- Invalid String Representation of Datetime
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.
- Internally Generated Table Names
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.
- Enhanced EXPLAIN Tables
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
- Setting of SQLN Field
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.
- C NUL-Terminated Strings - Variable Length
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.
- C NUL-Terminated Strings - NUL Byte
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.
- C NUL-Terminated Strings - Trailing Blanks
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.
- C NUL-Terminated Strings - Length
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.)
- SQL Statement String
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 item SYS VIEWS under V3R1 and V2R2 Incompatibilities.
- SQL/DS Preprocessing of Extended Dynamic Statements
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.
- Reason Codes for Incorrect Host Variable Declarations
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.
- Structured Declarations in COBOL and C
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
|
- Data Type of Hexadecimal Constants
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.
- Non-updatable View
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.
- SYSTEM Table Missing from the System Catalog
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.
- Folding of Lowercase in PREP and DBSU
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.
- Loading Audit Trace
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.
- Use of Host Variables in CONNECT Statement
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:
- C programs: C-NUL string of length 9
- Assembler, COBOL, or PL/I programs: fixed length character string of
length 8.
In V3R4, these conditions are checked by the preprocessor. If they
fail the check, SQLCODE -324 (SQLSTATE spaces) is generated.
- Data Types of Parameter Markers in Predicates
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.
- Bad Input Records in DATALOAD
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:
- SQLCODE
- SQLSTATE
- -405
- 53020
- -424
- 22502
- -530
- 23503
- -802
- 22003, 22012, or 22502
- -803
- 23505
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.
- Index Dependency of a Package
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.
- SQLSTATE Changes
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
- The Use of DBCS Characters with the CHARNAME Setting
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).
- Setting of CHARNAME
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.
- Addressing Mode 31-Bit
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:
- Ensure that they can accommodate 31-bit addressing mode
- Operate the application server in 370 or VM supervisor mode.
For more information on user exits, see the DB2
Server for VSE System Administration manual.
- Section Size in a Package
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.
- Three-Part Object Names
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.
- Special Characters for CONCAT Operation and Not Equal Condition
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.
- Invocation of TRACE for Storage
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.
- Change to Headers in Multiline Operator Console Messages
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.
View figure.
View figure.
View figure.
View figure.
View figure.
View figure.