Aster Web Data / Connect your data

Db2Commercial

How Aster Web Data uses Db2 for Linux, UNIX and Windows, and how Db2's types, collation and results shape what clients receive.

If your data is in Db2 12.1 for Linux, UNIX and Windows, Aster Web Data serves it from your existing tables through IBM's CLI client. Filters, sorts, pages and counts run in Db2, and each result is Db2's own, decoded exactly or refused, with no correction added for padding, overflow or a zero divisor. Where Db2's answer differs from OData's, Db2's stands; Departures from OData lists those places.

Three choices shape how a Db2 database serves: the page size of the tablespace, which limits how long a composite key can be; whether your text columns are VARCHAR or CLOB, because a table with a CLOB column loses text sorting and functions in the database; and the row change timestamp that gives a set its ETag.

Connecting

databases:
  warehouse:
    kind: db2
    url_env: ASTER_DB2_URL        # db2://user:password@host:50000/database
    tls: verify-full
    ca_file: db2-ca.pem

Percent-encode the user and password in the URL. The service loads IBM's Db2 CLI client library when it starts: install the client, and set ASTER_DB2_CLI_LIBRARY to the library's absolute path. Aster Web Data does not ship the client. Secrets explains how the URL is supplied, and the configuration reference lists every key with its default. These behave in a way particular to Db2:

• schema is DB2INST1 when absent, and a data source's own schema wins. Identifiers are quoted.
• tls takes disable, verify-ca or verify-full, and both verified modes need ca_file. Only verify-full refuses a host name that does not match the certificate. prefer and require are refused, because the CLI client has no opportunistic or unverified mode. A plaintext connection logs a warning, and tls_strict refuses it.
• collation: IDENTITY is the only collation accepted; any other named collation is refused.
• options are refused; the client library comes from ASTER_DB2_CLI_LIBRARY.
• read_only: true makes the connection refuse data-change statements and calls.

The service checks the server when it connects, and refuses anything other than Db2 12.1 for Linux, UNIX and Windows. The database must use UTF-8, and each connection checks a Unicode round trip. The tests run on Db2 Community Edition 12.1.5 on Linux.

Start-up check

When the service opens the database, it reads the catalog for each data source's table: columns, keys, foreign keys, checks and comments. It refuses a column whose type cannot hold the field exactly, such as DECFLOAT under a decimal field, naming the column and saying which type or facets to change. Physical column types are read once, when the tables are bound, so restart the service after changing a column's type.

Index keys are limited by the tablespace's page size. A composite key of a BIGINT and a VARCHAR(255 CODEUNITS32) exceeds the default 4K page, so create such tables in a tablespace with larger pages; field lengths are never shortened to fit.

A failed check makes only that database's sets unavailable; A database goes down describes what clients see. To run the check on its own, use the check command.

Types

DECFLOAT, and a floating-point result mapped to a decimal, are refused with the column's name.

Collation and case

The database-wide IDENTITY collation is published as a named collation. VARCHAR equality and ordering ignore trailing spaces under it, while case and accents stay distinct. like does not pad. tolower and toupper are LOWER and UPPER, with Db2's own tables. Text positions count Unicode characters (CODEUNITS32).

Where each operation runs

Each OData operator becomes the Db2 operator with the same meaning:

A table with a CLOB column keeps text sorting, text functions, like, grouping, distinct and unions out of the database for every text column of that table, because Db2 does not sort, group or compare a CLOB. Such a request runs in the service where it can, and is refused with 501 otherwise. A table whose text columns are all VARCHAR runs them in Db2. A statement binds at most 30,000 values.

Errors

A failed BEGIN, COMMIT or ROLLBACK discards the pooled connection.

Nulls and ordering

ORDER BY names NULLS FIRST or NULLS LAST as OData asks. A page with $count=true runs the page and the count in one transaction at repeatable read (RR), which prevents phantoms between them, so the total agrees with the page.

Departures from OData

Where OData 4.01 defines a result and Db2 gives another, Db2's result stands. These are the places:

• DOUBLE stores no NaN, infinity or subnormal value.
• Trailing spaces are ignored in VARCHAR equality and ordering, even under IDENTITY.
• Division by zero in an integer or a finite double division raises SQLSTATE 22012.
• mod of the smallest 64-bit integer by -1 is 0. Other integer overflow raises 22003.
• avg of integers returns an integer and truncates: the average of 1 and 2 is 1. A decimal avg has Db2's result scale.
• Decimal division follows Db2's result precision and scale: over DECIMAL(28,10) operands, 1.5 div 2 is 0.750 and 0.125 div 3 is 0.041. The divisor's type changes the scale.
• tolower and toupper use Db2's tables, which differ from the full Unicode mappings.
• trim removes spaces only, so tabs stay. substring with a negative start counts positions before the first character, so substring('abc', -2) is 'abc'.

Writes and transactions

• Isolation. Reads use read stability (RS) for repeatable read, and repeatable read (RR) for serializable groups and counted reads.
• Writes by key. A write by key first locks the rows it checks with SELECT ... WITH RS USE AND KEEP UPDATE LOCKS, which keeps the update locks until the commit, and compares the ETag's token there.
• Answers without a second read. An insert answers from SELECT ... FROM FINAL TABLE (INSERT ...) and an update from SELECT ... FROM FINAL TABLE (UPDATE ...). Db2 fails such a write when an AFTER trigger changes its target, so a response never shows rows a trigger changed afterwards.

• ETags. Db2 has no row version that etag: native could read, so a set has no ETag until it declares one. The usual token is a row change timestamp, which Db2 sets on every insert and update of the row:

  ALTER TABLE orders ADD COLUMN row_changed TIMESTAMP NOT NULL
    GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;
  

  Declare it as a read-only datetime field with precision: 6 and name it in etag: {columns: [row_changed]}. scaffold declares a table's row change timestamp as its token. A version column Aster advances, versionField, works as well. ETags and concurrent edits explains the tokens.

Indexes and sort order

Index the columns your screens filter and sort on, as Indexes describes. In the benchmark, equality, IN and prefix filters used their indexes, while a text sort by a column and the key chose a table scan and a sort even with a composite index and fresh statistics. A filter over arithmetic, such as a multiplication, scans the table as the same hand-written SQL does.

Scaffold

The scaffold command reads Db2's catalog for the tables, views, columns, keys, foreign keys, checks and comments, and writes a starting data-source configuration for the columns it can map exactly. It declares a row change timestamp as the set's ETag token. Connect your own database walks through it.