Unique constraints should be used with replicated
IDS12.10 FC4.
Occasionally saw this,
17:03:35 Warning - Table noaa:informix.ds_head_np
is replicated and contains a unique index
rather than a unique constraint.
17:03:35 Unique constraints should be used with replicated tables
rather than a simple unique index.
Any comments or potential negative?
This table is ER replicated with no warning in old versions when it was
originally defined. Attached table definition.
Thanks
Frank
create table "informix".ds_head_np
(
inventory_id serial not null ,
dataset_name varchar(255,44) not null ,
dataset_size_bytes int8,
datatype_name char(10) not null ,
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second not null ,
orig_data_filenm varchar(255,44) not null ,
distribution_site char(1) not null ,
data_source char(10),
has_visual_file char(1),
restriction_level smallint,
accessible char(1)
default 'Y',
uuid char(40),
primary key (inventory_id) constraint "informix".ds_head_np_pk
) with crcols in dbdata22 extent size 8192 next size 8192 lock mode row;
revoke all on "informix".ds_head_np from "public" as "informix";
create unique index "informix".ds_head_np_idx0 on "informix".ds_head_np
(dataset_name) using btree in dbdw08;
create index "informix".ds_head_np_idx1 on "informix".ds_head_np
(uuid) using btree in dbdata23;
create index "informix".ds_head_np_idx2 on "informix".ds_head_np
(datatype_name) using btree in dbdata01;
create index "informix".ds_head_np_idx3 on "informix".ds_head_np
(datatype_version) using btree in dbdata01;
create index "informix".ds_head_np_idx4 on "informix".ds_head_np
(ingest_dt) using btree in dbdata01;
alter table "informix".ds_head_np add constraint (foreign key
(datatype_name) references "informix".datatypes constraint
"informix".ds_head_np_dtn);
alter table "informix".ds_head_np add constraint (foreign key
(datatype_version) references "informix".datatyp_vers constraint
"informix".ds_head_np_dtv);
--001a114a6a4eab2fc20531df4254
Hi Frank,
you'd rather define a UNIQUE CONSTRAINT in addition to / on top of the=20
sole UNIQUE INDEX that you already have on this table. This would quiesce =
the warning and allow 'deferred constraint checking' (as now there is a=20
constraint to defer) which ER sometimes has to rely on on target side.
Best regards,
Andreas
From: "FRANK" <yunyaoqu@gmail.com>
To: ids@iiug.org
Date: 02.05.2016 19:25
Subject: Unique constraints should be used with replica.... [37071]
Sent by: ids-bounces@iiug.org
IDS12.10 FC4.=20
Occasionally saw this,=20
17:03:35 Warning - Table noaa:informix.ds=5Fhead=5Fnp=20
is replicated and contains a unique index=20
rather than a unique constraint.=20
17:03:35 Unique constraints should be used with replicated tables=20
rather than a simple unique index.=20
Any comments or potential negative?=20
This table is ER replicated with no warning in old versions when it was=20
originally defined. Attached table definition.=20
Thanks=20
Frank=20
create table "informix".ds=5Fhead=5Fnp=20
(=20
inventory=5Fid serial not null ,=20
dataset=5Fname varchar(255,44) not null ,=20
dataset=5Fsize=5Fbytes int8,=20
datatype=5Fname char(10) not null ,=20
datatype=5Fversion char(10),=20
ingest=5Fstatus char(10),=20
ingest=5Fdt datetime year to second not null ,=20
orig=5Fdata=5Ffilenm varchar(255,44) not null ,=20
distribution=5Fsite char(1) not null ,=20
data=5Fsource char(10),=20
has=5Fvisual=5Ffile char(1),=20
restriction=5Flevel smallint,=20
accessible char(1)=20
default 'Y',=20
uuid char(40),=20
primary key (inventory=5Fid) constraint "informix".ds=5Fhead=5Fnp=5Fpk=20
) with crcols in dbdata22 extent size 8192 next size 8192 lock mode row;=20
revoke all on "informix".ds=5Fhead=5Fnp from "public" as "informix";=20
create unique index "informix".ds=5Fhead=5Fnp=5Fidx0 on "informix".ds=5Fhea=
d=5Fnp=20
(dataset=5Fname) using btree in dbdw08;=20
create index "informix".ds=5Fhead=5Fnp=5Fidx1 on "informix".ds=5Fhead=5Fnp =
(uuid) using btree in dbdata23;=20
create index "informix".ds=5Fhead=5Fnp=5Fidx2 on "informix".ds=5Fhead=5Fnp =
(datatype=5Fname) using btree in dbdata01;=20
create index "informix".ds=5Fhead=5Fnp=5Fidx3 on "informix".ds=5Fhead=5Fnp =
(datatype=5Fversion) using btree in dbdata01;=20
create index "informix".ds=5Fhead=5Fnp=5Fidx4 on "informix".ds=5Fhead=5Fnp =
(ingest=5Fdt) using btree in dbdata01;=20
alter table "informix".ds=5Fhead=5Fnp add constraint (foreign key=20
(datatype=5Fname) references "informix".datatypes constraint=20
"informix".ds=5Fhead=5Fnp=5Fdtn);=20
alter table "informix".ds=5Fhead=5Fnp add constraint (foreign key=20
(datatype=5Fversion) references "informix".datatyp=5Fvers constraint=20
"informix".ds=5Fhead=5Fnp=5Fdtv);=20
--001a114a6a4eab2fc20531df4254=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
I'm pretty sure that we modified the code in one of the 11.x versions so that
we actually applied rows with unique indexes as though they were unique
constraints. The problem is that with unique constraints, we can do deferred
constraint checking so that the check was made as part of the commit. We
couldn't do that with unique indexes, even though a unique index and a unique
constraint is physically pretty much the same thing. Because we couldn't do
deferred reference checking as part of the commit, we could get into trouble
if two transactions were being applied in which one transaction created a
parent row and a subsequent transaction created the child. On target servers,
the physical apply of the rows could be in parallel while on the source it
could not because it was done by a single session serially. So initially we
put in the code to check for unique indexes and suggested that they be unique
constraints instead. Of course once we got the code in place to make ER apply
all unique indexes as though they were unique constraints, then we could defer
referential checking until the commit. And since the commits are always done
in the same order as the original transaction, the issue went away.
I'm guessing that we simply forgot to remove the warning.
Madison Pruet
Retired and Loving it
On Monday, May 2, 2016 12:25 PM, FRANK <yunyaoqu@gmail.com> wrote:
IDS12.10 FC4.
Occasionally saw this,
17:03:35 Warning - Table noaa:informix.ds_head_np
is replicated and contains a unique index
rather than a unique constraint.
17:03:35 Unique constraints should be used with replicated tables
rather than a simple unique index.
Any comments or potential negative?
This table is ER replicated with no warning in old versions when it was
originally defined. Attached table definition.
Thanks
Frank
create table "informix".ds_head_np
(
inventory_id serial not null ,
dataset_name varchar(255,44) not null ,
dataset_size_bytes int8,
datatype_name char(10) not null ,
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second not null ,
orig_data_filenm varchar(255,44) not null ,
distribution_site char(1) not null ,
data_source char(10),
has_visual_file char(1),
restriction_level smallint,
accessible char(1)
default 'Y',
uuid char(40),
primary key (inventory_id) constraint "informix".ds_head_np_pk
) with crcols in dbdata22 extent size 8192 next size 8192 lock mode row;
revoke all on "informix".ds_head_np from "public" as "informix";
create unique index "informix".ds_head_np_idx0 on "informix".ds_head_np
(dataset_name) using btree in dbdw08;
create index "informix".ds_head_np_idx1 on "informix".ds_head_np
(uuid) using btree in dbdata23;
create index "informix".ds_head_np_idx2 on "informix".ds_head_np
(datatype_name) using btree in dbdata01;
create index "informix".ds_head_np_idx3 on "informix".ds_head_np
(datatype_version) using btree in dbdata01;
create index "informix".ds_head_np_idx4 on "informix".ds_head_np
(ingest_dt) using btree in dbdata01;
alter table "informix".ds_head_np add constraint (foreign key
(datatype_name) references "informix".datatypes constraint
"informix".ds_head_np_dtn);
alter table "informix".ds_head_np add constraint (foreign key
(datatype_version) references "informix".datatyp_vers constraint
"informix".ds_head_np_dtv);
--001a114a6a4eab2fc20531df4254
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Andreas! Will do . Frank
On Mon, May 2, 2016 at 3:37 PM, Andreas Legner <andreas.legner@de.ibm.com>
wrote:
> Hi Frank,
>
> you'd rather define a UNIQUE CONSTRAINT in addition to / on top of the=20
> sole UNIQUE INDEX that you already have on this table. This would quiesce =
>
> the warning and allow 'deferred constraint checking' (as now there is a=20
> constraint to defer) which ER sometimes has to rely on on target side.
>
> Best regards,
>
> Andreas
>
> From: "FRANK" <yunyaoqu@gmail.com>
> To: ids@iiug.org
> Date: 02.05.2016 19:25
> Subject: Unique constraints should be used with replica.... [37071]
> Sent by: ids-bounces@iiug.org
>
> IDS12.10 FC4.=20
>
> Occasionally saw this,=20
>
> 17:03:35 Warning - Table noaa:informix.ds=5Fhead=5Fnp=20
>
> is replicated and contains a unique index=20
>
> rather than a unique constraint.=20
> 17:03:35 Unique constraints should be used with replicated tables=20
>
> rather than a simple unique index.=20
>
> Any comments or potential negative?=20
>
> This table is ER replicated with no warning in old versions when it was=20
> originally defined. Attached table definition.=20
>
> Thanks=20
> Frank=20
>
> create table "informix".ds=5Fhead=5Fnp=20
> (=20
>
> inventory=5Fid serial not null ,=20
>
> dataset=5Fname varchar(255,44) not null ,=20
>
> dataset=5Fsize=5Fbytes int8,=20
>
> datatype=5Fname char(10) not null ,=20
>
> datatype=5Fversion char(10),=20
>
> ingest=5Fstatus char(10),=20
>
> ingest=5Fdt datetime year to second not null ,=20
>
> orig=5Fdata=5Ffilenm varchar(255,44) not null ,=20
>
> distribution=5Fsite char(1) not null ,=20
>
> data=5Fsource char(10),=20
>
> has=5Fvisual=5Ffile char(1),=20
>
> restriction=5Flevel smallint,=20
>
> accessible char(1)=20
>
> default 'Y',=20
>
> uuid char(40),=20
>
> primary key (inventory=5Fid) constraint "informix".ds=5Fhead=5Fnp=5Fpk=20
> ) with crcols in dbdata22 extent size 8192 next size 8192 lock mode row;=20
> revoke all on "informix".ds=5Fhead=5Fnp from "public" as "informix";=20>
> create unique index "informix".ds=5Fhead=5Fnp=5Fidx0 on
> "informix".ds=5Fhea=
> d=5Fnp=20
>
> (dataset=5Fname) using btree in dbdw08;=20
> create index "informix".ds=5Fhead=5Fnp=5Fidx1 on "informix".ds=5Fhead=5Fnp
> =
>
> (uuid) using btree in dbdata23;=20
> create index "informix".ds=5Fhead=5Fnp=5Fidx2 on "informix".ds=5Fhead=5Fnp
> =
>
> (datatype=5Fname) using btree in dbdata01;=20
> create index "informix".ds=5Fhead=5Fnp=5Fidx3 on "informix".ds=5Fhead=5Fnp
> =
>
> (datatype=5Fversion) using btree in dbdata01;=20
> create index "informix".ds=5Fhead=5Fnp=5Fidx4 on "informix".ds=5Fhead=5Fnp
> =
>
> (ingest=5Fdt) using btree in dbdata01;=20
>
> alter table "informix".ds=5Fhead=5Fnp add constraint (foreign key=20
>
> (datatype=5Fname) references "informix".datatypes constraint=20
>
> "informix".ds=5Fhead=5Fnp=5Fdtn);=20
> alter table "informix".ds=5Fhead=5Fnp add constraint (foreign key=20
>
> (datatype=5Fversion) references "informix".datatyp=5Fvers constraint=20
>
> "informix".ds=5Fhead=5Fnp=5Fdtv);=20
>
> --001a114a6a4eab2fc20531df4254=20
>
>
> ***************************************************************************=
> ****=20
>
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1135419a5dcbd30531f26d65