Categoria: Getting Started

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

Zim supports the specification of decimal places for fields whose internal representation is in the form of integers (data types “int”, “longint” and “vastint”). This is done by maintaining the logical position of the decimal point external to the physical storage of the data. SQL servers do not do this – integer data types contain integer data only. If the data for a field has either explicit or implicit decimal places, then it must be defined with the appropriate SQL data type (DECIMAL, NUMERIC, NUMBER, FLOAT, DOUBLE PRECISION, etc.). To formalize the translation of field definitions between Zim and SQL, Zim insists that fields of data type “int”, “longint” and “vastint”, which have non-zero decimal places, also have an appropriate value (greater than zero) in the “Length” field of the Data Dictionary entity set “Fields”. The value ranges for the “Length” field are

The values specify the maximum number of significant digits that the field is required to support. This number is the sum of the number of digits to the left of and to the right of the (implied) decimal point. For example, if it is known that data values for a particular field range from 0.01 to 9.99, then in standard Zim, the definition of the field could be

Type = ‘int’ Length = 0 Decimals = 2

For SQL tables, the definition must be expressed as

Type = ‘int’ Length = 3 Decimals = 2

In SQL servers, these two numbers are generally referred to as the “Precision” (Length) and “Scale” (Decimals) for columns of data type NUMERIC, DECIMAL, and so on.

The specification of a non-zero value for the “Length” in Zim is not a concern.  For these data types, Zim ignores the “Length” value. On the other hand, for SQL tables, Zim checks to ensure that the “Length” value lies within the ranges stated above. The Zim Definition Interface uses the “Length” values to translate Zim number field definitions to the appropriate SQL definitions and vice versa. Even though an “int” value has limited support for 5 digits (i.e. up to 32767), it cannot support up to 99999 and accordingly, its real maximum precision is 4. Consider an SQL data type of DECIMAL(5,2). Such a definition can support values from -999.99 to 999.99. A Zim data type of “int” cannot support this complete range. The Definition Interface translates such a definition into a Zim “longint” data type with a “Length” of 5 and “Decimals” of 2.

To a Zim application developer, the impact of this requirement is relatively small. For all fields of type “int”, “longint” and “vastint” which have a “Decimals” value greater than zero, a “Length” value from the ranges listed above must also be specified. The “Length” value must also be sufficiently large to support the maximum expected number of significant digits for data values for the field.

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

The topics in this section provide information on reporting through the Zim programming environment.

Refer to the following sections for more information:

Available Format Options

How to Use the Report Generator

Format Options

Output Masks

Any meaningful combination of the following options can be applied to any value displayed in a report:

Output masks are employed to format the output of fields, form fields, and data values in output operations.

The MASK format option in Zim Reports specifies an output mask pattern for a report item.

The $mask function specifies an output masking pattern to be applied to fields, form fields, and data values and returns a character string with the result of the masking operation.

The Field Mask attribute defines the expected output format of a field’s data value when it is printed or listed in Zim Reports, or by the list or output commands.

Mask patterns used with any of the three options described above are constructed in the same manner using the same set of available characters.

However, the set of characters available varies according to the data type of the item being masked. For more information on mask patterns and their relation to data types, see Masking.

Selected Examples of Mask Patterns for Numbers

In the following examples, leading or trailing spaces are indicated by / in the Output column.

Output of Sign Placeholders

Behaviour of Fixed Text Characters

Behaviour of Float Characters

Mask Patterns and the Language Customizer

Certain characters inserted into the output value by a mask are defined internally in the software.

The Language Customizer (ZIMLANG) administrative utility is used to redefine the internal characters and strings used by the software in a variety of circumstances, such as output masks.

For example, the currency placeholder $,  is by default the dollar sign $. However, it can be redefined to output other symbols, such as the Euro sign €, the pound sign  Â£, etc.

To achieve this, the Language Customizer can redefine the currency symbol output by the currency placeholder.

When using the Language Customizer to edit the software’s internal characters and strings, note the following:

• In application programs, output masks are always specified using the standard mask pattern characters. This rule applies to mask patterns specified in reports, in field and form field definitions, and in the $mask function.
• In user interface displays and output, the characters output by a mask pattern are determined by the software’s internal values.

For example, in Zim application programs, the dollar sign $ is universally used as the currency placeholder.

However, the currency symbol output by the placeholder depends on the current language customization (if any).

The Report Generator takes a report specification and creates a report for viewing or printing.

The elements that must or can be provided to the Report Generator in the report specification are

• set of records

• page structure

• report headings and footings

• page headings and footings

• break headings and footings

• item position and format

How To Construct Report Specifications

A report specification is a series of commands that describe the set of data to be used in the report, the values to be extracted from the data set, and the formatting to be applied to each value and to the report in general.

The general form of a report specification is

REPORT FROM…

report commands

ENDREPORT

Reports can be line-oriented or column-oriented in their formatting.

How To Use Report Commands

Between REPORT FROM, that initiates the report and specifies the set of database records on which the report is to be based, and ENDREPORT, that marks the end of the report specification, any meaningful combination of the following commands can be used.

REPORT HEADING, REPORT FOOTING

Identifies and formats the data that is to appear at the very start and end of the report.

Always line-oriented by default, even in column-oriented reports. Can be column-oriented if desired.

PAGE HEADING, PAGE FOOTING

Identifies and formats the data that is to appear at the top and bottom of each page of the report.

Always line-oriented.

PAGE LEFT, PAGE RIGHT

In column-oriented reports, specifies “headings” that are to appear at the left-hand and right-hand sides of each page of the report.

DETAIL LINE

Extracts and formats values from the set of records specified in the REPORT FROM command, and manages the headings for each set of values.

Obeys the orientation specified in the REPORT FROM command.

BREAK

Specifies the conditions for dividing detail lines into groups and subgroups, and specifies the headings and footings that are to be output for each group.

The break headings and footings obey the orientation specified in the REPORT FROM command. In column-oriented reports, break headings and footings can be line-oriented if desired. Break headings and footings can differ in orientation.

COLUMN BREAK

In column-oriented reports, specifies the headings and footings that are to be output each time DETAIL LINE values carry over to a new tier of the report.

How To Specify the Report Set

The set specification is specified in the REPORT FROM command.

Only those records that fit the set specification are reported. One DETAIL LINE is produced for each record. The records are reported from top to bottom in sequential order. Typically, therefore, the set specification includes a SORT clause to place the records into a logical order.

Sorting is essential if breaks are to be included in the report. (As each record is processed, the break indicator is checked to determine whether a break has occurred.)

How To Specify Report Headings and Footings

Report headings are specified in the REPORT HEADING command; report footings, in the REPORT FOOTING command.

Report headings are processed before any records identified by the set specification are read.

The items specified in the report heading are output only once, at the start of the report.

Report footings are processed after all records identified by the set specification are read.

The items specified in the report footing are output only once, at the end of the report.

How To Specify Page Headings and Footings

Page headings and footings are specified in the PAGE HEADING, PAGE FOOTING, PAGE LEFT, and PAGE RIGHT commands.

Page headings and footings are processed when a page boundary is reached.

The items specified in the page headings/footings are output on every page of the report. The values can vary, however, depending on the source of the items (e.g., the current member from the set specification) and their format (e.g., NOPRINT, SUPPRESS).

How To Specify Breaks and Break Headings and Footings

Breaks, with their associated headings and footings are specified in the BREAK (Reports) command.

Breaks are processed when the break indicator expression changes in value. Many break levels can be specified in one report. Break headings and footings are processed when a break occurs at the corresponding level.

The items specified in the break heading are output when the first record of the set specification is processed, and when the first record of each new break group is processed. The values can vary, however, depending on the source of the items (e.g., the current member from the set specification) and their format (e.g., NOPRINT, SUPPRESS).

The items specified in the break footing are output when the last record of each break group is processed, and when the last record of the set specification is processed. The values can vary, however, depending on the source of the items (e.g., the current member from the set specification) and their format (e.g., NOPRINT, SUPPRESS).

How To Specify Item Position and Format

The items output by the various report commands can be explicitly specified, drawn from the records identified in the set specification, or produced by the evaluation of an expression that involves groups of such values, plus operators and functions, as desired.

Each item is individually positioned and formatted using report format options.

Other formatting of items can be accomplished using character functions such as $trim and $concat.

How To Specify the Report Page Structure

The general structure of a report page is specified in the REPORT FROM command.

Structural details that can be controlled include the overall width and depth of the page, the four margins (top, bottom, left, and right), the spacing between subsequent items on the same line, and the orientation of the entire report (line-oriented or column-oriented).

Report format options describe where and how each report item is to be displayed and printed. Format options define the appearance of the report, its contents, and the position and appearance of its data, headings, and footings. Each data item in the report can be followed by any meaningful group of format options (See Conventions). Each group of format options begins and ends with a colon. Within the colons, each format options is separated from the next by at least one space.

Some format options are designed for text reports while other for graphical reports, sometimes both.

The following is the Report Format Options description for graphical reports, where “n” can be any positive integer:

The following is the Report Format Options description for text reports, where “n” can be any positive integer:

Format options can be used in any report command except REPORT FROM and ENDREPORT.

Using Report Format Options

General points to remember when specifying format options for a report item are the following:

• The first report item in a command always begins on a new line (i.e.: NEWLINE 1 is automatic) in a line-oriented report. Also, this report item begins in column 1, unless otherwise specified.
• All line and column positioning is processed before the report item itself is formatted.

The following table contains a list of the Zim constants, types, locations, and values.