SQL Reference

Application Programming

VARCHAR Data Type in DB2 Universal Database Version 6

Change:  

Maximum possible size of VARCHAR (VARGRAPHIC) data type has increased from 4 000 characters (2 000 double bytes characters), to 32 672 characters (16 336 double bytes characters) in Version 6.

Symptom:  

An application that uses fixed length buffers of 4 000 bytes for a VARCHAR (VARGRAPHIC) data type has the potential for buffer overwrite or truncation, if it fetches a VARCHAR field which is longer than 4 000 bytes into a buffer that is too small. The CLI function - SQLGetTypeInfo() now returns the size of VARCHAR as 32 672. CLI applications that use this value in table DDLs may get errors due to sufficient page size table spaces not being available.

Resolution:  

The application should be coded, in the recommended manner, of first describing the columns of the result set by using the DESCRIBE statement, and then using buffers whose size is based on the data type's length as returned from the DESCRIBE of the column.

Java Programming Positioned UPDATE and DELETE in DB2 Universal Database Version 6

Change:  

When programming using Java in Version 6, positioned UPDATE and DELETE statements use the default authorization identifier of the person that bound the cursor package. This is different from Version 5.2 where the authorization identifier of the person running the package was used.

Symptom:  

The package containing the positioned UPDATE and DELETE statements may not run because the authorization identifier of the person who bound the package does not have sufficient authority.

Resolution:  

The authorization identifier of the person who binds the package must be granted sufficient authority to run the positioned UPDATE and DELETE statements found in the package. Grant the correct privileges and then re-bind the package.

Syntax Change in FOR UPDATE Clause in DB2 Universal Database Version 6

Change:  

In Version 5.2, in an SQLJ program, the FOR UPDATE clause can be used in a SELECT statement to identify the columns that can be updated in subsequent positioned UPDATE statements. The syntax is changed for Version 6.

Symptom:  

You will receive the error message SQJ0204E if a SELECT statement contains a FOR UPDATE clause.

Resolution:  

Remove the FOR UPDATE clause from the SELECT statement. Specify an updatable iterator through the iterator declaration clause. For example:

   #sql public iterator DelByName implements sqlj.runtime.ForUpdate(String EmpNo)
      with updateColumns = (salary);

If you want to explicitly identify what columns are updateable, specify them through the updateColumns keyword, used in conjunction with the WITH clause.

Refer to Application Development Guide for more information on positioned iterator declarations.

Character Name Sizes in DB2 Universal Database Version 6

Change:  

DB2 Universal Database Version 6 supports 128 byte table/view/alias names and 30 byte column names. The former support was for 18 byte names for each of these entities.

USER and CURRENT SCHEMA special registers were CHAR(8) and are now VARCHAR(128). The CURRENT EXPLAIN MODE special register was CHAR(8) and is now VARCHAR(254). The output for TYPE_SCHEMA and TABLE_SCHEMA built-in functions were CHAR(8) and are now VARCHAR(128).

Symptom:  

If applications that were developed before Version 6 are run against a Version 6 database that does not utilize the longer limits, then the application behavior should not change at all. However, running these applications against a Version 6 database that does utilize longer names, could result in certain side effects, depending on how these applications were coded.

Here are some examples:

• Consider an existing application that FETCHes a table or column name (typically from a catalog view) into a host variable that was defined to be 18 bytes long. Since 18 bytes was the limit on the size of the table or column name until Version 6, this application may not bother to check the sqlwarn1 bit of the SQLCA, assuming that there should never be truncation. This application may proceed with subsequent logic, assuming that no truncation has occurred whereas truncation actually did occur.

• Consider an application that FETCHes a table or column name (typically from a catalog view) into an SQLDA where the size of the sqldata field was allocated based on the sqllen field from a DESCRIBE of the SELECT. This will result in the correct (untruncated) result being returned to the application even though the size of the table/column names may have increased. If other application logic relies on the assumption that column names or lengths are limited to 18 bytes, then the longer names that have been returned may introduce unintended behavior in the application. As a simple example, the display of a longer column name may be truncated at 18 bytes.

• Since the SQLCA token field (sqlerrmc) is limited to 70 bytes, this may affect existing applications that attempt to insert a row into a table. In the event of the error message SQL0204N, this application determines the name of the table from the SQLCA sqlerrmc field and then performs some operations based on that object name. Whereas with earlier versions of DB2, the table/schema identifier limit guaranteed that the table name was included in the SQLCA in its entirety (as in the case with the error message SQL0204N), this is not the case with Version 6.

• An application using a downlevel API (such as LIST HISTORY, or GET SNAPSHOT) will only get the first 18 bytes of a table name.

• Existing CLI and ODBC applications that use the schema functions (such as SQLTables(), or SQLColumns(), and others) will be affected if connecting to a server with support for greater than 18 byte names. Although there will be truncation warnings, the application may not check for this warning and may proceed with a truncated name.

Resolution:  

The best way to resolve problems of this type is to re-code the application to handle longer table and column names. Otherwise, ensure that these applications are not run against Version 6 databases that use > 18 byte names.

PC/IXF Format Changes in DB2 Universal Database Version 6

Change:  

DB2 Universal Database Version 6 supports 128 byte table/view/alias names and 30 byte column names. The former support was for 18 byte names for each of these entities.

Symptom:  

A DB2 Universal Database Version 5 client is not able to import a PC/IXF that was exported by a DB2 Universal Database Version 6 export client. The error message SQL3059N will result.

Also, a PC/IXF file (an export from a DB2 Universal Database Version 6 client) cannot be loaded into a DB2 Universal Database Version 5 database. The error message SQL3059N will result.

Resolution:  

Always be aware of the version level of the PC/IXF file when running.

SQLNAME in a Non-doubled SQLVAR in DB2 Universal Database Version 6

Change:  

DB2 Universal Database Version 6 supports 30 byte column names. The former support was for 18 byte names. In Version 5, the documented behavior was that "0xFF" is placed in the 30th byte of an SQLNAME field for a non-doubled SQLVAR. Also in Version 5, for system-generated names and for user-specified column names specified in an "AS" clause, "0x00" is placed in the 30th byte.

In Version 6, the new behavior only returns "0xFF" in the 30th column if the name is system-generated.

Symptom:  

Any applications that rely on the 30th-byte of the SQLNAME to determine whether it is a user-specified column name or a system-generated name may receive unexpected logic checks if the user-specifed column name is 30 characters long. This should be a rare occurrence.

Resolution:  

These applications should be modified to only check for "0xFF" in the 30th byte of the SQLNAME field if the length of the SQLNAME is less than 30. In this case, the name is user-generated.

Obsolete DB2 CLI/ODBC Configuration Keywords in DB2 Universal Database Version 6

Change:  

When moving from version to version, you can change the behavior of the DB2 CLI/ODBC driver by specifying a set of optional keywords in the db2cli.ini file.

In Version 6, the TRANSLATEDLL and the TRANSLATEOPTION keywords became obsolete.

Symptom:  

These keywords will be ignored if they still exist. You may notice behavioral changes based on the removal of these settings.

Resolution:  

You will need to review the new list of valid parameters to decide what the appropriate keywords and settings are for your environment. See the CLI Guide and Reference for information on these keywords.

Event Monitor Output Stream Format in DB2 Universal Database Version 6

Change:  

Event monitor output streams have no version control. As a result, adding support for the greater than 18 byte table names requires the move to an output stream format.

Symptom:  

Applications that parse the event monitor output streams will no longer function as in previous releases.

Resolution:  

There are two options:

1. Update the application to use the new data stream.

2. Set the registry variable DB2OLDEVMON=evmonname1,evmonname2,... where "evmonname" is the name of the event monitor you wish to have write in the old data format. Note that any new fields in the event monitor will not be accessible in the old format.

[ DB2 List of Books | Search the DB2 Books ]