E9.1 Table primary Key change

[ashwin E1]

Hi,

I'm positive there is no similar question thats why posting this as a new thread.
I added a new field into a standard table(added the column as a last column in the table, also the field is a custom DD item i just now created). I have updated the primary key(Index) of the table to contain this new field. I only have added this extra column into the primary key, i took backup using SQL and generated the table and then put the data back.
Now my question is will any of the existing objects that use this table have any kind of adverse effects? I have though of enough scenarios but not able to find one which will result is existing report, Application or BSFN not working. I guess i will have to change the .h of CBSF's that are using this table or may be if this field is not used even they might not have an issue. Anyone has done this before and have seen issues later in this regards. This is a E9.1 system.

Thanks,

 

[Kim Schmidt]

You might have problems with old programs doing inserts to this table if they are unaware of it, since databases as a rule do not like having null values in a primary key column. If you created your new DD item with a default value, this probably would not be a problem. A point which may be more of a style preference: if existing code is performing Fetch Single operations against this table, I suggest using Select/Fetch Next operations when not selecting on the full primary key.

 

[eydeak]

When you change the primary key for a table, you'll want to restart JDE services to make sure all caches are cleared of references to the old spec. If you are on DB2, you'll also want to make sure SQL packages are cleared on the iSeries. Anything other than adding a new field to the end of the table, and you need to be really diligent about getting the old spec cleared out of all caches so that you don't get an unexpected result somewhere.

You definitely want to review all table I/O statements for this table in existing code. Anything using the primary key may no longer behave exactly as before. As Kim noted, inserts should be updated to populate the additional primary key field so that you are inserting unique records. Updates that aren't using the full primary key will update all records with a partial key match and that might not be desired. Fetch/Next that isn't specifying all key fields might not bring back the correct record first. Selects using a partial key may be bringing back extra records. You really need to review the code to know what the desired behavior is and determine whether the differences matter. I recommend using Patwell's Object Browser to do an ER search to get a comprehensive list of programs utilizing the tables.

Regards,
Ellen

 

[craig_welton]

I have to ask ... you said you changed a "standard table". As in a stock JDE table, like F0101?

 

[peterbruce]

Hi,

I'm positive there is no similar question thats why posting this as a new thread.
I added a new field into a standard table(added the column as a last column in the table, also the field is a custom DD item i just now created). I have updated the primary key(Index) of the table to contain this new field. I only have added this extra column into the primary key, i took backup using SQL and generated the table and then put the data back.
Now my question is will any of the existing objects that use this table have any kind of adverse effects? I have though of enough scenarios but not able to find one which will result is existing report, Application or BSFN not working. I guess i will have to change the .h of CBSF's that are using this table or may be if this field is not used even they might not have an issue. Anyone has done this before and have seen issues later in this regards. This is a E9.1 system.

Thanks,

Hi Ashwin,

If by "standard table" you refer to a table that is installed with JDE, I echo the concerns raised by others in this thread. A general rule is that standard JDE tables should not be modified, apart from adding extra indexes. Did you make these changes in the database? Or using JDE's design applications?

When upgrading, the extra column and primary index change will likely be lost. There could also be issues because the existing table specifications would be different to those contained in the upgrade.

When you find a need to add a column to a standard JDE table, you need to do your "due diligence" first. That means asking questions like "Will any of the existing objects that use this table have any kind of adverse effects?" before you make changes. Other questions to ask: Is there an alternative to modifying a standard JDE table? What have others done when they have a need to add a column to a standard JDE table? Would a tag table and/or virtual table satisfy the requirements? Would table triggers (JDE or Database) help (system tables should not be modified, which includes adding JDE triggers)?

If you can avoid modifying a standard JDE table, the changes required will be a lot less and a lot simpler and easier to maintain. You will not risk losing them when upgrading.

I hope the above helps and possibly broadens your options. I hope you can achieve your aims with small and simple changes.

 

[Larry_Jones]

Depending on how widely used the table is your change is like pointing a gun to your foot or your head and pulling the trigger.

 

[DSauve]

You are asking for trouble by modifying a native JDE table. If you need additional fields to store data, create a tag table with the same key values. You will thus prevent having to possibly update all places where the native JDE table is used in ER code, and you will be future-proofing your system from future ESU's that contain that same native JDE table.

 

[kiran4hari]

Modified Standard table and added a field to Primary Key? Curious to know which table is this?

 

[ashwin E1]

As you all had pointed that was my first question to the Functional when they asked me to do this. why changing the standard table? They still insisted. And yeah there are a ton of places this table is being updated and inserted and I'm changing all those. they themselves gave me list of objects taken through object browser, so yes they are aware. This client is still in 9.10 not sure how they plan to move to 9.20 they already having a lot of customized standard objects.

 

[Larry_Jones]

Well ... client is choosing to point gun at foot (maybe head) and pulling trigger. Hopefully you'll be out of there before the crap really hits the fan.

 

[Larry_Jones]

Also want to know what table?

 

[eydeak]

Glad other people noticed the word standard in your initial post. Very curious as to what table. Unless this is a very low seldom used table you are making any future upgrades a nightmare and much more expensive to accomplish. You'll also never be able to get Oracle to support anything related to that table. Any issues will always be blamed on your modifications.

That gun Larry mentioned is definitely going to draw blood - just a matter of how much.

 

[ashwin E1]

The table is F04573, seems like the client is about to switch from JDE.

 

[BOster]

As you all had pointed that was my first question to the Functional when they asked me to do this. why changing the standard table? They still insisted. And yeah there are a ton of places this table is being updated and inserted and I'm changing all those. they themselves gave me list of objects taken through object browser, so yes they are aware. This client is still in 9.10 not sure how they plan to move to 9.20 they already having a lot of customized standard objects.

Aside from modifying a DD item size or type modifying the PK of a pristine table is probably the single most dangerous thing to modify. I highly doubt it will be as "simple" as finding and modifying every piece of code that does an insert/update. This could significantly change business logic with far reaching ramifications. This will not end well.

Only thing I will add... there are exceptions to every rule. We have "rules" about what we do and do not modify and we have probably found cause to break almost all of them in certain situations... this is one of those rules and even though we have not broken this one I can remember one case where we almost did... we found an alternate solution but I was prepared to mod a PK of a pristine table. I guess what I am trying to say is that not being privy to everything that went into this decision I probably shouldn't say definitively that it won't work. If the functional team that requested this truly understands the ramifications from a technical standpoint then it may have been determined it was worth the risk and effort. This is just one of those "last resort" type of things IMO.

 

[JohnDanter2]

You've modified accounts payable detail?
I'd hazard a guess that almost every user of JDE on the planet will use that module and raise bugs/ESUs so you'll constantly be swamped updating all your code each ESU to retrofit what you've done.

I'm a pain in the butt developer as whenever a spec lands on my table I always ask 'why' and 'what's the problem you're trying to solve here' as chances are there is another way to solve the issue.
Non technical folk should not be making technical decisions and I think this change will come back to haunt your company.

Is this for something like split payments or some ability like that?
But that index already has 8 fields. most of which are numeric, surely you can make it more granular some other way? Id be more keen to look at what generating the records and mod that to make/break the numbers down for you

 

[Rauf]

As you all had pointed that was my first question to the Functional when they asked me to do this. why changing the standard table? They still insisted. And yeah there are a ton of places this table is being updated and inserted and I'm changing all those. they themselves gave me list of objects taken through object browser, so yes they are aware. This client is still in 9.10 not sure how they plan to move to 9.20 they already having a lot of customized standard objects.

Hi Ashwin.

Happy that I see a word "Functional" in your post.
Yea, they will ask what ever they like. I have the same experience. They will just point one application and will tell you to add one column.
I usually listen to a functional person in a different way: I will understand what he/she really needs. Then I decide "how" to achieve it, instead of asking them "how".

As we see in other posts, the best way is the tag table. It won't harm you, the functional person or JDE.

 

[ashwin E1]

Hi Ashwin.

Happy that I see a word "Functional" in your post.
Yea, they will ask what ever they like. I have the same experience. They will just point one application and will tell you to add one column.
I usually listen to a functional person in a different way: I will understand what he/she really needs. Then I decide "how" to achieve it, instead of asking them "how".

As we see in other posts, the best way is the tag table. It won't harm you, the functional person or JDE.

Thats the way I like and do it too! but from the start this client is do this and do that and not how this can be done. I'm here only for this modification, will be out mid of this week! hope they will roll it back after testing!