Edit

Share via

DB22SS0028: ROWID column generated (Info)

This article describes the reason why SQL Server Migration Assistant (SSMA) for Db2 adds ROWID column to the table, if there are triggers.

Background

In Db2 you can create a trigger that runs FOR EACH ROW, rather than for the entire set of rows that is changing. In SQL Server, triggers always execute for the entire set of modified rows. If row-level Db2 trigger accesses both OLD and NEW correlation names, then SSMA needs a way to match rows from both row sets, to identify what the value was for a given row before and after the update. To emulate such per-row functionality, SSMA adds special ROWID column to uniquely identify each modified row and uses it to establish a relationship between inserted and deleted row sets.

Examples

Consider the following Db2 trigger, which executes for each row updated in TRIG_TEST table:

CREATE OR REPLACE TRIGGER TSCHM.TRIG_TEST_AU
AFTER UPDATE OF DATA ON TSCHM.TRIG_TEST
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
    IF (N.DATA = 'ABC') THEN
        INSERT INTO TSCHM.TRIG_TEST(DATA) VALUES ('-' || O.DATA);
    END IF;
END

When you try to convert this trigger in SSMA, the following T-SQL is produced within the SQL Server trigger:

  1. Run a cursor over inserted row set, selecting ROWID and DATA columns into @new$0 and @new$DATA variables:

    DECLARE
    ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
    SELECT ROWID, DATA
    FROM inserted

OPEN ForEachInsertedRowTriggerCursor

FETCH ForEachInsertedRowTriggerCursor
    INTO @new$0, @new$DATA

  2. Select matching row from deleted row set into @old$0 and @old$DATA variables, based on the inserted ROWID (stored in @new$0 variable):

    SELECT @old$0 = ROWID, @old$DATA = DATA
FROM deleted
WHERE ROWID = @new$0

  3. Perform trigger actions using @old$DATA/@new$DATA variables:

    IF (@new$DATA = 'ABC')
    INSERT SSMAADMIN.TRIG_TEST(DATA)
    VALUES (('-' + ISNULL(@old$DATA, '')))

Remarks

This behavior is controlled by the Generate ROWID column project setting, which can be found under Tools > Project Settings > General > Conversion > ROWID generation. If the setting is No, but during trigger conversion SSMA identifies that it requires a ROWID column, then DB22SS0239: ROWID column not accessible (Error) conversion error is generated.