The information you provide is always helpful. Thanks to your first response we have what we need to move forward.
Axton On Feb 29, 2012 12:06 PM, "Mueller, Doug" <[email protected]> wrote: > 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: > [email protected]] On Behalf Of Axton > Sent: Monday, February 27, 2012 1:27 PM > To: [email protected] > 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 <[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" > > > _______________________________________________________________________________ > 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"
