Problem with CLOBS

[amitabh.mehra]

Hi

I m new to DB2..

I am using DB2 version8. I have a few queries (which i create
dynamically) stored in a table. The column which stores them is clob
32k. Each of these query has a ":wf_id" string in 2 places, which i
replace with another string (using the replace function in sysfun pkg
in db2) before executing them. its something like:

DECLARE sqlString VARCHAR(32000);
For i as select dataValue from test_table
do
SET sqlString = SYSFUN.REPLACE(i.dataValue, ':wf_id', 'abcdef');
-- PREPARE STMT from sqlString;
EXECUTE IMMEDIATE strSql;
END FOR;
I get errors with this replace function... i guess, it does not work
fine with CLOBs in version 8 of DB2. Is there some work around other
than changing my column type to varchar(32000).

I have tried cast function also as in :

For i as select cast(dataValue as VARCHAR(32000)) dataValueChar from
test_table

The error in this case is that the query that comes in i.dataValueChar
is too long... this again happens with replace function. If i dont
replace and just assign it to sqlString, all works fine.. something
like:
SET sqlString = i.dataValue;

Can someone please help me with this problem... or is there some other
way to run dynamic parameterized queries in db2 (with clob as a column
type).

Thanks in advance

[Knut Stolze]

am***********@gmail.com wrote:

Hi

I m new to DB2..

I am using DB2 version8. I have a few queries (which i create
dynamically) stored in a table. The column which stores them is clob
32k. Each of these query has a ":wf_id" string in 2 places, which i
replace with another string (using the replace function in sysfun pkg
in db2) before executing them. its something like:

DECLARE sqlString VARCHAR(32000);
For i as select dataValue from test_table
do
SET sqlString = SYSFUN.REPLACE(i.dataValue, ':wf_id', 'abcdef');
-- PREPARE STMT from sqlString;
EXECUTE IMMEDIATE strSql;
END FOR;
I get errors with this replace function... i guess, it does not work
fine with CLOBs in version 8 of DB2. Is there some work around other
than changing my column type to varchar(32000).

Why? REPLACE does support CLOBs:
http://publib.boulder.ibm.com/infoce...n/r0000843.htm

$ db2 "values replace(clob('abcdef', 32000), 'cd', 'xy')"

1
-----------
abxyef

1 record(s) selected.
So what exactly is the error that you got?

I have tried cast function also as in :

For i as select cast(dataValue as VARCHAR(32000)) dataValueChar from
test_table

The error in this case is that the query that comes in i.dataValueChar
is too long... this again happens with replace function.

What exactly is the error message that you got?

If i dont
replace and just assign it to sqlString, all works fine.. something
like:
SET sqlString = i.dataValue;

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[amitabh.mehra]

I get following error:
SQL0443N Routine "SYSFUN.REPLACE" (specific name "REPLACE2C") has
returned an error SQLSTATE with diagnostic text "SYSFUN:10"


On May 30, 1:33 pm, Knut Stolze <[e-mail]:

[e-mail] wrote:

Hi

I m new to DB2..

I am using DB2 version8. I have a few queries (which i create
dynamically) stored in a table. The column which stores them is clob
32k. Each of these query has a ":wf_id" string in 2 places, which i
replace with another string (using the replace function in sysfun pkg
in db2) before executing them. its something like:

DECLARE sqlString VARCHAR(32000);
For i as select dataValue from test_table
do
SET sqlString = SYSFUN.REPLACE(i.dataValue, ':wf_id', 'abcdef');
-- PREPARE STMT from sqlString;
EXECUTE IMMEDIATE strSql;
END FOR;

I get errors with this replace function... i guess, it does not work
fine with CLOBs in version 8 of DB2. Is there some work around other
than changing my column type to varchar(32000).

Why? REPLACE does support CLOBs:http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....

$ db2 "values replace(clob('abcdef', 32000), 'cd', 'xy')"

1
-----------
abxyef

1 record(s) selected.

So what exactly is the error that you got?

I have tried cast function also as in :

For i as select cast(dataValue as VARCHAR(32000)) dataValueChar from
test_table

The error in this case is that the query that comes in i.dataValueChar
is too long... this again happens with replace function.

What exactly is the error message that you got?

If i dont
replace and just assign it to sqlString, all works fine.. something
like:
SET sqlString = i.dataValue;

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[amitabh.mehra]

It works fine when the length of the string-part that is to be
reaplaced (arg 2 of replace) is equal or greater than the new string
path (arg 3 of replace). If arg 3 is greater than arg 2 i am getting
this error. (sysfun:10)

On May 30, 1:38 pm, [e-mail] wrote:

I get following error:
SQL0443N Routine "SYSFUN.REPLACE" (specific name "REPLACE2C") has
returned an error SQLSTATE with diagnostic text "SYSFUN:10"

On May 30, 1:33 pm, Knut Stolze <[e-mail]:

[e-mail] wrote:

Hi

I m new to DB2..

I am using DB2 version8. I have a few queries (which i create
dynamically) stored in a table. The column which stores them is clob
32k. Each of these query has a ":wf_id" string in 2 places, which i
replace with another string (using the replace function in sysfun pkg
in db2) before executing them. its something like:

DECLARE sqlString VARCHAR(32000);
For i as select dataValue from test_table
do
SET sqlString = SYSFUN.REPLACE(i.dataValue, ':wf_id', 'abcdef');
-- PREPARE STMT from sqlString;
EXECUTE IMMEDIATE strSql;
END FOR;

I get errors with this replace function... i guess, it does not work
fine with CLOBs in version 8 of DB2. Is there some work around other
than changing my column type to varchar(32000).

Why? REPLACE does support CLOBs:http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....

$ db2 "values replace(clob('abcdef', 32000), 'cd', 'xy')"

1
-----------
abxyef

1 record(s) selected.

So what exactly is the error that you got?

I have tried cast function also as in :

For i as select cast(dataValue as VARCHAR(32000)) dataValueChar from
test_table

The error in this case is that the query that comes in i.dataValueChar
is too long... this again happens with replace function.

What exactly is the error message that you got?

If i dont
replace and just assign it to sqlString, all works fine.. something
like:
SET sqlString = i.dataValue;

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany