DB2 LUW CLOB Data comparison needed between two tables and Except function.
Hi All
I have two tables with CLOB data type in a DB2 LUW 10.5 DB.
I want to compare all the columns and rows in the two tables and want to find any mismatches
It looks EXCEPT function does not work for CLOB data types columns.
Any suggestions on this Please.
Thanks in advance.
I have two tables with CLOB data type in a DB2 LUW 10.5 DB.
I want to compare all the columns and rows in the two tables and want to find any mismatches
It looks EXCEPT function does not work for CLOB data types columns.
Any suggestions on this Please.
Thanks in advance.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
You'll need to call DBMS_LOB.COMPARE() on the CLOB data. It's more work for you, but it will work.
DB2 handles LOB data differently than other row data because of its large size. A row is constrained to the block size. You couldn't have LOB data unless that constraint could be bent or broken. DB2 solves that by placing LOB data in another tablespace the doesn't utilize the normal block structure. (Note: DB2 can store LOB data in-line when the row, including the LOB data, will fit in a data block.) DB2 also does direct I/O on the LOB tablespace and does not cache this data in the bufferpool.
All that said, use EXCEPT to find mismatches in the non-LOB data. If the table(s) have a primary key, use INTERSECT to find the rows that match and call DBMS_LOB.COMPARE() on the LOB data in the matching rows.
Ugly, but it should work for you.
Kent