Category: ZIM-X Database Connectivity

If the Zim application being developed is based on an existing SQL database, then the Zim definition interface needs to be executed in order to import the definitions of the relevant tables from the SQL server and populate the Zim data dictionary so that the application can be developed.

Conversely, if the Zim application has been developed, but the database is not already resident in the SQL server, then the Zim definition interface would be invoked to export the Zim EntitySet and data relationship definitions to the SQL server. For some SQL servers, this requires a new database on the server. This is accomplished using a utility program/facility provided with the SQL server.

The Zim definition interface only imports and exports definitions. There is no facility to keep the Zim definitions and the SQL servers definitions in sync after they have been imported or exported. Any changes to definition in one must be made explicitly in the other. Failure to do so leads to unexpected results and errors.

The return value of $DDEFunction is one of the following. A negative result indicates failure.

Windows

Zim Server can be started manually from the Start menu, or the Startup folder.

You can also start the process from the command prompt:

start zsvserv.exe [-p <port number> | -s <service>]

This command starts Zim Server in its own console window. The -p option sets the port number on which the server is listening. The -s option does the same thing, but here you enter a service name. The server uses this name to retrieve the port number from the services file.

Linux/UNIX

The Zim Server process can be started manually or it can be invoked from a system start-up script. This process is started by the command zimserver, that is installed in the directory pointed to by the ZIM environment variable. You should start the process with the command line:

nohup zimserver [-p <port number> | -s <service>] &

Note: The same command line could be placed in a system start-up script.

Using NOHUP means that you can log off from the current session and the Zim Server process continues to run. Using ‘&’, means that the Zim Server process runs in the background and that you can continue to enter commands at your terminal. Output from ZIMSERVER and the Database Agent process that would normally go to the terminal are rerouted by NOHUP to a file called nohup.out in the current directory. This file normally contains lines showing that the Zim Server process has begun and ended. It also contains any terminal output from a Database Agent processes started by the Zim Server. It is not necessary to retain the contents of this file and you should occasionally check the size of nohup.out and delete it if necessary.

The -p option sets the port number on which the server is listening. The -s option performs the same function, but using the -s option requires that you enter a service. The server uses this name to retrieve the port number from the services file.

Errors generated during the Zim Server Connectivity activity can be checked via the system variables $SQLErrCode and $SQLErrMsg. The first one contains the error number and the second one the message text corresponding to that error number.

If you have developed an application in Zim and want to maintain a portion of the data in an SQL database, the definitions of those objects must be maintained in the SQL database as well as in Zim. Zim’s SQL definition interface translates Zim definitions into equivalent definitions in the SQL server environment. Refer to Importing and Exporting SQL Definitions topics. An EntitySet in the E-R model maps directly to a table in the relational model. Thus a Zim EntitySet maps to an SQL table with the same field (column) structure. Relationships, however, are explicitly represented in the E-R model and implicitly in the relational model. A Zim relationship with no fields is not represented in a relational database. Zim generates SQL join statements when the relationship is referenced. If a relationship is defined to own fields (a data relationship), then the relationship maps to a table in the SQL server environment with the same field (column) structure. The associated relationship condition is handled in the same manner as for relationships without fields – that is, it contributes to SQL join conditions in the generated SQL statement(s).

If you are developing a Zim application to run against an existing SQL database, you must create an equivalent representation of that data in Zim. Zim’s definition interface translates SQL definitions into equivalent definitions in Zim.

The mapping of SQL tables to Zim EntitySets or data relationships is straight-forward. A Zim developer must first establish a clear understanding of the characteristics of each table in the SQL database. Do this by examining the SQL statements (in particular, the join conditions of “select” statements) in existing application code to successfully identify the relationships relevant to the database.

SQL tables that exhibit the same properties as Zim EntitySets (i.e. contain data about “things” only) can be mapped directly to Zim EntitySets. Tables that appear to contain information representing interactions between other tables are candidates for mapping to either data relationships or EntitySets. In Zim, a binary data relationship “r”, defined between the EntitySets “e1” and “e2”, can be decomposed into an EntitySet called “e3” and two non-data binary relationships “r1” between “e1” and “e3” and “r2” between “e3” and “e2”. SQL statements in existing application code (in particular the join conditions of “select” statements) should be examined to identify potential non-data relationships and the relationship conditions for all relationships.

There are two ways of tracing the activity of the Zim Server Connectivity:

1) At the client side: when accessing a remote database, Zim translates Zim commands syntax into an appropriate syntax for execution on the remote server. In general, Zim commands are executed through a combination of processing at the server (or at several servers) and within the application itself. The syntax being generated for the server can be seeing by using the command SET SQLTRACE. With this option on, all Zim commands display their corresponding syntax that is going to be sent to the server;

2) At the server (remote) side: Zim or SQL commands effectively sent to the remote database can be logged and viewed in the file zimsvlog.zim by means of the configuration option “server request tracing yes”.  

All SQL servers require that some type of connection be established between a client program and the server before any database operations can be attempted. In Zim, a connection between a Zim application and an SQL server is established by the “CONNECT” command and removed by the “DISCONNECT” command.

During the execution of any Zim application, a CONNECT command must have been executed prior to the execution of any Zim command requiring database operations to be performed by the SQL server. Connections can be multiple or single as explained below.

Single Connections

When SQL database servers are accessed one at a time, a connection is always followed by a disconnection. Example:

connect to “JDBCSAM” using (“SalesDB”, “sqlserver”, “admin”, “”, “1422”, “172.16.20.33”)

…

disconnect from “JDBCSAM”

…

connect to “JDBCSAM” using (“Marketing”, “oracle”, “scott”, “tiger”, “1501”, “www.mycompany.com”)

…

disconnect from “JDBCSAM”

…

Notice that each CONNECT statement is followed by a DISCONNECT statement. In the above example, the first connection is different from the second one but it can be the same as well.

Multiple or Concurrent Connections

In this scenario, more than one connection is established and maintained at the same time without an intervening disconnection:

connect to “JDBCSAM” using (“SalesDB”, “sqlserver”, “admin”, “”, “1422”, “172.16.20.33”)

connect to “MYJDBC” using (“Marketing”, “oracle”, “scott”, “tiger”, “1501”, “www.mycompany.com”)

…

disconnect from “MYJDBC”

disconnect from “JDBCSAM”

Now, the second connection is performed not to JDBCSAM anymore but an alias to it called MYJDBC. The reasoning behind this difference is that all ZIM objects need to reference a specific type of object (for Entity Sets, it is the EntType; for Relationships, it is the RelType and so on) and Zim needs to know which object belongs to each type.

The aliases are defined in the “zimalias.zim” configuration file as described in the Servers Alias Configuration File.

Obviously, an alias could have been used in the example for single connections as well.

The use of Zim Server Connectivity in an application has an impact on performance. There are several factors to consider:

1. Client-server configurations introduce additional overhead that is not present when an application is using a local database. The most obvious overhead is that every database request must be translated into a server request, the request must be transferred to the server (which can introduce communications delays), and the returned data must be transferred back to the client. If this overhead was introduced only once per Zim command, it would not be too serious a concern but this is not always the case. For example, the Zim command

change all Customers let Discount = 0.10

results in a single server request being sent and the overhead is very low.

On the other hand, if Customers is an entity set managed by a remote database and Orders is an entity set managed on the client side, the Zim command:

change all Customers Issue Orders let Discount = 0.20

results in each Customer record being retrieved from the server, joined with any Orders at the client, and then an UPDATE request being sent back to the server. Here the server overhead is introduced hundreds or even thousands of times.

2. Zim commands that are executed in integrated server mode result in a single server request with low overhead. Commands that handle a combination of local and server data or data from different servers cannot be executed in integrated server mode.
3. The use of Zim Server can also improve performance and throughput. For example, a multi-user application might be running on a small UNIX machine. If this application was moved to client-server where the server was placed on a larger high performance UNIX system, then overall performance is likely enhanced.
4. The use of the configuration option “server request tracing yes” results in lots of printing and might introduce some delays in the command execution.