Multiple Issues

[Chris Kelleher]

Hi,
I was facing multiple issues and was wondering if anyone of you
could give me some relevant answers to the same. All of the issues are
in some way or the other linked to the fact that my database size has
exceeded 2GB.

1. We have gone in for a multi-volume conversion with 6 extents of 400
MB. Details : OS is NT with MFG/Pro 8.5F and Progress 8.2C.
Unfortunately all the extents are on one physical disk. Now the
performance is not so bad but one particular customized code is creating
havoc. It's multiple triggers on apvomt.p. The time taken for a Voucher
entry, single vod_det line is 20 minutes. This seems to be the only
affected code. I have some leads as to what the problem could be... just
thought some more inputs from you guys will be just what the doctor
ordered.

2. Can anyone throw some light on what is the difference in performance
with having multi-volumes and having a split schema.

3. Also, does it really serve the purpose to have all the extents in a
multi-volume structure on one physical disk or volume.

4. Has anyone used GL Consolidation. I wanted to know how the same
works. The transactions are exported from this database to another
database. What will be the effect of GL Consolidation on that.

Thanks in advance.

Regards,
Kawal Batra.

--
______________________________________________________________________

Kawal Batra
Application Specialist
Thirdware Consulting, India.

Tel: 91-22-8292225/2302.
Fax: 91-22-8292102.

e-mail: kawal@tspl.com
______________________________________________________________________

 

[Chris Kelleher]

> Hi,
> I was facing multiple issues and was wondering if anyone of you
> could give me some relevant answers to the same. All of the issues are
> in some way or the other linked to the fact that my database size has
> exceeded 2GB.
>
> 1. We have gone in for a multi-volume conversion with 6 extents of 400
> MB. Details : OS is NT with MFG/Pro 8.5F and Progress 8.2C.
> Unfortunately all the extents are on one physical disk. Now the
> performance is not so bad but one particular customized code is creating
> havoc. It's multiple triggers on apvomt.p. The time taken for a Voucher
> entry, single vod_det line is 20 minutes. This seems to be the only
> affected code. I have some leads as to what the problem could be... just
> thought some more inputs from you guys will be just what the doctor
> ordered.
>
Sound like index usage issue not a dtabase, onre of you triggers is reading
through an entire table, possibly multiple times.

> 2. Can anyone throw some light on what is the difference in performance
> with having multi-volumes and having a split schema.
>
With a split chema, you are splitting the data base into multiple databases.
Normally it is static data in one database, and dynalic in another. The
performance difference only comes in if you very large db or many, many
users

> 3. Also, does it really serve the purpose to have all the extents in a
> multi-volume structure on one physical disk or volume.
>
Ideaaly the should be spread among many physical volumes. something like
this:
Egxample 4 volume and 16 extents.
Volumes 1 2 3 4

Extents 1,5,9,13 2,6,10,14 3,7,11,15 4,8,12,16


> 4. Has anyone used GL Consolidation. I wanted to know how the same
> works. The transactions are exported from this database to another
> database. What will be the effect of GL Consolidation on that.
>
With G/L consolidation all the detail is kept at the source module, and
only totals sent to G/L
otterwise detail is both in G/L and the source module.

Gary Seibold
Logan Consulting
203 N LaSalle
Suite 2100
Chicago IL, 60601
Tel

312) 558-1799
Fax

312) 558-1797
Email: gseibold@logan-consulting.com
Web: www.logan-consulting.com

 

[Chris Kelleher]

From: Kawal Batra <kawal@tspl.com>

> 1. We have gone in for a multi-volume conversion with 6 extents of 400
> MB. Details : OS is NT with MFG/Pro 8.5F and Progress 8.2C.
> Unfortunately all the extents are on one physical disk. Now the
> performance is not so bad but one particular customized code is creating
> havoc. It's multiple triggers on apvomt.p. The time taken for a Voucher
> entry, single vod_det line is 20 minutes. This seems to be the only
> affected code. I have some leads as to what the problem could be... just
> thought some more inputs from you guys will be just what the doctor
> ordered.

How did you do the conversion? Procopy? D&L? Prostrct convert? If you did D&L,
could your original DB have had some non-standard index defined, that is not
defined in the new MV database? If they're not too long, perhaps you could share
your trigger code with us.

Also, are you looking at promon numbers at the time this happens? What other
performance tuning are you doing? Actually, before we talk too much about
performance and promon and MV, etc., I'd strongly suggest you cross-post this to
dba@peg.com .

> 2. Can anyone throw some light on what is the difference in performance
> with having multi-volumes and having a split schema.

Split schema is usually implemented to address some business issue such as how to
keep history data. It can be used to gain performance (eg putting "old" history
data on a separate DB, and connecting to it only when you need it), but it doesn't
inherently increase performance. The whole point of multi-volume is to increase
performance by spreading the data across multiple spindles (well, that and
increasing DB capacity beyond 2GB). Split-schema creates possible functional-level
headaches, esp. at upgrade time. MV is strictly a DBA issue about which the QAD
software functionally doesn't give a fig. Unless you have a functional or business
reason to go split-schema, I'd stick to MV.

> 3. Also, does it really serve the purpose to have all the extents in a
> multi-volume structure on one physical disk or volume.

It serves the purpose of allowing you to go beyond 2GB, but it will have little or
no impact on performance. As to whether/how it created the 20-minute AP voucher
entry you described above, again, you might get more action on dba@peg.com .
---------------
Paul T. O'Leary
Evco Plastics, a leader in plastics injection molding
DeForest, WI USA

 

[Chris Kelleher]

In regard to:

> 3. Also, does it really serve the purpose to have all the extents in a
> multi-volume structure on one physical disk or volume.

Note that with certain types of disk subsystems, even though you can address
individual spindles via unix, they are fairly meaningless because of the
interlacing technology that is used. Check with your unix admin before
going to the trouble of spreading things around.

David Takle
Software Consultant
Find First Consultant, Inc.

 

[Chris Kelleher]

Kawal,

To respond to your questions...

1. The conversion to a MV database should not affect trigger performance
like that with all of the extents on the same disk. Look at the code in
the triggers (as has been suggested) and check to see how fragmented the
data in your database is (via tabanalys). My first bet would be on the
code.

2. Multi-volume allows you to exceed the 2GB limit (except for the BI file
-- you can't exceed that, no matter how many extents, until Progress 9) and
to split the single physical database up into multiple chunks (extents),
which can be positioned on multiple disk drives in a form of manual
striping. Split schema allows you to split a single logical database into
multiple physical databases and manage each one separately, with each one
having its own -B, -L, etc. With split schema, particularly for MFG/PRO,
you *must* connect to all physical databases to have a single logical
database; otherwise, programs will fail. With a MV database, Progress
automatically ensures that you are connected to all the extents. In
general, you should go Multi-volume; you should only go split-schema if
there's a specific issue that you have which cannot be corrected or
addressed in any other way.

3. It's more efficient to have the database extents on multiple disks, but
it's not critical. You're just a little more open to an I/O problem, which
you probably were already seeing. If anything, put the BI and AI extents
on separate disks before you look to move data extents around. You'll get
more performance gains from those than from your initial movement of data
extents.

4. GL Consolidation, or Import/Export as I usually think of it, allows you
to take financial transactions from one database and move them into another
database. This allows you to take multiple plants and combine their
financials for regional, divisional, or corporate financial reporting. You
can also have multiple levels of consolidation -- plants to regions,
regions to countries and/or divisions, countries and divisions into
corporate. The data remains in the original database until you archive it.
The import process can address currency translation as well as
chart-of-accounts translation -- for example, the rest of the world moving
into the French chart of accounts, or vice versa. Review the on-line
documentation as well as the procedure help for more info, or post more
specific questions.

Good luck.

Scott
===============================================================
Scott M. Dulecki /* 1998061901 */ +1 616 975 6322
Product Manager scott_dulecki@qad.com
QAD, Inc. http://www.qad.com
1188 East Paris SE Grand Rapids, MI 49546 USA

Next Michigan Progress Users Group: 19 January 2000

All opinions are my own, and don't necessarily reflect those of
any other living being.
===============================================================

 

[Chris Kelleher]

Scott,
Thanks for you reply. Just one correction from my side.... I meant (GL)
Transaction Consolidation (25.13.11 in 8.5f) and not financial consolidation
across multiple databases. Actually there is no procedure help for the program
and I was wondering if this code will consolidate gltr_hist records for a day
(and other relevant indices) and hence reduce the number of gltr_hist records.
Since we are ourselves using the financial (GL) consolidation across databases,
I wanted to know what will be the effect if there are partially exported
records and some have not yet been exported.

Regards,
Kawal.

 

[Chris Kelleher]

Kawal,

I thought about the possibility of confusion when I responded to you, so
thanks for clearing it up.

Actually, there *is* procedure help for GL Transaction Consolidation -- I
just looked at it. I'd review your MFG/PRO installation to see if it has
been inadvertently removed, or if something else is going on. You can also
look at the on-line documentation on the QAD web site (you'll need your
password) to see the actual written documentation on this process.

In brief, Consolidation combines multiple GL transactions into one. You
specify how many (through date ranges and other selection criteria), and
the system takes it from there. Strongly consider archiving the data as
part of the process.

To see what specific tables are used in the process, look at the source
code cross-reference information for the consolidation routine.

As far as what happens with partially exported records, I'm not sure what
question you're asking. The export routine will export all records that
meet the selection criteria that you indicated and which have not been
exported yet. The consolidation routine will consolidate all records that
meet the selection criteria. The only confusion I can think of would be if
you exported records and then consolidated them; they *might* be picked up
again for the export routine. I've not tested this scenario, but I suspect
there might be an issue. I also suspect that few people would run into it,
since you should be doing exports and consolidations for different time
frames.

Good luck.

Scott
===============================================================
Scott M. Dulecki /* 1998061901 */ +1 616 975 6322
Product Manager scott_dulecki@qad.com
QAD, Inc. http://www.qad.com
1188 East Paris SE Grand Rapids, MI 49546 USA

Next Michigan Progress Users Group: 19 January 2000

All opinions are my own, and don't necessarily reflect those of
any other living being.
===============================================================