Keeping tabs on a big df

[Cringer]

I'm applying a df to a database that has rather a lot of indexes being added to some big tables. I'd quite like to keep tabs on where it's up to so I can have a reasonable idea of when it will finish. Is there a way of doing this? I know I can look at the .d files and work out which storage area is currently being hit, but there are a number of indexes in the same area so this isn't particularly accurate.

 

[TomBascom]

Run ProTop and watch the table and index statistics. The ones that are going bonkers are the ones being worked on

Or add the indexes inactive and activate them manually at your leisure.

 

[Cringer]

Thanks Tom. I'll have a look at protop. Do you know how Progress applies a df? Does it apply field/table changes and then sequentially add the indexes in the order they are in the df file?

 

[TomBascom]

I've never really looked in to it but I would assume that it does things in the order that they appear in the .df file.

 

[Cringer]

Looks like that's the way. Not too long to go now hopefully. 4 hours in. It took 6 hours to apply on UAT last week. I've got less data in this db, but it's running locally. So am hoping it'll be a similar time frame.

 

[Rob Fitzpatrick]

Wow, that seems like a long time for a schema change. However it will be affected somewhat by the structure, i.e. how much space has to be allocated for each storage object. Also, since this is transaction activity your BI sub-system performance will affect the run time. You may want to check your settings there (BI cluster size, -bibufs, running a BIW, BI block size).

Look in ProTop or promon to see how often you're checkpointing, whether you have buffers flushed, etc.

 

[Cringer]

The df contains some index changes to some of the biggest database tables so I'm not surprised it's taking so long.

Can't get ProTop working at the moment - it just seems to leave me with a blue screen. Probably because the db is being hammered by the schema change!

How would I go about seeing those sorts of things in promon?

 

[Cringer]

Ok so it's finished now. Yay! 5 hours - not as bad as I thought!!

 

[TomBascom]

The blue screen may just be because the default PROENV on Windows thinks that it is 300 lines long. So any prompt that progress wants to show you are somewhere near your ankles.

 

[TomBascom]

PROMON does not show _tablestat or _indexstat.

 

[cj_brandt]

Cringer - have you considered adding the index as inactive and then running idxactivate to activate the index ? It is a lot faster if you are trying to add indexes to large tables and the growth of the BI file is much less.
We added a single column unique index to a table with about 500 million records and it took about 14 hours to activate via the idxactivate command.

 

[Rob Fitzpatrick]

If you're working with a DB on your Windows box and running 10.2A or 10.2B, you may want to try ProTop .NET instead of running the CHUI version in proenv.

 

[Cringer]

I'll look into that, Rob, thanks.
Thanks for the idea, cj, I'll do some investigating.

 

[Cringer]

If you're working with a DB on your Windows box and running 10.2A or 10.2B, you may want to try ProTop .NET instead of running the CHUI version in proenv.

I think I'm being rather dense, but what do I need to do to get ProTop .NET running?! :/

 

[tamhas]

10.2A or B and the corresponding ProTop download. If you are having a problem ... and those have been fairly common because of the trickiness of compatibility with the .NET bits, I would ask Tom or start a new thread with your issues.

 

[Cringer]

Thanks tamhas - it's more that I don't know what I need to do to even get it started. I assume I run protop.r, but I just get messages about stuff being missing and so on and therefore was wondering if there's a specific directory it all needs to be in?

 

[Rob Fitzpatrick]

Well first, there are two GUI versions available for download, one for 10.2A and one for 10.2B, as they have different versions of the Infragistics controls, so be sure you have the one you need.

Unzip it into your directory of choice, preserving the directory structure. In <install path>\ProTop\etc you will find a shortcut; I think it's named ProTop Dot NET. Make a copy and change the properties to suit your need.

The target line looks something like: C:\Progress\OpenEdge\bin\prowin32.exe -pf etc\<target db>.pf. I have a pair of files in the etc directory, a shortcut and a .pf, for each database I want to monitor.

The parameter file will look something like this:

-db [I]dbname
-[/I]S[I] db broker port number or service name[/I]
-H [I]db server IP addr or host name[/I]
-p protop.p
-rereadnolock
-q
-s 200
-TB 31
-TM 32
-Bt 1024
-mmax 16384

Give it a try, and post your errors if you have any issues. Cheers.

 

[Cringer]

Ahhh that's the missing link!! Looks to be running now...