Db2

I've raised a case with IBM to get clarification on this. Will update once I find out more.

I am impressed by the way you detailed everything. It is really going to help me a lot.

Thanks Sophia,

Before using in earnest, I strongly suggest waiting until IBM answers my Case about the page that says a Java UDF returning a LOB that is not supported (though it seems to work and does not throw the SQLSTATE that page says it should). So it looks to me like this is out-of-date documentation, but best to get confirmation. Also, I would then like to do some final testing.

Assuming all is good, there are some interesting boundary considerations when it comes to large LOBs and compression. In the case of my own immediate work requirement, it is an archiving requirement with little apparent need for speed. So I will probably use a COMPACT LOB (not generally recommended by IBM) for maximum spaces savings, and I won't having to worry about the folliowing...

In the case of a NOT COMPACT LOB, it may be worth considering the LOB buddy space mechanism, where Db2 divides large blocks of storage in two until the smallest size that can store the LOB is reached, Say that the compression reduces a LOB's size from 31MB to 17MB... this is of no benefit. You still need a 32MB segment to store that LOB in either the original or compressed form. So the effective compression ratio is zero, and it would be better to store the uncompressed LOB. By contrast, a compression that only reduces a LOB from 17MB to 15MB will actually save 16MB, because the data can now fit into a smaller 16MB segment.

Insert code could take these interesting properties into account when assessing whether to store data compressed or not. However, so long as your LOB data is generally getting compression ratios of at least 2x, there will nearly always be a real space saving and there is no need to worry about this relatively complicated calculation.

I would not use the Gzip function to compress LOB data that already fits in-line uncompressed. If you want to save space for these smaller LOBs, it is best to use Db2 compression.

------------------------------
Jeremy Rickard
------------------------------

Very interesting and informative post thanks for sharing .

OK, I promised to post an update. It took longer than I imagined but here at last...

Firstly, the case with IBM confirmed that Java UDFs can return LOB data types. The documentation that says otherwise is out-of-date, and will be fixed later on. Thank you to Jian in the support team and the developers who looked into this.

Secondly, I've done a fair bit of testing, and tuned buffer sizes with dramatic improvements. A couple of conclusions:
1) If you want to work with BLOBs larger than 64MB you will need to increase JAVA_HEAP_SZ.
2) On my laptop running Db2 on Ubuntu, an uncompressed 25MB JSON document compresses 6.8x in about 2 seconds, and uncompresses in about 1 second. Performance is now perhaps adequate for archive databases of an appropriate design (my requirement likely to be that), but I would not suggest using this in any database where response matters.
3) Smaller LOBs containing similar data run faster, more-or-less proportional to size.

Lastly, a first "official" release! Release 0.1, see: https://github.com/easydataservices/db2-compress

Please read the notes in the README, and be sure to test this works well for you before deploying.