New index in qaddb

[Chris Kelleher]

Hi Peggers!

Can you tell me what consequences does the creation of a new index on an
existing table have? Should I be aware of something or just go ahead. Do I
have to make something extra if I want to upgrade my MFG/PRO afterwhile? I
mean will the standard upgrade utility work?

Any comments appreciated.

TIA
Tamas

 

[Chris Kelleher]

Hi Tamas,

You will have to recompile all the programs which use this table ...

Regards,

Ilya Romanov,
Programmer,
BMS, Inc.
Moscow Russia

 

[Chris Kelleher]

Tamas --

Adding an index should not effect the standard upgrade utility - - however,
proceed with caution when modifying the qaddb schema; usually what happens is,
once it is modified , it opens the door for additional modifications, which can
make an
upgrade MUCH more difficult to manage.

Have you examined the possibility of either using temp tables (and adding the
proper indexing), or adding a custom table to handle your indexing needs?

Regards,

DC

 

[Chris Kelleher]

Several consequences, some of which have already been pointed out:

1. All programs currently using the table will have to be recompiled.
Depending on the table affected, this may be a lot of programs or only a
few.
2. Upgrades WILL be affected for this reason: QAD supplies code
pre-compiled against the standard schema. If you need to carry the index
change forward, you will have to recompile the programs affected by the
schema change, which will not necessarily be only the ones that you
recompiled in your current release.

You haven't said why you think you need an additional index. You want to
look very carefully at the need you are trying to fulfill with your
additional index. Presumably it is for reporting purposes. You can
probably achieve your goal by incorporating an indexed temp-table into a
report program.

If you post more info, you will get more definite answers.

John Nisbet
Kilcreggan Systems and Software, Inc.
QAD Service Alliance Partner
1191 Chestnut Street Suite 2-6
Newton, MA 02464-1351
jnisbet@kilcreggan.com
(617) 969-5432 x201

 

[Chris Kelleher]

Standard programs may also be affected. Depending on the fields of your
proposed index, and on the makeup of the other indexes in that table,
and on the terms of, say, a FOR EACH in a standard program that
accesses that table, a standard program, when (as has been suggested)
you recompile it, may choose to use your index rather than an existing
one. This may affect either the speed or the actual logic of that
program.

I agreee with John that you may be able to work around this with a temp-
table within the custom code. As John said, tell us more about your
circumstance that calls for this index.

 

[Chris Kelleher]

Thanx for all the useful comments. To tell the truth I was just gathering
some info on the topic, because my colleague mentioned me by phone that he
has some ideas for a customer of ours. Today I talked with him and I think I
can give him another sufficient method for his problem. (Besides the
modification should have been done on the vo_mstr table. It would have been
affected the vo_type field.)

However you guys gave me some good ideas.
Thank you again
Tamas

 

[Chris Kelleher]

Tamas,

Aside from the issues that others have raised about the need to recompile
programs and the challenges for upgrades, you may also need to consider
that your added index may be chosen by Progress when running programs other
than the one you're creating it for, which *could* mean that those other
programs will have different results than before, which *could* be
incorrect....

Certainly there are a lot of assumptions and things that have to go
perfectly wrong for that to be a problem, but it's one that should be
considered.

Before giving any absolute advice to you, I'd ask the question that others
have: where are you wanting to add an index, and why?

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: 17 November 1999

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

 

[Chris Kelleher]

Tamas,

If you already have a custom database (and who doesn't?) then I would
suggest that you consider adding a 'shadow' table to it. A shadow table
would consist of a subset of the fields (and optionally some additional
fields) in the MFGPRO table. I've used this extensively to build extensions
to MFGPRO tables (cm_mstr, ad_mstr, so_mstr, etc.) for custom EDI purposes.
The benefits/drawbacks depend on what it is you are trying to do and which
MFGPRO table is involved (# and size of records). NOTE: Only customized
programs will be able to take advantage of the shadow table and its
facilities.

In order to do this, add a table to your custom database which contains a
unique key to the MFGPRO table and all of the fields you need for your new
index. You should create and install database triggers for the creation,
deletion and modification (write) of records in the MFGPRO table to
automatically maintain the records in the shadow table. Then you can add
any indexes, fields, relationships, etc. to the shadow table as you see fit.

Hope this helps.

.. Al Hubble
*********************************************************
Allen Hubble <AHubble@Hub-Tech.com>
Hubble Technology Consulting, Inc.
Progress/UNIX, Mfg/Pro, EDI/EC
Brampton, Ontario, Canada
416-802-5713
*************** Fortune favours the BOLD! ***************