SYSPROC.ACCEL_CONTROL_ACCELERATOR

Syntax

CALL SYSPROC.ACCEL_CONTROL_ACCELERATOR
(accelerator_name,
 command,
 result,
 message)

Options description

accelerator_name

The unique name of the accelerator. This accelerator must have been defined by the SYSPROC.ACCEL_ADD_ACCELERATOR2 stored procedure.

command

An XML input string that specifies the functions that you want to use. The string must conform to the structure of the controlCommand element in the SAQTSAMP(AQTSXSD1) data set. The command parameter is an input parameter, which is defined as a CLOB with a maximum size of 256 KB.

Each element is linked to a user-defined function, for which access rights can be set individually. This allows you to grant the right to run specific functions of the stored procedure selectively. If a user does not have the right to use a particular element, that is, is not allowed to run the user-defined function invoked by the element, a message similar to this one is returned in the output XML string:

<?xml version="1.0" encoding="UTF-8" ?>
<dwa:messageOutput xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0"> 
   <message severity="error" reason-code="AQT10308I">
      <text>The user-defined function "DSNAQT.ACCEL_CONTROL_GETACCELERATORINFO" 
            lacks EXECUTE authority for the caller of the stored procedure 
            "SYSPROC.ACCEL_CONTROL_ACCELERATOR". 
            This procedure was called with an input parameter value for 
            "COMMAND" that requests the execution of the sub-function 
            "getAcceleratorInfo".
       </text>
       <description>The requested sub-function within the stored procedure 
            requires EXECUTE authorization on a separate user-defined function 
            in Db2.
       </description>
       <action>Grant EXECUTE authority for the user-defined function to the 
            caller of the stored procedure.
       </action>
   </message>
</dwa:messageOutput>

XML Elements that can be used in the command parameter:

<cancelTasks>

Allows you to cancel a task that is running on the accelerator. The function requires the task ID as input. Provide the task ID in the <cancelTasks> element.

Example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
	<cancelTasks>
		<task id="24" />
	</cancelTasks>
</aqt:controlCommand>

<clearTraceData>

Deprecated. It can still be run to avoid compatibility issues with product version 5.1.x, but has no effect in connection with product version 7.x.x. In version 5.1.x, this sub-function is used to remove trace data from the accelerator, such that the result of the next <getTrace> request does not contain previously collected data. The following files are removed:

• Trace files (because new trace files are created)
• Core dumps

Draft comment: kuester
Replication logs (if incremental updates are configured)

Example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqttables:controlCommand 
 xmlns:aqttables="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.2" >

       <clearTraceData/>

</aqttables:controlCommand>

<getAcceleratorInfo>

Retrieves status information about the accelerator. This information includes software version numbers and accelerator state information. You can also set an optional attribute, includeEncryptionInfo = "true", which will include information about the encryption of data in motion. The information that is returned by the result output parameter is included in an XML element called acceleratorInfo, which is a child element of the root element controlResult. For more information, follow the link to the AQTSXSD1 data set at the end of this topic.

Example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqttables:controlCommand 
 xmlns:aqttables="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.4" >

       <getAcceleratorInfo includeEncryptionInfo = "true" />

</aqttables:controlCommand>

The output looks similar to the XML block in the following example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlResult 
 xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">
  <acceleratorInfo state="Online" 
   authenticationTokenTimestamp="2017-11-24T08:39:40.545249Z" 
   currentAcceleratorTimestamp="2017-11-24T08:44:35.196719Z" 
   activeTraceProfile="DEFAULT" > 
   
     <versionInformation> 
        <component name="Accelerator" version="7.1.0.201711241547" /> 
        <component name="Accelerator container" 
         version="ibmdashdb/local:v2.0.0-20171124-2243" /> 
        <component name="Access Server" version="11.4.10204" /> 
        <component name="Appliance software" 
         version="1.0.0.0 [Build 20171124154706]" /> 
        <component name="BackendDBS" version="11.1.9.0 
         [Build 1724041600]" /> 
        <component name="Linux Operating System" 
         version="CentOS Linux release 7.2.1511 (AltArch)" /> 
        <component name="Replication BackendDBS JDBC Version" version="" /> 
        <component name="Replication DB2 JDBC Version" version="" /> 
        <component name="Replication Engine" version="11.4.0.Head_5432" /> 
     </versionInformation> 

     <acceleratorSetting name="ENCRYPTION_AT_REST" value="enabled" />
     <acceleratorSetting name="ENCRYPTION_IN_MOTION" value="enabled" />
     <acceleratorSetting 
      name="ENCRYPTION_IN_MOTION_CERTIFICATE_LIFETIME" value="90" />
     
     <acceleratorSetting name="MAXIMUM_NUMBER_CONCURRENT_RUNNING_SQL_TRANSACTIONS" 
      value="110" /> 
     <acceleratorSetting name="SERIAL_NUMBER" value="4284629" /> 
     <acceleratorSetting name="SOFTWARE_UPDATE_APPLY" value="enabled" /> 
     <acceleratorSetting name="SOFTWARE_UPDATE_DEPLOY" value="enabled" /> 
     <acceleratorSetting name="SOFTWARE_UPDATE_LIST_DEPLOYED" 
      value="enabled" /> 
     <acceleratorSetting name="SOFTWARE_UPDATE_REMOVE_DEPLOYED" 
      value="enabled" />
     <acceleratorSetting name="LAST_REPLICATED_CHANGE_TIMESTAMP" 
      value="2018-03-07 02:46:57.045000" /> 
     <acceleratorSetting name="REPLICATION_SUBSCRIPTION_NAME" 
      value="DWAS45UY" /> 

     <replicationInfo state="STARTED" 
      lastChangeTimestamp="2016-09-30T23:45:00.123456" 
      latencyInSeconds="60">
         <sourceAgent insertCount="1" updateCount="2" deleteCount="3" />
         <targetAgent insertCount="11" updateCount="21" deleteCount="31" 
          spilledRows="1234" spilledRowsApplied="234"/>
     </replicationInfo>
    
      <encryptionInformation>
         <encryptionOfDataInMotion>
           <certificates></certificates>
           <peers></peers>
          </encryptionOfDataInMotion>
       </encryptionInformation>
 
  </acceleratorInfo>
</aqt:controlResult>

Notes:

• Naturally, the setting of the includeEncryptionInfo attribute can have an effect only if encryption of data in motion is enabled.
• The <replicationInfo> block is shown only if the incremental update function is enabled.
• The lastChangeTimestamp attribute in the <replicationInfo> block shows the last time that the incremental update status changed from Started to Stopped or vice versa.
• The replication latency is disclosed through different channels, at different points in time. Sometimes, it is calculated by different systems. Values are reported:
  • In the Accelerator view as described in the previous table
  • When you run the Db2 command -DIS ACCEL(<name>) DETAIL
  • In trace files:

    <Event id="61098" timestamp="2018-11-28 18:07:51" level="W" addressee="0" >
            The current replication latency of XXXXXs  on DB2 location ADB2K04 (subscription DWE9PLVY) 
            has exceeded the threshold of 3600s.
    </Event>

  • In the status monitoring counter Q8STCRL
  • As a result of running the SYSPROC.ACCEL_CONTROL_ACCELERATOR stored procedure in connection with the <getAcceleratorInfo> parameter.

  These systems or reporting channels are not synced, so deviating values are inevitable. Deviations can be considerable, depending on the channel that is tapped and on the time when this is done. Stick to one reporting channel if you need to compare latency values.

<getAcceleratorTasks>

Retrieves a list of the tasks that are running on the accelerator. This information, especially the task IDs, can be used to cancel specific tasks. The information that is returned by the result output parameter is included in an XML element called acceleratorTasks, which is a child element of the root element controlResult. For more information, follow the link to the AQTSXSD1 data set at the end of this topic.

Example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.2">
	<getAcceleratorTasks/>
</aqt:controlCommand>

<getActivationLog>

Retrieves information about previous software activations by the SYSPROC.ACCEL_UPDATE_SOFTWARE2 stored procedure (ActivateDeployedPackage subcommand).

Software updates are usually activated asynchronously, that is, after the completion of the SYSPROC.ACCEL_UPDATE_SOFTWARE2 stored procedure. If the accelerator cannot be reached during or after the activation of software updates, you can use this subcommand to check the activation progress and retrieve the latest log information.

Restriction: You can use this subcommand only if you are running IBM Db2 Analytics Accelerator on an IBM Integrated Analytics System. The subcommand fails if it is run against a Db2 Analytics Accelerator on Z.

If a software activation is ongoing at the time you run the stored procedure with the <getActivationLog> command, message AQT10518I is issued to indicate this.

In addition, the stored procedure reports the activation status by issuing an AQT10517I message for each affected component. Statuses are flagged as follows:

not_started

The activation has been scheduled, but not yet started.

started

The activation is currently in progress.

upgrade_skip_up_to_date

The activation of the update is unnecessary because the component is already up-to-date.

install_complete

The activation has been completed.

postinstall_complete

The activation has been completed and checks were applied successfully.

After a successful activation of software updates, the accelerator reboots. This takes about 30 minutes. If you run the stored procedure during that time, communication error AQT10051E is returned.

You can set an additional attribute, diagnostics="true" to include additional information in the third result set (identified by MESSAGES_CURSOR).

The input XML code must conform to the structure of the controlCommand element in the SAQTSAMP(AQTSXSD1) data set. The <getActivationLog> element must be specified. See the following example:

Example:

<?xml version="1.0" encoding="UTF-8" ?>
  <aqt:controlCommand 
   xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">
    <getActivationLog diagnostics="true" />
  </aqt:controlCommand>

<getAdditionalSupport planID="<number>" traceCommand="<tcommand>" timeoutInMinutes="<number>"

Generates Db2 for z/OS support documents or CDE performance trace information (CDE stands for columnar data engine). CDE performance trace information also includes the Db2 support documents for a given plan ID.

The information you collect is related to individual queries. Therefore, you need to obtain the plan ID of the query first. Run SYSPROC.ACCEL_GET_QUERIES2 for this purpose. You can use, for example, the following XML input string as the value of the query_selection parameter for SYSPROC.ACCEL_GET_QUERIES2:

<?xml version="1.0" encoding="UTF-8"?>
<dwa:querySelection xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
  <filter scope="all" />
</dwa:querySelection> 

In the result set, you will find the plan IDs of the queries run recently on the specified accelerator. For example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:queryList xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.4">
<query user="SYSADM" planID="0">

In this example, the plan ID is 0.

Depending on the type of information you want to collect, specify one of the following strings (<tcommand>) as the value of the traceCommand attribute:

DB2SUPPORT

To collect Db2 for z/OS support documents.

CDE_PERFORMANCE_TRACE

To collect CDE performance data and Db2 for z/OS support documents.

For the trace command CDE_PERFORMANCE_TRACE, you can also specify a timeout value on the timeoutInMinutes attribute. If the stored procedure run cannot be completed during the specified period (in minutes), it is aborted, and you receive an AQT10050E error that includes the following diagnostic information:

AQT10050E - An internal error occurred on the 'XYZ001' accelerator: 
ODBC operation executeQuery failed with SQLSTATE 22003 because
the timeout was reached, and the query execution was stopped. 
Nevertheless, trace information could be collected successfully.
<ODBCDiagnostics>
	<SQLSTATE>22003</SQLSTATE>
	<SQLCODE>-904</SQLCODE>
	<Tokens num="0">
	</Tokens>
	<Message></Message>
</ODBCDiagnostics>

Input string example (value of command parameter):

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
    <getAdditionalSupport planID="0" traceCommand="DB2SUPPORT" />
</aqt:controlCommand > 

Output example:

The result set shows the file paths of the compressed trace files:

<?xml version="1.0" encoding="UTF-8" ?><aqt:controlResult 
 xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
   <additionalSupport planID="0" 
    absoluteAdditionalSupportZipFilePath="/head/dwa/var/log/additional_support_info/
    cdePerformanceAndDb2SupportForTask_314_PlanId_0.zip" 
    fileSize="5463619"/>

Note: You can also use an option in IBM Db2 Analytics Accelerator Studio to collect CDE performance and system performance trace information. In the Save Trace window, select Manually collected diagnostic information.

<getAdditionalSupport traceCommand="SYSTEM_PERFORMANCE_DATA" topQueries="<number>"

To collect system performance data. This includes information about the utilization of the accelerator and the backend database engine, as well as information about SQL statements, accelerator tasks, and so on.

In addition, you can specify the topQueries attribute. If you set this attribute to an integer, say 5, only the five most recent queries are considered. That is, the collection of performance data is limited to these five queries.

Input string:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
    <getAdditionalSupport traceCommand="SYSTEM_PERFORMANCE_DATA" 
     topQueries="5" />
</aqt:controlCommand > 

Output example:

The result set shows the file paths of the compressed trace files:

<?xml version="1.0" encoding="UTF-8" ?><aqt:controlResult 
 xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
   <additionalSupport  
    absoluteAdditionalSupportZipFilePath="/head/dwa/var/log/additional_support_info/
    system_performance_data0.zip" 
    fileSize="7160001"/>

Note: You can also use an option in IBM Db2 Analytics Accelerator Studio to collect CDE performance and system performance trace information. In the Save Trace window, select Manually collected diagnostic information.

<getReplicationEvents fromTimestamp="yyyy-mm-ddThh:mm:ss.µµµµµµZ"

toTimestamp="yyyy-mm-ddThh:mm:ss.µµµµµµZ" minSeverity="INFORMATION">

Creates a report consisting of log information that was collected during incremental update activities. You can restrict the information to be returned by defining a timeframe using the fromTimestamp and toTimestamp attributes or by specifying the minimum severity of the information using the minSeverity attribute. This attribute can take on the following values (sorted by restrictiveness; most restrictive value first):

ERROR

All information whose severity is classed as Error or worse

WARNING

All information whose severity is classed as Warning or worse

INFORMATION

All information whose severity is classed as Information or worse

The information that is returned by the result output parameter is included in an XML element called replicationEvents, which is a child element of the root element controlResult. For more information, follow the link to the AQTSXSD1 data set at the end of this topic.

Example: The output looks similar to this block of XML code:

<?xml version="1.0" encoding="UTF-8" ?>
   <aqt:controlResult xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
    version="1.1">
      <replicationEvents>
         <event id="123456" message="Something went terribly wrong. 
          I'll cancel the subscription." originator="SourceAgent" 
          time="2011-01-11T10:33:42.487678Z" severity="ERROR" />
         <event id="123453" message="Start the subscription 0815 now" 
          originator="TargetAgent" time="2011-01-09T08:21:42.487678Z" 
          severity="INFO" />
      </replicationEvents>
   </aqt:controlResult>

<getTraceConfig>

Retrieves information about the trace configuration from the accelerator. This information includes:

• The currently active trace profile
• A list of all available trace profiles

The information that is returned by the result output parameter is included in an XML element called traceConfig, which is a child element of the root element controlResult. For more information, follow the link to the AQTSXSD1 data set at the end of this topic.

<getTraceData>

Retrieves diagnostic information for IBM support from the specified accelerator:

This information does not include trace information about stored procedures. Tracing for stored procedures is controlled by the message INOUT parameter.

The trace file generated by the stored procedure is a compressed archive, which includes other files.

Draft comment:
Provide links to topics in which this is described.

The <content> element in the <getTraceData> element allows you to include or exclude certain types of information. The following values are supported:

Table 1. Possible values of the <content> element

Value	Description
ACCELERATOR	Collects accelerator and SQL engine logs, along with system diagnostics from the Docker container in which IBM Db2 Analytics Accelerator and the SQL engine run.
APPLIANCE	Collects system diagnostics from the physical environment of the Docker container, that is, the IBM Integrated Analytics System. Note: This option is only for IBM Db2 Analytics Accelerator on anIBM Integrated Analytics System. To generate similar trace information for Db2 Analytics Accelerator on Z, you must log in to the Admin UI of the Secure Service Container LPAR and create a dump. See Creating a trace file (dump)
ADDITIONALSUPPORTINFO	Retrieves support information collected during online support sessions with a client and saves these to file (one file per session). The files are saved to an ADDITIONALSUPPORTINFO folder. For more information, see Example C. In connection with this value, you can set the attribute sinceLastSaveOnly on the <getTraceData> parent element. Possible values are true or false. If set to true, the attribute causes the stored procedure to include only those support information files in the trace archive that were added since the previous generation of the archive. However, none of the older support information files are deleted from the ADDITIONALSUPPORTINFO folder; they are just not included in the trace archive to be generated. See Example D.
BACKENDENVIRONMENTFILE	Collects environment information about the target database on the accelerator if the element is specified in addition to ADDITIONALSUPPORTINFO.
CURRENTTASKS	Retrieves current accelerator task data, which is needed by the Current Tasks view of the accelerator monitoring function in your administration client.
DATAUSAGE	Retrieves information about currently processed data slices, which is needed by the Data Slices view of the accelerator monitoring function in your administration client.
SYSTEMUTILIZATION	Retrieves current accelerator usage data, which is needed by the accelerator monitoring function in your administration client.
SYSTEMUTILIZATIONHISTORY	Retrieves all available usage data for the selected accelerator. This data is used by the accelerator monitoring function in your administration client. This data is needed by the accelerator monitoring function in your administration client.
TASKMANAGER
TASKMANAGERHISTORY	Retrieves all available information about completed accelerator tasks. This information is needed by the Finished Tasks view of the accelerator monitoring function in your administration client.
REPLICATION	Retrieves information about the current replication workload and the current replication latency. This data is needed by the Replication view of the accelerator monitoring function in your administration client.
REPLICATIONHISTORY	Retrieves all available information about the replication workload and the replication latency. This data is needed by the Replication view of the accelerator monitoring function in your administration client.

Example A: In the following example, the <getTraceData> element causes IBM Db2 Analytics Accelerator to return trace information for both, ACCELERATOR and APPLIANCE, in a result set:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
    <getTraceData keepConfiguration="true">
        <content>ACCELERATOR</content>
        <content>APPLIANCE</content>
    </getTraceData>
</aqt:controlCommand>

Example B: This example uses the <getTraceData> element in the XML string submitted as the value of the command parameter and the <trace> element in the XML string submitted as the value of the message input parameter. The aim is to generate two result sets, one containing trace information about IBM Db2 Analytics Accelerator and the SQL engine, the other one containing trace information about the stored procedures:

XML string for the command parameter:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
    <getTraceData keepConfiguration="true">
        <content>ACCELERATOR</content>
    </getTraceData>
</aqt:controlCommand>

XML string for the message input parameter:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:messageControl 
xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">

   <traceConfig>
      <component name="PROCEDURE" level="DEBUG" />
   </traceConfig>

</aqt:messageControl>

Example C: In this example, the ADDITIONALSUPPORTINFO content element has been added to the <getTraceData> element in addition to ACCELERATOR and APPLIANCE. ADDITIONALSUPPORTINFO refers to a folder that holds information collected during WebEx support sessions with a client. The extra information is kept in this folder for a period of 30 days. If you use this element, the extra information is retrieved and written to the specified output location.

<aqt:controlCommand 
 xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">
    <getTraceData outputLocation="/u/bcke/trace.tgz">
       <content>ACCELERATOR</content>
       <content>APPLIANCE</content>
       <content>ADDITIONALSUPPORTINFO</content>
    </getTraceData>
</aqt:controlCommand>

The ADDITIONALSUPPORTINFO element does not work with product versions released before version 7.1.8. In older versions, the extra information that it provides is part of the output retrieved by the other two options (ACCELERATOR and APPLIANCE). Note, however, that even if you run version 7.1.8 with a back-level set of stored procedures, you must use the ADDITIONALSUPPORTINFO element, as it has become the only way to retrieve this kind of trace information.

Example D: In addition to ADDITIONALSUPPORTINFO, you can specify a BACKENDENVIRONMENTFILE element. Using this element will collect trace information about the environment of the target database on the accelerator. Code example:

<aqt:controlCommand 
 xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">
    <getTraceData outputLocation="/u/bcke/trace.tgz" sinceLastSaveOnly="true">
       <content>ADDITIONALSUPPORTINFO</content>
       <content>BACKENDENVIRONMENTFILE</content>
    </getTraceData>
</aqt:controlCommand>

The BACKENDENVIRONMENTFILE element can only be used if ADDITIONALSUPPORTINFO has also been specified.

Under the hood, the use of BACKENDENVIRONMENTFILE causes a run of the db2support -cl 0 command, which gathers the environment information about the target database.

The attribute setting sinceLastSaveOnly="true" ensures that only support information files added since the last generation of the trace archive are included in the next version of the trace archive.

<setTraceConfig>

Allows you to change the trace configuration of the accelerator. Trace profiles determine the accelerator components to be traced and the trace detail level. They also determine the approximate size of the compressed trace data when this data is gathered by the getTraceData function. Furthermore, a profile determines whether a trace record is immediately written to disk (flush) or if it is cached in memory for better performance. Trace profiles can be selected and activated. Furthermore, you can define new profiles and remove existing ones (see examples).

Several trace profiles are preconfigured on the accelerator. You can retrieve available trace profiles by using the <getTraceConfig/> element.

Example: The following XML code instructs the SYSPROC.ACCEL_CONTROL_ACCELERATOR stored procedure to remove a trace profile named DEBUG_CONNECTIVITY, add a new profile with the same name, and activate this new profile. The trace information that is collected by the DEBUG_CONNECTIVITY profile has a default verbosity level of INFO. The trace-file size is limited to 2 MB. Old trace information will not be overwritten when this limit is reached. When trace information about the DRDA, CONTROLLER, and REPLICATION components is collected, the verbosity level changes to DEBUG, which means that all available information about these components will be collected. In addition, debug information will be collected from the accelerator.

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011"
	version="1.0">

	<setTraceConfig>
		<removeTraceProfile name="DEBUG_CONNECTIVITY" />
		<addTraceProfile name="DEBUG_CONNECTIVITY" defaultLevel="INFO"
			traceFileSizeInMB="2" forceRecordFlush="false">
			<description>This is for debugging connectivity problems</description>
			<component name="DRDA" level="DEBUG" />
			<component name="CONTROLLER" level="DEBUG" />
      <component name="REPLICATION" level="DEBUG" />
		</addTraceProfile>
		<activateTraceProfile name="DEBUG_CONNECTIVITY" />
	</setTraceConfig>
</aqt:controlCommand>

Important: Using a trace profile other than the DEFAULT profile might lower the performance of the accelerator. Therefore, only use a different profile when instructed to do so by IBM support. Re-enable the DEFAULT profile when the support activities have been completed.

<startReplication/>

Starts the automatic transfer of data changes in Db2 for z/OS tables to the corresponding accelerator-shadow tables, provided that these tables have been enabled for incremental updates.

Example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand 
 xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">

   <startReplication/>

</aqt:controlCommand>

<stopReplication/>

Stops incremental updates.

Example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand 
 xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">

   <stopReplication/>

</aqt:controlCommand>

Restriction: If you stop replication inadvertently while a regular reload of a replication-enabled accelerator-shadow table is taking place (started by the load function or the SYSPROC.ACCEL_LOAD_TABLES stored procedure), the table state changes to Suspended after the load. That is, the table is treated like a faulty table. To re-enable incremental updates for this table, restart incremental updates on the accelerator and reload the table again.

<waitForReplication/>

Suspends the execution of the SYSPROC.ACCEL_CONTROL_ACCELERATOR stored procedure until pending incremental updates are applied. These are updates that have been recorded in the DB2® log, but not yet propagated to the accelerator at the time that the stored procedure was called. When this option is used, the stored procedure does not return results before the relevant tables are in sync with the DB2 log at the time the stored procedure was called. This ensures that the result data returned by SYSPROC.ACCEL_CONTROL_ACCELERATOR is based on the data matching this point in time. The incremental update function must have been started for this option to work properly.

You can specify an additional timeout attribute for this element (timeoutInSeconds). This prevents the execution of the stored procedure from being postponed endlessly, but has the adverse affect that you cannot be sure whether all pending updates have been applied. By default, this attribute is set to the value 0. Setting it to a value greater than 0 makes the stored procedure return results when the relevant data has been replicated, or after the passing of the specified period, even if incremental updates have not finished at that time.

Example: The following XML code delays the execution of the SYSPROC.ACCEL_CONTROL_ACCELERATOR stored procedure up to a maximum of ten minutes (600 seconds), which gives the incremental update function ten minutes to finish table synchronization. If the job cannot be completed during this period, the procedure returns results without waiting any longer for the incremental updates to finish.

<?xml version="1.0" encoding="UTF-8" ?>
   <aqt:controlCommand 
    xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">

      <waitForReplication timeoutInSeconds="600" />

   </aqt:controlCommand>

Restriction: This option is not available in connection with IBM Integrated Synchronization. To use this option, the chosen incremental update technology must be IBM InfoSphere® Change Data Capture for z/OS (CDC).

You can achieve a similar behavior in connection with IBM Integrated Synchronization if you run a WAITFORDATA query on a replicated table. When the query returns results, all committed data at a given point in time has been replicated, and you can be sure that SYSPROC.ACCEL_CONTROL_ACCELERATOR will take that data into account.

To this end, proceed as follows:

1. Create a dummy table, for example T_DUMMY in Db2 for z/OS and add a row to this table.
2. Add this table to an accelerator.
3. Enable replication for this table and load it.
4. Update the row in T_DUMMY.
5. Commit the update.
6. Submit the following SQL query:

   
   SET CURRENT QUERY ACCELERATION = ALL;
   SET CURRENT QUERY ACCELERATION WAITFORDATA n;
   SELECT COUNT(*) FROM T_DUMMY FOR FETCH ONLY;

   where n is the maximum time the query is postponed so that the latest incremental updates can be applied. It corresponds to the timeoutInSeconds attribute on the <waitForReplication/> element.

When the query results are returned, you have the guarantee that all table data on the accelerator matches the data in Db2 for z/OS at the time of the commit step in 5.

Suggestion: Write a Db2 stored procedure that includes the steps 4 through6 to preserve the details of the query.

For more information about WAITFORDATA queries, see Making queries wait for incremental updates.

result

Status and version information as a result of using the <getAcceleratorInfo> element.

Depending on the input parameter, this output parameter returns the following information:

Table 2. Output returned by result parameter

Input parameter (value of command)	Output
<getTraceConfig/>	Current trace configuration
<getAcceleratorInfo/>	Software version of IBM Db2 Analytics Accelerator for z/OS and internal status
<getAcceleratorTasks/>	List of the tasks running on the specified accelerator.
<getReplicationEvents/>	Report of incremental update activities

This input XML file must conform to the structure of the controlCommand element in the SAQTSAMP(AQTSXSD1) data set. If you do not use any of the input parameters in Table 2, the value of the result parameter is NULL.

message

For the description, follow the appropriate link under Related reference at the end of this topic.

Result sets

This stored procedure returns three result sets:

First result set (ACCELERATOR_TRACE_CURSOR)

If the sub-function <getTraceData> is used, the first result set contains the trace archive of the accelerator. If the sub-function is not used, the first result set will be empty. The cursor ACCELERATOR_TRACE_CURSOR identifies the first result set.

The information in the TRACEDATA column is used by IBM support for the analysis of error situations. It contains byte-encoded data. If the length of a value exceeds 32698 characters, it is split into multiple result set rows. The rows are concatenated in ascending order of SEQID.

Second result set (SP_TRACE_CURSOR)

Depending on the trace configuration in the message input parameter, this result set is empty or contains trace information about the stored procedure execution. It is identified by the cursor SP_TRACE_CURSOR and contains the following columns:

• SEQID of type INTEGER
• TRACEDATA of type VARBINARY, with a maximum length of 32698 characters

The information in the TRACEDATA column is encoded in UTF-8. It is intended for analysis by IBM support. If the length of a value exceeds 32698 characters, it is split into multiple result set rows. The rows are concatenated in ascending order of SEQID.

Third result set (MESSAGES_CURSOR)

This result set contains an XML string that contains the same messages as the MESSAGE output parameter. In contrast to the MESSAGE output parameter, the result set does not have a 64 KB size limitation. Therefore, it always contains the whole set of <message> elements (no truncation). The structure of the XML string conforms to that of the messageOutput element in the SAQTSAMP(AQTSXSD1) data set. The result set is identified by a cursor named MESSAGES_CURSOR and contains the following columns:

• SEQID of type INTEGER
• MESSAGES of type VARBINARY, with a maximum length of 32698 characters

The rows in the result set are concatenated in ascending order of the values in the SEQID column. The information in the MESSAGES column is encoded in UTF-8.

Prerequisites

• For the user ID that calls the stored procedures on z/OS, you must define an OMVS segment in the Resource Access Control Facility (RACF®).
• The accelerator_name parameter must specify an accelerator name that is listed in the catalog tables of the product.
  Draft comment:
  Provide link to SYSPROC.ACCEL_SETUP_CONNECTION.

Authorizations for z/OS

On z/OS, the user ID under which this stored procedure is run must have the following privileges:

• EXECUTE on the stored procedure
• MONITOR1 authorization (needed so that DSNWLIR can be used to run Db2 commands via the Db2 Instrumentation Facility Interface (IFI)).
• Read/write and execute access to the /tmp directory for the user who calls the stored procedure
• If an output file or data-set location is specified:

  Write access in RACF to the output data set for trace data or write access to the specified location in the z/OS UNIX file system.

• RACF ACCESS(READ) on the data set that contains the AQTENV file in the started task procedure of the Workload Manager (WLM) environment.
• RACF ACCESS(READ) on the data set that contains the AQTDEF6 file in the started task procedure of the Workload Manager (WLM) environment.
• EXECUTE on the DSNADM.DSNADMIZ package to allow access to system parameters when the SYSPROC.ADMIN_INFO_SYSPARM stored procedure is called.

Accessed data and systems

The SYSPROC.ACCEL_CONTROL_ACCELERATOR stored procedure reads the catalog tables of IBM Db2 Analytics Accelerator in your database management system.