Creating Indexes in Progress

[emtgdkr]

We are using progress 9.0b patch 44. We are replicating our production database over and crerating a reporting version. I would like to add indexes to certain tables in the reporting database to improve report speed. What is the best way to create these index tags each night?

thanks

 

[mra]

Hi!

How do you replicate the database, by copying the db-files? In that case, you have to create new indexes every night. If you dump/load or something similar, creating the indexes is a one time job.


Regards
Mike

 

[emtgdkr]

We are trying both ways.
If we copy the datbase I was looking for a way to re-create indexes. the crerate index does not because the database was not created that way by the vendor.

in the dump and load. We were trying to figure out how to clear out the datbase then load it. We were thinking of having an empty structure then loading the data.

We are still trying to figure out which way will run faster.

thanks
DKR

 

[Norman Biggar]

How about having all the indexes on your production database, but set those only used for reporting to inactive.

Create your reporting databse by copying the production database each night.

Perform an idxbuild on the reporting database to activate the reporting indexes.

 

[emtgdkr]

The problem I am faced with is that ;
The vendor does not want us to create new indexes on the production datbase. This is bad for new releases and such. So we want to have our reports db separate with indexes that will not be in production.

thanks

 

[Norman Biggar]

OK,

I don't know if this can be done, but I'm sure if it can someone will tell us how.

Potentially, you could create the reporting database from the production database and then apply a prepared incremental .df to add the reporting indexes.

As I've said, I don't know how you would automate this process, but all Progress data dictionary processes are written in Progress. I vaguely remember hacking their code to create a .df file in V7. Have a hunt in your $DLC directory.

 

[Bill Burke]

Originally posted by Norman Biggar
OK,

Potentially, you could create the reporting database from the production database and then apply a prepared incremental .df to add the reporting indexes.

Norm,

I'm the DBA that works with DKR.

Did you mean an incremental .D file? I ask because I don't see how the definition would change, or is the index part of that? That would be interesting if we could selectively produce incremental .D files of the 30 or so tables we use.

I've been kicking it around for a few days. It seems like the best way for us would be to:

- create the reporting db structure (Rpt50Prd)
- dump & load data definitions of the required tables
- DKR adds his indexs
- we call that the empty structure and get a Probkup of it

Then going forward on a nightly basis we:

- prorest EmptyStructureBackup
- dump and load data from production to rpt50prd
- proutil unifipc -C idxbuild all
- start it up and print reports

Down side is each night this would take longer as the DB grows. That's where figuring out how to create incremental .D files would be helpful.

 

[mra]

I have no idea, if this will work!! :blue:

How about using After Image files to move transactions from Production to Report?
The caveat here is, that CRC for the schema will change when adding a new index, I don't know if Roll-Forward requires the tables to have the same CRC.

A quick look in the manuals and at the Progress K-Base didn't give me any hints.


Regards
Mike

 

[Norman Biggar]

What I had in mind was:

Do this only once:
Create an empty database with the production schema.
Add all the indexes you require for reporting.
Create an incremental .df file between this database and the production database called, for instance, addindex.df. This is what I meant by the "prepared incremental .df".

Each night:
Make your reporting copy of the database.
Apply addindex.df to the reporting database.

As you say it would take longer each night as the database grows. You might also have to experiment whether it is faster to:
Add the indexes as active
OR
Add the indexes as inactive and perform an index rebuild.

 

[emtgdkr]

Thanks , This is something simliar that I suggest to BB. I will discuss it with him.

thanks