create table

Description

Creates a new table.

Syntax

create table [database.[owner].]table_name (column_name datatype {null | not null} [{, next_column }...]) [on segment_name]

Parameters

table_name: is the name of the new table. It conforms to the rules for identifiers and is unique within the database and to the owner.

column_name: is the name of the column in the table. It conforms to the rules for identifiers and is unique in the table.

datatype

is the datatype of the column. Only system datatypes are used. As shown in Table B-1, several datatypes expect a length, n, in parentheses:

 	datatype(n)

null | not null: specifies a null value if a user does not provide a value during an insertion and no default exists (for null), or that a user must provide a non-null value if no default exists (for not null).

next_column: indicates that you can include additional column definitions (separated by commas) using the same syntax described for a column definition.

Examples

Example 1

create table titles
 	(title_id 	tid 	not null,
 	title 	varchar(80) 	not null,
 	type 	char(12) 	not null,
 	pub_id 	char(4) 	null,
 	price 	money 	null,
 	advance 	money 	null,
 	total_sales 	int 	null,
 	notes 	varchar(200)	null,
 	pubdate 	datetime 	not null,
 	contract 	bit 	not null)

Usage

T-SQL allows you to specify null or not null, with a default of not null. DB2 UDB allows only not null to be specified, and the default is null. Table B-2 shows how the DB2 access service transforms this clause.

Table B-2: Null transformations during T-SQL to DB2 create table
T-SQL specification

Transformed to...

null

<nothing>

not null

not null

<nothing>

not null

**Table B-2: Null transformations during T-SQL to DB2 create table**
T-SQL specification	Transformed to...
null	<nothing>
not null	not null
<nothing>	not null

Table B-3 shows the DB2 access service transformation of datatype specifications.

Table B-3: Datatype conversions during T-SQL to DB2 *create table*
T-SQL datatype	DB2 UDB datatype
tinyint	SMALLINT
smallint	SMALLINT
int	INT
numeric(p,s)	NUMERIC(p,s)
decimal(p,s)	DECIMAL(p,s)
float (double precision)	FLOAT
real	REAL
smallmoney	DECIMAL(10,4)
money	DECIMAL(19,4)
smalldatetime	TIMESTAMP
datetime	TIMESTAMP
char(n)	CHAR(n)
varchar(n)	VARCHAR(n)
text	LONG VARCHAR(n)
binary(n)	CHAR(n) FOR BIT DATA
varbinary(n)	VARCHAR(n) FOR BIT DATA
image	LONG VARCHAR (n) FOR BIT DATA
bit	SMALLINT