Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Problem in length of statement variables

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Have you used the same variable elsewhere in the program so that DB2 needs to append a "_2" at the end?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •