IBM SC41-5210-04 manual File data considerations for Query for iSeries

stored in the low-order four bits of each byte. The high-order four bits of the low-order byte contain the sign. The high-order four bits of all other bytes contain all 1s (1111). For example, the binary representation of +123 in zoned decimal format is 1111 0001 1111 0010 1111 0011.

If a file contains numeric fields defined as zoned (which is common on files migrated from a System/36), the iSeries system converts the fields from zoned to packed for the arithmetic operation and then converts them back to zoned when the resulting value is returned to the program.

When the program is Query, this means that running a query involving zoned numeric fields requires additional processing unit time and possibly somewhat longer response times. If you are concerned about this aspect of performance, consider converting your files to use packed fields instead of zoned. However, make this decision carefully, especially if the conversion has a major affect on your operation.

When creating a new file on the iSeries system, define numeric fields as packed, with the length of these fields being an odd number of characters. If the length of the field is declared as an even number, the first four bits of the first byte in the field are not used, but the system still checks these four bits to ensure that no overflow has occurred. If you declared the field as an odd length, this checking does not occur. The system uses additional processing time when numeric fields have even lengths.

File data considerations for Query for iSeries

Decimal data errors may show up with files that have migrated from the System/36. The System/36 applications sometimes place blanks in numeric fields instead of leading zeros (the blanks result in errors when processed on the iSeries system). You should identify and correct decimal data errors to obtain optimal query performance. See “Defining queries for Query for iSeries” on page 234 for more information.

You can identify and correct numeric field errors by using the Programmer’s Tool Kit PRPQ that is available for the iSeries system. The files you analyze or correct with this tool kit must be externally described database files. Ensure that all applications (especially those migrated from the System/36) are corrected so they do not continue to insert data with these kinds of errors.

Defining queries for Query for iSeries

This section provides tips and techniques for defining or changing queries on a single database file. Operations involving multiple files are covered in “Using join operations in Query for iSeries” on page 238.

The information in this section relates to the Define the Query display. See “Selecting definition steps when defining a Query for iSeries query” on page 28 for details.

Query for iSeries performance when designing or changing queries

When you design or change queries, view the results of the query in one of the following ways:

vPress F5 (Report) on the Define the Query display to run the query and show the results of the report as it is designed up to that point.

vPress F13 (Layout) on the Define the Query display to see an example of the layout, including column headings, a sample detail record, any report break text, and any summary functions defined for the query.

Note: Use F13 instead of F5 as much as possible because since F5 requires more processing unit time, I/O, and generally takes longer than F13. In either case, use output type option 1 (Display) while viewing the results.

To improve performance when going from one display to the next, view the lists without their accompanying text as much as possible. Query uses extra processing unit time and possibly some I/O to

234Query for iSeries Use V5R2