GET_SYSTEM_INFO procedure - Get system information

The GET_SYSTEM_INFO procedure returns information about the data server, including information about the system, the current instance, installed data server products, environment variables, available CPUs, and other system information.

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.

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.

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 document contains instance information, including information about the fix pack level, release, system information, and environment variables.

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 highest version of the procedure.

db2 "call sysproc.get_system_info(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: Return system information.

 db2 "call sysproc.get_system_info(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'3C3F786D6C20766572.....

  Parameter Name  : XML_MESSAGE
  Parameter Value : -

  Return Status = 0

The XML output document contains something similar to the following content:

<plist version="1.0">
<dict><key>Document Type Name</key><string>Data Server System 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>Instance Information</key>
   <dict>
      <key>Display Name</key><string>Instance Information</string>
      <key>Instance Name</key>
      <dict>
         <key>Display Name</key><string>Instance Name</string>
         <key>Value</key><string>myinstance</string>
         <key>Hint</key><string></string>
      </dict>
      <key>Partitionable State</key>
      <dict>
         <key>Display Name</key><string>Partitionable State</string>
         <key>Value</key><integer>0</integer>
         <key>Hint</key><string></string>
      </dict>
      <key>Number of Database Partitions</key>
      <dict>
         <key>Display Name</key><string>Number of Database Partitions</string>
         <key>Value</key><integer>1</integer>
         <key>Hint</key><string></string>
      </dict>
      .
      .
      .
   </dict>
   <key>Product Information</key>
   <dict>
      <key>Display Name</key><string>Product Information</string>
      .
      .
      .
      <key>DB2_ENTERPRISE_SERVER_EDITION</key>
      <dict>
         <key>Display Name</key><string>DB2_ENTERPRISE_SERVER_EDITION</string>
         <key>Product short name</key>
         <dict>
            <key>Display Name</key><string>Product short name</string>
            <key>Value</key><string>ESE</string>
            <key>Hint</key><string></string>
         </dict>
         <key>License</key>
         <dict>
            <key>Display Name</key><string>License</string>
            <key>Value</key><string>Y</string>
            <key>Hint</key><string></string>
         </dict>
         <key>Product Release</key>
         <dict>
            <key>Display Name</key><string>Product Release</string>
            <key>Value</key><string>9.7</string>
            <key>Hint</key><string></string>
         </dict>
         <key>License type</key>
         <dict>
            <key>Display Name</key><string>License type</string>
            <key>Value</key><string>DEVELOPER</string>
            <key>Hint</key><string></string>
         </dict>
         <key>Hint</key><string></string>
      </dict>
      .
      .
      .
   <key>Operating System Information</key>
   <dict>
      <key>Display Name</key><string>Operating System Information</string>
      <key>Name</key>
      <dict>
         <key>Display Name</key><string>Name</string>
         <key>Value</key><string>AIX</string>
         <key>Hint</key><string></string>
      </dict>
      <key>Version</key>
      <dict>
         <key>Display Name</key><string>Version</string>
         <key>Value</key><string>5</string>
         <key>Hint</key><string></string>
      </dict>
      <key>Release</key>
      <dict>
         <key>Display Name</key><string>Release</string>
         <key>Value</key><string>3</string>
         <key>Hint</key><string></string>
      </dict>
      <key>Hostname</key>
      <dict>
         <key>Display Name</key><string>Hostname</string>
         <key>Value</key><string>achilles</string>
         <key>Hint</key><string></string>
      </dict>
      .
      .
      .
   </dict>
   <key>Workload Management Configuration</key>
   <dict>
      <key>Display Name</key><string>Workload Management Configuration</string>
      <key>Service Class Information</key>
      <dict>
         <key>Display Name</key><string>Service Class Information</string>
         <key>1</key>
         <dict>
            <key>Display Name</key><string>1</string>
            <key>Service Class Name</key>
            <dict>
               <key>Display Name</key><string>Service Class Name</string>
               <key>Value</key><string>SYSDEFAULTSYSTEMCLASS</string>
               <key>Hint</key><string></string>
         </dict>
         <key>Parent Identifier</key>
         <dict>
            <key>Display Name</key><string>Parent Identifier</string>
            <key>Value</key><integer>0</integer>
            <key>Hint</key><string></string>
         </dict>
         <key>Parent Class Name</key>
         <dict>
            <key>Display Name</key><string>Parent Class Name</string>
            <key>Value</key><string></string>
            <key>Hint</key><string></string>
         </dict>
         <key>Creation Time</key>
         <dict>
            <key>Display Name</key><string>Creation Time</string>
            <key>Value</key><string>2008-04-21-15.14.32.956930</string>
            <key>Hint</key><string></string>
         </dict>
         <key>Alter Time</key>
         <dict>
            <key>Display Name</key><string>Alter Time</string>
            <key>Value</key><string>2008-04-21-15.14.32.956930</string>
            <key>Hint</key><string></string>
         </dict>
         <key>Enabled</key>
         <dict>
            <key>Display Name</key><string>Enabled</string>
            <key>Value</key><string>Y</string>
            <key>Hint</key><string></string>
         </dict>
         <key>Agent Priority</key>
         <dict>
            <key>Display Name</key><string>Agent Priority</string>
            <key>Value</key><integer>-32768</integer>
            <key>Hint</key><string></string>
         </dict>
         <key>Prefetcher Priority</key>
         <dict>
            <key>Display Name</key><string>Prefetcher Priority</string>
            <key>Value</key><string> </string>
            <key>Hint</key><string></string>
         </dict>
         .
         .
         .
      </dict>
      .
      .
      .

      <key>Workload Information</key>
      <dict>
         <key>Display Name</key><string>Workload Information</string>
         <key>1</key>
         <dict>
            <key>Display Name</key><string>1</string>
            <key>Workload Name</key>
            <dict>
               <key>Display Name</key><string>Workload Name</string>
               <key>Value</key><string>SYSDEFAULTUSERWORKLOAD</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Evaluation Order</key>
            <dict>
               <key>Display Name</key><string>Evaluation Order</string>
               <key>Value</key><integer>1</integer>
               <key>Hint</key><string></string>
            </dict>
            <key>Creation Time</key>
            <dict>
               <key>Display Name</key><string>Creation Time</string>
               <key>Value</key><string>2008-04-21-15.14.32.955296</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Alter Time</key>
            <dict>
               <key>Display Name</key><string>Alter Time</string>
               <key>Value</key><string>2008-04-21-15.14.32.955296</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Enabled</key>
            <dict>
               <key>Display Name</key><string>Enabled</string>
               <key>Value</key><string>Y</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Allow Access</key>
            <dict>
               <key>Display Name</key><string>Allow Access</string>
               <key>Value</key><string>Y</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Service Class Name</key>
            <dict>
               <key>Display Name</key><string>Service Class Name</string>
               <key>Value</key><string>SYSDEFAULTSUBCLASS</string>
               <key>Hint</key><string></string>
            </dict>
            <key>Parent Service Class Name</key>
            <dict>
               <key>Display Name</key><string>Parent Service Class Name</string>
               <key>Value</key><string>SYSDEFAULTUSERCLASS</string>
               <key>Hint</key><string></string>
            </dict>
            .
            .
            .
         </dict>
         <key>Hint</key><string></string>
      </dict>
   </dict>
</dict></dict></dict></plist>

Example 3: Call the GET_SYSTEM_INFO procure and pass in an unsupported locale.

db2 "call sysproc. get_system_info(1,0,'ja_JP',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'3C3F786D6C20766572.....

  Parameter Name  : XML_MESSAGE
  Parameter Value : -

  Return Status = 0

  SQL20461W  The procedure "SYSPROC.GET_SYSTEM_INFO" returned output in the
alternate locale, "en_US", instead of the locale, "ja_JP", specified in
parameter "3". SQLSTATE=01H57

The XML output document will contain the same content that is shown for Example 2.

Example 4: Call the procedure from a function.

 EXEC SQL BEGIN DECLARE SECTION;
  sqlint16  getSysInfMaj;
  sqlint16  getSysInfMin;

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

  EXEC SQL CALL SYSPROC.GET_SYSTEM_INFO(
                :getSysInfMaj,
                :getSysInfMin,
                'en_US',
                 null, 
                 null, 
                :xmlOutput,
                :xmlOutMessage );