UTF-8 or CODEUNITS32 issue?

[Troels Arvin]

Hello,

I have the below quoted procedure which has worked well with non-UTF-8
pre-11.1 DB2s for a long time. But when I try to define the procedure in
an UTF-8 v. 11.1.1.1 database, I get:

DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned: SQL0781N Condition "ERR_STATE" is not defined or the
definition is not in scope. LINE NUMBER=21. SQLSTATE=42737

I suspect this has something to do with character set stuff. The new
database has the following DB configuration parameter:
STRING_UNITS = CODEUNITS32

Does someone have an idea on how to proceed?

=====================================================
CREATE OR REPLACE PROCEDURE chkstr_date_(IN strval VARCHAR(254))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
DECLARE dateval DATE;
DECLARE retval INT DEFAULT 1;
DECLARE err_state CHAR(5) DEFAULT '?????';
DECLARE err_code INTEGER DEFAULT 0;
DECLARE err_msg VARCHAR(70) DEFAULT '?????';
DECLARE SQLSTATE CHAR(5) DEFAULT '?????';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
VALUES (SQLSTATE,SQLCODE) INTO err_state,err_code;
SET err_msg = 'While attempting to cast to DATE, got error
code: '||CHAR(err_code);

IF err_state='22007' THEN SET retval = 0;
ELSEIF err_state='22008' THEN SET retval = 0;
ELSE RESIGNAL SQLSTATE err_state SET MESSAGE_TEXT =
err_msg;
END IF;
END;

IF strval IS NULL THEN SET retval=NULL;
ELSE
SET dateval=DATE(strval);
RETURN retval;
END IF;
END
=====================================================

[Troels Arvin]

Hello,

Previously, I wrote:
[...]

Post by Troels Arvin
DECLARE err_state CHAR(5) DEFAULT '?????';

[...]

Changing it to CHAR(5 OCTETS) helped.

[b***@gmail.com]

Post by Troels Arvin
Hello,
[...]

Post by Troels Arvin
DECLARE err_state CHAR(5) DEFAULT '?????';

[...]
Changing it to CHAR(5 OCTETS) helped.
--
Troels

OK But Troels why did this help?

[Troels Arvin]

Hello,

Post by b***@gmail.com
OK But Troels why did this help?

I think it's because when STRING_UNITS is set to CODEUNITS32, then the
following

DECLARE err_state CHAR(5) DEFAULT '?????';

really is a "short hand" for

DECLARE err_state CHAR(5 CODEUNITS32) DEFAULT '?????';

And CHAR(5 CODEUNITS32) appears to be in conflict with the "RESIGNAL
SQLSTATE" operation which seems to only accept CHAR(5 OCTETS) values.