Prev
Next
Forums Integration / Customization S3 Customization/Development

Adding Custom Index/dbreorg question

 7 Replies
 0 Subscribed to this topic
 17 Subscribed to this forum
Sort:
Author
Messages
LauraP
Advanced Member Send Private Message
Posts: 28
Advanced Member
8/3/2012 1:10 PM
I think I know the answer - but I want to get the input of those here who are more knowledgeable! A consultant wants to add a custom index  to our Lawson ITEMLOC table (which has over 63,000,000 rows - yes that is MILLION).  I know once the index is added - a blddbdict and dbreorg are needed. The question is - will the dbreorg UNLOAD and RELOAD the table as part of adding the index?  I wasn't sure because it is NOT a conditional index. Based on whether this happens or not - adding an index may not be something we can do lightly since unloading 63,000,000 rows is no small undertaking. Thanks in advance, Laura Patenaude Yankee Candle Company
Greg Moeller
Veteran Member Send Private Message
Posts: 1498
Veteran Member
8/3/2012 1:42 PM
Laura: In past experience, it will try to add the index while the table is still in place. If it has any trouble doing so, it is at that point where the unload/add index/reload comes into play. It's really difficult to say for sure if the table will be unloaded/reloaded. You will at least need your WAS App Server shut off at this point. Anyone else have other thoughts?
LauraP
Advanced Member Send Private Message
Posts: 28
Advanced Member
8/3/2012 1:56 PM
Actually - I just found it that it WILL be a conditional index - so I'm thinking that definitely means an unload/reload so that the conditional index flags are set, correct?
Deleted User
New Member Send Private Message
Posts: 0
New Member
8/3/2012 2:32 PM
Yes. It will be creating a new column on the database. Question. I remember your very large ITEMLOC table from when you first went live - 2000. I recall that you guys liked having EVERY item at EVERY location. I also remember that when this created problems, we found some workarounds. What are you trying to accomplish? there may be another way to avoid modifying the table (there may not be, but there may be)
LauraP
Advanced Member Send Private Message
Posts: 28
Advanced Member
8/3/2012 2:35 PM
Adam - You're right.  I started here in July of 2001 so this was already being done.  And it is still being done.  Now things have become so large (so many rows in ITEMLOC) - we need to start figuring out how to purge.  The consultant working on it is starting by looking at only inactive items and then making sure there are no transactions against those items.  As long as they are inactive, with no transactions - they will be removed. There is a larger process that will obviously need to be done - but this is where we are starting. Thanks, Laura
LauraP
Advanced Member Send Private Message
Posts: 28
Advanced Member
8/3/2012 2:40 PM
Oh yeah - and I just double-checked the number of rows and I was a little low in my estimate - we have 186,496,822 rows in that table ...
Deleted User
New Member Send Private Message
Posts: 0
New Member
8/3/2012 5:05 PM
I'm not sure you need to modify the database to get this done. There are various ways to identify what itemloc records haven't been used - checking that there's nothing in the history table or the transaction table. You might do that location by location so that the query doesn't take forever to run. I remember that when we did your first inventory freeze, it took forever. We created a modification to the freeze program (which may have disappeared by now) to only freeze records that had information in the history table. Good luck! Adam
John Henley
Send Private Message
Posts: 3351
8/3/2012 6:38 PM
Depends on which environment version and which database you're on. Older versions did an unload/reload but newer ones use ALTER TABLE if it's available. You can always execute it manually combined with dbreorg -d to implement in the dictionary.
Thanks for using the LawsonGuru.com forums!
John