Indexed Fields in WHERE Clauses

In Zim, developers use expressions in the form

WHERE clauses retrieve data in either (a) ascending or (b) descending sequence of the data values in . 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:

• Inspect the application code to determine if any set specifications including the “unrelated” or “complete” qualifiers exist. Determine if these are functionally altered if the qualifiers were removed.
• Performance can be improved by splitting a “complete” set specification into the union of the same set specification with the “complete” removed (the normal “related” case) and the same set specification using “unrelated”.

“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. and DB2 (thus raising the error), .