Who's On?

	Membership:
	Latest: Saef
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5226

	People Online:
	Visitors: 188
	Members: 0
	Total: 188

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1372
Roger French	1315
mark.cook	1244

Adding Custom Index/dbreorg question

7 Replies

0 Subscribed to this topic

17 Subscribed to this forum

Sort:

Author

Messages

LauraP

Advanced Member

Posts: 28

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

Posts: 1498

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

Posts: 28

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?

Adam Jacobson

Veteran Member

Posts: 69

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

Posts: 28

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

Posts: 28

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

Adam Jacobson

Veteran Member

Posts: 69

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

Posts: 3353

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.