The use and mis-use of usrw_wkfl

[Chris Kelleher]

Progress 8.2C CHUI
Mfg/Pro 8.5F

Dear Peggers and QADers:

While our plain-vanilla Mfg/Pro system was being implemented, we had a
couple of contract programmers here to help us with some reports and
side systems. These programmers made heavy use of the usrw_wkfl,
using it where (I think) they should have been using temp tables; and
also storing persistent transaction data out there. When I saw what
was going on, I raised a red flag to the contractors, and then to my
boss; but the contractors insisted that there was no reason to be
concerned. And so the development continued, with the Workfile being
used in dozens of programs.

After the contractors left, we began to have problems. First we
discovered that obsolete records were not being deleted and at the
height of this Workfile use, we had several hundred thousand records
out there; and since some of the data was persistent, we couldn't just
blow it all away some night.

We also learned that record keys were not properly used and the entire
Workfile was being read on queries that should have been a bracketed.
Of course, with the large amount of data on usrw_wkfl, reports were
taking a long time.

We also began to experience chronic contention between applications,
with programs reporting that the usrw_wkfl table was locked by another
user. It was a nightmare.

And so we decided to replace the Workfile in all our programs with
temporary tables or with permanently-defined tables, as the case
required. Our problems went away.

Now we are considering using the usrw_wkfl, once again, but this time
as a general table file--a place to store a small number of records
for a given application. Control records, for instance; bits of data
that must be saved for the next run of the application; the kind of
stuff you might find in a .ini file. Of course, we will use the table
keys properly.

The reason we want to do this is so that we don't have to create a
whole bunch of little one-or-two-record tables; also, we have a
dickens of a time getting these tables installed into our 24x7
production system, since a table cannot be added or modified if anyone
is using the system (we have a programmer getting up in the middle of
the night, trying to install a new table or modify an old one; even
then, he can't always do it. This is a nightmare, too.)

Question: Is it a good idea to use usrw_wkfl as a general table file?
What are the pros and cons?

TIA

Jack Kaufman
Sr. Programmer/Analyst
Systems Department
Ingersoll-Rand Corporation
101 North Main Street
Athens, PA 18810

Phone: 570-882-0366
Fax: 570-882-0452
Email: Jack_Kaufman@ingerrand.com

 

[Chris Kelleher]

Jack,

The usrw_wkfl is designed as an additional "multi-table" table in the
database. With its six-part key, you have an almost umlimited number of
ways to search through and find data (at least 720, and that's before the
keys matter).

For one- or two record tables, it's close to perfect, particularly if
they're static. The problem arises when you have multiple large datasets
into it -- an incorrect query can quickly turn into multiple full table
scans, sending performance out for a while.

You can also create necessary tables in a side database (thereby keeping
the MFG/PRO schema vanilla and eliminating recompile problems). For larger
data sets, that's probably preferable.

Pro: flexible, already defined, and reserved for customer use.
Con: flexible (many data sets can be in there), already defined (what if
you need more fields? changes affect all datasets), and prone to misuse
and interpretation.

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]

All,

While it is preferable to create a side-database, it is sometimes not
practicle, for any of a thousand different reasons.

Here is a scheme I devised to eliminate may of the issues surounding use of
usrw_wkfl for large datasets.

1. usrw_key1 always identifies the dataset. ie usrw_key1 = "Dataset_Name"
2. usrw_key2 always define a unique key with the most common(hopefully)
index used to acces the file.
unfortunatly this sometimes requires string together multiple
fields.
I always include the datset identifier when accessing the table, this
ensures I am only selecting the reocrds for the dataset I am going after.

If it is not possible to create a unique key then the data set name could be
assign to usrw_key3 or usrw_key5.

The other 2 stes of index fields are for likely, but less often used
indexes.

To make readabity easier, and make sure I am going after the correct fields
I create an include file that maps the usrw_wkfl fields to a logical(?}
table layout as precompile directives. I then use the precompiler names in
any program i write. This makes reading and figuring out what the programs
are doing much easier

eg.

<BLOCKQUOTE><font size="1" face="Arial, Verdana">code:</font><HR><pre>
/* include ftr_det table*/

&global-defile ftr_det usrw_wkfl

&global-define frt_ident usrw_key1 = "frt_det"
&global-define frt_shipper usrw_key2
&global-define frt_nbr usrw_key3
&global-define frt_line usrw_key4
&global-define frt_cust usrw_key5
&global-define ftr_cost usrw_decfld[1]
&global-define frt_weight usrw_decfld[2]
&global-define frt_part usrw_charfld[1]
&global-define frt_cust_idx user_index3.
&global-define frt_order_idx usrw_index2


/* simple query */

/* define precopiler directives */
{frt_det.i}

for each (&frt_det} where {&frt_ident}
and {&frt_cust} = "123456"
use-index {&frt_cust_idx}.

accumulate {&ftr_cost} (total).
accumulate {&frt_weight} (total).
end.

display "Customer 123456"
accum total {&frt_weight} label "Freight Weight" format
">>>,>>9.99"
accum total {&frt_cost} label "Freight Cost" format
">>>,>>9.99".
[/code]


Gary Seibold
Logan Consulting
203 N LaSalle
Suite 2100
Chicago IL, 60601
Tel312) 558-1799
Fax312) 558-1797
Email: gseibold@logan-consulting.com
Web: www.logan-consulting.com

P.S. Don't know it this compiles, made it up on the fly

 

[Chris Kelleher]

Follow-up question:

Is it okay to use the Generalized Code Master (code_mstr), as Bharat
suggests below? That sounds like the perfect solution for what I want
to do: store one or two (.ini) records for use by an application I am
writing. The Generalized Code Master seems like a better place (to
store onesies and twosies) than usrw_wkfl (which seems more
appropriate for temporary data).

One concern I have is: when we upgrade to a new release of Mfg/Pro,
will custom records that we have in the Generalized Code Master get
blown away? Or will QAD overlay their new codes and leave ours alone?

Are there any other concerns?

Thanks for your help.

Jack Kaufman

 

[Chris Kelleher]

Jack,

I'm making the assumption that you're talking about storing data in the
generalized codes table, and then querying that table from other programs
to find a control record or the equivalent.

Remembering that each generalized code is one field, one value, and one
comment, you may end up with many records out there. If there's only one
or two, it probably won't be much of a problem; much beyond that, and
you're looking at lots of search time.

Remember also that whatever you put in the generalized codes table gets
used and reviewed by all of MFG/PRO. This means that you'll need to ensure
that your initial field is not one that's used in MFG/PRO, and not one
that's likely to get used in the future. While we don't replace the
contents of the generalized codes table with an upgrade, we may update
those contents, which could lead to overwriting one of your records. It's
a stretch, granted, but it could easily happen. We do not update or
replace the contents of the usrw_wkfl.

I would still suggest using the usrw_wkfl table, being selective about how
many of the six keys you use in identifying your control records.

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.
===============================================================