Use IBM DB2 Analytics Accelerator (IDAA) with IBM Capacity Management Analytics version 1.2 or IBM Capacity Management Analytics on z/OS version 1.2

Resolving The Problem

Applying the required APARs

To use IDAA with Capacity Management Analytics, you must apply the additional APARs.

For more information about required APARs for Capacity Management Analytics 1.2 Fix Pack 2, see Required fix packs and APARs for Capacity Management Analytics 1.2 (http://www.ibm.com/support/docview.wss?uid=swg27044094).

Ensuring that all database objects use EBCIDIC encoding

To use IDAA, you must also ensure that all database object's (the database, tables, tablespaces, UDFs, and stored procedures for both DRL and HCM databases) CCSID use EBCIDIC encoding. For example, the following function is encoded in EBCIDIC:

CREATE FUNCTION HCM.YEAR_BAND(d date)
RETURNS DATE
LANGUAGE SQL
PARAMETER CCSID EBCIDIC
DETERMINISTIC
READS SQL DATA
NO EXTERNAL ACTION
RETURN
(
  date ( varchar(year(d)) || '-01-01')
);

If you are not using EBCIDIC, you must do the following:

1. Backup your Capacity Management Analytics database.
2. Change the CCSID UNICODE in all SQL scripts to your database CCSID.
3. Recreate and configure your Capacity Management Analytics data store.

If all database objects use EBCIDIC and you are migrating to Capacity Management Analytics 1.2 Fix Pack 2, you must change the UDF PARAMETER CCSID in each UDF script to be the encoding scheme of the tables. Then you can run the scripts to create and configure your Capacity Management Analytics data store.

After you have applied the required APARs and ensured that the database tables use the same encoding, you must do either of the following to enable IDAA for Capacity Management Analytics:

• Option 1: enable acceleration by setting the appropriate ZPARMS in your DB2 configuration
• Option 2: enable IDAA within IBM Cognos Business Intelligence and IBM SPSS Modeler

Option 1: Setting ZPARMs for IDAA in DB2 for z/OS:

For more information, see the IBM DB2 Analytics Accelerator documentation and Enabling DB2 to work with IBM DB2 Analytics Accelerator for z/OS.

If you have already set QUERY_ACCELERATION to ALL, then all queries will use IDAA and you do not have to do any additional configuration.

If you are using another setting, some queries might not use IDAA. In this case, you can enable IDAA in IBM Cognos Business Intelligence and IBM SPSS Modeler (option 2 below).

Option 2, part 1: Enabling query acceleration in IBM Cognos Business Intelligence:

If you do not have DB2 ZPARM acceleration enabled, you can enable query acceleration by adding open session command blocks to the data source connection in IBM Cognos Administration. For more information see the "Setting the query acceleration register from IBM Cognos BI" section in Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS.

1. Open the IBM Cognos Business Intelligence home page, and click Launch, IBM Cognos Administration.
2. On the Configuration tab, select Data Source Connections.
3. Click the data source name.
4. Click the Set properties icon.
5. On the Connections tab, click Set beside Open session commands.
6. Enter the following in the XML database commands box:
   <commandBlock>
   <commands>
   <sqlCommand>
   <sql>SET CURRENT QUERY ACCELERATION ALL</sql>
   </sqlCommand>
   </commands>
   </commandBlock>
   
   It is recommended that you set the value to ALL, but you can set it to the value that is more appropriate for your environment. See the "Setting the query acceleration register from IBM Cognos BI" section in Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS for more information.
   
   If this data source is shared among other Cognos application, all queries would use the IDAA. If data from those shared applications is not also loaded into IDAA, then those reports may not complete. If loading the data for the shared applications into IDAA is not appropriate for your environment, consider setting up a second data source.
7. Click OK to close the dialog boxes.

Option 2, part 2: Enabling IDAA in IBM SPSS Modeler:

If you do not have DB2 ZPARM acceleration enabled, you can enable IDAA in SPSS Modeler. Enabling IDAA for an SPSS data source will accelerate all of your streams.

You cannot use the SDAP driver with IDAA.

1. Catalog the IDAA data source.
2. Edit the odbc.ini file to connect to the IDAA data source. Ensure that the data source name is the same as the database alias name that you used when you cataloged the database. For more information, see Configuring IBM SPSS Modeler Server with DB2 CLI/ODBC on Linux on System z operating systems.
3. Follow the steps in Enabling Integration with IBM DB2 Analytics Accelerator for z/OS.
   After you modify the odbc-db2-accelerator-names.cfg file in the Enabling the integration of IBM® DB2 for z/OS® in IBM SPSS Modeler task.
4. Restart SPSS Modeler Server.

Verifying that IDAA is being utilized:

You can verify that the query or stream was run with IDAA by using IBM Data Studio or IBM z/OS System Display and Search Facility (SDSF) on the LPAR where the database resides.

You can verify that a query or stream was run using IDAA from IBM Data Studio by selecting Accelerator. The executed queries or streams will be displayed.

If you use SDSF, you can run the following command to verify if a Cognos query or an SPSS Modeler stream was run using IDAA:
-DatabaseName DISPLAY THD(*) ACCEL(*)

For a Cognos query, the expected result would be something like:

DSNV401I -DatabaseName DISPLAY THREAD REPORT FOLLOWS -
DSNV402I -DatabaseName ACTIVE THREADS - 697
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER AC * 23 db2jcc_appli COGADM DISTSERV 0071 4185
V437-WORKSTATION=name
USERID=cogadm
APPLICATION NAME=db2jcc_application
V442-CRTKN=::IP_address.36684.CFD341BE1A54
V445-G91E8B0B.OF4C.CFD341BE1A54=4185 ACCESSING DATA FOR
::IP_address
V444-G91E8B0B.OF4C.CFD341BE1A54=4185 ACCESSING DATA AT
ZGRYPHO2-::IP_address..1400
DISPLAY ACTIVE REPORT COMPLETE

For an SPSS stream, the expected result would be something like:

-DatabaseNameDISPLAY THD(*) ACCEL(*)
DSNV401I -DatabaseNameDISPLAY THREAD REPORT FOLLOWS -
DSNV402I -DatabaseNameACTIVE THREADS - 606
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER RA * 230 modelerserve COGADM DISTSERV 0071 10446
V437-WORKSTATION=ADMINIB-7ILJPF1
USERID=cogadm
APPLICATION NAME=modelerserver.exe
V442-CRTKN=IP_address.61028.151111053635 V445-G9778346.EE64.151111053635=10446 ACCESSING DATA FOR ::IP_address V444-G9778346.EE64.151111053635=10446 ACCESSING DATA AT ZGRYPHO2-::IP_address..1400
DISPLAY ACTIVE REPORT COMPLETE
DSN9022I -DatabaseNameDSNVDT '-DISPLAY THD' NORMAL COMPLETION