**
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:[email protected]] On Behalf Of Axton
Sent: Wednesday, February 29, 2012 11:57 PM
To: [email protected]
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:[email protected]] On Behalf Of Axton
Sent: Monday, February 27, 2012 1:27 PM
To: [email protected]
Subject: Re: Remedy Table Recreation
We did not see a primary key defined for the T, H, or B tables, or the
meta-data tables, so we created a primary key on those tables. We
did, however, see a unique index on all the T, H, and B tables. We
are rethinking our approach because in later versions of Oracle
Streams they added an option to use a unique index, versus an explicit
primary key, for replication purposes.
I was referred to this KA on my inquiry to support:
https://kb.bmc.com/infocenter/index?page=content&id=KA305401
Which, at the end, states:
"In essence this is not something that is currently available in AR
System, the only primary keys added was for Sql Server and Sybase
because those were the only two databases that needed primary keys for
replication. The thought was that other databases don't need to have
primary keys and they can be replicated just with the presence of
unique indexes."
While this appears to be true today, this was no always the case.
This is still not true for some of the meta-data tables because they
have neither a unique index or a primary key constraint (e.g.,
actlink_set_char, actlink_set, filter_notify). While replication can
still work on these tables, it forces the database to perform a table
scan for each replicated record, comparing the value of every column.
The primary key can be defined at the time the table is created or
after the table is created and is different from a unique index. Take
this DDL from a stock system for table B1:
create table b1 (
c1 varchar2(15 byte) not null enable,
c158 varchar2(255 byte),
co158 number(15,0),
cc158 number(15,0))
/
create unique index ib1 on b1 (c1)
/
While this table has a unique constraint through index ib1, it does
not have a primary key defined. Compare this to the DDL for the same
table with the primary key constraint:
create table b1 (
c1 varchar2(15 byte) not null enable,
c158 varchar2(255 byte),
co158 number(15,0),
cc158 number(15,0),
constraint sys_pk_3933 primary key (c1)
/
create unique index ib1 on b1 (c1)
/
We looked to add this to the ardb.conf at the time a form is created,
but that is a no go because the ardb.conf entries only append a suffix
to the create table statements, at this position:
create table X (columns...) <ardb.conf clause goes here>
/
This is great for storage parameters, partitioning, etc., but not so
good for constraints. It also sounds like the ardb.conf is obsolete,
for all intents and purposes, since Remedy no longer drops/recreates
the underlying tables, thus making manual modifications to the
underlying tables characteristics persistent (safer).
Thanks for taking the time to look through the code and send this
information. It is immensely useful and gives us the information we
need to make the correct decision on going forward.
Regards,
Axton Grams
-----Original Message-----
On Mon, Feb 27, 2012 at 2:48 PM, Mueller, Doug wrote:
Axton,
By the way....
On the other topic in your message, definition of a primary key.
We found that a number of the replication technologies require a table to
have
a primary key in order to replicate successfully. Since every one of our
tables
has a primary key -- the entry ID -- and we can define combinations of
fields
for other forms -- like status history -- that are unique, we have added
a
primary key to every metadata table and all the data tables for things we
install or create going forward. I don't think we retrofit the DB to add
them
to existing installations.
This work was done in the 7.5 or so timeframe (before, at, or after I am
not
sure, but in the range of the three releases just before, at, or after
7.5).
No issue if you want to create something else as your primary key, but
just be
aware of the fact that the environment does create a primary key for all
tables
now so that it is present and defined for any environment where DB
replication
requires this capability.
So, you may want to check to see if there is already a primary key
defined
and
maintained for you. Then, you can decide whether this is sufficient or
do
you
need to do something different for your situation. Just be clear that
you
may
need to undo the primary key specification in your work as we should
already be
doing one. This was added in that range of just before, at, or just
after
the
7.5 release. If before or at, you have already seen/addressed this
situation.
If after, just be aware that this is coming and may have some impact on
you in
the future.
Doug Mueller
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of Axton
Sent: Monday, February 27, 2012 7:53 AM
To: [email protected]
Subject: Remedy Table Recreation
First, some background information:
It used to be the case that certain operations would trigger Remedy to
recreate a database table:
- rename existing table
- create new table with the original name
- copy the data from the renamed table to the new table
- drop the renamed table
I remember altering the precision on a decimal field would trigger
this, and I seem to also remember something with currency fields.
Now for the issue:
We have applied changes to every table in the Remedy database to
define a primary key. This primary key is used for Oracle Streams
replication to a target database. If the table is recreated, the
primary key is dropped, which can cause Streams to choke if the table
contains a large volume of data.
Now for the question:
Does anyone know of an action that a user can perform through the
Remedy clients that will cause a table to be recreated in this manner?
When I say "Remedy Clients" I am referring to Dev Studio, User Tool,
ITSM applications, mid-tier, or the Remedy API.
Relevant Environment Information:
- Oracle 11g
- ARServer 7.5
- Apps 7.5 (ITSM, CMDB, etc.)
Thanks,
Axton Grams
