PH59411: A SQLCODE -104 MAY BE ISSUED WHEN CREATING A STORED PROCEDURE WITH A SIGNAL STATEMENT USING AN UDF. 24/02/06 PTF PECHANGE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A SQLCODE -104 may be issued when creating a stored procedure
  with a SIGNAL statement using an UDF.
  The issue may occur after either v12 UI82954/PH49721 or v13
  UI82955/PH49721 was applied.
  The SIGNAL statement may get truncated and end unexpectedly
  causing SQLCODE -104.
  Without fixing PTF for PH49721, stored procedures with a SIGNAL
  statement using a compiled SQL scalar function would get an
  abend, and stored procedures with a SIGNAL statement using an
  in-line SQL scalar function would run correctly without
  truncation.
  
  Example create executed:
  --#SET TERMINATOR @
  CREATE PROCEDURE SYSADM.SP104M ()
  LANGUAGE SQL
  WITH EXPLAIN
  BEGIN
   SIGNAL SQLSTATE '70000'
   SET MESSAGE_TEXT =
   UDF_FUNC_CALL(
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'LONG TEXT XXXXXXXXXXXXXXXXXX' CONCAT
    'TEXT END' ) ;
  END@
  
  is going to get the following message issued:
  DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "CO".
  
  ADDITIONAL SYMPTOMS:
  SQLCODE104 SQLSTOREDPROC SQLINLINEUDF SQLSCALARUDF SQLUDF
  

Local fix

• BYPASS/CIRCUMVENTION:
  Please try to rewrite the failing statement to not exceed 256
  bytes.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 and Db2 13 for z/OS users of                      *
  * native SQL procedure, compiled SQL scalar                    *
  * function or advanced trigger.                                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Unexpected SQLCODE -104, -199, -010,                         *
  * etc could occur when a native                                *
  * SQL procedure, compiled SQL scalar                           *
  * function or advanced trigger contains                        *
  * a SIGNAL or RESIGNAL statement                               *
  * satisfies the following condition:                           *
  * 1. Contains a SIGNAL or RESIGNAL                             *
  * statement with a compiled SQL scalar                         *
  * user defined function, global                                *
  * variable, or XML type variable,                              *
  * special register, sequence, etc                              *
  * referenced in its diagnostic string                          *
  * expression, and the expression is                            *
  * very long.                                                   *
  * Or                                                           *
  * 2. With PH49721/UI82954/UI82955                              *
  * applied, contains a SIGNAL or RESIGNAL                       *
  * statement with a inline SQL scalar                           *
  * user defined function, special                               *
  * register, etc referenced in                                  *
  * its diagnostic string expression, and                        *
  * the expression is very long.                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  The following examples could explain the problem:
  1.
  CREATE VARIABLE GVAR1 INTEGER@
  CREATE PROCEDURE SYSADM.SP104M (IN  V_VAR1XXX INTEGER)
  LANGUAGE SQL
  BEGIN
    SET GVAR1 = 183;
    IF V_VAR1XXX = 0
    THEN
      SIGNAL SQLSTATE '70000'
       SET MESSAGE_TEXT =
        SUBSTR(
         'STR XXXXXXXXXXX X XXXXXXXXXXXXXXXXXXX ' CONCAT
         'XXXXXXXX XXX XXXXXX XXXXXXXXXX XXXXXX ' CONCAT
         'XXXX XXXXX X XXX XXXXXXXXXXXXXXX X XX ' CONCAT
         'X XXXXXXXXXXXXXXX X XXX XXXXXXXXX X XX' CONCAT
         'X XXXXXXXXX X XX X XXXXXX ABCD ' CONCAT
         'This is a test for signal with global variable',
          GVAR1, 48);
    END IF ;
  END@
  SQLCODE -010 is issued for SIGNAL statement inside above
  CREATE PROCEDURE statement with or without
  PH49721/UI82954/UI82955 applied.
  2.
  CREATE FUNCTION SYSADM.FN_XXX_XXXXXX(VAR VARCHAR(200),
    VAR1 CHAR(5), VAR2 CHAR(5), VAR3 CHAR(5), VAR4 CHAR(5),
    VAR5 CHAR(5))
  RETURNS VARCHAR(200)
  RETURN SUBSTR(VAR,1,20)||VAR1@
  CREATE PROCEDURE SYSADM.SP104M (
   IN  V_VAR1XXX INTEGER)
  LANGUAGE SQL
  BEGIN
    DECLARE V_VAR2XXXXXXXXXXXXX VARCHAR(5) DEFAULT 'Q5QQ';
    DECLARE V_VAR3XXXXXXXXXXX VARCHAR(5);
    DECLARE V_VAR4XXXXX VARCHAR(5);
    DECLARE V_VAR5XXXXX VARCHAR(5);
    DECLARE V_VAR6XXXXXXXXXXX VARCHAR(5);
  
    IF V_VAR1XXX = 0
    THEN
   SIGNAL SQLSTATE '70000'
   SET MESSAGE_TEXT =
   FN_XXX_XXXXXX(
   'STR XXXXXXXXXXX X XXXXXXXXXXXXXXXXXXX ' CONCAT
   'XXXXXXXX XXX XXXXXX XXXXXXXXXX XXXXXX ' CONCAT
   'XXXX XXXXX X XXX XXXXXXXXXXXXXXX X XX ' CONCAT
   'X XXXXXXXXXXXXXXX X XXX XXXXXXXXX X XX' CONCAT
   'X XXXXXXXXX X XX X XXXXXX ABCD ' ,
   CHAR(V_VAR2XXXXXXXXXXXXX),
   CHAR(V_VAR3XXXXXXXXXXX) ,
   CHAR(V_VAR4XXXXX) ,
   CHAR(V_VAR5XXXXX) ,
   CHAR(V_VAR6XXXXXXXXXXX)) ;
    END IF ;
  END@
  SQLCODE -199 is issued for SIGNAL statement inside above
  CREATE PROCEDURE statement after PH49721/UI82954/UI82955
  applied.
  

Problem conclusion

• Db2 was updated to handle the native SQL procedure, compiled
  SQL scalar function, advanced trigger containing the
  aformentioned SIGNAL or RESIGNAL statement correctly.
  Additional Keywords: SQLSP SQLNATIVESQLPL SQLSIGNAL SQLXML
                       SQLUDF SQLFUNCTION SQLTRIGGER SQLRESIGNAL
                       SQLGLOBALVAR SQLCODE104 SQLCODE199
                       SQLCODE010
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI96109 UI96110

Modules/Macros

• DSNXOYDA DSNHSMS1
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI96109

     UP24/03/26 P F403 {

• RD10 PSY UI96110

     UP24/03/26 P F403 {

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.