Category: Publish an Application

When an EntitySet or data relationship is created, it is declared to be a table managed either by Zim or by an SQL server. This declaration is done by the field EntType in the data dictionary EntitySet EntitySets or, for data relationships only, in the field RelType in the data dictionary EntitySet Relationships. This flag is checked to determine whether standard Zim code or SQL code (including SQL statements) should be generated when the object is referenced in a Zim command. The following tables indicate how to specify whether Zim or a SQL Server is managing an EntitySet or data relationship.

For example, if the field EntType in the definition of the EntitySet Customers contained the SAM name JDBCSAM, then the EntitySet Customers is a table managed by the JDBC SAM which in turn will handle data from this entity set that is located on a particular SQL database server.

If the field EntType or RelType is either blank or the value “ZIM”, the object continues to be managed by Zim; no other object definitions are required.

When the object is created, a check ensures that the SAM name in the field EntType or RelType is a valid server name.

The date mapping when using two heterogeneous database management systems (for instance Zim and Oracle or Zim and DB2) is a feature that enables date values referenced in Zim to be mapped into date values referenced by the server and vice-versa. This date mapping enables non-standard Zim date values to be represented on the server, so that the Zim application system does not change in functionality (that is changing the Zim code). For example, Zim can accept date values like “0” and “1” or even negative values (instead of the standard 20010101  for example) whereas the Server accepts only valid or null dates.

This date mapping is provided by the mapdate.cfg file, which converts Zim date values to valid Server date values and back again. Any time a Zim date value is sent to the Server, its corresponding value is checked in the table; if matching, this Zim date value assumes the corresponding server date value. Any time a date is requested from the Server, the opposite process is accomplished.

The mapdate.cfg configuration file is an ASCII file and should be located in the directory where the SAM has been installed. The mapdate.cfg file can contain any number of lines, each containing a pair of date values, comments, or both.

The pair of date values consists of a Zim date value and a server value, separated by a semi-colon (;). The format of a Zim date value follows Zim rules for dates (for instance 20010101, 0, 2) while the server date values must follow the format YYYY-MM-DD (i.e. 2005-12-31). If one of the date values is omitted, its corresponding value is assumed to be null.

The comments begin with a “%” and follow the same rules of Zim comments.

Use only 8 digits to represent the mapping date for the client. In the example below, a “0” in Zim is mapped onto 1001-01-01 on the database server.

An example of a mapdate.cfg file follows:

%This is date mapping for the accounting system.

%This is date mapping for the accounting system.
0;1001-01-1 % converts Zim dates containing zeros to this date on the server
1;1001-01-02
-1;1001-02-03 % any Zim date value can be used
; 3005-05-05 % null date values in Zim are converted to this date on the server
20031231;    % all Zim dates containing 2003/12/31 are converted to nulls

Use caution when implementing date mapping as shown in the last example, since all null dates on the server are converted back to 20031231.

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.

If there is to be more than one Zim application compiled against a given SQL server database, it is best to uniquely identify each application. Conflicts can occur between programs with the same name. An application can be flagged with an ID by including the following entry in the database configuration file (zimconfig.zim):

SQL DATABASE NAME xxx

where xxx is a three-character ID differentiating Zim programs from different Zim databases compiled against the same SQL server database.

If the Zim application already exists, then some modifications to the data dictionary definitions for EntitySets and data relationships must be performed. Specifically, the field “EntType” in the data dictionary EntitySet “EntitySets” and/or the field “RelType” in the data dictionary entity set “Relationships” must be flagged as residing on the SQL server. These fields should be set to the name associated with the server access module and the objects must be re-created.

If the application is to be developed for execution against a pre-existing SQL server database then it is likely that the table definitions have been loaded into the Zim data dictionary from the SQL server. The “EntType” fields should be set to the name associated with the server access module being used, and the objects recreated. Take advantage of Zim’s ability to access local data objects.

EntitySets and data relationships in Zim can also be defined to map to SQL “views”. Again, the Zim field list need not include all columns of the view, nor need the field sequence correspond to that of the columns of the view. However, if the corresponding EntitySet or data relationship must be updated, then the SQL view must be classified as being able to be updated according to the rules of the SQL server being used. This means that the definition of the view must not include the DISTINCT operator or the ORDER BY or GROUP BY clauses, that the view is defined over a single table only (no multi-table joins), and that columns of the base table which are not in the view must permit NULL values. These are typical limitations of SQL database servers. These restrictions on views are such that it is more practical to use base tables rather than views.

The mapping between an SQL table and a Zim EntitySet or data relationship need not be complete with respect to the set of columns owned by the table. Zim retrieves only the columns required from an SQL table; it generates the minimal “select-column-list” to satisfy the requirements of the corresponding Zim command. The Zim Definition Interface imports all the columns of an SQL table translated into Zim field definitions, but only those fields relevant to the application being developed need be used. Also, the defined sequence of the fields in a Zim EntitySet or data relationship need not correspond to that of the columns of the SQL table to which it maps.

It is important to note that if Zim “ADD” commands are to be executed against an SQL table whose Zim field list is a subset of the complete table column list, then those table columns which are not included in the Zim definition must enable the value NULL. An “INSERT” statement generated by Zim cannot specify values for columns about which it has no knowledge.

Database systems, including Zim, have rules as to how objects such as database tables and columns (EntitySets and fields and Zim) are named. In Zim, object names must be 1 to 18 characters in length and start with an alphabetic character, an underscore (“_”), or dollar sign (“$”). If the name of a SQL table to which you wish to map an EntitySet is invalid in Zim or the name of an EntitySet which you wish to export to a SQL server is invalid on the server, you can specify an alternate name in the “Remote Name” field of EntitySets, Relationships, or Fields. All generated SQL syntax uses the specified Remote Name. There is no need to specify a Remote Name if the Zim name and the SQL name are identical.

For example, some SQL servers enable table and column names with embedded blanks if the name is enclosed in double quotation marks, which is illegal syntax in Zim. In such cases, specify a Remote Name with the server table name in double quotation marks so that the correct SQL server syntax is generated.

Most SQL servers support the concept that each table is “owned” by a “user” or a “creator”. This enables multiple tables with the same name, but different owners, to be created. These servers also enable users to access tables owned by other users.

When a user has some private information stored in database space owned by him, he is the creator or owner of these tables. If, for the purposes of achieving data manipulation objectives, he needs to access tables “owned” by some other user, he does this by qualifying the name of the table(s) to be accessed with the name of the owner. To access a table called “CUSTOMERS” created by a user named “BOB”, the correct SQL syntax for the table reference would be

SELECT FROM BOB.CUSTOMERS

Zim supports this capability by providing the ability to specify a “Remote Owner” of an EntitySet or data relationship in the data dictionary field ” RemoteOwnerName“. Any EntitySet or data relationship whose Remote Owner is blank or null is assumed to be owned by the user. The EntitySet name is not qualified in any generated SQL syntax. A reference to an EntitySet or data relationship whose Remote Owner is not blank or null causes the name to be qualified with the RemoteOwnerName in the generated SQL syntax. Thus to generate the syntax such as

BOB.CUSTOMERS

the EntitySet “CUSTOMERS” must have its RemoteOwnerName set to “BOB”.

In general, SQL servers support limited indexing capabilities. Indexes can be maintained on individual columns (multi-valued or unique) and also on the concatenation of two or more columns (multi-valued or unique). In Zim terms, an indexed virtual field that corresponds to an SQL concatenated index can be represented only by an expression of the form:

$concat(field1,field2,…)

where “field1”, “field2” etc. are the names of fields only. No other expression operators are permitted.

This means that indexed virtual fields defined by such expressions can be mapped to indexes in the SQL database that are defined as the concatenation of two or more columns. The multi-valued/unique options for such indexes can also be mapped to SQL.

The Zim Definition Interface supports the export of indexed virtual fields whose associated field expressions conform to the simple concatenation structure indicated above. Performance improvements can be achieved from the SQL server for virtual field indexes so defined. Indexed virtual field expressions of any other form are not supported in SQL and accordingly, no performance improvements can be achieved. For applications whose design rests on the performance resulting from indexed virtual fields which do not conform to the above format (e.g. substrings of fields), test the application as it exists. If performance is not acceptable, then the virtual fields must be implemented as additional real fields, necessitating the creation of additional indexed columns to the corresponding SQL table(s).