To migrate a Zim host based application into a client/server application using a SQL-based server, you must prepare the data properly to ensure that the migration is successful.

Basically programs can stay as they are, but some fine-tuning is necessary for performance adjustments.

The basic steps are outlined below.

Model revision and adaptation

The first steps in migrating the data involve ensuring that the data model has been adapted to work in a client-server environment. This involves a number of individual steps.

Entity sets data relationships have a unique key: Ensure that all tables have at least one and only one primary key with the attributes UNIQUE or PRIMARY and REQUIRED. All data relationships must also have a primary key. Normally, data relationships do not have unique keys. If necessary, create a virtual field concatenating the primary keys of related EntitySets.

A Zim virtual field can be used as the primary key for a table. In this case, its field expression (Default Value) should contain only the concatenation of other fields. No other function or expression is permitted for SQL restrictions.

An error occurs during the export process if an attempt is made to export an indexed virtual field whose expression does not abide by this rule.

Table Naming

Any tables whose name starts with the symbol (underscore) must have its name changed. SQL servers do not permit tables to have names starting with this character.

Numeric Fields

Numeric fields of data type Int, Longint and Vastint which have decimal places need to be altered as well. The length specified for these types of fields should be the maximum allowed; use 4, 9, and 15 for Int, Longint, and Vastint fields respectively.

Table Relationships

Joins between tables located on the server and on the client can be created in Zim, however, a performance degradation occurs as a result of these types of relationships. Avoid this type of construction, and if possible, change these types of relationships before migrating the data.

If per-user EntitySets have relationships with tables located on the server side, reallocate the per-user EntitySets to the server side as well. Ensure that you modify the primary key to indicate the user identification.

Relationships between EntitySets and documents can cause performance problems when the document is located on the client side and the entity set on the server side. Copy the document to the server side before executing any command that relates the document and the table.

Ensure that there are no relationships between EntitySets and forms. If any relationships of this type exist, change them, as they can result in application malfunctions.

Table creation on the server

1. Use the DC to generate the Data Definition Language (DDL) for the table creation on the server and create them.
2. Using server tools, create the reference integrity rules for each index, where applicable.
3. For each Zim virtual field whose definition is not a simple field concatenation, create a real field and a trigger on the server, in order to evaluate it.

Table creation on the client side

1. Change the table type of all EntitySet tables and data relationships tables to the name of the server (e.g. Oracle).
2. Fine-tune the application, looking for performance bottlenecks. Run the main programs (those that are most critical,  most used, or both) of the application. Ensure that the  trace feature is enabled, in order to be able to visually inspect points where the trace encounters problems. If the trace stops, a performance problem is likely the issue. Ensure that you verify these points to enhance performance.
3. Where possible, combine multiple find commands (such as find .. -> set1, find set1 …-> set2 and so on) into single commands. This type of change decreases traffic between the client and the server.

Exporting Zim definition to SQL

1. The DC Export to SQL tools export and create the SQL tables on the server side. The DC Export also generates a file containing the SQL syntax (such as CREATE TABLE) to create the tables and, using the SQLEXEC command, sends and executes the script on the server side.
2. The CREATE TABLE syntax for a specific SQL server can include additional syntax that relates to the physical storage characteristics of the created table. This syntax varies widely from server to server. If you want to include the specification of some or all optional physical table characteristic parameters, enter the appropriate syntax in the field “Physical Table Characteristics Syntax:”. The syntax specified is appended to the CREATE TABLE statement for each name entered in the form.

For example, entering

   “TABLESPACE ts_account”

ensures that the table is created in the “ts_account” table space.

3. The SQLEXEC traces the SQL statements that it is executing, as well as identifying any error conditions. In general, the only error condition that is acceptable is an error from the DROP TABLE statement when the table does not exist. All other error conditions should be investigated. The SQLEXEC does not stop executing on encountering an error condition.