SYSPROC.ACCEL_CONFIG_OPTIMIZATION_PROFILE

Details

To run this stored procedure successfully, optimization profile support must have been set up on the accelerator (target) database. For more information, contact IBM support.

Syntax

CALL SYSPROC.ACCEL_CONFIG_OPTIMIZATION_PROFILE
(accelerator_name,
configure_command,
optimization_profile,
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.

configure_command

An XML input string that instructs the stored procedure to create, update, delete, or list optimization profiles. To determine the type of operation, set the command attribute in the XML string to the appropriate value:

• CREATE
• UPDATE
• DELETE
• LIST

Example: See the following example of an XML string for updating an optimization profile:

<?xml version="1.0" encoding="UTF-8" ?>
<dwa:configOptimizationProfileCommand
    xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0" >
       <optimizationProfileCommand 
        command = "UPDATE" 
        optimizationProfileSchema = "XYZSCH"
        optimizationProfileName = "PROF2" />
</dwa:configOptimizationProfileCommand>

If you want to run the LIST command, do not specify the optimizationProfileSchema and optimizationProfileName attributes. The XML string would then look like this:

<?xml version="1.0" encoding="UTF-8" ?>
<dwa:configOptimizationProfileCommand
    xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0" >
       <optimizationProfileCommand 
        command = "LIST" />
</dwa:configOptimizationProfileCommand>

optimization_profile

The definition of the optimization profile to be created or updated. If you want to delete a profile or list existing profiles, leave this parameter out.

Example: See the following example of an XML string for updating an optimization profile. The instruction for the creation of a profile would look the same:

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.5.0">
    <OPTGUIDELINES>
        <REGISTRY>
            <OPTION NAME="DB2_EXTENDED_OPTIMIZATION" 
                VALUE="COL_RTABLE_UD_THR 0,
                XGBPART ON FULL NONHDIR,
                NI2NE_WITH_NULLS OUTER,
                COLJOIN 1,
                DISTINCT2GBY_SEL FORCE" />
        </REGISTRY>
    </OPTGUIDELINES>
</OPTPROFILE>

message

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

Result sets

This stored procedure always returns three result sets:

First result set (OPTPROFINFO_CURSOR)

This result set is only populated if you run the LIST command and returns an XML string containing the optimization profile schema name, the selected optimization profile name and a list of the optimization profiles on the specified accelerator. The result set contains the following columns:

• SEQID of type INTEGER
• OPTIMIZATIONPROFILEINFO of type VARCHAR, with a maximum length of 32698 characters

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

Optimization profiles must have been set up and enabled by IBM support for the database engine of the accelerator database.

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
• EXECUTE on the SYSACCEL.* packages
• MONITOR1 authorization
• Read/write and execute access to the /tmp directory for the user who calls the stored procedure
• RACF® ACCESS(READ) on the data set that contains the AQTENV 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

During the execution, the stored procedure inserts, updates, or deletes entries from the SYSTOOLS.OPT_PROFILE table in the accelerator database. To list optimization profiles, the stored procedure reads from this table.