DataMasque Portal

IBM Db2 for i connections

DataMasque masks IBM Db2 for i tables in place.

Required privileges

Prerequisites

Each target library (schema) must have a journal named QSQJRN. IBM i requires tables to be journaled for commitment control (transactions). Tables created via SQL CREATE TABLE are journaled automatically when QSQJRN exists; tables created via CL (CRTPF) are not.

If the journal is missing, run these CL commands once as a system administrator:

CRTJRNRCV JRNRCV(<library>/QSQJRN0001)
CRTJRN JRN(<library>/QSQJRN) JRNRCV(<library>/QSQJRN0001)

Then start journaling on any existing un-journaled tables:

STRJRNPF FILE(<library>/<table>) JRN(<library>/QSQJRN)

Database Privileges

A DataMasque connection for IBM Db2 for i requires at least the following permissions:

  • SELECT permission on the following QSYS2 catalog views:
    • SYSTABLES
    • SYSCOLUMNS
    • SYSCST
    • SYSKEYCST
    • SYSREFCST
    • SYSINDEXES
    • SYSKEYS
    • SYSTABLESTAT
    • SYSTRIGGERS
  • SELECT permission on SYSIBM.SYSDUMMY1.
  • CREATEIN, DROPIN, and ALTERIN on the target library (schema). DataMasque uses these to create mapping tables, temporary tables, and the run history table.
  • SELECT, INSERT, UPDATE, and DELETE on each table to be masked.
  • ALTER TABLE authority on each table to be masked. DataMasque uses this to manage foreign key constraints during masking.
  • For surrogate-key DDL (ALTER TABLE DROP COLUMN / ALTER TABLE ALTER COLUMN SET NOT NULL):
    • EXECUTE privilege on QSYS2.QCMDEXC. DataMasque calls this procedure to run IBM i CL commands via SQL.
    • SELECT permission on QSYS2.REPLY_LIST_INFO. DataMasque queries this view to detect whether a CPA32B2 reply entry is already configured before attempting to add one.
    • DataMasque automatically installs a CPA32B2='I' system reply-list entry on first connect if the masking user has *IOSYSCFG authority. If the user lacks *IOSYSCFG, pre-configure the entry once as a system administrator (any sequence number from 1–9999 works; 9999 is customary): ADDRPYLE SEQNBR(9999) MSGID(CPA32B2) RPY('I') Verify with: SELECT * FROM QSYS2.REPLY_LIST_INFO WHERE MESSAGE_ID = 'CPA32B2'
    • DataMasque locates the entry by MESSAGE_ID, not by sequence number. Any free sequence number works.
    • DataMasque never modifies or removes this entry. Concurrent masking runs share it safely; install it once and leave it in place.

The following SQL statements grant the minimum required catalog access. Run these as a system administrator (QSECOFR or equivalent):

-- Catalog view access
GRANT SELECT ON QSYS2.SYSTABLES TO <user>;
GRANT SELECT ON QSYS2.SYSCOLUMNS TO <user>;
GRANT SELECT ON QSYS2.SYSCST TO <user>;
GRANT SELECT ON QSYS2.SYSKEYCST TO <user>;
GRANT SELECT ON QSYS2.SYSREFCST TO <user>;
GRANT SELECT ON QSYS2.SYSINDEXES TO <user>;
GRANT SELECT ON QSYS2.SYSKEYS TO <user>;
GRANT SELECT ON QSYS2.SYSTABLESTAT TO <user>;
GRANT SELECT ON QSYS2.SYSTRIGGERS TO <user>;
GRANT SELECT ON SYSIBM.SYSDUMMY1 TO <user>;

-- Required only if masking tables that use surrogate keys
GRANT EXECUTE ON PROCEDURE QSYS2.QCMDEXC TO <user>;
GRANT SELECT ON QSYS2.REPLY_LIST_INFO TO <user>;

-- Target library privileges
GRANT CREATEIN, DROPIN, ALTERIN ON SCHEMA <library> TO <user>;

-- Table-level privileges (repeat for each table to be masked)
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON TABLE <library>.<table> TO <user>;

Notes:

  1. IBM i catalog views (QSYS2.*) are accessible to all users by default. If your system has had these revoked, re-grant them as shown above.

  2. If a library (schema) is not explicitly specified in the connection settings, DataMasque uses the connection user's IBM i profile name as the default schema.

Connection parameters

For IBM Db2 for i databases, the following field is optional:
Library The name of the IBM i library (schema) that DataMasque will operate on. If not specified, the connection user's IBM i profile name will be used as the default schema.

Note: The default port for the IBM i database host server is 8471. If no port is specified in the connection settings, DataMasque will use this default.

Limitations

  • DDL statements (e.g. creating mapping tables, run history table) auto-commit and are not rolled back on error. If a masking run fails mid-execution, any DDL changes that already executed may need to be cleaned up manually.

  • truncate_table tasks do not truncate in the SQL sense but delete all rows instead (uses DELETE FROM {table} rather than TRUNCATE TABLE {table}). This is the same behaviour as Db2 LUW (see IBM Db2 LUW Limitations above).

  • BLOB data types are not supported for masking.

  • timestamp values do not support timezones, requiring careful handling during masking:

    • Masking functions like from_random_datetime disregard any supplied timezone.
    • Masking functions like retain_age operate in UTC time for masking, treating values as if they are in UTC.
      • This means the age that retain_age will retain will be relative to UTC and not your local time.
    • Including timezones in date values used in if conditions will cause masking to fail.

  • DataMasque always connects to IBM i using UTF-8 encoding (CCSID 1208). If the target library uses a different CCSID, characters not representable in that encoding may be replaced with the substitute control character (0x1A) when writing to the database. Ensure masked values, custom discovery match rules, and similar configuration contain only characters from the target library's CCSID.

Supported data types

The following IBM Db2 for i data types are supported:

Data Type Description
smallint 16-bit binary integer with range of -32768 to +32767 (inclusive)
integer 32-bit binary integer with range of -2147483648 to +2147483647 (inclusive)
bigint 64-bit binary integer with range of -2^63 (-9223372036854775808) to 2^63 - 1 (9223372036854775807) (inclusive)
real Single precision floating-point number (32-bit).
double, float Double precision floating-point number (64-bit).
decimal, numeric Numerical value with up to 63 digit precision.
decfloat IEEE 754 decimal floating-point number (16 or 34 digit precision).
char(n) Fixed-length character string between n=1 and n=32765 characters long (inclusive).
varchar(n) Variable-length character string between n=1 and n=32740 characters long (inclusive).
clob Character large object for large text data.
date Date value with no time.
time Time value with no date.
timestamp Date value with date and time, but no timezone.
xml XML data.

Notes:

Unsupported key columns

Data Type Unsupported Format
real All
decfloat All
blob All
clob All
dbclob All
datalink All
rowid All
xml All
timestamp Scale >= 7
decimal Scale >= 31