Db2 for i Specialist

Job Title: Db2 for i Specialist

Location: NJ preferred but must be based in the USA

Duration: 3 Months+

Role summary

Owns everything on the IBM i side of the platform: the Db2 for i files and fields, journaling readiness, the CDC source configuration, initial loads, source-side reconciliation and the inbound path through Client’s existing IBM i programs. Reads the RPG or COBOL behind the data to explain what fields mean, and works alongside Client’s IBM i team, without changing production programs.

Key responsibilities

Discovery: files, fields and readiness

·       Build the active-table inventory from the roughly 20,000 tables using IBM i catalog services: QSYS2.SYSTABLESTAT and SYSPARTITIONSTAT (LAST_USED_TIMESTAMP, LAST_CHANGE_TIMESTAMP, DAYS_USED_COUNT) and QSYS2.OBJECT_STATISTICS. Check LAST_RESET_TIMESTAMP, because usage statistics can be reset.

·       Map libraries, physical and logical files, DDS-described and SQL-defined tables, and multi-member files.

·       Confirm a unique key for every in-scope file. Client reports no programs rely on record numbers; verify that each file has a key CDC and reconciliation can use.

·       Document data representation: packed and zoned decimal, legacy date formats such as century-flag dates, space-padded CHAR, CCSID and EBCDIC handling, hex (CCSID 65535) fields and null-capable columns.

·       Read RPG IV, ILE RPG, free-form RPG and COBOL programs to explain field meanings, status codes and business rules for the data dictionary.

·       Confirm journaling readiness for CDC: before and after images (IMAGES(*BOTH)), minimal entry data settings, receiver management (MNGRCV, DLTRCV) and receiver retention.

·       Agree journal receiver retention with Client’s administrators so the CDC tool and Precisely Assure iTERA remote journaling both have what they need.

·       Establish whether eligibility and related programs run under commitment control, and which files share a journal.

·       Measure journal volume and system load during the morning enrollment runs.

·       Assess Client’s IBM i release (V7R4M0; IBM standard support ended 30 September 2026) and any planned upgrade against the shortlisted CDC tools’ support statements.

CDC source build and initial load

·       Install and configure the selected CDC tool’s IBM i components: product library, user profiles and authorities, subsystem and job settings.

·       Configure source datastores and table mappings, and tune for minimal production impact.

·       Plan and run initial loads, including the 30-year history, in windows that avoid 9:00 AM to 5:00 PM and the morning enrollment processing.

·       Handle library moves, file changes and DDL with the CDC tool’s documented procedures, and agree a change process with Client so source changes do not silently break replication.

·       Work with Client’s administrators on PTF levels, IPL windows and journal operations that affect replication.

Reconciliation and data quality

·       Write source-side, key-based extract and count queries for scheduled reconciliation against SQL Server, normalizing padded CHAR, decimal and date representations.

·       Investigate and explain mismatches, and repair them through the CDC tool’s supported refresh mechanisms.

Reports and the inbound path

·       Analyze report jobs run from the IBM i scheduler (RPG and COBOL print programs, Query, spooled output) to specify what each migrated report must reproduce.

·       Identify the existing IBM i programs that apply each type of inbound update, and expose them as SQL stored procedures or program calls with defined parameters.

·       Define commitment control, error handling and auditing for inbound calls, so that no integration writes to Db2 tables directly.

Knowledge transfer

·       Document the source configuration, journaling and receiver procedures, initial-load procedures and recovery steps.

·       Train Client’s shadow staff and IBM i administrators to operate and troubleshoot the CDC source.

Deliverables

·       Active-table inventory and recommended scope order

·       File and field data dictionary with business meanings, keys and data representations

·       Journaling readiness assessment, including receiver retention alongside Assure iTERA

·       IBM i release and upgrade impact note for the CDC tool decision

·       CDC source installation and configuration record

·       Initial-load and history-load plan with run logs

·       Source-side reconciliation queries and mismatch analysis

·       Inbound program inventory and stored procedure or program-call specifications

·       IBM i runbooks and knowledge-transfer sessions

Required qualifications

·       10+ years on IBM i (AS/400, iSeries) with Db2 for i in production environments.

·       Expert Db2 for i SQL, including the QSYS2 catalog views and IBM i services.

·       Deep knowledge of journaling: STRJRNPF and CHGJRNOBJ, journal receivers, IMAGES(*BOTH), remote journaling and commitment control.

·       Able to read and explain RPG IV, ILE RPG, free-form RPG and COBOL, and to write CL.

·       DDS and SQL-defined files, logical files, keys and access paths, and multi-member files.

·       Packed and zoned decimal, legacy date formats and CCSID/EBCDIC conversion issues.

·       Hands-on experience with at least one CDC or replication tool on an IBM i source.

·       Experience with IBM i 7.4 or later, PTF management and work management.

·       Experience exposing IBM i programs as SQL stored procedures or callable programs.

Preferred qualifications

·       IBM Data Replication CDC for Db2 for i, Precisely Connect CDC or Qlik Replicate with an IBM i source.

·       Experience alongside IBM i high-availability products such as Precisely Assure iTERA or MIMIX.

·       IBM i release upgrades from 7.4 to 7.5 or 7.6.

·       Health insurance or benefits administration data: eligibility, enrollment, claims.

·       Data reconciliation between IBM i and SQL Server.

Technical environment

·       IBM i V7R4M0, Db2 for i, journals and remote journaling

·       RPG IV, ILE RPG, free-form RPG, COBOL, CL, DDS

·       QSYS2 catalog services, IBM i job scheduler

·       Precisely Assure iTERA (existing high availability to DR)

·       CDC tool to be selected in discovery