Generate single index on table

Transaction tables such as F0911, F4111 etc hold a huge amount of data probably a third of petabyte. Generating indexes on such tables (through JDE) are a huge task and can hold up the database for a long period of time, probably crash it. There are multiple ways to handle this issue

Method 1 (most common and safest)

Generate the index in the database (via SQL) with the same column listing and properties as required.

You know why this is the most common and safest way to do? If there are users logged on to the system, or processes running which use the table – all your indexes get locked up. Thus JDE cannot generate the index; only SQL can.

Method 2 (Custom and 2nd best)

Use the API – 

JDB_CreateSingleIndex(hUser, szTableName, szObjectOwnerID, szSignonPassword, szDatabasePath, idIndexId, &nErrorNum)

With this API you can generate the particular index. Only catch is that, if you are modifying an index, you will have to drop the index first and then generate.

These API’s are encapsulated within the standard JDE BSFN – B9800200 (JDB Data Management Source File). Now I have termed this as 2nd best, since it’s in our control on what we are asking JDE to do. You will need to log off

all user sessions, interfaces and so which may use the table.

We have written a small utility application which can help you create/drop the single indexes as required.

Method 3 (JDE Standard Application)

P95150 – Index Selection tool should be used only by CNCs. This application can be used to do everything with Indexes of tables and is available from TR 8.98 onwards.

You can drop all indexes, generated the selected index, or drop the selected index etc.

To use the application, you need to add the table in the application for the particular Datasource and then select the index to generate it. Visit the MOS document 1114664.1 to know more or the Oracle Docs