The GET_MESSAGE procedure returns the short message text, long message text, and SQLSTATE for an SQLCODE.
>>-GET_MESSAGE--(--major_version--,--minor_version--,--requested_locale--,--> >--xml_input--,--xml_filter--,--xml_output--,--xml_message--)--><
The schema is SYSPROC.
Currently, the only supported value for requested_locale is en_US.
<?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>
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. |
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
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 );