Relational Data Framework Reference › SQL Statements › ALTER TABLE Statement—Add a Table Column

ALTER TABLE Statement—Add a Table Column

The ALTER TABLE statement, issued with an ADD COLUMN clause, lets you add a column to an existing relational table. A table contains up to 100 columns.

Note: You can use host variables for tablename and colname.

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

  ADDRESS SQL "ALTER TABLE keywords" {tablename}
  

  ADD COLUMN
  {colname}
  {datatype}
  [DEFAULT string|NULL]
  [UPPER CASE]
  [NOT NULL]
  [SYSTEM(ALL|EXT|sysnames)]
  [SYSWAIT(seconds)]
  [OUTPUT|NOOUTPUT]
  [SYSPLEX]
  [SUBSYS(subsystem)]
  

• To invoke the statement from a TSO terminal, a TSO/E REXX program, or a TSO CLIST, use this syntax:

  OPSQL ALTER TABLE {tablename}
  

  ADD COLUMN
  {colname}
  {datatype}
  [DEFAULT 'string'|NULL]
  [UPPER CASE]
  [NOT NULL]
  

tablename

Defines the name of the relational table to which you are adding a column.

Limits: 1-18-character-length log name

colname

Defines the name of the column that you are adding.

Limits: 1-18-character-length log name

datatype

Defines the type of data that the column stores.

Data types include:

CHAR(length)

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

Note: We recommend using data type CHAR for most purposes. If the length of the stored data differs from row to row, and the full potential column length is seldom used, use VARCHAR. CHAR and VARCHAR are processed more efficiently than other data types.

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, with length as 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 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 insert, update, delete, or search for values of these types, specify the data type with the value.

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

DEFAULT string

(Optional) Indicates that the string is a default value to be set for this column, if an INSERT statement does not provide a data value.

Notes:

The default string can be either a character string or a numeric string.

If you are using the OPSQL command processor to invoke the ALTER TABLE statement, enclose the value of the string in single quotes.

NULL

(Optional) Indicates that the column can contain no data. NULL is the default.

UPPER CASE

(Optional) The UPPER CASE keyword in a column definition specifies that the column can contain only uppercase characters. UPPER CASE also instructs SQL to convert any lowercase value in that column to uppercase.

NOT NULL

(Optional) Indicates that the new column cannot contain a null data value.

SYSTEM

(Optional) Performs cross-system SQL operations. Specify one of the following 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.

Note: For more information about the SYSTEM keyword, see 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. To have output returned, specify 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.

Example: Add a Table Column

Add an eight-character column called OPERNAME

ADDRESS SQL
  "ALTER TABLE SYSTEMS ADD COLUMN OPERNAME CHAR(8) NOT NULL"