School me on Dump and Loads

[doom1701]

I understand the concept--I've even done dump and loads. But I've always used scripts provided by someone else. I'm looking at the script that I used last, and it's pretty straight forward--a "dumpdb" function that gets the table name and the dump folder, which then runs _proutil db -C dump and checks for any errors. The bulk of the script is running the dumpdb function on every table in the database. The load script is essentially the same thing.

It's this list of tables that concerns me. It might be accurate for version x, but not for version x+1. Or we may have a modification that added a table. I'm curious how the pros do this. I'm also curious about system tables--our last D&L required me to run a user sync script because the D&L scripts didn't dump out the _users table. Infor has a script that rebuilds it (makes sense, since their D&L scripts didn't include it). Any reason not to include this? Are there other system table that should be included?

 

[Cecil Snodgrass]

The _user file is not a part of the application schema, don't know how your script is creating the dumpfile list for use by proutil, normally something like

for each _file where not _file-name begins "_":

File names begining with "_" are normally Progress meta-schema files and will have to be added as an exception to the logic that generates the dumpfile list.

I prefer to do ASCII dump/loads over binary, if the reason for the dump/load is to reclaim space.

 

[doom1701]

The _user file is not a part of the application schema, don't know how your script is creating the dumpfile list for use by proutil, normally something like

for each _file where not _file-name begins "_":

File names begining with "_" are normally Progress meta-schema files and will have to be added as an exception to the logic that generates the dumpfile list.

I'm honestly not sure how the script was generated; there are no _ tables in the list. Looking through the full output of for each _file:display _file-name it appears that none of the SYS items or the _ items are in the script. Most of those look like either DBMS stuff, database stats, or schema related. I'm not sure if any are needed besides the _Users table.

I prefer to do ASCII dump/loads over binary, if the reason for the dump/load is to reclaim space.

Why do you prefer ASCII over Binary? Heck, other than knowing the definition of those terms, I really don't know how the types of dumps differ. Does Binary dump full string widths (even if it's a bunch of nulls)?

 

[Cecil Snodgrass]

A binary load will place the records in the new database just as they were in the source database with the same data fragmentation (space from deleted records), there is not too much difference in a binary dump/load and restoring from backup. An ascii dump and load into a new empty database eliminates data fragments and conserves space.

In most cases a binary dump/load is faster. I perform ascii dumps with a Progress program I wrote that generates to dump procedures and runs them in parallel queues (10 - 50 or more in parallel depending on the horsepower of the server), it also splits the datafiles if you are limited by 2 GB file sizes and generates the bulkloader description file.

So it all depends on the purpose of the dump/load, if you want a fresh database with the least record fragmentation then ascii is the best. If you want to improve index performance, you may want to just rebuild indexes. You may want to compact the indexes which can be done online (version dependent).

In the newest OpenEdge versions with a properly designed database dump/loads are almost a thing of the past.

Some vendor may also have some limitations/features that require both a partial binary dump/load and a partial ascii dump/load as a part of their conversion process.

 

[TomBascom]

A binary load will place the records in the new database just as they were in the source database with the same data fragmentation (space from deleted records), there is not too much difference in a binary dump/load and restoring from backup. An ascii dump and load into a new empty database eliminates data fragments and conserves space.

This is not true. It is an old and popular myth but it has never been true.

The "binary" in binary dump refers to the format of the data in the output file. It is still a record by record dump and when it is loaded it is loaded in to freshly created records just as an ascii dump is. The only difference is that the data is not converted back and forth to ascii. This makes it slightly faster in most cases.

A binary dump & load reduces fragmentation and scatter (they are two different things) in exactly the same manner that an ascii dump and load does.

 

[TomBascom]

Proutil won't dump & load tables that begin with "_".

Depending on your Progress version sequences are another pain in the butt. Both sequences and _user are easy enough to handle manually if you just have one database to do. If you have more then you can script _user with the dictionary dump routines. Prior to 10.1 you needed to be able to compile on the fly to scripts sequences.

 

[TomBascom]

Generating an up to date list of tables is usually some variation of:

for each _file no-lock where _hidden = no:
  display _dump-name.
end.

Or you can use a more sophisticated tool. I have a highly parallel dump & load tool that I like to use. It can do either a binary d&l or it will do a BUFFER-COPY from old db to new. The latter is sometimes much faster because you eliminate the intermediate IO related to using the .d or .bd temp files and the index rebuilds.

 

[TomBascom]

BTW -- if your db is anything more than trivial I suggest that you stay far, far away from Infor's tools and techniques.

 

[TomBascom]

You might also find http://bravepoint.com/products/book_dump-load.html useful. It is written by Scott Dulecki of Bravepoint and thus has a bit of an MFG/Pro flavor to it (but you can ignore that).

 

[doom1701]

You might also find http://bravepoint.com/products/book_dump-load.html useful. It is written by Scott Dulecki of Bravepoint and thus has a bit of an MFG/Pro flavor to it (but you can ignore that).

Do I get a prize for having the most responses from Tom in a row?

I find it ironic that if you use the Infor tools they start with a massive page of warnings that say "Don't do this if you aren't Infor." (I'm paraphrasing). Why include it? Our script was actually written by a consultant I trust...unfortunately, Infor has become jerks and won't let them talk to us anymore.

I'll have to take a look at both your tool and the writeup from Bravepoint.

 

[Cecil Snodgrass]

This is not true. It is an old and popular myth but it has never been true.

The "binary" in binary dump refers to the format of the data in the output file. It is still a record by record dump and when it is loaded it is loaded in to freshly created records just as an ascii dump is. The only difference is that the data is not converted back and forth to ascii. This makes it slightly faster in most cases.

A binary dump & load reduces fragmentation and scatter (they are two different things) in exactly the same manner that an ascii dump and load does.

I defer to Tom (as always :blush, a binary dump is record by record. Wasn't there another way of performing a binary dump prior to V8?

 

[TomBascom]

There was an undocumented and unsupported v6-era binary dump utility. But it worked basically the same way.

 

[TomBascom]

Do I get a prize for having the most responses from Tom in a row?

I find it ironic that if you use the Infor tools they start with a massive page of warnings that say "Don't do this if you aren't Infor." (I'm paraphrasing). Why include it? Our script was actually written by a consultant I trust...unfortunately, Infor has become jerks and won't let them talk to us anymore.

I'll have to take a look at both your tool and the writeup from Bravepoint.

You are the customer. They are the vendor. You can do whatever you want until they start writing checks to you.

 

[doom1701]

You are the customer. They are the vendor. You can do whatever you want until they start writing checks to you.

I do what I want...but when the consultant tells me "I can't talk to you", there's little more I can do with them. I wish it were Infor telling me not to talk to the consultant...I'd laugh and do it anyway.

A little more background--the consulting firm is an Infor MA (reseller, essentially) that Infor contracted our implementation and installation through. So we are an Infor customer, but this MA had been our primary contact. For the first two years we would just contact the MA if we had questions, projects we wanted help with, etc. and deal directly with them.

Apparently Infor is hurting so much that they are threatening MAs if they do any direct work for Infor customers. So the MA can't talk to us unless Infor subcontracts them to talk to us. It's an ugly situation. I really need to just develop a relationship with a non-Infor partnered Progress consultant.

 

[TomBascom]

I just happen to know one of those non-"Infor MA" consultants...