DB2 Server for VSE & VM SQL Reference (Version 7 Release 5)

PROG1 (ISOL=RR)

PROG4 (ISOL=USER)

PACKA

PACKB

CREATE PACKAGE PACKA

CREATE PACKAGE PACKB

OPTION ISOL(RR)

OPTION ISOL(USER)

PREPARE FROM :STMTSTR

section n

PREPARE FROM :STMTSTR

SETTING :SECTION IN PACKA

SETTING :SECTION IN PACKB

PROG2 (ISOL=CS)

EXECUTE :SECTION IN PACK A

(run at RR)

PROG3 (ISOL=USER)

SQLISL = C

EXECUTE :SECTION IN PACKA

(run at RR)

EXECUTE :SECTION IN PACKB

(run at CS)

SQLISL = R

EXECUTE :SECTION IN PACKB

(run at RR)

Figure 13. Version 3 Release 1

Figure 13 shows the same scenario in V3R1. In this case, the isolation level RR

is specified when the PACKA package is created. When program PROG2

executes a section in PACKA, isolation level RR is used.

440

SQL Reference

PROG1 (ISOL=RR)

PACKA

CREATE PACKAGE PACKA

OPTION ISOL(RR)

PREPARE FROM :STMTSTR

section n

SETTING :SECTION IN PACKA

PROG2 (ISOL=CS)

EXECUTE :SECTION IN PACK A

(run at CS)

Figure 15. Dropping and Re-creating PACKA Without Repreprocessing PROG2

PACKA

section n

PROG2 (ISOL=CS)

EXECUTE :SECTION IN PACK A

(run at RR)

Figure 16. Re-preprocessing PROG2

Figure 15 and Figure 16 show that once an extended dynamic package has been

dropped and recreated in V3R1 with an isolation level other than USER, the

isolation level bind option will be enforced whenever the executing application

has also been preprocessed, assembled, and re-linked under V3R1. If the

PACKA package has been dropped and recreated in V3R1, with an isolation

level of RR, then:

v If program PROG2 is still pre-V3R1, when the section in PACKA is executed,

isolation level CS will be used.

v Otherwise, isolation level RR will be enforced whenever sections in PACKA

are executed.

3. New Positive SQLCODEs

These codes are shown in the table below.

SQLCODE

SQLSTATE

DESCRIPTION

+117

01525

The number of data values to be inserted does not equal

the number of columns specified or implied.

+134

Improper use of long string.

442

SQL Reference

SQLCODE

SQLSTATE

DESCRIPTION

+135

The input for a long string column in an INSERT

statement or UPDATE statement must be from a host

variable or be the keyword NULL.

+149

The view cannot be used to modify data because it is

based on more than one table.

+151

A column of a view cannot be updated since it is derived

from an expression.

+154

View limitations do not allow you to use the following

operation: xxxxxx

+202

01533

Column xxxxxx was not found in any table referenced by

the statement.

+204

01532

xxxxxx was not found in the system catalog.

+205

01533

Column xxxxxx was not found in table yyyyyy.

+206

01533

The xxxxxx on yyyyyy was not found.

+401

Incompatible data types found in an expression or

compare operation.

+404

A character string specified in an INSERT or UPDATE

statement is too large for the target column.

+405

The numeric value, xxxxxx, is not within the range of the

data type.

+407

Either an UPDATE statement or an INSERT statement

with a null value for a column defined as NOT NULL is

not allowed, or a null host variable value is not allowed in

a SELECT list.

+408

An UPDATE or INSERT of a data value is incompatible

with the data type of the associated target column.

+414

The LIKE clause was used for a numeric or date/time

column type. LIKE must only be used with character or

graphic compatible columns.

+415

The corresponding columns, n, of the operand of a

UNION or a UNION ALL do not have comparable

column descriptions.

+416

You cannot specify a long string column in the SELECT

clause of a UNION.

+419

The precision of the numerator and/or the scale of the

denominator are too large for decimal division.

+421

A hexadecimal literal associated with a graphic compatible

column in a predicate cannot have an odd length.

+551

01548

User xxxxxx does not have the yyyyyy privilege.

+552

01542

xxxxxx is not authorized to perform this statement.

+668

Table xxxxxx is inactive and you cannot access it.

Appendix H. Incompatibilities Between Releases

443

V3R3

and V3R2 Incompatibilities (VM Only)

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

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

444

SQL Reference

In V3R3, SQLCODE -180 (SQLSTATE 22007) is generated under the above

condition.

5. 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.

10.

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.)

11.

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;

Appendix H. Incompatibilities Between Releases

445

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

“SYSVIEWS” on page 406 under V3R1 and V2R2 Incompatibilities.

12.

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.

13.

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.

14.

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.

15.

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.

16.

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.

446

SQL Reference

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.

17.

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.

18.

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.

19.

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.

20.

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” on page 449 for a list of

these codes, along with their old and new SQLSTATEs.

System Environment

21. 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).

22. Setting of CHARNAME

Appendix H. Incompatibilities Between Releases

447

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.

23.

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:

v Ensure that they can accommodate 31-bit addressing mode

v Operate the database machine in 370 mode

v 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.

24.

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.

25.

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

448

SQL Reference

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.

26. 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.

Detailed Notes on V3R3-V3R2 Incompatibilities

1. SQLSTATE Changes

These changes are shown in the following table.

Old

New

SQLCODE

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.

Appendix H. Incompatibilities Between Releases

449

Old

New

SQLCODE

SQLSTATE

DESCRIPTION

-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.

V3R4 and V3R3 Incompatibilities (VM Only)

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

1. 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.

450

SQL Reference

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

2. 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.

3. 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

4. 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:

v file-name.field-name

v 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.

5. 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:

v 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.

v If the structure contained no subfields, the database manager rejected the

reference, generating an error message.

In V3R4:

v 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.

Appendix H. Incompatibilities Between Releases

451

v 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:

v C programs: C-NUL string of length 9

v 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” on page 454 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

452

SQL Reference

-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.

10. 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

11.

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.

12.

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.

13.

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.

14.

Enhancement to COLDLOG

Appendix H. Incompatibilities Between Releases

453

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.

Detailed Notes on V3R4-V3R3 Incompatibilities

Data Types of Parameter Markers in Predicates

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.

V3R4

and V3R2

Incompatibilities (VSE Only)

SQL and Data

1. 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.

454

SQL Reference

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

Appendix H. Incompatibilities Between Releases

455

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.

10.

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.

11.

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.)

12.

SQL Statement String

456

SQL Reference

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

“SYSVIEWS” on page 406 under V3R1 and V2R2 Incompatibilities.

13.

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.

14.

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.

15.

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

16. 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.

Appendix H. Incompatibilities Between Releases

457

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.

17.

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.

18.

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.

19.

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.

20.

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.

21.

Use of Host Variables in CONNECT Statement

458

SQL Reference

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:

v C programs: C-NUL string of length 9

v 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.

22.

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” on page 462 for some

examples and further discussion.

23.

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.

24.

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

Appendix H. Incompatibilities Between Releases

459

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.

25. 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” on page 462 for a list of

these codes, along with their old and new SQLSTATEs.

System Environment

26.

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).

27.

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.

28.

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:

v Ensure that they can accommodate 31-bit addressing mode

v 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.

29.

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.

460

SQL Reference

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.

30.

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.

31.

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.

Appendix H. Incompatibilities Between Releases

461

32. 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.

33. 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.

Detailed Notes on V3R4-V3R2 Incompatibilities

Data Types of Parameter Markers in Predicates

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.

462

SQL Reference

2. SQLSTATE Changes

These changes are shown in the following table.

Old

New

SQLCODE

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.

V3R5 and V3R4 Incompatibilities

1. SQL/DS Database Archive Incompatibilities

Archives that were created on prior releases of SQL/DS cannot be restored by

the SQL/DS V3R5 database manager. If this is attempted, the database manager

will issue message ARI2038E and terminate. See the DB2 Server for VM Messages

and Codes or DB2 Server for VSE Messages and Codes manual for more details on

this message.

Appendix H. Incompatibilities Between Releases

463

2. SQL/DS VSAM Shareoptions Changes under VSE

In prior releases of SQL/DS (VSE), the VSAM SQL/DS directory, data and log

data sets were defined with SHAREOPTIONS(1). In SQL/DS V3R5, these

VSAM files must now be defined with SHAREOPTIONS(2).

3. SQLSTATE Values Changes

Many SQLSTATE values have changed in SQL/DS V3R5. The new SQLSTATE

values and their former values can be found in the DB2 Server for VM Messages

and Codes or DB2 Server for VSE Messages and Codes manuals. Changing

SQLSTATEs is an incompatible change since many SQLSTATE values that are

returned from diagnostic situations will be different from previous releases of

SQL/DS. Application programmers should review any programs that use

SQLSTATE in the SQLCA each time an SQL statement is executed.

4. Messages and Codes Changes

Some SQL/DS messages and codes have changed, and some new ones have

been added in SQL/DS V3R5. See the DB2 Server for VM Messages and Codes

and DB2 Server for VSE Messages and Codes manuals for details.

5. Display CICS Information on SHOW CONNECT

If the package that the connected user is running was created in SQL/DS

Version 2 Release 2 or earlier, the CICS information will not be displayed by

the SHOW CONNECT command because the RDIIN for V2R2 or earlier does

not contain the RDIIN extension area. The package must be reprepped with

SQL/DS V3R5 and recompiled to make the CICS information available.

V5R1

and V3R5 Incompatibilities

1. Messages and Codes Changes

Many messages and codes have changed, and some new ones have been added

in DB2 Server for VSE & VM Version 5 Release 1. See the DB2 Server for VM

Messages and Codes and DB2 Server for VSE Messages and Codes manuals.

2. DB2 Database Archive Incompatibilities

Archives that were created on prior releases cannot be restored by the DB2

Server for VSE & VM Version 5 Release 1 database manager. If this is

attempted the database manager will issue message ARI2038E and terminate.

See the DB2 Server for VM Messages and Codes and DB2 Server for VSE Messages

and Codes manuals for more details on this message.

3. DBSU

If you use R350 DBSU to unload and reload a table in a R510 database, the

value of the DATACAPTURE column will be lost.

4. Date/Time Exits and Field Procedures

VM Users with Date/Time or Field Procedure Exits that are dependant on

running in a 370 Mode virtual machine must convert to execute in a ESA mode

virtual machine. Note that exits requiring AMODE=24 are not affected, as we

still support running the Server code in AMODE=24. The above also applies to

Single User Mode application programs. The above also applies to Vendor

programs that run on the Server, such as database monitoring or tape mount

handling programs.

V6R1

and V5R1 Incompatibilities

1. Running the Database Server in 24-bit Addressing Mode (VM)

464

SQL Reference

With Version 7 Release 5 the RDS component is linkedited with the AMODE

ANY option, instead of AMODE 24. This allows RDS to be loaded and

executed above the 16 MB line. This will free up valuable storage below the 16

MB line. However, if you use the AMODE(24) parameter, then RDS cannot be

executed above the line. If this is attempted, a program check will occur at start

up time.

To avoid this, you must use a maximum virtual storage size of 16MB which

will force RDS to be loaded below the line. If you need to run with

AMODE(24) all of the time, you should create an RDS saved segment that

resides below the 16MB line. If you only use AMODE(24) some of the time,

such as with some single user mode applications, you can create an alternate

bootstrap package which specifies an alternate RDS saved segment which

resides below the 16MB line, or specifies that RDS is run from free storage.

The AMODE parameter value is saved in the ″resid SQLDBN Q″ file. See the

DB2 Server for VM System Administration or DB2 Server for VSE System

Administration manual for details on the AMODE parameter and saved

segments.

Exploiting RDS above the 16 Megabyte Line

With Version 7 Release 5, the RDS component is linkedited with the ″RMODE

ANY″ option. This allows RDS to be loaded and executed above the 16MB line.

This will free up valuable storage below the 16 MB line. As the RDS code will

be loaded above the 16MB line before other storage is allocated, extremely

storage constrained systems may need to increase their partition size to

maximize their below the 16MB line free storage.

DBNAME Directory format change

The format of the DBNAME directory source member, ARISDIRD, has been

changed to support DRDA Online Requester support.

V7R1

and V6R1 Incompatibilities

There are no incompatibilities between DB2 Server for VM V6R1 and DB2 Server

for VM V7R1.

DB2 Server for VSE only:

1. DBNAME Directory format change

ARISDIRD has been restructured to improve readability and flexibility. Each

DBNAME entry is now defined explicitly by its type (Local, Remote or Host

VM (Guest Sharing)). CICS AXE Transaction TPNs (Transaction Program

Names) are still included in the directory as a type of ’LOCALAXE’. The

DBNAME Directory Builder program, ARICBDID has been rewritten as a

REXX/VSE procedure with extensive error and dependency checking. Support

for TCP/IP information is added and ’alias’ DBNAMEs are supported. ALL

DBNAMEs must be specified in the new DBNAME Directory, including the

Product Default DBNAME ″SQLDS″. A migration REXX/VSE procedure,

ARICCDID, is provided to assist in migrating to the new format.

V7R2

and V7R1 Incompatibilities

There are no incompatibilities between DB2 Server for VM V7R2 and DB2 Server

for VM V7R1.

There are no incompatibilities between DB2 Server for VSE V7R2 and DB2 Server

for VSE V7R1.

Appendix H. Incompatibilities Between Releases

465

Notices

IBM may not offer the products, services, or features discussed in this document in

all countries. Consult your local IBM representative for information on the

products and services currently available in your area. Any reference to an IBM

product, program, or service is not intended to state or imply that only that IBM

product, program, or service may be used. Any functionally equivalent product,

program, or service that does not infringe any IBM intellectual property right may

be used instead. However, it is the user’s responsibility to evaluate and verify the

operation of any non-IBM product, program, or service.

IBM may have patents or pending patent applications covering subject matter

described in this document. The furnishing of this document does not give you

any license to these patents. You can send license inquiries, in writing, to:

IBM Director of Licensing

IBM Corporation

North Castle Drive

Armonk, NY 10594-1785

U.S.A.

For license inquiries regarding double-byte (DBCS) information, contact the IBM

Intellectual Property Department in your country or send inquiries, in writing, to:

IBM World Trade Asia Corporation

Licensing

2-31 Roppongi 3-chome, Minato-ku

Tokyo 106, Japan

The following paragraph does not apply to the United Kingdom or any other

country where such provisions are inconsistent with local law:

INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS

PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER

EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED

WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS

FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or

implied warranties in certain transactions, therefore, this statement may not apply

to you.

This information could include technical inaccuracies or typographical errors.

Changes are periodically made to the information herein; these changes will be

incorporated in new editions of the publication. IBM may make improvements

and/or changes in the product(s) and/or the program(s) described in this

publication at any time without notice.

Any references in this information to non-IBM Web sites are provided for

convenience only and do not in any manner serve as an endorsement of those Web

sites. The materials at those Web sites are not part of the materials for this IBM

product and use of those Web sites is at your own risk.

IBM may use or distribute any of the information you supply in any way it

believes appropriate without incurring any obligation to you.

467

Licensees of this program who wish to have information about it for the purpose

of enabling: (i) the exchange of information between independently created

programs and other programs (including this one) and (ii) the mutual use of the

information which has been exchanged, should contact:

IBM Corporation

Mail Station P300

522 South Road

Poughkeepsie, NY 12601-5400

U.S.A

Such information may be available, subject to appropriate terms and conditions,

including in some cases, payment of a fee.

The licensed program described in this information and all licensed material

available for it are provided by IBM under terms of the IBM Customer Agreement,

IBM International Program License Agreement, or any equivalent agreement

between us.

Any performance data contained herein was determined in a controlled

environment. Therefore, the results obtained in other operating environments may

vary significantly. Some measurements may have been made on development-level

systems and there is no guarantee that these measurements will be the same on

generally available systems. Furthermore, some measurement may have been

estimated through extrapolation. Actual results may vary. Users of this document

should verify the applicable data for their specific environment.

Information concerning non-IBM products was obtained from the suppliers of

those products, their published announcements, or other publicly available sources.

IBM has not tested those products and cannot confirm the accuracy of

performance, compatibility, or any other claims related to non-IBM products.

Questions on the capabilities of non-IBM products should be addressed to the

suppliers of those products.

All statements regarding IBM’s future direction or intent are subject to change or

withdrawal without notice, and represent goals and objectives only.

This information may contain examples of data and reports used in daily business

operations. To illustrate them as completely as possible, the examples include the

names of individuals, companies, brands, and products. All of these names are

fictitious and any similarity to the names and addresses used by an actual business

enterprise is entirely coincidental.

This information may contain sample application programs in source language,

which illustrates programming techniques on various operating platforms. You

may copy, modify, and distribute these sample programs in any form without

payment to IBM, for the purposes of developing, using, marketing, or distributing

application programs conforming to the application programming interface for the

operating platform for which the sample programs are written. These examples

have not been thoroughly tested under all conditions. IBM, therefore, cannot

guarantee or imply reliability, serviceability, or function of these programs.

468

SQL Reference

Programming Interface Information

This book documents intended Programming Interfaces that allow the customer to

write programs to obtain services of DB2 Server for VSE & VM.

Trademarks

The following terms are trademarks of International Business Machines

Corporation in the United States, or other countries, or both:

AIX

APL2

C/370

CICS

CICS/ESA

CICS/VSE

CUA

DATABASE 2

DataPropagator

DB2

DFSMS/VM

DFSORT

Distributed Relational Database Architecture

DRDA

Enterprise Systems Architecture/390

IBM

Information Warehouse

Language Environment

MVS

Operating System/2

Operating System/400

OS/2

OS/400

QMF

RACF

S/390

SAA

SystemView

System/390

VM/ESA

VSE/ESA

VTAM

Microsoft, Windows, Windows NT, and the Windows logo are trademarks of

Microsoft Corporation in the United States, other countries, or both.

Unix and Unix-based trademarks and logos are trademarks or registered

trademarks of The Open Group.

Other company, product, and service names may be trademarks or service marks

of others.

Notices

469

DB2 Server for VSE & VM SQL Reference (Version 7 Release 5) - page 12