SQL Data Type Support for All CA Datacom/DB Tables

Basic Language Elements › Data Types › SQL Data Type Support for All CA Datacom/DB Tables

SQL Data Type Support for All CA Datacom/DB Tables

Tables created with prior versions of CA Datacom/DB may contain data types unrecognizable by SQL. For data to be retrieved from those tables, unrecognizable data types must be viewed as recognizable data types.

The chart on the following page shows the SQL-supported values for four CA Datacom Datadictionary FIELD attributes (TYPE, JUSTIFICATION, SIGN, and TYPE-NUMERIC), their meaning, and the equivalent SQL data type. When retrieving data from any column (field) that is defined in CA Datacom Datadictionary with a combination of attribute-values not included in this chart, CA Datacom/DB returns the data to an SQL application as if it were CHARACTER. That is, CA Datacom/DB presents the data without any translation or interpretation.

The CA Datacom Datadictionary FIELD Attributes have the following meanings:

TYPE

Represents CA Datacom Datadictionary FIELD Attributes TYPE

JUST

Represents CA Datacom Datadictionary FIELD Attributes JUSTIFICATION

SIGN

Represents CA Datacom Datadictionary FIELD Attributes SIGN

T-N

Represents CA Datacom Datadictionary FIELD Attributes TYPE-NUMERIC

Binary, halfword - 2 bytes

CA Datacom Datadictionary Valid Entry: B

SQL Data Type: SMALLINT

CA Ideal Support: Supported

Binary, fullword - 4 bytes

CA Datacom Datadictionary Valid Entry: B

SQL Data Type: INTEGER or INT

CA Ideal Support: Supported

Binary, length = 4, SEMANTIC-TYPE=SQL-DATE

CA Datacom Datadictionary Valid Entry: B

SQL Data Type: DATE

CA Ideal Support: Supported

Binary, length = 3, SEMANTIC-TYPE=SQL-TIME

CA Datacom Datadictionary Valid Entry: B

SQL Data Type: TIME

CA Ideal Support: Supported

Binary, length = 10, SEMANTIC-TYPE=SQL-STMP

CA Datacom Datadictionary Valid Entry: B

SQL Data Type: TIMESTAMP

CA Ideal Support: Supported

Character

CA Datacom Datadictionary Valid Entry: C

SQL Data Type: CHARACTER or CHAR

CA Ideal Support: Supported

Character data type with mixed DBCS and SBCS with SEMANTIC-TYPE=MIXED

CA Datacom Datadictionary Valid Entry: C

SQL Data Type: CHARACTER with FOR MIXED DATA

CA Ideal Support: Supported as character

Varying-length character string of length 1 to maximum row size

CA Datacom Datadictionary Valid Entry: V

SQL Data Type: VARCHAR

CA Ideal Support: Supported

V data type with mixed DBCS and SBCS with SEMANTIC-TYPE=MIXED

CA Datacom Datadictionary Valid Entry: V

SQL Data Type: VARCHAR

CA Ideal Support: Supported

V data type with attribute LONG=Y

Varying-length character string whose maximum length is determined by the amount of space available in a block after all fixed-length fields have been subtracted from the blocksize (assumes one record per block). See following notes.

CA Datacom Datadictionary Valid Entry: V

SQL Data Type: LONG VARCHAR

CA Ideal Support: Supported

Packed decimal

CA Datacom Datadictionary Valid Entry: D

SQL Data Type: DECIMAL or DEC, maximum length = 16 bytes

CA Ideal Support: Supported

Numeric, zoned decimal

CA Datacom Datadictionary Valid Entry: N

SQL Data Type: NUMERIC, maximum length = 31 bytes

CA Ideal Support: Supported

Hexadecimal, two-byte hexadecimal display

CA Datacom Datadictionary Valid Entry: H

SQL Data Type: n/a

CA Ideal Support: Not supported

Short floating-point, fullword aligned

CA Datacom Datadictionary Valid Entry: S

SQL Data Type: CHAR

CA Ideal Support: Not supported

Long floating-point, doubleword aligned

CA Datacom Datadictionary Valid Entry: L

SQL Data Type: FLOAT, REAL, DOUBLE PRECISION

CA Ideal Support: Not supported

Extended floating-point, 16-byte aligned

CA Datacom Datadictionary Valid Entry: E

SQL Data Type: CHAR

CA Ideal Support: Not supported

Double-byte character set (DBCS)

See notes following this list.

CA Datacom Datadictionary Valid Entry: Y

SQL Data Type: GRAPHIC

CA Ideal Support: Not supported

Mixed DBCS and single byte

See notes following this list.

CA Datacom Datadictionary Valid Entry: Z

SQL Data Type: CHAR with FOR MIXED DATA

CA Ideal Support: Supported as character

Graphics data

CA Datacom Datadictionary Valid Entry: G

SQL Data Type: GRAPHIC

CA Ideal Support: Not supported

Varying-length double-byte character set of length 2 to maximum row size

CA Datacom Datadictionary Valid Entry: W

SQL Data Type: VARGRAPHIC

CA Ideal Support: Not supported

Varying-length double-byte character set of length 2 to maximum row size

With attribute LONG=Y varying-length double-byte character set whose maximum length is determined by the amount of space available in a block after all fix ed length fields have been subtracted from the blocksize (assumes one record per block). See the following notes.

CA Datacom Datadictionary Valid Entry: W

SQL Data Type: LONG VARGRAPHIC

CA Ideal Support: Not supported

Kanji, same as Y and G

See notes following this list.

CA Datacom Datadictionary Valid Entry: K

SQL Data Type: GRAPHIC

CA Ideal Support: Not supported

PL/I bit representation

CA Datacom Datadictionary Valid Entry: T

SQL Data Type: n/a

CA Ideal Support: Not supported

Halfword binary, aligned

CA Datacom Datadictionary Valid Entry: 2

SQL Data Type: SMALLINT

CA Ideal Support: Not supported

Fullword binary, aligned

CA Datacom Datadictionary Valid Entry: 4

SQL Data Type: INTEGER or INT

CA Ideal Support: Not supported

Doubleword binary, aligned

CA Datacom Datadictionary Valid Entry: 8

SQL Data Type: CHAR(8)

CA Ideal Support: Not supported

Notes:

In CA Datacom Datadictionary the data types K, Y, and Z are being replaced by GRAPHIC and CHARACTER data types. You can use the DDCNVLM utility to update FIELD entity-occurrences with data types of K or Y to G (GRAPHIC) and data type Z to C (CHARACTER) with SEMANTIC-TYPE=MIXED. See the CA Datacom Installation Guide for more information about DDCNVLM.
SMALLINT and INTEGER data types do not allow decimals.
Aligned field types are aligned in records or tables, not in elements and keys.
Those lengths or types not specified as an SQL data type are treated as character through SQL.
VARCHAR (TYPE=V) and VARGRAPHIC (TYPE=W) have a two-byte binary length prefix added to the value you specify for the LENGTH attribute.