DB2 & IMS

Migration Challenges

6 Pages

Once job DSNTIJNE has completed, you’re a step away from full DB2 V8 NFM. All catalog conversion is now complete and all V8 executable code is in place. The last step is to run job DSNTIJNF (New Function). This will make available all DB2 V8 functions that were delivered with the product. (A few other steps are needed to allow applications to use these functions, including binding plans and packages with NEWFUN(YES).)

Transition Mode Issues

Most new issues that arise in this mode center around the catalog changes. Many columns that were once EBCDIC and fixed length are now in Unicode and defined as VARCHAR(128). So, you must be careful when coding SQL statements that access these catalog tables.

Most of the conversions necessary will occur automatically. For example, an SQL Select against SYSTABLES in SPUFI may appear to display EBCDIC data (rather than Unicode). This will depend on the CCSID settings in DSNHDECP, how you’ve bound the SPUFI plan (or package), and the code page setting for your terminal emulator.

Another set of issues revolves around certain characters that may not be converted correctly. Some shops have experienced difficulty binding SQL statements containing the caret (^) character, which can be used as a “not” operator in operations on expressions such as:

WHERE T1.COL1 ^= ‘1’

Here the caret with the equals sign is the not equals operator.

Another related issue is accessing Unicode data that may contain multibyte characters. Functions such as SUBSTR and LENGTH are byte-based, not character-based. They function on strings by byte position. Since Unicode strings may have multi-byte characters, however, use of SUBSTR and LENGTH may produce incorrect results.

You probably have no catalog tables containing object names (table names, column names, etc.) that, when converted to Unicode, result in a multi-byte character. Still, it’s possible. In any case, with the advent of Unicode data, you should move to the use of character-based functions such as SUBSTRING.

Here’s an example:

6 Pages