• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Table indexes, ESUs and indexes created externally on the DB

johndanter

Legendary Poster
Hi list,

I have a question on indexes, both JDE defined and also externally defined indexes and tables that maybe in an ESU.

We have many bolt on external systems that use PLSQL, java etc and have thus created many new indexes outside of E1 in SQL on the DB, to satisfy queries etc for the external systems to use.

My worry is if we have table A with 10 JDE defined indexes that you me and everyone using E1 also has, yet we here create 3 more to satisfy our external systems also using table A on the DB, what will happen if table A is present in an ESU to now give that table 11 indexes?

Would our 3 custom external indexes be wiped?

I'm thinking yes and am trying to get our other development team to start thinking about the indexes they create.
If they are created outside of E1 we have no record of that in E1 OMW tables and I am worried an ESU could wipe the external indexes.

If the new indexes in the ESU are merged, then that's fine. Yet I still have concerns over the fact I may actually need to create an index in E1. Then I do know the 3 custom indexes would disappear

Any thoughts?

Thanks

John
 
Last edited:

brandonkirsch

Active Member
Unless the ESU does a "generate table" command - which completely drops and recreates your table - your indexes *should* be left in place.

It's a bit risky, but should be easy enough to test in DEV. I would be interested to hear about your results.

I still think there are several good reasons for your development team to document anything they change, especially in the cases like this where they are bypassing OMW. If the indexes are important enough to be created in the first place, they are important enough to be written down or saved in a repository of some sort.
 

JMR

VIP Member
Hey John,

I don't know if ESU indexes are merged or not, but like you said, even if you have to add an index via OMW, you will wipe-out any non-E1 indexes. So you really need to be able to identify those non-E1 indices.

You can do this using a SQL tool and the following tables:

F98713
sysobjects
sysindexes
sysindexkeys (if you want to compare any column-level differences between E1 and DB)

You should be able to join those tables in such a way as to list indexes in the database that are not in E1. Sorry, I don't have an example for you right now, but if I find time, I will try to find one that I've used in the past.
 

Larry_Jones

Legendary Poster
Typically an ESU is not going to regenerate (wipe) a table unless its something like a temporary work table, etc.

What you're discussing is more of a concern for upgrades where a schema change forces a table conversion. In that case you will lose your outside indices.

Just make sure that the developers document them and move on ;-)

if the developers won't document them take away their privileges to create them.
 

peterbruce

Legendary Poster
John,

Larry has made an excellent point. Regenerating the table will remove all the data. If there was a need for external access to a table which held enough data to make additional non-JDE indexes worthwhile then it would be highly unlikely that such a table would be dropped and regenerated during the application of an ESU. As Larry said: "What you're discussing is more of a concern for upgrades ..."

This raises some good points for ESU application and upgrades:

1) Keep an external record of all changes to standard JDE objects as they will be lost in an upgrade.
2) Make sure you know what the application of an ESU will do. Tables indexes as discussed here would most likely not be impacted by applying an ESU, but there are other modifications to standard objects that may be impacted.
 

johndanter

Legendary Poster
Thanks for the replies guys.

I guess the ESU would never need to regenerate the table and risk losing data unless the ESU adds a new column.
But indexes can be added and the indexes regenerated at any time without losing the data (as you need the data there for the index anyway :))

So it could happen. I'm just curious if anyone has has issues with this and external indexes.

How do you guys do it?
Do you have external systems accessing the E1 database at all? Have you ever made any new external indexes to assist the external systems?

This table F984052 seems to list ESUs that have affected a table.
 

JMR

VIP Member
John,

This should give you a pretty good idea of DB indexes that are not in E1. If memory serves, I think there is a chance of false positives. Joining to the correct index is not trivial... on SQL server anyway. I notice you're on an Oracle DB, so I can't speak to the tables they use for the DB metadata (sysobjects, sysindexes, etc). I imagine there are some differences in valid keywords also (CASE, RIGHT, CHARINDEX, etc). Sorry, I don't have an Oracle equivalent.

Code:
select j1.*
from (


    select 
        O.name as TableName,
        I.id as ObjectID,
        I.indid as IndexID,
        I.rows, 
        I.name as IndexName, 
        
        /* --------------------------------------------------------------
          Use the Index name to extract the Index ID that corresponds to
          the E1 index ID.  
          ---------------------------------------------------------------*/ 
        CASE RIGHT(I.name, LEN(I.name) - CHARINDEX('_', I.name))
            WHEN 'PK' THEN (
                /* get ID of primary key index */
                select cast(tpinid as char(2))
                from odfdevsql.ps_dv810.dv810.f98712
                where tpobnm = O.name AND tpprmf = '1')
            ELSE RIGHT(I.name, LEN(I.name) - CHARINDEX('_', I.name))
        END as CharIdx,
        RIGHT(I.name, LEN(I.name) - CHARINDEX('_', I.name)) as CharIdx2
    from sysobjects as O
    join sysindexes as I
        on O.id = I.id
    where I.keycnt > 0 and I.rows > 0


) as j1


left outer join ps_dv810.dv810.f98712 as OW
    on j1.TableName = tpobnm and CharIdx = cast(tpinid as char(2)) 


-- get missing indexes from E1
where tpobnm is null


order by TableName
 

johndanter

Legendary Poster
Cheers guys and thanks for the input

A CNC friend of mine says that E1 will NOT drop any external indexes, just the ones in the ESU and then recreate them as the ESU according to the table spec.

So any external or new ones will remain as the table is not dropped in it's entirety.

I'll keep all this info handy and safe :)
 

BBritain

VIP Member
Hi guys,

I think I need to understand this before we write this off as "done" or "answered". I am just seeing this thread for the first time, so I was in "Oh crud!" mode as I am reading through this.

First, I think the only way for an external index to get "blown away" is if the E1 index has exactly the same name and therefore won't (get blown away) with an upgrade or ESU. IS THIS CORRECT?

Second, John, you state that you if you create a custom index that "Then I do know the 3 custom indexes would disappear". I don't think that even this is correct, again unless the index name conflicts with one of the 3 custom indices. IS THIS CORRECT?

My understanding is that many db optimizers will create "many" external indices in order to speed up processing and the db is responsible for which index get used anyway (and not E1). (the way I heard it, is that E1 can suggest an index, but the db may or may not use an exact match or even have a better index) Is this correct?

Ben again,
 

Larry_Jones

Legendary Poster
Ben,
I believe that during upgrades a table conversion creates a new table (such as F4211_UPG) following the JDE table specs. Data is copied/written to the table until completed. At which point original table is dropped and new table is renamed (F4211_UPG to F4211). This has the effect of "blowing away" any externally defined indices.

Correct me if I'm wrong.
 

JMR

VIP Member
I think one of the issues here is with adding a new E1 index to a table that has external indexes defined at the database level.

In OMW, when you do a 'Generate Indexes', what is this actually doing on the DB side? Is it dropping all the indexes and regenerating them? That would be my guess, but I am not sure.
Best case scenario: it drops only the indexes that are defined in E1 and regenerates those. I guess this is what it should do and then there is no problem for this scenario.
 

johndanter

Legendary Poster
I may fire this question off into the CNC forum to see what they have to say?

One of my CNC mates said E1 will only go through all the indexes in the ESU, it should not touch any new customs ones.
Thankfully here we name them TABLENAME_GTSN N being an incremented number.

Sorry to open a can of worms
 

craig_welton

Legendary Poster
Hi

Generating Indexes in OMW will drop all JDE "known" indexes and re-create them, so external ones are left alone. As a side note, check out B9800200 ;)

Craig
 

DBohner-(db)

Legendary Poster
Hi John,

The following Oracle Document does a good job explaining some issue with creating Custom Index within JDE:
- E1: TDA: Custom Indexes on EnterpriseOne Table and Index Changes in Upgrades or Software Updates (ESUs) (Doc ID 648417.1)

Basically, the idea is to create, then delete faux indexes - until you have a buffer of at least 10. JDE names the indexes Incrementally. So, when an ESU or Update hits the system, any new JDE Indexes could overwrite custom indexes, if the procedure is not fully followed. When this occurs, your fetches that used the original custom index will no-longer work as expected (the BC AddressNumber = TC AddressNumber might, now, looks like "BC AddressNumber <= TC AddressNumber".

P95150 allows you to look at E1 Indexes and Externally Defined Indexes. Additionally, it allows you to create a single index, so you don't have to rebuild all indexes over your F0911.
Look for Oracle Document, titled: Dropping indexes using P95150.

I, strongly, recommend my customer match the externally defined indexes into the JDE File Spec, even if they don't create them there. At some point and time - they will want to use them internally (or the table will be regenerated).

So - my two pennies in with the rest of the change.

(db)
 
Top