Indexed Fields in WHERE Clauses

In Zim, developers use expressions in the form


... WHERE <indexed-field> >= <value> ...


... WHERE <indexed-field> <= <value> ...

WHERE clauses retrieve data in either (a) ascending or (b) descending sequence of the data values in <indexed-field>. This technique is used instead of an explicit sort because it takes advantage of a physical characteristic exhibited by Zim. However, there is no guarantee that any given SQL server behaves in the same manner. The use of indexed fields in expressions of the above form whose sole purpose is to deliver data in a particular sequence should be avoided. Instead, an explicit sort expression should be included in the set specification.

"Unrelated" and "Complete"

The performance of Zim set specifications qualified by "unrelated" or "complete" against SQL servers can be seriously affected, if that SQL server does not provide explicit support for the outer join operation. In such cases, use the following guidelines:

"DATE" Data

For date type fields, Zim accepts NULL values, true date values (like 20021031, etc.) and non-date values (like -1, 0, etc.). However, SQL servers do not accept non-date values. "DATE" data types should be restricted to valid dates and NULL values to ensure compatibility. If your application uses such non-date values and it will be difficult to change it, you can use the ”mapdate” file to map date values from ZIM to SQL and vice-versa.

SET manipulation

Sets built using Zim tables are different from sets built using Zim tables connected to a SQL server. In the following example:

set executemode zimmode

find States -> StateSet

set executemode sqlmode

list StateSet

The LIST statement will raise an error because it will try to list the records existing locally but referenced remotely, which is inconsistent. The opposite is also an error.

DB2 SAM Comparisons with Constants

The following construction:

FIND MyEnt WHERE MyField = 111 OR 1 = 2

Although valid in ZIM and in other SQL servers, this construction should be avoided because it brings an error from DB2. The reason is that DB2 requires object types to be cast all the time. The objects ”1” and ”2” don’t have types neither implicitly defined nor inferred by DB2 (thus raising the error), whereas the object ”111” has the type defined by MyField.