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

Question

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?

It's some strange mixture of DECLARE GTT and INSERT INTO. It's definitely not supported syntax. This site doesn't support GTT declarations, but you may amend CREATE TABLE to your DECLARE GTT + set ON COMMIT PRESERVE ROWS at the end. Note your wrong number of columns in the 1-st row as well — Mark Barinstein
– Mark Barinstein, Commented Jun 26 at 21:54
IBM DB2 LUW documentation states "INSERT ... VALUES introduces one or more rows of values to be inserted." — Dave Clark
– Dave Clark, Commented Jun 26 at 22:05
1st row does not have the "wrong" number of columns. Omitted columns are NULL by default. But, I added two explicit NULL columns to the first row and the error persisted, only this time it showed the two NULL columns as the previous text to the error position. — Dave Clark
– Dave Clark, Commented Jun 26 at 22:15
I can solve syntax issues later--after I find out how to pin down the failing line number. — Dave Clark
– Dave Clark, Commented Jun 26 at 22:16

Mark Barinstein · Accepted Answer · 2025-06-27 18:05:28Z

0

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

edited Jun 27 at 18:05

answered Jun 27 at 7:43

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Dave Clark Jun 27 at 14:35

Like I said, I can solve syntax issues later. The question isn't about the syntax. In fact, I want invalid syntax so that I can get effective debugging techniques set up.

Dave Clark Jun 27 at 14:36

Now, regarding CLP reporting the line number... I'm working with a remote database. Am I correct that I can't use CLP with a remote database?

Mark Barinstein Jun 27 at 17:09

@DaveClark You can work with CLP with remote db, of course. You must catalog the corresponding remote instance and database first.

Dave Clark Jun 27 at 18:02

"Catalog" where? The remote database exists, of course, and it has an instance defined to it. But, I am not a Linux DB2 DBA, nor CLP expert, so I'm not sure what you're referring to.

Dave Clark Jun 27 at 17:57

Remove everything about syntax from your answer and I will accept it.

Mark Barinstein Jun 27 at 18:15

You must catalog it at your db2 client (or server, if you installed it locally) instance. IBM Data Server Client and Data Server Driver types. You need to install and configure one of the: IBM Data Server [Runtime] Client to get db2 CLP.

Dave Clark Jun 27 at 19:29

OK, I'm on a Windows box. The DB2 database is on a Linux box. I run SQuirrel v4.8 locally as my DB2 client. But, let's say that I install the IBM client and get CLP. How do I execute it from Windows? Would that be in a CMD session? ...or do I have to do it from within the IBM client? Thanks.

|

Dave Clark · Accepted Answer · 2025-06-27 18:00:26Z

Multi-row INSERT in DB2 LUW works just fine. The following is fully tested.

Declare Global Temporary TABLE SESSION.CSUMMRY_Objects
    (   MyType      VarChar(8)      NOT NULL,
        MyLabel     VarChar(12)     NOT NULL,
        MyTable     VarChar(128)    NOT NULL,
        MyParent    VarChar(128)    DEFAULT NULL,
        MyList      VarChar(255)    DEFAULT NULL
    ) ON COMMIT PRESERVE ROWS WITH REPLACE IN TMPUSRSPACE1;

INSERT INTO SESSION.CSUMMRY_Objects ( MyType, MyLabel, MyTable, MyParent, MyList )
VALUES ('TABLE', 'CSUMMRY', 'CSUMMRY_RECORD', NULL, NULL),
       ('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'' )');

This doesn't answer the question posed, but it addresses previously false information given.

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related