Re: Finding a suitable key

[DAsmussen@xxxxxxx]

Juris,

In a message dated 97-09-02 08:23:43 EDT, you write:

> > Darn, Juris!  I just realized that the quintessential MIDRANGE-L question
was
>  > never asked on this -- "Just WHY do you want to do it?".  The
application
>  > seems extreme, given the abilities of DB2/400...
>  
>  Well, I'm not sure if my english is good enaugh to understand the
>  question _OR_ the point :-), but if the question is "why do I want to
>  search for holes in my keys?" then perhaps my answer is:

Exactly the question.

>  For example there are many customers placing many orders and the orders
>  expire and fulfill all the time so there are really _LOTS_ of orders in
>  the whole, but not really lots of at the moment. I am keeping a database
>  of these orders. If I just assign a key to an order generously (just
>  Max(keys)+1) I _MIGHT_ someday run out of keys (I don't want to use
>  packed 31-digit numbers so 10-digit binary integers might not do it). So
>  I would like to use the used keys again once they are deleted (some
>  orders might last forever). Maybe I'm getting my problem quite wrong,
>  but that's just how I see it... Please, tell me if and where I'm
>  wrong...

Ahh, a valid concern.  I saw this at one other customer, and here's what we
did:

1)  Calculated how large a key we would need for the order file on a year's
worth of orders, adding x% of growth for 20 years and adding the percentage
of records for orders expected to be open at any time.
2)  Created duplicates of the order files that allowed multiple members, and
added the order date to the keys.
3)  Modified the year-end programs to move completed orders from the open
order files to the duplicates, with a member name of the year in question.
4)  Created a duplicate of the order inquiry program that used the date keys
to tie line items to the appropriate order, called by a CL that performed an
OVRDBF MBR(*ALL) on the duplicate order files.
5)  Prayed that growth didn't outstrip our estimates :-).

This worked well.  When our order number control data area wrapped, there was
almost no chance that we'd receive a duplicate order in the same year.
 Despite some unexpected growth in sales, the order files have yet to come
CLOSE to wrapping within the same year...

One Solution,

Dean Asmussen
Enterprise Systems Consulting, Inc.
Fuquay-Varina, NC  USA
(Between Raleigh and Research Triangle Park)
E-Mail:  DAsmussen@AOL.COM

"The saints are the sinners that just keep on trying." -- Robert Louis
Stephenson
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the RPG/400 Discussion Mailing List!  To submit a new         *
* message, send your mail to "RPG400-L@midrange.com".  To unsubscribe   *
* from this list send email to MAJORDOMO@midrange.com and specify       *
* 'unsubscribe RPG400-L' in the body of your message.  Questions should *
* be directed to the list owner / operator: david@midrange.com          *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *