The RAISE_ERROR Function

< Day Day Up >

The `RAISE_ERROR` Function

The RAISE_ERROR function is a different type of function than we have discussed so far. It is not a column function because it does not take a group of rows and return a single value. Nor is RAISE_ERROR truly a scalar function because it does not transform column data from one state to another.

Instead, the RAISE_ERROR function is used to raise an error condition in the SQLCA. The user supplies the SQLSTATE and error description for the error to be raised. The error will be raised with the specified SQLSTATE and a SQLCODE of “438 .

The RAISE_ERROR function can be used to signal application program and data problems. One situation where RAISE_ERROR may prove useful is in a CASE statement such as

SELECT EMPNO,

  CASE WHEN SEX = 'M' THEN 'MALE  '

       WHEN SEX = 'F' THEN 'FEMALE'

 ELSE  RAISE_ERROR('70SX1', 'INVALID DATA, SEX IS NEITHER F NOR M.')

   END

   FROM DSN8810.EMP;

The value specified for SQLSTATE must conform to the following rules:

The value must be a character string of exactly five characters in length.
Only the characters '0' through '9' and uppercase 'A' through 'Z' may be used.
The first two characters cannot be '00' , '01' , or '02' .
If the first character is '0' through '6' or 'A' through 'H' , the last three characters must start with a letter from 'I' through 'Z' .
If the first character is '7' , '8' , '9' , or 'I' though 'Z' , the last three characters can be any valid character.

NOTE

Technically, the RAISE_ERROR function does return a value. It always returns NULL with an undefined data type. You must use the CAST function to cast it to a defined data type to return the value to a program.

< Day Day Up >

MQSeries Built-in Functions

DB2 Version 7 adds a number of new built-in scalar and table functions for use with IBM's message queuing software, MQSeries. These functions enable MQSeries messages to be received and sent. The MQSeries scalar functions are

`MQREAD`	Accepts two parameters; returns a message (as `VARCHAR(4000)` ) from the MQSeries location specified in the first expression, using the quality of service policy defined in the second expression.
`MQREADCLOB`	Accepts two parameters; returns a message (as a `CLOB` ) from the MQSeries location specified in the first expression, using the quality of service policy defined in the second expression.

NOTE

When performing either the MQREAD or MQREADCLOB function the operation does not remove the message from the queue specified in the first expression. Additionally, for both functions, if no messages are available a NULL is returned.

`MQRECEIVE`	Same as `MQREAD` , except the operation will remove the messages from the queue.
`MQRECEIVECLOB`	Same as `MQREADCLOB` , except the operation will remove the messages from the queue.

NOTE

When performing either the MQRECEIVE or MQRECEIVECLOB function, the operation will remove the message from the queue specified in the first expression. Additionally, for both functions, if no messages are available a NULL is returned.

MQSEND

This function is used to send messages to an MQSeries queue. It returns a value of 1 if successful; if unsuccessful . It accepts three (possibly, four) parameters. The data contained in the first expression will be sent to the MQSeries location specified in the second expression, using the quality of service policy defined in the third expression. A user defined by the message correlation identifier may be specified as an optional fourth expression.

Using these scalar functions you can easily read, retrieve, and send information from and to MQSeries message queues. The scalar functions operate one message at a time. At times, though, you might want to operate on multiple MQSeries messages. This requires table functions, and DB2 Version 7 supplies several of these as well. The MQSeries table functions are

`MQREADALL`	Returns all of the messages (as `VARCHAR` ) from the MQSeries location specified in the first expression, using the quality of service policy defined in the second expression. An optional third parameter can be used to limit the number of rows to return.
`MQREADALLCLOB`	Returns all of the messages (as `CLOB` ) from the MQSeries location specified in the first expression, using the quality of service policy defined in the second expression. An optional third parameter can be used to limit the number of rows to return.
`MQRECEIVEALL`	Same as `MQREADALL` except the operation will remove the messages from the queue.
`MQRECEIVECLOBALL`	Same as `MQREADALLCLOB` except the operation will remove the messages from the queue.

NOTE

When performing any of the MQSeries functions that read or receive data, an operation returns a table with the following columns :

MSG ” Contains the contents of the MQSeries message, either a VARCHAR(4000) or CLOB based on which function was used.
CORRELID ” Correlation ID used to relate messages ” VARCHAR(24) .
TOPIC ” The topic that the message was published with, if available ” VARCHAR(40) .
QNAME ” The queue name where the message was received ” VARCHAR(48) .
MSGID ” The assigned MQSeries unique identifier for this message ” CHAR(24) .
MSGFORMAT ” The format (typically MQSTR) of the message, as defined by MQSeries ” VARCHAR(8) .