Spatial Extender stored procedure output parameters

Use the Spatial Extender stored procedure output parameters to diagnose problems when explicitly calling stored procedures in application programs or from the Db2® command line processor.

The Spatial Extender stored procedures return the following output parameters for message information to indicate the success or failure of a stored procedure:
msg_code (message code)
The msg_code parameter is an integer, which can be positive, negative, or zero (0). Positive numbers are used for warnings, negative numbers are used for errors (both critical and non-critical), and zero (0) is used for informational messages.
The absolute value of the msg_code is included in the msg_text as the message number. For example
  • If the msg_code is 0, the message number is 0000.
  • If the msg_code is -219 , the message number is 0219. The negative msg_code indicates that the message is a critical or non-critical error.
  • If the msg_code is +1036, the message number is 1036. The positive msg_code number indicates that the message is a warning.
The msg_code numbers for Spatial Extender stored procedures are divided into the three categories shown in the following table:
Table 1. Stored procedure message codes
Codes Category
0000 - 0999 Common messages
1000 - 1999 Administrative messages
2000 - 2999 Import and export messages
msg_text (message text)
The msg_text parameter consists of the message identifier, the message number, the message type, and the explanation. An example of a stored procedure msg_text value is: 

GSE0219N   An EXECUTE IMMEDIATE statement 
           failed. SQLERROR = "<sql-error>".

The explanation that appears in the msg_text parameter is the brief explanation. You can retrieve additional information about the message that includes the detailed explanation and suggestions to avoid or correct the problem through the SYSPROC.GET_MESSAGE procedure, the Db2 CLP, or the message reference for Spatial Extender.

For a detailed explanation of the parts of the msg_text parameter, and information on how to retrieve additional information about the message, see How to interpret Db2 Spatial Extender messages.

To diagnose stored procedures called implicitly by issuing the Spatial Extender commands, use the messages returned by the CLP. For more details, see How to interpret Db2 Spatial Extender messages

Working with stored procedures in applications

When you call a Db2 Spatial Extender stored procedure from an application, you will receive the msg_code and msg_text as output parameters. You can:
  • Program your application to return the output parameter values to the application user.
  • Perform some action based on the type of msg_code value returned.

Working with stored procedures from the Db2 command line

When you invoke a Spatial Extender stored procedure from the Db2 command line, you receive the msg_code and the msg_text output parameters. These output parameters indicate the success or failure of the stored procedure.

Suppose you connect to a database and want to invoke the ST_DISABLE_DB procedure. The following example uses a Db2 CALL command to disable the database for spatial operations and shows the output value results. A force parameter value of 0 is used, along with two question marks at the end of the CALL command to represent the msg_code and msg_text output parameters. The values for these output parameters are displayed after the stored procedure runs. 

call db2gse.st_disable_db(0, ?, ?)

  Value of output parameters
  --------------------------
  Parameter Name  : MSGCODE
  Parameter Value : 0

  Parameter Name  : MSGTEXT
  Parameter Value : GSE0000I  The operation was completed successfully.

  Return Status = 0

Suppose the msg_text returned is GSE2110N. Use the Db2 HELP command to display more information about the message. For example:


"? GSE2110"
The following information is displayed: 

GSE2110N    The spatial reference system for the
            geomentry in row "<row-number>" is invalid.
            The spatial reference system's 
            numeric identifier is "<srs-id>". 

Explanation: In row row-number, the geometry that is 
to be exported uses an invalid spatial reference system.  
The geometry cannot be exported. 

User Response: Correct the indicated geometry or 
exclude the row from the export operation by 
modifying the SELECT statement accordingly.

msg_code: -2110

sqlstate: 38S9A