APPXDatabase CookbookDatabase administration · Change FMS
APPX database administration

Move APPX/IO files to an RDBMS and roll back

Rehearse a file conversion with APPX Change FMS. Compare record counts, keys, and encoded values before allowing writes, and preserve new records if you roll back.

APPX 6.2.1 · Change FMS COUNTRY rehearsal · PostgreSQL 18.7

Prepare the two systems

First set up the RDBMS and prove that APPX can create, save, and reopen the cookbook’s scratch file. Use the cookbook that matches your APPX version, engine bitness, operating system, and driver. RDBMS cookbooks by platform

Rehearse on a copy of the APPX application and database with its matching design and runtime files. This walkthrough uses TST / DMO / COUNTRY on APPX 6.2.1, with native FMS type 9 and PostgreSQL FMS type 6. Record the actual native FMS type and group for each file. A type 1 source returns to type 1 if you roll it back.

Record before conversionWhat to verify
File specificationDatabase, application, version, file name, native FMS type, group, effective path, field overrides, and key definitions.
RDBMS destinationFMS group, identity, schema, resulting table name, database encoding, connection encoding, and any proxy locking settings.
Write accessAPPX users, scheduled work, integrations, and direct database writers that must pause at conversion and rollback.

Use a dedicated target whose tables can be created or replaced. If a target table already has data, identify its owners and take a backup before proceeding.

Back up and count the source

  1. Stop writes to the source. Close the APPX sessions that can still hold the file open. Back up the matching APPX/IO data and key files, design structure, and administration configuration that resolves the original file. Test the backup by opening it in a separate APPX test database with the original file specification.
  2. Open Data File Management and select only the file to convert. Use the native integrity check under Verify Files Menu or the analyzer on a working copy. Record active rows and verify every key.
  3. Save the active key list, the values of representative records, and the totals you will compare after the move. Use Export Files (CSV) to keep a readable baseline if it covers all the fields needed for comparison. Check how the export represents non-ASCII characters before opening it in a spreadsheet or editor.

For the COUNTRY rehearsal, save CANADA / PROVINCE / 2 and ENCODING / Québec / 1. Confirm both in a fresh APPX session. The working-copy analyzer reported two active records and two entries on the primary key.

COUNT REC
VERIFY KEY #ALL
QUIT

Use the matching .dat and .key pair for the analyzer. APPX analyzer and recovery procedures

Convert one file

  1. Open System Administration → Database/Applications → Databases / Applications. Select the scratch database, then Related Applications → Database Management on the application row.
  2. In File Selection, click Unselect All. Select COUNTRY and return to Data File Management. Confirm that only one file is selected.
  3. Click Change FMS. Set New FMS Type to the target type and FMS Group to the tested group. The PostgreSQL example uses 6 and APPX-POSTGRES.
  4. Leave Convert Files checked and click Proceed. Confirm the export and import count and save the log.
The Change FMS dialog with the Convert Files option checked
Change FMS exports and imports the selected files when Convert Files is checked.

WARNING! This Process MAY Destroy Data. Confirm the selected database, application, files, and verified backup before clicking Proceed. Clearing Convert Files changes the specification without moving records.

Change FMS reports two records exported and imported for COUNTRY
Change FMS reports two records exported and imported in the rehearsal.

APPX Change FMS documentation

Verify the destination

  1. Open a fresh APPX session. Confirm the target type and group in File Specifications. Reopen known records using their keys and check the accented value in the original screen.
  2. Connect to the RDBMS independently. Confirm the selected database and schema. Compare the row count and the complete primary key list with the source baseline. For a compound key, compare all its columns together.
  3. Check the indexes against the application’s keys, including uniqueness where required. Compare important numeric values, dates, blank and null handling, non-ASCII text, and business totals. Compare relationships to other files before moving a related set of records into service.

For PostgreSQL, the example returns two rows, two distinct keys, and 5175c3a9626563 for Québec in the UTF-8 database.

SELECT COUNT(*) AS rows, COUNT(DISTINCT country_name) AS keys
FROM public.appx_tst_dmo_country;
SELECT btrim(country_name), btrim(country_region_label), country_no_words,
       encode(convert_to(btrim(country_region_label), 'UTF8'), 'hex') AS utf8_hex
FROM public.appx_tst_dmo_country
ORDER BY country_name;
SELECT indexdef FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'appx_tst_dmo_country';

Put the file in service

Rehearse the rollback while the data is disposable. For the scheduled move, repeat the backup and baseline with writers stopped. Use the same verified target configuration, convert the selected files, and repeat the destination checks before letting users and jobs write again.

Record the cutover time and the database, schema, and FMS group that now serve each file. Preserve the original APPX/IO backup and take a backup of the SQL destination. Keep the logs and key comparisons with the change record.

Roll the file back

SQL target stateRollback procedure
No writes since cutoverKeep writers stopped. Restore the verified APPX/IO snapshot and matching original file specification and group. Reopen the restored file in a new APPX session and compare its counts, keys, and records with the source baseline.
Writes occurred, or you cannot rule them outStop APPX and direct SQL writers. Back up the current SQL tables and capture their record counts, key sets, and important field values. Rehearse the reverse conversion with that current data in a copy. In Change FMS, set the original native type and group, check Convert Files, and compare the restored APPX/IO file with the current SQL baseline before resuming writes.

In the rehearsal, AFTERMOVE / Québec / 1 was added while PostgreSQL served the file. After backing up the SQL table, the reverse Change FMS used type 9, a blank group, and Convert Files checked. APPX reported three records exported and imported. On the resulting APPX/IO working copy, COUNT REC and VERIFY KEY #ALL found three active records and three key entries. A fresh APPX session reopened the three keys, including AFTERMOVE and its accent.

Change FMS reports three records exported and imported on rollback
The reverse conversion includes the record added after cutover.
The restored APPX/IO COUNTRY file displays all three keys in a fresh session
COUNTRY in a fresh APPX session after returning to APPX/IO.

Troubleshooting

The counts differ. Save the conversion log. Confirm the selected files, target schema, rejected records, and unique keys. Keep writes stopped until you can account for each discrepancy.

The row count matches but a key is missing. Compare the full sorted key lists and check added and removed keys. An extra row can hide a missing row in the count.

An accent changed or the import reports an encoding error. Confirm the APPX field storage, ODBC driver and client encoding, and database encoding. Compare the source and destination as text and check the bytes or code points in the destination. Reproduce the problem on a scratch copy before attempting a second conversion.

The FMS changed but the data did not move. Check that Convert Files was selected. Preserve the source backup and inspect both file locations before retrying.