DB2 UDB v8.2 (FP11) on AIX 5.2.
I'm not an SQL guru, so please be gentle with me.
I have a table that contains a BLOB(312MB) column, and I'd like to
determine, in an efficient way, the row-counts to BLOB size ratio. The
output would be something like this:
BLOB Size (MB) Rowcount
0-32 2540
33-64 5680
65-96 56402
...
281-312 560
Any assistance much appreciated.
Thank you.
Knut Stolze - 27 Oct 2006 10:23 GMT
> DB2 UDB v8.2 (FP11) on AIX 5.2.
>
[quoted text clipped - 10 lines]
> ...
> 281-312 560
A simple grouping will do the trick:
SELECT ( LENGTH(blob_column) / 32 ) * 32 AS blob_size,
COUNT(*) AS row_count
FROM ...
GROUP BY ( LENGTH(blob_column) / 32 ) * 32
ORDER BY 1
If you want to have exactly your above formating, a little bit of polishing
is required:
SELECT RTRIM(CHAR(blob_size - 31)) || '-' || RTRIM(CHAR(blob_size)),
row_count
FROM ( SELECT ( LENGTH(blob_column) / 32 ) * 32 AS blob_size,
COUNT(*) AS row_count
FROM ...
GROUP BY ( LENGTH(blob_column) / 32 ) * 32 ) AS t
ORDER BY 1

Signature
Knut Stolze
DB2 Information Integration Development
IBM Germany
pike - 27 Oct 2006 13:13 GMT
Thank you very much Knut. Perfect.