Why is schema SYSIBM

[Robert Stearns]

I ran the following bit of SQL and my PRIMARY KEY wound up in schema
SYSIBM called SQL.... not schema is3 called primary. The index
registation did wind up there. Obviously there's something I don't
understand or see. If you have some insight into this problem that you
are willing to share, I'd be grateful. One possibility is the uniqueness
required: is the constraint required to be unique with table, schema or
database?

PS: Ignore the #SYNC lines, they are an artifact introduced by the quest
script generator.

set schema is3;

drop TABLE animals;

CREATE TABLE animals
(
BHID BIGINT NOT NULL,
CONTROLLER INTEGER NOT NULL,
LOCATION INTEGER NOT NULL,
TATTOO CHARACTER(9),
TATTOO_LOC CHARACTER(1),
ASSOC CHARACTER(5),
PREFIX CHARACTER(5),
REGNUM character(10),
TAG CHARACTER(5),
SEX CHARACTER(1),
BIRTH_DATE DATE,
NAMEX VARCHAR(50),
ACTIVEX CHARACTER(1),
SIRE_BHID BIGINT NOT NULL,
DAM_BHID BIGINT NOT NULL,
RECIP_BHID BIGINT NOT NULL,
ELECTRONIC_ID VARCHAR(30),
COMMENTX VARCHAR(240),
DISPOSAL_CODE SMALLINT,
DISPOSAL_DATE DATE,
COLOR CHAR(2),
HPS CHAR(1),
MATING CHAR(1),
BREED_1 CHAR(2),
PCT_1 DECIMAL(5,2),
BREED_2 CHAR(2),
PCT_2 DECIMAL(5,2),
BREED_3 CHAR(2),
PCT_3 DECIMAL(5,2),
BREED_4 CHAR(2),
PCT_4 DECIMAL(5,2),
BLOOD_TYPED CHARACTER(1),
BLOOD_CASE_NUMB CHARACTER(8),
DNA_TESTED CHARACTER(1),
DNA_CASE_NUMB CHARACTER(8),
OLD_TAG CHARACTER(5),
BANGS_ID CHARACTER(12),
deal_desc varchar(1000)
)
DATA CAPTURE NONE
IN USERSPACE1;

#SYNC 10;

ALTER TABLE animals
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;

#SYNC 20;

ALTER TABLE animals
ADD CONSTRAINT PRIMARY PRIMARY KEY
(BHID);

#SYNC 30;

ALTER TABLE animals
ADD CONSTRAINT ACTIVEX CHECK
(ACTIVEX in ('Y','N'))
ENFORCED
ENABLE QUERY OPTIMIZATION;

#SYNC 40;

ALTER TABLE animals
ADD CONSTRAINT DISPOSAL_CODE CHECK
(DISPOSAL_CODE between 30 and 37)
ENFORCED
ENABLE QUERY OPTIMIZATION;

#SYNC 60;

ALTER TABLE animals
ADD CONSTRAINT SEX CHECK
(SEX in ('B','C','S'))
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT TATTOO_LOC CHECK
(TATTOO_LOC in ('B','L','R'))
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT COLOR FOREIGN KEY
(COLOR)
REFERENCES COLOR_defn
(BH_COLOR_CODE)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT HPS CHECK
(HPS in ('H','P','S'))
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT MATING CHECK
(MATING in ('N','A','E'))
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT BREED_1 FOREIGN KEY
(BREED_1)
REFERENCES BREED_defn
(BH_BREED_CODE)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT PCT_1 CHECK
(0<PCT_1 AND PCT_1<=100)
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT BREED_2 FOREIGN KEY
(BREED_2)
REFERENCES BREED_defn
(BH_BREED_CODE)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT PCT_2 CHECK
(0<PCT_2 AND PCT_2<=100)
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT BREED_3 FOREIGN KEY
(BREED_3)
REFERENCES BREED_defn
(BH_BREED_CODE)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT PCT_3 CHECK
(0<PCT_3 AND PCT_3<=100)
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT BREED_4 FOREIGN KEY
(BREED_4)
REFERENCES BREED_defn
(BH_BREED_CODE)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT PCT_4 CHECK
(0<PCT_4 AND PCT_4<=100)
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT PCT_ALL_TOT CHECK
(PCT_1+COALESCE(PCT_2,0)+COALESCE(PCT_3,0)+COALESC E(PCT_4,0)=100)
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT PCT_ALL_ORDER CHECK
( PCT_1>=COALESCE(PCT_2,0) AND
COALESCE(PCT_2,0)>=COALESCE(PCT_3,0) AND
COALESCE(PCT_3,0)>=COALESCE(PCT_4,0) )
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT BLOOD_TYPED CHECK
(BLOOD_TYPED in ('N','Y'))
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE animals
ADD CONSTRAINT DNA_TESTED CHECK
(DNA_TESTED in ('N','Y'))
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE ANIMALS
ADD CONSTRAINT location FOREIGN KEY
(LOCATION)
REFERENCES locations
(LOC_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

#SYNC 70;

ALTER TABLE ANIMALS
ADD CONSTRAINT association FOREIGN KEY
(ASSOC)
REFERENCES ASSOC_DEFN
(assoc_code)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

#SYNC 80;

ALTER TABLE ANIMALS
ADD CONSTRAINT controller FOREIGN KEY
(CONTROLLER)
REFERENCES ENTITIES_pub
(ENTITY_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

#SYNC 90;

insert into animals
(bhid,controller,location,namex,sire_bhid,dam_bhid ,recip_bhid)
values(0,1,-1,'Anonymous parent',0,0,0);

ALTER TABLE ANIMALS
ADD CONSTRAINT sire_bhid FOREIGN KEY
(SIRE_BHID)
REFERENCES ANIMALS
(BHID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

#SYNC 100;

ALTER TABLE ANIMALS
ADD CONSTRAINT dam_bhid FOREIGN KEY
(DAM_BHID)
REFERENCES ANIMALS
(BHID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE ANIMALS
ADD CONSTRAINT recip_bhid FOREIGN KEY
(recip_BHID)
REFERENCES ANIMALS
(BHID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;

#SYNC 110;

CREATE INDEX registration ON ANIMALS (PREFIX, REGNUM);

[Jan M. Nelken]

Robert Stearns wrote:

I ran the following bit of SQL and my PRIMARY KEY wound up in schema
SYSIBM called SQL.... not schema is3 called primary. The index
registation did wind up there. Obviously there's something I don't
understand or see. If you have some insight into this problem that you
are willing to share, I'd be grateful. One possibility is the uniqueness
required: is the constraint required to be unique with table, schema or
database?

PRIMARY KEY constraint implies UNIQUE.

Your Primary Key *did not wound up in schema SYSIBM called SQL...*. You
have constraint called IS3.PRIMARY which represents fact that column
BHID is a Primary Key for the table ANIMALS.

You may see it from this query:

SELECT DEFINER, CONSTNAME, TABSCHEMA, TABNAME FROM SYSCAT.TABCONST

DB2 chooses to use unique index as a method to implement this
constraint. Since DB2 will create this index for its *implementation*
of the Primary key Constraint - it creates the index in schema SYSIBM
with generated name of SQL + timestamp of creation.

If you prefer to use your own index instead - have it ready *prior* to
creating constraint - as in this example:

D:\>db2 connect to sample

Database Connection Information

Database server = DB2/NT xx.xx.xx
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
D:\>db2 create table test (id int not null, name char(32))
DB20000I The SQL command completed successfully.

D:\>db2 create unique index PK on test(id)
DB20000I The SQL command completed successfully.

D:\>db2 alter table test add constraint primary primary key(id)
SQL0598W Existing index "DB2ADMIN.PK" is used as the index for the
primary key or a unique key. SQLSTATE=01550

D:\>

Jan M. Nelken

[Ian]

Robert Stearns wrote:

I ran the following bit of SQL and my PRIMARY KEY wound up in schema
SYSIBM called SQL.... not schema is3 called primary. The index
registation did wind up there. Obviously there's something I don't
understand or see. If you have some insight into this problem that you
are willing to share, I'd be grateful. One possibility is the uniqueness
required: is the constraint required to be unique with table, schema or
database?

You create a primary key *constraint* named 'primary'. A primary key
constraint is not the same thing as a unique index. DB2 automatically
creates a unique index to enforce this constraint (if one does not
already exist). When an index is automatically created for this
constraint, it is created as SYSIBM.SQL<timestamp>.

Dropping the primary key constraint will drop the automatically created
index.

If you create a unique index before adding the primary key constraint,
DB2 will use this index instead of creating another one:

create table t1 (
c1 int not null,
c2 char(2)
);

create unique index px_t1
on t1 (c1);

alter table t1
add constraint pk_t1
primary key (c1);

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Robert Stearns]

Ian wrote:

Robert Stearns wrote:

I ran the following bit of SQL and my PRIMARY KEY wound up in schema
SYSIBM called SQL.... not schema is3 called primary. The index
registation did wind up there. Obviously there's something I don't
understand or see. If you have some insight into this problem that you
are willing to share, I'd be grateful. One possibility is the
uniqueness required: is the constraint required to be unique with
table, schema or database?

You create a primary key *constraint* named 'primary'. A primary key
constraint is not the same thing as a unique index. DB2 automatically
creates a unique index to enforce this constraint (if one does not
already exist). When an index is automatically created for this
constraint, it is created as SYSIBM.SQL<timestamp>.

Dropping the primary key constraint will drop the automatically created
index.

If you create a unique index before adding the primary key constraint,
DB2 will use this index instead of creating another one:

create table t1 (
c1 int not null,
c2 char(2)
);

create unique index px_t1
on t1 (c1);

alter table t1
add constraint pk_t1
primary key (c1);

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Thank you.

With your example and instruction, I discovered some (perhaps
disappointing) things about db2 (somewhere in the 100MB of
documentation, no doubt).

It appears that DB2 tries to use the name of the constraint as the index
name (though still in SYSIBM) but the uniqueness constraint on the name
of an index is over a whole schema, not just within a table (and the
length of the name is restricted to 18 character or less, so just pre-
or appending 'primary' to the table name is out unless you can keep
table names to less than 10 characters!) and when a uniqueness conflict
is found (I foolishly named all of my primary key constraints 'primary')
db2 silently "corrects" the problem.

Since table names and column names, for that matter, have somewhat
longer length limitations I am looking for a good naming convention for
my indices; none of: 1) the function (primary, e.g.); 2) the indexed
column; or 3) the indexed table; gives a unique name within the schema;
worse the latter two occasionally exceed the length limitation. I prefer
names within my schema that are indicative of the purpose of and/or the
columns involved in the index to help "reading" of the database and/or
code which uses it. Right now I have 8 main data tables and 24
supporting tables (and at least as many views), which is not many for a
development team with DBAs, designers, programmers, testers and
documenters, but is a fairly large set for a single person wearing all
those hats (as well as PHP/DB interface programmer; luckily not HTML
programmer or screen design) to keep track of, so I need good naming
conventions.

[Matt Emmerton]

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

Ian wrote:

Robert Stearns wrote:

[ snip ]
Since table names and column names, for that matter, have somewhat
longer length limitations I am looking for a good naming convention for
my indices; none of: 1) the function (primary, e.g.); 2) the indexed
column; or 3) the indexed table; gives a unique name within the schema;
worse the latter two occasionally exceed the length limitation. I prefer
names within my schema that are indicative of the purpose of and/or the
columns involved in the index to help "reading" of the database and/or
code which uses it. Right now I have 8 main data tables and 24
supporting tables (and at least as many views), which is not many for a
development team with DBAs, designers, programmers, testers and
documenters, but is a fairly large set for a single person wearing all
those hats (as well as PHP/DB interface programmer; luckily not HTML
programmer or screen design) to keep track of, so I need good naming
conventions.

What I describe (in length) below many people will disagree with, and it
does go against what Robert is asking for.
However, this is the process I've used for every database I've designed, and
it has served me well.

Rule 1: Follow the KISS principle when naming objects
Rule 2: Document your schema with English (or native-language)
documentation.

A perfectly valid (and useful) naming convention would be:
Table: ANIMALS
Primary Key: ANIMALS_PK
Foreign Keys: ANIMALS_FK1, ANIMALS_FK2, etc.
Check Constraints: ANIMALS_CK1, ANIMALS_CK2, etc.
Indexes: ANIMALS_IDX1, ANIMALS_IDX2, etc.
Views: ANIMALS_V1, ANIMALS_V2, etc, *or* something describing the
subset/superset of data that is being displayed.

View are the one place where I diverge a bit, since views are alternate
representations of data you already have, and are often used as the primary
references in queries. Using descriptive names here allows your SQL queries
to read more like English.

This a simple naming convention explicitly groups objects together and makes
the organization of the database clear. Often times the toughest part of
understanding a database (when things go wrong) is figuring out how things
relate, and being able to see the database as "clusters" of information
makes the relationships clear.

Any attempt to include details about the database object in the name itself
will ultimately end in failure. As Robert has already indicated, his first
3 naming attempts have exceeded the limits on object names that DB2 imposes.
There is a subtle hint here -- the purpose of an object name is to uniquely
identify the object in the database -- not to describe the purpose and/or
structure of the object itself.

This latter task is best handled by supporting documentation, which has two
important but discrete parts:
1) A full schema dump. For example, use "db2look -a -e -l -m -f -d
<dbname> -o <output file>". Save this on both hardcopy and softcopy and
store in a safe place, as this will allow you to reconstruct your database
(sans data) if something disastrous should to your database (and backups).
This will save you from the exercise of re-engineering your schema from your
head.
2) An English (or native-language) description of each object, how it
relates to others, and *why* it exists. (For example, check constraints
ensure validity of data; indexes are used to support certain queries,
primary and foreign keys help maintain data integrity.)

This documentation, when done properly, will serve everyone's needs. The
DBA can understand why objects exist and how they are related. The
application designer can understand how the data is organized, and what
tools (check constraints, primary/foreign keys, indexes) are available to
make building the application easier.

As I said, this is just my methodology; hopefully it can help others out.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

[Robert Stearns]

Matt Emmerton wrote:

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

Ian wrote:

Robert Stearns wrote:

[ snip ]

Since table names and column names, for that matter, have somewhat
longer length limitations I am looking for a good naming convention for
my indices; none of: 1) the function (primary, e.g.); 2) the indexed
column; or 3) the indexed table; gives a unique name within the schema;
worse the latter two occasionally exceed the length limitation. I prefer
names within my schema that are indicative of the purpose of and/or the
columns involved in the index to help "reading" of the database and/or
code which uses it. Right now I have 8 main data tables and 24
supporting tables (and at least as many views), which is not many for a
development team with DBAs, designers, programmers, testers and
documenters, but is a fairly large set for a single person wearing all
those hats (as well as PHP/DB interface programmer; luckily not HTML
programmer or screen design) to keep track of, so I need good naming
conventions.

What I describe (in length) below many people will disagree with, and it
does go against what Robert is asking for.
However, this is the process I've used for every database I've designed, and
it has served me well.

Rule 1: Follow the KISS principle when naming objects
Rule 2: Document your schema with English (or native-language)
documentation.

A perfectly valid (and useful) naming convention would be:
Table: ANIMALS
Primary Key: ANIMALS_PK
Foreign Keys: ANIMALS_FK1, ANIMALS_FK2, etc.
Check Constraints: ANIMALS_CK1, ANIMALS_CK2, etc.
Indexes: ANIMALS_IDX1, ANIMALS_IDX2, etc.
Views: ANIMALS_V1, ANIMALS_V2, etc, *or* something describing the
subset/superset of data that is being displayed.

View are the one place where I diverge a bit, since views are alternate
representations of data you already have, and are often used as the primary
references in queries. Using descriptive names here allows your SQL queries
to read more like English.

This a simple naming convention explicitly groups objects together and makes
the organization of the database clear. Often times the toughest part of
understanding a database (when things go wrong) is figuring out how things
relate, and being able to see the database as "clusters" of information
makes the relationships clear.

Any attempt to include details about the database object in the name itself
will ultimately end in failure. As Robert has already indicated, his first
3 naming attempts have exceeded the limits on object names that DB2 imposes.
There is a subtle hint here -- the purpose of an object name is to uniquely
identify the object in the database -- not to describe the purpose and/or
structure of the object itself.

This latter task is best handled by supporting documentation, which has two
important but discrete parts:
1) A full schema dump. For example, use "db2look -a -e -l -m -f -d
<dbname> -o <output file>". Save this on both hardcopy and softcopy and
store in a safe place, as this will allow you to reconstruct your database
(sans data) if something disastrous should to your database (and backups).
This will save you from the exercise of re-engineering your schema from your
head.
2) An English (or native-language) description of each object, how it
relates to others, and *why* it exists. (For example, check constraints
ensure validity of data; indexes are used to support certain queries,
primary and foreign keys help maintain data integrity.)

This documentation, when done properly, will serve everyone's needs. The
DBA can understand why objects exist and how they are related. The
application designer can understand how the data is organized, and what
tools (check constraints, primary/foreign keys, indexes) are available to
make building the application easier.

As I said, this is just my methodology; hopefully it can help others out.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

The first sugestion seems appropriate (use the table name with a
function suffix) until you have tables with names like
"entities_priv_interests" which is a real example from my database. It
could further abbreviated, say to "ent_prv_int", but at what point does
the abbreviation become too cryptic to unravel in 6 months? 18 months?
more? (Do you remember what PCMCIA means? I don't mean People Can't
Memorize Computer Industry Acronyms :-) While I am the sole techie
involved, I'm reaching retirement age and have to look forward to
someone else trying to read what I've done.

The second point is right in theory, but has two shortcomings: first is
human behavior -- get it running, then we'll see about documentation;
and the second, more serious, is that the documentation and the
definitions are separate things -- in effect the documentation "rots"
over time because it is not updated as often as the thing being
documented. We have all seen documentation (even comments in code) which
misleads us, even directly contradicts the actual code. I understand the
problem of putting semantic value in names, it is similar to putting
semantic meaning in keys; sooner or later the semantics rules must be
broken because of unforeseen changes in the environment. But it is the
best we can do, unless we can devote at least as much effort (many
times, more) to the documentation effort as we do to the actual
technical problem.

Anent this problem, is it possible to put at least a minimum of
documentation into the definitions so that the dblook command (thanks
for that) could print it at the same time as the actual structure?

[Matt Emmerton]

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

Matt Emmerton wrote:

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

Ian wrote:

Robert Stearns wrote:

[ snip ]

Since table names and column names, for that matter, have somewhat
longer length limitations I am looking for a good naming convention for
my indices; none of: 1) the function (primary, e.g.); 2) the indexed
column; or 3) the indexed table; gives a unique name within the schema;
worse the latter two occasionally exceed the length limitation. I prefer
names within my schema that are indicative of the purpose of and/or the
columns involved in the index to help "reading" of the database and/or
code which uses it. Right now I have 8 main data tables and 24
supporting tables (and at least as many views), which is not many for a
development team with DBAs, designers, programmers, testers and
documenters, but is a fairly large set for a single person wearing all
those hats (as well as PHP/DB interface programmer; luckily not HTML
programmer or screen design) to keep track of, so I need good naming
conventions.

What I describe (in length) below many people will disagree with, and it
does go against what Robert is asking for.
However, this is the process I've used for every database I've designed, and it has served me well.

Rule 1: Follow the KISS principle when naming objects
Rule 2: Document your schema with English (or native-language)
documentation.

A perfectly valid (and useful) naming convention would be:
Table: ANIMALS
Primary Key: ANIMALS_PK
Foreign Keys: ANIMALS_FK1, ANIMALS_FK2, etc.
Check Constraints: ANIMALS_CK1, ANIMALS_CK2, etc.
Indexes: ANIMALS_IDX1, ANIMALS_IDX2, etc.
Views: ANIMALS_V1, ANIMALS_V2, etc, *or* something describing the
subset/superset of data that is being displayed.

View are the one place where I diverge a bit, since views are alternate
representations of data you already have, and are often used as the primary references in queries. Using descriptive names here allows your SQL queries to read more like English.

This a simple naming convention explicitly groups objects together and makes the organization of the database clear. Often times the toughest part of understanding a database (when things go wrong) is figuring out how things relate, and being able to see the database as "clusters" of information
makes the relationships clear.

Any attempt to include details about the database object in the name itself will ultimately end in failure. As Robert has already indicated, his first 3 naming attempts have exceeded the limits on object names that DB2 imposes. There is a subtle hint here -- the purpose of an object name is to uniquely identify the object in the database -- not to describe the purpose and/or structure of the object itself.

This latter task is best handled by supporting documentation, which has two important but discrete parts:
1) A full schema dump. For example, use "db2look -a -e -l -m -f -d
<dbname> -o <output file>". Save this on both hardcopy and softcopy and
store in a safe place, as this will allow you to reconstruct your database (sans data) if something disastrous should to your database (and backups). This will save you from the exercise of re-engineering your schema from your head.
2) An English (or native-language) description of each object, how it
relates to others, and *why* it exists. (For example, check constraints
ensure validity of data; indexes are used to support certain queries,
primary and foreign keys help maintain data integrity.)

This documentation, when done properly, will serve everyone's needs. The DBA can understand why objects exist and how they are related. The
application designer can understand how the data is organized, and what
tools (check constraints, primary/foreign keys, indexes) are available to make building the application easier.

As I said, this is just my methodology; hopefully it can help others out.
--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

The first sugestion seems appropriate (use the table name with a
function suffix) until you have tables with names like
"entities_priv_interests" which is a real example from my database. It
could further abbreviated, say to "ent_prv_int", but at what point does
the abbreviation become too cryptic to unravel in 6 months? 18 months?
more? (Do you remember what PCMCIA means? I don't mean People Can't
Memorize Computer Industry Acronyms :-) While I am the sole techie
involved, I'm reaching retirement age and have to look forward to
someone else trying to read what I've done.

The second point is right in theory, but has two shortcomings: first is
human behavior -- get it running, then we'll see about documentation;
and the second, more serious, is that the documentation and the
definitions are separate things -- in effect the documentation "rots"
over time because it is not updated as often as the thing being
documented. We have all seen documentation (even comments in code) which
misleads us, even directly contradicts the actual code. I understand the
problem of putting semantic value in names, it is similar to putting
semantic meaning in keys; sooner or later the semantics rules must be
broken because of unforeseen changes in the environment. But it is the
best we can do, unless we can devote at least as much effort (many
times, more) to the documentation effort as we do to the actual
technical problem.

Anent this problem, is it possible to put at least a minimum of
documentation into the definitions so that the dblook command (thanks
for that) could print it at the same time as the actual structure?

db2look just dumps the database schema from the database.
It does not have any support for annotations or anything like that.

--
Matt Emmerton

[Douglas Doole]

Robert Stearns wrote:

Anent this problem, is it possible to put at least a minimum of
documentation into the definitions so that the dblook command (thanks
for that) could print it at the same time as the actual structure?

Pretty much everything in the DB2 catalogs has a REMARKS column for exactly
this purpose. You set the remarks using the COMMENT ON statement. For
example:

COMMENT ON COLUMN animals.diet IS 'Dietary requirement for animal'

Take a look at the SQL Reference for a complete list of what can be
commented on.
--
__________________________________________________ ___________________
Doug Doole
DB2 Universal Database Development
IBM Toronto Lab

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2...s2unix/support