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