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 shown on the following pages. For each column, include a separate column definition clause in your CREATE TABLE statement. You must 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. The list may grow in future releases of the SQL standard component.

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:

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]

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 are creating.

colname

Defines the 1- to 18-character name of a column that you are defining for this table. For special considerations when 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 will store. Data types include:

CHAR(length)

Character data, with length as the fixed number of characters you want this column to store.

Note: We recommend that the data type CHAR be used.

DATE

A date indicator of the form yyyy-mm-dd; for example, 1994-07-25.

DECIMAL

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

HEX(length)

Hexadecimal data, with length as the maximum number of hexadecimal bytes this column will store.

INTEGER

Full-word integer data, with a maximum value of 2147483647.

SMALLINT

Half-word integer data, with a maximum value of 32767.

TIME(length)

A time indicator of the form hh:mm:ss.nnn; for example, 13:21:53.876. If a value for length is not specified, then the default value of 8 is used.

TIMESTAMP(length)

A date/time indicator, the format being a combination of the DATE and TIME formats. If a value for length is not specified, then the default value of 20 is used.

VARCHAR(length)

Character data, with length as the maximum number of characters this column will store.

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 when it is used to store data that is often significantly less 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, you must specify the data type along with the value; for example:

  WHERE UPDATE = DATE '2004-04-27'
  

GLOBAL TEMPORARY

(Optional) Creates a table definition in the SYSCKH1 DIV data set that will enable 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 and will be deleted when CA OPS/MVS stops.

Use the following syntax to create a table definition:

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

UPPER CASE

(Optional) Indicates that the column can contain only uppercase characters. It also instructs SQL to convert any lowercase value inserted 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 10 columns as the primary key; however, they must be defined sequentially 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 in which you added them. We strongly recommend that you always specify at least one column as the primary key for every table. There is a significant amount of overhead in processing non-keyed tables with the table editor.

When designating more than one column as a primary key, you can enter PRIMARY KEY beside each column definition. Alternately, 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, you can copy the line that contains it, make changes to the PRIMARY KEY field on the new line, and then delete the original line.

Note: We strongly 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 if 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, you must enclose the value of 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 one to eight system names as the value of sysnames.

For more information, 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. You may specify a value between 1 and 300 seconds.

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 have output returned; 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.

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'.