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.

How did you come to that conclusion?

From IBM DB2 9.7 for Linux, UNIX and Windows Information Center :

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

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

What the hell does this mean?

WHERE W.status = VARCHAR(9)

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.

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.