IBM Informix Staging Environment

Introduction

Efficient performance of the IBM Informix Database server is crucial for the optimal functioning of the enov8 Test Data Management (TDM) Tool. This document outlines the standard configurations required for an Informix staging server and provides a comprehensive set of troubleshooting steps for Database Administrators (DBAs) to ensure that the database aligns with these standards and operates at peak efficiency.

Disclaimer

This documentation applies to all IBM Informix versions currently supported by IBM. The configurations and SQL queries provided are recommendations based on typical staging setups and may not be suitable for all environments. It is essential to evaluate these recommendations in the context of your specific staging environment and requirements. Consult with your organization's database administrators and IBM Informix documentation for guidance tailored to your specific needs.

Connectivity

The enov8 TDM tool currently connects to Informix over DRDA using the ibm_db Python driver. When adding an Informix data source, set the Connection Type to DRDA. Support for connecting via Informix SQL / TCP (which uses the Informix Server/DBSERVERNAME field) is planned for a future release (TDM 3.12).

1. Standard Database Server Configuration

1.1 Hardware Requirements

The hardware requirements should scale based on the size of the database and the volume of data being masked. Below is a comprehensive guideline for CPU, memory, disk, and network:

CPU

Cores: Start with 4-6 cores, add 2 cores per 1 TB of data.
Example:
- For a 1 TB database: 4 cores.
- For a 5 TB database: 12 cores.
CPU Virtual Processors: Informix parallelism is driven by CPU virtual processors (VPs). Provision one CPU VP per physical core (VPCLASS cpu,num=<cores> in the ONCONFIG file) so that masking scans, sorts, and MERGE operations can be distributed across cores.

Memory (RAM)

Base Requirement: Start with 16 GB, add 16 GB per 1 TB of data.
Example:
- For a 1 TB database: 16 GB of RAM.
- For a 5 TB database: 80 GB of RAM.

Disk

Type: Use SSDs for optimal performance, especially for I/O-intensive masking operations.
Capacity: Provision at least 2× the total data size to accommodate logical logs, temporary tables, and sort work space.
IOPS: Minimum of 10,000 IOPS for SSDs; 300 IOPS for HDDs.
Example:
- For a 1 TB database: 2 TB of SSD storage.

Network

Bandwidth: At least 1 Gbps network interface card (NIC).
Latency: Low-latency network connections to minimise delays between the enov8 TDM application server and the Informix instance.

1.2 System-Level Database Optimizations

Optimizing for Bulk Data Masking

Size the Logical Logs for Large Transactions:
- Purpose: Masking operations issue large UPDATE and MERGE statements. On a logged Informix database, an undersized logical log set causes transactions to hit the long-transaction high-water mark (LTXHWM) and roll back mid-job.
- Implementation (ONCONFIG):
```
LOGFILES        20        # Number of logical log files
LOGSIZE         51200     # 50 MB per logical log file
LTXHWM          70        # Warn/checkpoint at 70% long-tx fill
LTXEHWM         80        # Exclusive access at 80%
```
- Add logical logs online without a restart:
```
onparams -a -d <dbspace> -s 51200   # add a 50 MB logical log
onmode -l                            # advance to the new log
```
- Implication: Databases created with no logging (or ANSI-mode without buffered logging) are not subject to long-transaction limits, but most staging databases use logging. Ensure the logical log set is large enough to hold the largest single-table masking transaction.
Provision Temporary Dbspaces:
- Purpose: The TDM tool creates working tables during masking — a per-column temporary lookup table (UNMASKED, MASKED, and a SERIAL row-number column) and a preview table via SELECT FIRST 100 * ... INTO TEMP ..._ENOV8_PREVIEW WITH NO LOG. Sorts and hash joins for MERGE also spill to temp space.
- Implementation (ONCONFIG):
```
DBSPACETEMP     tmpdbs1,tmpdbs2    # Dedicated temp dbspaces
```
- Disk allowance: Reserve temp dbspace of at least 1.5× the size of the largest table being masked to absorb sort and join overflow.
Enable Parallel Data Query (PDQ):
- Purpose: PDQ parallelises scans, sorts, and joins across CPU VPs — the Informix equivalent of intra-partition parallelism. Adequate PDQ resources accelerate chunk-based MERGE masking on large tables.
- Implementation (ONCONFIG):
```
MAX_PDQPRIORITY   100     # Cap on PDQ resource per query (%)
DS_MAX_QUERIES    16      # Max concurrent PDQ queries
DS_TOTAL_MEMORY   2097152 # Decision-support memory (KB)
```
- Verification: Confirm the current values with onstat -g mgm (Memory Grant Manager).

Keep Optimizer Statistics Current

Purpose: The TDM tool reads row counts from the nrows column of systables, which is populated by UPDATE STATISTICS. Stale statistics produce inaccurate row counts and poor query plans for masking scans and merges. The tool runs UPDATE STATISTICS FOR TABLE automatically after creating a helper index, but DBAs should ensure statistics are current after any large data load.

Remediation (run before masking jobs):

UPDATE STATISTICS HIGH FOR TABLE "staging_schema"."my_table";

1.3 Informix Internal Configuration

Buffer Pool Sizing

Purpose: Informix caches data and index pages in the resident shared-memory buffer pool. Adequate buffers reduce physical I/O during masking scans and updates.

Implementation (ONCONFIG):

BUFFERPOOL   default,buffers=200000,lrus=16,lru_min_dirty=50,lru_max_dirty=60

Verification:

onstat -b        # buffer usage summary
onstat -p        # read cache / write cache hit ratios

Note: Aim for a read-cache hit ratio above 95% during masking scans. A low ratio indicates the buffer pool is undersized for the working set.

Lock Configuration

Purpose: Large UPDATE and MERGE transactions during masking hold many row locks simultaneously. If the lock table is exhausted, statements fail with SQL -134 (no more locks).

Implementation (ONCONFIG):

LOCKS   500000    # Initial lock table entries (grows dynamically)

Note: For very large single-statement updates, consider setting the table lock mode to page or table level (ALTER TABLE ... LOCK MODE (PAGE)) to reduce lock pressure, or rely on TDM chunking (see section 1.4) to keep transactions small.

Checkpoints and Physical Log

Purpose: Frequent checkpoints during heavy write workloads can stall masking. A suitably sized physical log and non-blocking checkpoints keep throughput steady.

Implementation (ONCONFIG):

PHYSFILE       204800   # 200 MB physical log (if not using PLOG dbspace)
RTO_SERVER_RESTART 60   # Enables interval/soft checkpoints

Verification:

onstat -g ckp    # checkpoint history and duration

Sessions and Connections

Purpose: The TDM tool opens a single connection per masking job. For environments with multiple concurrent TDM users, ensure the instance allows enough sessions.

Implementation (ONCONFIG):

NETTYPE   drsoctcp,1,200,NET   # 200 poll-thread connections for DRDA

1.4 Chunking and ROWID Awareness

Purpose: For large tables, the TDM tool processes updates in chunks to keep individual transactions small. On Informix, chunking is driven by the rowid pseudo-column.
How it works: Before chunking, the tool checks whether ROWID is available on the target table. If it is, updates are batched using SELECT FIRST <chunk_size> rowid .... If ROWID is unavailable, the tool automatically falls back to a single-statement update.
Requirement: Fragmented tables created without explicit row IDs do not expose a stable rowid and return SQL -857. To benefit from chunked masking on large fragmented tables, create them WITH ROWIDS, or use non-fragmented tables (which always have a rowid).
```
-- Enable rowid on an existing fragmented table
ALTER TABLE "staging_schema"."my_table" ADD ROWIDS;
```
Verification:
```
SELECT FIRST 1 ROWID FROM "staging_schema"."my_table";
```
A successful result confirms chunked masking will be used; an error indicates the tool will use a single-statement update instead.

1.5 Network Configuration

Default Port: For DRDA connections, Informix typically listens on port 9089. Ensure the enov8 TDM application server can reach the Informix instance on the configured DRDA port.

DRDA Listener (sqlhosts): DRDA requires a DBSERVERALIASES entry using the drsoctcp protocol. Define it in the ONCONFIG file and the sqlhosts file:

# ONCONFIG
DBSERVERNAME     ol_informix           # Native SQLI server
DBSERVERALIASES  dr_informix           # DRDA alias

# sqlhosts
ol_informix   onsoctcp   dbhost   9088
dr_informix   drsoctcp   dbhost   9089

Connection String: The TDM tool builds the following DRDA connection string:
```
DATABASE=<dbname>;HOSTNAME=<host>;PORT=9089;UID=<user>;PWD=<pass>;DELIMIDENT=y;PROTOCOL=TCPIP;
```
DELIMIDENT=y enables delimited (double-quoted) identifiers, so table, column, and schema names are treated exactly as stored — including case. Ensure the staging object names match the case used in the profiling and masking configurations.
Firewall Rules:
- Open the DRDA port (default 9089) from the TDM application server to the Informix host.

1.6 Security and Patch Management

Patching:
- Keep the Informix instance at a supported version and fix pack level to receive security and performance fixes.
Authentication:
- The TDM tool authenticates using the UID/PWD parameters in the connection string. Informix delegates authentication to the operating system (or a configured PAM module) by default.
- Ensure the TDM service account is a valid OS user on the Informix host with CONNECT privilege on the staging database.
Security Parameters:
- Restrict the TDM service account to the staging database only. Grant the minimum privileges described in section 3 — avoid granting instance-wide DBA unless required by your security policy.
- Ensure the service account password does not expire during masking job windows.

2. Troubleshooting Steps for DBAs

When experiencing performance issues with the TDM masking tool on Informix, the following steps can help identify and resolve common problems.

2.1 Verify Hardware and OS Configuration

Check CPU and Memory

Purpose: Ensure hardware meets specifications.
Commands:
- Linux:
```
lscpu
free -h
```
Verification: Confirm CPU core count and RAM meet the requirements in section 1.1, and that the CPU VP count (onstat -g glo) matches the available cores.

Disk I/O Performance

Purpose: Ensure disk I/O supports masking, sort, and temp-table operations.
Commands:
```
iostat -x 1 5
```
Verification: The %util column should remain below 80%. High utilisation during masking indicates I/O bottlenecks that can slow sorts, logical log writes, and temp-space operations.

2.2 Validate Informix Configuration Parameters

Logical Log Configuration

Purpose: Confirm sufficient logical log space is allocated to avoid long-transaction rollbacks during masking.

Commands:

onstat -l        # logical and physical log usage

Verification: Total logical log space = LOGFILES × LOGSIZE. This should be large enough to hold the largest single masking transaction. Watch for logs reported as U-B--- (used, being backed up) piling up, which indicates log backups are not keeping pace.

Parallelism (PDQ) Resources

Purpose: Confirm PDQ is configured so chunk-based merge operations can execute in parallel.

Commands:

onstat -g mgm    # Memory Grant Manager: PDQ memory and query slots

Verification: Confirm MAX_PDQPRIORITY, DS_MAX_QUERIES, and DS_TOTAL_MEMORY are non-zero and sized per section 1.2.

Lock Usage

Purpose: Confirm the lock table is large enough for bulk masking transactions.

Commands:

onstat -k        # locks currently held
onstat -p | grep -i ovlock   # lock overflow count since boot

Verification: A rising ovlock value indicates the lock table is being exhausted. Increase LOCKS, coarsen the table lock mode, or reduce the masking chunk size.

2.3 Assess Database Performance

Check Logical Log Usage During Masking

Purpose: Monitor log utilisation to catch near-full conditions before they abort transactions.
Commands:
```
onstat -l
```
Verification: If the used percentage across logical logs climbs toward the LTXHWM threshold during a single masking job, increase LOGFILES/LOGSIZE or enable TDM chunking to shorten transactions.

Monitor Active Sessions

Purpose: Confirm the TDM service account has an active session and is not blocked.

Commands:

SELECT sid, username, connected, feprogram
FROM sysmaster:syssessions
WHERE username = 'tdm_user';

Verification: Confirm one active session per TDM masking job.

Check for Long Transactions

Purpose: Detect masking transactions approaching the long-transaction limit.

Commands:

onstat -x        # transactions and their log-space consumption

Verification: A transaction whose log usage is approaching LTXHWM will be rolled back. If this occurs, enable chunking on the affected table or increase logical log space.

2.4 Check Session and Query Performance

Identify Long-Running Statements

Purpose: Find masking queries consuming excessive time or resources.

Commands:

SELECT s.sid, s.username, q.sqx_sqlstatement, q.sqx_estcost
FROM sysmaster:syssqexplain q, sysmaster:syssessions s
WHERE q.sqx_sessionid = s.sid
  AND s.username = 'tdm_user';

Verification: Investigate statements with a high estimated cost. Use SET EXPLAIN ON to review the query plan for problematic statements.

Review Execution Plans

Purpose: Determine if inefficient plans are causing slow masking performance.

Commands:

SET EXPLAIN ON;
UPDATE "staging_schema"."my_table"
SET "col1" = 'masked_value'
WHERE "col1" IS NOT NULL;

Verification: Review the sqexplain.out output for sequential scans on large tables. These indicate missing or stale statistics and should be addressed with UPDATE STATISTICS.

Check Lock Waits

Purpose: Identify sessions waiting on locks, which can stall masking jobs.

Commands:

onstat -u | grep -i L    # sessions in a lock wait state

Verification: Lock waits during masking indicate other sessions are holding conflicting locks on staging tables. Ensure no other workloads access the staging schema during masking jobs.

2.5 Evaluate Storage and Space Usage

Check Database Size and Schema Storage

Purpose: The TDM tool estimates schema size from systables (SUM(nrows * rowsize)). Confirm the staging schema has sufficient space.

Commands:

SELECT SUM(nrows * rowsize) / (1024 * 1024) AS size_mb
FROM systables
WHERE owner = 'staging_schema'
  AND tabtype = 'T'
  AND tabid >= 100;

Verification: Ensure sufficient dbspace free space exists for masking temporary tables and sort overflow (budget at least 2× the schema size).

Check Dbspace Free Space

Purpose: Confirm dbspaces have headroom for temp tables created during masking.

Commands:

onstat -d        # dbspace / chunk usage (free vs used pages)

Verification: No dbspace used by the staging schema — or the temp dbspaces listed in DBSPACETEMP — should exceed 80% utilisation.

2.6 Review Statistics and Indexes

Identify Tables with Stale Statistics

Purpose: The TDM tool reads row counts from the nrows column in systables. If statistics are stale, row counts are inaccurate and the optimizer may generate poor masking query plans.

Commands — review cardinality:

SELECT tabname, nrows
FROM systables
WHERE owner = 'staging_schema'
  AND tabtype = 'T'
  AND tabid >= 100
ORDER BY tabname;

Remediation (DBA action — run before masking jobs):

UPDATE STATISTICS HIGH FOR TABLE "staging_schema"."my_table";

Review Index Information

Purpose: The TDM tool reads index metadata from sysindexes and constraint metadata from sysconstraints. Confirm that key columns have appropriate indexes to support masking join and merge operations.

Commands:

SELECT TRIM(i.idxname) AS idxname, TRIM(t.tabname) AS tabname, i.idxtype
FROM sysindexes i
JOIN systables t ON i.tabid = t.tabid
WHERE t.owner = 'staging_schema'
  AND t.tabtype = 'T'
  AND t.tabid >= 100
ORDER BY t.tabname;

Verification: Unique indexes (idxtype = 'U') backing primary and unique keys are critical for merge-based masking performance. Missing indexes on frequently joined columns will result in sequential scans.

Unmaskable Columns

The TDM tool automatically excludes serial/auto-increment columns (SERIAL, SERIAL8, BIGSERIAL) and primary-key, unique, and foreign-key constraint columns from masking, since altering them would break referential integrity. These are identified via syscolumns, sysconstraints, and sysindexes.

2.7 Network Diagnostics

Test Connectivity on the DRDA Port

Purpose: Confirm the enov8 TDM application server can reach the Informix instance over DRDA.

Commands (from the TDM application server):

# Windows
Test-NetConnection -ComputerName <informix-hostname> -Port 9089
# Linux
nc -zv <informix-hostname> 9089

Expected Output (Linux):

Connection to <informix-hostname> 9089 port [tcp] succeeded!

Verify the DRDA Listener

Purpose: Confirm the Informix instance is listening on the DRDA alias and port.

Commands (on the Informix host):

onstat -g dis    # instance and listener status
netstat -tlnp | grep 9089

Verification: Confirm the drsoctcp alias defined in sqlhosts is online and bound to the expected port.

2.8 Verify Security and Patch Levels

Check Informix Version

Purpose: Confirm the Informix version for compatibility. The TDM tool retrieves version information at runtime via DBINFO('version', 'full').

Commands:

SELECT DBINFO('version', 'full') FROM systables WHERE tabid = 1;

Verification: Confirm the instance is running a version of Informix currently supported by IBM.

2.9 Application Interaction

Confirm Driver Installation

Purpose: The TDM tool uses the ibm_db Python library over DRDA, which requires the IBM Informix Client SDK (CSDK) or the IBM Data Server Driver to be installed on the TDM application server.
Verification:
- Confirm ibm_db is installed: pip show ibm_db.
- Confirm the client driver package is present and its environment variables (for example INFORMIXDIR, or IBM_DB_HOME for the Data Server Driver) point to it.
- The connection string used by the TDM tool is:
```
DATABASE=<dbname>;HOSTNAME=<host>;PORT=9089;UID=<user>;PWD=<pass>;DELIMIDENT=y;PROTOCOL=TCPIP;
```

Monitor TDM Session Activity

Purpose: Verify that TDM masking sessions are active and progressing normally.

Commands:

onstat -g ses    # per-session activity, including rows processed

Expected Output: The TDM session's read/write counts should increase over time as masking progresses.

2.10 System Resource Monitoring

Monitor CPU and Memory Usage

Purpose: Detect system-level bottlenecks limiting masking throughput.

Commands (Linux):

top
onstat -g seg    # Informix shared-memory segment usage

Verification: Check for high CPU usage by oninit processes and for memory pressure. If shared memory is undersized, buffer-pool effectiveness degrades.

Check Swap Usage

Purpose: Ensure the OS is not swapping Informix shared memory to disk.
Commands:
```
swapon -s
free -h
```
Verification: Minimal swap usage. Informix performance degrades substantially when the resident shared-memory segment is paged out to swap.

3. Database Permissions

The following table outlines the specific database permissions required for the Enov8 Test Data Management (TDM) Tool to function properly with IBM Informix. Ensure that the TDM service account has the appropriate privileges on both the staging schema and the system catalog tables.

Privilege	Object	Description	Used By
CONNECT	Staging database	Required to establish a session with the target database at connection time.	All
SELECT	`syscolumns`	Required for reading column metadata, decoding data types (`coltype`), and lengths for all profiling, masking, and validation operations.	All
SELECT	`systables`	Required for listing tables, reading row counts (`nrows`), sizes (`rowsize`), and resolving table owners (schemas).	All
SELECT	`sysconstraints`	Required for reading constraint metadata (primary key, unique, foreign key) to identify unmaskable columns.	All, Masking
SELECT	`sysindexes`	Required for reading index metadata used in column analysis and masking operations.	All, Masking
SELECT	`sysusers`	Required for counting database users during forensic scan operations.	Forensic Scan
SELECT, INSERT, UPDATE, DELETE	Target schema tables	Required for reading source data and writing masked values back to staging tables.	Masking
RESOURCE	Staging database	Required for creating temporary masking tables and preview tables (`_ENOV8_PREVIEW`), creating helper indexes, and running `ALTER TABLE`.	Masking
CREATE / DROP TABLE	Target schema	Required for creating and cleaning up temporary tables created during masking operations.	Masking
ALTER TABLE	Target schema	Required for adding and dropping temporary columns, adding row IDs, and enabling/disabling triggers (`SET TRIGGERS`).	Masking
TRUNCATE	Target schema tables	Required for clearing staging tables between masking runs.	Masking

IBM Informix Staging Environment

Introduction​

1. Standard Database Server Configuration​

1.1 Hardware Requirements​

CPU​

Memory (RAM)​

Disk​

Network​

1.2 System-Level Database Optimizations​

Optimizing for Bulk Data Masking​

Keep Optimizer Statistics Current​

1.3 Informix Internal Configuration​

Buffer Pool Sizing​

Lock Configuration​

Checkpoints and Physical Log​

Sessions and Connections​

1.4 Chunking and ROWID Awareness​

1.5 Network Configuration​

1.6 Security and Patch Management​

2. Troubleshooting Steps for DBAs​

2.1 Verify Hardware and OS Configuration​

Check CPU and Memory​

Disk I/O Performance​

2.2 Validate Informix Configuration Parameters​

Logical Log Configuration​

Parallelism (PDQ) Resources​

Lock Usage​

2.3 Assess Database Performance​

Check Logical Log Usage During Masking​

Monitor Active Sessions​

Check for Long Transactions​

2.4 Check Session and Query Performance​

Identify Long-Running Statements​

Review Execution Plans​

Check Lock Waits​

2.5 Evaluate Storage and Space Usage​

Check Database Size and Schema Storage​

Check Dbspace Free Space​

2.6 Review Statistics and Indexes​

Identify Tables with Stale Statistics​

Review Index Information​

2.7 Network Diagnostics​

Test Connectivity on the DRDA Port​

Verify the DRDA Listener​

2.8 Verify Security and Patch Levels​

Check Informix Version​

2.9 Application Interaction​

Confirm Driver Installation​

Monitor TDM Session Activity​

2.10 System Resource Monitoring​

Monitor CPU and Memory Usage​

Check Swap Usage​

3. Database Permissions​

Introduction

1. Standard Database Server Configuration

1.1 Hardware Requirements

CPU

Memory (RAM)

Disk

Network

1.2 System-Level Database Optimizations

Optimizing for Bulk Data Masking

Keep Optimizer Statistics Current

1.3 Informix Internal Configuration

Buffer Pool Sizing

Lock Configuration

Checkpoints and Physical Log

Sessions and Connections

1.4 Chunking and ROWID Awareness

1.5 Network Configuration

1.6 Security and Patch Management

2. Troubleshooting Steps for DBAs

2.1 Verify Hardware and OS Configuration

Check CPU and Memory

Disk I/O Performance

2.2 Validate Informix Configuration Parameters

Logical Log Configuration

Parallelism (PDQ) Resources

Lock Usage

2.3 Assess Database Performance

Check Logical Log Usage During Masking

Monitor Active Sessions

Check for Long Transactions

2.4 Check Session and Query Performance

Identify Long-Running Statements

Review Execution Plans

Check Lock Waits

2.5 Evaluate Storage and Space Usage

Check Database Size and Schema Storage

Check Dbspace Free Space

2.6 Review Statistics and Indexes

Identify Tables with Stale Statistics

Review Index Information

2.7 Network Diagnostics

Test Connectivity on the DRDA Port

Verify the DRDA Listener

2.8 Verify Security and Patch Levels

Check Informix Version

2.9 Application Interaction

Confirm Driver Installation

Monitor TDM Session Activity

2.10 System Resource Monitoring

Monitor CPU and Memory Usage

Check Swap Usage

3. Database Permissions