Description of Field Types and query SQL statements for Structure Analysis of INFORMIX Data Tables

SQL statement used to query fields of all INFORMIX data tables:

Select a. tabname, B. colname, B. coltype,
Case B. coltype
When '0' then 'Char'
When '1' then 'smallint'
When '2' then' integer'
When '3' then 'float'
When '4' then 'smallfloat'
When '5' then 'decimal'
When '6' then 'serial'
When '7' then 'date'
When '8' then 'money'
When '9' then 'null'
When '10' then 'datetime'
When '11' then 'byte'
When '12' then 'text'
When '13' then 'varchar'
When '14' then 'interval'
When '15' then 'nchar'
When '16' then 'nvarchar'

When '17' then 'int8'
When '18 'then' serial8'
When '19' then'set'
When '20' then' Multiset'
When '21' then'list'
When '22 'then' unnamed row'
When '40' then 'lvarchar'
When '41' then 'clob'
When '43 'then' blob'
When '44' then'boolean'
When '000000' then 'Char'
When '000000' then 'smallint'
When '000000' then' integer'
When '000000' then 'float'
When '100' then'real'
When '000000' then 'decimal'
When '000000' then'serial'
When '000000' then 'date'
When '000000' then 'money'
When '000000' then 'datetime'
When '000000' then 'byte'
When '000000' then 'text'
When '000000' then 'varchar'
When '000000' then 'interval'
When '000000' then 'nchar'
When '000000' then 'nvarchar'

When '000000' then 'int8'
When '000000' then 'serial8'
When '000000' then'set'
When '100' then' et'
When '000000' then 'LIST'
When '000000' then 'unnamed row'
When '000000' then 'lvarchar'
When '000000' then 'clob'
When '000000' then 'blob'
When '000000' then'boolean'

When '100' then' named row'
End as coltypename, B. colno from orders Ables a left join syscolumns B on. tabid = B. tabid where. tabid> 99 and. tabtype = 't'order by. tabname, B. colno;

 

Informix fields are of the following types:

Field Type	Description	Field Length and default value
Char (size)	It is used to save string data of a fixed length (size) byte.	The fixed length of each line (the remaining part is filled with spaces). The maximum length is 254 bytes. The default value is 1 byte in each line.
Varchar (size)	Used to save variable-length string data. The maximum byte length is specified by (size.	The length of each line is variable. The maximum length is 254 bytes. The default data length is 1.
Serial	Automatic growth type.	Storage by integer type.
Nchar (size)	Double-byte string.	The fixed length of each line (the remaining part is filled with spaces ). Length (size) refers to the number of characters, the maximum number of bytes is 254*2.
Nvarchar (size)	Variable Length, double-byte string.	Each line gets longer. Length (size) refers to the number of characters, the maximum number of bytes is size * 2.
Real	Save the floating point data type. The value range is-2.23e-308 to + 2.23e-308.	The maximum length is 8 bytes.
Interval	Specifies a time span.	(5-3) year to month, (11: 15) hour to minute.
Date	Date type.	Each row is fixed to 10 bytes in length.
Datetime	Date and Time type.	Each line is fixed to 8 bytes in length.
Integer	Stores integer data in the range of-2147483648 to + 2147483647.	The maximum length is 4 bytes 32-1 bytes or 4 GB.
Smallint	Stores integer data in the range of-32768 to + 32767.	The maximum length is 2 bytes 32-1 bytes or 4 GB.
Float	Save the floating point data type. The value range is-2.23e-308 to + 2.23e-308.	The maximum length is 8 bytes.
Smallfloat	Save the floating point data type. The value range is-2.23e-308 to + 2.23e-308.	The maximum length is 8 bytes.
Money (P, S)	The data type of the exact value is saved. The value range is-10e-38 to + 10e-38. P indicates the precision and the total decimal places (1 ~ 38), S is the decimal point (decimal point less than or equal to P)	The maximum length is 2 ~ 17 bytes.
Decimal (P, S)	The data type of the exact value is saved. The value range is-10e-38 to + 10e-38. P indicates the precision and the total decimal places (1 ~ 38), S is the decimal point (decimal point less than or equal to P)	The maximum length is 2 ~ 17 bytes.
Text	Save text data within 2 GB.	The maximum length is 2 GB.
Byte	Saves binary data within 2 GB.	The maximum length is 2 GB.