Hi,

DB2 document says that the limit of statement variable lengh in DB2 is 18 characters.

But it accepts only 16 characters as such. I think, it internally appends some two characters, which I infer from the below error when I tried for 18 characters :

declare stmtaaaaaaaaaaaaaa statement; // It gave the below error with 18 character

Error :
--------

SQL0060W The "C" precompiler is in progress.
376 SQL0107N The name "STMTAAAAAAAAAAAAAA_2" is too long. The
maximum length is "18".
SQL0095N No bind file was created because of previous
errors.
SQL0092N No package was created because of previous
errors.
SQL0091W Precompilation or binding was ended with "3"
errors and "0" warnings.

But it works fine, if the following statement is given :

declare stmtaaaaaaaaaaaa statement; // with 16 characters

Can anyone tell, what is the exact variable limit for statement variables in DB2 ?

Please advice,

Thanks,
Sam

Have you used the same variable elsewhere in the program so that DB2 needs to append a "_2" at the end?

DB2 procedure :
===========

CREATE PROCEDURE refcur
DYNAMIC RESULT SETS 2
LANGUAGE SQL
p1: BEGIN
DECLARE mycursoraaaaaaaaa1_FOUND INTEGER DEFAULT NULL ;
DECLARE SQLCODE INTEGER DEFAULT 0 ;
BEGIN

DECLARE rec1_sno DECIMAL(22) ;
DECLARE rec1_sno1 DECIMAL(22) ;
DECLARE rec1_sno2 DECIMAL(22) ;
DECLARE mycursoraaaaaaaaa1_OPEN_FLAG INTEGER ;
DECLARE mycursoraaaaaaaaa1_sqlstring VARCHAR(4000) ;
DECLARE mycursor11111_STMT STATEMENT ;

DECLARE mycursoraaaaaaaaa1 CURSOR WITH HOLD WITH RETURN TO CALLER FOR mycursor11111_STMT;

SET mycursoraaaaaaaaa1_OPEN_FLAG = 1 ;
SET mycursoraaaaaaaaa1_sqlstring = 'SELECT * FROM test WHERE sno < 2 ' ;
PREPARE mycursor11111_STMT FROM mycursoraaaaaaaaa1_sqlstring ;
OPEN mycursoraaaaaaaaa1 ;

LOOP_LABEL1: LOOP
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET mycursoraaaaaaaaa1_FOUND = SQLCODE ;
SET mycursoraaaaaaaaa1_FOUND = 0;
FETCH FROM mycursoraaaaaaaaa1 INTO rec1_sno, rec1_sno1, rec1_sno2 ;
END ;
IF mycursoraaaaaaaaa1_FOUND = 100 THEN
LEAVE LOOP_LABEL1;
END IF;
END LOOP LOOP_LABEL1 ;
END ;
END p1
@


Below is the error I am getting when the above procedure is compiled in DB2 :

SQL0060W The "C" precompiler is in progress.
473 SQL0107N The name "MYCURSOR11111_STMT_3" is too long. The
maximum length is "18".
499 SQL0107N The name "MYCURSOR11111_STMT_3" is too long. The
maximum length is "18".
508 SQL4946N The cursor or statement name "CURS4" is not
defined.
541 SQL4946N The cursor or statement name "CURS4" is not
defined.
SQL0095N No bind file was created because of previous
errors.
SQL0092N No package was created because of previous
errors.
SQL0091W Precompilation or binding was ended with "6"
errors and "0" warnings.

-- END OF LOG FILE (SQLCODE: -1)

Please advice,

Thanks,
Sam