Client Experiences: How DB2 Migration Affects Decimal Type Conversion

Recently, I assisted a client migrating their DB2 databases from v9.7 to v11.1.

I intentionally say “migrating” and not “upgrading”, even though three major releases were jumped, because the existing databases were located on Z-Linux servers (big endian) and the target databases were to be placed on POWER Linux servers (little endian), so it was more of a migration scenario than a classical DB2 upgrade.
Because of a different endian-ness of the source and target platforms, a backup/restore approach was not possible, but export/load was used instead.

After the first test-pilot database was migrated to its new POWER server and released to the testing team, they quickly noticed a discrepancy between the old and the new environment when executing a simple query that included a conversion of the Decimal type into the Character type: (click images to enlarge)

On DB2 v9.7, the result was:

On DB2 v11.1, the result was:

Even though the result was mathematically correct, the application logic required the leading zeros in order for the presentation layer to work correctly, so we needed to fix this as soon as possible.

As it turned out, this problem had more than one solution to it:

The first solution, quick and dirty, was to update the original SQL statement(s) to something like this:

Or, somewhat better:

(from the KC: “Leading zeros and trailing decimal characters are also included in the result of the CHAR_OLD scalar function, which has the same syntax as the CHAR function.”)

Quick and dirty, as I said, but also more difficult to read such SQL statements later on. Not to mention the fact that the application had more than one such statement and hunting them all down is not always easy and bullet-proof (so not really a “quick” solution after all). Also, whenever a new SQL statement is added to the application logic, the person doing it must remember to use the ghastly string of built-in functions shown above in order to get the DEC-to-CHAR conversion right.

Another, much simpler solution was to update the database configuration:

which out-of-the-box enables the old (v95) conversion format from DEC to CHAR that returns the leading zeros.

One thing to keep in mind with this approach, however, is that a database restart is required for the change to take effect.

More information can be found at the IBM DB2 Knowledge Centre

Needless to say, the client decided to go with the second solution, thus avoiding any recompilations and redeployment of the application code. The extra database restart was added to the migration plan and the subsequent migrations went smoothly!

• Share