Release Notes
Abstract
This document describes enhancements and fixes in IBM DB2 Analytics Accelerator for z/OS Version 5.1.0, maintenance level PTF-4
Content
After an installation of V5.1.0 PTF-4, 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 DB2 subsystem or group is located:
How to identify tables that should be reloaded after an installation of V5.1.0 PTF-4
What's new
Please find information about new functions here.
Solved problems
- Stored procedure SYSPROC.ACCEL_ARCHIVE_TABLES fails with AQT10060WAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1.0 using High Performance Storage Saver (HPSS)Problem descriptionDuring the archiving of partitions, the stored procedure SYSPROC.ACCEL_ARCHIVE_TABLES fails with message AQT10060W.Problem summaryIf a table space contains many partitions in different states, the DISPLAY DATABASE command might be unable to return a list of all partitions within 50 lines of space. If that happens, the stored procedure fails.Problem conclusionThe problem has been fixed.
- Loading an incrementally updated table fails with error AQT20017E.Affected usersUsers of the auto-key function for an organizing key in connection with incremental updates in IBM DB2 Analytics Accelerator for z/OS Version 5.1.0Problem descriptionThe problem occurs if you run incremental updates and have enabled the automatic definition of distribution and organizing keys. If you enable incremental updates for a table with a unique constraint on a DECIMAL column, and the precision is greater than 18, the auto-key function might erroneously define an organizing key for this column. As a result, a load of the table fails with error AQT20017E ('column is of type, which cannot be used as an organizing key column'). This happens because the auto-key function does not take the precision of DECIMAL columns into account.: You can only select a DECIMAL column as an organizing key if its precision is not greater than 18.Problem summaryLoading a table fails with AQT20017E for an incrementally updated table with a unique constraint on a DECIMAL column whose precision is greater than 18.Problem conclusionThe problem has been fixed. The auto-key function now considers the precision of DECIMAL columns and will not select a DECIMAL column with a precision greater than 18 as an organizing key.
- WLM started task returns code U4036 (abnormal ending)Affected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1.0Problem descriptionThe WLM started task returns abend code U4036 to IBM DB2 Analytics Accelerator when it runs the DSNAQT.ACCEL_READFILE2 or the DSNAQT.ACCEL_GETVERSION functionProblem summaryIf the stack size is too low for IBM DB2 Analytics Accelerator stored procedures and functions, DSNAQT.ACCEL_READFILE2 and DSNAQT.ACCEL_GETVERSION fail because the entire stack will be used up by RUNOPTS. The problem is the missing definition of the stack size during the installation of these functions.Problem conclusionThe problem has been fixed.
- Refreshing the SQL history results in SQL error 'XMLParseException... 'Unterminated entity reference''Affected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1.0Problem descriptionWhen you refresh the SQL history in IBM DB2 Analytics Accelerator Studio, a SQL error message with the following text might be shown: 'XMLParseException ... 'Unterminated entity reference'' 'Problem summaryIf the XML string returned by the stored procedure SYSPROC.ACCEL_GET_QUERY_DETAILS is too long, it might be truncated at the wrong position.Problem conclusionThe problem has been fixed.
- SYSPROC.ACCEL_GET_TABLES_INFO returns warning AQT10115W if versions do not matchAffected usersUsers of a mixed environment with a version 5.1.0 accelerator and version 4.1.0 stored procedures at fix level PTF-5 or PTF-6.Problem descriptionFor accelerator-only tables (AOTs) in such an environment, the stored procedure SYSPROC.ACCEL_GET_TABLES_INFO returns AQT10115W (“table was added by a newer stored procedure version than the one currently active”). This is incorrect because the version of the stored procedures version did not change.Problem summaryThe value of the SUPPORTLEVEL column in the catalog table SYSACCEL.SYSACCELERATEDTABLES is determined by the accelerator when an AOT is created. In this case, the version number is 5, which is higher than the version number 4 of the stored procedures. This leads to message AQT10115W. The product behavior is correct, but the message is misleadingProblem conclusionThe problem has been fixed for the stored procedures in version 5.1.0 PTF-4, such that it will not occur again if the version of the accelerator software is higher. If you do not want to upgrade the stored procedures from version 4.1.0 PTF-5 or PTF-6 to version 5.1.0, you can set the value of the SUPPORTLEVEL column in SYSACCEL.SYSACCELERATEDTABLES to 4 for AOTs.
- DSNX881I message 5 “RunAwayQuery” is written to system logs of unaffected DB2 subsystemsAffected usersUsers of version 5.1.0 accelerators that have been paired with multiple DB2 subsystemsProblem descriptionThe message DSNX881I with Message-ID 5 “RunAwayQuery” is sent to all paired DB2 subsystems, rather than to the affected subsystems only. In addition, a message with similar information (DSNX881I with Message-ID 2001 “LongRunningSQLStatement') is written to the SYSLOG of the DB2 subsystem that the query came from.Problem summaryIf more than one DB2 subsystem is paired with an accelerator, long-running queries cause messages of the type DSNX881I with Message-ID 5 “RunAwayQuery” to be written to the SYSLOGs of all paired subsystems. In addition, a DSNX881I message with Message-ID 2001 “LongRunningSQLStatement” added to the SYSLOG of the DB2 subsystem that the query came from.Problem conclusionThe problem has been fixed. Just one message is generated, and it is added to the SYSLOGs of the affected DB2 subsystems only (DSNX881I with Message-ID 2001 “LongRunningSQLStatement”).
- Mako Ultralite (N3001-001) ceases to work after upgrade of Netezza Host Platform (HPF) softwareAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1.0 with an Ultralite (N3001-001) systemProblem descriptionAfter an upgrade of the HPF software to version 5.4.1, 5.5.1, or higher on an Ultralite (N3001-001) system, the accelerator does not come online again or ceases to work.Problem summaryAn upgrade of the HPF to version 5.4.1 or 5.5.1 on an Ultralite (N3001-001) system has the following side-effects:
- The NPS variable NPS_IMM_IFACE in /nzlocal/scripts/hpinfo is renamed to NPS_IPMI_IFACE.
- The entries imm1/2 in /etc/hosts are renamed to ipmi1/2.
Problem conclusionThe problem has been fixed. - dwamonitor IP address can no longer be identifiedAffected usersUsers of IBM DB2 Analytics Accelerator for z/OS Version 5.1.0 who changed the /etc/hosts file on the acceleratorProblem descriptionProblems occur during the initial installation, during cluster maintenance, during network maintenance for an Ultralite system, and during server startup.Problem summaryIf a support engineer commented out an entry in /etc/hosts for backup purposes, and added a new line for dwamonitor, the lookup of the dwamonitor IP address could fail.Problem conclusionThe problem has been fixed.
- JCL of AQTTRIN fails with BIND error / OWNER clause is missingAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1.0Problem descriptionBIND operations fail when you run the generated JCL job AQTTRIN, The BIND statements in the JCL do not contain an OWNER clause.Problem summaryIf the installation program AQTTRIN is run in preview mode, it generates a JCL that executes the same operations as the installer program. The creation of database objects is part of the same job step as the BIND operations, even though they use different DD names. In some cases, the BIND statements are executed before the database objects have been created. These statements fail because the database objects do not exist yet.Problem conclusionThe problem has been fixed. The creation of database objects is now handled by a separate job step that is executed before the job step that contains the BIND statements. OWNER clauses are now added to the BIND statements.
- Different number of rows although query is run against the same dataAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1.0Problem descriptionIf a query is run multiple times, it might return result sets with a different number of rows even though the underlying data has not changed.Problem summaryIf multiple queries (in general more than 10) are run in parallel, a result set of more than several hundred rows might be incomplete. The problem is sometimes accompanied by failing queries with SQLCODE = -904, SQLSTATE = 57011, SQLERRMC = 00001080 'Bad data issue too many times'.Problem conclusionThe problem has been fixed.
- A reload of a table set with many tables failsAffected usersAll users of IBM DB2 Analytics Accelerator for z/OS Version 5.1.0Problem descriptionA reload of a table set with thousands of tables might fail sporadically with the error message "ERROR: 54 : Storage transaction table is full".Problem summaryThe number of tables that can be fully reloaded by a single request is limited to 4000 tables. If this limit is exceeded, the transaction aborts and the following error message is displayed: "ERROR: 54 : Storage transaction table is full"Problem conclusionThe problem has been fixed: A message is displayed before load starts. This message advises you to reduce the number of tables in the table set to no more than 4000 tables.
Migration recommendations and considerations
For recommendations and considerations concerning migration to IBM DB2 Analytics Accelerator for z/OS Version 5.1.0 read here.
Known issues
- IBM Netezza AnalyticsFor known issues with the IBM Netezza Analytics stored procedure package in IBM DB2 Analytics Accelerator for z/OS Version 5.1.0, see:Encryption of Data in MotionFor known issues with the network traffic encryption feature (encryption of data in motion) in IBM DB2 Analytics Accelerator for z/OS Version 5.1.0, see:Transfer of software update packages from IBM DB2 Analytics Accelerator Studio not possibleSummaryIBM 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. If 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 might be blocked in rare cases.Solution or workaroundThe problem has been fixed with the stored procedures of version 5.1.0 PTF-1. If you run the software transfer with a previous version of the stored procedures, use the following workaround:
- 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.
If the system utilization is very high, the accelerator might restart automatically to recover from the overload situationSummaryThe accelerator goes online again after the recovery. Processes that were ended prematurely by the restart, such as load jobs or queries, must be restarted.
Example of high system utilization: 30 parallel load threads with tables having 10 partitionsSolution or workaround- Check the system utilization.
- Adjust the system utilization as needed.
- Retry failed operations and continue.
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 the incremental update processing queue.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.Insertion of integer and decimal values causes failure of IDT jobsSummaryThe insertion of integer and decimal values by an INSERT INTO ... VALUES statement might cause a failure of in-database transformation (IDT) jobs.For example, the following statement failed during tests:
CREATE TABLE T1 (CDEC51 DECIMAL(5,1)) IN ACCELERATOR XYZ;
INSERT INTO T1(CDEC51) VALUES(77247.04-69993);Symptoms: SQL codes like the following are returned:
SQLCODE = -802, SQLSTATE = 22003, DATA EXCEPTION FROM IBM DB2 Analytics Accelerator TOKENS OVERFLOW ACCELERATOR NUMERIC DATA...
Solution or workaroundUse only decimal values instead of a mixture of integer and decimal values. The following statement, for example, will be successful:INSERT INTO T1(CDEC51) VALUES(77247.04-69993.0)
Tables cannot be added if their names contain special characters, such as tabs and newlinesSummaryTables cannot be added if their names contain special characters, such as tabs and newline characters.Solution or workaroundFix will be delivered in a later release.Large number of accelerator-shadow tables might extend time needed to activate incremental updatesSummaryIf there is a large number of tables on an accelerator, that is, more than 30000 the activation of incremental updates for these tables might take longer. In this context, it does not matter whether the tables have already been enabled. The extra time needed is relevant if the number of table activations in one request exceeds 10 because this will lead to a premature end of the operation, which in turn leads to an automatic disablement of incremental updates for those tables. You must re-enable and reload such tables.Solution or workaroundReduce the number of tables that are activated in a single batch job to less than ten (<10).If the system utilization is high, load operations might fail with a message "Failed to start child transaction in session " -LocksharedObjectWait: LockAcquire failed".SummaryUnder a high system load, load operations might fail with a message "Failed to start child transaction in session " -LocksharedObjectWait: LockAcquire failed".Solution or workaroundRetry the load operation.ALTER TABLE ALTER PARTITION is not blocked by a partition in PRO State (DB2 V11 only)SummaryA partition can be altered by an ALTER TABLE ALTER PARTITION statement although the partition has been archived by the SYSPROC.ACCEL_ARCHIVE_TABLES stored procedure. Normally, SQLCODE -904 would be returned because the archived partition has been set to the PRO state.Solution or workaroundBecause a read-only partition was changed, a following restore operation will probably fail. The problem has been fixed in DB2 V12.How to identify tables which should be reloaded after an installation of V5.1.0 PTF-4 (only if product level is "V5.1.0 GA" or "V5.1.0 PTF-1", without hot fix UI37271)
STEP 1: Identify the tables which could be affected- Use the sample code (for a stored procedure) in the following technote:
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.
STEP 2: For all identified tables, disable query acceleration. For more information, see Disabling query acceleration for tables
STEP 3: Install V5.1.0 PTF-4.
STEP 4: Automatic conversion- Immediately after the upgrade to V5.1.0 PTF-4, 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.
STEP 5: Reloading tablesWithin the first hour after installation of the new accelerator server code, you might see a "DSNX881I -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. The conversion failures are caused by a timeout mechanism. Check the SYSLOG one hour after the installation of the accelerator server. If you see zero or at most one DSNX88I-2004 message, the conversion has finished successfully and you can start with 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, you might have a real conversion problem. In this case, contact IBM support. STEP 6: Enable query acceleration after a full table reload. For more information, see Enabling tables for query accelerationClick on the image to enlarge it[{"Product":{"code":"SS4LQ8","label":"Db2 Analytics Accelerator for z\/OS"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"5.1.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
08 August 2018
UID
swg27049398