Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    268

    Unanswered: DB2 Stored Proc Error After Upgrade

    DB2 V9.5 FP5 - RHEL 5.2.

    I have restored a database from DB2 V8.2 to V9.5 and ran
    db2rbind MYDB -l logfile.txt all

    It gave me an error about statement heap size then I increased the STMTHEAP size to 3072 from 2048, then it ran successfully.

    My real problem is that I have a stored procedure with cursors that loads the tables by taking data from another table. This stored procedure runs without any errors on DB2 V8.2 server but it gives

    "SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22001"

    error on DB2 V9.5 FP5 server. I have read the explanation of the error but I can not find which statement violating the condition.

    How can I run this stored procedure and find the exact statement/SQL - Table/Column is in violation ?

    Thanks

    Mike

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    you could throw in consecutive handlers, checking for sqlstate 22001
    and signal an ever increasing value, which would sort of be a display to indicate the statement actually returning the 22001.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Oct 2004
    Posts
    268
    Thanks for the suggestion. I am not much of a programmer. I probably wouldn't know how to do that.

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    Probably when you upgrade the database, the codeset of the db has changed, this could mean a character would take 2 char to fit instead of 1 char before. For instance, "A" would take 1 char to store, but now it needs 2 char. Thus, one of the cursor variables isnt big enough to store the value... just a thought

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use
    i would look the other way. it is the value (contents) of a host variable that is to large.
    not knowing what the SP looks like, hard to guess.
    Dick Brenholtz, Ami in Deutschland

  6. #6
    Join Date
    Oct 2004
    Posts
    268
    Thanks for all the feed back. For some strange reason droping the stored procedure and re-creating it resolved the problem................

Posting Permissions

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