Validating Data from IBM DB2¶

AIM DMV migrates and validates data from IBM DB2 for Linux, UNIX, and Windows (LUW).

This page covers IBM DB2-specific setup for Data validation. For workflow field definitions, see Data validation configuration reference.

Prerequisites¶

Worker driver: Same bundled DB2 driver as Migrating Data from IBM DB2. You don’t install a DB2 ODBC driver.
Authentication: Username and password only. SSL, LDAP, and Kerberos aren’t supported.
Worker path: IBM DB2 validation runs on the Worker. It doesn’t use the local in-process validation engine.
Privileges: SELECT on every table or view being validated. See IBM DB2.

Connectivity¶

Reuse [connections.source.db2] Worker TOML from data migration. [connections.source.db2].database must match the database in the validation workflow.

[connections.source.db2]
username = "myuser"
password = "your_password"
database = "SAMPLE"
host = "db2.example.com"
port = "50000"

Set sourcePlatform: db2 in the validation workflow YAML.

Row validation uses the same two transports as migration. With a bucket, prefer cloud_direct. Without a bucket, regular extracts through the Worker to an internal stage. regular doesn’t need an external stage. When row validation uses object storage, set extraction.strategy to cloud_direct and extraction.externalStage to the stage name. When more than one of [connections.target.s3], [connections.target.gcs], and [connections.target.blob] is present, the Worker prefers S3, then GCS, then Azure Blob. See L3 extraction.

Example workflow excerpt:

sourcePlatform: db2
targetDatabase: TARGET_DB
schemaMappings:
  MYSCHEMA: PUBLIC
validationConfiguration:
  schemaValidation: true
  metricsValidation: true
  rowValidation: false
tables:
  - fullyQualifiedName: MYSCHEMA.CUSTOMERS
    columnNamesToPartitionBy:
      - CUSTOMER_ID
  - fullyQualifiedName: MYSCHEMA.ORDERS
    columnNamesToPartitionBy:
      - ORDER_ID
    validationConfiguration:
      rowValidation: true

Validation can compare a DB2 view with a Snowflake view. Ask for that the same way you ask for a table.

Data type mappings¶

The target types are the same as Migrating Data from IBM DB2. Schema comparison uses those mapped Snowflake types.

DB2 type	Snowflake type	Supported for validation	Notes
`SMALLINT`	`NUMBER(5,0)`	Yes
`INTEGER`, `INT`	`NUMBER(10,0)`	Yes
`BIGINT`	`NUMBER(19,0)`	Yes
`DECIMAL`, `DEC`, `NUMERIC`	`NUMBER(p,s)`	Yes	Default `NUMBER(20,10)` when precision isn’t in the catalog
`REAL`, `FLOAT`, `DOUBLE`, `DOUBLE PRECISION`	`FLOAT`	Yes	Compared as the IEEE-754 value. A difference that survives in the binary value is a mismatch
`DECFLOAT`	`DECFLOAT`	Yes	Exact decimal text, not `FLOAT`. A difference past digit 16 still mismatches
`CHAR`, `CHARACTER`, `NCHAR`	`VARCHAR`	Yes	See Trailing blanks. Logical mode trims the DB2 side. The migrated `VARCHAR` keeps trailing spaces
`VARCHAR`, `CHARACTER VARYING`, `NVARCHAR`	`VARCHAR`	Yes
`CLOB`, `DBCLOB`, `NCLOB`, `LONG VARCHAR`	`VARCHAR`	Yes	Hashed. A value larger than 32,672 bytes, or a `DBCLOB` or `NCLOB` that doesn’t fit in that cast, is a mismatch against a truncated copy. `CLOB` is omitted from L2 metrics
`GRAPHIC`, `VARGRAPHIC`, `LONG VARGRAPHIC`	`BINARY`	Yes	Compared as bytes, not as text
`BINARY`, `VARBINARY`, `FOR BIT DATA`	`BINARY`	Yes
`BLOB`	`BINARY`	Yes	Binary cast of 32,672 bytes. A larger value doesn’t match a truncated copy
`BOOLEAN`	`BOOLEAN`	Yes
`DATE`	`DATE`	Yes
`TIME`	`TIME`	Yes	No fractional seconds. Compared as `hh:mm:ss`
`TIMESTAMP`	`TIMESTAMP_NTZ`	Yes	Digits 1 through 9 are kept. Digits 10 through 12 aren’t stored on Snowflake. A difference in digit 9 is a mismatch
`XML`	`VARIANT`	Yes	Text serialization of at most 32,000 bytes, loaded as a string `VARIANT`. A longer serialization isn’t a match
`ROWID`	none	No	Not validated

Text comparison mode accepts logical (default) and raw. Raw comparison compares stored bytes and reports a trailing-blank or encoding difference.

Validation levels¶

Level	IBM DB2 behavior
L1 schema	Compares the mapped Snowflake type. Collation can be reported. Generated SQL doesn’t force a collation.
L2 metrics	Numeric, character, datetime, and Boolean metrics. `CLOB` is omitted. Integer averages and character lengths are aligned with Snowflake.
L3 rows	Supported, including hybrid mode: a row hash, then cell drill-down only where the hash mismatches. `FLOAT` uses IEEE-754. `DECFLOAT` uses exact decimal text.

Platform-specific considerations¶

Reuse the same [connections.source.db2] Worker block as migration.
Set sourcePlatform: db2.
Schema, metrics, and row validation are all available. Ask for row validation per table.
Prefer cloud_direct for row validation when a bucket and a matching external stage exist. Otherwise use regular.
Tables and created global temporary tables validate as tables. Views and materialized query tables validate as views, so supply their keys as described in Keys for source views. Declared global temporary tables aren’t in SYSCAT.TABLES, so they’re treated as missing. See How catalog kinds are classified.
ROWID isn’t validated.
Anti-locking: No automatic hint is added on IBM DB2. Set queryModifiers only when you need a custom source SQL hint. See Anti-locking and query modifiers.

Prompt:

Run cloud data validation for my IBM DB2 tables, with schema and metrics validation on all tables and row-level validation on CUSTOMERS