Feb 22 2016

Generate single index on table [Tips]

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)

WN-medJust posting an old tip, since we had to do this recently.

Transaction tables such as F0911, F4111 etc hold 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 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 logoff 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.

Please register to download the attachments in Generate single index on table [Tips]

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

NB: It is very easy to mess up with this app.

Published under the license.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Generate single index on table [Tips], 5.0 out of 5 based on 1 rating
(Visited 766 times, 1 visits today)

About the author

Deepesh M Divakaran

Deepesh MD is more of a Gizmo Geek, and a techie.
A very experienced Technical Consultant on Oracle JD Edwards EnterpriseOne, and OneWorld. As a consultant he has worked for wide range of industries, from Manufacturing, Media, Oil & gas etc. and has worked on re-engineering modules, and areas of customization.
With a touch of class and understanding towards what ever he does, he is often regarded by his colleagues as the one stop answer for all Technical/Software related queries.

1 comment

  1. jainmegha24

    Great Article

    VN:F [1.9.22_1171]
    Rating: 0 (from 0 votes)

Leave a Reply