Adding Index to the F0911

Has anyone out there added any indexes to the F0911? We have about 11 million records in our GL and report performance is starting to show it. I am thinking about adding an index to GLOBJ as this is one of our primary params for General Ledger reporting.
I am curious about OneWorld's reaction to an additional index.
Also, should I do it through TDA or native DB2 tools?

Thanks In Advance!

Well, it's already a heavily indexed table. However every new index you add not only gobbles disk space, but also slows down inserts. You should do a careful analysis of the SQL your reports are generating before going ahead and adding another index. Is your DBMS tuned with up to date statistics for it's query optimiser?

JDE itself won't care about the index, other than allowing a new permutation to selection in Table I/O's. It is your DBMS that should sit up and take notice.

As far as whether to add the index from JDE or the DBMS: Who rules the roost there, DBA's or JDE Techs? Personally I'd say use JDE to add the index every time. Better visibility and control, plus the developers will be able to take advantage of the new index. It makes absolutely no difference to the execution of queries whichever way you go; the index will be created the same.

Cheers,
JohnO

Timing is everything, we have 10,000,000+ records in F0911 and just refreshed DEV from PROD so we are in the midst of purging this back. Using R09911we have moved 3.75 million records from F0911 to F0911P for Fiscal Year 2001. We're still tracking down JDE's intent for this process, whether this is an online history archive or just mechanism to flush data hasn't been determined yet. But just getting these records off of the F0911 would improve performance. If anyone knows the purpose of this application and JDE's intended use, please fill in the blanks. For my immediate need in DEV the F0911P is now flushable.

As far as the adding of indexes, we had mixed results using just DB400 so now we do all the index adds in JDE and anytime we use Reorganize Physical File Mbr (RGZPFM) we then use JDE Generate Index function afterwards.

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

Interesting...I originally looked at the indexes through RDBMS and saw only 3. I just took a look through TDA and there 34 indexes on the F0911. The only ones in my system are:
F0911_32, 33, and 34.

F0911_1 through 31 are missing!!!??? The only thing I can think of is that our original implementation consultant (before my time) removed the other indexes for some reason. I am afraid that my GL post performance is going to slow to a crawl if I rebuild them. But, this may explain why my GL reports are SO SLOW!

What to do.... If I speed up the report, I will probably kill UBE posting times. I'm pretty sure users will be unhappy either way...

Ryan,

We are believers in alternate indexes. We do all our index modifications in JDE. For the F0911 we have added 10 indexes.

Our take on this is that from a user acceptance point of view, it is much better to take a sub-millisecond performance hit on an insert, to prevent a second (or more) hit for an interactive user.

The only files we don't seriously consider canidates for new indexes are teh Z & work files.

We re-evaluate our indexing about 2 months after every major 'Go-Live'. We recently brought up a large portion of South America and doubled our concurrent user base from 300 to 600. We also increased the number of UBE's flowing through JDE from about 69K a week to 73K a week.

I added about 120 indexes for 20 different files, doing so decreased our 99th percentile CPU usage (the % of cpu used 99% of the time) for the month from about 87% to 65%. I can't prove it yet, but I'm pretty sure that a goodly portion of this is due to not having to build temporary indexes on the fly, with the remainder coming from decreased system overhead for paging. I can tell that system paging has decreased several percent.

You need to have a good handle on how your system is used, before being really agressive though. You should seriously consider working WITH your DBA to determine which indexes it would be advantageous to add.

WARNING, as with anything indexing can be taken too far and you can start seeing a decrease in throughput, hence my suggestion that you work with a DBA or someone that has an intimate knowledge of how your database engine works.

Tom Davidson

Most of the indices are just duplicates or I should say they resolve to the
duplicate data path for the database and does not help the database at all
that is why the dba probably deleted those indices which is the correct
thing to do if you know what you are doing and you deleted the correct one.
On the contrary having too many indices would hurt the database on inserts
and deletes. The reason OneWorld shows those indices in TDA is that you
need them so since OneWorld specs use them. Do not delete those!!. I have
also found that having too many indices or having less indices are not on
the critical path for performance. There is something wrong with your
report. Provide detail of you report and we would get to the bottom of
performance blues....

In looking more closely, I am missing a ton of indexes. I currently have just barely more indexes than I do tables...barely.
Our Xe upgrade was handled by consultants a few years ago, so myself and staff know little about the process. Is there a chance our indexes where removed and never rebuilt during the upgrade?

I am considering doing a blanket rebuild which raises a few questions I am hoping the list can answer:
1) A blanket rebuild appears easier through RDBMS. But, there are those that argue I will see a performance hit from this. Is there a UBE in OneWorld that will rebuild all indexes?
2) How long will this take for about 2650 tables (financials and real estate...about 30GB data)
3) Is there anyway to predict the increase in DASD util???
4) I am hoping to upgrade to ERP 8.0 within the next 4 to 6 weeks. Should I wait to rebuild after the upgrade?

Thanks In Advance!

Ryan Hunt

Ryan,

There is a UBE that will allow you to do a full index build across all files but our CNC is out today so I don't have that handy. We have done this recently and it took a little over 24 hours and our PROD data covers a little over 101 Gb of Dasd. The logical files across our PROD environment is 22.6 Gb. I hope this helps.

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

Ryan,

During Off-Peak hours - just go into TDA, and hit the Generate Indexes button. YES - with a zillion records, it is going to take a bit of time - and you will have a performance issue while the indexes are being built, but - all the indexes (JDE Indexes) will be rebuilt...

Exclusive access to the table may be required.

Depending on what indexes were removed - you may have duplicate keys - be aware of this issue. It is one reason not to remove indexes.

db

Thanks to all for the very informative responses. I appreciate your help.

DBohner...about the duplicate keys....how does that happen? I didn't realize that indexes deal with integrities and constraints?

When you go into the Table Design Aid, one panel is for indices. Withing those indices is an option of Unique... you create the index, then right-click and check unique... by default, the Primary Key is unique.

There can be more than one set of unique keys per table (degrading performance). If the previous consultants removed indexes, there is a chance that they may have removed unique keys (just a chance). If the uniqueness was removed - and new records are inserted - there is a chance that the new records do not maintain the uniqueness of the subsequent keys...

Help or Hinder??

db

Yes, gotcha. I don't spend much time in JDE tools such as TDA, RDA, FDA,
etc. I wasn't aware it would be that easy to make that mistake within
OMW/design tools.

Let's hope nobody did anything like that!!! Thanks for the info.

Ryan Hunt



Ryan Hunt
OneWorld XE; Update2; SP17.1_E1
AS400; V4R5
DS: Win2k SP2, SQL 7.0 SP3
TSE's: Win2k(SP2) & NT4.0(SP6a) with Metaframe 1.8

Sorry about the long post, but I could really use some clarification on the DB2 optimizer. We were able to repair our report by changing the SQL it used. Odly enough, the query now seems a bit more complex and has to do much larger table scans, but the report is much quicker.

I am now trying to determine if rebuilding all indexes by environment is worth the trouble. My current plan is to rebuild all the indexes for our development environment since, like production, it is missing most indexes. I started with just the F0911 to start and ran some query and optimizer tests.

I have found that some queries are doing full table scans against production F0911 and taking a very long time. The same SQL against our indexed development F0911 takes advantage of various indexes and finishes much more quickly.

HOWEVER, I have had some queries that are finishing more quickly in production (with only 3 of 34 indexes for F0911) with the optimizer using views such as F0911LA instead of indexes!!! Same SQL in development is now using indexes, not using views, and some are taking longer.

So, I am looking for some of the following info:
1) In the past of have been an MS SQL tech so I am very unfamiliar with the DB2 optimizer. Can anyone shed any light on why the optimizer utilizes views?? And, why are these views quicker since I would expect that they would also rely on parent table indexes?

2) Will I be potentially making a mistake by rebuilding the production indexes? JDE of course wants me to build them as they were there for a reason. However, if views are sometimes used and are quicker, should I be more selective about the indexes I rebuild?

3) I am worried about post performance if I rebuild indexes. If any other AS400 users would like to respond with how many indexes you are using, and if you have any experience with UBE post performance per index, I would really be interested.

4) When we upgraded from V4R5 to V5R2 I remember there was a command we had to run to convert/rebuild access paths etc. I can't remember what that command was, but could that be what removed so many of my indexes (for instance if they had not been used lately, would the AS400 omit them or something??)

5) Do interactive versions/applications exclusively use views to query the DB? If so, is there a chance that I will continue to see the trend of views not relying on indexes and therefore no performance increase?

Any input will be greatly appreciated!!!

Thanks

Ryan Hunt

Ryan,

two months ago I was in the very same situation you are in now. My F0911 table had only a few indexes. In fact, my production database was missing 1240 OneWorld indexes! I was shocked! However, the database contained over 2000 AS/400 logical views. After doing some research, I had learned that this was a migration issue... we started out with World and then decided to go with OneWorld soon after. The consultants who did the install simply installed OneWorld over World. To my understanding (somebody correct me if I'm wrong), the installation scripts for migrating World to OneWorld detected that some OneWorld indexes were already represented by the World logicals, meaning that the key fields in some of the OneWorld indexes were the same as some of the existing logical views, therefore it did not create them because you would end up with duplicate indexes... I did a full backup of the database, deleted all World logicals in that database and then re-created all the OneWorld indexes by following document oti-00-0123 titled "Re-Creating Large Amounts of OneWorld Indexes". Users haven't experienced any slowdown in performance at all. In fact, overall performance is better... then again, I did not do any benchmarking.

By the way, by eliminating the World logicals, I cut my database size in half!

I hope this helps,

When you tested the reports to see how long they were taking did you delete the SQLPKG for the reports before you ran them? If there is an existing SQLPKG for a report, it will use the query optimize information inthat package vs. looking for new access paths.

I would suggest building all indices as it will improve overall performance. If you do not want to go this route, use the DB monitor on the AS/400 to help you determine which indices need to be created.

Ryan,

How do you determine what indexes are on a file on the AS400 and how you were missing the OW indexes? Can I do this from the AS400 command line?

We are in Coexistence and I looked on the AS400 (wrkobj) and there are numerous logicals on the F0911 file (F0911AA, AB, etc). I looked on the Operations Navigator (under Database/Libraries) and there were no indexes on the F0911 file)

So if I have all these logicals with some key fields, I can't create the OneWorld index or can I?

Grant

<WARNING_LONG_POST>

Grant,

I believe that you can create your OneWorld indexes regardless of your World logicals. My previous post to Ryan mentions that the migration scripts for migrating World to OneWorld had detected duplicates and therefore did not create the OneWorld index of an equivalent World logical. However, if you regenerate your indexes throught OneWorld, they will get created as if you never had World logicals. (Once again, if I am wrong, somebody please correct me). OneWorld does not know about any indexes and/or views on any table outside of its object librarian. If you have 26 indexes defined in OneWorld for the F0911 table, than all 26 indexes will get regenerated when doing it throught OneWorld regardless if there are really 78 World logicals on that table.

The AS/400 stores index information about every table in the system in table SYSINDEXES in library QSYS2. Therefore, to see all indexes (including logicals) you have for table F0911 in database PRODDTA, you could simply issue the following SQL statement:

SELECT index_name FROM qsys2.sysindexes WHERE table_schema = 'PRODDTA' AND table_name = 'F0911'

OneWorld stores index information about every table per environment in table F98712 in the Central Objects database of that given environment. Therefore, to see all indexes that OneWorld knows about for table F0911 in the production environment, you could simply issue the following statement:

SELECT tpobnm, tpinid FROM copd7333.f98712 WHERE tpobnm = 'F0911'

NOTE: TPOBNM is the name of the table (obviously) and TPINID is the sequence of the index. Therefore, if you concatenate these two fields, you end up with the exact names of the OneWorld indexes... see were I'm going...

In order to find out how many OneWorld indexes are missing in your database/library, you simply have to find out which indexes are in the F98712 and NOT in the SYSINDEXES table. Sounds simple. It is.

The following SQL statement doesn't look very pretty, but it does the trick:

SELECT tpobnm AS OWTable, {FN CONCAT({FN RTRIM(tpobnm)}, {FN CONCAT('_', {FN CAST(tpinid AS CHAR(3))})})} AS OWIndexName FROM copd7333.f98712 WHERE {FN CONCAT({FN RTRIM(tpobnm)}, {FN CONCAT('_', {FN CAST(tpinid AS CHAR(3))})})} NOT IN (SELECT index_name FROM qsys2.sysindexes WHERE table_schema = 'PRODDTA')
ORDER BY OWIndexName, OWIndexName

<\WARNING_LONG_POST>

Hope this helps,

Jim, I think you have solved it! I talked with someone who was here during our implementation and they said we had originally intended to go live on World, but the OneWorld Real Estate module was ready sooner than expected so at the last minute we installed OneWorld and went live with that.

Did you find any corruption when you built the indexes, or did the logicals properly maintain integrity. I am nervous about that.

I think I will delete logicals and rebuild indexes for our Dev environment this weekend.

Thanks again!

Ryan Hunt

On the AS/400 you can use the dspdbr command to find indices over a physical file. This gives you a huge list not sorted in any order. I have created a command prtdbr that will show you all of the indices for a physical file in key field order. You can download it from www.jdetips.com

Jean,

How about uploading your utility to the jdelist downloads forum? I assume that it is freeware.



.......................