Expansion of Data-type

The expanded parameters of data-type specify data types in an SQL data description statement.
idmscu19
The expanded parameters of data-type specify data types in an SQL data description statement.
This article describes the following information:
2
2
Syntax
Expansion of data-type
►►─┬─ BINary ─┬──────────────────────┬─────────────────────────┬──────────────►◄    │          └─ ( ─── 
length
 ─── ) ─┘                         │    ├─ CHARacter ─┬──────────────────────┬──────────────────────┤    │             └─ ( ─── 
length
 ─── ) ─┘                      │    ├─ DATE ────────────────────────────────────────────────────┤    ├─ DECimal ─┬───────────────────────────────────────────┬───┤    │           └─ ( ─── 
precision
 ───┬────────────────┬ ) ─┘   │    │                                 └─ , ─── 
scale
 ──┘        │    ├─ DOUBLE PRECISION ────────────────────────────────────────┤    ├─ FLOAT ─┬─────────────────────────┬───────────────────────┤    │         └─ ( ─── 
precision
 ─── ) ─┘                       │    ├─ GRAPHIC ─┬──────────────────────┬────────────────────────┤    │           └─ ( ─── 
length
 ─── ) ─┘                        │    ├─ INTeger ─────────────────────────────────────────────────┤    ├┬─ LONGINT ─┬──────────────────────────────────────────────┤    │└─ BIGINT ──┘                                              │    ├─ NUMeric ─┬───────────────────────────────────────────┬───┤    │           └ ( ─── 
precision
 ───┬────────────────┬─ ) ─┘   │    │                                └─ , ─── 
scale
 ──┘         │    ├─ REAL ────────────────────────────────────────────────────┤    ├─ SMALLINT ────────────────────────────────────────────────┤    ├─ TIME ────────────────────────────────────────────────────┤    ├─ TIMESTAMP ───────────────────────────────────────────────┤    ├─ UNSIGNED DECimal─┬───────────────────────────────────┬───┤    │                   └─ ( - 
precision
 ─┬────────────┬ ) ─┘   │    │                                     └ , - 
scale
 ─┘        │    ├─ UNSIGNED NUMeric ─┬──────────────────────────────────┬───┤    │                    └ ( - 
precision
 ─┬────────────┬ ) ─┘   │    │                                     └ , - 
scale
 ─┘        │    ├┬─ VARCHAR ───────────┬─┬──────────────────────┬───────────┤    │└─ CHARacter VARYING ─┘ └─ ( ─── 
length
 ─── ) ─┘           │    └─ VARGRAPHIC ─┬──────────────────────┬─────────────────────┘                   └─ ( ─── 
length
 ─── ) ─┘
Parameters
  • BINARY
    Identifies a set of values that are fixed-length bit strings. BINARY values are represented by hexadecimal literals (for example, X'12A5E978').
    • length
      Specifies the number of eight-bit bytes in a BINARY value.
      Length
      must be an integer in the range 1 through 32,760. The default is 1.
      The maximum length of a column with a data type of BINARY is limited by page size and the total length of other columns in the table.
      For more information about the length of a BINARY value, see CREATE TABLE.
  • CHARacter
    Identifies a set of values that are fixed-length single-byte character strings. CHARACTER values are represented by character string literals (for example, 'Past due').
    • length
      Specifies the number of bytes in a CHARACTER value.
      Length
      must be an integer in the range 1 through 32,760. The default is 1.
      The maximum length of a column with a data type of CHARACTER is limited by page size and the total length of other columns in the table.
      For more information about the length of a CHARACTER value, see CREATE TABLE.
  • DATE
    Identifies the set of values that represent valid dates from January 1, 0001, through December 31, 9999. DATE values are represented by character string literals (for example, '1999-07-22').
    The maximum length of a DATE value is 10 bytes. Internally, the length of a DATE value is always eight bytes.
  • DECimal
    Identifies a set of fixed-point, signed packed decimal values. DECIMAL values are represented by exact numeric literals (for example, 17.23).
    The range of values included in the set is determined by the precision and scale specified for the data type. The largest possible DECIMAL value is 10
    31
    -1. The smallest possible DECIMAL value is -(10
    31
    -1).
    • precision
      Specifies the number of digits in a DECIMAL value.
      Precision
      must be an integer in the range 1 through 56. The default is 56.
      The length of a DECIMAL value is equal to the precision plus 1, divided by 2.
    • scale
      Specifies the number of digits to the right of the decimal point in a DECIMAL value.
      Scale
      must be an integer in the range 0 through the precision of the DECIMAL value. The default is 0.
  • DOUBLE PRECISION
    Identifies the set of 64-bit (long) floating-point values with a seven-bit exponent and a binary precision of 56. DOUBLE PRECISION values are represented by approximate numeric literals (for example, 0.1E-16).
    The magnitude that can be represented by a
    positive
    DOUBLE PRECISION value ranges from approximately 5.4E-79 to approximately 7.2E+75. The magnitude that can be represented by a
    negative
    DOUBLE PRECISION value ranges from approximately -5.4E-79 to approximately -7.2E+75.
    The length of a DOUBLE PRECISION value is eight bytes.
  • FLOAT
    Identifies a set of floating-point values with a seven-bit exponent and a user-specified precision. FLOAT values are represented by approximate numeric literals (for example, -1.4E9).
    The magnitude that can be represented by a
    positive
    FLOAT value ranges from approximately 5.4E-79 to approximately 7.2E+75. The magnitude that can be represented by a
    negative
    FLOAT value ranges from approximately -5.4E-79 to approximately -7.2E+75.
    • precision
      Specifies the binary precision of a FLOAT value.
      Precision
      must be an integer in the range 1 through 56. The default is 24.
      If
      precision
      is less than or equal to 24, the length of a FLOAT value is four bytes. If
      precision
      is greater than 24, the length of a FLOAT value is eight bytes.
  • GRAPHIC
    Identifies a set of values that are fixed-length double-byte character strings. GRAPHIC values are represented by double-byte character string literals (for example, G'<####>', where < and > represent the shift-out and shift-in characters and # represents a double-byte character).
    The GRAPHIC data type is a CA IDMS extension of the SQL standard.
    • length
      Specifies the number of characters in a GRAPHIC value. The length in bytes of a GRAPHIC value is equal to the number of bytes in one character times the number of characters.
      Length
      must be an integer in the range 1 through 16,380. The default is 1.
      The maximum length of a column with a data type of GRAPHIC is limited by page size and the total length of other columns in the table.
      For more information about the length of a GRAPHIC value, see CREATE TABLE.
  • INTeger
    Identifies the set of values that are 31-bit signed integers in the range -2,147,483,648 through 2,147,483,647. INTEGER values are represented by exact numeric literals (for example, -2874).
    The length of an INTEGER value is four bytes.
  • BIGINT (or LONGINT)
    Identifies the set of values that are 63-bit signed integers in the range -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807. BIGINT values are represented by exact numeric literals (for example, 2187168).
    The length of a BIGINT value is eight bytes. The keyword LONGINT can be used as a synonym for BIGINT but this is a CA IDMS extension of the SQL standard.
  • NUMeric
    Identifies a set of fixed-point, signed zoned decimal values. NUMERIC values are represented by exact numeric literals (for example, -4.7). The use of NUM as a synonym for NUMERIC is a CA IDMS extension of the SQL standard.
    The range of values included in the set is determined by the precision and scale specified for the data type. The largest possible NUMERIC value is 10
    31
    -1. The smallest possible NUMERIC value is -(10
    31
    -1).
    • precision
      Specifies the number of digits in a NUMERIC value.
      Precision
      must be an integer in the range 1 through 31. The default is 1.
      The length in bytes of a NUMERIC value is equal to the precision.
    • scale
      Specifies the number of digits to the right of the decimal point in a NUMERIC value.
      Scale
      must be an integer in the range 0 through the precision of the NUMERIC value. The default is 0.
  • REAL
    Identifies the set of 32-bit (short) floating-point values with a seven-bit exponent and a binary precision of 24. REAL values are represented by approximate numeric literals (for example, 0.4E52).
    The magnitude that can be represented by a
    positive
    REAL value ranges from approximately 5.4E-79 to approximately 7.2E+75. The magnitude that can be represented by a
    negative
    REAL value ranges from approximately -5.4E-79 to approximately -7.2E+75.
    The length of a REAL value is four bytes.
  • SMALLINT
    Identifies the set of values that are 15-bit signed integers in the range -32,768 through 32,767. SMALLINT values are represented by exact numeric literals (for example, 16433).
    The length of a SMALLINT value is two bytes.
  • TIME
    Identifies the set of values that represent valid times from 00.00.00 through 23.59.59.
    TIME values are represented by character string literals (for example, '13.42.59').
    The maximum length of a TIME value is eight bytes. Internally, the length of a TIME value is always eight bytes.
    A TIME value of 24.00.00 is accepted and treated as 00.00.00.
  • TIMESTAMP
    Identifies the set of values that represent valid date/time combinations with a precision of millionths of a second. Valid dates range from January 1, 0001, through December 31, 9999. Valid times range from 00.00.00.000000 through 23.59.59.999999.
    TIMESTAMP values are represented by character string literals (for example, '1999-05-02-09.46.39.738294').
    The maximum length of a TIMESTAMP value is 26 bytes. Internally, the length of a TIMESTAMP value is always eight bytes.
  • UNSIGNED DECIMAL
    Identifies a set of fixed-point, unsigned packed decimal values. UNSIGNED DECIMAL values are represented by exact numeric literals (for example, 17.23).
    The range of values included in the set is determined by the precision and scale specified for the data type. The largest possible UNSIGNED DECIMAL value is 10
    31
    -1. The smallest possible DECIMAL value is 0.
    The UNSIGNED DECIMAL data type is a CA IDMS extension of the SQL standard.
    • precision
      Specifies the number of digits in an UNSIGNED DECIMAL value.
      Precision
      must be an integer in the range 1 through 31. The default is 1.
      The length of an UNSIGNED DECIMAL value is equal to the precision plus 1, divided by 2.
    • scale
      Specifies the number of digits to the right of the decimal point in an UNSIGNED DECIMAL value.
      Scale
      must be an integer in the range 0 through the precision of the UNSIGNED DECIMAL value. The default is 0.
  • UNSIGNED NUMERIC
    Identifies a set of fixed-point, unsigned zoned decimal values. UNSIGNED NUMERIC values are represented by exact numeric literals (for example, 4.7).
    The range of values included in the set is determined by the precision and scale specified for the data type. The largest possible UNSIGNED NUMERIC value is 10
    31
    -1. The smallest possible NUMERIC value is 0.
    The UNSIGNED NUMERIC data type is a CA IDMS extension of the SQL standard.
    • precision
      Specifies the number of digits in an UNSIGNED NUMERIC value.
      Precision
      must be an integer in the range 1 through 31. The default is 1.
      The length in bytes of an UNSIGNED NUMERIC value is equal to the precision.
    • scale
      Specifies the number of digits to the right of the decimal point in an UNSIGNED NUMERIC value.
      Scale
      must be an integer in the range 0 through the precision of the NUMERIC value. The default is 0.
  • VARCHAR (or CHAR VARYING)
    Identifies a set of values that are variable-length single-byte character strings. VARCHAR values are represented by character string literals (for example, 'Customer address needs to be verified').
    • length
      Specifies the maximum number of characters in a VARCHAR value.
      Length
      must be an integer in the range 1 through 32,758. The default is 1.
      The length of a VARCHAR value is the number of characters in the value. The number of bytes reserved for a VARCHAR value is always the same; the maximum length, plus 2 regardless of the length of the VARCHAR value. A VARCHAR value is preceded by a 2-byte binary length of the value.
      The maximum length of a column with a data type of VARCHAR is limited by page size, the total length of other columns in the table, and other factors.
      For more information about the length of a VARCHAR value, see CREATE TABLE.
  • VARGRAPHIC
    Identifies a set of values that are variable-length double-byte character strings. VARGRAPHIC values are represented by double-byte character string literals (for example, G'<####>', where < and > represent the shift-out and shift-in characters and # represents a double-byte character).
    The VARGRAPHIC data type is a CA IDMS extension of the SQL standard.
    • length
      Specifies the maximum number of characters in a VARGRAPHIC value.
      Length
      must be an integer in the range 1 through 16,379. The default is 1.
      The length of a VARGRAPHIC value is the number of characters in the value. The numbeCREATE TABLEr of bytes reserved for a VARGRAPHIC value is the maximum length times the number of bytes for one character, plus 2. A VARGRAPHIC value is preceded by a 2-byte binary length of the value.
      The maximum length of a column with a data type of VARGRAPHIC is limited by page size and the total length of other columns in the table.
      For more information about the length of a VARGRAPHIC value, see CREATE TABLE.
Usage
Graphics Data
The use of graphics data requires the installation of CA IDMS DBCS.
Example
Defining Table Columns
The following CREATE TABLE statement creates a table with ten columns. Each column is associated with a data type. The data type specification determines the set of values that can occur in the column.
create table job    (job_id           integer        not null,    job_title         character(20)  not null,    job_desc_line_1   varchar(60),    job_desc_line_2   varchar(60),    min_rate          decimal(8,2),    max_rate          decimal(8,2),    salary_ind        character(1),    num_of_positions  smallint,    num_open          smallint,    eff_date          date);