Index question

Guys,

I have issue. We have very large F0911 table and we wanted to add a new index to this table. The correct way would be to generate the index from OMW. But it will generate all the indexes ( after dropping them first). Since this is such a large table we want to avoid generating all of them. In addition, we have replication tuned on.

We created the index manaully directly on the database and deployed promoted the spec to prod, build and deployed the pacakge. But it still says unable to find the index.
Hence to resolve the issue in DEV I did following.
1. Renames F0911 table,
2. Created blank table F0911
3. FROM OMW i generated the index.
4. Deleted the New F0911 table
5. Rename the orignal table back to F0911.

Everything is working.

Question, Why do I have to generate the index from the OMW ( considering spec exist both in client and server and full pacakge was build and deployed). I looked thru the debug log and did not see any special table it updated for it to work.

Has any one seen this issue. Is there any thing I can do without actually doing a manual Index generation from OMW.

Appreciate any help

Nick,

There should be no issue creating the index manually, and the DB should take advantage of it. You don't specify what your DB is, but if you are on the 400 (IBM i) you can create a duplicate index F0911_xy where x is the index # and y is a new designation, and they will share the actual access path. Then when JDE drops the F0911_x file, the F0911_xy will keep the path. When JDE recreats the F0911_x file, it will again share the path and you won't have a rebuild.

Just one idea.

Tom

Hi,

We are on SQL 2005. Our release is E810 ( 8.96.2.3) on windows platform.

Unfortunately, Its not working.

Nick,

I think his concern about creating the Index 'manually' is that, it doesn't display as an Index Option during the creation of a Fetch Statement in ER.

Generally, if the Manually Created Index matches the E1 Naming Convention and Index Keys - of the E1 Defined Index, there shouldn't be an issue.
- First, create the E1 Defined Index (do not 'generate' the Index through E1, if your are going the Custom Route.
- Second, Use the Database's Index tool to create a Manual Index that matches the Naming Convention and syntax of the E1 Definition

Now, when you use ER to Fetch, the E1 Definition is presented and the Index is available within the database.

Another 'generally' assumed rule is: Indexes that are created by the Database are better than those that are created by the E1 Toolset. I haven't, personally, experienced the difference - but the great statisticians on the JDE List - say it is so.

On an iSeries - Index Advisor can be both, your Friend and Foe.


(db)

Daniel,

Thanks for your answer. Unfortunatley ER is not the issue. In OMW, index is shown fine without any issue in the code of the application. The problem occurs when we run the application. In the debug log, we see index not found error. We have build an deployed the full package without any error. Theoretically, I know the spec exists, it matches the index (name and specification). In fact we generated the index in DEV, so we know what it should be called at DB level. But its not working.

Nick,

Review:
http://www.jdelist.com/ubb/showflat.php?Cat=0&Number=168890

When you look at the F98713 table, notice the OBNM and the INID fields. If you concatenate them, you end up with the Index Name:

F0101 + 1 = F0101_1

Now, if memory serves me, when you 'manually' create an index that you will be pairing with an E1 Defined Index - it has to match the naming convention from the table. You may have to adjust the Index_Owner and validate that the Index was created in the same Schema as the table (yes, based on the profile used to create an index, the Index could be owned outside of JDE and have been crated in a completely different schema)

On an iSeries, a validation query might look like:
select * from qsys2.sysindexes
where table_name = 'F0101'
order by index_name

I'm unsure of the syntax for MS SQL

If the Index_Name matches expectations and the index is in the right schema - I'm not sure why you would be getting the error.

Confirm?

Append any logs that might be deemed as helpful?

(db)

Daniel,

I have done exactly what you have mentioned. My index is F0911_37 and I still have the issue. As I said, In Dev, I was able to generate the index and it started to work. Its not workign in PY and PROD. Attached is the partial log where I see the error.

Entering JDB_OpenTable(Table = F0911)
May 16 16:40:09.316092 - 9892/10172 FOREIGN_THREAD Opening up local metadata repository. Type - JDESPECTYPE_GLBLTBL.
May 16 16:40:09.316093 - 9892/10172 FOREIGN_THREAD jdeSpecOpen() opening spec of release JDESPECRELLVL_B9U.
May 16 16:40:09.316094 - 9892/10172 FOREIGN_THREAD SpecEncapsulation : jdeSpecOpenLocal completed OK.
May 16 16:40:09.316095 - 9892/10172 FOREIGN_THREAD SpecEncapsulation : Close completed.
May 16 16:40:09.316096 - 9892/10172 FOREIGN_THREAD Opening up local metadata repository. Type - JDESPECTYPE_GBRLINK.
May 16 16:40:09.316097 - 9892/10172 FOREIGN_THREAD PKGBLD: Entering IntSvrPkgGetSpecIniFilePath
May 16 16:40:09.316098 - 9892/10172 FOREIGN_THREAD Entering IntSvrPkgGetINIPaths
May 16 16:40:09.316099 - 9892/10172 FOREIGN_THREAD Entering IntSvrPkgGetINIPaths
May 16 16:40:09.316100 - 9892/10172 FOREIGN_THREAD PKGBLD: Exiting IntSvrPkgGetSpecIniFilePath
May 16 16:40:09.316101 - 9892/10172 FOREIGN_THREAD jdeSpecOpen() opening spec of release JDESPECRELLVL_B9U.
May 16 16:40:09.316102 - 9892/10172 FOREIGN_THREAD SpecEncapsulation : jdeSpecOpenLocalOpt completed OK.
May 16 16:40:09.316103 - 9892/10172 FOREIGN_THREAD SpecEncapsulation : jdeSpecSelectKeyed completed OK.
May 16 16:40:09.316104 - 9892/10172 FOREIGN_THREAD SpecEncapsulation : Close completed.
May 16 16:40:09.316105 - 9892/10172 FOREIGN_THREAD ODBC:S DBInitConnection shr(7) con=0A7AF008 env=04421978 dbc=04425DF8 spid=247 cur=0,3,6 sep=. NJRDBKDDB A (ERPUSER@Business Data - CRP)
May 16 16:40:09.316106 - 9892/10172 FOREIGN_THREAD JDB9900920 - Failed to find index 37 for table F0911
May 16 16:40:09.332000 - 9892/10172 FOREIGN_THREAD JDB3100014 - Failed to load requested index
May 16 16:40:09.332002 - 9892/10172 FOREIGN_THREAD ODBC:S DBFreeConnection free(6) con=0A7AF008 env=04421978 dbc=04425DF8 spid=247 cur=0,3,6 sep=. NJRDBKDDB A (ERPUSER@Business Data - CRP)
May 16 16:40:09.332003 - 9892/10172 FOREIGN_THREAD Exiting JDB_OpenTable(Table = F0911) with Failure

Nick,

Redundant questions - but, they have to be asked:
- Did you generate the index in PY and Production Schemas?
- Did you PROMOTE and Deploy (no errors) the F0911 to PY and Production?

The Index Definition is stored interior to the Table Spec. If you don't promote / deploy the spec, the subsequent environments do not know what Index F0911_37 is (and, thus, they fail?)

If you have KG access:

E1: TDA: JDB errors - JDB9900920, JDB3100014, JDB9900262, JDB3200008 [ID 649643.1]

The first things mentioned for resolution:
- build/deploy an update package of the table
- refresh the global spec tables (glbltbl - .ddb & .xdb) after the deployment

There are additional steps, within the document, to validate the Index exists.

This has been an interesting exercise!

(db)

Add the index in OMW, gen the table header, copy the index name from the header and manually create the index EXACTLY the same in SQL.

Do it all the time.

Just make sure a developer didn't rearrange the order of the indexes in the table editor. JDE really doesn't like that if you don't gen it!

Daniel,

Thanks for your help. Sometime you need to do everything yourselves to get it working. We did everything that was mentioned including global table previous (that's what I assumed). I did the global table generation all over again and its working .

Thanks for your help

Nick,

UHG! Sometimes, .... Yes, you are correct. Personalized Manual Labor - there is no getting around it.

At least - now we all have a solid reference to work from.



(db)

Nick,

for what its worth . . . I'm sure its a hidden "feature" in JDE.
I had a custom table once that I wanted to add a column to. I modified the table definition definition in JDE, checked in and promoted the table def (did not use the Generate table function), built package, added the column in the database via SQL and populated with desired values, and then deployed the package.

JDE refused to acknowledge that field/column until I regenerated the table. I've had this happen several times, so now I copy the table contents via SQL, generate, and move the data back.

For what its worth,