WebFOCUS Reporting With IMS

You can issue any SQL or TABLE request through the Adapter for IMS. However, those requests whose record selection criteria can be applied at the IMS level (with qualified SSAs on .KEY, .IMS, or .HKY fields) result in many fewer DL/I calls and I/O operations than unoptimized requests. They can achieve performance improvements measured in orders of magnitude.

Note: Since IMS does not support the concept of missing data, all fields are considered not missing.

These topics include sample requests with corresponding trace results. You enable traces through Diagnostics on the home pane. In the trace, you get a dump of the SSA buffer setup for each request. For example, the following request contains a selection test on the SEQFIELD search field in the PATINFO segment of the PATDB01 data source:

The SSA always includes the segment name. What distinguishes an optimized request from an unoptimized request is whether the SSA is qualified; a qualified SSA includes the selection test from the request, as in the previous example. An unqualified SSA contains the segment name with no selection criteria.

Whenever possible, the adapter translates screening conditions from the SQL request into qualified SSAs. However, not all server screening conditions can be translated into SSAs. Such conditions are still applied, but by the server, rather than by IMS. In those cases, the adapter retrieves the data sequentially and passes it to the server for screening.

IMS Access Method Restrictions

Some IMS access methods are limited in their ability to apply certain screening conditions. These restrictions can affect request optimization. The following considerations apply to specific access methods:

Since HDAM data sources are stored randomly and accessed directly, IMS cannot retrieve their records in root key sequence. Therefore, with an HDAM data source, the adapter can only optimize equality conditions on the root key:
```
KEY=value
```
You can build a secondary index on the root key and use it to optimize sequential processing.
Note: The NE condition is not optimized.
With HIDAM data sources, the adapter optimizes LIKE tests with a mask by translating the LIKE into a range condition. For an example, see Partial Key and Multi-Segment Requests in IMS.
To take advantage of efficiencies available by limiting processing to one area of a Fast Path DEDB data source, the PSB must limit access to that area.

IMS Rules for Constructing SSAs From WHERE Tests

In order for the adapter to translate an SQL WHERE test into a qualified SSA:

The field tested must be an IMS key field, search field, or a secondary index.
The test relation must be one of the relations described in these topics.
The tested field cannot be a floating point numeric or zoned field.
Note: In order for the adapter to optimize selection tests on zoned key fields, you must describe the USAGE format of these fields as alphanumeric or packed in the Master File.
The ACTUAL and USAGE formats of the tested field must be basically the same, so no format conversion is required. For example, extending with leading zeros or padding with trailing blanks is not a format conversion, but shifting the implied decimal point in a packed field is a format conversion.
In an HDAM data source, the tests must use the equality (=) relation on the root key, with no value masking.

The following test relations can be translated to qualified SSAs:

A comparison of a field to a list of values:
```
fld relation value1 OR fld relation value2 ... OR fld relation valuen
```
For a discussion of SSA generation when comparing a field to a list of values, see Complex Screening Conditions in IMS.
= <> < > <= >=
IN
NOT IN
LIKE
NOT LIKE
BETWEEN
The BETWEEN relation is treated as a pair of relations. For example:
```
WHERE field GE . . . AND WHERE field LE . . .
```
A search on a partial key (the high-order portion of a key) is also translated to a range condition and optimized.
NOT BETWEEN
Using WebFOCUS, INCLUDES and EXCLUDES on target segments in secondary index databases. For information, see Qualifying Parent Segments Using INCLUDES and EXCLUDES.

Qualifying Parent Segments Using INCLUDES and EXCLUDES

How to:

Qualify Parent Segments Using INCLUDES and EXCLUDES

Reference:

Usage Notes for INCLUDES and EXCLUDES

Using WebFOCUS, you can test whether instances of a given field in a child segment include or exclude all literals in a list using the INCLUDES and EXCLUDES operators. You can also have additional screening conditions for any descendant of the INCLUDES/EXCLUDES parent segment.

Top of page

Syntax: How to Qualify Parent Segments Using INCLUDES and EXCLUDES

You can connect the literals you are testing for with ANDs and ORs, however, you cannot create compound INCLUDES and EXCLUDES tests with logical AND and OR operators. The Adapter for IMS will always connect all literals used in an INCLUDES/EXCLUDES phrase with the AND condition regardless of the logical predicate used in the request.

A request that contains either of the following phrases returns stock records with cycle number instances for both 00003333 and 00001111:

WHERE CYCLNUMB INCLUDES 00003333 OR 00001111

WHERE CYCLNUMB INCLUDES 00003333 AND 00001111

For a record to be selected, its cycle number field must have values of both 00003333 and 00001111. If either one is missing, the record will not be selected for the report.

With the following selection criteria, every record that is not associated with the provided secondary index value is selected for the report.

WHERE CYCLNUMB EXCLUDES 00003333

An EXCLUDE request potentially results in erroneous report output if there is more than one secondary index value associated with the particular target record.

Top of page

Reference: Usage Notes for INCLUDES and EXCLUDES

INCLUDES and EXCLUDES are applicable only to the target segments in secondary index databases.
INCLUDES and EXCLUDES cannot be used when a child segment has type U.
Literals containing embedded blanks must be enclosed in single quotation marks.
To use more than one INCLUDES or EXCLUDES phrase in a request, begin each phrase on a separate line.

Top of page

Example: Using INCLUDES to Qualify Parent Segments

The secondary index database was created based on the IMS IVP sample database Parts.

The Stock Status record was indexed by the CYCLNUMB field in the Cycle Count record, which resulted in the following structural view of the new database:

 STRUCTURE OF IMS FILE DIX1DBD ON 12/21/04 AT 13.22.50
 
STOKSTAT
01 S0
**************
*STOCKEY     **
*STOKFIL1    **
*STOKNUMS    **
*STOKFIL2    **
*            **
***************
 **************
       I
       +-----------------+-----------------+
       I                 I                 I
       I PARTROOT        I CYCCOUNT        I BACKORDR
    02 I U            04 I S0           05 I S0
**************     **************     **************
*PARTKEY     *     *CYCLKEY     **    *BACKKEY     **
*PARTFIL1    *     *CYCLNUMB    **    *FILL1       **
*FILL1       *     *CYCLFIL1    **    *BACKREFR    **
*PARTNAME    *     *CYCLVALU    **    *            **
*            *     *            **    *            **
**************     ***************    ***************
       I            **************     **************
       I
       I
       I
       I STANINFO
    03 I S0
**************
*STANKEY     **
*STANFIL1    **
*STANTYPE    **
*STANFIL2    **
*            **
***************
 **************

The following request selects part keys and stock keys for all details that have requested cycle numbers.

TABLE FILE DIX1DBD
PRINT CYCLNUMB BY STOCKEY BY PARTKEY BY CYCLKEY
WHERE CYCLNUMB INCLUDES 00003333 AND 00001111
END

The output is:

STOCKEY     PARTKEY         CYCLKEY    CYCLNUMB
-------     -------         -------    --------
0025906026  02N51P3003F000  20         00003600
                            21         00001111
                            22         00002222
                            23         00003333
0028009126  02RC07GF273J    20         00000190
                            21         00001111
                            23         00003333

The report output contains extra CYCCOUNT records that are children of the selected STOCKEY record. To eliminate the extra child records, the request needs an extra screening condition to select only those CYLCNUMB fields that match one of the values in the INCLUDE condition:

IF CYCLNUMB EQ 00003333 OR 00001111

With this extra condition, the output is:

STOCKEY     PARTKEY         CYCLKEY    CYCLNUMB
-------     -------         -------    --------
0025906026  02N51P3003F000  21         00001111
                            23         00003333
0028009126  02RC07GF273J    21         00001111
                            23         00003333

Complex Screening Conditions in IMS

How to:

Construct Multiple SSAs in IMS

Reference:

The general form for a complex screening condition is:

field1 relation1 value1 OR field2 relation2 value2  OR ...

If the fields are search or sequence fields, the adapter can optimize the screening test subject to certain conditions. The adapter either constructs a single SSA or multiple SSAs, depending on the characteristics of the segment and the type of relation used.

Note: Specifying the AND operator between logical conditions in a selection test is equivalent to using multiple WHERE statements without the AND. The adapter constructs a qualified SSA that incorporates the AND operation in either case.

The following topics describe:

The SSA buffer, and how the adapter processes SSAs that are too long to fit into the buffer.
Conditions under which the adapter constructs a single SSA.
Conditions under which the adapter constructs multiple SSAs.

Top of page

Reference: The IMS SSA Buffer

Once the adapter constructs an SSA, it must place the SSA in the SSA buffer in order to submit it in a DL/I call. If the SSA is too long to fit into the buffer, the adapter makes the following choices between the individual screening conditions within the SSA:

Tests on key fields have priority over all other tests.
The remaining tests are given priority based on their order in the query statement. Since earlier tests are retained and later tests are eliminated from the SSA, you can control the SSA generation process by carefully constructing your screening conditions.

When an SSA does not fit into the SSA buffer, no error is generated. The selection tests that do not get passed to IMS in the SSA are still applied, but they are not optimized. That is, the server applies them, not IMS.

Note: The number of conditions that fit into the SSA buffer is not fixed. It varies depending on the lengths of the values in the comparisons.

Top of page

Example: Constructing a Single SSA in IMS

The adapter constructs a single SSA that incorporates the entire complex logical condition if either:

The segment has no key.
There is a key, but it is not qualified.
There is a qualified key, and the relation in the test on the key is the equality (=) relation or a single range.

If the entire SSA fits into the SSA buffer, it is submitted in the DL/I call. If the SSA is too long to fit into the SSA buffer, the adapter makes the choices described in The IMS SSA Buffer.

The following example illustrates SSA generation when there is no equality test on the key field, but there are tests on a search field:

SQL
SELECT LAST_NAME, FIRST_NAME, SEQFIELD, SALARY, SSN
FROM PATDB01
WHERE SEQFIELD IN ('100000','100005') AND 
      LAST_NAME IN ('BORRERO','JONES');
END

The trace produced is illustrated below. Note that because the sequentially ordered secondary index database is used in the search, the SQL IN condition for SEQFIELD is translated to a range test in the SSA. If the HDAM database itself had been used, the IN condition would have been translated to an equality test in the SSA:

14.25.42 DU  set up SSA-Q:
14.25.42 DU D7C1E3C9 D5C6D640 5C604DE2 C5D8C6C9      *PATINFO *-(SEQFI*
14.25.42 DU C5D3C47E 40F1F0F0 F0F0F05C C9E7D5C1      *ELD= 100000*IXNA*
14.25.42 DU D4C54040 6E7EC2D6 D9D9C5D9 D6404040      *ME  >=BORRERO   *
14.25.42 DU 40400000 00000000 00000000 00005CC9      *  ............*I*
14.25.42 DU E7D5C1D4 C540404C 7EC2D6D9 D9C5D9D6      *XNAME  <=BORRERO*
14.25.42 DU 40404040 40FFFFFF FFFFFFFF FFFFFFFF      *     ...........*
14.25.42 DU FF4EE2C5 D8C6C9C5 D3C47E40 F1F0F0F0      *.+SEQFIELD= 1000*
14.25.42 DU F0F05CC9 E7D5C1D4 C540406E 7ED1D6D5      *00*IXNAME  >=JON*
14.25.42 DU C5E24040 40404040 40000000 00000000      *ES       .......*
14.25.42 DU 00000000 005CC9E7 D5C1D4C5 40404C7E      *.....*IXNAME  <=*
14.25.42 DU D1D6D5C5 E2404040 40404040 FFFFFFFF      *JONES       ....*
14.25.42 DU FFFFFFFF FFFFFFFF 4EE2C5D8 C6C9C5D3      *........+SEQFIEL*
14.25.42 DU C47E40F1 F0F0F0F0 F55CC9E7 D5C1D4C5      *D= 100005*IXNAME*
14.25.42 DU 40406E7E C2D6D9D9 C5D9D640 40404040      *  >=BORRERO     *
14.25.42 DU 00000000 00000000 00000000 5CC9E7D5      *............*IXN*
14.25.42 DU C1D4C540 404C7EC2 D6D9D9C5 D9D64040      *AME  <=BORRERO  *
14.25.42 DU 404040FF FFFFFFFF FFFFFFFF FFFFFF4E      *   ............+*
14.25.42 DU E2C5D8C6 C9C5D3C4 7E40F1F0 F0F0F0F5      *SEQFIELD= 100005*
14.25.42 DU 5CC9E7D5 C1D4C540 406E7ED1 D6D5C5E2      **IXNAME  >=JONES*
14.25.42 DU 40404040 40404000 00000000 00000000      *       .........*
14.25.42 DU 0000005C C9E7D5C1 D4C54040 4C7ED1D6      *...*IXNAME  <=JO*
14.25.42 DU D5C5E240 40404040 4040FFFF FFFFFFFF      *NES       ......*
14.25.42 DU FFFFFFFF FFFF5D40                        *......)         *

If this SSA did not fit into the SSA buffer, the adapter would retain as much of it as possible in a qualified call, after which the server would apply the remaining tests to the returned segments.

The output is:

LAST_NAME  FIRST_NAME  SEQFIELD  SALARY   SSN
 
JONES      CORNELIUS   100005    1005.00  197548684

Top of page

Syntax: How to Construct Multiple SSAs in IMS

This topic describes how the adapter handles SSA generation when the condition in the SQL request compares a key field to a list of values. The key can be unique or non-unique. The form of such a condition is

WHERE key IN (value1, value2, ... ,valuen)

where:

key: Is a key field.
value1,...,valuen: Are the comparison values.

In this case, the adapter constructs a separate SSA for each value in the list (in ascending sort sequence) and transmits each one in turn to IMS:

(key EQ value1)
  .
  .
  .
(key EQ valuen)

The adapter first issues a DL/I call containing only the first SSA. If IMS locates a segment that satisfies the condition in the SSA, the adapter returns the segment to the server. Otherwise, the adapter issues a DL/I call that incorporates only the second SSA. It continues until IMS either locates a segment that satisfies one of the SSAs or exhausts the list of values.

Top of page

Example: Constructing Multiple SSAs in IMS

In the following example, the adapter constructs three SSAs:

SQL
SELECT SSN, SEQFIELD, LAST_NAME
FROM PATDB01
WHERE SSN IN ('197548682', '197548685', '197548691')
END

The trace results show the three separate SSAs:

14.54.29 DU  set up SSA-Q:  
14.54.29 DU D7C1E3C9 D5C6D640 5C604DE2 E2D54040      *PATINFO *-(SSN  *
14.54.29 DU 4040407E 40F1F9F7 F5F4F8F6 F8F25D40      *   = 197548682) *
 
14.54.29 DU D7C1E3C9 D5C6D640 5C604DE2 E2D54040      *PATINFO *-(SSN  *
14.54.29 DU 4040407E 40F1F9F7 F5F4F8F6 F8F55D40      *   = 197548685) *
 
14.54.29 DU D7C1E3C9 D5C6D640 5C604DE2 E2D54040      *PATINFO *-(SSN  *
14.54.29 DU 4040407E 40F1F9F7 F5F4F8F6 F9F15D40      *   = 197548691) *

The output is:

SSN        SEQFIELD  LAST_NAME
 
197548682  100003    SALEH
197548685  100006    JACA
197548691  100012    BOYCE

The next request illustrates an equality test on the key field and an additional test on a search field:

SQL
SELECT SSN, SEQFIELD, LAST_NAME
FROM PATDB01
WHERE SSN IN ('197548682', '197548685', '197548691') AND SEQFIELD < 
'100012';
END

The corresponding trace shows the multiple SSAs that are generated:

15.14.05 DU  set up SSA-Q:
15.14.05 DU D7C1E3C9 D5C6D640 5C604DE2 E2D54040      *PATINFO *-(SSN  *
15.14.05 DU 4040407E 40F1F9F7 F5F4F8F6 F8F25CE2      *   = 197548682*S*
15.14.05 DU C5D8C6C9 C5D3C44C 40F1F0F0 F0F1F25D      *EQFIELD< 100012)*
15.14.05 DU 40                                       *                *
 
15.14.05 DU D7C1E3C9 D5C6D640 5C604DE2 E2D54040      *PATINFO *-(SSN  *
15.14.05 DU 4040407E 40F1F9F7 F5F4F8F6 F8F55CE2      *   = 197548685*S*
15.14.05 DU C5D8C6C9 C5D3C44C 40F1F0F0 F0F1F25D      *EQFIELD< 100012)*
15.14.05 DU 40                                       *                *
 
15.14.05 DU D7C1E3C9 D5C6D640 5C604DE2 E2D54040      *PATINFO *-(SSN  *
15.14.05 DU 4040407E 40F1F9F7 F5F4F8F6 F9F15CE2      *   = 197548691*S*
15.14.05 DU C5D8C6C9 C5D3C44C 40F1F0F0 F0F1F25D      *EQFIELD< 100012)*

The adapter constructs three SSAs and applies them one at a time:

(SSN EQ 197548682 AND SEQFIELD LT 100012)
(SSN EQ 197548685 AND SEQFIELD LT 100012)
(SSN EQ 197548691 AND SEQFIELD LT 100012)

The output is:

SSN        SEQFIELD  LAST_NAME
 
197548682  100003    SALEH
197548685  100006    JACA

If the SSA generated by combining the conditions in all the WHERE statements is too long to fit into the SSA buffer, the adapter retains as much of it as possible in a qualified call by applying the rules described in The IMS SSA Buffer.

Top of page

Reference: Sequentially Accessed Root Segments in IMS

If the root segment is the target of the SSA generated by a request, and if it has a unique key, the adapter assumes that IMS will use an index or randomizing scheme to locate the segment without an exhaustive search of the root segment chain. Therefore, the adapter retrieves the segment with qualified GET UNIQUE calls.

Even if the assumption that there is an index or randomizing scheme for IMS to use is not valid, as with HSAM data sources, each call starts its search at the first record in the data source. In this case, it is preferable to describe the root segment as having no key (SEGTYPE=S0), and not to describe any field's alias with the KEY suffix. This causes the adapter to issue qualified GET NEXT calls that access the roots sequentially and maintain the current data source position from one call to the next.

Partial Key and Multi-Segment Requests in IMS

How to:

Optimize Selections on a Partial Key in IMS

The following topics illustrate SSAs for requests that select on a partial key and requests that access values from multiple segments:

Top of page

Syntax: How to Optimize Selections on a Partial Key in IMS

Record selection on a partial key can be optimized as a range condition using GE and LE unless the data source is an HDAM data source. The partial key must be the high-order (leftmost) portion of the key. To search on a partial key, use a mask as the comparison value in the relation.

WHERE field LIKE 'xxx%'

where:

xxx: Are any number of characters that constitute the leftmost portion of the key.
%: Is a wildcard character indicating that any string of characters in this position and beyond satisfies the screening criteria.

Top of page

Example: Optimizing Selections on a Partial Key in IMS

The adapter translates the LIKE condition in the following request to a range using GE and LE:

SQL
SELECT SSN, SEQFIELD, LAST_NAME
FROM PATDB01
WHERE SSN LIKE '1975486%';
END

The trace results show that the selection test is translated to a range condition:

DU ssa 1
10.09.21 DU D7C1E3C9 D5C6D640 5C604DE2 E2D54040      *PATINFO *-(SSN  *
10.09.21 DU 4040406E 7EF1F9F7 F5F4F8F6 00005CE2      *   >=1975486..*S*
10.09.21 DU E2D54040 4040404C 7EF1F9F7 F5F4F8F6      *SN     <=1975486*
10.09.21 DU FFFF5D40                                 *..)             *

To define the range of values, the adapter appends the lowest hexadecimal value (00) and the highest hexadecimal value (FF) to 1975486 (00 and FF are not alphanumeric representations of numbers; therefore they do not print as such on the right side of the dump).

The output is:

SSN        SEQFIELD  LAST_NAME
 
197548679  100000    ROSANO
197548680  100001    LOVELACE
197548681  100002    BORRERO
197548682  100003    SALEH
197548683  100004    SALGADO
197548684  100005    JONES
197548685  100006    JACA
197548686  100007    PENA
197548687  100008    FREEMAN

Top of page

Example: Generating Multi-Segment Requests in IMS

When a request requires access to multiple segments, the adapter constructs one SSA for each segment. If the selection criteria for a particular segment can be optimized, its corresponding SSA is qualified.

The following request includes an equality test on PARTKEY, the root key of the DI21PART data source, but it contains no selection criteria for the STANKEY field in the STANINFO segment:

SQL
SELECT PARTKEY, STANKEY
FROM DI21PART
WHERE PARTKEY = '02AN960C10'
END

The SSA for the root segment, PARTROOT, is qualified, but the SSA for the STANINFO segment is not qualified:

10.53.47 DU  set up SSA-Q:
10.53.47 DU D7C1D9E3 D9D6D6E3 5C604DD7 C1D9E3D2      *PARTROOT*-(PARTK*
10.53.47 DU C5E8407E 40F0F2C1 D5F9F6F0 C3F1F040      *EY = 02AN960C10 *
10.53.47 DU 40404040 40405D40                        *      )         *
 
10.53.47 DU E2E3C1D5 C9D5C6D6 5C604040               *STANINFO*-      *

The output is:

PARTKEY     STANKEY
 
02AN960C10  02

Auto Index Selection

Reference:

Selection Tests on Multiple Fields or a Field With Multiple Indexes

When the Access File defines secondary indexes for a data source, the adapter analyzes each request to determine the most efficient entry point into the data source.

The adapter scans each request to determine whether fields used in record selection tests are key fields, secondary indexes, or the high-order (leftmost) parts of either. Depending on the request criteria, the adapter selects the appropriate PCB for the most efficient access to the data.

Top of page

Example: Using Auto Index Selection

The PATIENT data source has a secondary index named IXADMD on the ADMIT_DATE field.

In the following request, the field referenced in the WHERE condition is the field associated with the secondary index called IXADMD:

SQL
SELECT LAST_NAME,SALARY,ADMIT_DATE
  FROM PATINFO
WHERE ADMIT_DATE = '19920925'
END

The trace shows that the adapter generates a qualified SSA using the IXADMD index:

11.44.55 DU D7C1E3C9 D5C6D640 5C604DC9 E7C1C4D4      *PATINFO *-(IXADM*
11.44.55 DU C440407E 40F1F9F9 F2F0F9F2 F55D40        *D  = 19920925)  *

The output is:

LAST_NAME  SALARY   ADMIT_DATE
 
CAPPARELLI 1024.00  19920925
CAPPARELLI 16682.00 19920925

Top of page

Reference: Selection Tests on Multiple Fields or a Field With Multiple Indexes

If a request includes record selection tests on more than one field, or if a field participates in more than one type of index, the adapter uses the following order of precedence in choosing the PCB to use:

.KEY field (primary index).
If a field in a record selection test is both a .KEY field and the high-order portion of a secondary index, the adapter accesses the data source using the primary index on the .KEY field.

However, if the data source is an HDAM database, and if the request includes a range test on a field that is both an .HKY field and a secondary index field, the adapter accesses the data source through the secondary index field.
Secondary index field.
.IMS field (search field).

Top of page

Example: Using Auto Index Selection to Generate a Qualified SSA

The following example demonstrates how secondary indexes and the Auto Index Selection feature affect SSA generation.

The following Master File, AIHDAM, describes an HDAM database. Without a secondary index, the adapter would generate an unqualified SSA on the key field. However, a secondary index is described in the Access File:

FILE=AIHDAM   ,SUFFIX=IMS   ,$
SEGNAME=LANGUAGE ,SEGTYPE=S0 ,$
 FIELD=EMPLOYEE_ID6  ,ALIAS=EMPL6.HKY   ,I9 ,I4  ,$
 FIELD=LANGUAGE      ,ALIAS=LANG6.IMS   ,A15 ,A15 ,$

The adapter can use the Auto Index Selection feature on the following request to generate a qualified SSA for the range test on the key field:

SELECT *
FROM AIHDAM
WHERE EMPLOYEE_ID6 BETWEEN 5248 AND 6393

14.46.43 DU  set up SSA-Q:
14.46.43 DU D3C1D5C7 E4C1C7C5 5C604DC9 E7C5D4D7      *LANGUAGE*-(IXEMP*
14.46.43 DU F640406E 7E000014 805CC9E7 C5D4D7F6      *6  >=....*IXEMP6*
14.46.43 DU 40404C7E 000018F9 5D40                   *  <=...9)       *