GET_CONFIG procedure - Get configuration data

The GET_CONFIG stored procedure retrieves the database configuration, database manager configuration, and registry variables that are set for a particular instance.

In a partitioned database environment, this procedure retrieves database configuration and registry variable settings from all partitions.

Syntax

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.

Supported versions: 1 and 2

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.

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

Currently, this procedure accepts no input. You must specify NULL for this parameter, or an error (+20458) is raised to indicate that the input is not valid.

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 the data server product version from the XML output document:

/plist/dict/key[.='Data
Server Product Version']/following-sibling::string

. 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.

The XML output is determined by the values that you specify for major_version and minor_version:

Major version	Minor version	`xml_output` value
NULL	NULL	NULL
1	0	Database manager and database configuration parameters and registry variables, including their values.
2	0	Database manager and database configuration parameters grouped into categories. For each parameter, indicates whether the parameter can be updated. Also returns registry variables and the values set for the instance.

When the procedure operates in complete mode, this parameter returns an XML document that you can modify and pass back to the procedure as the xml_input parameter. This approach provides a programmatic way to create valid XML input documents. For more information, see the topic about complete mode.

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.get_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 : 2

Parameter Name : MINOR_VERSION
Parameter Value : 0

Parameter Name : XML_OUTPUT
Parameter Value : -

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

Example 2: Return database and database manager configuration parameters, grouped into categories.

db2 "call sysproc.get_config(2,0,'en_US',null, null, ?,?)"

The following is an example of output from this query:

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

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 XML output document contains the following content:

<plist version="1.0">
<dict>
   <key>Document Type Name</key><string>Data Server Configuration Output</string>
   <key>Document Type Major Version</key><integer>2</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>Application</key>
      <dict>
         <key>Display Name</key><string>Application</string>
         <key>agentpri</key>
         <dict>
            <key>Display Name</key><string>agentpri</string>
            <key>Parameter Value</key>
            <dict>
               <key>Display Name</key><string>Parameter Value</string>
               <key>Value</key><string>-1</string>
               <key>Updatable</key><string>No</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Value Flags</key>
            <dict>
               <key>Display Name</key><string>Value Flags</string>
               <key>Value</key><string>NONE</string>
               <key>Updatable</key><string>No</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Deferred Value</key>
            <dict>
               <key>Display Name</key><string>Deferred Value</string>
               <key>Value</key><string>-1</string>
               <key>Updatable</key><string>Yes</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Deferred Value Flags</key>
            <dict>
               <key>Display Name</key><string>Deferred Value Flags</string>
               <key>Value</key><string>INTEGER</string>
               <key>Updatable</key><string>Yes</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Data Type</key>
            <dict>
               <key>Display Name</key><string>Data Type</string>
               <key>Value</key><string>NONE</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Hint</key>
            <string>
            Specifies the priority given to an agent and other database manager 
            instance processes and threads by the operating system scheduler. 
            Consider rebinding applications after changing this parameter. 
            </string>
         </dict>
         <key>Hint</key><string></string>
      </dict>
   </dict>
   <key>Administration</key>
   .
   .
   .
   <key>Communication</key>
   .
   .
   .
   <key>Diagnostics</key>
   .
   .
   .
   <key>Environment</key>
   .
   .
   .
   <key>Miscellaneous</key>
   .
   .
   .
   <key>Monitor</key>
   .
   .
   .
   <key>Parallel</key>
   .
   .
   .
   <key>Performance</key>
   .
   .
   .
   </dict>
   <key>Database Partition</key>
   <dict>
      <key>Display Name</key><string>Database Partition</string>
      <key>0</key>
      <dict>
         <key>Display Name</key><string>0</string>
         <key>Database Configuration Parameter Settings</key>
         <dict>
            <key>Display Name</key>
            <string>Database Configuration Parameter Settings</string>
            <key>Application</key>
            .
            .
            .
            <key>Environment</key>
         <dict>
            <key>Display Name</key><string>Environment</string>
            <key>alt_collate</key>
            <dict>
               <key>Display Name</key><string>alt_collate</string>
               <key>Parameter Value</key>
               <dict>
                  <key>Display Name</key><string>Parameter Value</string>
                  <key>Value</key><string></string>
                  <key>Updatable</key><string>No</string>
                  <key>Hint</key><string></string>
               </dict>
               <key>Value Flags</key>
               <dict>
                  <key>Display Name</key><string>Value Flags</string>
                  <key>Value</key><string>NONE</string>
                  <key>Updatable</key><string>No</string>
                  <key>Hint</key><string></string>
               </dict>
               <key>Deferred Value</key>
               <dict>
                  <key>Display Name</key><string>Deferred Value</string>
                  <key>Value</key><string></string>
                  <key>Updatable</key><string>Yes</string>
                  <key>Hint</key><string></string>
               </dict>
               <key>Deferred Value Flags</key>
               <dict>
                  <key>Display Name</key><string>Deferred Value Flags</string>
                  <key>Value</key><string>INTEGER</string>
                  <key>Updatable</key><string>Yes</string>
                  <key>Hint</key><string></string>
               </dict>
               <key>Data Type</key>
               <dict>
                  <key>Display Name</key><string>Data Type</string>
                  <key>Value</key><string>NONE</string>
                  <key>Hint</key><string></string>
               </dict>
               <key>Hint</key>
               <string>
               Specifies the collating sequence to be used for Unicode tables in a 
               non-Unicode database. Until this parameter is set, Unicode tables and 
               routines cannot be created in a non-Unicode database. When set, this 
               parameter cannot be changed or reset. Default [range] : 
               Null [IDENTITY_16BIT]. 
               </string>
            </dict>
            .
            .
            .
         </dict>
         <key>Logs</key>
         .
         .
         .
         <key>Maintenance</key>
         .
         .
         .
         <key>Performance</key>
         .
         .
         .
         <key>Recovery</key>
         .
         .
         .
         <key>Status</key>
         .
         .
         .
      </dict>
      <key>Registry Variables Settings</key>
      <dict>
         <key>Display Name</key><string>Registry Variables Settings</string>
         <key>DB2CODEPAGE</key>
         <dict>
            <key>Display Name</key><string>DB2CODEPAGE</string>
            <key>Parameter Value</key>
            <dict>
               <key>Display Name</key><string>Parameter Value</string>
               <key>Value</key><string>1208</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Is Aggregate</key>
            <dict>
               <key>Display Name</key><string>Is Aggregate</string>
               <key>Value</key><integer>0</integer>
               <key>Hint</key><string></string>
            </dict>
            <key>Aggregate Name</key>
            <dict>
               <key>Display Name</key><string>Aggregate Name</string>
               <key>Value</key><string></string>
               <key>Hint</key><string></string>
            </dict>
            <key>Level</key>
            <dict>
               <key>Display Name</key><string>Level</string>
               <key>Value</key><string>I</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Hint</key><string></string>
         </dict>
         .
         .
         .
      </dict>
      <key>Hint</key><string></string>
   </dict>
</dict>
</plist>

Example 3: Return database and database manager configuration parameters.

db2 "call sysproc.get_config(1,0,'en_US',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 : x'3C3F78.......'

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

The XML output document contains content that is similar to example 2, but does not group the configuration parameters into categories.

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 = 2;
  getconfigMin = 0;

  EXEC SQL CALL SYSPROC.GET_CONFIG(
                :getconfigMaj,
                :getconfigMin,
                'en_US',
                null,
                null, 
                :xmlOutput,
                :xmlOutMessage );