GET_MESSAGE stored procedure
The GET_MESSAGE stored procedure returns the short message text for an SQLCODE.
Authorization
To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have EXECUTE privilege on the GET_MESSAGE stored procedure.
Syntax
The following syntax diagram shows the SQL CALL statement for invoking this stored procedure:
>>-CALL---------------------------------------------------------> >--GET_MESSAGE--(--+-major_version-+--,--+-minor_version-+--,---> '-NULL----------' '-NULL----------' >--+-requested_locale-+--,--+-xml_input-+--,--+-xml_filter-+--,--xml_output--,--xml_message--)->< '-NULL-------------' '-NULL------' '-NULL-------'
The schema is SYSPROC.
Option descriptions
- major_version
- An input and output parameter of type INTEGER that indicates the major document version. On
input, this parameter indicates the major document version that you support for the XML documents
that are passed as parameters in the stored procedure (xml_input,
xml_output, and xml_message). The stored procedure processes
all XML documents in the specified version, or returns an error (-20457) if the version is invalid.
On output, this parameter specifies the highest major document version that is supported by the stored procedure. To determine the highest supported document version, specify NULL for this input parameter and all other required parameters. Currently, the highest and only major document version that is supported is 1.
If the XML document in the xml_input parameter specifies the 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.
This parameter is used in conjunction with the minor_version parameter. Therefore, you must specify both parameters together. For example, you must specify both as either NULL, or non-NULL.
- minor_version
- An input and output parameter of type INTEGER that indicates the minor document version. On
input, this parameter specifies the minor document version that you support for the XML documents
that are passed as parameters for this stored procedure (xml_input,
xml_output, and xml_message). The stored procedure processes
all XML documents in the specified version, or returns an error (-20457) if the version is invalid.
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. Currently, the highest and only minor document version that is supported is 0 (zero).
If the XML document in the xml_input parameter specifies the 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.
This parameter is used in conjunction with the major_version parameter. Therefore, you must specify both parameters together. For example, you must specify both as either NULL, or non-NULL.
- requested_locale
- An input parameter 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 are not
translated. The only translated portion of the XML output and XML message documents are
Display Name, Display Unit, and Hint.
The value might be globalized where applicable. You 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 supported values for requested_locale are en_US and NULL. If you specify a null value, the result is the same as specifying en_US.
- xml_input
- An input parameter of type BLOB(2G) that specifies an XML input document of type Data Server
Message Input in UTF-8 that contains input values for the stored procedure.For this stored procedure, the general structure of an XML input document is as follows:
<?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>Document Locale</key><string>en_US</string> <key>Complete</key><false/> <key>Required Parameters</key> <dict> <key>SQLCODE</key> <dict> <key>Value</key><integer>sqlcode</integer> </dict> </dict> <key>Optional Parameters</key> <dict> <key>Message Tokens</key> <dict> <key>Value</key> <array> <string>token1 in SQLCA</string> <string>token2 in SQLCA</string> </array> </dict> </dict> </dict> </plist>
For an example of an XML input document that will not run in Complete mode, see Example 2 in the Examples section.
Complete mode: For an example of an XML input document that is returned by the xml_output parameter when the stored procedure is running in Complete mode, see Example 1 in the Examples section.
- xml_filter
- An input parameter of type BLOB(4K) in UTF-8 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 XPath expressions for filtering output.The following example selects the value for the short message text from the XML output document:
If the key is not followed by the specified sibling, an error is returned./plist/dict/key[.='Short Message Text']/following-sibling::dict[1]/key [.='Value']/following-sibling::string[1]
- xml_output
- An output parameter of type BLOB(2G) that returns a complete XML output document of type Data
Server Message Output 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.
For an example of an XML output document, see Example 3.
- 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 an SQL warning condition. This
document is returned when a call to the procedure results in an 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.
For an example of an XML message document, see Example 4.
Example
<?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>Document Locale</key>
<string>en_US</string>
<key>Required Parameters</key>
<dict>
<key>Display Name</key>
<string>Required Parameters</string>
<key>SQLCODE</key>
<dict>
<key>Display Name</key>
<string>SQLCODE</string>
<key>Value</key>
<integer />
<key>Hint</key>
<string />
</dict>
<key>Hint</key>
<string />
</dict>
<key>Optional Parameters</key>
<dict>
<key>Display Name</key>
<string>Optional Parameters</string>
<key>Message Tokens</key>
<dict>
<key>Display Name</key>
<string>Message Tokens</string>
<key>Value</key>
<array>
<string />
</array>
<key>Hint</key>
<string />
</dict>
<key>Hint</key>
<string />
</dict>
</dict>
</plist>
<?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>Document Locale</key><string>en_US</string>
<key>Required Parameters</key>
<dict>
<key>SQLCODE</key>
<dict>
<key>Value</key><integer>-104</integer>
</dict>
</dict>
<key>Optional Parameters</key>
<dict>
<key>Message Tokens</key>
<dict>
<key>Value</key>
<array>
<string>X</string>
<string>( . LIKE AS</string>
</array>
</dict>
</dict>
</dict>
</plist>
<?xml version="1.0" encoding="UTF-8"?>
<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>DSN</string>
<key>Data Server Product Version</key><string>9.1.5</string>
<key>Data Server Major Version</key><integer>9</integer>
<key>Data Server Minor Version</key><integer>1</integer>
<key>Data Server Platform</key><string>z/OS</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>Hint</key><string />
</dict>
</dict>
</plist>
<?xml version="1.0" encoding="UTF-8" ?>
<plist version="1.0">
<dict>
<key>Document Type Name</key><string>Data Server Message</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>DSN</string>
<key>Data Server Product Version</key><string>9.1.5</string>
<key>Data Server Major Version</key><integer>9</integer>
<key>Data Server Minor Version</key><integer>1</integer>
<key>Data Server Platform</key><string>z/OS</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>DSNA630I DSNADMGM A PARAMETER FORMAT OR CONTENT ERROR WAS FOUND.
The value for key 'Document Type Minor Version' is '2'. It does
not match the value '0', which was specified for parameter 2 of
the stored procedure. Both values must be equal.</string>
<key>Hint</key><string />
</dict>
</dict>
</plist>
Example 5: This example shows a simple and static Java program that calls the GET_MESSAGE stored procedure with an XML input document and an XPath that queries the short message text of an SQLCODE.
<?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>Document Locale</key>
<string>en_US</string>
<key>Complete</key>
<false />
<key>Required Parameters</key>
<dict>
<key>SQLCODE</key>
<dict>
<key>Value</key>
<integer>-204</integer>
</dict>
</dict>
<key>Optional Parameters</key>
<dict>
<key>Message Tokens</key>
<dict>
<key>Value</key>
<array>
<string>SYSIBM.DDF_CONFIG</string>
</array>
</dict>
</dict>
</dict>
</plist>
The XPath is statically created as a string object by the program and then converted to a BLOB to serve as input for the xml_filter parameter. After the stored procedure is called, the xml_output parameter contains only a single string and no XML document. This output is materialized as a file called xml_output.xml that is in the same directory where the GetMessageDriver class resides.
//***************************************************************************
// Licensed Materials - Property of IBM
// 5635-DB2
// (C) COPYRIGHT 1982, 2006 IBM Corp. All Rights Reserved.
//
// STATUS = Version 9
//***************************************************************************
// Source file name: GetSystemDriver.java
//
// Sample: How to call SYSPROC.GET_SYSTEM_INFO with a valid XML input document
// and a valid XPath to extract the operating system name and release.
//
// The user runs the program by issuing:
// java GetSystemDriver <alias or //server/database> <userid> <password>
//
// The arguments are:
// <alias> - DB2 subsystem alias for type 2 or //server/database for type 4
// connectivity
// <userid> - user ID to connect as
// <password> - password to connect with
//***************************************************************************
import java.io.*;
import java.sql.*;
public class GetSystemDriver
{
public static void main (String[] args)
{
Connection con = null;
CallableStatement cstmt = null;
String driver = "com.ibm.db2.jcc.DB2Driver";
String url = "jdbc:db2:";
String userid = null;
String password = null;
// Parse arguments
if (args.length != 3)
{
System.err.println("Usage: GetSystemDriver <alias or //server/database>
<userid> <password>");
System.err.println("where <alias or //server/database> is DB2 subsystem
alias or //server/database for type 4 connectivity");
System.err.println(" <userid> is user ID to connect as");
System.err.println(" <password> is password to connect with");
return;
}
url += args[0];
userid = args[1];
password = args[2];
try {
String str_xmlfilter = new String(
"/plist/dict/key[.='Operating System Information']/following-sibling::
dict[1]" +
"/key[.='Name and Release']/following-sibling::dict[1]" +
"/key[.='Value']/following-sibling::string[1]");
// Convert XML_FILTER to byte array to pass as BLOB
byte[] xml_filter = str_xmlfilter.getBytes("UTF-8");
// Read XML_INPUT from file
File fptr = new File("xml_input.xml");
int file_length = (int)fptr.length();
byte[] xml_input = new byte[file_length];
FileInputStream instream = new FileInputStream(fptr);
int tot_bytes = instream.read(xml_input,0, xml_input.length);
if (tot_bytes == -1) {
System.out.println("Error during file read");
return;
}
instream.close();
// Load the DB2 Universal JDBC Driver
Class.forName(driver);
// Connect to database
con = DriverManager.getConnection(url, userid, password);
con.setAutoCommit(false);
cstmt = con.prepareCall("CALL SYSPROC.GET_SYSTEM_INFO(?,?,?,?,?,?,?)");
// Major / Minor Version / Requested Locale
cstmt.setInt(1, 1);
cstmt.setInt(2, 1);
cstmt.setString(3, "en_US");
// Input documents
cstmt.setObject(4, xml_input, Types.BLOB);
cstmt.setObject(5, xml_filter, Types.BLOB);
// Output Parms
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.registerOutParameter(6, Types.BLOB);
cstmt.registerOutParameter(7, Types.BLOB);
cstmt.execute();
con.commit();
SQLWarning ctstmt_warning = cstmt.getWarnings();
if (ctstmt_warning != null) {
System.out.println("SQL Warning: " + ctstmt_warning.getMessage());
}
else {
System.out.println("SQL Warning: None\r\n");
}
System.out.println("Major Version returned " + cstmt.getInt(1) );
System.out.println("Minor Version returned " + cstmt.getInt(2) );
// Get output BLOBs
Blob b_out = cstmt.getBlob(6);
if(b_out != null)
{
int out_length = (int)b_out.length();
byte[] bxml_output = new byte[out_length];
// Open an inputstream on BLOB data
InputStream instr_out = b_out.getBinaryStream();
// Copy from inputstream into byte array
int out_len = instr_out.read(bxml_output, 0, out_length);
// Write byte array content into FileOutputStream
FileOutputStream fxml_out = new FileOutputStream("xml_output.xml");
fxml_out.write(bxml_output, 0, out_length );
//Close streams
instr_out.close();
fxml_out.close();
}
Blob b_msg = cstmt.getBlob(7);
if(b_msg != null)
{
int msg_length = (int)b_msg.length();
byte[] bxml_message = new byte[msg_length];
// Open an inputstream on BLOB data
InputStream instr_msg = b_msg.getBinaryStream();
// Copy from inputstream into byte array
int msg_len = instr_msg.read(bxml_message, 0, msg_length);
// Write byte array content into FileOutputStream
FileOutputStream fxml_msg = new FileOutputStream(new File
("xml_message.xml"));
fxml_msg.write(bxml_message, 0, msg_length);
//Close streams
instr_msg.close();
fxml_msg.close();
}
}
catch (SQLException sqle) {
System.out.println("Error during CALL "
+ " SQLSTATE = " + sqle.getSQLState()
+ " SQLCODE = " + sqle.getErrorCode()
+ " : " + sqle.getMessage());
}
catch (Exception e) {
System.out.println("Internal Error " + e.toString());
}
finally
{
if(cstmt != null)
try { cstmt.close(); } catch ( SQLException sqle)
{ sqle.printStackTrace(); }
if(con != null)
try { con.close(); } catch ( SQLException sqle)
{ sqle.printStackTrace(); }
}
}
}