I don't think every supported database has the "CREATE OR REPLACE VIEW" syntax, so I believe the system drops and creates a new view.
Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Tuesday, March 06, 2012 8:52 AM To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation I know on Oracle a view can not be altered. It can only be dropped and created. Not sure if the same applies to MSSQL. -----Original Message----- On Tue, Mar 6, 2012 at 8:17 AM, Narayanan, Radhika wrote: > ** > > Hi, > > That is a lot of useful info. Thanks for that. We're working on SQL Server > DB Replication for reporting purposes. The reporting DB uses views. When a > field is added to a regular/join form, does the view get dropped and > recreated or does it get altered? > > When the DB Replication is ON, we are not being allowed to modify forms > using Developer Studio. We're forced to remove the replication, modify forms > and then install Replication again. Is there an alternative to this? > > SQL Server 2008 is what we use. > > Thanks > Radhika > +44 20718 20431 > >-----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton > Sent: Wednesday, February 29, 2012 11:57 PM > To: arslist@ARSLIST.ORG > Subject: Re: Remedy Table Recreation > > ** > > The information you provide is always helpful. Thanks to your first > response we have what we need to move forward. > > Axton > >-----Original Message----- > On Feb 29, 2012 12:06 PM, "Mueller, Doug" 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: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 > >-----Original Message----- >> On Mon, Feb 27, 2012 at 2:48 PM, Mueller, Doug 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"
