August 2009 Newsletter

This Month's Coding Tip from our Development Team

Creating Optimal Schemas for Accessing VSAM and QSAM Data

Do you create 4GL schemas to map VSAM or QSAM data, either as an UltraQuest administrator or as a NOMAD user? If so, this second article in a series on efficient use of UltraQuest and NOMAD has several tips that may help you significantly improve the performance of accessing data using the schemas you create.

Tip 1: always specify 4GL keys

If the data set being mapped by a 4GL schema contains a key, the MASTER describing the data should always include a KEYED clause. This ensures that efficient access using the key can be used by UltraQuest or NOMAD. This is especially important for a VSAM data set with a key (i.e., one using the Key Sequenced Data Set organization, KSDS).

If you have installed NOMAD Version 7.53, then the COPYBOOK utility provides a user interface for selecting key columns. Otherwise, you will need to edit the schema and add the KEYED clause by hand.

Tip 2: if VSAM key is redefined, choose 4GL keys wisely

The same area in a VSAM data set may be mapped in a 4GL schema two or more times, using the REDEFINE schema statement. The first mapping is referred to as the original definition, and re-mappings below a REDEFINE statement are called redefinitions. As is true in a COBOL copybook, the definitions may be swapped, that is, a redefinition may be specified first as the original definition and the ITEM statements in the original definition may be placed below a REDEFINE statement. Only items (fields) in the original definition may be included in the KEYED clause, which specifies 4GL keys.

If the key in a VSAM KSDS is mapped multiple times, UltraQuest and NOMAD will perform efficient access only when keys in the KEYED clause are used in data filtering (SELECTs). Thus, it is important to specify the columns that are more likely to be used in data filters in the original definition and in the KEYED clause.

Tip 3: create an RPROC for keys with fixed values

Some VSAM data sets contain a key where a portion of it, often the first byte, is a constant value. In this situation, create an RPROC (a permanent data filter) using the value of this key, e.g.:

rproc-name KEY_FIRST_BYTE = 'A'

This technique ensures that data filters on the column that starts at the next key position will be as efficient as possible.

Tip 4: use Repeating Segments instead of Repeating Groups (REPEATGROUPs)

VSAM and QSAM data often contain repeating groups of fields, mapped by COBOL using an OCCURS structure. Historically, these have been mapped in 4GL schemas using the REPEATGROUP statement. As of NOMAD Version 7.52, they may instead be mapped using the repeating segment structure.

Accessing a table with repeating segments is more efficient than one with repeating groups if the columns in the segment/group are not referenced. This is because UltraQuest and NOMAD will reformat repeating groups into 4GL arrays even if their columns are not referenced.

If you are using the COPYBOOK utility to create a schema, specify an Occurs Type of S to map all COBOL OCCURS structures as repeating segments. Fields in the OCCURS structures will be mapped as scalar 4GL columns in repeating segments. If array processing is desired on these columns, a 4GL DEFINE using the INSTANCE function may be created in the parent table to convert these columns into arrays.

Tip 5: map PICTURE 9 COBOL fields not used in calculations as An

VSAM and QSAM data often contain fields of numeric characters (e.g., using the COBOL PICTURE 999 format) that are not used in calculations, e.g., an account number. The COPYBOOK utility maps such fields as numeric, which cause UltraQuest and NOMAD to convert these fields to numbers when they are referenced. If they are instead mapped using an alphanumeric (An) display format, this conversion is not done.

UltraQuest Webinar Featuring Version 5.0 with Dashboards

Sign up now for an UltraQuest Webinar and see how UltraQuest can help you leverage your investment in NOMAD by allowing your users to now receive reports in a variety of formats including Dashboards.

The scheduled date for the next UltraQuest Webinar is:

• September 9th

Click here to register.

If you want to see UltraQuest in action and this date isn't convenient for you, please This e-mail address is being protected from spambots. You need JavaScript enabled to view it with your availability.

Select Business Solutions

35 Nutmeg Drive

Trumbull, CT 06611

Phone: 1-888-472-7347

Email: This e-mail address is being protected from spambots. You need JavaScript enabled to view it

 

If you do not wish to receive future e-mails from

Select Business Solutions, please email: This e-mail address is being protected from spambots. You need JavaScript enabled to view it