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.
- Significance of Trailing Blanks
Prior to V3R1, delimited identifiers "TABLE1" and
"TABLE1 " would be considered two different tables, and VARCHAR
values 'ABC' and 'ABC ' two different values,
where ' ' represents a blank character.
In V3R1, in the case of the table names, the system would not accept the
two tables because they now have identical names. In the case of the
VARCHAR values, they are considered equal, except in a LIKE comparison.
However, if specified at INSERT or UPDATE time, trailing blanks are included
in the varying length string data stored in the database.
If your applications must continue to treat trailing blanks as significant,
you may have to undertake some redesign. For example, prior to V3R1, if
your table had a VARCHAR column, COLX, containing
'AAA ' and you wanted to select all values from
COLX that were not equal to 'AAA', the following search condition
would satisfy this requirement, because it would return value
'AAA ' along with any other values not equal to
'AAA':
WHERE COLX <> 'AAA'
In V3R1, value 'AAA ' does not get returned
in the above example. This search condition must be redesigned in order
to get the same results as in prior releases. One solution is:
WHERE COLX NOT LIKE 'AAA'
For more discussion on migration considerations for this item, see
"Considerations for VARCHAR and VARGRAPHIC Compare" in the chapter which
discusses migrating from V2R2, in the System Administration manual, V3R1 or later.
- New Options in CREATE PROGRAM Statement
The following examples illustrate the incompatibilities that may arise when
you migrate to V3R1.
Figure 128. Legend
Figure 129. Version 2 Release 2
Figure 129 illustrates how isolation levels are determined for packages
created using extended dynamic SQL in V2R2. For example, program PROG1
contains the CREATE PROGRAM statement for package PACKA, and prepares a
section in the package. Program PROG2 subsequently executes the section
in PACKA. Since program PROG2 was preprocessed with isolation level
cursor stability (CS), the section executes using CS.
Figure 130. Version 3 Release 1
Figure 130 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.
Figure 131. Migration
Figure 131 shows packages being migrated to V3R1. In this case,
the isolation level bind option will be automatically set to USER.
Applications will notice no change in isolation level handling from previous
releases.
Figure 132. Dropping and Re-creating PACKA Without Repreprocessing PROG2
Figure 133. Re-preprocessing PROG2
Figure 132 and Figure 133 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:
- If program PROG2 is still pre-V3R1, when the section in PACKA is executed,
isolation level CS will be used.
- Otherwise, isolation level RR will be enforced whenever sections in PACKA
are executed.
- 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.
|
| +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.
|
