JDE DBA Best Practices - Advice Request

DougR

Member
We have and instance of JDE v812 and we are running a 24/7 Europe wide operation and have a number of tables that have more than 45 million rows (>30Gb).

I am an Oracle DBA and am trying to establish a best practice policy.

Is it good practice to regenerate an entire table when adding a new index, particularly when the table contains over 45 million rows and is 25Gb in size from JDE? And then restore the data from a backup?

When creating a new index on the live database using JDE using the OMW, we encounter an error most likely due to locking by running processes.

Is it acceptable to apply new indexes to existing tables (after they have been set up and promoted to live) using raw SQL by the DBA, as this allows us to use the database’s extended commands to circumvent locking and accelerate the speed of applying the index?

All comments would be appreciated
 
Doug,

Application P95150 allows the creation of a single index, in E1 if your version of software supports it. I don't know which version the application might have been introduced, or if it has always been there.

-------------------------------------------
Review KG Documents:
E1: DB: How to Drop and Restore Indices for EnterpriseOne Tables Using P95150 [ID 1114664.1]
E1: DB: Master Note of Known Issues with UTB, Databrowser, Index Generation and General Database Questions [ID 1197043.1]
-------------------------------------------

Be Careful, you can wipe-out the indexes as easily as you create them.

Note: Sometimes, Creating Indexes using DBA Tools provide better performance...

(db)
 
Hi Doug

First of all, the tables you're talking about are locked for a reason - especially if you have services still running, etc.

So, dropping and recreating the table can be dangerous if users are on the system at that point in time (or if processes are still running). The only way to really remove all locks reliably is to go completely quiet (drop all user sessions, all web services as well as all E1 Logic services running) as well as any 3rd party software. Usually thats not really possible.

So instead, the next safest way to add a new index in Oracle is to run a Create Index Command from SQL. JDE is very separated from the database layer - so there is no issue in running a Create Index command even while users are on the system. Therefore, my recommended steps are as follows :

1. Modify table indexes in JDE for a lower (smaller set of data) environment. Regen indexes using JDE - either OMW (all indices) or through the P95150 (single index)

2. Once the index has been generated, use SQL explorer to identify the "create index" command for the new index

3. in the larger data environment, manually run the "Create Index" command after promoting the object and running a package build (just to make sure that the new index is "in" that environment !)

Some customers just create custom indexes without updating JDE - which can be acceptable, since Oracle will always use a direct index if one is available, but the risk is that a table regen/copy through OMW will drop all the custom indexes if they are not set up in the tool.

Just my 2c....
 
Many thanks guys, I think that generating the index from TOAD allows me to circumvent the locking and turn of the logging is OK it's just we had a few differences of opinions down here.

Regards
 
Back
Top