Optimizing SQL statements to promote partial key subfields in the WHERE Clause

You can use a variety of SSA optimization techniques to improve access to your IMS data. To use subfield values to define a search range within a primary key range, set the optional ssaOptimization property to true.

A general SSA optimization technique is to use a SQL WHERE statement to qualify access to your IMS data. In the metadata grammar, define a searchable field as a column, then write a SQL statement with a WHERE clause that references the column. A Segment Search Argument (SSA) is generated that optimizes access by applying your WHERE qualification to the search field. For best performance, the WHERE clause must reference columns that supply one or more key values from the primary key or a secondary index.

You can use a similar technique to optimize your IMS data access with a partial key. To create a partial key, map multiple columns to an IMS primary key or secondary index so that each column references only a portion of the field. Then write a WHERE clause that references only a subset of the columns. Optimization occurs when the partial key is used to generate a range that specifies the lowest and highest key values within that subset of columns. When you write the WHERE clause, specify partial keys in the sequence in which they map to the IMS field. To learn more, see Partial keys.

In some cases, partial key values only cover the beginning byte or bytes of the key rather than the entire offset range. The optional property ssaOptimization is available for such cases. When set to true, this optimization creates a search range on the key in the SSA list, and then fills the remaining bytes with the appropriate values. Rather than using low and high key values, this optimization uses the low and high hexadecimal values of the remaining bytes to create low and high bounds for the range. The result is a search for all the values that are in the range that begin with any given subfield value. To use this property, set ssaOptimization to true.

Example

In the following example, a searchable IMS key field named MYSAMPLE is 10 bytes long. MYSAMPLE is mapped to three columns, a WHERE clause is included in the SQL query, and the ssaOptimization property is set to true. IMS returns only the segments that match the WHERE qualification and that are within the generated SSA range.

Step 1. Map three columns to MYSAMPLE in the following sequence:

COLUMN1 = bytes 1-3
COLUMN2 = bytes 4-5
COLUMN3 = bytes 6-10

Step 2. Issue the following SQL query:

WHERE COLUMN1 = 'abc' and COLUMN2 = '00'

The following SSA is generated:

MYSAMPLE >= abc00(low values) & MYSAMPLE =< abc00(high values).

IMS returns segments where bytes 1–5 are equal to 'abc00' and where the bytes in COLUMN3 are within the range defined by the lowest and highest hexadecimal values for bytes 6–10.

The SSA is generated successfully and optimization occurs because COLUMNS 1 and 2 are specified in proper sequence. Optimization cannot occur if the WHERE clause references only COLUMN2 or COLUMN3 because the query processor cannot generate an SSA and the query processor or connector must retrieve all of the mapped segments and perform all filtering logic.