Db2 luw replace character in a clob

Koen Vannisselroy

Db2 luw replace character in a clob

Hello,

 

Has anyone an sql solution to read an xml file form a clob and search for a specific character and replace it with another character?

This is a question for db2 luw 10.5

 

regards

Michael Hannan

RE: Db2 luw replace character in a clob
(in response to Koen Vannisselroy)

XML File in a CLOB???? I guess its not really considered a "file" any more.

You are not using a special XML datatype, but rather  a plain  CLOB right? In which case the XML contents of the CLOB is not very relevant? The Character to be replaced is in a known position or all occurrences of that character to be replaced? Or to be replaced in a specific XML field? Normally DB2 stores XML in an XML datatype or a  BLOB under the covers  I think. 

I think the question is just a little vague, right now. 

Its probably not advisable to store XML in a CLOB unless processing of the XML is very minimal.

The SQL REPLACE function cannot operate on a CLOB, however it maybe possible to cast the CLOB to a VARCHAR and use REPLACE on that, provided that it is less than 32k in maximum length. I am more familiar with zOS, but I expect the other DB2 is similar. This is assuming that the XML part is not relevant. I have certainly written SQLs to cast CLOBs to VARCHAR before, to then allow me to use text functions on it, to split it up into pieces and other things. e.g. SYSPACKSTMT column STATEMENT containing SQL text.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 04, 2020 - 11:39 AM (Europe/Berlin)

Koen Vannisselroy

RE: Db2 luw replace character in a clob
(in response to Michael Hannan)

Michael,

Thank you for the reply.

I lost the discussion for using xml datatype versus clob unfortunately.

The character they want to replace is a hex(1a) on which the xml parser has problems with.

it is not everytime on the same place.

Michael Hannan

RE: Db2 luw replace character in a clob
(in response to Koen Vannisselroy)

So you can try something simple like (wild thought untested):

UPDATE table SET clobcol = REPLACE(
    CAST(clobcol as VARCHAR(30000)), X'1A', newval)
WHERE length(clobcol) < 30000
add key range predicates here
;

This might perform poorly, but adding a predicate using LOCATE_IN_STRING to find rows with X'1A' may perform equally badly or worse. Maybe most rows are to be updated.

Key range predicates are to make each Update take a more reasonable time before commit.

There are Text extender functions for DB2 processing of CLOBs etc. Read up to see if that is useful.

Might be much easier to unload the table, update the x'1A' with flatfile utility (ICETOOL on zOS) and load the data back again.

An application pgm might also do it better.

I have never tried anything like this, updating BLOBs or CLOBs. Should be possible though if don't try to do too many rows per UOW.

Michael Hannan,
DB2 Application Performance Specialist (zOS)
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 08, 2020 - 06:18 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 08, 2020 - 06:19 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 08, 2020 - 06:20 AM (Europe/Berlin)