Alternate ways to concatenating a string in stored procedures

SoftwareEngineer3 · #1 (**permalink**) 08-13-12, 05:38

Hi,

Using DB2 9.7 fixpack 4 on Windows 7.

I have a situation where I am creating a huge SQL inline query into a 32k VARCHAR. The SQL query string I am generating is so large that apparently the string is cut-off before it has been fully constructed

I then execute the SQL query string using:

PREPARE S1 FROM V_SQL;
EXECUTE S1;

Are there alternative ways to create a query string? A 32k VARCHAR does not seem to be enough for me. The query has to be executed in one go though...

tonkuma · #2 (**permalink**) 08-13-12, 06:54

How about to use CLOB?

PREPARE - IBM DB2 9.7 for Linux, UNIX, and Windows

Quote:

FROM

Introduces the statement string. The statement string is the value of the specified host variable.

host-variable
Specifies a host variable that is described in the program in accordance with the rules for declaring character string variables. It must be a fixed-length or varying-length character-string variable that is less than the maximum statement size of 2 097 152 bytes. Note that a CLOB(2097152) can contain a maximum size statement, but a VARCHAR cannot.

expression
An expression specifying the statement string. The expression must return a fixed-length or varying-length character-string type that is less than the maximum statement size of 2 097 152 bytes.

SoftwareEngineer3 · #3 (**permalink**) 08-13-12, 07:27

Quote:

Originally Posted by tonkuma

How about to use CLOB?

PREPARE - IBM DB2 9.7 for Linux, UNIX, and Windows

Thanks, that solved it

Internet Services

Web Development

Digital Marketing

Consumer Tech