sysibm.SQLCAMessage Function

By default, .NET Core Provider Driver for JDBC and SQL for HCL OneDB™ does not return localized error messages. Detailed and localized error messages from the server are expected, however, when property “retrieveMessagesFromServerOnGetMessage=true” is set in the connection URL.

The SQLCAMessage function is an SPL routine that supports the retrieval of detailed error message text from remote DB2® or HCL OneDB database servers to client applications that use the Distributed Relational Database Architecture™ (DRDA®) protocol. The SQLCAMessage routine is automatically created in every database of HCL OneDB instances that are configured as DRDA application servers. The .NET Core Provider Driver for JDBC and SQL client application must specify the 'sysibm' owner name to invoke this function from an ANSI-compliant database.

The SQLCAMessage function retrieves localized error messages, based on the SQLSTATE code in the SQL Communications Area (SQLCA).

The definition of this function uses IN, OUT, and INOUT parameters: 
CREATE function sysibm.SQLCAMessage ( 
      IN SQLCode       INTEGER, 
      IN SQLErrml      SMALLINT,   	
      IN SQLErrmc      VARCHAR(70),  
 	   IN SQLErrp       CHAR(8),   
      IN SQLErrd0      INTEGER, 		
      IN SQLErrd1      INTEGER,		
      IN SQLErrd2      INTEGER, 
      IN SQLErrd3      INTEGER, 
      IN SQLErrd4      INTEGER, 
      IN SQLErrd5      INTEGER, 
      IN SQLWarn       CHAR(11),  		 
      IN SQLState      CHAR(5),   		
      IN MessageFileName VARCHAR(20), 
      INOUT Locale    VARCHAR(33),
      OUT  Message  LVARCHAR(4096),
      OUT  Rcode      INTEGER)
      RETURNING  INTEGER
      EXTERNAL NAME ‘(SQLCAMessage)'
      LANGUAGE C
To invoke the function, you can use this syntax:
sysibm.SQLCAMessage

1  'sysibm'.SQLCAMessage ( error_code  ,  input_locale  ,  message_ file )
Argument Description Restrictions Syntax
error_number The SQLCODE value of the error, Must exist Expression
input_locale Name of the input locale for receiving the message. Default is the U.S. English locale (en_us). Must exist Identifier
message_file Name of the message file Must exist Pathname

The function retrieves the text from the specified message_file for the specified SQLCODE and input_locale. The return code indicates the success or failure of the call to execute the SQLCAMessage routine.

The HCL OneDB DRDA application server attempts to retrieve the error message text using the specified input parameters:
  • SQLCODE
  • input_locale, and
  • message_file

The HCL OneDB DRDA application server attempts to retrieve the error message text using the specified input parameters: The default message file (errmsgtxt) is used if the MessageFileName argument message_file is NULL. The default locale (en_us) is used to retrieve the error message if retrieval using the specified input_locale is unsuccessful. The token array is used to replace the tokens in the retrieved message text, if applicable.

If the retrieval is successful,
  • SQLCODE is removed from the error message,
  • the error message is copied to the OUT parameter ‘Message'
  • the locale used for retrieving the message is copied to INOUT parameter ‘Locale'.

If the retrieval is unsuccessful, the error message text "Message not found" is copied to the Message parameter.

For both cases, the OUT parameter Rcod is set to the return code for executing this SPL routine.

Detailed message for ISAM errors are supplied from the SQLERRD[0] value. ISAM error messages are concatenated to actual error message string and returned to the application.

For the codes of SQLSTATE values for which the SQLCAMessage function can return the corresponding error message text, see List of SQLSTATE Codes.