GET_MESSAGE procedure - Get message text
The GET_MESSAGE procedure returns the short message text, long message text, and SQLSTATE for an SQLCODE.
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.
- 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.
- 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.
- 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.
- 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.
- 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 supported version
of the procedure.
db2 "call sysproc.get_message(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: 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, ? , ?)@
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'3C3F786D6C20766572........................'
Parameter Name : XML_MESSAGE
Parameter Value : -
Return Status = 0
The output XML document contains
the following content:
<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, ? , ?)@
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'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".
The XML output document contains the following content:
<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 );