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 doug_muel...@bmc.com 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=contentid=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
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 radhika.naraya...@logica.com 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 doug_muel...@bmc.com 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=contentid=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
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=contentid=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
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=contentid=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
Re: Remedy Table Recreation
@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 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
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=contentid=KA305401 Which, at the end, states: In essence this is not something that is currently available in AR System, the only primary keys added was for Sql Server and Sybase because those were the only two databases that needed primary keys for replication. The thought was that other databases don't need to have primary keys and they can be replicated just with the presence of unique indexes. While this appears to be true today, this was no always the case. This is still not true for some of the meta-data tables because they have neither a unique index or a primary key constraint (e.g., actlink_set_char, actlink_set, filter_notify). While replication can still work on these tables, it forces the database to perform a table scan for each replicated record, comparing the value of every column. The primary key can be defined at the time the table is created or after the table is created and is different from a unique index. Take this DDL from a stock system for table B1: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0)) / create unique index ib1 on b1 (c1) / While this table has a unique constraint through index ib1, it does not have a primary key defined. Compare this to the DDL for the same table with the primary key constraint: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0), constraint sys_pk_3933 primary key (c1) / create unique index ib1 on b1 (c1) / We looked to add this to the ardb.conf at the time a form is created, but that is a no go because the ardb.conf entries only append a suffix to the create table statements, at this position: create table X (columns...) ardb.conf clause goes here / This is great for storage parameters, partitioning, etc., but not so good for constraints. It also sounds like the ardb.conf is obsolete, for all intents and purposes, since Remedy no longer drops/recreates the underlying tables, thus making manual modifications to the underlying tables characteristics persistent (safer). Thanks for taking the time to look through the code and send this information. It is immensely useful and gives us the information we need to make the correct decision on going forward. Regards, Axton Grams On Mon, Feb 27, 2012 at 2:48 PM, Mueller, Doug doug_muel...@bmc.com wrote: Axton, By the way On the other topic in your message, definition of a primary key. We found that a number of the replication technologies require a table to have a primary key in order to replicate successfully. Since every one of our tables has
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 doug_muel...@bmc.com 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=contentid=KA305401 Which, at the end, states: In essence this is not something that is currently available in AR System, the only primary keys added was for Sql Server and Sybase because those were the only two databases that needed primary keys for replication. The thought was that other databases don't need to have primary keys and they can be replicated just with the presence of unique indexes. While this appears to be true today, this was no always the case. This is still not true for some of the meta-data tables because they have neither a unique index or a primary key constraint (e.g., actlink_set_char, actlink_set, filter_notify). While replication can still work on these tables, it forces the database to perform a table scan for each replicated record, comparing the value of every column. The primary key can be defined at the time the table is created or after the table is created and is different from a unique index. Take this DDL from a stock system for table B1: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0)) / create unique index ib1 on b1 (c1) / While this table has a unique constraint through index ib1, it does not have a primary key defined. Compare this to the DDL for the same table with the primary key constraint: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0), constraint sys_pk_3933 primary key (c1) / create unique index ib1 on b1 (c1) / We looked to add this to the ardb.conf at the time a form is created, but that is a no go because the ardb.conf entries only append a suffix to the create table statements, at this position: create table X (columns...) ardb.conf clause goes here / This is great for storage parameters, partitioning, etc., but not so good for constraints. It also sounds like the ardb.conf is obsolete, for all intents and purposes, since Remedy no longer drops/recreates the underlying tables, thus making manual modifications to the underlying tables characteristics persistent (safer). Thanks for taking the time to look through the code and send this information. It is immensely useful and gives us the information we need to make the correct decision on going forward. Regards, Axton Grams On Mon, Feb 27, 2012 at 2:48 PM, Mueller, Doug doug_muel
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=contentid=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
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
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 lj.longw...@gmail.com 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 lj.longw...@gmail.com 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 m...@rrr.se 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 m...@rrr.se 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 frederick.w.gro...@xo.com 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 m...@rrr.se 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 frederick.w.gro...@xo.com 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 m...@rrr.se 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=contentid=KA305401 Which, at the end, states: In essence this is not something that is currently available in AR System, the only primary keys added was for Sql Server and Sybase because those were the only two databases that needed primary keys for replication. The thought was that other databases don’t need to have primary keys and they can be replicated just with the presence of unique indexes. While this appears to be true today, this was no always the case. This is still not true for some of the meta-data tables because they have neither a unique index or a primary key constraint (e.g., actlink_set_char, actlink_set, filter_notify). While replication can still work on these tables, it forces the database to perform a table scan for each replicated record, comparing the value of every column. The primary key can be defined at the time the table is created or after the table is created and is different from a unique index. Take this DDL from a stock system for table B1: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0)) / create unique index ib1 on b1 (c1) / While this table has a unique constraint through index ib1, it does not have a primary key defined. Compare this to the DDL for the same table with the primary key constraint: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0), constraint sys_pk_3933 primary key (c1) / create unique index ib1 on b1 (c1) / We looked to add this to the ardb.conf at the time a form is created, but that is a no go because the ardb.conf entries only append a suffix to the create table statements, at this position: create table X (columns...) ardb.conf clause goes here / This is great for storage parameters, partitioning, etc., but not so good for constraints. It also sounds like the ardb.conf is obsolete, for all intents and purposes, since Remedy no longer drops/recreates the underlying tables, thus making manual modifications to the underlying tables characteristics persistent (safer). Thanks for taking the time to look through the code and send this information. It is immensely useful and gives us the information we need to make the correct decision on going forward. Regards, Axton Grams On Mon, Feb 27, 2012 at 2:48 PM, Mueller, Doug doug_muel...@bmc.com wrote: Axton, By the way On the other topic in your message, definition of a primary key. We found that a number of the replication technologies require a table to have a primary key in order to replicate successfully. Since every one of our tables has a primary key -- the entry ID -- and we can define combinations of fields for other forms -- like status history -- that are unique, we have added a primary key to every metadata table and all the data tables for things we install or create going forward. I don't think we retrofit the DB to add them to existing installations. This work was done in the 7.5 or so timeframe (before, at, or after I am not sure, but in the range of the three releases just before, at, or after 7.5). No issue if you want to create something else as your primary key, but just be aware of the fact that the environment does create a primary key for all tables now so that it is present and defined for any environment where DB replication requires this capability. So, you may want to check to see if there is already a primary key defined and maintained for you. Then, you can decide whether this is sufficient or do you need to do something different for your situation. Just be clear that you may need to undo the primary key specification in your work as we should already be doing one. This was added in that range of just before, at, or just after the 7.5 release. If before or at, you have already seen/addressed this situation. If after, just be aware that this is coming and may have some impact on you in the future. Doug Mueller -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Axton Sent: Monday, February 27, 2012 7:53 AM To: arslist@ARSLIST.ORG Subject: Remedy Table Recreation First, some background information: It used to be the case that certain operations would trigger Remedy to recreate a database table: - rename existing table - create new table with the original name
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=contentid=KA305401 Which, at the end, states: In essence this is not something that is currently available in AR System, the only primary keys added was for Sql Server and Sybase because those were the only two databases that needed primary keys for replication. The thought was that other databases don't need to have primary keys and they can be replicated just with the presence of unique indexes. While this appears to be true today, this was no always the case. This is still not true for some of the meta-data tables because they have neither a unique index or a primary key constraint (e.g., actlink_set_char, actlink_set, filter_notify). While replication can still work on these tables, it forces the database to perform a table scan for each replicated record, comparing the value of every column. The primary key can be defined at the time the table is created or after the table is created and is different from a unique index. Take this DDL from a stock system for table B1: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0)) / create unique index ib1 on b1 (c1) / While this table has a unique constraint through index ib1, it does not have a primary key defined. Compare this to the DDL for the same table with the primary key constraint: create table b1 ( c1 varchar2(15 byte) not null enable, c158 varchar2(255 byte), co158 number(15,0), cc158 number(15,0), constraint sys_pk_3933 primary key (c1) / create unique index ib1 on b1 (c1) / We looked to add this to the ardb.conf at the time a form is created, but that is a no go because the ardb.conf entries only append a suffix to the create table statements, at this position: create table X (columns...) ardb.conf clause goes here / This is great for storage parameters, partitioning, etc., but not so good for constraints. It also sounds like the ardb.conf is obsolete, for all intents and purposes, since Remedy no longer drops/recreates the underlying tables, thus making manual modifications to the underlying tables characteristics persistent (safer). Thanks for taking the time to look through the code and send this information. It is immensely useful and gives us the information we need to make the correct decision on going forward. Regards, Axton Grams On Mon, Feb 27, 2012 at 2:48 PM, Mueller, Doug doug_muel...@bmc.com wrote: Axton, By the way On the other topic in your message, definition of a primary key. We found that a number of the replication technologies require a table to have a primary key in order to replicate successfully. Since every one of our tables has a primary key -- the entry ID -- and we can define combinations of fields for other forms -- like status history -- that are unique, we have added a primary key to every metadata table and all the data tables for things we install or create going forward. I don't think we retrofit the DB to add them to existing installations. This work was done in the 7.5 or so timeframe (before, at, or after I am not sure, but in the range of the three releases just before, at, or after 7.5). No issue if you want to create something else as your primary key, but just be aware of the fact that the environment does create a primary key for all tables now so that it is present and defined for any environment where DB replication requires this capability. So, you may want to check to see if there is already a primary key defined and maintained for you. Then, you can decide whether this is sufficient or do you need to do something different for your situation. Just be clear that you may need to undo the primary key specification in your work as we should already be doing one. This was added in that range of just before, at, or just after the 7.5 release
Remedy Table
Hi, Which Remedy table has the Support Organization, Support Group, and the Location? ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Remedy Table
Koyb,
That information is stored in the ARSchema table within the database,
specifically the SchemaID column. The schema ID's are generated during
the install and my vary by implementation.
If you're using SQL, you can do something like:
Select Name, SchemaID from ARSchema
Where Name IN ('CTM_People_Organization', 'CTM_Support_Group', '
SIT_Site')
Then just prepend a 'T' to the schema ID and you're set.
Cheers!
Eli
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] On Behalf Of Koyb P. Liabt
Sent: Wednesday, December 08, 2010 9:00 AM
To: arslist@ARSLIST.ORG
Subject: Remedy Table
**
Hi,
Which Remedy table has the Support Organization, Support Group, and the
Location?
_attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
Yippee :jumping: People who know more about this than l do! Thanks for offer l will not ask just now, as I'm just trying to finish a report l've been working for 4 weeks now %-| However to use a famous quote 'I'll be back!' Thanks again... -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20682374.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
Neil, I do all of our Remedy reports in SSRS. If you use the views, you should be able to query off of HPD:Help Desk and set the group parameter on the report to the assigned support group field, then do a count for each group. The assignment log form will show you all assignments, both past and present. You'll want to look at the help desk form for the current ones, unless you want to add some data parameters to the report if you stick with the assignment log. HTH, -Matt Matthew C. Gayford Application Developer Remedy Administrator University of North Carolina Wilmington (910) 962-7177 -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of npeterson Sent: Sunday, November 23, 2008 5:32 PM To: arslist@ARSLIST.ORG Subject: Re: SQL Reporing Services - making sense of Remedy Table structure Thanks both of you - this advice had gotten me almost to where I need to be. One wierd thing I've noticed is that in the HPD_HelpDeskAssignmentLogJoin table (the table I believe has what I need in it) , there are two rows for each submitted incident. The difference is on the Request_ID - one will be something like INC5(HDA565) and the second will be something like INC5(HDA249) This is causing all of my reports to some out double. I've tried to filter this down using fields like actual duration second and while it did get the numbers down thay were not acurate. Has any had to deal with something like this before or have any advice? Thanks neilp Drew Shuller wrote: npeterson, query the views instead. Or look for the Database Guide in the documentation. Drew ERCOT I am hoping to user SQL Reporting services to create some Remedy reports, however I can not make one bit of sense of the tables in this Database. We are using Remedy IT Service Management 7.0 - DB is on SQL server 2005. A sample report would just be count all tickets currently assigned to a specific support group. I can not find any reference to support groups, open incidents, support personnel, etc. Is there some secret or guide on what data is stored where? For the record I am not a remedy guy, I just happen to admin the system and have been given some reporting requirement that can not be met with the integrated reporting tools. Thanks -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20626113.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20651835.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
Welcome to the joy of reporting from Remedy :- There is database documentation, IMHO its as good as some m/soft documentation l have found. If anyone understands it please let me know! Well done for using the tables, the OBDC link that is supplied is unless to say the least, in particular for advance reporting esp enterprise reporting. Some banana skins, to be aware of Date fields are epoch dates e.g. number of seconds after 01/01/1970 00:00:00 this can be solved using TSQL functions. On the forms you will see a pull down list, the values stored in the field, are a numeric value. However you can use a function, to look up the value. I have TSQL functions for the above done in SQL 2005 if you want. Some of the look up values between the tables are not obvious to the say the least, the HPD_Help_Desk form and the SLA form springs to mind. I do not have the details to hand at the moment as I'm at home. This is particular ITSM, where a number of the relationships as based on GUID number's. I'm sure its in the system somewhere however l have not found it yet. Nor does there seem to be any central point of information, be that website or document. When a form is created, a corresponding view with the same name is created. So you will find a view called HPD_Help_Desk, which corresponds to the form of the same name. From what l can see of your reporting requirements, l agree with Matthew Gayford. My job is reporting, at the moment l report from 3 versions of Remedy. As l say to date whilst l have resolved a number of challenges, there are gaps in my knowledge which l would like to plug. We also looking to use SSRS as some point future to provide report for our customers. Regards Robert French -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20668739.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
One slight correction, Date/Time fields are Unix epoch of 1/1/1970. Date fields have a different epoch -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of RFrench Sent: Monday, November 24, 2008 1:08 PM To: arslist@ARSLIST.ORG Subject: Re: SQL Reporing Services - making sense of Remedy Table structure Welcome to the joy of reporting from Remedy :- There is database documentation, IMHO its as good as some m/soft documentation l have found. If anyone understands it please let me know! Well done for using the tables, the OBDC link that is supplied is unless to say the least, in particular for advance reporting esp enterprise reporting. Some banana skins, to be aware of Date fields are epoch dates e.g. number of seconds after 01/01/1970 00:00:00 this can be solved using TSQL functions. On the forms you will see a pull down list, the values stored in the field, are a numeric value. However you can use a function, to look up the value. I have TSQL functions for the above done in SQL 2005 if you want. Some of the look up values between the tables are not obvious to the say the least, the HPD_Help_Desk form and the SLA form springs to mind. I do not have the details to hand at the moment as I'm at home. This is particular ITSM, where a number of the relationships as based on GUID number's. I'm sure its in the system somewhere however l have not found it yet. Nor does there seem to be any central point of information, be that website or document. When a form is created, a corresponding view with the same name is created. So you will find a view called HPD_Help_Desk, which corresponds to the form of the same name. From what l can see of your reporting requirements, l agree with Matthew Gayford. My job is reporting, at the moment l report from 3 versions of Remedy. As l say to date whilst l have resolved a number of challenges, there are gaps in my knowledge which l would like to plug. We also looking to use SSRS as some point future to provide report for our customers. Regards Robert French -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-s tructure-tp20626113p20668739.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
Curious what is it then ? -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20670813.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
January 1, 4713, BC. Jennifer Meyer -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of RFrench Sent: Monday, November 24, 2008 5:03 PM To: arslist@ARSLIST.ORG Subject: Re: SQL Reporing Services - making sense of Remedy Table structure Curious what is it then ? -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20670813.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
Thanks for that, for me personally speaking it raises another issue where %*% is all this information. I have only been in my role for about 8 months, l have reached the conculsion that l am missing something. There is obviously plenty of information and experience regarding the backend database, and some other issue, but finding it is another issue althogher. My experience so far, is find one bit of information here then another bit somewhere else, yet very little is documented, in any meaning form. Sorry for the rant Remedy is an excellent product however even in my short experience there are things missing. Not least of all documenation. Guess now l have managed to start posting l might be asking some more questions =) -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20672036.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
RFrench, You will find that the people in this forum likely have most, if not all of the information that you are looking for. If you provide the details of your environment, and what you are looking for, where you have tried, and maybe even where you looked to try to find the right informationthis forum will likely bridge the gap of your inexperience and bring you up to speed fairly quicklynow...to provide a bit of information to answer your question...even though I'm sure it's not what you meanthere you go. The information is stored mainly in the T tables, the H tables are used strictly for Status History storage (unless you have that turned off that is)...and the B tables store the binary data associated with the records in T if you happen to have attachment fields on that form. :) -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of RFrench Sent: Monday, November 24, 2008 4:12 PM To: arslist@ARSLIST.ORG Subject: Re: SQL Reporing Services - making sense of Remedy Table structure Thanks for that, for me personally speaking it raises another issue where %*% is all this information. I have only been in my role for about 8 months, l have reached the conculsion that l am missing something. There is obviously plenty of information and experience regarding the backend database, and some other issue, but finding it is another issue althogher. My experience so far, is find one bit of information here then another bit somewhere else, yet very little is documented, in any meaning form. Sorry for the rant Remedy is an excellent product however even in my short experience there are things missing. Not least of all documenation. Guess now l have managed to start posting l might be asking some more questions =) -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-s tructure-tp20626113p20672036.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
Thanks both of you - this advice had gotten me almost to where I need to be. One wierd thing I've noticed is that in the HPD_HelpDeskAssignmentLogJoin table (the table I believe has what I need in it) , there are two rows for each submitted incident. The difference is on the Request_ID - one will be something like INC5(HDA565) and the second will be something like INC5(HDA249) This is causing all of my reports to some out double. I've tried to filter this down using fields like actual duration second and while it did get the numbers down thay were not acurate. Has any had to deal with something like this before or have any advice? Thanks neilp Drew Shuller wrote: npeterson, query the views instead. Or look for the Database Guide in the documentation. Drew ERCOT I am hoping to user SQL Reporting services to create some Remedy reports, however I can not make one bit of sense of the tables in this Database. We are using Remedy IT Service Management 7.0 - DB is on SQL server 2005. A sample report would just be count all tickets currently assigned to a specific support group. I can not find any reference to support groups, open incidents, support personnel, etc. Is there some secret or guide on what data is stored where? For the record I am not a remedy guy, I just happen to admin the system and have been given some reporting requirement that can not be met with the integrated reporting tools. Thanks -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20626113.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20651835.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
SQL Reporing Services - making sense of Remedy Table structure
I am hoping to user SQL Reporting services to create some Remedy reports, however I can not make one bit of sense of the tables in this Database. We are using Remedy IT Service Management 7.0 - DB is on SQL server 2005. A sample report would just be count all tickets currently assigned to a specific support group. I can not find any reference to support groups, open incidents, support personnel, etc. Is there some secret or guide on what data is stored where? For the record I am not a remedy guy, I just happen to admin the system and have been given some reporting requirement that can not be met with the integrated reporting tools. Thanks -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20626113.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
npeterson, query the views instead. Or look for the Database Guide in the documentation. Drew ERCOT I am hoping to user SQL Reporting services to create some Remedy reports, however I can not make one bit of sense of the tables in this Database. We are using Remedy IT Service Management 7.0 - DB is on SQL server 2005. A sample report would just be count all tickets currently assigned to a specific support group. I can not find any reference to support groups, open incidents, support personnel, etc. Is there some secret or guide on what data is stored where? For the record I am not a remedy guy, I just happen to admin the system and have been given some reporting requirement that can not be met with the integrated reporting tools. Thanks -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20626113.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: SQL Reporing Services - making sense of Remedy Table structure
I haven't used SSRS much, but I do recall that it is more difficult to report off of applications like Remedy than .NET applications. The reason being that Remedy doesn't store foreign keys and such to relate to other tables in the DBMS, rather storing relationships like that in the application itself. As a result, it will be harder for you to pull in related tables to get all the data you need. So the advice given to you by Drew is spot on -- use the Views, however, without knowing what you're looking for, you are going to have difficulty. For example, without being a remedy guy you probably won't know that Incidents are stored in HPD_HelpDesk. You probably want to engage a person who does have more knowledge of ITSM to help you find the right places to pull data. Shawn Pierson -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of npeterson Sent: Friday, November 21, 2008 11:35 AM To: arslist@ARSLIST.ORG Subject: SQL Reporing Services - making sense of Remedy Table structure I am hoping to user SQL Reporting services to create some Remedy reports, however I can not make one bit of sense of the tables in this Database. We are using Remedy IT Service Management 7.0 - DB is on SQL server 2005. A sample report would just be count all tickets currently assigned to a specific support group. I can not find any reference to support groups, open incidents, support personnel, etc. Is there some secret or guide on what data is stored where? For the record I am not a remedy guy, I just happen to admin the system and have been given some reporting requirement that can not be met with the integrated reporting tools. Thanks -- View this message in context: http://www.nabble.com/SQL-Reporing-Services---making-sense-of-Remedy-Table-structure-tp20626113p20626113.html Sent from the ARS (Action Request System) mailing list archive at Nabble.com. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are Private and confidential as detailed here: http://www.sug.com/disclaimers/default.htm#Mail . If you cannot access the link, please e-mail sender. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy Table Indexes
Hi, I hate to compare Remedy to a spreadsheet, but that is probably the best way to explain this concept. When Remedy creates a form, the database table for that form has 1 defined index. It is the only clustered index for that form/table. That index is for the Request ID (field id #1) . The Request ID becomes the equivalent of the row number (meaning the physical location of the record in the table) in a spreadsheet like Excel. The clustered index is created without any additional interaction from the developer. Additional indexes can then be added to the form, but in the database, they are non-clustered indexes. Hope this helps, Marc Simmons On 5/21/08, ITSM Support [EMAIL PROTECTED] wrote: *Hi Luksha Timothy,* It's true that Request ID is the only implicitely indexed field in Remedy. But,in form properties,what is the status of Indexes ?Are there any fields indexed?Check that also. There may be work flows related to Direct SQL action or SQL Query in set fields action in which primary key may be configered. Hope this helps... Regards, * Sandeep Vyom Labs Pvt. Ltd. An ISO 2 certified company. Consulting | Outsourcing | Training || BMC Remedy BSM | ITIL Web : www.vyomlabs.com * Luksha, Timothy wrote: I was recently asked to check which columns of one of our remedy forms were indexed. The documentation gave me the impression that only Request Id would be indexed automatically, which makes sense since it is the primary key. Remedy administrator indicated that two other columns had indexes. When I looked at the Oracle table in a database tool I have, there were four indexes listed. The one that wasn't appearing in remedy administrator mapped to the Date Submitted field. Does this simply indicate that someone added the index through Oracle without asking remedy to create it or is something more funny going on? Thanks, Tim ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are -- Marc Simmons Remedy Administrator Everyday above ground is a good day... the rest is a choice! ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy Table Indexes
Since the other posts didn't answer your Q: You are correct. If Remedy doesn't know about an index, it was added through Oracle. Cheers Ben - --- Original Message --- - From: Marc Simmons [EMAIL PROTECTED] To: arslist@ARSLIST.ORG Sent: Wed, 21 May 2008 09:46:38 Hi, snip Luksha, Timothy wrote: I was recently asked to check which columns of one of our remedy forms were indexed. The documentation gave me the impression that only Request Id would be indexed automatically, which makes sense since it is the primary key. Remedy administrator indicated that two other columns had indexes. When I looked at the Oracle table in a database tool I have, there were four indexes listed. The one that wasn't appearing in remedy administrator mapped to the Date Submitted field. Does this simply indicate that someone added the index through Oracle without asking remedy to create it or is something more funny going on? Thanks, Tim ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Remedy Table Indexes
I was recently asked to check which columns of one of our remedy forms were indexed. The documentation gave me the impression that only Request Id would be indexed automatically, which makes sense since it is the primary key. Remedy administrator indicated that two other columns had indexes. When I looked at the Oracle table in a database tool I have, there were four indexes listed. The one that wasn't appearing in remedy administrator mapped to the Date Submitted field. Does this simply indicate that someone added the index through Oracle without asking remedy to create it or is something more funny going on? Thanks, Tim ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy Table Indexes
RequestID is the only automatically indexed field on any form. Your impression on the others makes sense. If that's true, or should I say, until you are sure that it is NOT true, you should not modify your indexes in Remedy Administrator. If they were created in the DB, they need to be maintained there. Rick On Tue, May 20, 2008 at 11:23 AM, Luksha, Timothy [EMAIL PROTECTED] wrote: I was recently asked to check which columns of one of our remedy forms were indexed. The documentation gave me the impression that only Request Id would be indexed automatically, which makes sense since it is the primary key. Remedy administrator indicated that two other columns had indexes. When I looked at the Oracle table in a database tool I have, there were four indexes listed. The one that wasn't appearing in remedy administrator mapped to the Date Submitted field. Does this simply indicate that someone added the index through Oracle without asking remedy to create it or is something more funny going on? Thanks, Tim ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy Table Indexes
*Hi Luksha Timothy,* It's true that Request ID is the only implicitely indexed field in Remedy. But,in form properties,what is the status of Indexes ?Are there any fields indexed?Check that also. There may be work flows related to Direct SQL action or SQL Query in set fields action in which primary key may be configered. Hope this helps... Regards, * Sandeep Vyom Labs Pvt. Ltd. An ISO 2 certified company. Consulting | Outsourcing | Training || BMC Remedy BSM | ITIL Web : www.vyomlabs.com * Luksha, Timothy wrote: I was recently asked to check which columns of one of our remedy forms were indexed. The documentation gave me the impression that only Request Id would be indexed automatically, which makes sense since it is the primary key. Remedy administrator indicated that two other columns had indexes. When I looked at the Oracle table in a database tool I have, there were four indexes listed. The one that wasn't appearing in remedy administrator mapped to the Date Submitted field. Does this simply indicate that someone added the index through Oracle without asking remedy to create it or is something more funny going on? Thanks, Tim ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
