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
Never miss an opportunity! Create an alert based on the job you applied for.
