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