Skip to main content

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).

Python
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 typeArrow typeNotes
SMALLINTint16
INTEGERint32
BIGINTint64
REALfloat32
DOUBLE / FLOATfloat64
DECIMAL(p,s) / NUMERIC(p,s)decimal128(p,s)Precision and scale kept, no detour through text.
DECFLOAT(16) / DECFLOAT(34)float64The 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=stringutf8The 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 typeArrow typeNotes
CHAR(n)utf8Converted to UTF-8 from the column's CCSID. Padding kept.
VARCHAR(n), LONG VARCHARutf8Converted to UTF-8 from the column's CCSID.
GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHICutf8UTF-16 converted to UTF-8.
CLOButf8Converted to UTF-8 from the column's CCSID.
DBCLOButf8UTF-16 converted to UTF-8.
XMLutf8The 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 DATAbinaryutf8 with adbc.arpedb2.bit_data_ccsid (see below).
BINARY(n), VARBINARY(n)binary
BLOBbinary

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 typeArrow typeNotes
DATEdate32A value that is not a real date fails the read with an error naming the column.
TIMEtime32[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=exacttimestamp[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=exactutf8Arrow 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 typeArrow typeNotes
BOOLEAN on Db2 for ibool
BOOLEAN on Db2 for LUWint160 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 typeArrow typeNotes
VECTOR(n, FLOAT32)utf8The vector's text, for example [1.5,-2.25,3].
VECTOR(n, INT8)utf8The 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:

Python
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 iDb2 for LUWWhat ArpeDb2 returns on Db2 for i
Text in each column's CCSID, usually EBCDICUTF-8utf8, converted.
NCHAR(n), NVARCHAR(n), NCLOB(n) are UTF-16 GRAPHIC, VARGRAPHIC, DBCLOBcharacter types in UTF-8utf8, with ARPEDB2:type GRAPHIC(n), VARGRAPHIC(n), DBCLOB(n).
NUMERIC(p,s) is zoned decimala synonym of DECIMALdecimal128(p,s), with ARPEDB2:type NUMERIC(p,s).
DECIMAL / NUMERIC up to 63 digitsup to 31decimal128(31,s): see below.
BOOLEAN is a real Booleansent as SMALLINTbool.
ROWIDno such typebinary (26 bytes on IBM i 7.5), with ARPEDB2:type ROWID.
DATALINK(n)no such typeutf8 holding the URL. Select DLCOMMENT(col) for the comment.
no VECTORVECTOR (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 targetArrow types accepted
SMALLINT, INTEGER, BIGINTany integer type, and bool (as 0 or 1); out-of-range values are refused
REAL, DOUBLEfloat32 / float64 matching the target
DECIMALdecimal128, 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, VARCHARutf8, large_utf8; for FOR BIT DATA columns also binary, large_binary, fixed_size_binary
GRAPHIC, VARGRAPHICutf8, large_utf8
BINARY, VARBINARYbinary, large_binary, fixed_size_binary
DATEdate32; date64 and timestamp without a time zone, when the value is exactly midnight
TIMEtime32[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
BOOLEANbool
CLOB, DBCLOButf8, large_utf8
XMLutf8, large_utf8: the document text, parsed by the server
BLOBbinary, 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 typeDb2 column
int8, uint8, int16SMALLINT
uint16, int32INTEGER
uint32, int64BIGINT
uint64DECIMAL(20,0)
float32 / float64REAL / DOUBLE
boolBOOLEAN
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_utf8VARCHAR(n), n from adbc.arpedb2.ingest.varchar_length (default 4000)
binary, large_binaryVARBINARY(n), same n
fixed_size_binary(n)BINARY(n); VARBINARY(n) for n above 255
date32, date64DATE
time32, time64TIME
timestamp[s], [ms], [us], [ns], with or without a time zoneTIMESTAMP(0), TIMESTAMP(3), TIMESTAMP, TIMESTAMP(9)
dictionarythe 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​