SET_CONFIG procedure - Set configuration parameters

The SET_CONFIG stored procedure updates the database and database manager configuration parameters that are returned by the GET_CONFIG procedure.

The SET_CONFIG procedure accepts an input XML document that contains configuration parameters and their values, uses this information to update the specified configuration parameters, and returns an output XML document that indicates the update status of each configuration parameter.

Syntax

Read syntax diagramSkip visual syntax diagramSET_CONFIG(major_version,minor_version,requested_locale ,xml_input,xml_filter,xml_output,xml_message)

The schema is SYSPROC.

Procedure parameters

major_version
An input and output argument of type INTEGER that indicates the major document version. On input, this argument indicates the major document version that the caller supports for the XML documents passed as parameters in the procedure (see the parameter descriptions for xml_input, xml_output, and xml_message). The procedure processes all XML documents in the specified version, or returns an error (+20458) if the version is not valid. On output, this parameter specifies the highest major document version that is supported by the procedure. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters.
If the XML document in the xml_input parameter specifies a Document Type Major Version key, the value for that key must be equal to the value provided in the major_version parameter, or an error (+20458) is raised.
Supported versions: 1
minor_version
An input and output argument of type INTEGER that indicates the minor document version. On input, this argument specifies the minor document version that the caller supports for the XML documents passed as parameters for this procedure (see the parameter descriptions for xml_input, xml_output, and xml_message). The procedure processes all XML documents in the specified version, or returns an error if the version is not valid. On output, this parameter indicates the highest minor document version that is supported for the highest supported major version. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters.
If the XML document in the xml_input parameter specifies a Document Type Minor Version key, the value for that key must be equal to the value provided in the minor_version parameter, or an error (+20458) is raised.
Supported versions: 0
requested_locale
An input argument of type VARCHAR(33) that specifies a locale. If the specified language is supported on the server, translated content is returned in the xml_output and xml_message parameters. Otherwise, content is returned in the default language. Only the language and possibly the territory information is used from the locale. The locale is not used to format numbers or influence the document encoding. For example, key names and values are not translated. The only translated portion of the XML output and XML message documents are the text for hint, display name, and display unit of each entry. The caller should always compare the requested language to the language that is used in the XML output document (see the document locale entry in the XML output document).

Currently, the only supported value for requested_locale is en_US.

xml_input
An input argument of type BLOB(32MB) that specifies an XML input document (encoded in UTF-8) that contains input values for the procedure.
For this procedure, the XML input document contains database and database manager configuration settings.
xml_filter
An input argument of type BLOB(4K) that specifies a valid XPath query string. Use a filter when you want to retrieve a single value from an XML output document. For more information, see the topic that describes XPath filtering.
The following example selects the value for a specific configuration parameter setting from the XML output document: /plist/dict/key[.="Database Manager Configuration Parameter Settings"]/following-sibling::dict[1]/key[3]/following-sibling::dict[1]/dict[1]/key[.="Value"]/following-sibling::string[1]. If the key is not followed by the specified sibling, an error is returned.
xml_output
An output parameter of type BLOB(32MB) that returns a complete XML output document in UTF-8. If a filter is specified, this parameter returns a string value. If the stored procedure is unable to return a complete output document (for example, if a processing error occurs that results in an SQL warning or error), this parameter is set to NULL.
When this procedure operates in complete mode, this parameter returns an XML document that contains the current configuration values set in the server. You can modify this document and pass it back to the procedure as the xml_input parameter. This approach provides a programmatic way to create valid XML input documents.
xml_message
An output parameter of type BLOB(64K) that returns a complete XML output document of type Data Server Message in UTF-8 that provides detailed information about a SQL warning condition. This document is returned when a call to the procedure results in a SQL warning, and the warning message indicates that additional information is returned in the XML message output document. If the warning message does not indicate that additional information is returned, then this parameter is set to NULL.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Example

Example 1: Return the latest version of the procedure.
db2 "call  sysproc.set_config (null,null,null,null,null,?,?)"
The following is an example of output from this query:
Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1

Parameter Name : MINOR_VERSION
Parameter Value : 0

Parameter Name : XML_OUTPUT
Parameter Value : -

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

Example 2: Run a script called setconfig.sql that updates a few database and database manager configuration parameters.

setconfig.sql:

call sysproc.set_config(1,0,'en_US',blob('
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
  <key>Document Type Name</key><string>Data Server Set Configuration Input</string>
  <key>Document Type Major Version</key><integer>1</integer>
  <key>Document Type Minor Version</key><integer>0</integer>
  <key>Document Locale</key><string>en_US</string>
  <key>Database Manager Configuration Parameter Settings</key>
  <dict>
    <key>diaglevel</key><dict><key>Parameter Value</key>
    <dict>
      <key>Value</key><string>4</string>
    </dict>
  </dict>
  <key>fcm_num_buffers</key>
  <dict>
    <key>Parameter Value</key>
    <dict>
      <key>Value</key><string>4096</string>
    </dict>
    <key>Value Flags</key>
    <dict>
      <key>Value</key><string>MANUAL</string>
    </dict>
  </dict>
  <key>instance_memory</key>
  <dict>
    <key>Deferred Value</key>
    <dict>
      <key>Value</key><string>7424</string>
    </dict>
    <key>Deferred Value Flags</key>
    <dict>
        <key>Value</key><string>AUTOMATIC</string>
    </dict>
  </dict>
  </dict>
  <key>Database Partition</key>
  <dict>
    <key>All</key>
    <dict>
      <key>Database Configuration Parameter Settings</key>
      <dict>
        <key>avg_appls</key>
        <dict>
          <key>Parameter Value</key>
          <dict>
            <key>Value></key><string>2</string>
          </dict>
          <key>Value Flags</key>
          <dict>
            <key>Value</key><string>AUTOMATIC</string>
          </dict>
        </dict>
        <key>database_memory</key>
        <dict>
          <key>Deferred Value</key>
          <dict>
            <key>Value</key><string>2</string>
          </dict>
          <key>Deferred Value Flags</key>
          <dict>
            <key>Value</key><string>MANUAL</string>
          </dict>
        </dict>
      </dict>
    </dict>
  </dict>
</dict>
</plist>'), null, ?,?)@
The following is an example of output from this query:
Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1

Parameter Name : MINOR_VERSION
Parameter Value : 0

Parameter Name : XML_OUTPUT
Parameter Value : x'3C3F78...'

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0
The output XML document contains something similar to the following content:
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
  <key>Document Type Name</key>
  <string>Data Server Set Configuration Output</string> 
  <key>Document Type Major Version</key><integer>1</integer> 
  <key>Document Type Minor Version</key><integer>0</integer>
  <key>Data Server Product Name</key><string>QDB2/AIX64</string> 
  <key>Data Server Product Version</key><string>9.7.0.0</string> 
  <key>Data Server Major Version</key><integer>9</integer> 
  <key>Data Server Minor Version</key><integer>7</integer> 
  <key>Data Server Platform</key><string>AIX 64BIT</string> 
  <key>Document Locale</key><string>en_US</string> 
  <key>Database Manager Configuration Parameter Settings</key>
  <dict>
    <key>Display Name</key>
    <string>Database Manager Configuration Parameter Settings</string> 
    <key>diaglevel</key> 
    <dict>
      <key>Display Name</key><string>diaglevel</string> 
      <key>Parameter Value</key> 
      <dict>
        <key>Display Name</key><string>Parameter Value</string> 
        <key>Value</key><string>4</string> 
      </dict>
      <key>Parameter Update Status</key> 
      <dict>
        <key>Display Name</key><string>Parameter Update Status</string> 
        <key>SQLCODE</key> 
        <dict
          <key>Display Name</key><string>SQLCODE</string> 
          <key>Value</key><integer>0</integer> 
        </dict>
      <key>Message Tokens</key> 
      <dict>
        <key>Display Name</key><string>Message Tokens</string> 
        <key>Value</key><array><string></string></array>
      </dict>
      <key>SQLSTATE</key> 
      <dict>
        <key>Display Name</key><string>SQLSTATE</string> 
        <key>Value</key><string></string> 
      </dict>
    </dict>
  </dict>
  <key>fcm_num_buffers</key> 
  <dict>
    <key>Display Name</key><string>fcm_num_buffers</string> 
    <key>Parameter Value</key> 
      <dict>
        <key>Display Name</key><string>Parameter Value</string> 
        <key>Value</key><string>4096</string> 
      </dict>
      <key>Value Flags</key> 
      <dict>
        <key>Display Name</key><string>Value Flags</string> 
        <key>Value</key><string>MANUAL</string> 
      </dict>
      <key>Parameter Update Status</key> 
      <dict>
        <key>Display Name</key><string>Parameter Update Status</string> 
        <key>SQLCODE</key><dict>
        <key>Display Name</key><string>SQLCODE</string> 
        <key>Value</key> <integer>0</integer> 
      </dict>
      <key>Message Tokens</key> 
      <dict>
        <key>Display Name</key><string>Message Tokens</string> 
        <key>Value</key><array><string></string></array>
      </dict>
      <key>SQLSTATE</key> 
      <dict>
        <key>Display Name</key><string>SQLSTATE</string> 
        <key>Value</key><string></string> 
      </dict>
    </dict>
  </dict>
  <key>instance_memory</key> 
  <dict>
    <key>Display Name</key><string>instance_memory</string> 
    <key>Deferred Value</key> 
    <dict>
      <key>Display Name</key><string>Deferred Value</string> 
     <key>Value</key><string>7424</string> 
    </dict>
    <key>Deferred Value Flags</key> 
    <dict>
      <key>Display Name</key><string>Deferred Value Flags</string> 
      <key>Value</key><string>AUTOMATIC</string> 
    </dict>
    <key>Parameter Update Status</key> 
    <dict>
      <key>Display Name</key><string>Parameter Update Status</string> 
      <key>SQLCODE</key>
      <dict>
        <key>Display Name</key><string>SQLCODE</string> 
        <key>Value</key><integer>0</integer> 
      </dict>
        <key>Message Tokens</key> 
        <dict>
          <key>Display Name</key><string>Message Tokens</string> 
          <key>Value</key><array><string></string></array>
        </dict>
        <key>SQLSTATE</key> 
         <dict>
           <key>Display Name</key><string>SQLSTATE</string> 
           <key>Value</key><string></string> 
         </dict>
       </dict>
     </dict>
    </dict>
    <key>Database Partition</key> 
    <dict>
      <key>Display Name</key><string>Database Partition</string> 
      <key>All</key> 
      <dict>
        <key>Display Name</key><string>All</string> 
       <key>Database Configuration Parameter Settings</key> 
       <dict>
         <key>Display Name</key>
         <string>Database Configuration Parameter Settings</string> 
         <key>avg_appls</key> 
         <dict>
           <key>Display Name</key><string>avg_appls</string>
           <key>Parameter Value</key> 
           <dict>
             <key>Display Name</key><string>Parameter Value</string> 
             <key>Value</key><string>2</string> 
           </dict>
          <key>Value Flags</key> 
          <dict>
            <key>Display Name</key><string>Value Flags</string> 
            <key>Value</key><string>AUTOMATIC</string> 
          </dict>
          <key>Parameter Update Status</key> 
          <dict>
            <key>Display Name</key><string>Parameter Update Status</string> 
            <key>Update Coverage</key> 
            <dict>
              <key>Display Name</key><string>Update Coverage</string> 
              <key>Value</key><string>Complete</string> 
            </dict>
            <key>SQLCODE</key> 
            <dict>
              <key>Display Name</key><string>SQLCODE</string> 
              <key>Value</key><integer>0</integer> 
            </dict>
            <key>Message Tokens</key> 
            <dict>
              <key>Display Name</key><string>Message Tokens</string> 
              <key>Value</key><array><string></string> </array>
            </dict>
            <key>SQLSTATE</key> 
            <dict>
               <key>Display Name</key><string>SQLSTATE</string> 
               <key>Value</key><string></string> 
            </dict>
          </dict>
        </dict>
        <key>database_memory</key> 
        <dict>
          <key>Display Name</key><string>database_memory</string> 
          <key>Deferred Value</key>
          <dict>
            <key>Display Name</key><string>Deferred Value</string> 
            <key>Value</key><string>2</string> 
          </dict>
          <key>Deferred Value Flags</key> 
          <dict>
            <key>Display Name</key><string>Deferred Value Flags</string> 
            <key>Value</key><string>MANUAL</string> 
          </dict>
          <key>Parameter Update Status</key> 
          <dict>
            <key>Display Name</key><string>Parameter Update Status</string> 
            <key>Update Coverage</key> 
            <dict>
              <key>Display Name</key><string>Update Coverage</string> 
              <key>Value</key><string>Complete</string> 
            </dict>
            <key>SQLCODE</key> 
            <dict>
              <key>Display Name</key><string>SQLCODE</string> 
              <key>Value</key><integer>0</integer> 
            </dict>
            <key>Message Tokens</key> 
            <dict>
              <key>Display Name</key><string>Message Tokens</string> 
              <key>Value</key><array><string></string></array>
            </dict>
            <key>SQLSTATE</key> 
            <dict>
              <key>Display Name</key><string>SQLSTATE</string> 
              <key>Value</key><string></string> 
            </dict>
          </dict>
        </dict>
      </dict>
    </dict>
  </dict>
</dict>
</plist>

Example 3: Specify a filter to return the value for a specific configuration parameter.

db2 "call sysproc.set_config(1,0, 'en_US', blob('
<plist version="1.0">
<dict>
   <key>Document Type Name</key><string>Data Server Set Configuration Input</string>
   <key>Document Type Major Version</key><integer>1</integer>
   <key>Document Type Minor Version</key><integer>0</integer>
   <key>Document Locale</key><string>en_US</string>
   <key>Database Manager Configuration Parameter Settings</key>
   <dict>
      <key>diaglevel</key>
      <dict>
         <key>Parameter Value</key>
         <dict>
            <key>Value</key><string>4</string>
         </dict>
      </dict>
      <key>fcm_num_buffers</key>
      <dict>
         <key>Parameter Value</key>
         <dict>
            <key>Value</key><string>4096</string>
         </dict>
        <key>Value Flags</key>
        <dict>
           <key>Value</key><string>MANUAL</string>
        </dict>
      </dict>
      <key>instance_memory</key>
      <dict>
         <key>Deferred Value</key>
         <dict>
            <key>Value</key><string>7424</string>
         </dict>
         <key>Deferred Value Flags</key>
         <dict>
            <key>Value</key><string>AUTOMATIC</string>
         </dict>
      </dict>
   </dict>
   <key>Database Partition</key>
   <dict>
      <key>All</key>
      <dict>
         <key>Database Configuration Parameter Settings</key>
         <dict>
            <key>avg_appls</key>
            <dict>
               <key>Parameter Value</key>
               <dict>
                  <key>Value></key><string>2</string>
               </dict>
               <key>Value Flags</key>
               <dict>
                  <key>Value</key><string>AUTOMATIC</string>
               </dict>
            </dict>
            <key>database_memory</key>
            <dict>
               <key>Deferred Value</key>
               <dict>
                  <key>Value</key><string>2</string>
               </dict>
               <key>Deferred Value Flags</key>
               <dict>
                  <key>Value</key><string>MANUAL</string>
               </dict>
            </dict>
         </dict>
      </dict>
   </dict>
</dict>
</plist>'),
blob('/plist/dict/key[.="Database Manager Configuration Parameter Settings"]
/following-sibling::dict[1]/key[3]
/following-sibling::dict[1]/dict[1]/key[.="Value"]
/following-sibling::string[1]'),?,?)"

The following is an example of output from this query:

Value of output parameters
--------------------------
Parameter Name : MAJOR_VERSION
Parameter Value : 1

Parameter Name : MINOR_VERSION
Parameter Value : 0

Parameter Name : XML_OUTPUT
Parameter Value : x'34303936'

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0
The following value is returned for xml_output:
4096

Example 4: Call the procedure from a function.

  EXEC SQL BEGIN DECLARE SECTION;
  sqlint16  getconfigMaj;
  sqlint16  getconfigMin;

  SQL TYPE IS BLOB(2M) xmlOutput;
  SQL TYPE IS BLOB(2K) xmlOutMessage;
  EXEC SQL END DECLARE SECTION;
   getconfigMaj = 1;
   getconfigMin = 0;

  EXEC SQL CALL SYSPROC.SET_CONFIG(
                :getconfigMaj,
                :getconfigMin,
                'en_US',
                BLOB('blob('
                <?xml version="1.0" encoding="UTF-8"?>
                <plist version="1.0">
                <dict>
                  <key>Document Type Name</key>
                  <string>Data Server Set Configuration Input</string>
                  <key>Document Type Major Version</key><integer>1</integer>
                  <key>Document Type Minor Version</key><integer>0</integer>
                  <key>Document Locale</key><string>en_US</string>
                  <key>Database Manager Configuration Parameter Settings</key>
                  <dict>
                    <key>diaglevel</key><dict><key>Parameter Value</key>
                    <dict>
                      <key>Value</key><string>4</string>
                    </dict>
                  </dict>
                  <key>fcm_num_buffers</key>
                  <dict>
                    <key>Parameter Value</key>
                    <dict>
                      <key>Value</key><string>4096</string>
                    </dict>
                    <key>Value Flags</key>
                    <dict>
                      <key>Value</key><string>MANUAL</string>
                    </dict>
                  </dict>
                  <key>instance_memory</key>
                  <dict>
                    <key>Deferred Value</key>
                    <dict>
                      <key>Value</key><string>7424</string>
                    </dict>
                    <key>Deferred Value Flags</key>
                    <dict>
                        <key>Value</key><string>AUTOMATIC</string>
                    </dict>
                  </dict>
                  </dict>
                  <key>Database Partition</key>
                  <dict>
                    <key>All</key>
                    <dict>
                      <key>Database Configuration Parameter Settings</key>
                      <dict>
                        <key>avg_appls</key>
                        <dict>
                          <key>Parameter Value</key>
                          <dict>
                            <key>Value></key><string>2</string>
                          </dict>
                          <key>Value Flags</key>
                          <dict>
                            <key>Value</key><string>AUTOMATIC</string>
                          </dict>
                        </dict>
                        <key>database_memory</key>
                        <dict>
                          <key>Deferred Value</key>
                          <dict>
                            <key>Value</key><string>2</string>
                          </dict>
                          <key>Deferred Value Flags</key>
                          <dict>
                            <key>Value</key><string>MANUAL</string>
                          </dict>
                        </dict>
                      </dict>
                    </dict>
                  </dict>
                </dict>
                </plist>'), 
                null, 
                :xmlOutput,
                :xmlOutMessage );