How to report of the space used by LOBS?

How to report of the space used by LOBS?

Database Analyses reports do not reflect information about LOB objects.

How to determine how much space clobs are using?

The space used by blobs is not accounted for in DBANALYS reports

How to evaluate how much space binary objects are occupying in the database?
Can LOB sizes be queried with ABL?

Upgrade to OpenEdge 11.6 or later where LOB support in database analysis reports was introduced.
 

For OpenEdge versions prior to 11.6, the space used by LOB objects is not accounted for in DBANALYS reports. 

The following example ABL code can be customized to report on the space currently occupied by LOB Objects housed in an OpenEdge database.

/* DEFINE BUFFER b_blobtable for <tablenamewithlobobjects>. */
DEFINE BUFFER b_blobtable for customer. 
DEFINE VAR i_count AS INT NO-UNDO. 
DEFINE VAR i_bytes AS INT NO-UNDO. 
 
RUN getTableStats (INPUT BUFFER b_blobtable:HANDLE, OUTPUT i_count, OUTPUT i_bytes). 
MESSAGE "Number of records: " i_count SKIP "Total Number of Bytes in the table: " i_bytes VIEW-AS ALERT-BOX. 
 
PROCEDURE getTableStats: 
DEFINE INPUT PARAMETER hBuffer AS HANDLE NO-UNDO. 
DEFINE OUTPUT PARAMETER iNumRecords AS INT64 NO-UNDO. 
DEFINE OUTPUT PARAMETER iNumBytes AS INT64 NO-UNDO. 
 
DEFINE VARIABLE hCountQuery AS HANDLE NO-UNDO. 
DEFINE VARIABLE hCountBuffer AS HANDLE NO-UNDO. 
DEFINE VARIABLE iDummy AS INTEGER NO-UNDO. 
DEFINE VARIABLE cLobList AS CHARACTER NO-UNDO. 
 
DEFINE VARIABLE mDummy AS memptr NO-UNDO. 
 
/* Check if handle passed in is usable. If it's not a buffer, error out. 
Exception: Handle temp-table by linking through to default buffer. */ 
 
IF NOT VALID-HANDLE(hBuffer) OR hBuffer:TYPE <> "BUFFER" THEN DO: 
    IF hBuffer:TYPE = "TEMP-TABLE" THEN DO: 
        hBuffer = hBuffer:DEFAULT-BUFFER-HANDLE. 
    END. 
    ELSE DO: 
        ASSIGN iNumRecords = ? 
            iNumBytes = ?. 
        RETURN ERROR. 
    END. 
END. 
 
/* Use separate buffer to ensure this does not accidentally reposition 
buffers the application code relies on. 
Exception: it's not allowed to create dynamic buffers for before-tables. 
Because of this, do NOT run this code while saving changes in a dataset 
to the database since we WILL reposition the before-buffer here. */ 
 
IF VALID-HANDLE(hBuffer:AFTER-BUFFER) 
    THEN hCountBuffer = hBuffer. 
ELSE CREATE BUFFER hCountBuffer FOR TABLE hBuffer 
    BUFFER-NAME "CountBuffer". 
 
    CREATE QUERY hCountQuery. 
 
/* Using PRESELECT for two reasons: 
a) Benchmarks indicated this is the fastest way to count 
b) More reliable snapshot when table to count is database table */ 
 
hCountQuery:SET-BUFFERS(hCountBuffer). 
hCountQuery:QUERY-PREPARE("PRESELECT EACH " + hCountBuffer:NAME + " NO-LOCK"). 
hCountQuery:QUERY-OPEN(). 
 
iNumRecords = hCountQuery:NUM-RESULTS. 
 
/* no need to do following block if table is empty... */ 
 
IF iNumRecords > 0 THEN DO: 
    IF hCountBuffer:HAS-LOBS THEN DO: 
        DO iDummy = 1 TO hCountBuffer:NUM-FIELDS: 
            IF LOOKUP(hCountBuffer:BUFFER-FIELD(iDummy):data-type,"BLOB,CLOB") > 0 
                THEN cLobList = cLobList + /* hCountBuffer:NAME + "." + */ 
                hCountBuffer:BUFFER-FIELD(iDummy):NAME + ",". 
        END. 
    clobList = RIGHT-TRIM(clobList,","). 
END. 
 
REPEAT: 
    hCountQuery:GET-NEXT(NO-LOCK). 
    IF hCountQuery:QUERY-OFF-END THEN LEAVE. 
    iNumBytes = iNumBytes + hCountBuffer:RECORD-LENGTH. 
    DO iDummy = 1 TO NUM-ENTRIES(cLobList): 
        copy-lob from hCountBuffer:buffer-field(ENTRY(iDummy,cLobList)):buffer-value to mDummy NO-CONVERT. 
 
 
/* Here the length of the string not the bytes from the blob-field */ 
/* message ENTRY(iDummy,cLobList) skip LENGTH(ENTRY(iDummy,cLobList),"RAW") view-as alert-box.*/ 
 

iDummy = get-size(mDummy).
        iNumBytes = iNumBytes + (if iDummy eq ? then 0 else get-size(mDummy)). 
        i_bytes = i_bytes + iNumBytes.
        set-size(mDummy) = 0. 
    END. 

END. 
END. 
 
DELETE OBJECT hCountQuery. 
DELETE OBJECT hCountBuffer. 
END PROCEDURE.