Size of blob column

Hello all, I need to know the space allocated by a table that has a byte column in a blobspace. When I look at the size of the table, I don't see the portion of it that is stored in the blobspace. Is there a way to get this information? Thanks! ------------------------------ Celso Freitas ------------------------------ #Informix

This not giving you what you want ? Select sum(length(blob)) from .... Cheers Paul

might be good enough with large blobs, covering multiple blobpages, but pretty far off with blobs significantly smaller than blobpage size. More precise in terms of real blobspace blobpages usage might be: select sum((((length(blob_column) - 1) / (blobpage_size - 40))::int + 1) * blobpage_size)/1024 as blob_column_kB from ... ------------------------------ Andreas Legner ------------------------------

Or, simpler (maybe): SELECT SUM(((length(blobcol) + blobpagesize - 41)/(blobpagesize - 40)) * blobpagesize)/1024 AS blobtotal_kb FROM mytable; ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Paul, Andreas and Art! Regards ------------------------------ Celso Freitas ------------------------------

What if LENGTH() gets error '674: Routine (length) can not be resolved.' in 11.70 for BLOB columns? ------------------------------ Sincerely, Dennis ------------------------------

Install the compatibility data blade ( excompat). It contains a length-like function for BLOB and CLOB type columns that you can then remap as length(BLOB). Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Oops, wrong package. You need the DBMS_LOB package installed which contains the function dbms_lob.get_length() function which you can wrap in length(BLOB) and length(CLOB) functions. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Art: Does it exist for 11.70? ------------------------------ Sincerely, Dennis ------------------------------

Yes, it is the 'excompat' datablade for all versions. 11.7 I believe has 1.0. As a note for other folks, version 1.2 is out, fixes a bug where it didn't correctly calculate sizes > 2gb and is backwards compatible with older versions of Informix. select dbms_lob_getlength(column) from <table>. ------------------------------ Thomas Beebe Vice President xDB Systems, Inc Woodbridge 5713399029 ------------------------------

.

Hi Art & Company. I would have to look up my copy if this 15+ year-old script I wrote just before the big layoff in 2008. This was on IDS 10.(whatever). It had a similar functionality in mind, to check on how much blob space is being used by a table. My dim recollection is that there was no error on my SQLs for using the length() function on the blob columns. I need to get back to that; I already have a script (Perl) that tabulates the space occupied by every blessed tablespace in a server but when I developed that module I was working in an environment that did not use BLOBspaces. ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

I see this was edited, did you get it to work? If not, make sure you are using the blade manager to register it. (if you do it again on v14 use the sysbldprepare function) ------------------------------ Thomas Beebe Vice President xDB Systems, Inc Woodbridge 5713399029 ------------------------------

You register a database either with the BladeMgr or with the following function: EXECUTE FUNCTION sysbldprepare (<blade name>, 'create' );So: EXECUTE FUNCTION sysbldprepare ('excompat.1.2', 'create' );Once you do that in the database you are using, then you will be able to do things like: select sum( dbms_lob_getlength( blob_col ) ) from mytable; Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thomas: I found no other way to delete the message. Yes, I've managed to register the datablade--I got a successful report at least, Registering DataBlade module... (may take a while). DataBlade excompat.1.0 was successfully registered in database bora. A problem occurred when I tried to unregister the datablade, blademgr: ERROR: DataBlade excompat.1.0 could not be unregistered from database bora. Use 'show last log' for details of failures in module's scripts. ------------------------------ Sincerely, Dennis ------------------------------

Thomas: I've got a problem unregistering excompat.1.0 on 11.70: EXECUTE FUNCTION sysbldprepare('excompat.1.0','drop');# ^ #(U0001) - SYSBldUnregister - Unregistration failed for DataBlade module "excompat.1.0" - unknown error - check online log and sysblderrorlog table for more information A sysblderrorlog table contains a single row, order 1416 bld_id excompat.1.0 err_operation SYSBldRegister err_expected unexpected err_sql_stmt alter function dbms_random_jrand48 (integer)| with (Modify External Name = "jrand48"); err_sql_state XIX000:-674 : Routine (dbms_random_jrand48) can not be resolved. Comparing the datablade with that of 12.10, I found that regardless of the same version (1.0) two files differ, excompat.bld 74733 74801 objects.sql 99609 99608 The difference in text file objects.sql is as follows, 4559c4559 < "alter function dbms_random_jrand48 (integer) --- > "alter function dbms_random_jrand48 (bigint) How do I try to fix the problem? Replace the files of 11.70 with those of 12.10 and restart the server? Or something else? ------------------------------ Sincerely, Dennis ------------------------------

Dennis: Try this: find the DDL for the dbms_random_jrand48() function in the objects.sql file and try to CREATE it and ALTER it. Then see if you can deregister the datablade. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Art: Another trick helped: I UPDATEd sysbldobjects.drop_sql mistyped in 'objects.sql'. ------------------------------ Sincerely, Dennis ------------------------------

Awesome! Great catch! ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------