GET_MESSAGE procedure - Get message text

Syntax

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

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 an SQLCODE and uses the following format:

<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
    <key>Document Type Name</key><string>Data Server Message Input</string>
    <key>Required Parameters</key>
    <!-- Specify either SQLCODE or message identifier and message tokens 
    for the key values below. -->
    <dict>
        <key>SQL Code</key><integer></integer>
        <key>Message Identifier</key><integer></integer>
        <key>Message Tokens</key><array><string>...</string></array>
    </dict>
    <key>Optional Parameters</key>
    <dict>
        <key>Message Token Delimiter<key><string>;</string>
    </key></key></dict>
</dict>
</plist>

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 SQLSTATE from the XML output document: /plist/dict/key[.="SQLSTATE"]/following-sibling::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.

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	Returns the short text message and SQLSTATE for the corresponding SQLCODE passed in xml_input.
2	0	Returns the short text message, long text message and SQLSTATE for the corresponding SQLCODE passed in xml_input.

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

Default PUBLIC privilege

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

Example

db2 "call sysproc.get_message(null,null,null,null,null,?,?)"

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: Run a script called getmsglong.sql to return the short text message and long text message for SQL1034.

getmsglong.sql:

call sysproc.get_message(2,0, 'en_US', blob('
<?xml version="1.0" encoding="UTF-8"?>
<plist version="1.0">
<dict>
  <key>Document Type Name</key><string>Data Server Message Input</string>
  <key>Document Type Major Version</key><integer>2</integer>
  <key>Document Type Minor Version</key><integer>0</integer>
  <key>Required Parameters</key>
  <dict>
    <key>SQLCODE</key><string>SQL1034</string>
  </dict>
</dict>
</plist>'), null, ? , ?)@

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'3C3F786D6C20766572........................'

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

<plist version="1.0">
<dict>
  <key>Document Type Name</key>
  <string>Data Server Message 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>Short Message Text</key>
  <dict>
  <key>Display Name</key><string>Short Message Text</string>
  <key>Value</key>
  <string>
  SQL1034C  The database is damaged.  All applications processing the database 
  have been stopped.
  </string>
  <key>Hint</key><string></string>
</dict>
  <key>SQLSTATE</key>
  <dict>
    <key>Display Name</key><string>SQLSTATE</string>
    <key>Value</key><string> 58031</string>
    <key>Hint</key><string></string>
  </dict>
  <key>Long Message Text</key>
  <dict>
    <key>Display Name</key><string>Long Message Text</string>
    <key>Value</key>
    <array>
      <string>
      SQL1034C  The database is damaged. All applications 
      processing the
      </string>
      <string>      database have been stopped.</string>
      <string></string>
      <string>Explanation: </string>
      <string></string>
      <string>
      Damage has occurred to the database. It cannot be used until it is
      </string>
      <string>
      recovered. All applications connected to the database have been
      </string>
      <string>
      disconnected and all processes running applications on the 
      database have
      </string>
      <string>been stopped.</string>
      <string></string>
      <string>The command cannot be processed.</string>
      <string></string>
      <string>User response: </string>
      <string></string>
      <string>
      Issue a RESTART DATABASE command to recover the database. If the RESTART
      </string>
      <string>
      command consistently fails, you may want to restore the database from a
      </string>
      <string>
      backup. In a partitioned database server environment, check the syslog
      </string>
      <string>
      to find out if the RESTART command fails because of node or
      </string>
      <string>
      communication failures before restoring the database from a backup. If
      </string>
      <string>
      so, ensure the database manager is up and running and communication is
      </string>
      <string>
      available among all the nodes, then resubmit the restart command.
      </string>
      <string></string>
      <string>
      If you encountered this error during roll-forward processing, you must
      </string>
      <string>
      restore the database from a backup and perform roll-forward again.
      </string>
      <string></string>
      <string>
      Note that in a partitioned database environment, the RESTART database
      </string>
      <string>
      command is run on a per-node basis. To ensure that the database is
      </string>
      <string>restarted on all nodes, use the command: </string>
      <string></string>
      <string>db2_all db2 restart database</string>
      <string><database_name></string>
      <string></string>
      <string>
      This command may have to be run several times to ensure that all
      </string>
      <string>in-doubt transactions have been resolved.</string>
      <string></string>
      <string>
      If you are installing the sample database, drop it and install the
      </string>
      <string>sample database again.</string>
      <string></string>
      <string> sqlcode: -1034</string>
      <string></string>
      <string> sqlstate: 58031</string>
      <string></string>
      <string></string>
      <string></string>
      </array>
    <key>Hint</key><string></string>
  </dict>
</dict>
</plist>

Example 3: Run a script called getmsgshort.sql to return only the short text message for SQL1034.

getmsgshort.sql:

call sysproc.get_message(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 Message Input</string>
  <key>Document Type Major Version</key><integer>1</integer>
  <key>Document Type Minor Version</key><integer>0</integer>
  <key>Required Parameters</key>
  <dict>
    <key>SQLCODE</key><string>SQL1034</string>
  </dict>
</dict>
</plist>'), null, ? , ?)@

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'3C3F786D6C20766572........................'

Parameter Name : XML_MESSAGE
Parameter Value : -

Return Status = 0

SQL20460W The procedure "SYSPROC.GET_MESSAGE" supports a higher version, "2",
than the specified version, "1", for parameter "1".

<plist version="1.0">
<dict><key>Document Type Name</key><string>Data Server Message 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>Short Message Text</key>
  <dict>
    <key>Display Name</key><string>Short Message Text</string>
    <key>Value</key>
    <string>
    SQL1034C  The database is damaged.  All applications processing the database 
    have been stopped.
    </string>
    <key>Hint</key><string></string>
  </dict>
  <key>SQLSTATE</key>
  <dict>
    <key>Display Name</key><string>SQLSTATE</string>
    <key>Value</key><string> 58031</string>
    <key>Hint</key><string></string>
  </dict>
</dict>
</plist>

Example 4: Specify a filter to return the SQLSTATE for SQL1034.

db2 "call sysproc.get_message(2,0, 'en_US', blob(' 
<plist version="1.0">
<dict> 
   <key>Document Type Name</key>
   <string>Data Server Message Input</string> 
   <key>Required Parameters</key> 
   <dict> 
      <key>SQLCODE</key><string>SQL1034</string> 
   </dict> 
</dict> 
</plist>'), 
blob('/plist/dict/key[.="SQLSTATE"]/following-sibling::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 : 2 

Parameter Name : MINOR_VERSION 
Parameter Value : 0 

Parameter Name : XML_OUTPUT 
Parameter Value : x'203538303331' 

Parameter Name : XML_MESSAGE 
Parameter Value : - 

Return Status = 0

The following value is returned for xml_output:

58031

Example 5: Call the procedure from a function.

EXEC SQL BEGIN DECLARE SECTION;
  sqlint16  getMsgMaj;
  sqlint16  getMsgMin;

  SQL TYPE IS BLOB(2M) xmlOutput;
  SQL TYPE IS BLOB(2K) xmlOutMessage;
 EXEC SQL END DECLARE SECTION;
   getMsgMaj = 2;
   getMsgMin = 0;

  EXEC SQL CALL SYSPROC.GET_MESSAGE(
                :getMsgMaj,
                :getMsgMin,
                'en_US',
                BLOB('
                <?xml version="1.0" encoding="UTF-8"?>
                <plist version="1.0">
                <dict>
                   <key>Document Type Name</key>
                   <string>
                   Data Server Message Input
                   </string>
                   <key>Document Type Major Version</key><integer>2</integer>
                   <key>Document Type Minor Version</key><integer>0</integer>
                   <key>Required Parameters</key>
                   <dict>
                      <key>SQLCODE</key><string>SQL1034</string>
                   </dict>
                </dict>
                </plist>'), 
                null, 
               :xmlOutput,
               :xmlOutMessage );