Edit

Share via

DB22SS0239: ROWID column not accessible (Error)

This article describes the reason why SQL Server Migration Assistant (SSMA) for Db2 requires a ROWID column to be defined for the table with 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 in order to identify what the value was for a given row before and after the update. To emulate such "for each 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.

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 set to No, then the ROWID column isn't added to the table, and SSMA can't convert such triggers.

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, if the Generate ROWID column project setting is set to No, then SSMA generates the following error message:

DB22SS0239: ROWID column not accessible

Possible remedies

Change the Generate ROWID column project setting to Add ROWID column for tables with triggers, which allows SSMA to produce the following T-SQL within the SQL Server trigger when converting the previous example:

  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, '')))