IBM Support

Under certain conditions, data in accelerator tables enabled for incremental updates might become inconsistent with the data in DB2 for z/OS

Troubleshooting


Problem

Under certain conditions, data in accelerator tables that were enabled for incremental updates might become inconsistent with the data that is stored in DB2 for z/OS.

Symptom

An affected table contains more rows on the accelerator than in DB2 for z/OS. The reason is that DELETE operations on the accelerator, caused by UPDATE or DELETE operations on the DB2 for z/OS tables, did not result in a deletion of rows from the accelerator.

Cause

An error in the Netezza Performance Server (NPS) software.

Environment

You are affected by this problem only if you use version 3.1.0 (since September 30, 2016 version 3.1.0 is out of service please contact your IBM support) or version 4.1PTF-1(GA) or 4.1PTF-2 of IBM DB2 Analytics Accelerator for z/OS in conjunction with the incremental update function. The problem affects all PTF levels in both product versions.

You are not affected if you use product version 2.1.0 (since September 30, 2016 version 2.1.0 is out of service please contact your IBM support)or do not use the incremental update function.

  1. A table is affected by this issue if it meets all of the following conditions:
  2. The table is enabled for incremental updates.
  3. The table was updated or rows were deleted after the table had been enabled for incremental updates. If you only inserted rows, the table is not affected.
  4. The encoding scheme of the table is EBCDIC.
  5. The table on the accelerator is organized and the key columns of the organizing key equal the key
    columns of a unique index that contains VARCHAR FOR SBCS DATA or CHAR FOR SBCS DATA columns. In connection with these key columns, one of the following conditions applies:
    a) The length of one or more of these columns is less than 8 characters.
    b) One or more of these columns contain values with a space character (0x40) as one of the first 8 characters.

Diagnosing The Problem

To identify potentially affected tables, check the DB2 catalog for the following information:

  • The encoding scheme of the table is EBCDIC (according to the ENCODING_SCHEMA column in SYSIBM.SYSTABLES).
  • The table has a unique index that contains VARCHAR FOR SBCS DATA or CHAR FOR SBCS DATA columns (according to SYSIBM.SYSINDEXES).
  • The table is accelerated (according to SYSACCEL.SYSACCELERATEDTABLES).

Thereafter, verify the incremental update status and the key columns of the organizing key for tables that match the these criteria (for example, by using IBM Data Studio or the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure).

If the incremental update status is true and the key columns of the organizing key are equal to the key columns of a unique index that contains VARCHAR FOR SBCS DATA or CHAR FOR SBCS DATA columns, the table is potentially affected by this problem.

The attached file (see end of this document) contains a sample SQL script that allows you to check tables for the criteria in 1., 3., 4. and 5.b. The last check omits an inspection of data values so that it does not take too long to complete. You must replace the variables !SCHEMA! and !ACCELERATOR_NAME! with real values and run the script by using the statement terminator '@' with auto-commit turned on.

Resolving The Problem

Fixing affected tables
If a table meets the above conditions, remove the organizing key of the table on the accelerator to prevent potential inconsistencies. After removing the organizing key, the you must fully reload the table to remove duplicate rows.

Installing the proper fix pack so that the problem does not occur anymore
The problem was solved in the following fix packs of the Netezza Performance Server (NPS) certified for use with IBM DB2 Analytics Accelerator for z/OS:

  • NPS 6.0.8.17
  • NPS7.0.2.13
  • NPS 7.0.4.5
  • NPS 7.1.0.2 P1

To find the proper fix pack for your version of IBM DB2 Analytics Accelerator for z/OS, see Netezza Database management software in the appropriate version of the following document:
Restriction: A Netezza fix pack for this problem that can be used with SMP/E is not available. To obtain such a fix pack, open a problem management record (PMR) against the Netezza Performance Server (NPS) product.

After installing the required NPS version, the organizing keys can be re-created without reloading the data.

Important: If you did not remove the organizing keys of the tables on the accelerator, a full reload of the data is required before you can re-create these keys, that is, before you complete step 2 below.

Complete the following steps:
  1. Stop incremental updates on the accelerator.
  2. Create the recommended organizing keys on the tables to improve the performance.
  3. Restart incremental updates on the accelerator.

sample_script.sqlsample_script.sql

[{"Product":{"code":"SS4LQ8","label":"Db2 Analytics Accelerator for z\/OS"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"3.1.0;4.1.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 August 2018

UID

swg21674693

Page Feedback