Required to have names for simple constraints?
John asked whether Informix requires explicit names for simple constraints (NOT NULL, PRIMARY KEY) when rebuilding a huge table by creating a new one, loading recent rows, dropping the old and renaming. Answers: names aren't required, but they ease disabling/deferring constraints and avoid clashes on import, since auto-generated constraint/index names embed the table's tabid, which changes after a non-in-place ALTER; naming matters mainly for PK/FK/unique, not NOT NULL or DEFAULT. Ben added that naming (with owner prefix) controls constraint ownership. John posted his working procedure, including ALTER TABLE ... CONSTRAINT syntax, plus Art's suggestion to tune extent sizes or use range/interval fragmentation.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Folks, Working on IDS 11.5 on Solaris. DB was setup many years ago and has not had much mx/tuning since then. Gotten very slow. I'm primarily a MySQL DBA and have done a lot of reading on informix. Things are running much better, but one remaining step is to truncate some very large tables with a lot of historical data that has never been purged. The summary plan is to create a new table, insert the needed data, drop the old, and then do a rename. Restore any indexes, constraints. Got through most of it, but I'm trouble by some diffs I'm seeing in the old/new table schema (two examples below). It seems when the table was created they named every constraint - do I really need to do that for simple cases like a "not null" or "primary key" definition. I've read informix assigns a generated name. Is there a 'gotcha' I'm mising? < trans_id integer not null constraint "informix".nlbdtrans_id, --- > trans_id integer not null , AND < primary key (trans_id,recno) constraint "informix".pkbd --- > primary key (trans_id,recno) Many thanks! John ---------------- John Murtari - jm5903@att.com<mailto:jm5903@att.com> Ciberspring office: 315-944-0998 cell: 315-430-2702
No, strictly speaking constraints do not require an explicit name. However, having a name makes it easier to manage the constraint. For example, you may want to suspend/disable a particular constraint during data loading or purging operations or defer foreign key constraint checking until commit time for a large complex update operation. These can be done more easily if the specific constraints can be disabled or deferred by name rather than doing so for all constraints on the table(s). Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 8, 2016 at 11:06 AM, MURTARI, JOHN <jm5903@att.com> wrote: > Folks, > > Working on IDS 11.5 on Solaris. DB was setup many years ago and has not had > much mx/tuning since then. Gotten very slow. I'm primarily a MySQL DBA and > have done a lot of reading on informix. Things are running much better, but > one remaining step is to truncate some very large tables with a lot of > historical data that has never been purged. > > The summary plan is to create a new table, insert the needed data, drop the > old, and then do a rename. Restore any indexes, constraints. > > Got through most of it, but I'm trouble by some diffs I'm seeing in the > old/new table schema (two examples below). It seems when the table was > created > they named every constraint - do I really need to do that for simple cases > like a "not null" or "primary key" definition. I've read informix assigns a > generated name. Is there a 'gotcha' I'm mising? > > < trans_id integer not null constraint "informix".nlbdtrans_id, > --- > > trans_id integer not null , > AND > < primary key (trans_id,recno) constraint "informix".pkbd > --- > > primary key (trans_id,recno) > > Many thanks! > John > > ---------------- > John Murtari - jm5903@att.com<mailto:jm5903@att.com> > Ciberspring > office: 315-944-0998 > cell: 315-430-2702 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11469644269485053c015761
Apart of what Art mentioned, automatically generated constraints names may give you issues when importing data. Depending on how you do it some constraints may not be in the schema and when Informix generates them it may conflict with others that were created in the original system... Can't remember the details, but i usually try to provide names at least for FK, PK. I think it's related to the fact that the imported tabid will not be the original.... so you may have the same names on different tables that by any chance go the original tabid.... Maybe someone can be clearer about this... On Thu, Sep 8, 2016 at 5:06 PM, MURTARI, JOHN <jm5903@att.com> wrote: > Folks, > > Working on IDS 11.5 on Solaris. DB was setup many years ago and has not had > much mx/tuning since then. Gotten very slow. I'm primarily a MySQL DBA and > have done a lot of reading on informix. Things are running much better, but > one remaining step is to truncate some very large tables with a lot of > historical data that has never been purged. > > The summary plan is to create a new table, insert the needed data, drop the > old, and then do a rename. Restore any indexes, constraints. > > Got through most of it, but I'm trouble by some diffs I'm seeing in the > old/new table schema (two examples below). It seems when the table was > created > they named every constraint - do I really need to do that for simple cases > like a "not null" or "primary key" definition. I've read informix assigns a > generated name. Is there a 'gotcha' I'm mising? > > < trans_id integer not null constraint "informix".nlbdtrans_id, > --- > > trans_id integer not null , > AND > < primary key (trans_id,recno) constraint "informix".pkbd > --- > > primary key (trans_id,recno) > > Many thanks! > John > > ---------------- > John Murtari - jm5903@att.com<mailto:jm5903@att.com> > Ciberspring > office: 315-944-0998 > cell: 315-430-2702 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c114d2abe0570053c023902
Good point Fernando. This is the main reason that I have maintained myschema for all these years. The issue happens when a table experiences an ALTER that is not in-place. That causes the tables tabid to change but the constraints and auto-generated index names created to support constraints use the table's original tabid. When tables are imported that can clash with the constraints for tables created in the new database which happen to have the other table's original tabid. This is the main reason why myschema always generates explicit indexes to support constraints and explicit constraint names unless told not to. However, note that this is NOT an issue for NOT NULL constraints, CHECK constraints, or DEFAULT constraints and so myschema does not generate constraint names for these unless they were explicitly named in the source database. I would drop the names of NOT NULL constraints and DEFAULTs, there is no compelling reason to name them. You can make a case for naming CHECK constaints for the reasons I gave earlier though. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 8, 2016 at 1:18 PM, Fernando Nunes <domusonline@gmail.com> wrote: > Apart of what Art mentioned, automatically generated constraints names may > give you issues when importing data. > Depending on how you do it some constraints may not be in the schema and > when Informix generates them it may conflict with others that were created > in the original system... > > Can't remember the details, but i usually try to provide names at least for > FK, PK. > > I think it's related to the fact that the imported tabid will not be the > original.... so you may have the same names on different tables that by any > chance go the original tabid.... > > Maybe someone can be clearer about this... > > On Thu, Sep 8, 2016 at 5:06 PM, MURTARI, JOHN <jm5903@att.com> wrote: > > > Folks, > > > > Working on IDS 11.5 on Solaris. DB was setup many years ago and has not > had > > much mx/tuning since then. Gotten very slow. I'm primarily a MySQL DBA > and > > have done a lot of reading on informix. Things are running much better, > but > > one remaining step is to truncate some very large tables with a lot of > > historical data that has never been purged. > > > > The summary plan is to create a new table, insert the needed data, drop > the > > old, and then do a rename. Restore any indexes, constraints. > > > > Got through most of it, but I'm trouble by some diffs I'm seeing in the > > old/new table schema (two examples below). It seems when the table was > > created > > they named every constraint - do I really need to do that for simple > cases > > like a "not null" or "primary key" definition. I've read informix > assigns a > > generated name. Is there a 'gotcha' I'm mising? > > > > < trans_id integer not null constraint "informix".nlbdtrans_id, > > --- > > > trans_id integer not null , > > AND > > < primary key (trans_id,recno) constraint "informix".pkbd > > --- > > > primary key (trans_id,recno) > > > > Many thanks! > > John > > > > ---------------- > > John Murtari - jm5903@att.com<mailto:jm5903@att.com> > > Ciberspring > > office: 315-944-0998 > > cell: 315-430-2702 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --94eb2c114d2abe0570053c023902 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c0c27f4323946053c0399a6
You just gave me a confortable feeling as you explained in detail why I do something.... glad to know there"s a reason ;) Thanks! On Sep 8, 2016 20:57, "Art Kagel" <art.kagel@gmail.com> wrote: > Good point Fernando. This is the main reason that I have maintained > myschema for all these years. > > The issue happens when a table experiences an ALTER that is not in-place. > That causes the tables tabid to change but the constraints and > auto-generated index names created to support constraints use the table's > original tabid. When tables are imported that can clash with the > constraints for tables created in the new database which happen to have the > other table's original tabid. This is the main reason why myschema always > generates explicit indexes to support constraints and explicit constraint > names unless told not to. > > However, note that this is NOT an issue for NOT NULL constraints, CHECK > constraints, or DEFAULT constraints and so myschema does not generate > constraint names for these unless they were explicitly named in the source > database. I would drop the names of NOT NULL constraints and DEFAULTs, > there is no compelling reason to name them. You can make a case for naming > CHECK constaints for the reasons I gave earlier though. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Thu, Sep 8, 2016 at 1:18 PM, Fernando Nunes <domusonline@gmail.com> > wrote: > > > Apart of what Art mentioned, automatically generated constraints names > may > > give you issues when importing data. > > Depending on how you do it some constraints may not be in the schema and > > when Informix generates them it may conflict with others that were > created > > in the original system... > > > > Can't remember the details, but i usually try to provide names at least > for > > FK, PK. > > > > I think it's related to the fact that the imported tabid will not be the > > original.... so you may have the same names on different tables that by > any > > chance go the original tabid.... > > > > Maybe someone can be clearer about this... > > > > On Thu, Sep 8, 2016 at 5:06 PM, MURTARI, JOHN <jm5903@att.com> wrote: > > > > > Folks, > > > > > > Working on IDS 11.5 on Solaris. DB was setup many years ago and has not > > had > > > much mx/tuning since then. Gotten very slow. I'm primarily a MySQL DBA > > and > > > have done a lot of reading on informix. Things are running much better, > > but > > > one remaining step is to truncate some very large tables with a lot of > > > historical data that has never been purged. > > > > > > The summary plan is to create a new table, insert the needed data, drop > > the > > > old, and then do a rename. Restore any indexes, constraints. > > > > > > Got through most of it, but I'm trouble by some diffs I'm seeing in the > > > old/new table schema (two examples below). It seems when the table was > > > created > > > they named every constraint - do I really need to do that for simple > > cases > > > like a "not null" or "primary key" definition. I've read informix > > assigns a > > > generated name. Is there a 'gotcha' I'm mising? > > > > > > < trans_id integer not null constraint "informix".nlbdtrans_id, > > > --- > > > > trans_id integer not null , > > > AND > > > < primary key (trans_id,recno) constraint "informix".pkbd > > > --- > > > > primary key (trans_id,recno) > > > > > > Many thanks! > > > John > > > > > > ---------------- > > > John Murtari - jm5903@att.com<mailto:jm5903@att.com> > > > Ciberspring > > > office: 315-944-0998 > > > cell: 315-430-2702 > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --94eb2c114d2abe0570053c023902 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --94eb2c0c27f4323946053c0399a6 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1142e14293a624053c041ed3
Fernando, Art,
Thanks for your input -- especially that a table RENAME may keep some
constraints present and tied to the table id. I was getting constraint already
exists errors and starting to pull my hair out!!! Let me just recap what I was
trying to do and the final solution for those interested.
Old database (but busy) with a transaction table (transb_master) that had data
going back for over 8 years, even though only data within the last year was
ever referenced. The quickest fix plan (during early morning hours) is to:
1. Go into a mx window and stop new transaction recording.
2. Dump the schema for the existing table (which used named constraints for
everything, including NOT NULL).
dbschema -d db -t transb_master
3. Just use the "create table" part of the schema to create table
transb_master_new. BUT -- don't include any indexes, NOT NULL or other
constraints/triggers.
4. Load the new table with just recent data from the old, e.g.
INSERT INTO transb_master_new SELECT * FROM transb_master WHERE trans_id >=1404365
5. DROP the original table (trying to save it doing a RENAME generated
constraint errors later - reasons as described in thread)
6. RENAME the new table to the original (still has no constraints).
7. Apply all the constraints, triggers, etc.. from the schema, I'm including
examples of restoring NOT NULL, CHECK, and primary key -- the syntax diffs
were driving me crazy!
ALTER TABLE transb_master MODIFY user_name VARCHAR(64,24) NOT NULL CONSTRAINTnlbruser_name;
ALTER TABLE transb_master MODIFY ctrans_id VARCHAR(32) NOT NULL CONSTRAINTnlbrctrans_id;
ALTER TABLE transb_master ADD CONSTRAINT CHECK(status IN ('N' ,'S' ,'F' ))CONSTRAINT ckbrstatus;
ALTER TABLE transb_master ADD CONSTRAINT PRIMARY KEY(trans_id) CONSTRAINT pkbr;
8. AGAIN - as folks said, the constraint names weren't all necessary, but to
avoid issues with other tech staff on review -- easiest to make it look the
same.
Looks like a working plan John. One modification if you have not already
gone through this: Estimate the required space for the new table once it is
populated including for future growth and set the EXTENT SIZE and NEXT SIZE
for the table so that a full year (or at least six months' worth) of data
can reside in a single extent and that the table grows in the same 1/2 year
or year sized extents over time.
Better yet, I don't remember if you are using v12.10, but if you are,
consider partitioning the table using range/interval partitions. Then you
can configure it to automatically drop off the oldest partition and add a
new one. You can partition using either the transaction date or the
trans_id (giving each partition a range approximating the number of months
of data you want in each partition). Then a partition can contain say 4 to
6 months' data with a rolling 16 to 18 months on disk at all times and you
never have to do this clean up again!
If most queries against the data include the date/datetime or trans_id
column used to partition it fragment elimination will make those queries
more efficient even than for an all inclusive table lie you have now.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, Sep 9, 2016 at 9:40 AM, JOHN MURTARI <jm5903@att.com> wrote:
> Fernando, Art,
>
> Thanks for your input -- especially that a table RENAME may keep some
> constraints present and tied to the table id. I was getting constraint
> already
> exists errors and starting to pull my hair out!!! Let me just recap what I
> was
> trying to do and the final solution for those interested.
>
> Old database (but busy) with a transaction table (transb_master) that had
> data
> going back for over 8 years, even though only data within the last year was
> ever referenced. The quickest fix plan (during early morning hours) is to:
>
> 1. Go into a mx window and stop new transaction recording.
>
> 2. Dump the schema for the existing table (which used named constraints for
> everything, including NOT NULL).
>
> dbschema -d db -t transb_master>
> 3. Just use the "create table" part of the schema to create table
> transb_master_new. BUT -- don't include any indexes, NOT NULL or other
> constraints/triggers.
>
> 4. Load the new table with just recent data from the old, e.g.
> INSERT INTO transb_master_new SELECT * FROM transb_master WHERE trans_id >=> 1404365
>
> 5. DROP the original table (trying to save it doing a RENAME generated
> constraint errors later - reasons as described in thread)
>
> 6. RENAME the new table to the original (still has no constraints).
>
> 7. Apply all the constraints, triggers, etc.. from the schema, I'm
> including
> examples of restoring NOT NULL, CHECK, and primary key -- the syntax diffs
> were driving me crazy!
>
> ALTER TABLE transb_master MODIFY user_name VARCHAR(64,24) NOT NULL> CONSTRAINT
> nlbruser_name;
>
> ALTER TABLE transb_master MODIFY ctrans_id VARCHAR(32) NOT NULL CONSTRAINT> nlbrctrans_id;
>
> ALTER TABLE transb_master ADD CONSTRAINT CHECK(status IN ('N' ,'S' ,'F' ))> CONSTRAINT ckbrstatus;
>
> ALTER TABLE transb_master ADD CONSTRAINT PRIMARY KEY(trans_id) CONSTRAINT> pkbr;
>
> 8. AGAIN - as folks said, the constraint names weren't all necessary, but
> to
> avoid issues with other tech staff on review -- easiest to make it look the
> same.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--94eb2c0c27f42ba66e053c13e5c0
There is a reason to name them, unfortunately, and that is to control the
owner of the constraints.
If you have table owned by user 'schemaowner' with a column col1, type int
with nulls allowed and a DBA runs:
alter table thetable modify col1 int not null;
It generates an auto-named constraint owned by the user the DBA was logged in
as.
This means that your schema ownership can become a bit of a mess. Dbschema
hides the ownership so you can only see it when you look in the system tables,
for example:
SELECT
CAST(TRIM(t.owner) || '.' || t.tabname || '#' || TRIM(c.owner) || '.' ||
c.constrname AS varchar(60)),
CASE
WHEN constrtype='C' THEN 'check constraint'
WHEN constrtype='P' THEN 'primary key'
WHEN constrtype='R' THEN 'foreign key'
WHEN constrtype='T' THEN 'table constraint'
WHEN constrtype='N' THEN 'not null constraint'
ELSE 'unique key'
END
FROM
sysconstraints c,
systables t
WHERE
c.tabid=t.tabid AND
t.tabid>99;
I am pretty sure that very old versions of dbschema (Informix 7.3X) used to
show constraint owners.
There are only two ways to avoid the problem in this example that I am aware
of.
1. log in as "schemaowner" and run the SQL: this means using shared accounts.
2. use a named constraint with the owner specified, e.g. alter table thetable
modify col1 int not null constraint schemaowner.constrname;
Does this matter? Maybe not very much but I regard it as untidy to have schema
objects owned by random people, some of whom may no longer work for us. It
also makes it harder to see any tables in the database users might have
created for themselves.
Ben.