V5.1 PTF-1 Release Notes
This document describes enhancements and fixes in IBM DB2 Analytics Accelerator for z/OS Version 5.1, PTF-1
April 2016: We recommend an upgrade of the accelerator server component with PTF UI37271 for those customers already at product level V5.1 PTF-1.
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 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:Check the Prerequisites and Maintenance for IBM DB2 Analytics Accelerator for z/OS, V5.1 website for further updates.
Query history shows truncated statementsAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1Problem descriptionThe 'Show SQL' output for a query in the query history of the Accelerator view in IBM DB2 Analytics Accelerator Studio shows truncated statements.Problem summaryIf a statement exceeds a certain length, only a part of the statement is kept in memory. Despite this behavior, the complete statement is stored on disk. If, however, the truncated part does not occupy the entire space that is reserved for it, (this happens to prevent the split of UTF-8 characters), the query statement will not be retrieved. Only the truncated part will be displayed.Problem conclusionThe problem has been fixed.
Transfer of software update packages from IBM DB2 Analytics Accelerator Studio not possibleAffected usersAll users of IBM DB2 Analytics Accelerator for z/OSProblem descriptionIBM Analytics Accelerator Studio does not allow users to transfer packages. A message hints at the fact that a newer version is already installed on the server.Problem summaryIf the locale in the IBM Analytics Accelerator Data Studio is set to a value different from English or German, the transfer of software update packages may be blocked in rare cases.Problem conclusionThe problem has been fixed with the stored procedures of the version 5.1 PTF-1. If you run the software transfer with a previous version of the stored procedures then use the following circumvention: 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:
Save and close the file. Then start the application.
Tables not displayed anymore after changing to an unsupported table typeAffected usersAll users of IBM DB2 Analytics Accelerator for z/OSProblem descriptionAfter (accidentally) changing the type of an accelerator table to an unsupported type, the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure returns error AQT10205I: 'An internal processing error occurred in the stored procedure. Diagnostic information: An assertion 'false' failed. Additional info: unsupported table type'. In IBM DB2 Analytics Accelerator Studio, all tables disappear from view. Furthermore, the SYSPROC.ACCEL_REMOVE_TABLES stored procedure is unable to remove the table with the unsupported type.Problem summaryAfter the change to an unsupported table type, error 10205 is returned, with the effect that no table information is output. Actions that lead to this error:
- Defining a table as the history table of another base table.
- Dropping a table in DB2 and re-creating a table-like object with the same name, but of an unsupported type, such as a view.Problem conclusionThe problem has been fixed.
The SYSPROC.ACCEL_GET_TABLES_INFO stored procedure no longer fails with error AQT10205I. The proper output is returned, but the unsupported table is marked as 'ViolatedOnDBMSSystem'. The unsupported table can be removed by running the SYSPROC.ACCEL_REMOVE_TABLES stored procedure.
Accelerator stops workingAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1Problem descriptionThe accelerator state is 'stopped'. The accelerator trace information contains an entry like 'Error: File system /nz usage exceeded 95 threshold on rack1.host1 System will be stopped'Problem summaryExtended retention of query history data results in more and bigger log files, which might fill up the file system. When the file system reaches the limits of its capacity, the database system of the accelerator stops working to prevent damage to the data.Problem conclusionThe problem has been fixed.
Stored procedures that create trace file entries fail with error AQT10200EAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem descriptionStored procedures that create trace file entries fail with the following error: AQT10200E - The SELECT operation failed. Error information: 'DSNT408I SQLCODE = -443, ERROR: ROUTINE ACCEL_READFILE (SPECIFIC NAME ACCEL_READFILE) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT FILE OPEN ERROR:?%????Á?*??Ê??? DSNT418I SQLSTATE = 38AQT SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXRRTN SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -818 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFCCE' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION '. The unsuccessful operation was initiated by the following statement: 'SELECT COUNT(*) FROM TABLE ( DSNAQT.ACCEL_READFILE(:h</tmp/aqt-trace-20151218-1954-17-FghGxQ>, 0) ) AS T(SEQID, TRACEDATA) INTO :h'.Problem summaryThe problem is that under certain conditions, the DSNAQT.ACCEL_READFILE function cannot convert EBCDIC data to ASCII. This happens, for example, if the function is run in a locale that has been set to a multi-byte (MBCS) coded character set identifier (CCSID). As a consequence, all stored procedure functions fail if tracing is enabled for these procedures. The error message gives no clue to the cause of the problem (see Problem description).Problem conclusionThe problem has been fixed. A new function DSNAQT.ACCEL_READFILE2 was introduced for stored procedure tracing.
TIMESTAMP arithmetics results of IBM DB2 Analytics Accelerator for z/OS V5.1.0 might differ from results in DB2 for z/OSAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summaryThis can affect TIMESTAMP based calculations or conversions where one of the operands is the end of the month.Problem conclusionThe problem has been fixed.
Unsupported type of HAVING clause not documented in "Incompatibilities between DB2 for z/OS and IBM DB2 Analytics Accelerator for z/OS"Affected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summaryNetezza 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).Problem conclusionThe problem has been fixed.
Query does not return anything (query hangs)Affected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summaryIn 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.Problem conclusionThe problem has been fixed.
Special characters not allowed in accelerator-only tablesAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summaryThe following special characters must not be used in accelerator-only tables: ampersand (&), double quotes ("), single quotes ('), less than (<), greater than (>)Problem conclusionThe problem has been fixed.
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.Affected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summaryDB2 erroneously allows columns with a data type of timestamp(0) in CREATE TABLE IN ACCELERATOR statements.Problem conclusionThe problem has been fixed.
Timestamp operations do not return fractional secondsAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summaryIn 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.Problem conclusionThe problem has been fixed.
Rare occurrences of SQL errors if the system utilization is highAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summarySQL errors might occur if the system utilization is very high, such as: SQLCODE=-4742, SQLCODE= -904Problem conclusionThe problem has been fixed.
After a reload, query acceleration is disabled for a table that is enabled for incremental updatesAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summaryIf 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.Problem conclusionThe problem has been fixed.
Load operation aborts with SIGABEND if the system utilization is very highAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS version 5.1Problem summaryOccasionally, a load operation might end abnormally if the system utilization is very high.Problem conclusionThe problem has been fixed.
If you want to upgrade to the newest version of the incremental update function from a version older than 4.1 PTF-5, you must update the replication engine two times. The first time, apply the base package (RE Base) of Version 5.1 GA, and then the update package of Version 5.1 GA (RE Update). Wait until RE Base has been applied successfully before you continue with RE Update.
Attention: Make sure that a replication status other than 'Error' is displayed in the header of the Accelerator view in IBM DB2 Analytics Accelerator Studio before you apply an update of the replication engine. Otherwise, the accelerator might stop working and enter a state that requires the help of IBM support.
To avoid this issue, use IBM DB2 Analytics Accelerator Studio Version 220.127.116.11 or a higher version for the update because this version checks the replication status before an update can be applied.
If any of the following passwords (which were stored on your accelerators) has a length of less than 4 characters, you must first change these passwords to a new value with at least 4 characters before you can upgrade the product software to version 5.1.
- 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)
IBM Netezza AnalyticsFor known issues with the IBM Netezza Analytics stored procedure package in Version 5.1 PTF-1, see: http://www.ibm.com/support/docview.wss?uid=swg27047503
Encryption of Data in MotionFor known issues with the network traffic encryption feature (encryption of data in motion) in Version 5.1 PTF-1, see: http://www.ibm.com/support/docview.wss?uid=swg27047502
Different results possible for strright() function on NCHAR columns in IBM DB2 Analytics Accelerator for z/OS V5.1.0 and DB2 for z/OSSummaryThis only affects Unicode tables when MULTIPLY_ENCODING is ON.Solution or workaroundThis problem will be fixed in a later release.
If the system utilization is very high, the accelerator might restart automatically to recover from the overload situation.SummaryThe 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
- Check the system utilization.
- Adjust the system utilization as needed.
- Retry failed operations and continue.
AQT10050E : An internal error occurred on the 'name' accelerator: ODBC operation SQLExecDirect failed with code -1 when executing statement: TRUNCATE TABLESummaryIf 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 workaroundRetry the failed operations. The problem will be fixed in a later release.
Load operations hang if the system utilization is very highSummaryOccasionally, load jobs might end abnormally if the system utilization is very high.Solution or workaroundCancel and restart the load operation.
The execution of an INSERT INTO ... SELECT ... FROM statement in an in-database transformation job might failSummaryIn 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 workaroundThis is an indication that the system limits were reached. Check the system utilization and adjust it to the capacity of the system.
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 messagesSummaryIf 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 workaroundThis is an indication that the system limits were reached. Check the system utilization and adjust it to the capacity of the system.
Parallel incremental updates require continuous incremental updates to be enabledSummaryYou might lose table updates if the parallel application of incremental updates is enabled, while continuous incremental updates are disabled.Solution or workaroundIf you want to apply incremental updates in parallel, make sure that you also enable continuous incremental updates.
Subscription of suspended tables ends in rare casesSummaryIn 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 workaroundRestart the incremental update processes via the config console (5) - (Menu) Manage Incremental Updates -> (4) - Restart incremental update processes
Schema change blocks reloads if continuous incremental updates are enabledSummaryContinuous 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 workaroundIf you want to change the schema of an incrementally updated table, follow these steps:
- 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.
- Remove the table from the accelerator. If the table is found on more than one accelerator, remove it from all these accelerators.
- Restart incremental updates for your accelerators.
- Run the REORG utility on the table (in DB2).
- Re-define the table on as many accelerators as necessary.
High system utilization causes slower performance of IBM DB2 Analytics Accelerator tasksSummaryMaintenance tasks might take longer than expected if the system utilization is high.Solution or workaroundIf the problem persists, restart the Netezza Platform Software (NPS) from the IBM DB2 Analytics Accelerator Console.
Queries might fail if system utilization is very highSummaryA very high system utilization might cause queries to fail occasionally with out-of-memory errors, such as ‘NZ IO error‘ or ‘SQL code = -904 00E7000E Bad data’ .Solution or workaroundThe system continues to operate and recovers from such situations. Subsequent queries succeed. The error indicates that system limits might have been reached. Check the system utilization and adjust it as needed.
A reload of a table set with many tables fails.SummaryA reload of a table set with thousands of tables might fail sporadically.Solution or workaroundSplit the table sets to be reloaded into smaller units and retry the operation.
Rare occurrence of CHC0051W messageSummaryUnder rare circumstances, the following error might occur:
CHC0051W The oldest open commit group in the staging space for subscription XXXXXXXX opened at YYYY-MM-DD-hh.mm.ss has exceeded the open commit group warning age of 60 minutes by xxxx minutesSolution or workaroundRequest a CDC staging space report and reload the affected tables.
Call-home function not working under certain conditionsSummaryService 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).
- 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 an older release of V5.1 PTF-1, install the server component UI37271 only. This upgrades your installation to the latest version of V5.1 PTF-1.
- 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.
Translate this page: