Re: Remedy Table Recreation
I don't believe there are any actions possible from Mid-Tier or the User tool that will cause a table to be recreated. Yes I believe there are still some actions from DevStudio (or the API) that can cause the table to be recreated, but they should be limited to structure changes (and any anyone changing your table structure should know about the primary key). Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Monday, February 27, 2012 9: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"
Re: Remedy Table Recreation
Axton, Based on recent conversations with Dougshrinking the size of a Char field will NOT cause a recreation of the table...but a re-increase, of a size below the size allowed with the previous size, will...for example Original size: 255 New Size1: 49 New Size2: 50 New Size3: 255 You can't resize a field from 255 to 50 without re-building the table...and the size increase in the second increase WOULD rebuild the table...and again in resize 3 These are the only examples I know of off the top of my headI'm positive there are others of course :) -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Monday, February 27, 2012 8: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" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
Re: Remedy Table Recreation
I read that thread, but the terminology confused me. Just to be clear on the terminology; I am looking for things that issue a DROP TABLE and a CREATE TABLE (in an effort to alter the characteristics of an existing table) and I am not concerned with things that issue an ALTER TABLE. Thanks, Axton Grams On Mon, Feb 27, 2012 at 10:33 AM, LJ LongWing wrote: > Axton, > Based on recent conversations with Dougshrinking the size of a Char > field will NOT cause a recreation of the table...but a re-increase, of a > size below the size allowed with the previous size, will...for example > > Original size: 255 > New Size1: 49 > New Size2: 50 > New Size3: 255 > > You can't resize a field from 255 to 50 without re-building the table...and > the size increase in the second increase WOULD rebuild the table...and again > in resize 3 > > These are the only examples I know of off the top of my headI'm positive > there are others of course :) > > -Original Message- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton > Sent: Monday, February 27, 2012 8: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" > > ___ > 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"
Re: Remedy Table Recreation
I can't speak definitively on that as I don't watch that level of activity at the DB on a typical basis. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Monday, February 27, 2012 9:50 AM To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation I read that thread, but the terminology confused me. Just to be clear on the terminology; I am looking for things that issue a DROP TABLE and a CREATE TABLE (in an effort to alter the characteristics of an existing table) and I am not concerned with things that issue an ALTER TABLE. Thanks, Axton Grams On Mon, Feb 27, 2012 at 10:33 AM, LJ LongWing wrote: > Axton, > Based on recent conversations with Dougshrinking the size of a Char > field will NOT cause a recreation of the table...but a re-increase, of a > size below the size allowed with the previous size, will...for example > > Original size: 255 > New Size1: 49 > New Size2: 50 > New Size3: 255 > > You can't resize a field from 255 to 50 without re-building the table...and > the size increase in the second increase WOULD rebuild the table...and again > in resize 3 > > These are the only examples I know of off the top of my headI'm positive > there are others of course :) > > -Original Message- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton > Sent: Monday, February 27, 2012 8: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" > > ___ > 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"
Re: Remedy Table Recreation
Hi, It depends on the database, where different vendors allow for different changes with ALTER TABLE. I think that if you go to/from 255 bytes in character lenght, this requires a datatype change from VARCHAR to TEXT, which will trigger the operation. For all practical purposes, it should be impossible for a person using the normal user clients to cause this. You would also need to have Admin or Sub Admin permissions to do anything like that. Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. > 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"
Re: Remedy Table Recreation
Thanks Misi. I'm trying to identify a specific case on Oracle. I don't think this exists any more (as it used to), but I need to try and confirm. Hoping I get lucky and find someone who has run into this. Axton Grams On Mon, Feb 27, 2012 at 11:32 AM, Misi Mladoniczky wrote: > Hi, > > It depends on the database, where different vendors allow for different > changes with ALTER TABLE. > > I think that if you go to/from 255 bytes in character lenght, this > requires a datatype change from VARCHAR to TEXT, which will trigger the > operation. > > For all practical purposes, it should be impossible for a person using the > normal user clients to cause this. > > You would also need to have Admin or Sub Admin permissions to do anything > like that. > > Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) > > Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > Find these products, and many free tools and utilities, at http://rrr.se. > >> 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"
Re: Remedy Table Recreation
Try changing a character field from < 4000 characters to > 4000 characters (or unlimited) This should change it from a VARCHAR2 to a CLOB and I think that will cause ARS to do the create new table, copy the data, and rename the tables operation. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Monday, February 27, 2012 12:04 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation Thanks Misi. I'm trying to identify a specific case on Oracle. I don't think this exists any more (as it used to), but I need to try and confirm. Hoping I get lucky and find someone who has run into this. Axton Grams -Original Message- On Mon, Feb 27, 2012 at 11:32 AM, Misi Mladoniczky wrote: > Hi, > > It depends on the database, where different vendors allow for different > changes with ALTER TABLE. > > I think that if you go to/from 255 bytes in character lenght, this > requires a datatype change from VARCHAR to TEXT, which will trigger the > operation. > > For all practical purposes, it should be impossible for a person using the > normal user clients to cause this. > > You would also need to have Admin or Sub Admin permissions to do anything > like that. > > Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) > > Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > Find these products, and many free tools and utilities, at http://rrr.se. > >> 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"
Re: Remedy Table Recreation
Close. It creates a temp column and not a temp table: ALTER TABLE T3185 ADD (C536870916Z clob NULL) ALTER TABLE T3185 DROP COLUMN C536870916 ALTER TABLE T3185 RENAME COLUMN C536870916Z TO C536870916 DROP VIEW ztestsql1 CREATE VIEW ztestsql1 (Request_ID,Submitter,Create_Date,Assigned_To,Last_Modified_By,Modified_Date,Status,Short_Description,Date_Time_Field,Character_Field) AS SELECT T3185.C1,C2,C3,C4,C5,C6,C7,C8,C536870913,C536870916 FROM T3185 DROP VIEW SH_ztestsql1 CREATE VIEW SH_ztestsql1 (Request_ID,New_TIME,New_USER,Assigned_TIME,Assigned_USER,Fixed_TIME,Fixed_USER,Rejected_TIME,Rejected_USER,Closed_TIME,Closed_USER) AS SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H3185 On Mon, Feb 27, 2012 at 12:10 PM, Grooms, Frederick W wrote: > Try changing a character field from < 4000 characters to > 4000 characters > (or unlimited) > > This should change it from a VARCHAR2 to a CLOB and I think that will cause > ARS to do the create new table, copy the data, and rename the tables > operation. > > Fred > > > -Original Message- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton > Sent: Monday, February 27, 2012 12:04 PM > To: arslist@ARSLIST.ORG > Subject: Re: Remedy Table Recreation > > Thanks Misi. I'm trying to identify a specific case on Oracle. I > don't think this exists any more (as it used to), but I need to try > and confirm. Hoping I get lucky and find someone who has run into > this. > > Axton Grams > > -Original Message- > On Mon, Feb 27, 2012 at 11:32 AM, Misi Mladoniczky wrote: >> Hi, >> >> It depends on the database, where different vendors allow for different >> changes with ALTER TABLE. >> >> I think that if you go to/from 255 bytes in character lenght, this >> requires a datatype change from VARCHAR to TEXT, which will trigger the >> operation. >> >> For all practical purposes, it should be impossible for a person using the >> normal user clients to cause this. >> >> You would also need to have Admin or Sub Admin permissions to do anything >> like that. >> >> Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) >> >> Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): >> * RRR|License - Not enough Remedy licenses? Save money by optimizing. >> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. >> Find these products, and many free tools and utilities, at http://rrr.se. >> >>> 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"
Re: Remedy Table Recreation
I seem to remember years ago that databases would drop a table and rebuild it if you deleted a field but I understand that modern databases don't need to do this. Misi, adding or deleting a CMDB attribute will cause fields to be added to and deleted from tables. Out-of-the-box you'd need admin permissions to do this but a customer could have mapped the CMDB roles to non-admin staff. Cheers Peter -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: 27 February 2012 17:33 To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation Hi, It depends on the database, where different vendors allow for different changes with ALTER TABLE. I think that if you go to/from 255 bytes in character lenght, this requires a datatype change from VARCHAR to TEXT, which will trigger the operation. For all practical purposes, it should be impossible for a person using the normal user clients to cause this. You would also need to have Admin or Sub Admin permissions to do anything like that. Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. > 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"
Re: Remedy Table Recreation
Have you checked the Database Reference Guide for your version? There's a section in there that details when/how the tables are modified. Mark -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: 27 February 2012 18:20 To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation Close. It creates a temp column and not a temp table: ALTER TABLE T3185 ADD (C536870916Z clob NULL) ALTER TABLE T3185 DROP COLUMN C536870916 ALTER TABLE T3185 RENAME COLUMN C536870916Z TO C536870916 DROP VIEW ztestsql1 CREATE VIEW ztestsql1 (Request_ID,Submitter,Create_Date,Assigned_To,Last_Modified_By,Modified_Date,Status,Short_Description,Date_Time_Field,Character_Field) AS SELECT T3185.C1,C2,C3,C4,C5,C6,C7,C8,C536870913,C536870916 FROM T3185 DROP VIEW SH_ztestsql1 CREATE VIEW SH_ztestsql1 (Request_ID,New_TIME,New_USER,Assigned_TIME,Assigned_USER,Fixed_TIME,Fixed_USER,Rejected_TIME,Rejected_USER,Closed_TIME,Closed_USER) AS SELECT entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4 FROM H3185 On Mon, Feb 27, 2012 at 12:10 PM, Grooms, Frederick W wrote: > Try changing a character field from < 4000 characters to > 4000 > characters (or unlimited) > > This should change it from a VARCHAR2 to a CLOB and I think that will cause > ARS to do the create new table, copy the data, and rename the tables > operation. > > Fred > > > -Original Message- > From: Action Request System discussion list(ARSList) > [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton > Sent: Monday, February 27, 2012 12:04 PM > To: arslist@ARSLIST.ORG > Subject: Re: Remedy Table Recreation > > Thanks Misi. I'm trying to identify a specific case on Oracle. I > don't think this exists any more (as it used to), but I need to try > and confirm. Hoping I get lucky and find someone who has run into > this. > > Axton Grams > > -Original Message- > On Mon, Feb 27, 2012 at 11:32 AM, Misi Mladoniczky wrote: >> Hi, >> >> It depends on the database, where different vendors allow for >> different changes with ALTER TABLE. >> >> I think that if you go to/from 255 bytes in character lenght, this >> requires a datatype change from VARCHAR to TEXT, which will trigger >> the operation. >> >> For all practical purposes, it should be impossible for a person >> using the normal user clients to cause this. >> >> You would also need to have Admin or Sub Admin permissions to do >> anything like that. >> >> Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP >> 2011) >> >> Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): >> * RRR|License - Not enough Remedy licenses? Save money by optimizing. >> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. >> Find these products, and many free tools and utilities, at http://rrr.se. >> >>> 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"
Re: Remedy Table Recreation
I thought DROP COLUMN always existed since the beginning of times - its just that the AR API didn't use it when it could have in many cases in the earlier days.. But then beginning of times for me was Oracle 7.x and greater and MS-SQL 6.x (6.5 I think) and greater, so am in no position to confirm what I just claimed for anything below those versions.. Joe -Original Message- From: Peter Romain Sent: Monday, February 27, 2012 1:21 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation I seem to remember years ago that databases would drop a table and rebuild it if you deleted a field but I understand that modern databases don't need to do this. Misi, adding or deleting a CMDB attribute will cause fields to be added to and deleted from tables. Out-of-the-box you'd need admin permissions to do this but a customer could have mapped the CMDB roles to non-admin staff. Cheers Peter -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: 27 February 2012 17:33 To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation Hi, It depends on the database, where different vendors allow for different changes with ALTER TABLE. I think that if you go to/from 255 bytes in character lenght, this requires a datatype change from VARCHAR to TEXT, which will trigger the operation. For all practical purposes, it should be impossible for a person using the normal user clients to cause this. You would also need to have Admin or Sub Admin permissions to do anything like that. Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. 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"
Re: Remedy Table Recreation
I don't think it was for Oracle or MS-SQL that Remedy used the table recreate method. It was for one of the other databases and Remedy used the same commands for all (to make cross platform development and support the same everywhere). It may even go back as far as when Remedy used to support flat files as a database. What matters is that it doesn't appear to be used anymore Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: Monday, February 27, 2012 1:28 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation I thought DROP COLUMN always existed since the beginning of times - its just that the AR API didn't use it when it could have in many cases in the earlier days.. But then beginning of times for me was Oracle 7.x and greater and MS-SQL 6.x (6.5 I think) and greater, so am in no position to confirm what I just claimed for anything below those versions.. Joe -Original Message- From: Peter Romain Sent: Monday, February 27, 2012 1:21 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation I seem to remember years ago that databases would drop a table and rebuild it if you deleted a field but I understand that modern databases don't need to do this. Misi, adding or deleting a CMDB attribute will cause fields to be added to and deleted from tables. Out-of-the-box you'd need admin permissions to do this but a customer could have mapped the CMDB roles to non-admin staff. Cheers Peter -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: 27 February 2012 17:33 To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation Hi, It depends on the database, where different vendors allow for different changes with ALTER TABLE. I think that if you go to/from 255 bytes in character lenght, this requires a datatype change from VARCHAR to TEXT, which will trigger the operation. For all practical purposes, it should be impossible for a person using the normal user clients to cause this. You would also need to have Admin or Sub Admin permissions to do anything like that. Best Regards - Misi, RRR AB, http://www.rrr.se (ARSList MVP 2011) Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10/11): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. > 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"
Re: Remedy Table Recreation
Axton, Well, I can say that the 7.6.04 release for Oracle, MS SQL, and Sybase databases does not drop and recreate any table at the DB level for any operation. Whether that operation is to resize a column, add a column, delete a column, change the constraints on a column, whatever you may do to a form. We do not drop and recreate a table. If you delete a form, we of course drop the DB table (but don't recreate it). If you drop an attachment field, we drop the DB table. If you turn off status history, we stop using the DB table (H table) but do not drop it When you delete a field or change length from less than 8000 bytes to more than 8000 bytes we may delete a column and create a new one or create a new table. BUT, we don't drop a table and recreate it in the product. Now, did we in the past? Yes, there were operations - like deleting a column - that were not supported by databases back in 1990 when we first coded things. However, as the DBs added support for these things, we updated logic to use the new commands and stop recreating the tables. An engineer checked the code to make sure we did not drop table for any operation other than deleting the form in the 7.6.04 code line. I don't believe there is any change for the listed databases between 7.5 and 7.6.04 in this area but I didn't have them check the 7.5 code line specifically. So, I have to go with I don't believe there is any issue with 7.5 or later vs. I have confirmed there is not an issue for 7.6.04 or later. NOTE: You will noticed that I did not list DB2 in the database list above. When DB2 added the DROP COLUMN operation, the code was not updated to take advantage of it -- an oversight. So, deleting a column in DB2 does cause the table to be rebuilt and the original deleted. This oversight has been corrected now in the code line and DB2 will join the list of databases were we do not drop and recreate the table under any conditions in a future release. But, this is a situation unique to DB2 environments. I hope this helps, 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" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
Re: Remedy Table Recreation
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" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
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...) / 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 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 nam
Re: Remedy Table Recreation
Axton, I just looked at my 7.6.4 oracle database and all 3 of those tables has a column called RECORDID, although that column is not indexed on any of them. Primary key constraints can also be added after table creation with the alter statement (and they can have the same name as the unique index) ALTER TABLE B1 ADD CONSTRAINT IB1 PRIMARY KEY (C1) ENABLE VALIDATE; Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Monday, February 27, 2012 3: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...) / 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 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 >
Re: Remedy Table Recreation
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 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...) > / > > 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 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 >>
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...) / 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 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 > t
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 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...) > / > > This is great for storage parameters, partitioning, etc., but not so > good for constraints. It also sounds like the ardb.conf is obsolete,
Re: Remedy Table Recreation
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...) / 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 throug
Re: Remedy Table Recreation
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 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 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 su
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. 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 > > > > 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 > > 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 >
Re: Remedy Table Recreation
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 prima
Re: Remedy Table Recreation
CREATE or REPLACE is available only in Oracle to the best of my knowledge. I haven't tried it on MS-SQL. The AR System however does not use CREATE or REPLACE even if the underlying DB is Oracle. It just does a DROP VIEW and then a CREATE. Joe -Original Message- From: Grooms, Frederick W Sent: Tuesday, March 06, 2012 10:02 AM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: Remedy Table Recreation 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
