SYSTABLEPART.LIMITKEY format variations in DB2 11

Content

The following text is added to the list of application programming and SQL incompatibilities for migration to DB2 11 for z/OS:

SYSTABLEPART.LIMITKEY format variations

Explanation
Starting in DB2 11 conversion mode, the LIMITKEY column of the SYSTABLEPART catalog table can contain a mix of differently formatted values:

• Date and time values are delimited by single quotation marks (for example, '2001-01-01'). However, values that were added in releases prior to DB2 11 do not contain the delimiters.
• When the decimal point indicator is comma, a space follows any comma delimiter in the value. The comma decimal point indicator is used when the DECIMAL POINT IS field setting is , (comma) or a COBOL program that executes the ALTER statement uses the COMMA processing option. No space follows comma delimiters when period decimal point indicators are used, or for values added in releases prior to DB2 11.

Possible impact to your DB2 environment
Applications that do not tolerate the format variations might fail.

Actions to take
Modify any applications that use the LIMITKEY column to tolerate the format variations.

For example, to remove the single-quote delimiters for date and time values, from a single column in a partitioning key, you might use the following SQL statements:
SELECT DATE(STRIP(LIMITKEY,B,X'27'))  FROM SYSIBM.SYSTABLEPART WHERE …;
SELECT TIME(STRIP(LIMITKEY,B,X'27'))  FROM SYSIBM.SYSTABLEPART WHERE …;

If (STRIP(LIMITKEY,B,X'27'))is omitted from the statements, DB2 issues SQLCODE -180.

The use type of the column is also changed to S, which indicates a product-sensitive programming interface. It is expected that programs written to such interfaces might need to be changed in order to run with new product releases or versions, or as a result of service.