Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Unanswered: concatenated string limited to 256 characters

    Hello. I'm using 9.7 LUW and Data Studio 2.2 to convert some SQL Server stored procedures to DB2. I have run into an issue with concatenated being limited to 256 characters. I made sure the declared variable holding the strings was large enough, varchar(1000), but still have the issue. Is there any way I can increase the concatenated string size? Thank you.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jay8550 View Post
    I have run into an issue with concatenated being limited to 256 characters.
    How did you come to that conclusion?

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    From IBM DB2 9.7 for Linux, UNIX and Windows Information Center :

    "Maximum length of concatenated character string 2 147 483 647"

  4. #4
    Join Date
    Feb 2011
    Posts
    3
    When I run the stored procedure in the Data Studio debugger, the value of the variable representing the concatenated string is 256 characters long. I increased the variable display length in the debugger with no change in theoutput.

    string:
    SET n_sql='INSERT INTO SESSION.TABLE_TEMP '||
    'SELECT * FROM TABLE1 W '||
    'WHERE W.status = VARCHAR(9) and W.ADDDATE < CURRENT_TIMESTAMP - '||db_days||' DAYS '||
    'AND NOT EXISTS (select 1 from '||schema_name|| '.TABLEDETAILS T where T.TableKey = W.TableKey) ORDER BY TableKey FETCH FIRST 100 ROWS ONLY;';

    debugger output for n_sql:
    INSERT INTO SESSION.TABLE_TEMP SELECT * FROM TABLE1 W WHERE W.status = VARCHAR(9) and W.ADDDATE < CURRENT_TIMESTAMP - 10 DAYS AND NOT EXISTS (select 1 from schema1.TABLEDETAILS T where T.TableKey = W.TabletKey) ORDER BY TableKey FETCH FIRST 10


    error message:
    A database manager error occurred.SQLCODE: -104, SQLSTATE: 42601 - An unexpected token "" was found following "". Expected tokens may include: "FIRST 100 ROWS ONLY".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.11.69

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What the hell does this mean?

    WHERE W.status = VARCHAR(9)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How did you declared n_sql?

    Why didn't you use '9' instead of VARCHAR(9)?

    Anyway, how about removing a semicolon(";") from generated INSERT statement.

  7. #7
    Join Date
    Feb 2011
    Posts
    3
    Tonkuma

    Removing the semicolon helped to resolve the issue. I had other errors in my code, but that suggestion went a long way in fixing the issue. Thank you.

Posting Permissions

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