For other people's reference, if they have any interest in Oracle replication, I created enhancement request SW00425739 to update Remedy to create a primary key for all tables.
Regards, Axton Grams On Mon, Feb 27, 2012 at 3:27 PM, Axton <[email protected]> wrote: > 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 <[email protected]> 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:[email protected]] On Behalf Of Axton >> Sent: Monday, February 27, 2012 7:53 AM >> To: [email protected] >> 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" >> >> _______________________________________________________________________________ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
