Mainframe COBOL/VSAM to Ellucian Banner Student Data Migration

/* COBOL Copybook: STUDENT-REC copybook defines VSAM input layout */
01  STUDENT-REC.
    05  S-ID              PIC X(09).
    05  S-LAST-NAME       PIC X(30).
    05  S-FIRST-NAME      PIC X(20).
    05  S-DOB             PIC 9(08)   COMP-3.
    05  S-SSN             PIC X(11).
    05  S-ETHNIC-CODE     PIC X(02).
    05  S-ADMIT-TERM      PIC 9(06).
    05  S-PROGRAM-CODE    PIC X(08).

/* Banner STVETHN (ethnicity), SGBSTDN (student base), SPRIDEN (person) */

/* Step 1: JCL to unload VSAM to sequential with Cobol copybook */
//STEP1  EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//OUTFILE  DD DSN=HLQ.VSAM.OUTPUT,DISP=(NEW,PASS),RECFM=FB,LRECL=94
//SYSIN    DD *
  REPRO INFILE(VSAM.IN) OUTFILE(OUTFILE)

/* Step 2: Batch load control file for Banner SQL*Loader equivalent */
LOAD DATA
INFILE 'student_vsam.dat' "str '\n'"
INTO TABLE GLBEXTR APPEND
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
  GLBEXTR_PIDM              CONSTANT -1,
  GLBEXTR_TABLE_NAME        CONSTANT 'SPRIDEN',
  GLBEXTR_COLUMN_NAME       CONSTANT 'SPRIDEN_SURID',
  GLBEXTR_SEQ_NUM           SEQUENCE (MAX,1),
  GLBEXTR_VALUE             CHAR,
  GLBEXTR_USER_ID           CONSTANT 'MIGRATION',
  GLBEXTR_ACTIVITY_DATE     SYSDATE
)

/* Step 3: Field mapping table for SPRIDEN population */
/* VSAM Byte Offset → Banner Column → Transform Logic */

How we'd take this from discovery to a production-safe cutover — the phases, the canonical mapping, and the edge cases that bite.

Migrating legacy mainframe student records from VSAM into Ellucian Banner requires parsing COBOL copybook record layouts, transforming packed-decimal and date formats, and loading through Banner's staging infrastructure. The following pathway assumes VSAM KSDS files keyed on student ID with sequential extraction to an intermediate dataset, Banner 8 or 9 on Oracle, and the use of Banner's SDBSTDN staging tables for student term data.

Implementation Phases

1. Discovery and Source Analysis — Obtain all production COBOL copybooks for student master (STUMAST), address (STUADDR), and program enrollment files. Document PIC clauses, OCCURS clauses, COMP/COMP-3 fields, and redefine relationships. Extract sample records using IDCAMS PRINT or a custom COBOL program to verify record lengths and block sizes.
2. Banner Target Mapping — Map VSAM record segments to Banner base tables: SPBPERS (biographical), SPRADDR (addresses), SGBSTDN (student term status), and GORPRGE (race/ethnicity). Identify which Banner columns are mandatory versus optional; verify term codes in STVTERM exist for all migration terms.
3. Extraction and Parsing — Write a COBOL program (or use a tool such as IBM Data Studio / Rational Developer) to read VSAM sequentially, expand OCCURS clauses into normalized rows, convert COMP-3 packed-decimal to display format, and write delimited output (CSV or fixed-width) to a z/OS dataset or transfer to an ETL server via FTP in binary mode.
4. Data Transformation and Cleansing — On the ETL platform, parse the copybook-derived schema definition. Convert mainframe dates (CYYDDD Julian or YYMMDD) to Oracle DATE format. Pad/trim alphanumeric fields to Banner column lengths. Handle redefines by selecting the appropriate source segment based on a type code field.
5. Staging Load — Use SQL*Loader or Banner's BNKSTU or equivalent import utility to populate Banner staging tables (e.g., SDBSTDN, SDBADDR). Disable Banner triggers and rule-based validation during bulk load, then re-enable for post-load checks. Load addresses before students due to referential integrity.
6. Validation and Reconciliation — Run Banner-provided validation reports (SZBVALD or custom SQL) against staging tables. Cross-check record counts, term enrollment totals, and demographic field distributions against source VSAM totals. Flag and correct duplicates on PIDM or ID.
7. Production Cutover Planning — Define a freeze period: no new VSAM updates during the migration window. Perform a final incremental extraction. Load, validate, and sign off. Establish rollback triggers: retain VSAM backup and pre-load Banner snapshots.
8. Cutover Execution — Run the production load using the same staging-and-validate process. Upon successful completion, activate Banner as the system of record. Retain the VSAM dataset in read-only archive status for 90 days per audit policy.
9. Post-Migration Audit — Validate 5% sample of student records field-by-field against source. Confirm enrollment counts per term match. Close out VSAM files and update data lineage documentation.

Field Mapping

Edge Cases

• Duplicate PIDM collisions — A student may already exist in Banner (from prior term imports) with a different SPRIDEN ID; load must update existing PIDM rather than create a new one, or the reconciliation report will show inflated headcount.
• OCCURS with depending-on (ODO) — Variable-length address blocks in COBOL can cause misaligned field reads if the ETL assumes fixed offsets; use the actual runtime value of the DEPENDING ON counter, not the maximum.
• COMP-3 date fields with invalid century bits — A CYYDDD value of 099001 represents 1990-01-01 but a malformed century nibble can produce 1890 or 2090; flag and review any CYY less than 050 (2050) or greater than 130 (2130).
• Missing mandatory Banner columns — Source records with NULL SSN, missing birth date, or blank last name must be staged separately; Banner rejects these rows at validation and the load job will abend if referential integrity is enforced.
• Redefines with conflicting data types — A single VSAM record may redefine a numeric field as alphanumeric for display; selecting the wrong segment produces garbled values (e.g., packed bits interpreted as printable ASCII).
• Term code gaps — A student may have enrollment records for terms not yet defined in STVTERM; inserting SGBSTDN rows with unknown term codes violates the foreign key and corrupts enrollment reporting.
• Concurrent updates during extraction window — If VSAM is updated while the incremental extract runs, records may be skipped or duplicated; lock the file with ENQ/DEQ or take a VSAM cluster snapshot before extraction.
• Ethnicity reclassification — Banner stores race data per term (GORPRGE with term code); a single legacy ethnicity record must be replicated across all active enrollment terms or enrollment queries will return incomplete ethnicity profiles.

Cutover

Cutover must be reversible: before the migration window, take an Oracle export snapshot of all Banner student tables (SPBPERS, SPRADDR, SGBSTDN, GORPRGE) using expdp with FLASHBACK_SCN. Retain the VSAM cluster backup on tape for 90 days. Execute the final incremental extraction immediately before the load; apply the same staged validation pipeline. Upon successful post-load reconciliation (record counts within 0.01% tolerance, 100% of sampled fields match source), promote the staging records from a shadow schema to the production Banner owner. If a rollback is required, drop and re-import the pre-migration snapshot; re-enable VSAM as the system of record and resynchronize any updates that occurred during the window. Schedule a post-cutover job to rebuild any Banner-derived summary tables (e.g., enrollment aggregates) that are not automatically refreshed by triggers.