Reindexing

We are planning to rebuild the indexes on our JDE databases through sql server's DBCC DBREINDEX tool (which essentially drops & recreates the index). Has anyone out there had any experiences good or bad with reindexing JDE tables using sql server tools? Any advice is greatly appreciated.

Hi jehub,
Don't have any experience to offer but I do have a question. What prompted you to decide to re-index the SQL tables ? Do you have a SQL DBA that thinks this is a good idea ? If so, why ?
Regards,
Dave

I did this once and suffered major performance problems because the indexes created were completely different from the indexes defined in OneWorld. Document oti-00-0123 details how to re-create large amounts of OneWorld indexes.

Bill

Did you use DBCC DBREINDEX?

Some people think that it might be faster to use sql server tools to reindex the tables. Opinion?

It my opinion that should not matter.

Definately reindexing through database will be faster. JDE is also using the SQL commands to database to reindex the tables and now you are directly issing the commands to the database( Avoiding Application layer).
But I agree with Dav that why do you want to do that?
One of the reason can be that indexes are fragmented and you want to regenerate it. Is that the case with you?

Yes - just need to defragment.

Sounds like you are on a SQL Server but I think the general treatment of JDE files and indexes are the same across platforms. I did an OS/400 reorganize across all our JDE environments to recover disk space from deleted records. This also rebuilds the indexes for the DB2 databases which JDE is using. The unfortunate truth is JDE does keep track of Database internals or something itself, I'm assuming this is captured when you do a JDE reindex. I haven't found any documents stating this as fact, just experiencing the symptoms and doing some self diagnosis. If you do any indexing via the native DB utilities you will have to do the same thing again via JDE reindexing or pay a performance hit. Bottom line...JDE needs to be the last thing that touches the indexes. If anybody knows what JDE is doing under the covers with the indexes that native DB reorgs undo, I'd love to hear from you.

Darrell Allison
Systems Programmer
AS/400 V4R5 9406-730 8-Way / OneWorld B7332 SP11.3

HI,

I agree will Bill. I was at a client that used the SQL to reindex tables and there was a performance problem (it dropped some of the primary keys???) . Reindexed the tables using the document and that fixed the performance problem.

Don't know why but IT works. Any If does not take that long to run (a few hours).

Been here, done it. The amazing thing is that you need to be so careful
with indexes. Remember, within OW the indexes are generated from the
table definitions, hence if you rebuild indexes from one machine, you may
not get the same indexes as you do from another, simply because the table
specs are different. That's always a fun one to try to explain.

I strongly urge you to do all needed re-indexing through OW. Utilize the
KG document on how to re-index large numbers of tables. OW was not
written for one specific DB platform, which has obvious benefits. But
one big negative is that these tables are not definied specific to the
native DB, they are defined inside of OW. I've re-indexed Oracle, SQL,
and DB2 DB's all with the same results...missing indexes, and very, in
some case VERY poor performance, until the tables in question were
indexed through OW.

However, in the past, I just knew to do it through OW and was content
that it was because "something" was defined in OW and not on the DB side.
I'll try to find out the specifics now, and will post the technical
reason if I should locate it.

But again I'd state, if it were me...I'd reindex through OW.

Jim
On Wed, 30 Apr 2003 08:55:05 -0700 (PDT) jehub <[email protected]>
writes:

________________________________________________________________
The best thing to hit the internet in years - Juno SpeedBand!
Surf the web up to FIVE TIMES FASTER!
Only $14.95/ month - visit www.juno.com to sign up today!

Hi,

Recreating Indices are not bad if you know what you
your database very well. We just did this and our
performance has increased at least by 50%. But the
advantage that we had was we didn't have that many
custom indices and for the matter of fact you can
easily recreate them if you know them. Mostly if the
indices are corrupted the data that gets stored in
table is fragmented and it uses up lot more space than
what it should be using and also performance hits the
bottom (our database size dropped from 40gb to 15gb).
You can actually use R98404 provided by JDE to do
this. But you will need to have SQL scripting
knowledge to create a query to get a list of tables in
an environment and feed it to R98404.

Hope this helps.

Thanks,
Satish.

--- wloban <[email protected]> wrote:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OW&Number=53985


__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

My 2 cents.

We have been live for 3 years on SQL platform (B73.2.2 thru Xe Update6). We have moved from SQL6.5 to 7.0 to 2000 in that time as well.

We have always used the database tools to perform database maintenance, and as such cannot comment on the usability or performance of the OW options.

We have never experienced any performance problems tied to table design or index generation (apart from when the JDE upgrade process failed to regen some for us).
However, I have never trusted the DBCC REINDEX options either. I prefer to have JDE generate the tables and indexes it wants (after each upgrade / update) and then I generate a script for the recreation of indexes using the "GENERATE SQL Script" option - one script for PK's and one for other non-clustered indexes.
Then when I have a need to :
- defrag index files
- shrink or restructure an index file(s)
- split indexes onto a separate physical volume
- etc.
I can just mod theses scripts (if necessary) and rerun

Hi All,

I've been doing quite a bit of SQL DBA over some 15 years and tuned lots of OW installations on SQL.

From experience:

"dbcc dbreindex" is good for a few reasons and works like a dream - it used to be a bit flaky in SQL 6.5, but since v. 7 it's an absolute must:
- it flattens the index;
- it also defragments the index;
- it updates statistics

all of which are making indexes substantially faster.

It also preserves index filegroup - it won't move indexes from where you want them to be back to the PRIMARY (the way OW would).

If you ever had any performance issues with what it does - the issue must lay elsewhere. There are not many parameters one can change in SQL, but some can affect the way dbcc behaves.

Specifically, the DB "Auto Generate Statistics" setting, which is generally believed to slow SQL down, must be turned ON for OW: this alone would quadruple the performance if you had it turned OFF before - you do not have to believe this, you can try it!

If your fears are old (as old as SQL 6.5) - try it again one day - you'll be surprised.

Normal SQL Maintenance "Optimisations" execute it anyway, so if you are running any maintenance jobs you may already be using it even if you didn't ask for it.

On the other hand, I know that OW doesn't do anything about the performance. All it does is the trivial "CREATE INDEX ...".

Regards,
Alex.

Re: RE: Reindexing

Darrell,

We recently upgraded from V4R5 to V5R2 and one of the necessary steps was an object conversion process. I was under the impression that part of this process is a rebuild of all indexes. Do you know if that is correct? If so, should I plan to rebuild all indexes again through OneWorld?

Thanks

Ryan

RE: RE: Reindexing

Ryan,

I think it would be prudent. As I had said earlier I have no documents supporting or refuting my claim, just symptoms that appear if we only do the reorganizations from the AS/400 DB2 side of the world. Once we do the JDE reindexing, searches and updates improve. One thing I haven't tested yet is to see if the JDE reindexing actually removes deleted records or not. Something I hope to confirm real soon. If it does then we'll probably do everything from within JDE. No matter how much time JDE reindexing might take, it would be faster than doing everything twice.

Darrell Allison
Systems Programmer
AS/400 V4R5 9406-730 8-Way / OneWorld B7332 SP11.3

Re: RE: Reindexing

Ryan, the conversion process that happens on an upgrade from V4R5 to a higher release, is just converting database files to a new IBM format. It's not just indices that get rebuilt. Every database file will get converted as it is used. You can speed up this conversion in a couple of ways, but it will happen without any intervention. You do not have to rebuild anything.

Does anyone have any idea approximately how long the R98404 UBE referred to in this doc takes to run?

Thanks,
Si

Simon,

As this UBE is simply issuing the appropriate native SQL commands (via JDEBase middleware) the amount of time it takes to execute will depend on the amount of data in your database. I have run this at a variety of clients.

As an example, a 500 user financials only site with about 3 years of historical data and 2 million F0911 records took 5 hours. This was on an older spec AS/400 with 2 cpu's. Your results may vary.

Regards,

We often re-index our Oracle DB using the database tools and not JDE. Now I am concerend that maybe we have been doing something wrong. Can someone comment on this topic that uses Oracle?

Thanks