IBM Support

IBM Db2 Analytics Accelerator for z/OS V7.1.0.2: Release Notes

Release Notes


Abstract

The release notes contain the latest information about IBM Db2 Analytics Accelerator for z/OS V7.1.0.2

Content



What's new

IBM® Db2® Analytics Accelerator for z/OS®, V7.1 delivers two new deployment options on either the recently announced IBM Integrated Analytics System or directly on the new IBM Z mainframe. Both of these deployment options are based on the premier IBM analytical engine, that is, Db2 Warehouse software. This guarantees a uniform experience. You can invest once to enable your applications and workloads for acceleration and then transition easily between the two deployment options.

  • Support for the newly announced IBM Integrated Analytics System:
    • A pre-configured offering that includes hardware (computers, storage, and networking) and software for an easy deployment, management, and a high performance.
    • It offers secure, flexible and elastic data storage that is both easy to deploy and easy to manage.
  • Support for the newly announced IBM Z mainframe as part of Integrated Facility for Linux® (IFL) processors, and without the need to attach a separate appliance. This accelerator on IBM Z deployment option offers you:
    • A deeper integration with the IBM Z infrastructure. This offers a unified homogeneity of service, support and operations, and a deeper integration with operational processes, such as disaster recovery.
    • Flexible capacity to respond to peak analytic workload requirements while meeting SLAs.
    • Allows you to start with an Accelerator environment deployed on IBM Z and seamlessly grow into a larger IBM Integrated Analytics System deployment, as needed.


Migration considerations

There are no migration considerations.


Updates

IBM Db2 Analytics Accelerator on IBM Z

The updated image 7.1.0.1-Information_Management-IDAA-Z contains a fix for the following problem:

  • SQL queries with large result sets may fail with an error (DSNT408I SQLCODE = -904, SQLSTATE = 57011).

The updated image 7.1.0.2-Information_Management-IDAA-Z contains fixes for the following problems:

  • The accelerator does not restart properly and cannot be reached over the network anymore after a "reset with wipe data" operation or an update installation.
  • If an accelerator is run for a longer period of time, internal log files fill up allocated disk space, which causes the accelerator to stop and to revert to the "not initialized" state.
  • The accelerator ignores changed memory sizes


Known Issues and restrictions

Deployment option independent

General recommendations

  • Stored procedure calls that add, load, or drop many thousand tables at once might hang or fail.

    Recommendation
    Use table sets of no more than 5000 tables.

  • Under very high load the accelerator may appear slow or might run into timeouts.

    Recommendations
    Do not run more than:

    • 30 query threads in parallel.
    • 30 load threads in parallel.
    • 15 mixed threads in parallel like adding tables, loading or reloading tables, removing tables, retrieving table information.
    • 10 load threads with 20 query threads in parallel.

Add tables

  • The stored procedure SYSPROC.ACCEL_ADD_TABLES fails with the message:
    DSNT408I SQLCODE = -913, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C9008E

    Recommendation
    Try to add the tables again and eventually reduce the size of the table set or reduce the number of operations executed in parallel on the accelerator.

  • If the stored procedure SYSPROC.ACCEL_ADD_TABLES is called with a table set containing more than 5000 tables, the accelerator might restart and a core dump is written.

    Recommendation
    Reduce the number of tables contained in the table set.

Create accelerator-only tables

  • Creating an accelerator-only table fails with a message indicating that the accelerator-only table is not available.
    This behavior shows up during daylight-saving time.

    Recommendation
    Wait for end-of-the-time switch and recreate the accelerator-only table.

Query

  • A query runs for hours while the reload of a referenced table is in progress.

    Recommendation
    Cancel the query and wait until table statistics have been generated for new or reloaded tables.

  • Queries might not be routed to a newly paired accelerator shortly after the pairing. THE STATEMENT CANNOT BE EXECUTED BY DB2 OR IN THE ACCELERATOR (REASON 13). SQLCODE=-4742, SQLSTATE=560D5, DRIVER ...

    Recommendation
    Wait 30 seconds after pairing a new subsystem with an accelerator before you route queries to it.

  • Queries do not return results after the following sequence of events:
    • The reload of a table with the option forceFullReload=true
    • The termination of the load process through task cancellation
    • A reload of the same table with the option forceFullReload=false

    Recommendation
    Remove the affected table from the accelerator and add it again.

  • Queries end with an error message as shown below when submitted while an ALTER TABLE command or a table load operation is in progress.:
    SQL0727N An error occurred during implicit system action type "3". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "DB2INST1.DBNAME". SQLSTATE=56098

    Recommendation
    Don't issue SQL queries while an ALTER TABLE command is running. If the table is empty after the alteration, reload it.

Load tables

  • If many load requests, that is, calls to the stored procedure SYSPROC.ACCEL_LOAD_TABLES are requested in parallel the stored procedures might hang, and no more Db2 connections are allowed.

    The number of additional Db2 connections for the stored procedure is 4 times the AQT_MAX_UNLOAD_IN_PARALLEL value. If this exceeds the number of available connections in Db2 according to the CTHREADS ZPARM, the ACCEL_LOAD_TABLES procedure will hang and no more Db2 connections are allowed.

    Recommendation
    Set AQT_MAX_UNLOAD_IN_PARALLEL to a lower value, or do not run the stored procedure SYSPROC.AQT_ACCEL_LOAD_TABLES in parallel. Consider fewer calls with more tables.

  • The stored procedures SYSPROC.ACCEL_LOAD_TABLES fails with SQLCODE=-430 or SQLCODE=-471 if many stored procedures are running at the same time.

    Recommendation
    Reduce the NUMTCB setting for the WLM address space to 30 or less to avoid this situation.

  • A table load fails if the stored procedure SYSPROC.ACCEL_LOAD_TABLES is called repeatedly with 50 or more tables in the table set.
    Error message:
    AQT10050I - An internal error occurred on the '[...]' accelerator: ODBC operation SQLExecDirect failed with code -1 when Executing statement: /* IBM_DWA */ CREATE OR REPLACE VIEW "DWA[...]"."[...]" AS
    SELECT [...] FROM "DWADWBDB12"."OID-ID_15368-V1"
    WHERE "DWA_Partition_ID (hidden)" <= 100000:
    <ODBCDiagnostics>
        <SQLSTATE>40001</SQLSTATE>
        <SQLCODE>-911</SQLCODE>
        <Tokens num="1">
            <Token>68</Token>
        </Tokens>
        <Message>[IBM][CLI Driver][DB2/LINUXX8664] SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".  SQLSTATE=40001</Message>
    </ODBCDiagnostics>
    
    - or - 
    
    AQT10071I - The accelerated table "[...]"."[...]" is in 'INITIAL LOAD IN PROGRESS' state, which does not allow the attempted operation. Diagnostic information: operation: 'State change to UPDATE IN PROGRESS by task of type 2'
    

    Recommendation
    Run SYSPROC.ACCEL_LOAD_TABLES once more after a short period of time. If it fails again, remove the table from the accelerator and add it again.

  • The stored procedure SYSPROC.GET_TABLES_INFO fails with the message AQT10200E as in this example::
    AQT10200E - The FETCH operation failed. Error information: "DSNT408I SQLCODE = -183, ERROR: AN ARITHMETIC OPERATION ON A DATE OR TIMESTAMP HAS A RESULT THAT IS NOT WITHIN THE VALID RANGE OF DATES DSNT418I SQLSTATE = 22008 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXRDTF SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -795 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFCE5' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION ". The unsuccessful operation was initiated by the following statement: "SELECT A.NAME, A.CREATOR, A.REMOTENAME, A.REMOTECREATOR, A.ENABLE, A.ARCHIVE, A.SUPPORTLEVEL, B.TYPE, B.ALTEREDTS-CURRENT TIMEZONE, A.ALTEREDTS-CURRENT TIMEZONE,HEX(B.DBID) || HEX(B.OBID), B.VERSION INTO :h :h, :h, :h, :h, :h, :h :h :h :hFROM SYSACCEL.SYSACCELERATEDTABLES A LEFT OUTER JOIN SYSIBM.SYSTABLES B ON A.NAME=B.NAME AND A.CREATOR=B.CREATOR WHERE ACCELERATORNAME = :accelName AND A.NAME = :h AND A.CREATOR = :hFETCH FIRST 1 ROWS ONLY".

    Recommendation
    Run the following query: SELECT NAME FROM SYSIBM.SYSTABLES WHERE ALTEREDTS='0001-01-01 00:00:00'

    Remove the tables that are output by this query, that is,
    call the stored procedure SYSPROC.ACCEL_REMOVE_TABLES for these tables.

Remove tables and remove accelerator

  • A table removal fails with the error message AQT10076W, which indicates that an entry for the table does not exist in the table SYSACCEL.SYSACCELERATEDTABLES, but that the table is none the less present on the accelerator.

    Recommendation
    Remove the table by using IBM Db2 Analytics Accelerator Studio or the stored procedure SYSPROC.ACCEL_REMOVE_TABLES.

  • After an accelerator restart, a table cannot be removed because the DB2 catalog table SYSACCEL.SYSACCELERATEDTABLES and the accelerator catalog are out of sync with regard to the specified table.

    Recommendation
    Restart the accelerator by using the dwa-admin restart option on the Db2 Analytics Accelerator Console.

  • The removal of an accelerator or the removal of a large set of tables might fail with an error message:
    AQT10050E An internal error occurred on the accelerator: Lock for object for transaction in mode "SHARED" has not been granted within timeout "1800000"

    Recommendation
    Wait until the previous operation has finished or cancel the corresponding task. If a table removal failed for a large set of tables, reduce the number of tables in the set..

Monitoring

  • An incorrect table size is shown in IBM Db2 Analytics Accelerator Studio and IFI records.

Tracing

  • If an accelerator is restarted during a table load, very large trace files are created. As a consequence, the collection of trace information takes a long time.

    Recommendation
    Do not restart an accelerator while the stored procedure SYSPROC.ACCEL_LOAD_TABLES is running.

Miscellaneous

  • Stored procedures are not cancelled after closing IBM Db2 Analytics Accelerator Studio

    The DISPLAY THREAD command on the z/OS console shows active threads although IBM Db2 Analytics Accelerator Studio has been closed.

  • The accelerator server might crash if it is shut down while asynchronous load operations are running. Symptoms: the server will be restarted and a core dump will be created.

    Recommendation
    Do not shut down the server from the Db2 Analytics Accelerator Console as long as asynchronous load operations are running. (Check this with the List tasks function before.)

Issues specific to IBM Db2 Analytics Accelerator on IBM Z

Query

  • The accelerator terminates connections and ultimately reboots if the query workload is too high.

    Recommendation
    If the accelerator does not recover within several minutes, restart the accelerator from IBM Db2 Analytics Accelerator Studio.

  • The accelerator goes offline when many queries are running in parallel and produce a high workload.
    System log shows: "Accelerator is not online", "System went from state 'ONLINE' to 'UNKNOWN'

    Recommendation
    After some time, the Accelerator will restart services and come online again. Then rerun the affected queries.

Tracing

  • The Save Trace function returns the message "Error occurred during trace collection" if the option "Appliance" has been selected.

    Recommendation
    Deselect the "Appliance" option when if you want to capture trace information about IBM Db2 Analytics Accelerator on Z. Appliance-specific diagnostic information has to be captured with the Appliance UI (Dump).

Miscellaneous

  • You cannot retrieve the serial number from IBM Db2 Analytics Accelerator Studio in order to prepare a support session with WebEx and ssh.

    Recommendation
    Generate a service password after the attempt to connect with ssh.

Restrictions

  • A gateway can only be specified for one of the defined network interfaces, which then becomes the default route for the system. An interface that does not have a gateway set is only accessible from systems on the same subnet as that interface.

    Recommendation
    Use a single gateway definition for a router and connect Db2 subsystems in different subnets to this router.

  • The accelerator does not support a combination of FICON DASD and FCP storage devices within the same storage pool.

    Recommendation
    Use only FICON DASD or only FCP storage devices for a specific storage pool definition.

Issues specific to IBM Db2 Analytics Accelerator Studio

  • A refresh of the accelerator view takes very long after adding a very high number of tables in a short time.

[{"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":"7.1.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 August 2018

UID

swg27050681