Doug, Your info is always helpful.
In the discussion you may want to have the upgrade code checked as I just upgraded ARS to 7.6.04 SP3 (on Linux and Oracle) and while my metadata tables have the new column, there is no index on it on any of them. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Mueller, Doug Sent: Wednesday, February 29, 2012 12:07 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation Axton, OK, the implementation was not carried to the complete degree for Oracle as was originally planned. There is again discussion about why not. For ALL databases, all data tables (T/H/B all have the entry ID as a unique value) and ALL metadata tables (several of the ones you call out you will find have a new column in later releases that provides that unique value) have a unique value that should have unique index on it. Sybase and MS SQL have these unique keys all defined as primary keys. Oracle at this time did not take that final step of defining them as unique keys and just have them as unique indexes. The argument of the team was that the DB replication technology of Sybase and MS SQL required the use of primary keys so we did that. The DB replication technology of Oracle just required a unique indexed field exist so we did that for Oracle and didn't go the last step as it was not required for DB replication. Now, you can of course add the primary key characteristic if you want to all of the tables (and it is being discussed internally). Now, one final challenge for you is that this work has been being done over a couple of releases and it is not fully in place for everyone until the 7.6.04 release. So, you will not have some of the work that was done for Oracle in your 7.5 installation. Sorry for the slightly incorrect information about Oracle and the primary key. We are there with other DBs and at the doorstep with Oracle with unique indexes on every table. I hope this correction is helpful, Doug Mueller -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Monday, February 27, 2012 1:27 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation We did not see a primary key defined for the T, H, or B tables, or the meta-data tables, so we created a primary key on those tables. We did, however, see a unique index on all the T, H, and B tables. We are rethinking our approach because in later versions of Oracle Streams they added an option to use a unique index, versus an explicit primary key, for replication purposes. I was referred to this KA on my inquiry to support: https://kb.bmc.com/infocenter/index?page=content&id=KA305401 Which, at the end, states: "In essence this is not something that is currently available in AR System, the only primary keys added was for Sql Server and Sybase because those were the only two databases that needed primary keys for replication. The thought was that other databases don't need to have primary keys and they can be replicated just with the presence of unique indexes." While this appears to be true today, this was no always the case. This is still not true for some of the meta-data tables because they have neither a unique index or a primary key constraint (e.g., actlink_set_char, actlink_set, filter_notify). While replication can still work on these tables, it forces the database to perform a table scan for each replicated record, comparing the value of every column. The primary key can be defined at the time the table is created or after the table is created and is different from a unique index. Take this DDL from a stock system for table B1: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0)) / create unique index ib1 on b1 (c1) / While this table has a unique constraint through index ib1, it does not have a primary key defined. Compare this to the DDL for the same table with the primary key constraint: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0), constraint sys_pk_3933 primary key (c1) / create unique index ib1 on b1 (c1) / We looked to add this to the ardb.conf at the time a form is created, but that is a no go because the ardb.conf entries only append a suffix to the create table statements, at this position: create table X (columns...) <ardb.conf clause goes here> / This is great for storage parameters, partitioning, etc., but not so good for constraints. It also sounds like the ardb.conf is obsolete, for all intents and purposes, since Remedy no longer drops/recreates the underlying tables, thus making manual modifications to the underlying tables characteristics persistent (safer). Thanks for taking the time to look through the code and send this information. It is immensely useful and gives us the information we need to make the correct decision on going forward. Regards, Axton Grams On Mon, Feb 27, 2012 at 2:48 PM, Mueller, Doug <doug_muel...@bmc.com> wrote: > Axton, > > By the way.... > > On the other topic in your message, definition of a primary key. > > We found that a number of the replication technologies require a table to have > a primary key in order to replicate successfully. Since every one of our > tables > has a primary key -- the entry ID -- and we can define combinations of fields > for other forms -- like status history -- that are unique, we have added a > primary key to every metadata table and all the data tables for things we > install or create going forward. I don't think we retrofit the DB to add them > to existing installations. > > This work was done in the 7.5 or so timeframe (before, at, or after I am not > sure, but in the range of the three releases just before, at, or after 7.5). > > No issue if you want to create something else as your primary key, but just be > aware of the fact that the environment does create a primary key for all > tables > now so that it is present and defined for any environment where DB replication > requires this capability. > > So, you may want to check to see if there is already a primary key defined and > maintained for you. Then, you can decide whether this is sufficient or do you > need to do something different for your situation. Just be clear that you may > need to undo the primary key specification in your work as we should already > be > doing one. This was added in that range of just before, at, or just after the > 7.5 release. If before or at, you have already seen/addressed this situation. > If after, just be aware that this is coming and may have some impact on you in > the future. > > Doug Mueller > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton > Sent: Monday, February 27, 2012 7:53 AM > To: arslist@ARSLIST.ORG > Subject: Remedy Table Recreation > > First, some background information: > It used to be the case that certain operations would trigger Remedy to > recreate a database table: > - rename existing table > - create new table with the original name > - copy the data from the renamed table to the new table > - drop the renamed table > > I remember altering the precision on a decimal field would trigger > this, and I seem to also remember something with currency fields. > > Now for the issue: > We have applied changes to every table in the Remedy database to > define a primary key. This primary key is used for Oracle Streams > replication to a target database. If the table is recreated, the > primary key is dropped, which can cause Streams to choke if the table > contains a large volume of data. > > Now for the question: > Does anyone know of an action that a user can perform through the > Remedy clients that will cause a table to be recreated in this manner? > When I say "Remedy Clients" I am referring to Dev Studio, User Tool, > ITSM applications, mid-tier, or the Remedy API. > > Relevant Environment Information: > - Oracle 11g > - ARServer 7.5 > - Apps 7.5 (ITSM, CMDB, etc.) > > Thanks, > Axton Grams _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
