Adding Custom Index/dbreorg question

Sort:
You are not authorized to post a reply.
Author
Messages
LauraP
Advanced Member
Posts: 28
Advanced Member
    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
    Posts: 1498
    Veteran Member
      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
      Posts: 28
      Advanced Member
        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?
        Adam Jacobson
        Veteran Member
        Posts: 69
        Veteran Member
          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
          Posts: 28
          Advanced Member
            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
            Posts: 28
            Advanced Member
              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 ...
              Adam Jacobson
              Veteran Member
              Posts: 69
              Veteran Member
                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
                Senior Member
                Posts: 3348
                Senior Member
                  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
                  You are not authorized to post a reply.