IBM Books

Administration Guide


SQL

Updating Partitioning Key Columns


UNIX OS/2 WIN DB2 PE

Change

In DB2 PE Version 1.2, partitioning key columns could be updated if the table was in a single-node nodegroup. In DB2 Version 5, partitioning key columns can be updated if the table is in a table space in a single-node nodegroup, and there is no partitioning key defined.

Symptom

An update statement fails with SQL270N (SQLCODE -270, SQLSTATE 42997) with reason code 2. The same error is returned if the table is in a table space in a single or multiple node nodegroup.

Resolution

If the table is in a table space in a single node nodegroup, then use the ALTER TABLE statement to DROP the partitioning key. As with DB2 PE Version 1.2, if the table is in a table space in a multiple node nodegroup, the nodegroup must be changed to a single-node nodegroup and REDISTRIBUTE NODEGROUP must be issued before attempting to update partitioning key columns.

Column NGNAME


UNIX OS/2 WIN DB2 PE

Change

In DB2 PE Version 1.2, a table was directly associated with a nodegroup. In DB2 Version 5, a table is in a table space, which is within a nodegroup. Since there is no longer a direct relationship with a nodegroup, there is no need for a column, named NGNAME in the SYSIBM.SYSTABLES catalog table.

Symptom

An SQL statement that refers to the NGNAME column from SYSIBM.SYSTABLES catalog table will return an SQLCODE of -206 (SQLSTATE 42703).

Resolution

Remove the column NGNAME from the SQL statement. To determine the nodegroup name for the table, refer to NGNAME in the row of SYSCAT.TABLESPACES catalog view, that relates to the table space in which the table is stored.

Node Number Temporary Space Usage


UNIX OS/2 WIN DB2 PE

Change

When using a temporary table that requires row identifiers, the amount of space needed is increased to include the node number. The space limit for temporary tables is 4005 bytes. If temporary tables are close to the 4005 byte limit, any further increase can exceed this limit.

Symptom

There are two possible symptoms of this change.

Resolution

You should review and use the directions in the Actions section of the message details for SQL0670N to fix the error.

Authorities for Create and Drop Nodegroups


UNIX OS/2 WIN DB2 PE

Change

The authorization required for creating or dropping a nodegroup has changed from SYSADM or DBADM to SYSADM or SYSCTRL. This means that a user ID with DBADM authority cannot create, alter, or drop nodegroups.

Symptom

A user ID, with DBADM authority, issuing a CREATE NODEGROUP or DROP NODEGROUP statement will receive an SQL00551N (SQLSTATE 42501).

Resolution

Issue the statement using a user ID that has SYSADM or SYSCTRL authority. For your convenience, you may wish to include the user ID in the SYSCTRL group. Refer to the Administration Guide for further information.

Target Map in REDISTRIBUTE NODEGROUP


UNIX OS/2 WIN DB2 PE

Change

The specification of a target map in the REDISTRIBUTE NODEGROUP command or API no longer causes database partitions to be implicitly added or dropped from the node group. This means that the target map cannot include nodes that are not defined to the node group. An undefined node that is included in the target map file will cause an error to be returned. A database partition, which has been defined to the node group, can be excluded from the target map file and will not appear in the partition map.

Symptom

If a node is included in the target map file and was not defined to the node group, the REDISTRIBUTE NODEGROUP command will return an SQLCODE-6053 with a reason code 6.

Resolution

Before issuing the REDISTRIBUTE NODEGROUP command, add the database partition to the node group, using the ALTER NODEGROUP statement. You can also drop the node from the node group using the ALTER NODEGROUP statement, either before or after issuing the REDISTRIBUTE NODEGROUP command. Refer to the SQL Reference for further information on the ALTER NODEGROUP statement.

Node Group for Create Table


UNIX OS/2 WIN DB2 PE

Change

In DB2 PE Version 1, a table was directly associated with a node group. In DB2 Version 5, a table is in a table space within a node group. When a user issues a CREATE TABLE statement, the name following the IN keyword is a table space name, not a node group name. The default table space selected may not be defined in the IBMDEFAULTGROUP node group, which was the default node group in DB2 PE Version 1.

Symptom

If you use existing CREATE TABLE statements from DB2 PE Version 1, they may fail with an SQLCODE of SQL0204N (SQLSTATE 42704), with the name specified following the IN keyword in the message. This will occur if a table space with the same name as the node group has not been automatically created during database migration.

If you are using CREATE TABLE statements that do not specify the IN keyword, the table space selected, by default, may not be using the node group, IBMDEFAULTGROUP, and will not include data on all the database nodes. You can check the partition map for the table to confirm this.

Resolution

Ensure that any name specified following the IN keyword on the CREATE TABLE statement is the name of a defined table space. For existing statements, you could set up a table space for each node group with the same name.

To ensure that tables default to the IBMDEFAULTGROUP for all users, define a table space called IBMDEFAULTGROUP, defined in the node group, IBMDEFAULTGROUP. This ensures that tables created by any users will default to use this table space.
Note:This is done automatically during database migration from DB2 PE Version 1 to DB2 Version 5.

Revoking CONTROL on Tables or Views


UNIX OS/2 WIN DB2 PE

Change

A user can grant privileges on a table or view using the CONTROL privilege. In DB2 Version 5, the WITH GRANT OPTION provides a mechanism to determine a user's authorization to grant privileges on tables and views to other users. This mechanism is used in place of CONTROL to determine whether a user may grant privileges to others. When CONTROL is revoked, users will continue to be able to grant privileges to others.

Symptom

A user can still grant privileges on tables or views, following the revocation of CONTROL privilege.

Resolution

If a user should no longer be authorized to grant privileges on tables or views to others, revoke all privileges on the table or view and grant only those required.

High Level Qualifiers for Objects in DB2 Version 5


UNIX OS/2 WIN DB2 PE

Change

In DB2 PE Version 1, users would create a table, view, index or package with any schema name or qualifier with the exception of SYSIBM. This differs from other IBM database products and is not compliant with SQL92. In DB2 Version 5, there are limits of the schema names that you can use.

Symptom

If you create an object with an invalid schema name, the CREATE statement returns an SQLCODE of SQL0553N. This message indicates that the object cannot be created with the schema name.

If a CREATE, ALTER, COMMENT ON or DROP statement returns an SQLCODE of SQL0551N, you did not have the necessary privilege. This may be the result of schema-related privileges and could indicate that:

Resolution

Depending on the symptom:

Inoperative VIEWs


UNIX OS/2 WIN

Change

In DB2 Version 2, a view is made inoperative if a SELECT privilege upon which the view definition is dependent is revoked or if an object upon which the view definition is dependent was dropped (or possibly made inoperative in the case of another view). This is in contrast to the behavior in DB2 Version 1 where the view would have been dropped under the same circumstances.

Symptom

If the use of an inoperative VIEW is attempted, an SQL0575N will be returned to the application.

Resolution

To resolve this problem, you will need to do two things:

  1. Resolve the dependency (such as CREATE the dropped table).

  2. Execute a CREATE VIEW.

    Since the view is only inoperative and not dropped, you can query the TEXT column of SYSCAT.VIEWS to retrieve the current definition of the view.

Unusable VIEWs


UNIX OS/2 WIN

Change

If you currently have a view defined with SELECT * on a table as part of the view definition, the view may be unusable after migration.

Symptom

You will receive an SQL0158N error if you attempt to use a view that is unusable.

Resolution

In order to resolve this problem you will need to:

  1. Drop the existing view (DROP VIEW command).
  2. Re-create the view (CREATE VIEW command), specifying column names in place of "*".

SQLCODE Changes


UNIX OS/2 WIN

Change

The SQLCODEs returned for an INSERT or UPDATE statement resulting in data being out of range have changed. These are:

The message has changed from "A numeric value/string in the UPDATE or INSERT statement is ..." to "Overflow occurred during numeric data type conversion". Note that there have been no changes to the corresponding SQLSTATEs.

Symptom

These SQLCODEs are caused by trying to place a value in a column that is outside a limit that exists on the data in that column. For applications, different values will now be returned in SQLCA.SQLCODE. In any interactive situation (such as using the command line processor), a different error code will be reported to the user.

Resolution

If your application specifically looks for the old SQLCODEs, you will need to change the comparison to use the new codes.

WITH CHECK OPTION on CREATE VIEW


UNIX OS/2 WIN

Change

The default used when WITH CHECK OPTION is specified without keywords has changed from LOCAL in Version 1 to CASCADED in Version 2.

Symptom

This will cause the constraints of all dependant views to be applied.

Resolution

Explicitly specify the LOCAL keyword with the WITH CHECK OPTION to get the same behavior as in Version 1.

SQLSTATE Changes


UNIX OS/2 WIN

Change

With DB2 Version 2, the SQLSTATEs have been updated to comply with the final published SQL92 standard.

Symptom

In some cases, the value of SQLCA.SQLSTATE will be different than it would be in Version 1 for the same error or situation.

Resolution

If your application is expecting a specific SQLSTATE, you may need to update the value in the comparison.

FOR BIT DATA Comparisons


UNIX OS/2 WIN

Change

In Version 1, all character strings, including FOR BIT DATA, were compared according to the database collating sequence. In Version 2, character strings with the FOR BIT DATA attribute will be compared according to their bit values, irrespective of the database collating sequence.

Whenever the database manager compares two character strings, if either comparand has the FOR BIT DATA attribute, the comparison is performed with the bit values of the comparands, without consideration of the database collating sequence. If the comparands are of different lengths, there is a logical blank padding (with X'20' on the right) of the shorter string to the length of the longer string.

Symptom

Comparison results will differ from results in Version 1 when the collating sequence and the bit values are in different orders (only for FOR BIT DATA columns). For example, 'A' = x'41' and 'a' = x'61'. 'A' > 'a', however, x'41' < x'61'.

Keep in mind that comparisons take place in many situations including:

Resolution

You should replicate the data from the FOR BIT DATA column to a column with type CHAR. This will allow the data to be sorted according to the collating sequence instead of their bit values.

Code Page Conversion


UNIX OS/2 WIN

Change

Code page conversion rules for operands changed in Version 2. These changes improve DB2 compliance with SQL92 standards. It is important to understand that in most cases this will not affect result sets, however, it is possible to find scenarios where output would be different from DB2 Version 1 to Version 2 or to Version 5. In these cases, the output in Version 1 would be the incorrect output from the standpoint of the SQL92 standards compliance.

A few scenarios will be discussed where different output may be experienced:

Symptom

The result set may be different since DB2 now adheres to SQL92 standards.

Resolution

There is no resolution as this is an improvement for compliance with SQL92 standards.

Isolation Levels and Blocking All


UNIX OS/2 WIN

Change

When a cursor is declared without either the FOR UPDATE or FOR READ ONLY clause, it is considered to be an ambiguous cursor. If a package containing dynamically declared cursors is bound with the bind option BLOCKING=ALL, but without the bind option LANGLVL=MIA, then any ambiguous cursors will be treated as if FOR READ ONLY had been specified.

Symptom

Your application may receive an SQLCODE of SQL0510N (SQLSTATE 42828) when performing a DELETE WHERE CURRENT OF CURSOR.

Resolution

Rebind with the BLOCKING=UNAMBIG or LANGLVL=MIA options or add a FOR UPDATE clause to the cursor.

ORDER BY Temporary Space Usage


UNIX OS/2 WIN

Change

Whenever an ORDER BY is performed on a column which does not have an index, a temporary table is used to perform the sort. Beginning in Version 2, LONG VARCHAR and LONG VARGRAPHIC column types will use an increased amount of space as compared to Version 1 in these temporary tables. This may cause the query result rows to exceed the maximum row size (4005 bytes).

Symptom

ORDER BY queries with one or more LONG VARCHAR (or LONG VARGRAPHIC) columns in the SELECT list and for which the select list is physically large, may fail to execute in Version 2 with SQLCODE SQL0670N (SQLSTATE 54010).

Resolution

The following are some ways of attempting to resolve or avoid this scenario:

Using Quotes in SQL Statements


UNIX OS/2 WIN

Change

A defect in previous versions of DB2 allowed double quotes to be used in SQL statements as delimiters of some keywords and operators. For instance, though this is unpredictable, a query of the form SELECT C1 "FROM" T1 was processed as if the FROM was not delimited.

Beginning in Version 2, this behaviour has been corrected.

Symptom

SQL statements which incorrectly use double quotes to delimit keywords or operators will return errors during statement parsing.

Resolution

The statement syntax should be changed to removed the unnecessary double quotes. For static SQL, if the application source code is unavailable, bind files can be carefully edited to remove the unnessary quotes from the statements. Note that SQL identifiers may require the use of double quotes (these are called delimited identifiers).


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]