PL/I data types supported by the data server

When you map user tables in the metadata catalogs on the data server, Classic Data Architect can convert PL/I data types to native types that the server supports and their corresponding SQL types.

Data types in a CREATE TABLE statement

When you map a new table, you can import data definitions in PL/I include files that describe the layout of the source database and the types that are associated with the data items. The information in the following tables shows how the data server converts PL/I data types to native and SQL types.

When you create a new user table in the metadata catalogs on a data server, you run a CREATE TABLE statement. Typically, this statement is found in the Data Definition Language (DDL) that Classic Data Architect creates automatically when you run the Generate DDL wizard. The CREATE TABLE statement assigns a native data type to each column by using a DATATYPE keyword and assigns an SQL data type in a USE AS clause.

You can also use the information that is shown here to modify the data types that are assigned to columns in a user table. One approach is to edit the DDL in an SQL Editor window. Data types that you assign manually must be appropriate for the data in the source database.

Supported PL/I data types

The following table describes the PL/I data types that the data server supports. The table also provides information about native and SQL types that correspond with PL/I.

Computational data type	Precision and Range	Scale	Native data type	SQL data type
BIT(n)³			B	BINARY(n/8)
CHAR(n)	n <= 32704		C	CHAR(n)
CHAR(n) VARYING	n <= 32704		V	VARCHAR(n)
GRAPHIC(n)	n <= 16352		C	GRAPHIC(n)
GRAPHIC(n) VARYING	n <= 16352		V	VARGRAPHIC(n)
PICTURE (non-numeric)	size <= 32704		C	CHAR(n)
PICTURE (V,9)¹			UC	DECIMAL(p,q)
PICTURE (all other numeric formats)¹	size <= 32704		C	CHAR(n)
BIN FIXED(p,q) UNSIGNED	9 <= p <= 16	q=0	UH	SMALLINT
BIN FIXED(p,q) UNSIGNED	17 <= p <= 32	q=0	UF	INTEGER
BIN FIXED(p,q) UNSIGNED	33 <= p <= 64	q=0	D	DECIMAL(21,0)
BIN FIXED(p,q) SIGNED²	8 <= p <= 15	q=0	H	SMALLINT
BIN FIXED(p,q) SIGNED²	16 <= p <= 32	q=0	F	INTEGER
BIN FIXED(p,q) SIGNED²	32 <= p <= 63	q=0	D	DECIMAL(19,0)
DECIMAL FIXED(p,q)		q>=0	P	DECIMAL(p,q)
DECIMAL FLOAT(p)	1 <= p <= 6		F	REAL
DECIMAL FLOAT(p)	7 <= p <= 16		D	DOUBLE
BIN FLOAT(p)	1 <= p <= 21		F	REAL
BIN FLOAT(p)	22 <= p <= 53		D	DOUBLE
¹PL/I supports many different types of arithmetic character data, such as 9, V, S, +, -, Z, /, R, CR, DB, T, R, K, and F. Values that contain character strings are typically used for report formatting purposes, and the data server maps them as character data. With PL/I picture strings, S, +, and - denote a character, not a sign bit. The data server does not support them as DECIMAL. ²The SIGNED attribute is the default. ³For BIT fields, only lengths that are a multiple of 8 are supported.

Native data types

The following table describes the contents of native data types and their equivalent SQL types. A CREATE TABLE statement specifies the native data type by using the DATATYPE parameter.

The Other SQL data types column lists alternative SQL data types that you can specify as overrides by editing DDL in the SQL Editor window.

Table 1. Native data types
DATATYPE value	Contents	Standard SQL data type	Other SQL data types
C	Mixed mode character data. When the SQL data type is DECIMAL, the data is assumed to consist wholly of numbers with the right most number identifying the sign.	CHAR DECIMAL	DECIMAL, VARCHAR, GRAPHIC, or VARGRAPHIC, BINARY, VARBINARY
P	Packed decimal data where the sign is stored in the far right aggregation of four bits.	DECIMAL	BINARY
D¹	Floating point data. The columns length or precision determines whether the SQL data type is DOUBLE PRECISION or DECIMAL. 64-bit data is mapped as DECIMAL.	DOUBLE PRECISION	FLOAT(`precision`) DECIMAL, BINARY
F²	32-bit signed binary value where the sign is in the high order bit.	INTEGER REAL	FLOAT(`precision`) BINARY
H	16-bit signed binary value where the sign is in the high order bit.	SMALLINT	BINARY
V	Variable mixed-mode character data, where the actual data is preceded by a 16-bit signed binary number that identifies the actual length of the data.	VARCHAR	LONG VARCHAR, VARGRAPHIC, or LONG VARGRAPHIC, VARBINARY
UC	Unsigned zoned-decimal data where the last character does not identify the sign. The value is always a positive value.	DECIMAL	CHAR, BINARY, VARBINARY
UP	Packed decimal data where the sign bit is ignored. The value is always positive.	DECIMAL	BINARY
UF	Unsigned 32-bit binary value.	INTEGER	BINARY
UH	Unsigned 16-bit binary value.	SMALLINT	BINARY
B³	Fixed length binary data.	BINARY	DECIMAL, INTEGER, SMALLINT, VARBINARY
VB³	Variable length binary data, where the actual data is preceded by a 16-bit signed binary number that identifies the actual length of the data.	VARBINARY	N/A
¹The SQL data type is DOUBLE PRECISION or FLOAT. DOUBLE PRECISION is shorthand for an 8-byte floating point number. In the USE AS clause, you can identify this data type as DOUBLE PRECISION or FLOAT(precision). If the precision value is in the range 22–53, the column represents an 8-byte floating point number. If you assign FLOAT, specify the maximum precision. The column length is based on the precision. ²The SQL data type is REAL or FLOAT. REAL is shorthand for a 4-byte floating point number. In the USE AS clause, you can identify this data type as REAL or FLOAT(precision). If the precision value is in the range 1–21, the column represents a 4-byte floating point number. If you assign FLOAT, specify the maximum precision. The column length is based on the precision. ³ Although PL/I does not support native binary types, you can manually edit the DDL to use binary data types. When related to PL/I BIT support, it will be automatically mapped to BINARY.