Enterprise Replication - Primary Key

Hi everybody, I am in charge of administering two AIX 4.3.3/RS6000 IBM Servers with Informix 7.31 running on each one. After one week of research and night work I got my two servers configured for Enterprise Replication (tmp-ats and tmp-fis filesystems, sqlhost group definition, Interfaces connected with crossover cable, send and receive dbspaces, cdr define, start, stop, resume scripts, /etc/hosts file well defined). When I finally decided to synchronize the databases and execute the cdr define commands i got the message : "command failed -- table does not contain primary key (18)" on every table. Does the ER needs the primary keys for each table to be replicated? What can i do to avoid to define primary keys on my tables? I know that primary keys are required for having a good database design and performance and I know that primary keys are very very important, but i am not in charge of the software application that runs on my server, I am just the DBA, so the application administrator refuse to define primary keys and the effect of the primary keys on the software-application could be very bad ( What a great software right? ), at least in a short period of time. What options do i have? I think that HDR could be an option, does HDR require primary keys? (I don't need to replicate the whole databases, but if i have no choice i could do it) thanks in advance regards sending to informix-list

ER requires a primary key to identify the row on the different nodes within the replication domain. "Francisco Roldan" <froldan@5b.com.gt> wrote in message news:bgpboh$5mq$1@terabinaries.xmission.com... > > Hi everybody, > I am in charge of administering two AIX 4.3.3/RS6000 IBM Servers > with Informix 7.31 running on each one. > After one week of research and night work > I got my two servers configured for Enterprise > Replication (tmp-ats and tmp-fis filesystems, sqlhost group definition, > Interfaces connected with crossover cable, send and receive dbspaces, > cdr define, start, stop, resume scripts, /etc/hosts file well defined). > > When I finally decided to synchronize the databases and execute the > cdr define commands i got the message : > > "command failed -- table does not contain primary key (18)" > > on every table. > > Does the ER needs the primary keys for each table to be replicated? > What can i do to avoid to define primary keys on my tables? > > I know that primary keys are required for having a good database design > and performance and I know that primary keys are very very important, but i > am not in charge of the software application that runs on my server, I am > just the DBA, so the application administrator refuse to define primary keys > and the effect of the primary keys on the software-application could be very > bad ( What a great software right? ), at least in a short period of time. > > What options do i have? > > I think that HDR could be an option, > does HDR require primary keys? > (I don't need to replicate the whole databases, but if i have no choice > i could do it) > > thanks in advance > > regards > > sending to informix-list

If all you are wanting to do is to set up replication for availability purposes, then HDR might be a better choice. It does not require a primary key. "Francisco Roldan" <froldan@5b.com.gt> wrote in message news:bgpboh$5mq$1@terabinaries.xmission.com... > > Hi everybody, > I am in charge of administering two AIX 4.3.3/RS6000 IBM Servers > with Informix 7.31 running on each one. > After one week of research and night work > I got my two servers configured for Enterprise > Replication (tmp-ats and tmp-fis filesystems, sqlhost group definition, > Interfaces connected with crossover cable, send and receive dbspaces, > cdr define, start, stop, resume scripts, /etc/hosts file well defined). > > When I finally decided to synchronize the databases and execute the > cdr define commands i got the message : > > "command failed -- table does not contain primary key (18)" > > on every table. > > Does the ER needs the primary keys for each table to be replicated? > What can i do to avoid to define primary keys on my tables? > > I know that primary keys are required for having a good database design > and performance and I know that primary keys are very very important, but i > am not in charge of the software application that runs on my server, I am > just the DBA, so the application administrator refuse to define primary keys > and the effect of the primary keys on the software-application could be very > bad ( What a great software right? ), at least in a short period of time. > > What options do i have? > > I think that HDR could be an option, > does HDR require primary keys? > (I don't need to replicate the whole databases, but if i have no choice > i could do it) > > thanks in advance > > regards > > sending to informix-list

Francisco Roldan wrote: > [SNIP] but i am not in charge of the software application that > runs on my server, I am just the DBA, so the application > administrator refuse to define primary keys and the effect of the > primary keys on the software-application could be very bad ( What > a great software right? ), at least in a short period of time. This adminstrator needs to know that IF they have a unique index on a table, then they already have a primary key. The only difference is that a PRIMARY KEY definition also implicitly adds (in effect) a NOT NULL clause to all fields of the primary key. However, "proper design" rarely allows NULL fields in the key so it's highly likely that the data already satisfies this rule. Therefore: 1) If a table contains a unique index AND all the fields of the unique index are also NOT NULL then it immediately qualifies for a primary key definition on the same fields, and the performance will not be affected AT ALL. Nor will a new index be created, since the PK will overlay itself on the unique index. 2) If a table contains a unique index but there are fields in this index that are not defined as NOT NULL, you can test these fields by counting how many NULLS actually exist in the data. Often the lack of a NOT NULL clause on one of these fields is just an oversight. If you do find NULLs, beware that if the column definition is not "strong" enough, you might actually be looking at a garbage row of data that has come from a program failure; coupled with the database not being strong enough to reject this rubbish row because of the failure to define NOT NULL on the proper columns! If so, you will be simultaneously cleaning up your database, thereby improving the quality of the data, and you will also be making the database more capable of rejecting bad data in the future. You will also be able to push the table into class 1 above. 3) If there are no UNIQUE indexes on the table, examine the existing indexes to see if the designer / implementor has committed another oversight by not realising that the index should be UNIQUE. You only need to perform select count(*) from mytable group by A, B, C having count(*) > 1 Nothing comes out, then the index is in fact covering a unique set of fields. You can and must confirm this using human intelligence to consider the data and confirm according to the business rules that these columns are indeed unique. If true, you can promote the index to UNIQUE, then follow 2 and 1 above. Following steps 1, 2, 3 above will not affect performance, query paths or locking contention. Therefore steps 1, 2, and 3 are safe reliable actions to take immediately (really, as your analysis time permits) After this, things get a little uglier, but not too much. 4) any tables remaining will either have no indexes or no indexes covering unique data. Perhaps there are some fields out there that could be uniquely indexed? It's a fact of real-life that virtually all tables in an OLTP system (note - I'm excluding a warehouse and possibly a crude data logging system) will have some uniqueness. A table is virtually useless or clumsily and carelessly designed if it doesn't. Well, careless design is out there, so you could be unlucky. Assuming there's no careless design, you can go looking for uniqueness candidates. First-off, a serial field is a very strong candiate. Otherwise, think about the table, apply the proper principles and decide which fields are likely to be the PK. Test the theory with the SQL above. When you discover the PK for each remaining table, *don't* rush in and add an index! Any real change to the schema may affect a process! Usually, adding a unique index will be an improvement for the better, but it probably will change query plans (once again, usually for the better) but you need to think about it table by table. Adding an index will nearly always reduce locking contention too, but once again, that's a 99.9% likelihood, not a guarantee. Apply intelligent, experienced thought. If you or the other designer do not know the principles, then read about it and/or ask. None of this discussion has anything to do with foreign keys. Sometimes, when you talk to people about primary keys they can't help assuming you are also talking about foreign keys and relationships. This is not necessary either for your schema or for replication. The way Informix FK's are implemented, there can be a performance cost associated. For some tables and some activities, this cost can be very high indeed. Therefore reassure your collegue that no FK's are required and will not happen from this project. As Madison pointed out, you need to know why you are replicating. If you are replicating so that the 2nd machine can be an active SQL engine in it's own right, then HDR will not allow this. The most you can do with HDR is query-only; you need a non-logged temp space in the engine even to do anything that requires temp tables to solve the query. If you want to replicate some tables "to make sure they are safe" then you really need to look at your backup ritual. That's the #1 choice for data protection. If you want to replicate some tables to ensure they are 100% available in the event of a machine failure, then you probably need HDR and you also need to make sure you can switch over the users to the secondary quickly enough to keep the trains running. If you want to replicate for any other reason (eg logical or business reasons), then you probably do need ER which means you need PK's.