IBM Db2 Analytics Accelerator for z/OS Version 7.5 Release Notes

Find information about new functions here.

If you plan to install or upgrade IBM Db2 Analytics Accelerator, contact IBM support by opening a case record.

Click here for the list of APARs.

• The query result is "undefined" under the following conditions:
  (a) A query contains a CAST AS TIMESTAMP expression and a TIMESTAMP() function call on a CHAR(14) column.
  (b) The CHAR(14) column contains timestamp values in the format yyyyddmmHHMMSS.

The SYSPROC.ACCEL_LOAD_TABLES stored procedure behaves differently if automatic change detection is enabled.
Before version 7.1.7, a load job with a setting of detectChanges=“DATA” sometimes did not capture the changes in all partitions if the lock mode NONE had been used for the previous load.
This was fixed with version 7.1.7.

The new behavior:
If you use lock mode NONE and detectChanges=“DATA”,

• it can happen that more changes are detected than are actually transferred to the accelerator. This is caused by the SKIP LOCKED DATA option, which is used implicitly by the Db2 Unload Utility. If parts of the data were locked by other processes, then this data is ignored and not unloaded to the accelerator.

• therefore a partition in an accelerator-shadow table is always fully reloaded to ensure that all changes are finally transferred. A reload is started even if the partition had no further changes since then.

General recommendations

• Under a very high system load, the accelerator might appear slow or run into timeouts.

  Recommendation: Do not run more than 30 load threads in parallel.

Accelerator-only tables

• Multi-row inserts fail if rows contain NULL values to be inserted in columns of data type BINARY or VARBINARY.

  Recommendation: Do not use the multi-row insert method under these conditions.

• When doing an INSERT into AOT with subSELECT of a DATE data type into a CHAR/VARCHAR column for the LOCAL date format DD/MM/YYYY, the output will be a valid date format, just not DD/MM/YYYY.

  Example:
  CREATE TABLE T1(CHARCOL1 CHAR(10),CHARCOL2 CHAR(10)) IN ACCELERATOR ZGRYPHON;
  CREATE TABLE T2(CHARCOL1 CHAR(10),CHARCOL2 CHAR(10)) IN ACCELERATOR ZGRYPHON;
  COMMIT;
  SET CURRENT QUERY ACCELERATION=ALL;
  INSERT INTO T2 VALUES('31/12/2001','31/12/2001');
  INSERT INTO T1(CHARCOL1,CHARCOL2) SELECT DATE(CHARCOL1),DATE('31/12/2001') FROM T2;
  -> an unexpected local data format will be returned by the SELECT.
  CHARCOL1 CHARCOL2
  ---------- ----------
  12/31/2001 12/31/2001

  Recommendation:
  The workaround is to change the SELECT statement to use an explicit cast:
  CREATE TABLE T1(CHARCOL1 CHAR(10),CHARCOL2 CHAR(10)) IN ACCELERATOR ZGRYPHON;
  CREATE TABLE T2(CHARCOL1 CHAR(10),CHARCOL2 CHAR(10)) IN ACCELERATOR ZGRYPHON;
  COMMIT;
  SET CURRENT QUERY ACCELERATION=ALL;
  INSERT INTO T2 VALUES('31/12/2001','31/12/2001');
  INSERT INTO T1(CHARCOL1,CHARCOL2) SELECT CHAR(DATE(CHARCOL1)),CHAR(DATE('31/12/2001')) FROM T2;

Queries

• A query running during a partial reload of an incrementally updated table might be unable to access the new partitions.

  Recommendation: Re-run the query after the partial reload has finished.

• A query referencing a large set of federated objects could result in an SQLCODE -904: 954: Not enough storage is available error

  Recommendation: Please contact IBM support for assistance.

• A query referencing a large set of federated objects could run very slow.

  Recommendation: Please contact IBM support for assistance.

• When executing a query with a large result set, the query could be slow.

  Recommendation: Please contact IBM support for assistance.

• A SQL query might hang in IBM Db2 Analytics Accelerator

  Recommendation: Cancel the query from IBM Db2 Analytics Accelerator Studio, or call the SYSPROC.ACCEL_CONTROL_ACCELERATOR stored procedure using the <cancelTasks> command.

Loading tables

• After running the REORG utility in Db2 12 for z/OS,  reloads might be wrongly recommended for partitions that have already been loaded on the accelerator.

  Recommendation: To receive correct reload recommendations, do not use interactive load recommendations and batch job change detection at the same time.

• If you run SYSPROC.ACCEL_LOAD_TABLES with lock mode "TABLESET", the operation might fail and return an AQT10071E error.

  Recommendation: Cancel the previous load and re-run the load operation.

• 04E-00C90050 ABEND can occur in Db2z if you are run parallel load jobs or use integrated synchronization to update replicated tables.

  Recommendation: Even with the 04E ABEND during a load or during an update of replicated tables, the replication process will not be affected because the abend  occurs during the termination of a single log-reader task connection. A new connection will be started automatically afterwards. No additional process is needed to rerun the task or to restart the server. To avoid the issue, run multiple load jobs to load individual objects rather than a single load job to load multiple objects.

Incremental updates

• The time needed to reload a set of incrementally updated tables increases as well as the replication latency.

  Recommendation: Pause after each reload, as this allows the system to come back to normal throughput rates and latency levels.

• Long load scenarios, during which a continuous replication setup collects about 24 million rows, might cause the integrated synchronization function to run out of memory.

  Recommendation: Try a reload. If the problem reoccurs, load the table with lock mode tableset.

• Replication using Integrated Synchronization fails with "Detected LSN moving backwards! current LSN: < lrsn1 > previous LSN: < lrsn2 >" and might set the table into error state.

  Recommendation: Avoid loading many replicated tables at the same time. If the table is in error state, re-enable replication and restart the load.

Monitoring

• The values of the monitoring counter Q8STTMUD are too high, for example 0.18E+20.

  Recommendation: Ignore the high value. It will be corrected during the next collection of statistics.

• The monitoring values of the Q8STCQL, Q8STCQLS, and Q8STQUEW metrics might be displayed incorrectly.

Tracing

• The information collected by the apdiag tool is not included in the trace archive of the appliance.

  Recommendation: Contact IBM L3 support and ask if this information is really needed.

Miscellaneous

• Under a very high system load caused by the parallel execution of various operations, such as 'Add Tables', 'Remove Tables', and 'Alter Keys', the accelerator might restart unexpectedly, leading to the abortion of running tasks or queries.
  Symptoms are aborted tasks, lock timeout exceptions, or messages with SQL code -911, SQLSTATE=40001, and rc=68.

  Recommendation: Reduce the task diversity of parallel jobs if you process big table sets with more than 100 tables. For example, do not run 'Alter Keys', 'Add Tables', and 'Remove Tables' operations at the same time. Reduce this to 'Alter Keys' and 'Add Tables' jobs or any other combination of just two different tasks.
  If a query failed, rerun the query.

• Error when removing multiple tables in parallel. The following error message is "AQT20118E: A distributed commit between the stored procedure and the server failed for the following reason: Distributed commit failed while initiator was preparing for commit".

  Recommendation: Repeat the operation.

Issues specific to IBM Db2 Analytics Accelerator on Z

• During the replication of changes based on Integrated Synchronization the accelerator may run out of disk space in rare cases.

  Recommendation: As mitigation reload the table and open a PMR for further assistance.