Was this helpful?
Abstract Data Types
Abstract data types include the following:
Money
Numeric string
Logical
IP network address
Universal unique identifier
Money Data Type
The money data type is an abstract data type. Money values are stored significant to two decimal places. These values are rounded to their amounts in dollars and cents or other currency units on input and output, and arithmetic operations on the money data type retain two-decimal-place precision.
Money columns can accommodate the following range of values:
$-999,999,999,999.99 to $999,999,999,999.99
A money value can be specified as either:
A character string literal—The format for character string input of a money value is $sdddddddddddd.dd. The dollar sign is optional and the algebraic sign (s) defaults to + if not specified. There is no need to specify a cents value of zero (.00).
A number—Any valid integer or floating point number is acceptable. The number is converted to the money data type automatically.
On output, money values display as strings of 20 characters with a default precision of two decimal places. The display format is:
$[-]dddddddddddd.dd
where:
$ is the default currency symbol
d is a digit from 0 to 9
The following settings affect the display of money data. For details, see the System Administrator Guide.
Variable
Description
II_MONEY_FORMAT
Specifies the character displayed as the currency symbol. The default currency sign is the dollar sign ($). II_MONEY_FORMAT also specifies whether the symbol appears before or after the amount.
II_MONEY_PREC
Specifies the number of digits displayed after the decimal point; valid settings are 0, 1, and 2.
II_DECIMAL
Specifies the character displayed as the decimal point; the default decimal point character is a period (.). II_DECIMAL also affects FLOAT, FLOAT4, and the DECIMAL data types.
Note:  If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;
Numeric String Data Type
The numeric string data type is a virtual type that exists only when numeric data types are compared directly with character data. If a comparison is requested between these two classes of data, the character data is examined to see if it is numeric in form. If it is, then the comparison is performed as though both were numeric. If the data is not numeric, the result will be such that all numbers collate before all non-numbers. For example:
–100 < ‘-9’ < ‘0.01’ < 1 < ‘1.1e1’ < ‘one’ < ‘three’ < ‘two’
The numeric conversion is performed using float8 precision and leading and trailing spaces are ignored.
The data types considered as numeric are all the integer types, all the float types, and decimal. Money is not treated in this manner because it has its own character data compatibility.
To be deemed numeric, a character data value must contain only one number, which can be in integer, decimal, float, or scientific notation form. All the character data types are checked for numeric content except the long variants, which are treated as non-numeric.
Logical Key Data Type
The logical key data type allows the DBMS Server or your application to assign a unique key value to each row in a table. Logical keys are useful when an application requires a table to have a unique key, and the columns of the table do not comprise a unique key.
There are two types of logical keys:
SYSTEM_MAINTAINED—The DBMS Server automatically assigns a unique value to the column when a row is appended to the table. Users or applications cannot change system maintained columns. When the column is created, the DBMS Server assigns it the option NOT NULL WITH DEFAULT. An error is returned if any option other than NOT NULL WITH DEFAULT is specified.
NOT SYSTEM_MAINTAINED—The DBMS Server does not assign a value to the column when a row is appended. Your application must maintain the contents of the column; users and application programs can change logical key columns that are not system maintained. The default for logical key columns is NOT SYSTEM_MAINTAINED.
Specify the scope of uniqueness for SYSTEM_MAINTAINED logical key columns using the following options:
TABLE_KEY—Values are unique within the table.
OBJECT_KEY—Values are unique within the database.
If two or more SYSTEM_MAINTAINED logical key columns of the same type (OBJECT_KEY or TABLE_KEY) are created within the same table, the same value is assigned to all columns of the same type in a given row. Different values are assigned to object and table key columns in the same row, as shown in the following diagram:
TABLE_KEY values are returned to embedded SQL programs as 8-byte strings, and OBJECT_KEY values as 16-byte strings. Values can be assigned to logical keys that are NOT SYSTEM_MAINTAINED using string literals. For example:
INSERT INTO keytable(table_key_column) VALUES('12345678');
Values assigned to TABLE_KEYs must be 8-byte strings; values assigned to OBJECT_KEYs must be 16-byte strings.
In a UTF-8 environment, logical keys must be passed as type BYTE.
Restrictions on Logical Keys
When working with logical keys, be aware of the following restrictions:
A SYSTEM_MAINTAINED logical key column cannot be created using the CREATE TABLE...AS SELECT statement. A NOT SYSTEM_MAINTAINED data type is assigned to the resulting column.
The COPY statement cannot be used to load values from a file into a SYSTEM_MAINTAINED column.
IP Network Address Data Types
IPV4 and IPV6 are abstract data types that store IPv4 and IPv6 host addresses, respectively, in binary format.
IPV4 is a 4-byte host address in dotted-decimal notation (four decimal numbers, each ranging from 0 to 255, separated by dots). For example: 172.16.254.1.
IPV6 is a 16-byte host address in eight groups of four hexadecimal digits separated by colons. For example: 2001:0db8:85a3:0042:1000:8a2e:0370:7334.
We recommend using the IPV4 and IPV6 data types instead of plain text to store network addresses because they provide input error checking and specialized operators and functions.
An IPv4 address can be stored in an IPv6-type value, but the address will be stored as an IPv4-mapped IPv6 address. When coerced to type IPV6, a “pure” IPv4 address cannot be distinguished from a mapped IPv4 address—for example, “192.168.0.1” cannot be distinguished from “::ffff:c0a8:1” or from “::ffff:192.168.0.1”.
Comparisons between two IPV4 or two IPV6 values operate like BYTE comparisons (that is, unsigned byte-wise). IPV6 is the “higher” type in mixed expressions.
IPV4 and IPV6 addresses are stored as 4 and 16 bytes, respectively.
Note:  CIDR notation (for example: 192.168.100.0/24) is not supported.
Example input and output:
IPV4 or IPV6 Input
Output
172.16.254.1
172.16.254.1
2001:db8:0:1234:0:567:8:1
2001:db8:0:1234:0:567:8:1
2001:db8:0:0:0:0:0:0
2001:db8::
::ffff:c0a8:1
192.168.0.1
Universal Unique Identifier (UUID) Data Type
A Universal Unique Identifier (UUID) is a 128-bit, unique identifier generated by the local system upon request or loaded from external sources.
The database can generate UUID values with the UUID() function. The identifier is unique across both space and time with respect to the space of all UUIDs. UUID values generated are Version 1 (time-based) UUIDs.
A UUID can be used to tag records to ensure that the database records are uniquely identified regardless of which database they are stored in, for example, in a system where there are two separate physical databases containing accounting data from two different physical locations.
No centralized authority is responsible for assigning UUIDs. They can be generated on demand (10 million per second per machine if needed).
A UUID can be used for multiple purposes:
Tagging objects that have a brief life
Reliably identifying persistent objects across a network
Assigning as unique values to transactions as transaction IDs in a distributed system
UUIDs are fixed sized (128 bits), which is small relative to alternatives. This fixed small size lends itself well to sorting, ordering, and hashing of all sorts, sorting in databases, simple allocation, and ease of programming.
UUID Format
The format of 128-bits (16 octets) UUID is:
Field
Data Type
Octet Number
Note
time_low
unsigned 32-bit integer
0-3
The low field of the timestamp
time_mid
unsigned 16-bit integer
4-5
Time middle field of the timestamp
time_hi_and_version
unsigned 16-bit integer
6-7
The high field of the timestamp multiplex with the release number
clock_seq_hi_and_reserved
unsigned 8-bit integer
8
The high field of the clock sequence multiplex with the variant
clock_seq_low
unsigned 8-bit integer
9
The low field of the clock sequence
node
unsigned 48-bit integer
10-15
The spatially unique node identifier
Last modified date: 02/03/2024