DEV Community

Franz
Franz

Posted on

🚀 Migrating Informix Overflow Tables for Uniface – A Simple Step by Step Guide

#database #backend #tutorial

🤖 This blog post was created with the help of an AI assistant, based on the official Uniface documentation.

If you are using Uniface with an Informix database and your application uses overflow tables (for very long text or large variable-length fields), you must be careful when you upgrade your Uniface version. A change in how Uniface defines table sizes can lead to data corruption if you do not migrate your tables correctly. 😱

🧠 What changed in Uniface?

In older Uniface Informix connectors (before version V8.2.03-B306), the size of the base part of a variable-length field depended on the total size of the fixed-length fields in the table. It could be 2, 4, 6, or 8 KB, minus the sum of all fixed-length field sizes.

From Uniface V8.2.03-B306 and later, the base size is always 8 KB minus the size of the fixed-length fields. This is different from the previous behavior and makes existing tables created with old SQL scripts incompatible with the new driver.

The important point: the table size is stored in the table definition in the database when the table is created. So old tables keep their old layout, and new drivers expect a new layout. That’s where the trouble starts.

⚠️ Why you MUST migrate

If you use the Informix drivers of Uniface V8.2.03-B306 or later on tables created with old SQL scripts, data in variable-length fields that use overflow tables can be corrupted during storage. No error is returned by the database, the connector, or Uniface. Everything looks fine, but the data is damaged in the background. 💣

To avoid this silent corruption, you need to recreate the tables with SQL scripts generated by Uniface V8.2.03-B306 or later and then import the data again.

📦 What are overflow tables (in simple words)?

An overflow table is an extra table that stores data that does not fit into the main (base) table row. This is typically used for long text, large binary fields, or big variable-length fields. The main table contains a pointer to the overflow table, where the rest of the data is stored.

Think of it like this:

  • The base table is a small box with basic information and a link.
  • The overflow table is a bigger box where the long text or large data is stored.

If the size rules for the base table change, but the data is still stored with the old layout, the links between base table and overflow table can break or overlap, and your data becomes inconsistent.

🛠️ Migration steps – High level overview

The Uniface documentation describes a clear set of steps you must follow when your application model contains fields that use overflow tables. Here is the process in simple language:

  1. Backup your Informix database. Always take a full backup before changing tables. If something goes wrong, you can restore your data. 🔙
  2. Use an older Uniface (V8.2.03-B305 or older) to analyze your models. Find all entities that contain fields using overflow tables. This is important so you do not miss any tables.
  3. Export the data with Uniface (not with database tools). Still using the older Uniface version, export all data of these entities to TRX files. You can use:
*   the _Convert Data_ facility, or
*   the `/cpy` command line switch.

Do **not** use Informix utilities like `dbexport` etc., because only Uniface understands the overflow table structure correctly.
Enter fullscreen mode Exit fullscreen mode
  1. Drop the old tables using SQL. In Informix, use SQL commands to drop all involved tables (base and related overflow tables).
  2. Install Uniface V8.2.03-B306 or later and configure it for your Informix database. No special extra assignment settings are needed, apart from your normal DB configuration.
  3. Recreate the tables using new SQL scripts. Let Uniface 8.2.03-B306 or later create the SQL scripts (for example with the Create Table facility) and use these scripts to create the tables in Informix. Now the tables are compatible with the new driver and use the correct 8 KB base size rule.
  4. Import the data back using Uniface /cpy. With the new Uniface version, import all previously exported TRX data back into the newly created tables using the /cpy command line switch.

💡 Example scenario

Imagine your application has an entity CUSTOMER_NOTES with a field LONG_COMMENT that uses an overflow table because users often write very long comments about customers.

In Uniface V8.2.03-B305, you created the tables in Informix using old SQL scripts. The base table has a smaller variable-length area. In Uniface V8.2.03-B306, the driver now expects that the base table always has an 8 KB area for variable-length fields (minus fixed field sizes).

If you simply install the new Uniface version and point it to the same Informix database, everything seems to work. Users can still store comments. But because the layout in the DB does not match what the new driver expects, the content in LONG_COMMENT may get corrupted when stored or updated, and no errors are raised.

By following the migration steps above, you export all CUSTOMER_NOTES data using the old driver (which still understands the old layout), drop the tables, recreate them with the new SQL scripts (correct layout), and then import the data using the new driver. After that, the data and the table definitions are in sync again. ✅

🐞 Typical problems to watch out for

  • Using database export tools instead of Uniface Tools like dbexport do not know how Uniface stores overflow data. If you use them instead of Uniface TRX export, you risk losing the logical structure of your overflow fields.
  • Forgetting some entities If you miss an entity that uses overflow tables, that part of your data might still get corrupted with the new driver. Always carefully analyze your application model first.
  • Skipping the drop step If you do not drop and recreate the tables, they will still have the old layout, even if you upgrade Uniface. The driver alone cannot fix this.
  • No backup If something goes wrong during migration and you have no backup, you cannot easily recover. Always backup before you start.

🧾 Simple command line example with /cpy

Here is a very simple example of how the workflow with /cpy could look in practice. The exact syntax depends on your environment, but the idea is:

; Step 1: Export data with old Uniface version uniface.exe /cpy EXPORT_MODEL /asn=old.asn /str=export_customer_notes.str ; ...drop and recreate tables with new Uniface scripts... ; Step 2: Import data with new Uniface version uniface.exe /cpy IMPORT_MODEL /asn=new.asn /str=import_customer_notes.str
Enter fullscreen mode Exit fullscreen mode

In this example:

  • EXPORT_MODEL and IMPORT_MODEL are components (or operations) designed to handle the copy logic.
  • .asn files define database and driver assignments.
  • .str files define the copy structure (which entities and fields are included).

The important part is: both export and import are done by Uniface itself, not by external Informix utilities, so the overflow table handling stays consistent.

✅ Summary

  • Uniface changed how it calculates the base size for variable-length fields in Informix from version V8.2.03-B306 onwards.
  • Old tables created with old SQL scripts are incompatible with the new driver and can cause silent data corruption for overflow fields.
  • You must export your data with an older Uniface version, drop and recreate the tables using new SQL scripts, and then import the data with the newer Uniface version.
  • Always use Uniface tools (TRX, /cpy) for overflow data and never rely only on database export utilities.

If you follow this migration process, your Informix overflow tables will work correctly with newer Uniface Informix drivers, and your data will stay safe. 💾✨

Top comments (0)