ArpeDb2 — Data types
How IBM Db2 types map to Apache Arrow on the fetch (read) path, and how Arrow types map back to Db2 on the bind and ingest (write) path. The tables apply to Db2 for LUW and Db2 for i; the few differences are listed in Db2 for i: what differs from Db2 for LUW.
SQL type name in the field metadata
Every field of a result carries the Db2 column type as the Arrow field
metadata ARPEDB2:type, written as in Db2 DDL with the declared size:
CHAR(3), VARCHAR(40), DECIMAL(9,2), CLOB(1048576), TIMESTAMP(9), and
so on. Bulk ingest in create mode reads it back, so a table copied from Db2
to Db2 keeps its column types (see
Keeping Db2 column types).
table = cur.fetch_arrow_table()
for field in table.schema:
print(field.name, field.type, field.metadata[b"ARPEDB2:type"].decode())
Read path: Db2 → Arrow
Numeric
| Db2 type | Arrow type | Notes |
|---|---|---|
SMALLINT | int16 | |
INTEGER | int32 | |
BIGINT | int64 | |
REAL | float32 | |
DOUBLE / FLOAT | float64 | |
DECIMAL(p,s) / NUMERIC(p,s) | decimal128(p,s) | Precision and scale kept, no detour through text. |
DECFLOAT(16) / DECFLOAT(34) | float64 | The nearest double: DECFLOAT(34) holds 34 digits, a double about 16. For the exact value, set adbc.arpedb2.decfloat_as to string. |
DECFLOAT with decfloat_as=string | utf8 | The exact value, written as Db2 writes VARCHAR(decfloat): every digit and trailing zero kept (1.50), scientific notation for very large or small values (1.2E+41), Infinity, -Infinity, NaN, sNaN. Since v0.3.6. |
Character
| Db2 type | Arrow type | Notes |
|---|---|---|
CHAR(n) | utf8 | Converted to UTF-8 from the column's CCSID. Padding kept. |
VARCHAR(n), LONG VARCHAR | utf8 | Converted to UTF-8 from the column's CCSID. |
GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC | utf8 | UTF-16 converted to UTF-8. |
CLOB | utf8 | Converted to UTF-8 from the column's CCSID. |
DBCLOB | utf8 | UTF-16 converted to UTF-8. |
XML | utf8 | The serialized document, as the server serializes it (Db2 for LUW: UTF-8, no XML declaration). For a declaration or a set encoding, select XMLSERIALIZE(col AS CLOB INCLUDING XMLDECLARATION). Since v0.3.6. |
CHAR(n) / VARCHAR(n) / LONG VARCHAR FOR BIT DATA | binary | utf8 with adbc.arpedb2.bit_data_ccsid (see below). |
BINARY(n), VARBINARY(n) | binary | |
BLOB | binary |
BLOB, CLOB, DBCLOB and XML values are read whole, up to
adbc.arpedb2.max_lob_bytes (64 MiB by default). A larger value fails the
read with an error naming the column; it is never cut short.
Text encoding. Db2 for LUW sends text as UTF-8. Db2 for i sends each column in its own CCSID, usually an EBCDIC code page, and the driver converts it:
- single-byte EBCDIC: 37, 273, 277, 278, 280, 284, 285, 297, 500, 871, 1047, and the euro variants 1140 to 1149;
- Unicode and ASCII: 1208 (UTF-8), 1200 and 13488 (UTF-16), 367 and 819;
- Japanese (930, 939, 1390, 1399; double-byte 300, 16684), Simplified Chinese (935, 1388; 837, 4933), Traditional Chinese (937; 835) and Korean (933, 1364; 834, 4930). Since v0.3.5.
Any other CCSID fails with an error naming the column and its CCSID, and so does a byte with no Unicode mapping: no character is ever replaced silently. Text you bind as a parameter is always sent as UTF-8, and the server converts it to the column's CCSID.
FOR BIT DATA as text. Older tables often keep text in FOR BIT DATA
columns, which read as binary. Set adbc.arpedb2.bit_data_ccsid to the
CCSID that text is in (for example 37 or 273 for EBCDIC, 819 for
Latin-1, 1208 for UTF-8): those columns then read as utf8. Every
FOR BIT DATA column of the result is converted with the one CCSID, and a
value with a byte the CCSID cannot decode fails with an error naming the
column. BINARY, VARBINARY and BLOB stay binary. Since v0.3.8.
NCHAR on Db2 for LUW. In a Unicode database, NCHAR(n) holds n
characters in up to 4n bytes of UTF-8, and Db2 describes it to the driver as
VARCHAR(4n). An NCHAR(3) column therefore reads as utf8 with
ARPEDB2:type VARCHAR(12). The value is right, blank-padded to n
characters. NVARCHAR(n) reads as VARCHAR(4n) and NCLOB(n) as CLOB(4n).
Temporal
| Db2 type | Arrow type | Notes |
|---|---|---|
DATE | date32 | A value that is not a real date fails the read with an error naming the column. |
TIME | time32[s] | Db2's 24.00.00 reads as 86400 seconds. |
TIMESTAMP(p) | timestamp[us] (no time zone) | Db2's default TIMESTAMP is TIMESTAMP(6). The extra digits of TIMESTAMP(7) to (12) are cut to microseconds unless adbc.arpedb2.timestamp_precision is exact. |
TIMESTAMP(7) to (9) with timestamp_precision=exact | timestamp[ns] (no time zone) | Every digit kept. timestamp[ns] only holds 1677-09-21 to 2262-04-11: a value outside fails the read (read it with micro, or cast it to VARCHAR). Since v0.3.6. |
TIMESTAMP(10) to (12) with timestamp_precision=exact | utf8 | Arrow has no unit finer than the nanosecond, so the value comes as text with every digit: 2023-05-15 13:45:30.123456789012. |
Boolean
| Db2 type | Arrow type | Notes |
|---|---|---|
BOOLEAN on Db2 for i | bool | |
BOOLEAN on Db2 for LUW | int16 | 0 or 1. Db2 for LUW describes a BOOLEAN column to the driver as SMALLINT. |
On Db2 for i, a describe without execution (ExecuteSchema,
GetParameterSchema) reports a BOOLEAN column or parameter as binary
(BINARY(2)), which is how IBM i describes it before the query runs. The
query itself returns bool.
Vector (Db2 12.1.2 and later)
| Db2 type | Arrow type | Notes |
|---|---|---|
VECTOR(n, FLOAT32) | utf8 | The vector's text, for example [1.5,-2.25,3]. |
VECTOR(n, INT8) | utf8 | The vector's text, for example [1,-2,127]. |
Db2 never sends a vector to a remote client as a vector, IBM's own drivers
included: it describes the column as a VARCHAR (a CLOB for large
dimensions) and sends the same text VECTOR_SERIALIZE returns. The driver
cannot tell such a column from a real VARCHAR, so it returns utf8. The
text is exact: FLOAT32 elements carry 9 significant digits.
GetObjects reads the catalog and does report the column as VECTOR.
To get Arrow lists, parse the text on the client:
import json
import pyarrow as pa
text = table.column("EMBEDDING")
vectors = pa.array([None if v is None else json.loads(v) for v in text.to_pylist()],
pa.list_(pa.float32(), 768))
Types that are not mapped
A column of any other type (row types, structured user-defined types) fails
the read with an unmappable SQLTYPE error naming the column. Cast it to a
supported type in the query.
Db2 for i: what differs from Db2 for LUW
Checked type by type on IBM i 7.5.
| Db2 for i | Db2 for LUW | What ArpeDb2 returns on Db2 for i |
|---|---|---|
| Text in each column's CCSID, usually EBCDIC | UTF-8 | utf8, converted. |
NCHAR(n), NVARCHAR(n), NCLOB(n) are UTF-16 GRAPHIC, VARGRAPHIC, DBCLOB | character types in UTF-8 | utf8, with ARPEDB2:type GRAPHIC(n), VARGRAPHIC(n), DBCLOB(n). |
NUMERIC(p,s) is zoned decimal | a synonym of DECIMAL | decimal128(p,s), with ARPEDB2:type NUMERIC(p,s). |
DECIMAL / NUMERIC up to 63 digits | up to 31 | decimal128(31,s): see below. |
BOOLEAN is a real Boolean | sent as SMALLINT | bool. |
ROWID | no such type | binary (26 bytes on IBM i 7.5), with ARPEDB2:type ROWID. |
DATALINK(n) | no such type | utf8 holding the URL. Select DLCOMMENT(col) for the comment. |
no VECTOR | VECTOR (12.1.2 and later) | n/a |
Everything else reads as in the tables above, with the same options
(decfloat_as, timestamp_precision, bit_data_ccsid).
DECIMAL wider than 31 digits (Db2 for i)
Db2 for i allows DECIMAL and NUMERIC up to 63 digits, but describes such a
column to a DRDA client as DECIMAL(31,s), so it reads as
decimal128(31,s). Values that fit 31 digits read normally. For a value that
does not, the server ends the result with a warning, and the query fails with
SQLCODE +802 and SQLSTATE 01519. To read every digit, cast the column to
text in the query, for example CAST(D AS VARCHAR(70)).
Write path: Arrow → Db2
Bound parameters and bulk ingest send each Arrow column to the Db2 type the server describes for it. The driver sets no limit on the number of parameters of a statement, on the number of columns of an ingested table or on the length of the SQL text: the server's limits apply.
| Db2 target | Arrow types accepted |
|---|---|
SMALLINT, INTEGER, BIGINT | any integer type, and bool (as 0 or 1); out-of-range values are refused |
REAL, DOUBLE | float32 / float64 matching the target |
DECIMAL | decimal128, decimal256 (the value is rescaled to the column's scale, and a value that would lose a non-zero digit is refused); any integer type |
CHAR, VARCHAR | utf8, large_utf8; for FOR BIT DATA columns also binary, large_binary, fixed_size_binary |
GRAPHIC, VARGRAPHIC | utf8, large_utf8 |
BINARY, VARBINARY | binary, large_binary, fixed_size_binary |
DATE | date32; date64 and timestamp without a time zone, when the value is exactly midnight |
TIME | time32[s] |
TIMESTAMP(p) | timestamp, any unit, written with the column's p fractional digits; a timestamp with a time zone is stored as its UTC date and time |
BOOLEAN | bool |
CLOB, DBCLOB | utf8, large_utf8 |
XML | utf8, large_utf8: the document text, parsed by the server |
BLOB | binary, large_binary, fixed_size_binary |
VECTOR(n, FLOAT32), VECTOR(n, INT8) | list, large_list or fixed_size_list of float32 or of any integer type; also utf8 text such as [1,2,3] |
ROWID (Db2 for i) | binary |
A column of the Arrow null type (what DB-API builds for a lone Python
None) binds NULL to a parameter of any type. A dictionary-encoded column
(a pandas categorical, a Parquet dictionary column) binds its values.
Timestamps into DATE. A source without a date-only type sends dates as
timestamps: an Oracle DATE, for example, reaches Arrow as timestamp[s].
Appending it to a Db2 DATE column works when every value is midnight. A
value with a time of day fails the load with an error naming the column and
the row, so no time is ever dropped silently.
Timestamps with a time zone. An Arrow timestamp with a time zone holds a
UTC instant, and a Db2 TIMESTAMP keeps no zone, so the driver stores the UTC
date and time: 2024-01-15 12:00 in America/New_York is stored as
2024-01-15-17.00.00. To store local times instead, convert on the source
side before ingest (with pyarrow, pyarrow.compute.local_timestamp).
Lists into VECTOR. The driver sends each list as the text Db2 expects,
float32 with 9 significant digits so that every value arrives bit-exact.
Refused before anything is sent, naming the column and the row: a null
element, an empty list, NaN or infinity, and float64 elements (cast to
float32). Very large vectors that Db2 describes as CLOB (FLOAT32 from
2042 dimensions, INT8 from 6535) take the vector text, not a list.
Byte strings and LOBs. A value longer than a BINARY, VARBINARY or
FOR BIT DATA parameter is refused before anything is sent. BLOB, CLOB,
DBCLOB and XML values of any size are accepted on INSERT and UPDATE,
in a query's parameters and as CALL arguments.
DATALINK (Db2 for i). A DATALINK parameter is refused: write
DLVALUE(CAST(? AS VARCHAR(n))) and bind the URL as text.
Ingest: the column types create makes
Bulk ingest in create, replace and create_append modes creates the table
from the Arrow schema. Every field needs a non-empty name, which becomes the
column name.
| Arrow type | Db2 column |
|---|---|
int8, uint8, int16 | SMALLINT |
uint16, int32 | INTEGER |
uint32, int64 | BIGINT |
uint64 | DECIMAL(20,0) |
float32 / float64 | REAL / DOUBLE |
bool | BOOLEAN |
decimal128(p,s), decimal256(p,s) | DECIMAL(p,s); p above 31 (Db2's maximum) becomes 31, and a value that needs more digits is refused at insert |
utf8, large_utf8 | VARCHAR(n), n from adbc.arpedb2.ingest.varchar_length (default 4000) |
binary, large_binary | VARBINARY(n), same n |
fixed_size_binary(n) | BINARY(n); VARBINARY(n) for n above 255 |
date32, date64 | DATE |
time32, time64 | TIME |
timestamp[s], [ms], [us], [ns], with or without a time zone | TIMESTAMP(0), TIMESTAMP(3), TIMESTAMP, TIMESTAMP(9) |
| dictionary | the column type of its value type |
fixed_size_list<float32>[n] | VECTOR(n, FLOAT32) (Db2 12.1.2 and later) |
fixed_size_list<int8>[n] | VECTOR(n, INT8) (Db2 12.1.2 and later) |
A list or large_list has no fixed length, so the VECTOR dimension is
unknown and create refuses it: create the table yourself and ingest with
append.
On Db2 for i a row holds at most 32,766 bytes: lower
adbc.arpedb2.ingest.varchar_length for a wide table with many string
columns.
To place the table or set its storage (IN tablespace, ORGANIZE BY,
DISTRIBUTE BY, COMPRESS YES), set the statement option
adbc.arpedb2.ingest.create_table_suffix: see
Statement options.
Keeping Db2 column types
When a field carries the ARPEDB2:type metadata that ArpeDb2 reads emit,
create uses that type instead of the table above. Copying a Db2 table into
Db2 (read with this driver, ingest the result) therefore keeps CHAR(3),
VARCHAR(40), NUMERIC(7,3), GRAPHIC(5), BINARY(4), CLOB(2097152),
TIMESTAMP(9) and so on, instead of VARCHAR(4000) everywhere. The metadata
is used only when it names a type the values can be written to; otherwise the
table above applies. Since v0.3.6.
To get the default types instead, remove the field metadata from the schema
before ingest (with pyarrow, rebuild the fields with
field.remove_metadata()).
See also
- Connection: connection and statement options
- Compatibility: validated Db2 versions
- Examples: reading and writing typed data