Defect or not!
I do have an opened case with the great Richard Shulman.
All my CLOB fields are in there own AREA.
This month my CLOB area growth is up to 8 GB, why????
I dumped the CLOB - the size for the most part is really small - 1KB Records: 413852 size : 1K raw : 9 or 10 in length
Database Block Usage for Area: Lobs Active blocks: 990207 Data blocks: 989702 Free blocks: 505 Empty blocks: 193 Total blocks: 990400 Extent blocks: 4 Records/Block: 1 Cluster size: 512
because you have maximum rows per block set to 1 ???
Well that is a problem.
That was best practice, I guess it does not work for us.
so my block size is 8K and the the max size for the CLOB fields are 100M
what are the recommendation for the RPB, cluster size, and CLOB field size?
Thanks in advance!
That is NOT best practice. Ever. For /any/ situation.
Set maximum rows per block to 64.
Set cluster size to 8 or 64. 512 is rarely needed.
The maximum clob size is irrelevant to sapce allocation. It is there to help applications detect errors. By setting a maximum size for blobs that are know to be always small (for example, a thumbnail or icon or other small image), you can prevent errors. in such cases a 1 GB blob is always wrong.
Thank you Gus! I read that in KB 000022014 "There is a strong argument in defining 1 rpb for Lob areas (Type I or Type II)" .
> I dumped the CLOB - the size for the most part is really small - 1KB Records
Then why they are defined as Large Objects? It's just an artificial record fragmentation = performance degradation. The limits for the sizes of Progress records is 32K. 1KB field could be a small part of a record.
I agree 100 percent.. they (developers) did not know what size the CLOB would be. Or they could of program some Boolean code,,,, something like if the data is less then 32k the put data in a normal field.
I changed the RPB to 64, and dumped and loaded the records, however still same size. Why are the CLOBs reserving more space than is necessary?
Check dbrpr/13. Display Block Contents
for any data block in lob area.
how much space is necessary?
how do you know this?
I created a new LOB area with 64 rpb (test db) and dumped (from Production) and reloaded the new AREA (Test db) and still the same size as before. I know this by post database growth and pre database growth (ABL program internal size - FOR EACH _AreatStatus etc..).
I can't do the dbrpr right now - I will need to restore a fresh copy of Production soon.
> I can't do the dbrpr right now
You can ;-)
Awesome! I don't know what all this means
/dy/db/data->sudo sh ./viewdbblock.sh carolina 72
viewdbblock.sh Release V1.4 as of Jul 21, 2012
Area Info: "Lobs":72,1;512
Dbkey: 2 (Block 2)
Blocksize: 8 (KB)
Size: 2.02803e+06 (KB)
Offset: 16 (KB)
./viewdbblock.sh: 2.02803e+06: Syntax error
Creating temp database... Please, wait.
0000 bk_dbkey: 0x00000002 2
bk_type: 0x0c 12 (Object Block)
bk_frchn: 0x7f 127 (NOCHN)
bk_incr: 0x0001 1
bk_nextf: 0x00000000 0
bk_updctr: 0x00001559 5465
0010 bkCheckSum: 0x8ad9 -29991
bkHeaderSize: 0x0040 64
objectId: 0x0000 0
partitionId: 0x0000 0
objectType: 0x0008 8
bkObjDbkey: 0x0000000000000002 2
0020 bkDbkey: 0x0000000000000002 2
bkNextf: 0x0000000000000000 0
0030 bkLastBiNote: 0x0000000000000000 0
Reserved: 0x0000 0
Reserved: 0x00000000 0
0040 nextCluster: 0x0000000000000000 0
prevCluster: 0x0000000000000007 7
0040 totalBlocksOld: 0x0000000000000000 0
hiWaterBlockOld: 0x0000000000000000 0
chainFirst[FREECHN]: 0x0000000000000007 7
0050 chainFirst[RMCHN]: 0x0000000000000000 0
chainFirst[LOCKCHN]: 0x0000000000000000 0
0060 numBlocksOnChainOld[FREECHN]: 0x0000000000000000 0
numBlocksOnChainOld[RMCHN]: 0x0000000000000000 0
numBlocksOnChainOld[LOCKCHN]: 0x0000000000000000 0
0070 chainLast[FREECHN]: 0x0000000000000007 7
chainLast[RMCHN]: 0x0000000000000000 0
0080 chainLast[LOCKCHN]: 0x0000000000000000 0
objectId: 0x0000 0
objectType: 0x0008 8
0090 serialNumber: 0x0000000000000000 0
firstFreeCluster: 0x0000000000000000 0
00A0 lastFreeCluster: 0x0000000000000000 0
totalBlocks: 0x00000000001b28bc 1779900
00B0 hiWaterBlock: 0x00000000001b27ff 1779711
numBlocksOnChain[FREECHN]: 0x00000000000001f9 505
00C0 numBlocksOnChain[RMCHN]: 0x0000000000000000 0
numBlocksOnChain[LOCKCHN]: 0x0000000000000000 0
00D0 partitionId: 0x0000 0
Block Info: carolina.area.72.dbkey.2.160429_150523.txt