CA-L-Serv Commands › SQL Statements › CREATE TABLE Statement

CREATE TABLE Statement

The CREATE TABLE statement defines a new VSAM file to the SQL server as data in a relational table.

Syntax

LSQL CREATE TABLE tablename IN DATABASE (ddname,offset[ PREFIX(xx)])
        (colname datatype [[PRIMARY KEY] [UPPER CASE] [NOT NULL]
        [DEFAULT value]])...

Parameters

colname

1‑ to 18‑character name of a column that you are defining in this table. Define the columns in the left‑to‑right order in which the data occurs in the VSAM file.

datatype

Type of data the column can store:

CHAR(length)

Character data, with length being the maximum number of characters. A column of this type must be from 1 through 32000 bytes in length.

DATE

Date indicator of the form yyyy‑mm‑dd; for example, 2001‑08‑31.

DECIMAL(nn,nn)

Decimal data, with the maximum number of digits being 15.

DOUBLE PRECISION

Approximate numeric data, 8‑byte length.

FLOAT(nn)

Approximate numeric data of variable length; mantissa 2‑16 digits, exponent range e‑60 to e60.

HEX(length)

Hexadecimal data, with length as the maximum number of hexadecimal bytes.

INTEGER

32‑bit integer data, with a maximum value of 2147483647.

REAL

Approximate numeric data, 4‑byte length.

SMALLINT

16‑bit integer data with a maximum value of 32767.

TIME or TIME(nn)

Time indicator of the form hh:mm:ss; for example, 13:21:53.

TIMESTAMP or TIMESTAMP(nn)

Date/time indicator, the format being a combination of the DATE and TIME formats.

Note: The DATE, TIME, and TIMESTAMP data types are stored as unsigned packed decimal numbers. When inserting, updating, deleting, or searching for these values, specify the data type with the value. For example:
WHERE CLOSE_DATE = DATE '2001‑08‑31'

ddname

ddname of a VSAM file that contains the data that you want to map to a relational table.

DEFAULT value

Default value to be set for this column if an INSERT statement does not provide one. The default value can be a character string or a numeric value; however, it must be compatible with the data type of the column.

IN DATABASE

Lets you specify the VSAM file and the data within it that is to be contained in the relational table.

NOT NULL

Indicates that the column cannot contain a null value.

offset

Column in the VSAM data set where the data that you want to map begins. For example, if the third byte of data in the VSAM record is to be the first byte of data in the relational table, the offset is 3.

Default: 1

PREFIX(xx)

The 2‑character prefix that identifies the table that a record belongs to when a VSAM file contains records for more than one table. The prefix that you enter here becomes the first 2 bytes of the VSAM key field.

PRIMARY KEY

Designates a column as the primary key for this table. The primary key parallels the traditional concept of keyed files and improves performance in your relational tables.

You can designate more than one column as a primary key. Either type PRIMARY KEY next to each column definition or type in the column definitions and then type PRIMARY KEY(firstcol‑lastcol) at the end of the column definitions. (The firstcol and lastcol variables represent the first and last columns of the primary key.)

tablename

1‑ to 18‑character name of the table.

UPPER CASE

Converts the entries in the column to upper case characters.

Notes:

• You can define up to 250 columns per table.
• Each record in the VSAM data set becomes a row in your relational table.
• For each column you define, include separate column definition clauses in the CREATE TABLE statement. Separate the column definition clauses with commas.
• You can use host variables for tablename or colname.
• If you do not specify a DEFAULT value and data is not provided for a column, binary zeros are inserted.

Example

To define a table that is named RETCODES that contains four columns (with the APPLICATION and USER_ID columns as the primary key), issue the following LSQL command:

LSQL CREATE TABLE RETCODES IN DATABASE (LDMSQL,1)
   (APPLICATION CHAR(15) PRIMARY KEY,
   USER_ID CHAR(8) PRIMARY KEY,
   STATUS CHAR(4) DEFAULT 'DOWN',
   RET_CD CHAR(2))