I would like to load some production data locally into my own PDSOE-licensed database on my workstation. I have a big SSD and lots of space. Performance will not be an issue (I expect I'll probably get better performance on my local SSD than what we have on the production EMC disk, even after many years of tuning - type II areas by size, etc).
I'm not a Progress DBA. I'm more of an OE/ABL developer.
The production data lived on an HP-UX/IA64 server; it was already backed up and restored on a staging/development HP-UX/IA64 server and I already deleted half of the data to make sure it should only be in the 100-200 GB range.
But it turns out that getting data into a local database on my Windows workstation is not going smoothly. It should be a *lot* easier than this...
I'm not sure why the dump/load has to be managed on a table-by-table and area-by-area and extent-by-extent basis. What a lot of micro-management...
To make matters worse, I get half-way thru my binary load only for it to fail, telling me my variable-length extent has filled up and shutting down my database! I do some research, find a way to enable large files for my variable-length extents, only to find that it won't let me. So now I have to pre-allocate a ton of tiny 2GB "fixed" extents for each storage area, just to move over the data . This is like a multi-day effort and I'm almost ready to go hire an OE DBA/expert to work on my laptop for me.
Before attempting this work, I was aware it wouldn't be just a backup/restore. I knew that moving data from an HP-UX server to a Windows desktop requires binary dump/load. But I guess I wasn't prepared to be jumping so many hoops. Micro-managing extents is especially annoying. If *any* database license allows for simple, variable lenth extents, with large files (>2 GB), it should be the PDSOE license. That would make this process quite a bit less painful than it is. Is there a back-door way to enable that feature? Am I going about this stuff all wrong? Does my experience sound typical for setting up a local copy of the production database?
I feel your pain. But unfortunately for you, the PDSOE license includes the Personal RDBMS product. Only the Enterprise RDBMS product allows for large files (extents >2 GB). So your only option is to set up the structure file of your target with as many extents per area as are needed to contain your data.
And yes, binary dump is per-table, but you can fairly easily write a program to output a shell script that binary dumps every table. Don't forget to dump out other data you may need, e.g. _User contents, current sequence values, SQL permissions.
I find it ridiculous that large files is a premium feature in this day and age. In fact I have created an enhancement request on the topic:
[View:https://community.progress.com/community_groups/products_enhancements/i/openedge_database_enhancements_-_tell_us_what_youd_like_to_see/make_large_file_support_available_without_enterprise:550:50]
Unfortunately though there is no way you can get around this.
In terms of your D&L, can you use your production system to create a load script for you? GO through the _file table to get the tables and build a windows batch file. Saves a lot of hassle.
Great! Even more hoop-jumping to look forward to....
I suppose someone is going to offer to sell me a third party product ("Roundtable"?) which makes it possible to perform a cross-platform backup/restore in a couple hours (instead of a couple full days of hard-core DBA busy-work)?
I never thought getting data locally into my PDSOE installation would be so hard. I've worked with OE/ABL on the programming side for years, and I guess I've always used shared databases and was happily ignorant about the DBA side ("behind the curtain"). This is an eye-opener to be sure.
> Only the Enterprise RDBMS product allows for large files (extents >2 GB).
Can the Personal RDBMS licence open a database that is already large files enabled?
Nope.
Yes, I found there was another product that Progress sells which actually makes a cross-platform backup/restore feasible. It comes from "bravepoint" and its named "pro d&l". This *should* be bundled for free with PDSOE. Most developers would benefit from having a copy of production data on their development workstations.
In our case, my Progress databases come from HP-UX. It was a *massive* pain to finally move it over - via the so-called "binary dump and load".
Last month when I attempted this stuff, I got scared quickly and had to R-U-N-N-O-F-T. (www.youtube.com/watch )
This month I tried again. I must have read about 2 dozen KB's (*not* a joke) on the topic of everything from storage extents, blocksizes, "proutil" commands for indexing, loading large data records before small ones, and on and on and on. I now have a five page document on how to get a developer copy of a production database. I know *infinitely* more than I ever wanted to about this (that is to say zero). Progress needs to seriously consider placing a huge abstraction layer on this process, whereby a person can point at a database on one server and click a button that says "Move It" . Remember that the database is only moving to a developer instance of the OE database server. It would normally be on a local SSD, for private use, and the risks for screwing something up are minimized. In this type of scenario a developer should not have to sift thru the minutiae in the KB, or waste days of manual effort. This shouldn't be so hard.
> Progress needs to seriously consider placing a huge abstraction layer on this process, whereby a person can point at a database on one server and click a button that says "Move It" .
To move db between platforms with little and big endian Progress needs to change the bits only in the block's headers (including the headers that are specific for a block's type). IMHO, it could be implemented as an option of probkup/prorest: -changeEndian.
> On Apr 14, 2016, at 9:36 AM, George Potemkin wrote:
>
> IMHO, it could be implemented as an option of probkup/prorest: -changeEndian.
backup does not have to do anything.
restore could do it automagically without you having to tell it.
And why this "easy" thing is not implemented? Could save so much time (and jobs?)
who said it was easy ?
This is now an enhancement request: https://community.progress.com/community_groups/products_enhancements/i/openedge_database_enhancements_-_tell_us_what_youd_like_to_see/permit_cross-platform_database_restore
Feel free to vote. :)
BTW:
Backup is from a different architecture platform. (9431)
This is a warning that architecture dependencies in the backup media (media and DB headers only) will be addressed so that the backup can be restored and used on the local architecture.
The message was introduced in 9.1D (May 08, 2002).
I guess there was a project for a cross platform backup.
There was a project a while back to actually implement this as George points out. However, there were some issues found during testing that were never addressed and the fact that the bi needed to be truncated prior to the backup meant that the original approach was unacceptable.