Another database
Same structure, new system.
Tables, keys and data move, the types are translated. This is the case this page shows.
Some conversion is always needed: translating types, cleaning data, converting values that do not exist as such in the target. Most of the time, though, goes into making it all fit together. There are hundreds of tables rather than a handful, the order follows from the foreign keys, every stage is checked, the time window is limited, and at four in the morning there are a few red tables you want to repeat without the others.
JOX covers both. The data manipulations of the conversion run as SQL and OS steps in JOX, traceable, with their output and repeatable one by one. JOX also runs all steps in the right order, as parallel as your databases can take, checks after every stage and repeats exactly the steps that failed. This page shows it with a complete case.
In some migrations the structure stays the same and only the database changes; in others the target is an entirely different structure. Often it is a new version of your own application, with a few changed tables and columns.
Tables, keys and data move, the types are translated. This is the case this page shows.
Tables are merged or split, values re-keyed, master data cleaned. Each of these transformations is a step of its own with its own check, and it can be repeated.
A few new columns, a split table, changed keys: moving to the new version is a small migration with the same demands on order, checks and repeatability.
In all three cases the migration becomes a repeatable, deterministic process in JOX. It runs as parallel as your systems allow, and every pass has the same steps in the same order. The results can be traced at any time: every step has a status and output, every table its reconciliation.
The schema was generated for the demo. It is large enough that nobody wants to write the steps by hand, and it contains the traps that real migrations fail on.
Master data, organisation, customers and contacts, sales, products, orders, invoices, contracts, service, marketing, activities. On top of that come auto-increment, views and grants to two roles, the objects that are easily forgotten.
A credit note line hangs on the credit note, the invoice, the order and the customer, down to the master data. The order is derived from the schema, not kept in a list.
A good half of the tables are small, the rest go up to 700,000 rows, and four transaction tables are partitioned by month. Each size class has a loading path of its own.
In JOX the migration is a group with a few hundred job templates; every table has its own jobs and dependencies. Every run is an order of this group, and the tenth run takes no more effort than the first.
The run checks that the export directory exists and that the target can read it, that the partitions still match the planned ones, and that the roles the grants go to exist. Leftovers of an earlier run are cleared from the target. If a check fails, nothing starts.
Small tables go from source to target in one job. Large ones
are unloaded and loaded with COPY; the file is
written right where the target reads it. Partitioned tables
get this pair per partition.
The foreign key from order to customer waits only for these two tables, not for “all the data”. Then come sequences with the right start value, views and grants.
Row count and checksum of both sides are stored per table and per run in a control table in the target. A SQL check evaluates them and reports green or red, with output and repeatable.
The steps are arranged in subgroups: pre-check, clean-up, create tables, move, follow-up work, reconciliation. A run can be limited to a part, single jobs can be repeated, and the order can be paused.
The final clean-up runs only after a manual release. After the run, the order map shows the critical path: which table determined the run time, and why.
A migration is a complicated undertaking, and the more often it has been rehearsed, the better it turns out. So you plan many passes, not just one rehearsal before the real thing. Since every pass is an order, in JOX that is the normal case and needs no rebuilding.
Nobody built the migration group in the demo by hand. It is generated by a planner, a tool of our own that we use to prepare migration projects. It reads the data dictionary of the source, orders the tables by foreign keys and size, chooses the path for each table and writes the result as a group that JOX imports.
The planner itself is not part of the demo; we bring it into a project. For your schema it needs neither data nor access, only the result of a read-only script over your dictionary. From that we create your plan on our side.
We have run migrations this way for years: an analysis tool plans, JOX executes. So the demo shows a method that has proven itself in practice.
Translating types and moving data can be solved. It gets difficult with the meaning of the data, and that is also where the success of a migration is decided.
A column in the old system often means something other than its name says, and the new model no longer has it in that form. Mapping old to new is a business decision.
For statuses, products, tariffs and categories someone has to decide what each old value becomes in the new system. These rules are made during the project and change along the way. After every change they have to run over all the data again.
Duplicates, incomplete records, values that should not exist according to the rules. What is cleaned, taken over or rejected is decided by the business side, not by the loading program.
Open orders, running contracts, invoices in the middle of their cycle: they have to carry on in the new system with their history, without a break.
“It ran through” is not enough for that. The business side has to see what happened to each record and why. Reconciliations, reports and the output of every step make the result verifiable.
Because the questions are business questions, the answers come bit by bit. If the migration can be repeated completely and identically at any time, every answer is a short step and not a new project.
For this we bring our experience along with JOX. We support the business clarification and turn every decision into a traceable step of the process. Technical quirks of the databases such as types, date values and keys are handled by the process along the way; in the demo run they show up as steps of their own.
We have carried out database migrations in many settings: between different systems, onto new structures and onto new versions of applications. The decisive questions were almost always business questions: what the data means, by which rules it moves into the new system and how the business side can sign off the result.
JOX controls migrations between all the databases it connects to: Oracle, PostgreSQL, MariaDB and MySQL, DB2, Informix, Snowflake and more. Process, checks and stages stay the same; only the dictionary queries and the unload format differ. Write to us which systems you use.
After registering you get a JOX environment of your own with this case: source, target and the migration group, ready for the first order. You can also tell us about your migration directly.