Script for Creating new Extents

jmac13

jmac13

Member
Hi All,

(im using open edge 10.2b)

We have quite a few clients where extents get larger than 2gig. They only have workgroup license and won’t spend to get the enterprise license (so I can’t turn on large extents for them). Has anyone done any scripts for monitoring extents so that when they get close to 2gig limit it can automaticity create a new extent?

Thanks
 
Not directly, but we always have one extent more than needed. We then have a report that runs on a regular basis and flags up if any variable extents are being used. Then we can add new extents to the system.
 
when you say you have one more extent than you need.. I've been taught to use the command prostrct addonline <dbname> <st file>
and the st file contains e.g.: d "Deckling":12,64 D:\cdmsdb\sop_12.d1 so that would close extent 11 and open a new 12th one.. but can i add 12 and 13 and it fills it as it goes?
 
Sorry I'm not sure of the mechanisms in use as I'm not DBA, but we have at least 1 fixed extent that is in use, and then the variable extent. The report sends an alarm if it detects that any Storage Area has hit the variable extent.
 
If you are running Linux, you can script it.

here is the command for file size:

Code: 
cd dbextdir
# finds all files with size greater than 1.5GB   
find ./ -size +1500000k > bigfiles.txt
cd biextdir
# finds all files with size greater than 1.5GB   
find ./ -size +1500000k >> bigfiles.txt

You can then just email the bigfiles.txt to whomever so they can look at it and be prewarned. Size to check is whatever you want. in the duh category you don't have to cd into the directory, you can just change the ./ to whatever directory you want to check.
 
One downside to waiting until you grow into the variable extent is that eventually you have to convert it to fixed when you add another extent to the area. You eventually wind up with a mish-mash of different file sizes in your fast-growing storage areas. Aside from the fact that these extents of various sizes are aesthetically unappealing for OCD types :), having many of these small files means you're consuming more file handles than you should given the data in your database. This in turn can mean more handles in use during index rebuilds, however with a Workgroup license that may be less of a concern as you can't do multi-threaded rebuilds anyway.

I try very hard to keep clients away using from Workgroup or even hearing about it. It's really not appropriate for mission-critical workloads. If you're at the point where you have multiple 2 GB extents, you should be looking at Enterprise. I don't believe in creating new problems that wouldn't otherwise exist in the name of saving a few bucks.

I think it would be better to regularly monitor the high water mark of your storage areas so you are aware of when you need to add extents before you grow into the variable. Collecting this data regularly (say, monthly) allows you to plot growth and do capacity and maintenance planning. Also, there is a VST (_Area-threshold, if memory serves) that allows you see which areas are nearing capacity. But that's just a rough guideline. Figuring out the percentage full by the HWM is a better way to go.
 
Actually its _AreaStatus
Code: 
/* Sum up the total size, utilization and free space for the storage area */
FOR FIRST _AreaStatus NO-LOCK WHERE _AreaStatus._AreaStatus-Areanum = _Area._Area-Num:
  ASSIGN dAreaTotalSize = DECIMAL ( _Areastatus._areaStatus-TotBlocks ) * _Area._Area-blocksize / 1024 / 1024 / 1024
    dAreaUsedSpace = DECIMAL ( _Areastatus._areaStatus-HiWater ) * _Area._Area-blocksize / 1024 / 1024 / 1024
    dEmptySpace    = DECIMAL ( _AreaStatus-Totblocks - _AreaStatus-Hiwater - _AreaStatus-Extents ) * _Area._Area-blocksize / 1024 / 1024 / 1024.
END.

From there you can calculate the utilization. We have thresholds that uses empty space for large storage areas (> 10GB) and utilization for small storage areas (<= 10GB) and a job that checks the area status every 3 minutes. As soon as an area is over utilized an attempt to add an extent automatically (via a shell script) is made :cool:

Heavy Regards, RealHeavyDude.
 
RealHeavyDude said:
Actually its _AreaStatus
Code: 
/* Sum up the total size, utilization and free space for the storage area */
FOR FIRST _AreaStatus NO-LOCK WHERE _AreaStatus._AreaStatus-Areanum = _Area._Area-Num:
  ASSIGN dAreaTotalSize = DECIMAL ( _Areastatus._areaStatus-TotBlocks ) * _Area._Area-blocksize / 1024 / 1024 / 1024
    dAreaUsedSpace = DECIMAL ( _Areastatus._areaStatus-HiWater ) * _Area._Area-blocksize / 1024 / 1024 / 1024
    dEmptySpace    = DECIMAL ( _AreaStatus-Totblocks - _AreaStatus-Hiwater - _AreaStatus-Extents ) * _Area._Area-blocksize / 1024 / 1024 / 1024.
END.

From there you can calculate the utilization. We have thresholds that uses empty space for large storage areas (> 10GB) and utilization for small storage areas (<= 10GB) and a job that checks the area status every 3 minutes. As soon as an area is over utilized an attempt to add an extent automatically (via a shell script) is made :cool:

Heavy Regards, RealHeavyDude.
Click to expand...

The one I was referring to is _AreaThreshold, which simply gives a value for each area which represents a percentage full (HWM / total blocks). The _AreaThreshold field shows 1 if the area is 80% full, 2 if it is 90% full, 4 if it is full, and 0 otherwise. I agree that _AreaStatus is the best table to use for monitoring. It's like the difference between a car dashboard with gauges versus one with idiot lights.
 
cool I think i can write something now..I understands robs one cause basically run it for an area e.g. DB syncloc area 7 and if it gets to 90% or 2 on_Areastatus I can run some code to create me a new extent. (see below for example). Have I got this right? thanks


Extents.PNG
 
jmac13 said:
cool I think i can write something now..I understands robs one cause basically run it for an area e.g. DB syncloc area 7 and if it gets to 90% or 2 on_Areastatus ...
Click to expand...

I think you mean _AreaThreshold. Actually, if I were you I would go with RHD's approach in using _AreaStatus for a more detailed view of your area consumption and rate of growth.

Ideally, you could incorporate not only percentage full for your areas but also extent file sizes, so you could determine how close you are to growing into your variable extents (if that matters to you).
 
yeah ur right sorry _AreaThreshold not _Areastatus. I wouldnt mind doing it RHD way.. but it doesnt seem to indicate how big the last extent is. just give me the total size etc but I want to know when say extent 10 is close to getting close to 2 gig so i create new one so the DB doesnt fall over.
 
Hi all (again)


I had a DB on site that the extent was 1.75 gig which i thought was 87.5% of 2gig so I thought If I ran a for each _AreaThreshold it would show 1 to indicate it 80% full but it showed 0. I am getting confused between areas and extent size? because I want a way to detect I’m getting close to the 2gig limit and the create a new extent. I can't seem to do this at the moment. currently we have a email that fired using blat that shows the via dir what the sizes of the extents are and i have to manually check and then increase if needed. anyone got any ideas thanks?


extent.png
 
Well, some testing shows me that _AreaThreshold doesn't do what I thought it did. I built a DB (no large file support), added a table to a new area, then added records to the area until _Areathreshold changed from 0 to 1. It never did. The DB just shut down when the variable extent hit 2 GB. So clearly, this VST doesn't tell you when the area HWM reaches 80% of the current total blocks in the area, nor does it tell you when the area has reached 80% of the maximum blocks possible in the area based on the existing structure.

The docs say that _Areathreshold = 1 means "Area is above 80 percent of maximum addressable threshold value". So I guess it's a comparison of _areastatus-totblocks to the theoretical maximum number of dbkeys in the area? If so, I'm not sure how useful that is when you have 64-bit dbkeys.

As per your screenshot, area 7 in database sop has eight extents totalling 14.58 GB on disk. This doesn't give us any indication of how full the extents are. Extent 8 could be almost full. Or if someone just purged the table(s) it could be completely empty.

I think the monitoring solution you're looking for will involve a join on _Area, _AreaExtent and _AreaStatus. _AreaStatus has the HWM and total blocks for each area, and _AreaExtent has size information for each extent in each area. I don't have any code at hand to do that, but I'll think about it if I get time.

Also, and I know I'm preaching to the converted here, based on area 7 alone this DB is really too big for the workgroup license. At this size the client should be on Enterprise, in which case 2 GB extents are no longer a concern.
 
You must log in or register to reply here.
Back
Top