Posted by: Serge Rielau

A few days ago I did an internal presentation on improvements to DB2's compatibility with Oracle applications.
Doing so I stumbled upon one change I actually implemented myself that had nearly slipped my mind.
The change is very minor, and, on the face of it has nothing to do with Oracle applications.
What I implemented for DB2 9.7.3 were comparison operators for "small LOBs"
A small LOB in itself is a bit of an oxymoron, given that the L stands for Large.
Also Oracle allows a lot less native operations on LOBs than DB2 to begin with. And comparison operators are not amongst them.

Imagine you have a table that looks like this:

CREATE TABLE emp(empid     INTEGER,
                 name      VARCHAR(4000),
                 firstname VARCHAR(4000),
                 address   VARCHAR(4000),
                 salary    DECIMAL(31, 2),
                 title     VARCHAR(4000),
                 jobdesc   VARCHAR(4000),
                 review    VARCHAR(4000),
                 skills    VARCHAR(4000),
                 education VARCHAR(4000),
                 languages VARCHAR(4000));

OK, perhaps the columns in this table are a tad silly.
But who can earnestly say they have never seen string types maxed out (4000 is Oracle's max for VARCHAR in SQL).
column doesn't mean we can simply override it to another smaller value.

Now, given DB2's limit of 32KB (32677 to be exact) per row, how do you implement a schema like the above?
One way, of course is to split the table vertically. In that case a join is required to reconstitute the table.
This will be OK for selects. After all the join can be hidden in a view.
 
Another approach is to "lobify" some of the columns.
But LOB's are said to have some downsides:

• You cannot index LOBs
• You cannot compare LOBs
• LOBs are slow to access and insert since they are not subject to the buffer pool
• LOBs are said to be slow when retrieving them to the client.

I have already cleaned up with some of these myths in "Speeding up LOBS".

In DB2 9.7.3 we figured that the chances that someone wants to actually compare big lobs are really not that high.
We also figured that for those small LOBs which could have been VARCHARs to begin with we could simply compare them as VARCHARs.
If the actual size of the LOB is less than 32KB then the comparison will succeed. If it is bigger it will fail.
The functions supported are:

• "="
•  "<>"
• ">"
• ">="
• "<"
• "<="
• BETWEEN

Previously supported was LIKE, POSSTR, SUBSTR and most other string functions.
Not that the IN predicate is absent here for the time being.

However, I think that in many cases when strings are large there is no desire to index the column to begin with.
What's the point of indexing "remarks" or "description"?
These are candidates for full text indexes which is a different topic altogether, but not for a b-tree index.

So our silly table above could be tweaked as such:

CREATE TABLE emp(empid     INTEGER,
                 name      VARCHAR(4000),
                 firstname VARCHAR(4000),
                 address   CLOB(4000) INLINE LENGTH 100,
                 salary    DECIMAL(31, 2),
                 title     VARCHAR(4000),
                 jobdesc   CLOB(4000) INLINE LENGTH 200,
                 review    CLOB(4000) INLINE LENGTH 200,
                 skills    CLOB(4000) INLINE LENGTH 100,
                 education CLOB(4000) INLINE LENGTH 50,
                 languages CLOB(4000) INLINE LENGTH 100);

I have avoid changing name, firstname and title because they could reasonably indexed.

A query such as:

SELECT jobdesc FROM emp WHERE languages LIKE '%ENGLISH%' AND education = 'University"

will work unchanged thanks to comparison support for small LOBs
#Db2