IBM DB2 Analytics Accelerator for z/OS V5.1.0: Release Notes

1. April 2016 We recommend a full upgrade to the latest version of V5.1 PTF-1 for those customers who are currently running V5.1 GA.
   
   
   With V5.1.0 and V5.1.1, a new way of data organization was introduced. The new organization has the potential to speed up the insertion of small chunks of data, which are typical for incremental updates. In rare situations, with huge table sizes, this new way of organizing data might lead to different query results between DB2 for z/OS and the accelerator. The problem can be avoided by switching back to the former way of data organization.
   
   After an installation of IBM DB2 Analytics Accelerator for z/OS Version 5.1 PTF-1 (PTF UI37271), the former method of data organization is re-activated. If the switch back to the former data organization fails, the following message is written to the z/OS system log (SYSLOG) of the LPAR in which the subsystem or group is located:
   DSNX881I -SSID W 2004 ... Not all zone map records are consistent with the expected layout
   
   For further information regarding the structure of DSNX881I messages see Structure of DSNX881I Messages.
   This is a temporary fix. The problem will be finally fixed with an upcoming release of the Netezza Platform Software (NPS), the use of which will be certified for IBM DB2 Analytics Accelerator for z/OS.
   Important: To make sure that all inconsistent data is removed from the accelerator, reload the potentially affected tables as decribed here:

   • How to identify tables that should be reloaded after an installation of V5.1 PTF-1 (PTF UI37271)
   Check the Prerequisites and Maintenance for IBM DB2 Analytics Accelerator for z/OS, V5.1 website for further updates.

1. IBM DB2 Analytics Accelerator for z/OS does not support tables with leading spaces
   Summary

   IBM DB2 Analytics Accelerator for z/OS does not support tables with leading spaces in their names. Although it is possible to define such a table, problems occur when you remove or re-define it.

   Description

   When you define a table with leading spaces in the name, and then remove and re-define it, the following load of the tables fails with the following error message:

   "ACCEL_LOAD_TABLES fails with "AQT10050I - An internal error occurred [...] 'ERROR: CREATE TABLE: object [...] already exists.'"

   Solution

   The problem has been fixed.
2. Table load fails with 'dwa-iu-init-fwd-agent-monitor healthCheck' error
   Summary

   The dwa-iu-re-agent-monitor healthCheck script fails when a replication-enabled table is loaded.

   Description

   The following message is displayed:

   "AQT20023I - Incremental updates could not be enabled for the "xxx" accelerator because the following script returned an error: "dwa-iu-init-fwd-agent-monitor healthCheck""

   Solution

   The problem has been fixed.
3. Systems under heavy load experience temporal or permanent service outages
   Summary

   Problem can occur if the environment is under heavy stress, with might be caused by with multiple running queries at the same time, high-volume load tasks or incremental updates.

   Description

   System becomes unresponsive, initiates a fail-over, or restarts its services, such as the Access Server, the Replication Engine or the Netezza Performance Server (NPS).

   Solution

   The problem has been fixed.
4. Incorrect table skew
   Summary

   If data of an accelerator table is heavily skewed so that not all hardware resources are used, the SYSPROC.ACCEL_GET_TABLE_INFO stored procedure might none the less report a skew value of 0 for the table.

   Description

   The stored procedure reports that the table is not skewed at all, even though it is not true.

   Solution

   The problem has been fixed.
5. Tables with columns whose name start with an underscore cannot be defined on an accelerator
   Summary

   The product does not permit columns with such names because the Netezza Performance Server (NPS) cannot handle these.

   Description

   Creating an accelerator-only table with a column that uses an underscore as the first character in the column name fails with SQLCODE -4747.

   Solution

   The problem has been fixed.
6. Update or deletion of rows from incrementally updated tables with NULL values in key columns takes incredibly long or fails
   Summary

   It takes an extremely long time to update or delete a great number of rows from an incrementally updated table with NULL values in key columns. Sometimes, the process runs out of memory (fails).

   Description

   The replication latency increases significantly when changes of these types are propagated to the target table on the accelerator.

   Solution

   The problem has been fixed.
7. ID of DB2 11 for z/OS subsystems not shown in Query History
   Summary

   The problem occurs when a DB2 11 for z/OS subsystem is used with IBM DB2 Analytics Accelerator.

   Description

   "DB2 Subsystem ID" in Query History is shown for DB2 10 for z/OS subsystems, but not for DB2 11 for z/OS subsystems.

   Solution

   The problem has been fixed.
8. Active NPS is removed after failed nzupgrade check
   Summary

   The problem can occur when a nzupgrade check fails during an upgrade of the Netezza Performance Server (NPS).

   Description

   The active NPS version is removed.

   Solution

   The problem has been fixed.
9. Accelerator is restarted without prior confirmation after configuration change on console
   Summary

   For some options that can be modified on the IBM DB2 Analytics Accelerator Console, the saving of a new configuration might cause an immediate restart of the accelerator.

   Description

   The accelerator is immediately restarted after a configuration change without asking the console user for a confirmation. During the restart, the accelerator is not available.

   Solution

   If a restart of an accelerator is required after a configuration change, the user is prompted to confirm the configuration change and the restart.
10. Modifications to resource limits are automatically reset
    Summary

    The user sets the resource limits for the DB2 subsystem on the IBM DB2 Analytics Accelerator Console. After some time, the value is reset to the original value by an internal process because the value is not persistent.

    Description

    After setting the resource limits for DB2 subsystems in the IBM DB2 Analytics Accelerator Console, the value is automatically reset to its original value.

    Solution

    The problem has been fixed.

Affected passwords:

• IBM DB2 Analytics Accelerator Console
• CDC started task user ID
• User ID of the owner of the CDC metadata tables (if different from CDC started task user)
• Logon user ID of call-home FTP server
• Keystore containing the Authentication Encryption Key (AEK) for self-encrypting disks (Netezza)
• Encryption of Data in Motion: password securing the keystore (PKCS#12 file) that contains the RSA key pair for encrypted connections to an accelerator (to be stored on the NSS server and to be imported into the accelerator)
• DB2 user ID configured to run the CDC Capture Agent (as specified on IBM DB2 Analytics Accelerator Console and if different from CDC started task user ID)

1. IBM Netezza Analytics
   For known issues with the IBM Netezza Analytics stored procedure package, see:http://www.ibm.com/support/docview.wss?uid=swg27047149
2. Encryption of Data in Motion
   For known issues with the network traffic encryption feature (encryption of data in motion), see:http://www.ibm.com/support/docview.wss?uid=swg27047150
3. TIMESTAMP arithmetics results of IBM DB2 Analytics Accelerator for z/OS V5.1.0 might differ from results in DB2 for z/OS
   Summary

   This can affect TIMESTAMP based calculations or conversions where one of the operands is the end of the month.

   Solution or workaround

   This problem will be fixed in V5.1.0 PTF-1.
4. Different results possible for strright() function on NCHAR columns in IBM DB2 Analytics Accelerator for z/OS V5.1.0 and DB2 for z/OS
   Summary

   This only affects Unicode tables when MULTIPLY_ENCODING is ON.

   Solution or workaround

   This problem will be fixed in a later release.
5. Unsupported type of HAVING clause not documented in "Incompatibilities between DB2 for z/OS and IBM DB2 Analytics Accelerator for z/OS"
   Summary

   Netezza specification: In a grouping select, the HAVING clause can only reference expressions that are single-valued within a group. That is, you can only reference group fields, aggregates, or single-valued expressions that are derived from group fields or aggregates (which must include constants).

   Solution or workaround

   The documentation in the IBM Knowledge Center and the IBM DB2 Analytics Accelerator for z/OS: User's Guide will be updated for V5.1.0 PTF-1
6. Query does not return anything (query hangs)
   Summary

   In rare cases, a query which causes a run-time error right after start (numeric overflow, such as a division by zero, and so on) does not return anything, that is, not even an error message.

   Solution or workaround

   The problem will be fixed in V5.1.0 PTF-1
7. Special characters not allowed in accelerator-only tables
   Summary

   The following special characters must not be used in accelerator-only tables: ampersand (&), double quotes ("), single quotes ('), less than (<), greater than (>)

   Solution or workaround

   The problem will be fixed in V5.1.0 PTF-1
8. Timestamp columns with a precision other than 6 or 12 are incorrectly created as precision 6 columns in accelerator-only tables. This should not block SELECT, INSERT, UPDATE, or DELETE operations on these columns, but DB2 for z/OS blocks such operations for no apparent reason.
   Summary

   DB2 erroneously allows columns with a data type of timestamp(0) in CREATE TABLE IN ACCELERATOR statements.

   Solution or workaround

   The problem will be fixed in V5.1.0 PTF-1
9. Timestamp operations do not return fractional seconds
   Summary

   In the database users guide, it is mentioned that timestamp values have a date part and a time part. Six digits are reserved for the decimal representation of fractional seconds. Within the range from January 1, 0001 00:00:00.000000 to December 31, 9999 23:59:59.999999, the fractional seconds part is erroneously omitted.

   Solution or workaround

   The problem will be fixed in V5.1.0 PTF-1
10. If the system utilization is very high, the accelerator might restart automatically to recover from the overload situation.
    Summary

    The accelerator goes online again after the recovery. Processes that were ended prematurely by the restart, such as load jobs or queries, must be restarted.

    Solution or workaround

    1. Check the system utilization.
    2. Adjust the system utilization as needed.
    3. Retry failed operations and continue.
11. AQT10050E : An internal error occurred on the 'name' accelerator: ODBC operation SQLExecDirect failed with code -1 when executing statement: TRUNCATE TABLE
    Summary

    If the system utilization is very high because load jobs, queries, and incremental update operations are running in parallel, the accelerator might return the error message that is mentioned above.

    Solution or workaround

    Retry the failed operations. The problem will be fixed in a later release.
12. Load operations hang if the system utilization is very high
    Summary

    Occasionally, load jobs might end abnormally if the system utilization is very high.

    Solution or workaround

    Cancel and restart the load operation.
13. The execution of an INSERT INTO ... SELECT ... FROM statement in an in-database transformation job might fail
    Summary

    In rare situations, INSERT INTO ... SELECT ... FROM statements in in-database transformation jobs can fail if the system utilization is very high and many processes are run in parallel.

    Solution or workaround

    This is an indication that the system limits were reached. Check the system utilization and adjust it to the capacity of the system.
14. Incremental update processing might stop and restart if the system utilization is very high (many parallel load and query operations). This is indicated by according console messages
    Summary

    If the system utilization is very high, with many processes running in parallel, the processing of incremental updates might stop. Processing resumes after some time and stalled operations are recovered automatically. If "continuous incremental updates" are enabled", tables that are being loaded at the time of the interruption go into the "suspended" state. You must reload such tables to get them back into incremental update processing.

    Solution or workaround

    This is an indication that the system limits were reached. Check the system utilization and adjust it to the capacity of the system.
15. Parallel incremental updates require continuous incremental updates to be enabled
    Summary

    You might lose table updates if the parallel application of incremental updates is enabled, while continuous incremental updates are disabled.

    Solution or workaround

    If you want to apply incremental updates in parallel, make sure that you also enable continuous incremental updates.
16. Rare occurrences of SQL errors if the system utilization is high
    Summary

    SQL errors might occur if the system utilization is very high, such as: SQLCODE=-4742, SQLCODE= -904

    Solution or workaround

    This is an indication that the system limits were reached. Check the system utilization and adjust it to the capacity of the system.
17. Subscription of suspended tables ends in rare cases
    Summary

    In rare situations, the subscription (registration for incremental updates) ends for suspended tables without an apparent reason when using the feature "Suspend faulty tables" and when a table is about to be suspended due to a fault.

    Solution or workaround

    Restart the incremental update processes via the config console (5) - (Menu) Manage Incremental Updates -> (4) - Restart incremental update processes
18. Software update problem due to wrong locale setting
    Summary

    Some packages might not be selectable in the Transfer window of IBM DB2 Analytics Accelerator Studio even though they have not yet been transferred. This is most likely caused by one of the following conditions:

    1. IBM DB2 Analytics Accelerator Studio was set to a locale other than US English.
    2. IBM DB2 Analytics Accelerator Studio was not set to any locale. In this case, the program uses the locale of the operating system. If that locale is different from US English, you end up with the same issue.

    Solution or workaround

    To circumvent this problem, use US English as the locale for IBM DB2 Analytics Accelerator Studio. On a Windows system, add '-nl en_US' (without the quotes) to the start command that you find in the properties of the shortcut that launches the application ("Target" field on "Shortcut" tab). On a Linux system, open the eclipse.ini file and add the following line:

    -Duser.language=en -Duser.country=US

    Save and close the file. Then start the application.

19. After a reload, query acceleration is disabled for a table that is enabled for incremental updates
    Summary

    If query acceleration is automatically disabled for suspended tables (a new feature that must be enabled) and "continuous incremental updates" are turned on, then, under very rare conditions and a high system utilization, query acceleration might be disabled for a table. In such a situation, a DSNX881I message is issued to inform you that query acceleration has been disabled for a particular table.

    Solution or workaround

    1. Repair the faulty table.
    2. Reload the table.
    3. Re-enable query acceleration for the table.

    In some cases, a false positive is reported, that is, query acceleration was disabled, but the table is still eligible for incremental updates. You can detect cases like easily by checking the table state in the Accelerator view of IBM DB2 Analytics Accelerator Studio or by running the corresponding stored procedure. In case of a false positive, just re-enable query acceleration. Fixing the table and reloading it is not needed.
20. Load operation aborts with SIGABEND if the system utilization is very high
    Summary

    Occasionally, a load operation might end abnormally if the system utilization is very high.

    Solution or workaround

    Restart the load operation.
21. Schema change blocks reloads if continuous incremental updates are enabled
    Summary

    Continuous incremental updates are enabled and you change the schema of one of the (incrementally updated) tables. The schema change enforces a full reload of the table, but the reload fails.

    Solution or workaround

    If you want to change the schema of an incrementally updated table, follow these steps:

    1. From DB2 Analytics Accelerator Studio, stop incremental updates for the affected accelerator. If the table exists on more than one accelerator, stop incremental updates for all accelerators on which this table exists.
    2. Remove the table from the accelerator. If the table is found on more than one accelerator, remove it from all these accelerators.
    3. Restart incremental updates for your accelerators.
    4. Run the REORG utility on the table (in DB2).
    5. Re-define the table on as many accelerators as necessary.
22. Call-home function not working under certain conditions
    Summary

    Service requests are not opened automatically by the call-home function if the attachment size exceeds 38 MB.

    Solution or workaround

    • Check the DSNX881I messages in the SYSLOG, which might report events that led to this situation, and might enable you to solve the problem. See the following technote: http://www-01.ibm.com/support/docview.wss?uid=swg27037905
    • Contact IBM support for an interim fix.
    • Wait for the final code fix, which will be provided in a later program temporary fix (PTF).

How to identify tables which should be reloaded after an installation of V5.1 PTF-1 (PTF UI37271)

• Use the sample code (for a stored procedure) from the technote at How to store information about accelerated tables in a DB2 for z/OS table to retrieve information about your accelerator-shadow tables. This information will be stored in a temporary DB2 table.
• Run a simple SQL query against the replicationStatus and the lastLoadTimestamp columns of this temporary table to identify tables that must be reloaded. Follow the diagram at the end of this note to dertermine whether a table must be reloaded.

• If your current version is V5.1 GA, upgrade to the latest version of V5.1 PTF-1. This is a full upgrade of all accelerator components.

• Immediately after the upgrade to the latest version of V5.1 PTF-1 an automatic conversion of data structures takes place.
• Depending on the total amount of data to be converted, this might take more than 1 hour, during which the Netezza Platform Software (NPS) will be in the "Initializing" state. That is, the NPS cannot process requests.

• Within the first hour after the installation of the new accelerator server software, you might see a "DSNX881I -SSID W 2004 ... Not all zone map records are consistent with the expected layout." message in the SYSLOG, which reports conversion failures. This is normal if the amount of data to be converted is high and due to a timeout mechanism. Check the SYSLOG one hour after the installation of the accelerator server software. If you see zero or a maximum of one DSNX88I...2004... message, the conversion has finished successfully and you can start the full-table-reload of the identified tables. If you see more than one DSNX88I...2004... message, check the SYSLOG again after 7 hours. If further messages show up, the conversion could not run to completion. In this case, contact IBM support.