The message parameter

The message parameter is used by all IBM® Db2® Analytics Accelerator stored procedures. It serves as an input parameter and as an output parameter.

The message parameter is always used as an output parameter and produces an XML string with information about the success or failure of a stored-procedure run. The structure of this string conforms to that of the messageOutput element in the SAQTSAMP(AQTXSD1) data set.

You can use the message parameter as an input parameter for a variety of purposes.
The input string must conform to the structure of the <messageControl> element in the SAQTSAMP(AQTSXSD1) data set. The message parameter is defined as an INOUT character large object (CLOB) with a maximum size of 64 KB. If the 64 KB CLOB is unable to contain all <message> elements, some of these elements are omitted. Warning AQT10004W is issued in cases like this; it shows the number of skipped messages. To see all messages in cases where the 64 KB CLOB does not suffice, open the result set identified by the MESSAGES_CURSOR. This result set always contains the whole set of messages.
Important: NULL, an empty string, or space characters are allowed as an input value for the message parameter. In this case, the stored procedure uses a default value.

The default values are defined in the SAQTSAMP(AQTDEF6) data set member. This data set member is loaded by the AQTDEF6 DD statement in the Workload Manager (WLM) startup job.

Controlling the trace behavior

To control the trace behavior and write trace information about the stored procedure to a result set or file. To do so, you specify an input XML string that uses the <traceConfig> element:
Example A: 
<?xml version= "1.0" encoding= "UTF-8" ?>
<spctrl:messageControl xmlns:spctrl = "http://www.ibm.com/xmlns/prod/dwa/2011" 
 version = "1.2" >
   <language>en_US</language> <!-- currently ignored -->
   <traceConfig location = "/tmp/spcreate.trace" keepTrace = "true" 
    traceFileSizeInMB = "10" >

      <component name = "PROCEDURE" level = "DEBUG" />
   </traceConfig>
</spctrl:messageControl>

If you use this example, stored-procedure trace information is written to the /tmp/spcreate.trace file in the z/OS® UNIX file system (zFS) because the location attribute is set to this value. The trace file is allowed to grow to a size of 10 MB. When this size is exceeded, the oldest entries are removed from the trace file to free up space for the newest entries. The trace information will not be deleted after the stored-procedure run (keepTrace=True). The information is collected with a detail level of DEBUG.

Example B: 
<?xml version= "1.0" encoding= "UTF-8" ?>
<spctrl:messageControl xmlns:spctrl = "http://www.ibm.com/xmlns/prod/dwa/2011" 
 version = "1.2" >
   <language>en_US</language> <!-- currently ignored -->
   <traceConfig location = "/u/myTraceDirectory/" 
    keepTrace = "onError" traceFileSizeInMB = "10" >
      <component name = "PROCEDURE" level = "DEBUG" />
   </traceConfig>
</spctrl:messageControl>

In contrast to the previous example, the trace information is written to the /u/myTraceDirectory/ directory. A file name has not been specified, so the default name is chosen. The trace information is deleted if no error occurs. If an error causes the procedure to fail, the trace information is kept at the specified location (keepTrace=onError).

Tip: In an automated setup, it is favorable to configure tracing for stored procedures as shown in Example B, that is:
  • Specify a writable location, for example location="/u/someDirectory/" so that the trace information is written to a file. Trace files are much easier to read and interpret than result sets.

    You can also specify location="GENERATED". In this case, the trace file is written to the /tmp directory of the z/OS UNIX file system. The file name will be chosen at random.

  • Keep trace files in case of errors only by using the keepTrace="onError" attribute on the message input parameter of the stored procedures.

Setting the compatibility level

  • To set the compatibility level for the stored-procedure output. This way, you can exclude newer elements or attributes from the XML output, which might be necessary to keep the output compatible with self-written applications that were developed for earlier product versions.
    Example: 
    <dwa:messageControl xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.2">
  <compatibilityLevel>77</compatibilityLevel>
</dwa:messageControl>

    In this example, the compatibility level has been set to the value 77. This compatibility level makes the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure produce version 7.5.8 output. This output does not include the last_access_timestamp and access_count attributes because these attribute were introduced later, with product version 7.5.11.

    Table 1 lists recent compatibility levels and the (additional) attributes that were introduced with these levels.

    Table 1. Compatibility levels and attributes introduced with these levels
    Compatibility levels Additional output attributes by stored procedure Introduced with stored procedure version
    76
    SYSPROC.ACCEL_GET_QUERIES2
    • waitTimeHTAPSec
    • timeoutsHTAP
    		 7.5.7
    77
    SYSPROC.ACCEL_GET_QUERIES2
    • prepareTimeSec
    • sessionID
    SYSPROC.ACCEL_GET_TABLES_INFO
    • load_backend_statistics_collection_timestamp
    • archive_backend_statistics_collection_timestamp
    		 7.5.8
    79
    SYSPROC.ACCEL_GET_QUERIES2
    • applicationStallTime
    SYSPROC.ACCEL_GET_TABLES_INFO
    • last_access_timestamp
    • access_count
    		 7.5.11
    Note: The compatability levels are cumulative. That is, if you specify 79, you also get the output attributes that were introduced with earlier compatibility levels, such as 76 and 77.

    For a description of the listed output attributes, see the appropriate reference chapter about the stored procedure:

    To ensure that the stored-procedure output is compatible with future product versions, proceed as follows:
    1. Determine the current interface level of the stored procedures by running any IBM Db2 Analytics Accelerator stored procedure. For the message parameter, specify just the versionOnly attribute, with a value of true. The input XML code for the message looks like this:
      <?xml version= "1.0" encoding= "UTF-8" ?>
<spctrl:messageControl xmlns:spctrl = "http://www.ibm.com/xmlns/prod/dwa/2011" 
version = "1.0" versionOnly = "true" ></spctrl:messageControl>
    2. Make a note of the interface version number that is returned as the query result.
    3. Include this number as the value of the compatibilityLevel attribute in the message input parameter when your custom applications call IBM Db2 Analytics Accelerator stored procedures.

    Setting the compatibilityLevel attribute to the correct value, you need not change your custom applications after installing a newer version of the stored procedures.

Setting or overriding environment variables

To set or override environment variables temporarily on a per-call basis. Settings will be valid only for the duration of a stored-procedure call. The following environment variables can be set in this way:
  • AQT_MAX_UNLOAD_IN_PARALLEL
  • AQT_ARCHIVE_COPY1
  • AQT_ARCHIVE_COPY2
  • AQT_ARCHIVE_RECOVERYCOPY1
  • AQT_ARCHIVE_RECOVERYCOPY2

Example:

<?xml version= "1.0" encoding= "UTF-8" ?>
<aqttables:messageControl 
 xmlns:aqttables = "http://www.ibm.com/xmlns/prod/dwa/2011" version = "1.2">
   <traceConfig>
      <component name = "PROCEDURE" level = "INFO" />
   </traceConfig>
   <environment>
      <environmentVariable name = "AQT_MAX_UNLOAD_IN_PARALLEL" value = "2" />
   </environment>
</aqttables:messageControl>