CREATE TABLE Statement—Define Relational Table

Relational Data Framework Reference › SQL Statements › CREATE TABLE Statement—Define Relational Table

CREATE TABLE Statement—Define Relational Table

Issue a CREATE TABLE statement to define a new relational table to CA OPS/MVS.

The CA OPS/MVS product supports up to 1000 relational tables. You can define as many as 100 columns per table, with as many rows as you need. Each row can contain up to 16,000 characters.

To create a table, invoke an SQL statement using the keywords and arguments that are shown on the following pages. For each column, include a separate column definition clause in your CREATE TABLE statement. Separate the column definition clauses with commas.

For a list of reserved keywords in SQL statements, see the chapter “Using the Relational Data Framework” in the User Guide. When specifying columns for relational tables, do not use these keywords as column names.

When using the CREATE TABLE statement:

You can use host variables for tablename and colname.
The following minimum and maximum lengths apply to columns of the specified data type:

Data Type	Minimum - Maximum
CHAR	1 - 16000
DECIMAL	1 - 15
HEX	1 - 256
TIME	8 - 15
TIMESTAMP	19 - 26
VARCHAR	1 - 16000

Use this syntax in an AOF rule or an OPS/REXX program:

ADDRESS SQL "CREATE [GLOBAL TEMPORARY] TABLE keywords"

{tablename}
{datatype}
/* For every column that you define, you may specify one or more of */
/* these keywords. If you use them, specify them after the datatype */
/* keyword, in the closing parentheses. Specify them in the order   */
/* shown:                                                           */
[UPPER CASE]
[NOT NULL]
[PRIMARY KEY]
[DEFAULT 'string'|NULL]
/* You may specify these keywords only once for each CREATE TABLE statement: */
[SYSTEM(ALL|EXT|sysnames)]
[SYSWAIT(seconds)]
[OUTPUT|NOOUTPUT]
[SYSPLEX]
[SUBSYS(subsystem)]

Use this syntax to invoke the statement from a TSO terminal, a TSO/E REXX program, or a TSO CLIST:

OPSQL CREATE [GLOBAL TEMPORARY] TABLE

{tablename(colname datatype)}
/* For every column that you define, you may specify one or more of */
/* these keywords. If you use them, specify them after the datatype */
/* keyword, in the closing parentheses. Specify them in the order   */
/* shown:                                                           */
[UPPER CASE]
[NOT NULL]
[PRIMARY KEY]
[DEFAULT 'string'|NULL]

You may specify these operands for the CREATE TABLE statement:

tablename: Defines the 1- to 18-character name of the table you create.
colname: Defines the 1- to 18-character name of a column that you define for this table.

Note: For more information about naming columns, see Reserved Keywords in SQL Statements in the chapter “Using the Relational Data Framework” in the User Guide.

datatype

Defines the type of data the column stores. Data types include:

CHAR(length)

Specifies character data; length specifies the fixed number of characters you want this column to store.

Note: We recommend that you use the data type CHAR.

DATE

Specifies the date, in the format yyyy-mm-dd.

Example: 1994-07-25.

DECIMAL

Specifies decimal data.

Limits: 1-15 digits

HEX(length)

Specifies hexadecimal data; (length) specifies the maximum number of hexadecimal bytes this column stores.

INTEGER

Specifies full-word integer data.

Limits: maximum value of 2147483647

SMALLINT

Specifies half-word integer data.

Limits: maximum value of 32767

TIME(length)

Specifies the time, in the format hh:mm:ss.nnn.

Example: 13:21:53.876

Note: If a value for length is not specified, the default value is 8.

TIMESTAMP(length)

Specifies the date and time, in a format that combines the DATE and TIME formats.

Note: If a value for length is not specified, the default value is 20.

VARCHAR(length)

Specifies character data; length represents the maximum number of characters this column stores.

Notes:

Data of type CHAR is stored in fixed lengths by RDF and padded, as necessary, with blank characters. Data of type VARCHAR is stored in variable lengths by RDF and not padded. Use of the VARCHAR data type saves space in the CA OPS/MVS global variable pool. Space is saved when the pool is used to store data that is shorter than the maximum defined length of a column. VARCHAR data types cannot be defined as primary keys because of their variable length values.

The DATE, TIME, and TIMESTAMP data types are stored as unsigned packed decimal numbers. When you are inserting, updating, deleting, or searching for values of these types, specify the data type with the value.

Example: WHERE UPDATE = DATE '2004-04-27'

GLOBAL TEMPORARY

(Optional) Creates a table definition in the SYSCKH1 DIV data set enables you to store relational table data. Include the GLOBAL TEMPORARY operand on a CREATE TABLE statement. Although the table definition is permanent, the table rows are temporary. CA OPS/MVS deletes the table rows when it stops.

To create a table definition, use the following syntax:

ADDRESS SQL

"CREATE GLOBAL TEMPORARY TABLE (rest of SQL statement...)"

UPPER CASE

(Optional) Indicates that the column can contain only uppercase characters. UPPER CASE also instructs SQL to convert any lowercase value in that column to uppercase. Specify UPPER CASE if you want INSERT and UPDATE statements to always translate data to uppercase before storing that data in the table.

In the CA OPS/MVS version of SQL, all string data comparisons are case-sensitive.

NOT NULL

(Optional) Indicates that the column can never contain a null data value.

PRIMARY KEY

(Optional) Designates a column as the primary key for this table. The primary key parallels the concept of keyed files and improves Relational Data Framework performance.

Note: VARCHAR data type columns cannot be specified as primary keys.

You can define up to ten columns as the primary key. However, columns must be defined sequentially and contiguous to each other, and their combined length cannot exceed 71 characters. If you do not identify a primary key, the Relational Data Framework stores rows in the order you added them. We recommend that you always specify at least one column as the primary key for every table. Processing nonkeyed tables with the table editor requires significant overhead.

When you designate more than one column as a primary key, you can enter PRIMARY KEY next to each column definition. Alternatively, you can enter the column definitions, then enter PRIMARY KEY(firstcol-lastcol) at the end of the column definitions.

Note: The firstcol and lastcol variables represent the first and last sequentially contiguous columns of the primary key.

When the CA OPS/MVS table editor displays a table definition, you cannot enter new data into a PRIMARY KEY field. To change the contents of this field, perform the following actions:

Copy the line that contains the PRIMARY KEY field.

Edit the PRIMARY KEY field on the new line.

Delete the original line.

Note: We recommend that you use CHAR data type columns for the primary key.

DEFAULT string

(Optional) The string is a default value to be set for this column when an INSERT statement does not provide a data value. The default string can be either a character string or a numeric string. If you are using the OPSQL command processor to invoke the CREATE TABLE statement, enclose the value of the string in single quotes.

NULL

(Default) Indicates that the column can contain no data.

SYSTEM

(Optional) Performs cross-system SQL operations. Specify one of these values:

ALL: Routes the SQL command to all active MSF-defined systems, including the local system.
EXT: Routes the SQL command to all remote, active MSF-defined systems.
sysnames: Routes the SQL command to the specified systems. You may specify from 1 to 8 system names as the value of sysnames.

Note: For more information about the SYSTEM operand, see the chapter “Using the Relational Data Framework” in the User Guide.

SYSWAIT

(Optional) Defines the number of seconds the SQL processor waits for output from a remote system.

Limits: 1-300 seconds.

Note: Do not specify a value for SYSWAIT if you specify the SYSTEM(ALL), SYSTEM(EXT), or NOOUTPUT keywords.

OUTPUT or NOOUTPUT

(Optional) Indicates whether the command returns output to the external data queue. Specify OUTPUT to return output; otherwise, specify NOOUTPUT.

NOOUTPUT is implied when:

The ADDRESS SQL command is issued in an AOF rule.
You specify more than one system name as the value of the SYSTEM(sysnames) keyword.
You specify SYSTEM(ALL) or SYSTEM(EXT).

SYSPLEX: (Optional) Reduces the scope of the SYSTEM(ALL|EXT) operand to MSF connected systems that belong to the same z/OS sysplex as the command issuer. The keyword has no effect on a list of explicit system names.

SUBSYS

(Optional) Performs SQL operations on other CA OPS/MVS subsystems that are in communication with the current one. Specify the following value:

subsystem: Routes the SQL command to the specified subsystem. You can use only one four-character name of the subsystem as follows: OPSx.

Examples: CREATE TABLE

The following examples illustrate the use of CREATE TABLE.

Example 1-Designate a Primary Key

This CREATE TABLE statement sets up the SYSTEMS table. The RECOV_PROC column stores the desired name of a REXX program to invoke when the current and desired states of a system do not match.

ADDRESS SQL
  "CREATE TABLE SYSTEMS (NAME CHAR(8) PRIMARY KEY,",
               "CURRENT_STATE CHAR(4),",
               "DESIRED_STATE CHAR(4),",
               "RECOV_PROC    CHAR(8))"

The NAME column stores the system name. Because system names should be unique and provide an easy way to refer to rows in the table, the NAME column is defined as the primary key.

Example 2-Invoke the CREATE TABLE Statement from a TSO CLIST

This TSO CLIST creates a relational table named DAILY_SCHEDULE.

PROC 0
 CONTROL MSG CONLIST SYMLIST
     OPSQL   CREATE TABLE DAILY_SCHEDULE                 +
        (NAME         CHAR(8) NOT NULL PRIMARY KEY,      +
         EVENT        CHAR(4) NOT NULL,                  +
         TYPE         CHAR(1) UPPER CASE,                +
         STATUS       CHAR(1) UPPER CASE,                +
         SCHED_DATE   DATE,                              +
         SCHED_TIME   TIME,                              +
         REPEAT_TIME  TIME,                              +
         RUN_DATE     DATE,                              +
         RUN_TIME     TIME,                              +
         END_DATE     DATE,                              +
         END_TIME     TIME,                              +
         MAX_CC       CHAR(4),                           +
         PREREQ1      CHAR(20),                          +
         PRETYPE1     CHAR(1),                           +
         PREREQ2      CHAR(20),                          +
         PRETYPE2     CHAR(1),                           +
         PREREQ3      CHAR(20),                          +
         PRETYPE3     CHAR(1)) SUB(OPSF)
WRITE &SQLCODE
WRITE &SYSOUTLINE
WRITE &LASTCC

Example 3-Case-sensitive Comparison A

In the CA OPS/MVS version of SQL, all string data comparisons are case-sensitive. Examples 3 and 4 deal with case-sensitivity issues.

For example, this sample CREATE TABLE statement sets up the SYSTEMS table:

ADDRESS SQL
  "CREATE TABLE SYSTEMS (NAME CHAR(8) PRIMARY KEY,",
               "CURRENT_STATE CHAR(4),",
               "DESIRED_STATE CHAR(4),",
               "RECOV_PROC    CHAR(8))"

Now suppose that the NAME column in the SYSTEMS table contains the lowercase value 'cics'. The following SELECT statement asks SQL to return all columns containing the characters 'cics':

SELECT * FROM SYSTEMS WHERE NAME = 'cics'

This statement returns the 'cics' value from the NAME column, because it matches the lowercase 'cics' specified on the SELECT statement.

Example 4-Case-sensitive Comparison B

Now suppose that you are still using the CREATE TABLE statement shown in Example 3 and that the NAME column in the SYSTEMS table still contains the lowercase value 'cics'. However, your SELECT statement is as follows:

SELECT * FROM SYSTEMS WHERE NAME = 'CICS'

This SELECT statement does not return the value 'cics' from the NAME column, because the uppercase search criteria 'CICS' does not match the lowercase column value 'cics'.