DB2 LUW - Get Compile-time Failing Line Number in Stored Procedure

I've compiled other stored procedures (from SQuirrel© v4.8) and was able to figure out the error messages sufficiently to find my errors until I get a successful compile. But this time I'm getting the following unhelpful, cryptic information.

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);Y', 'CSUMMRY_RECORD';), DRIVER=4.33.31
SQLState:  42601
ErrorCode: -104

I've previously interpreted this as the SQLERRMC=) part is where the failure occurred; the Y', 'CSUMMRY_RECORD' part is the text immediately before the failure; and, the ), part is the possible token(s) expected next. Well, if it failed on a right-paren and a right-paren was expected next, then why is that an error?

Anyway, I do find one place where that sequence occurs--in the first row of a multi-row insert into a temporary table. But the documentation says this syntax is supported by DB2 LUW.

INSERT INTO SESSION.MyConstraints ( MyType, MyLabel, MyTable, MyParent, MyList ) VALUES
(   ('TABLE', 'CSUMMRY', 'CSUMMRY_RECORD'),
    ('ARRAY', 'CHK_VT',  'CSUMMRY_RECORD_CSUM_CHECKS_VT',   'CSUMMRY_RECORD', '( CSUM_CORP,CSUM_BANK,CSUM_PERIOD )'),
    ('ARRAY', 'DEP_VT',  'CSUMMRY_RECORD_CSUM_DEPOSITS_VT', 'CSUMMRY_RECORD', '( CSUM_CORP,CSUM_BANK,CSUM_PERIOD )'),
    ('CHECK', 'CSUMMRY', 'CSUMMRY_RECORD',                              NULL, '( CSUM_BANK = ''L'' OR CSUM_BANK = ''X'' )'),
    ('CHECK', 'CHK_VT',  'CSUMMRY_RECORD_CSUM_CHECKS_VT',   'CSUMMRY_RECORD', '( CSUM_BANK = ''L'' OR CSUM_BANK = ''X'' )'),
    ('CHECK', 'DEP_VT',  'CSUMMRY_RECORD_CSUM_DEPOSITS_VT', 'CSUMMRY_RECORD', '( CSUM_BANK = ''L'' OR CSUM_BANK = ''X'' )')
);

So, I want to confirm the failing line number. I've done my research and found the following two methods that seem to be run-time solutions. Correct?

• SQLCA ... SQLERRD(3) field, and
• GET DIAGNOSTICS ... DB2_LINE_NUMBER is apparently not a DB2 LUW thing.

I also tried the following--to no avail (only a null value returned).

• Values DBMS_UTILITY.FORMAT_CALL_STACK()
• Values DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()

Is there any other means of finding out the failing line number of a stored procedure at compile time?

---

ADDITIONAL NOTE: I did find how to change this message format:

• Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);Y', 'CSUMMRY_RECORD';), DRIVER=4.33.31

To this slightly more helpful message format:

• Error: An unexpected token ")" was found following "Y', 'CSUMMRY_RECORD'". Expected tokens may include: ")".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.33.31

I added the following to the URL for the remote database:

• mydatabase:retrieveMessagesFromServerOnGetMessage=true;

But I still want to know if there are any other means of finding out the failing line number of a stored procedure at compile time?

Look at the following example.

--#SET TERMINATOR @

CREATE OR REPLACE PROCEDURE TEST()
BEGIN

DECLARE GLOBAL TEMPORARY TABLE SESSION.MyConstraints ( 
    MyType  VARCHAR(100)
  , MyLabel VARCHAR(100)
  , MyTable VARCHAR(100)
  , MyParent    VARCHAR(100) DEFAULT ''
  , MyList  VARCHAR(100) DEFAULT ''
) WITH REPLACE ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.MyConstraints ( MyType, MyLabel, MyTable, MyParent, MyList ) VALUES
--(  -- Wrong (SQL0104N)
    ('TABLE', 'CSUMMRY', 'CSUMMRY_RECORD'),                -- Wrong (SQL0117N)
    --('TABLE', 'CSUMMRY', 'CSUMMRY_RECORD', NULL, NULL),  -- Correct
    ('ARRAY', 'CHK_VT',  'CSUMMRY_RECORD_CSUM_CHECKS_VT',   'CSUMMRY_RECORD', '( CSUM_CORP,CSUM_BANK,CSUM_PERIOD )'),
    ('ARRAY', 'DEP_VT',  'CSUMMRY_RECORD_CSUM_DEPOSITS_VT', 'CSUMMRY_RECORD', '( CSUM_CORP,CSUM_BANK,CSUM_PERIOD )'),
    ('CHECK', 'CSUMMRY', 'CSUMMRY_RECORD',                              NULL, '( CSUM_BANK = ''L'' OR CSUM_BANK = ''X'' )'),
    ('CHECK', 'CHK_VT',  'CSUMMRY_RECORD_CSUM_CHECKS_VT',   'CSUMMRY_RECORD', '( CSUM_BANK = ''L'' OR CSUM_BANK = ''X'' )'),
    ('CHECK', 'DEP_VT',  'CSUMMRY_RECORD_CSUM_DEPOSITS_VT', 'CSUMMRY_RECORD', '( CSUM_BANK = ''L'' OR CSUM_BANK = ''X'' )')
--)  -- Wrong (SQL0104N)
;

END
@

It's constructed intentionally to raise a compilation-time error. We get SQLCODE -117 here.

When we run it with db2 CLP, it informs you about the corresponding erroneous line (LINE NUMBER):

$ db2 -f 1.sql
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0117N  The number of values assigned is not the same as the number of
specified or implied columns or variables.  LINE NUMBER=12.  SQLSTATE=42802

You may get the same programmatically, with SQLERRD(3).

$ db2 -a -f 1.sql
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -117   sqlerrml: 0
 sqlerrmc:
 sqlerrp : SQLNQ25E
 sqlerrd : (1) -2145779603      (2) 0                (3) 12
           (4) 0                (5) -110             (6) 0
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
           (7)      (8)      (9)      (10)       (11)
 sqlstate: 42802