Answered How do I check if I need a dump/load ?

[sentinelace]

I am running a redhat 5 server. Progress 10.1c. Is there a way to check if the database needs a dump/load while the databases are running? what are the exact commands on how to do this? IF not, how do I do it with them down?

 

[Rob Fitzpatrick]

There are many circumstances in which you may benefit from a dump and load, either partial or full. These would include moving from Type I to Type II storage, restructuring Type II storage areas, e.g. to relocate storage objects or change area attributes like records per block or blocks per cluster, addressing issues with record scatter or fragmentation, reclaiming disk space after a large data purge, improving index utilization, etc.

You can get a static analysis of your "dbname" database with the following command (assumes your DLC and PATH environment variables are set correctly):

proutil dbname -C dbanalys > dbname.dba

The resulting text file (dbname.dba) contains information about your tables, indexes, and DB in general. With a little massaging you can import the RECORD BLOCK SUMMARY section into Excel to get an idea of which are your large/fast-growing tables that should be in their own storage areas.

For a good overview of storage optimization, read Tom Bascom's Storage Optimization Strategies presentation, or view it.

Start by posting your DB structure file contents (within CODE tags for readability) and maybe we can make some preliminary suggestions.

 

[sentinelace]

I need to run this on my redhat server if possible. the dba file just says proutil: _dbutil was not found.

 

[Rob Fitzpatrick]

I need to run this on my redhat server if possible. the dba file just says proutil: _dbutil was not found.

You should be running this on your Red Hat server; I assumed that. Ensure your DLC and PATH environment variables are set correctly and exported. For example if OpenEdge is installed in /usr/dlc101c, do the following:

DLC=/usr/dlc101c
PATH=$DLC/bin:$PATH
export DLC PATH

Then run the proutil dbanalys command again.

 

[sentinelace]

in /usr I have dlc and a oe10 diretory. I did the following:

DLC=/usr/oe10
PATH=$DLC/bin:$PATH
export DLC PATH

same error.

 

[Rob Fitzpatrick]

in /usr I have dlc and a oe10 diretory.

I don't know exactly what you mean by this. Are both dlc and oe10 subdirectories of /usr? Or is one the parent of the other, e.g. /usr/dlc/oe10?

You need to find the fully-qualified path where OpenEdge 10.1C is installed. It contains a text file called version whose contents will look something like this:

OpenEdge Release 10.1C as of Sat Feb  9 14:16:03 EST 2008

Find all the files on your system called version. If you don't have a locate command in Red Hat, use an equivalent find command instead.

locate version | grep \/version$

Once you find the directory that contains your 10.1C installation, set your DLC to that location as I described above. Then your proutil command will work properly.

 

[TomBascom]

Before you start thinking about dumping & loading you might want to get some orientation on using Progress and being a DBA. There are tons of great DBA oriented sessions being offered at PUG Challenge this year including one that looks tailored to your situation: Congrats! You're a DBA -- Now What?

http://pugchallenge.org

 

[sentinelace]

version is in /usr/oe10

I ran the commands you gave me and still get:


OpenEdge Release 10.1C04 as of Fri May 29 22:13:10 EDT 2009
proutil: _dbutil was not found.

 

[TomBascom]

$DLC/bin must also be in the PATH

PATH=$DLC/bin:$PATH

 

[cj_brandt]

If you have problems using proutil, do you think it is wise to attempt a dump and load ? That probably sounded rude, but it wasn't supposed to be. Lots of things can go wrong with a dump and load so practice A LOT.

A lazy man's approach to dump and load for performance reasons is -
Do you have type I storage areas - if yes then dump each area, delete the area and recreate using type II.
If you already have type II storage areas, a dump and load is probably not going to help much.

 

[sentinelace]

I will not be doing the dump/load. I simply want to see if it needs done.

 

[Rob Fitzpatrick]

I will not be doing the dump/load. I simply want to see if it needs done.

Are you primarily responsible for the maintenance and performance of this database?

 

[TomBascom]

If you really want to know if it should be done you ought to come to PUG Challenge and learn about how to make that determination.

On the other hand if you just need an answer to justify (or repudiate) what someone has already decided you can go with any of the following:

• Yes!
• No!
• Yes, a dump and load should be done every X months.
• No, we are using Progress version X and it is no longer necessary to D&L.
• Yes -- dbanalys reports table scatter greater than X.
• No -- dbanalys shows no significant scatter.
• Yes -- dbanalys reports index utilization less than X.
• No -- dbanalys shows excellent index utilization.
• Yes -- users are complaining about performance.
• No -- users are perfectly happy.
• Yes -- we must dump & load to convert from type 1 to type 2 storage areas.
• No -- we already have type 2 storage areas.
• Yes -- we need to get our data out of the schema area.
• No -- we don't have any data in the schema area.

(Pick X to suit your needs...)