Stephane:
Version 7 (specifically 7.2, which I think you are using):
Is it ok to use the identity functionality to generate primary keys ? (we
may use the loader to bulk load data from flat files)
DB2 is using identity as Oracle uses sequences. One difference is that you
can have only one "identity" column per table, and you typically don't
update it. DB2 generates this value on INSERT into the table, unless you've
given it a default value. On load, you just don't specify the column, and
DB2 will generate the sequence for you, starting from 1. It will be unique,
so you can assign a primary key to it; but it would be hard to use if
referentially -- since the number will basically be a sequential count.
Is it better to use decimal (x.0) or to use int,smallint or biggint ?
If this is referring to "identity" you want to make sure the column value is
not a decimal (unless it has a precision of 0). Bigint is the resulting
type of a lot of OLAP functions, and I'd want the sequence of the column to
be able to grow a long time. I'd use BigInt when I didn't know the
upperlimit (when I'd use SmallInt) I'd never use decimal, unless I knew I
wanted to fractionalize the number (or maybe do some math on it) at some
time. It makes for unnecessary overhead with internal conversion.
Is there an order for the columns in a table, not null at the beginning and
null at the end ?
How do you mean? Internally, it's best to store the data so that null
references fall at the end of the table. How you define the table is
immaterial. For clarity, and because some people say it's a good idea, you
might want to define all the column that are NOT NULL/NOT NULL WITH DEFAULT
early; since they will have varying content.
Hope this helps!
Ken Bateh
Senior DBA
(904) 783-5848
[Email Address Removed] Message-----
From: Stephane Paquette [ mailto:stephane.[Email Address Removed] ]
Sent: Thursday, October 24, 2002 10:02 AM
To: [Email Address Removed] Table creation
Hi,
After 13 years with Oracle, I'm trying DB2 (UDB 7.2).
I have to do the physical model.
Is it ok to use the identity functionnality to generate primary keys ? (we
may use the loader to bulk load data from flat files)
Is it better to use decimal (x.0) or to use int,smallint or biggint ?
Is there an order for the columns in a table, not null at the beginning and
null at the end ?
Any other tips for creating tables ?
TIA
Stéphane Paquette
Administrateur de bases de données
Database Administrator
Standard Life
www.standardlife.ca
Tél. (514) 499-7948
stephane.[Email Address Removed]DB2 & UDB email list listserv db2-l LazyDBA home page