Category: Zim:X Interacting With SQL Databases

The export facility is used to translate Zim definitions for entity sets and/or data relationships and create them on the designated SQL server. The definitions of the resulting tables (with associated indexes) correspond to each exported Zim EntitySet or data relationship definition. The Zim definitions to be exported are selected from the EntitySets and Relationships and their associated Fields that are in your database. The selected objects to be exported are grouped according to their Remote Owner Name field in EntitySets and Relationships and a complete export cycle is accomplished for each different Remote Owner Name.

In a Zim Client-Server application, all EntitySets and data relationships should have at least one unique index defined on a field or virtual field. Limitations of SQL require that if the unique index is on a virtual field, then the field expression must be

$concat(field1,field2,…)

where “field1”, “field2” are the names of real fields only. They cannot be expressions or other virtual field names. 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. See Exporting Zim Table Definitions for more information on this subject.

To export Zim table definitions to the server, choose the Tools/Export/Objects/To SQL option from the main menu of DC. The SQL Export Definiton window appears. Choose the server to which you wish to export the definitions by selecting it from the “Export selected items to” list box. The names of the EntitySets and data relationships in your Zim database are listed. (If more than 30 table definitions exist, you can scroll forwards and backwards through the table names.) To the left of each table name is a check box. Clear the check box if you do not wish to export the definition to the server. Selecting the “Deselect All” button clears all check boxes and selecting the “Select All” button selects all boxes. Initially, all entries have their check boxes selected.

Also appearing on this window is the Replace Existing Table check box. If this box is selected, Zim attempts to DROP each table on the server before it creates it. Hence, selecting this box unconditionally replaces each existing table definition. By clearing this check box, the server generates an error if the table already exists and no new table is created. Therefore to guard against the accidental replacement of a table definition, clear this box. If the box is selected and the table does not exist, the server generates a benign error.

By clearing this check box, the server generates an error if the table already exists and no new table is created. Therefore to guard against the accidental replacement of a table definition, clear this box. If the box is selected and the table does not exist, the server generates a benign error.

The “CREATE TABLE” syntax for a specific SQL server can optionally include additional syntax which relates to the physical storage characteristics of the created table. This syntax varies widely from server to server. If the specification of some or all of the optional physical table characteristic parameters is desired, 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” tablespace.

Another way to change characteristics is to check the “Modify SQL Definitions” check box. This options brings the resulting SQL definitions through the default text  editor for editing. Edit the definitions you want to apply and save the text. The export then proceeds.

To export those EntitySets and/or data relationships selected, click the “Export” button. The table definitions are translated to the appropriate “CREATE TABLE” syntax for the selected SAM and the output is written to a file. Next, the server parameter dialog box appears. Values for these items are required so that the utility program “SQLEXEC” (which the DC invokes) can access the appropriate database in the SQL server in which to create the tables. SQLEXEC in turn invokes the “Server Interface Module” of the selected SAM. These items are identical to parameters 2 through 7 of the “CONNECT” command. Refer to the Connecting to SQL Servers topic for information on how to supply the correct values for these items. Although these values are used to connect to the SQL database, Zim itself does not need to be connected to the database.

With the appropriate values supplied for these items, the export process proceeds to execute a general purpose SQL statement executor program “SQLEXEC”. SQLEXEC traces the SQL statements that it is executing as well as any error conditions that are detected. It does not stop executing on an error condition. In general, the only error condition that is acceptable is an error from the “DROP TABLE” statement when the table does not already exist. All other error conditions should be investigated. A common error is attempting to create an SQL table with a name beginning with the underscore (“_”) character. This is legal in Zim, but generally not legal in SQL.

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.

When developing client-server database applications, it is important to consider the location of the data. Any data under the control of the database server contributes to both the load on the network as well as load on the server machine. Because of this, it is important to analyze the data associated with a given application and determine the best location (or locations).

Static data, or data that does not change, can be duplicated and distributed over the client workstations to reside under Zim control. This type of data is typically part of the application. A table of help screen text messages is an example of static data that can (or should) be located on the client workstation.

Data that changes on a monthly, weekly, or even daily basis can be located on both the client and server workstations. An application can down load this data (or part of it) to a local copy at an appropriate time. The single server copy would be updated as required and client workstations would pick up any updates automatically.

Finally, there is dynamic shared data that resides on the server and is shared by the client workstations. This type of data resides under the SQL database’s control and be accessed and manipulated from within Zim.

In Client-Server applications, you do not have to do anything special to access or manipulate data stored in the SQL database. The data definitions of objects under Zim’s control and the data definitions of objects not under Zim’s control are exactly the same. To an application, Zim manipulates both in exactly the same way.

The development of a Zim Client-Server application can be performed in isolation from the SQL database; it can in fact take place on an entirely different computing platform. Zim does not have to be connected to the server through the development phase. This “off-line” development capability eliminates the heavy load that can be placed on SQL servers by development activities.

In order to access SQL database tables, Zim must have a definition of the table. The table must be defined in two places: in Zim and in the SQL database. EntitySets and relationships are flagged as being under the control of Zim or under the control of the SQL database. The definitions of those EntitySets and relationships under control of the SQL database are either exported to the SQL database or imported from the SQL database. You must ensure that the definitions in Zim and the SQL database are compatible. Zim’s SQL definition interface provides the means to easily import and export definitions.

Figure 1

In Figure 1, the table “Customers” is defined both in Zim and in the SQL database. If “Customers” is defined as a Zim EntitySet or if Zim is running in ZIM mode, then all references to customers are directed to the Zim EntitySet. If, however, “Customers” is defined to be an SQL table and Zim is running in SQL mode (the default), then all references to customers result in the generation of the appropriate SQL code which is sent to the SQL database.

Zim requires that each EntitySet and data relationship to be managed by the SQL database have at least one unique index defined for it. The unique index is a primary key for the SQL table to which the EntitySet or data relationship corresponds. A primary key is necessary to directly access any record (row) of any SQL table. It does this by generating select statements which specify values for the primary key column(s) of the table. For more information, see Primary Keys.

Zim supports two types of transactions: implicit and explicit. An implicit transaction is associated with each individual Zim command that accesses the database in some manner (either read/only or read/write). When the command ends, all locks that were acquired are released and any database updates are committed to the database. If the command fails (e.g. a deadlock condition), then Zim automatically removes any partial updates and releases all locks.

Explicit transactions are provided in order to support database updates associated with multiple database access commands that must either be all committed or all removed as a single “logical unit of work” (or “logical unit of recovery”). An explicit transaction is initiated by the command “transaction” and ended either by the command “endtransaction” (that commits any database updates) or by the command “quittransaction” (that cancels any database updates). Both commands release all held locks. Should any database access command inside an explicit transaction fail (e.g. by a deadlock condition) then all partial updates to that point are removed and all held locks are released.

Zim supports this “transaction” functionality against an SQL server database.

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.

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).

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 “SET SQLCOMPILE” Command

Zim is designed to take advantage of many characteristics of a given SQL server. Many SQL servers provide compile time facilities to increase performance at runtime. A compile time option enables you to control when the database server does certain compilation functions such as statement parsing. If SQLCOMPILE is OFF, then these functions are performed at Zim’s runtime. If SQLCOMPILE is ON, these function are performed as Zim compiles the application, if possible. This option is set using the command:

SET SQLCOMPILE [ ON | OFF ]

The default setting for SQLCOMPILE is OFF.

In order to take advantage of these facilities, you must be connected to the SQL server at compile time. If this option is turned off, all work is done at run time and you do not have to be connected at compile time.

Different applications being compiled against the same SQL server database can have programs with the same name (i.e. “MAIN”). To avoid conflicts that can arise when compiling several applications against the same SQL server database, each application should be flagged with a unique identifier. The “config.zim” entry is

SQL DATABASE NAME xxx

where “xxx” is a three-character ID. Zim makes use of this ID when compiling. It should be noted that SQLCOMPILE can be turned on or off for a single statement if desired.

A dynamic configuration setting called "block factor" can be used to tune performance. This factor is the number of records retrieved by Zim each time it fetches records from the database. The larger the number, the fewer times Zim has to query the database, and the more time it takes to retrieve a block. So, the number must be adjusted to an appropriate value and not one that is arbitrarily large.

Continue reading “Block Fetching”