Initial value challenge

[Casper]

Hi fellow progress talkers,

OpenEdge 10.1B
AIX.Linux

We have had this great idea to make our application able to cope with multiple (financial) administrations. In effect this means that many of the 600+ tables needed an extra field (admincode). Most of these fields are primary key fields.
We have this reconfiguration procedure for new releases which consists of loading df files, loading data in systems tables and post dataprocessing. The problem we are facing now is that all records in the tables with this new admincode field need to have admincode = '01'. What would in your opinion be the fastest way to do this?

In our standard reconfiguration procedure we update the tables in the postdf phase of the reconfiguration procedure with a single user connection on a shutdown database running a program which updates all necessary tables. For relatively small databases of 3-5 GB this process takes some 13 hours to finish. WhichIMO is unacceptable long.

Would ascii dump, change .d file, load data, rebuild indexes be fast?
Would inactivate indexes, update data, rebuild indexes be fast?
Should I start up my database before udating?

Any suggestions would be welcome.

TIA,

Casper.

BTW, I know I could do tests with all possible solutions and I know it is also very much system dependent, but since the process takes so much time to run I am hoping to find some plausible solutions here, which can give me a starting point to start my tests.... (bit lazy )

 

[joey.jeremiah]

Hi fellow progress talkers,

OpenEdge 10.1B
AIX.Linux

We have had this great idea to make our application able to cope with multiple (financial) administrations. In effect this means that many of the 600+ tables needed an extra field (admincode). Most of these fields are primary key fields.
We have this reconfiguration procedure for new releases which consists of loading df files, loading data in systems tables and post dataprocessing. The problem we are facing now is that all records in the tables with this new admincode field need to have admincode = '01'. What would in your opinion be the fastest way to do this?

In our standard reconfiguration procedure we update the tables in the postdf phase of the reconfiguration procedure with a single user connection on a shutdown database running a program which updates all necessary tables. For relatively small databases of 3-5 GB this process takes some 13 hours to finish. WhichIMO is unacceptable long.

Would ascii dump, change .d file, load data, rebuild indexes be fast?
Would inactivate indexes, update data, rebuild indexes be fast?
Should I start up my database before udating?

Any suggestions would be welcome.

TIA,

Casper.

BTW, I know I could do tests with all possible solutions and I know it is also very much system dependent, but since the process takes so much time to run I am hoping to find some plausible solutions here, which can give me a starting point to start my tests.... (bit lazy )

Hi Casper,

I have limited DBA experience but 13 hours for 3 to 5 gigs feels like alot. Did dumping take most of the time ? I did a dump and load awhile ago on an average sized 13 gig, ~30 milion records database that took between 2 to 3 hours all together.

I'm not sure I understand the process you're using but from my experience deactivating and rebuilding indexes is much faster then loading records with active indexes. Although, I remember there was a very big performance improvement in the index rebuild util in release 10.

In regrads to parrallel dump and loads, tips and tricks, I'll leave that to the experts, when Tom gets here.

 

[TomBascom]

Having the db in single user mode is killing you. If you were trying to make this slow that would be one of the most effective things that you could do.

I kind of like the idea of modifying the .d files prior to loading them.

Usually loading with indexes off and doing an index rebuild is going to be faster. YMMV. Especially if you have an overpowered server -- as if there can be too much power ;-)

Starting the db with -i should also help. On the downside if it crashes before you are done then you get to start over. On the plus side it has been known to make enough of a difference that you can start over a couple of times and still be done faster ;-) (Ok, that was a long time ago -- but it is still good for a nice improvement and the risk of a crash is pretty low.)

 

[Casper]

Thanks Tom and Joey for your replies. I'll start doing some testing early this week. I'll let you know the results.
I'd personally opt for the dump data and change .d file route. Which looks rather promising to me...

Casper.

 

[joey.jeremiah]

... dump data and change .d file route. Which looks rather promising to me...

really interesting to hear how changing the .d file before load turns out ?

we'll call it the Casper Damkat method

 

[timk519]

Hi fellow progress talkers,
We have had this great idea to make our application able to cope with multiple (financial) administrations. In effect this means that many of the 600+ tables needed an extra field (admincode). Most of these fields are primary key fields.
We have this reconfiguration procedure for new releases which consists of loading df files, loading data in systems tables and post dataprocessing. The problem we are facing now is that all records in the tables with this new admincode field need to have admincode = '01'. What would in your opinion be the fastest way to do this?

Bring the db up in MU mode.

Fire off as 10-20 processes - and have each one update the admincode field in a field. Updating multiple tables at the same time will be a big help here.

If you're not blocking the updates into groups of 100 records / TX, that would also be a huge performance win.