Problem with CLOBS

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • amitabh.mehra@gmail.com
    #1

    Problem with CLOBS

    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

    Tags: None
    • Knut Stolze
      #2
      Re: Problem with CLOBS

      amitabh.mehra@g mail.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/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000843.htm


      $ db2 "values replace(clob('a bcdef', 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

        Comment

        • amitabh.mehra@gmail.com
          #3
          Re: Problem with CLOBS

          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 <sto...@de.ibm. comwrote:
          amitabh.me...@g mail.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...ic/com.ibm.db2....
          >
          $ db2 "values replace(clob('a bcdef', 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

            Comment

            • amitabh.mehra@gmail.com
              #4
              Re: Problem with CLOBS

              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, amitabh.me...@g mail.com 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 <sto...@de.ibm. comwrote:
              >
              amitabh.me...@g mail.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...ic/com.ibm.db2....
              >
              $ db2 "values replace(clob('a bcdef', 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

                Comment

                Previous template Next
                Working...