Operations (Db2)

For Db2, the underlying implementation is leveraging the DB2BulkCopy class of the IBM Data Server .NET Provider (IBM.Data.Db2 namespace).

For BulkInsert, the entities/rows are written straight to the target table (or, when Db2BulkImportIdentityBehavior.ReturnIdentity is requested, a staging table is used instead so the generated identity values can be read back via Db2’s SELECT ... FROM FINAL TABLE (INSERT ...) clause on the very same statement that loads the real table — no separate pre-generation round-trip is needed the way Oracle’s NEXTVAL approach requires).

For BulkDelete, BulkDeleteByKey, BulkMerge and BulkUpdate, a pseudo (staging) table is created (and truncated) under a transaction context. The library writes to it via BulkInsert internally, then cascades the changes to the original table using the correct SQL statement.

The data is brought together from the client application into the database server (at one-go). It then gets processed together at the same time.

The other bulk operations can be optimized further by targeting the underlying table indexes (via qualifiers). Pass a list of Field objects when calling the operations.

Pseudo Table Type

The Db2BulkImportPseudoTableType enum lets you choose between a session-private temporary table (Memory), an ordinary heap table (Physical), or let the library decide based on row count (Auto, the default).

Auto and Memory both currently resolve to Physical at runtime — the internal auto-resolution logic returns Physical on every outcome, so there is no session-private staging path implemented yet despite the enum advertising one. Because a physical pseudo-table has no per-session isolation, avoid running concurrent bulk operations against the same target table until this is resolved. Also note that every bulk call creates its own staging table (CREATE TABLE ... DEFINITION ONLY) and drops it once the call completes, rather than creating one per (table, pseudo table type) and reusing it across calls — since CREATE TABLE/DROP TABLE are DDL and commonly force a commit boundary, this happens on every single call, not just the first.

Supported Objects

Below are the following objects supported by the bulk operations.

• System.DataTable
• System.Data.Common.DbDataReader
• IEnumerable<T>
• ExpandoObject
• IDictionary<string, object>

Operation SQL Statements

Once all the data is in the staging (pseudo) table, the correct SQL statement is used to cascade the changes towards the original table.

BulkInsert writes directly into the target table and skips the staging table entirely — unless identityBehavior is set to ReturnIdentity, in which case a staging table is used first (see above).

For BulkDelete / BulkDeleteByKey

> DELETE FROM "OriginalTable" T
> WHERE EXISTS (
>     SELECT 1 FROM "PseudoTempTable" S
>     WHERE T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2
> );

Unlike Oracle, Db2 supports a plain correlated EXISTS subquery against the staging table, so there is no need for the ROWID-matching workaround that Oracle’s DELETE requires.

For BulkMerge

> MERGE INTO "OriginalTable" T USING "PseudoTempTable" S ON (T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2)
> WHEN MATCHED THEN
> UPDATE SET T.Field3 = S.Field3, T.Field4 = S.Field4
> WHEN NOT MATCHED THEN
> INSERT (Field1, Field2, ...) VALUES (S.Field1, S.Field2, ...);

The identity column, if any, is always left out of the INSERT column list — a brand-new row’s identity property is typically an unset default (e.g. 0), not a real value meant to be inserted as-is.

The single MERGE above is only used when identityBehavior is not ReturnIdentity. When it is, the library instead runs three separate statements — see Identity Setting Alignment below.

For BulkUpdate

> MERGE INTO "OriginalTable" T USING "PseudoTempTable" S ON (T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2)
> WHEN MATCHED THEN
> UPDATE SET T.Field3 = S.Field3, T.Field4 = S.Field4;

Unlike BulkMerge, there is no WHEN NOT MATCHED branch — staged rows with no matching target row are left as-is, not inserted.

Special Arguments

The arguments below are available on most operations.

Identity Setting Alignment

When identityBehavior is set to ReturnIdentity, the library adds a __RepoDbBulkRowOrder__ identity column to the pseudo-table to track each entity’s position in the source IEnumerable.

For BulkInsert, the library runs a single SELECT identity FROM FINAL TABLE (INSERT INTO ... SELECT ... FROM "PseudoTempTable" ORDER BY __RepoDbBulkRowOrder__) ORDER BY identity statement, then assigns the returned identities back to the entities in that order.

For BulkMerge, the single-statement MERGE cannot both branch on match and read back generated identities in Db2, so the library instead runs three separate statements: (1) a LEFT JOIN snapshot between the staging and target table to classify each staged row as matched or unmatched, (2) a MERGE ... WHEN MATCHED THEN UPDATE for the matched rows, and (3) an insert-only statement (again via FINAL TABLE) for the unmatched rows to read back their new identities. This is not atomic — three round-trips instead of one — and the matched/unmatched classification can go stale if another connection modifies the target table between the snapshot and the follow-up statements.

BatchSize

All the provided operations have a batchSize argument that lets you override the number of rows wired-up to the server per batch. By default it is null, meaning all items are sent together in one-go.

Use this argument if you wish to optimize the operation based on certain situations.

• Network Latency
• Infrastructure
• No. of Columns
• Type of Data

Async Methods

All the provided synchronous operations have an equivalent asynchronous (Async) counterpart.

BulkDelete

using (var connection = new DB2Connection(connectionString))
{
    var people = connection.Query<Person>(e => e.IsActive == false);
    var deletedRows = connection.BulkDelete<Person>(people);
}

Or with qualifiers.

using (var connection = new DB2Connection(connectionString))
{
    var deletedRows = connection.BulkDelete<Person>(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkDeleteByKey

using (var connection = new DB2Connection(connectionString))
{
    var primaryKeys = new object[] { 10045, 10046, 10047 };
    var deletedRows = connection.BulkDeleteByKey("Person", primaryKeys);
}

BulkInsert

using (var connection = new DB2Connection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people);
}

To return the newly generated identity values:

using (var connection = new DB2Connection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people,
        identityBehavior: Db2BulkImportIdentityBehavior.ReturnIdentity);
}

BulkMerge

using (var connection = new DB2Connection(connectionString))
{
    var people = GetPeople(10000);
    var mergedRows = connection.BulkMerge(people);
}

Or with qualifiers.

using (var connection = new DB2Connection(connectionString))
{
    var mergedRows = connection.BulkMerge(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkUpdate

using (var connection = new DB2Connection(connectionString))
{
    var people = GetPeople(10000);
    var updatedRows = connection.BulkUpdate(people);
}

Or with qualifiers.

using (var connection = new DB2Connection(connectionString))
{
    var updatedRows = connection.BulkUpdate(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

Table of contents

• BulkDelete
• BulkDeleteByKey
• BulkInsert
• BulkMerge
• BulkUpdate