In this section: |
You can issue any FOCUS report request through the Adapter for IMS/DB. However, those requests whose record selection criteria can be applied at the IMS level (with qualified SSAs on .KEY, .IMS, .HKY, or .SKY fields) result in many fewer DL/I calls and I/O operations than non-optimized requests. They can achieve performance improvements measured in orders of magnitude.
Note: Since IMS does not support the concept of missing data, FOCUS considers all fields not missing.
This section includes sample requests with their corresponding trace results. When you enable a trace as described in Tracing Adapter Processing, you get a display of the SSA buffer set-up for each request. For example, the following is a selection test on the SEQFIELD search field in the PATINFO segment of the PATDB01 database (see Sample File Descriptions):
IF SEQFIELD EQ '100000' OR '100001'
It produces the following results:
set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 C5D8C6C9 *PATINFO *-(SEQFI* C5D3C47E 40F1F0F0 F0F0F04E E2C5D8C6 *ELD= 100000+SEQF* C9C5D3C4 7E40F1F0 F0F0F0F1 5D40 *IELD= 100001) *
Note:
Since not all hexadecimal values represent printable alphanumeric characters, those parts of the buffer contents that correspond to non-printable characters display as blanks on the right side. In particular, if a comparison value in an SSA is a packed number, it displays as blank on the right-hand side of the buffer display. In this example, the comparison values are alphanumericand, therefore, they display.
Whenever possible, the adapter translates screening conditions from the FOCUS request into qualified SSAs. However, not all FOCUS screening conditions can be translated into SSAs. Such conditions are still applied, but by FOCUS, not by IMS. In those cases, the adapter retrieves the data sequentially and passes it to FOCUS for screening.
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:
KEY EQ value
In order for the adapter to translate a FOCUS IF test into a qualified SSA, the following conditions must exist:
Note: In order for the adapter to optimize selection tests on zoned key fields, you mustdescribe the USAGE format of these fields as alphanumeric or packed in the Master File.
The adapter can translate the following test relations to qualified SSAs:
field relation value1 OR value2 ... OR valuen
See Complex Screening Conditions for a discussion of SSA generation when comparing a field to a list of values.
The adapter treats the FROM…TO relation as a pair of relations. For example:
IF field GE . . . AND IF field LE . . .
The adapter also translates a search on a partial key (the high-order portion of a key) to a range condition and optimizes it (see Partial Key and Multi-Segment Requests for an example).
Note: The adapter does not optimize the relations INCLUDES, EXCLUDES, CONTAINS, and OMITS.
In this section: |
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: Using the AND operator between logical conditions in a selection test is equivalent to using multiple IF statements without the AND operator. The adapter constructs a qualified SSA that incorporates the AND operation in either case.
This section describes the following:
Once the adapter constructs an SSA, it must place it 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:
If an SSA does not fit into the SSA buffer, no error is generated. The selection tests omitted from the SSA are applied, but they are not optimized. That is, FOCUS 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 and on the relations used in the comparisons.
The adapter constructs a single SSA that incorporates the entire complex logical condition if any of the following is true:
If the entire SSA fits into the SSA buffer, the adapter submits it 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 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:
TABLE FILE PATINFO PRINT SSN SEQFIELD LAST_NAME IF SEQFIELD EQ '100000' OR '100005' IF LAST_NAME EQ 'BORRERO' OR 'JONES' END
These selection criteria produce the following SSA:
set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 C5D8C6C9 *PATINFO *-(SEQFI* C5D3C47E 40F1F0F0 F0F0F05C C9E7D5C1 *ELD= 100000*IXNA* D4C54040 6E7EC2D6 D9D9C5D9 D6404040 *ME >=BORRERO * 40400000 00000000 00000000 00005CC9 * ............*I* E7D5C1D4 C540404C 7EC2D6D9 D9C5D9D6 *XNAME <=BORRERO* 40404040 40FFFFFF FFFFFFFF FFFFFFFF * ...........* FF4EE2C5 D8C6C9C5 D3C47E40 F1F0F0F0 *.+SEQFIELD= 1000* F0F05CC9 E7D5C1D4 C540406E 7ED1D6D5 *00*IXNAME >=JON* C5E24040 40404040 40000000 00000000 *ES .......* 00000000 005CC9E7 D5C1D4C5 40404C7E *.....*IXNAME <=* D1D6D5C5 E2404040 40404040 FFFFFFFF *JONES ....* FFFFFFFF FFFFFFFF 4EE2C5D8 C6C9C5D3 *........+SEQFIEL* C47E40F1 F0F0F0F0 F55CC9E7 D5C1D4C5 *D= 100005*IXNAME* 40406E7E C2D6D9D9 C5D9D640 40404040 * >=BORRERO * 00000000 00000000 00000000 5CC9E7D5 *............*IXN* C1D4C540 404C7EC2 D6D9D9C5 D9D64040 *AME <=BORRERO * 404040FF FFFFFFFF FFFFFFFF FFFFFF4E * ............+* E2C5D8C6 C9C5D3C4 7E40F1F0 F0F0F0F5 *SEQFIELD= 100005* 5CC9E7D5 C1D4C540 406E7ED1 D6D5C5E2 **IXNAME >=JONES* 40404040 40404000 00000000 00000000 * .........* 0000005C C9E7D5C1 D4C54040 4C7ED1D6 *...*IXNAME <=JO* D5C5E240 40404040 4040FFFF FFFFFFFF *NES ......* FFFFFFFF FFFF5D40 *......) *
The trace shows that the adapter generates one SSA, incorporating the following selection criteria:
(SEQFIELD EQ 100000 AND IXNAME GE BORRERO AND IXNAME LE BORRERO OR SEQFIELD EQ 100000 AND IXNAME GE JONES AND IXNAME LE JONES OR SEQFIELD EQ 100005 AND IXNAME GE BORRERO AND IXNAME LE BORRERO OR SEQFIELD EQ 100005 AND IXNAME GE JONES AND IXNAME LE JONES)
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 FOCUS would apply the remaining tests to the returned segments.
The request produces the following report:
PAGE 1 SSN SEQFIELD LAST_NAME --- -------- --------- 197548684 100005 JONES
This section describes how the adapter handles SSA generation when the condition in the FOCUS 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
IF key EQ value1 OR value2 OR value3 ...
IF key IS (filename)
or
WHERE key IN (value1, value2, ... ,valuen)
where:
Is a key field or secondary index.
Are the comparison values.
Indicates that the comparison values are stored in a sequential file allocated to DDNAME filename. See the Creating Reports manual for information about the maximum size of the file.
With this kind of screening condition, 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 FOCUS. 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.
In the following example, the adapter constructs three SSAs:
TABLE FILE PATINFO PRINT SSN SEQFIELD LAST_NAME IF SSN EQ '197548682' OR '197548685' OR '197548691' END
The tracce results show the three separate SSAs generated:
set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 E2D54040 *PATINFO *-(SSN * 4040407E 40F1F9F7 F5F4F8F6 F8F25D40 * = 197548682) * set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 E2D54040 *PATINFO *-(SSN * 4040407E 40F1F9F7 F5F4F8F6 F8F55D40 * = 197548685) * set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 E2D54040 *PATINFO *-(SSN * 4040407E 40F1F9F7 F5F4F8F6 F9F15D40 * = 197548691) *
The following is the resulting report:
PAGE 1 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:
TABLE FILE PATINFO PRINT SSN SEQFIELD LAST_NAME IF SSN EQ '197548679' OR '197548682' OR '197548685' IF SEQFIELD GT '100000' END
The request produces multiple SSAs, as illustrated in the following trace:
set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 E2D54040 *PATINFO *-(SSN * 4040407E 40F1F9F7 F5F4F8F6 F7F95CE2 * = 197548679*S* C5D8C6C9 C5D3C46E 40F1F0F0 F0F0F05D *EQFIELD> 100000)* 40 * * set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 E2D54040 *PATINFO *-(SSN * 4040407E 40F1F9F7 F5F4F8F6 F8F25CE2 * = 197548682*S* C5D8C6C9 C5D3C46E 40F1F0F0 F0F0F05D *EQFIELD> 100000)* 40 * * set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 E2D54040 *PATINFO *-(SSN * 4040407E 40F1F9F7 F5F4F8F6 F8F55CE2 * = 197548685*S* C5D8C6C9 C5D3C46E 40F1F0F0 F0F0F05D *EQFIELD> 100000)* 40 * *
The adapter constructs three SSAs and applies them one at a time:
(SSN EQ 197548679 AND SEQFIELD GT 100000) (SSN EQ 197548682 AND SEQFIELD GT 100000) (SSN EQ 197548685 AND SEQFIELD GT 100000)
The following report is produced:
PAGE 1 SSN SEQFIELD LAST_NAME --- -------- --------- 197548682 100003 SALEH 197548685 100006 JACA
If the SSA generated by combining the conditions in all the IF 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 SSA Buffer.
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 databases, each call starts its search at the first record in the database. In this situation, it is preferable to describe the root segment as having no key (SEGTYPE=S0), and not to describe any field alias with the .KEY suffix. This technique causes the adapter to issue qualified GET NEXT calls that access the roots sequentially and maintain their position in the database from one call to the next.
When the adapter constructs DL/I calls, it looks only at IF selection tests, not WHERE tests. However, prior to constructing DL/I calls, it examines WHERE selection tests and, when possible, translates them to equivalent IF tests. Therefore, WHERE tests that can be expressed as IF tests are subject to the same optimization rules as IF tests.
For example, if field1 and field2 are search or sequence fields, the adapter optimizes the following WHERE test:
WHERE field1 relation1 value1 AND field2 relation2 value2
It is equivalent to the following:
IF field1 relation1 value1 IF field2 relation2 value2
The adapter can also optimize the following WHERE conditions:
The adapter optimizes the following request:
TABLE FILE PATINFO PRINT SSN SEQFIELD LAST_NAME WHERE SSN EQ '197548679' AND SEQFIELD EQ '100000' END
The trace shows the qualified SSA that the request generates:
set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 E2D54040 *PATINFO *-(SSN * 4040407E 40F1F9F7 F5F4F8F6 F7F95CE2 * = 197548679*S* C5D8C6C9 C5D3C47E 40F1F0F0 F0F0F05D *EQFIELD= 100000)* 40 * *
The report follows:
PAGE 1 SSN SEQFIELD LAST_NAME --- -------- --------- 197548679 100000 ROSANO
The adapter cannot optimize the following types of WHERE tests:
WHERE field1 relation field2 WHERE field1 relation1 value1 OR field2 relation2 value2
For example, the adapter does not optimize the next request:
TABLE FILE PATINFO PRINT SSN SEQFIELD LAST_NAME WHERE SSN EQ '197548679' OR SEQFIELD EQ '100000' END
The resulting SSA is not qualified; it includes the segment name, but no selection criteria:
set up SSA-Q: D7C1E3C9 D5C6D640 5C604040 *PATINFO *- *
FOCUS retrieves all records from the segment, applies the selection criteria, and produces the following report:
PAGE 1 SSN SEQFIELD LAST_NAME --- -------- --------- 197548679 100000 ROSANO
In this section: |
This section illustrates SSAs for requests that select on a partial key and requests that access values from multiple segments.
The adapter can optimize record selection on a partial key unless the database is an HDAM database. 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. The syntax is
{IF|WHERE} field EQ 'xxx$*'
{IF|WHERE} field LIKE 'xxx%'
where:
Is a valid field name.
Are any number of characters that constitute the leftmost portion of the key. You must enclose the masking characters between single quotation marks.
Are wildcard characters indicating that any string of characters in this position satisfies the screening criteria. Use $* with the EQ relation and % with the LIKE relation.
The adapter translates the condition to a range using GE and LE. Consider the following request:
TABLE FILE PATINFO PRINT SSN SEQFIELD LAST_NAME WHERE SSN EQ '1975486$*' END
The trace results show that the adapter translated the selection test to a range condition:
set up SSA-Q: D7C1E3C9 D5C6D640 5C604DE2 E2D54040 *PATINFO *-(SSN * 4040406E 7EF1F9F7 F5F4F8F6 00005CE2 * >=1975486 *S* E2D54040 4040404C 7EF1F9F7 F5F4F8F6 *SN <=1975486* 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 on the right side of the buffer display).
The first few lines of the resulting report follow:
PAGE 1 SSN SEQFIELD LAST_NAME --- -------- --------- 197548679 100000 ROSANO 197548681 100002 BORRERO 197548682 100003 SALEH 197548683 100004 SALGADO 197548684 100005 JONES 197548685 100006 JACA 197548686 100007 PENA 197548687 100008 FREEMAN . . .
When a request requires access to multiple segments, the adapter constructs one SSA for each segment. (DL/I Calls explains the rules for accessing multiple segments.) 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 database, but it contains no selection criteria for the STANKEY field in the STANINFO segment:
TABLE FILE DI21PART PRINT PARTKEY STANKEY IF PARTKEY EQ '02AN960C10' END
The SSA for the root segment, PARTROOT, is qualified, but the SSA for the STANINFO segment is not qualified:
set up SSA-Q: D7C1D9E3 D9D6D6E3 5C604DD7 C1D9E3D2 *PARTROOT*-(PARTK* C5E8407E 40F0F2C1 D5F9F6F0 C3F1F040 *EY = 02AN960C10 * 40404040 40405D40 * ) * set up SSA-Q: E2E3C1D5 C9D5C6D6 5C604040 *STANINFO*- *
The request produces the following report:
PAGE 1 PARTKEY STANKEY ------- ------- 02AN960C10 02
The next request includes a not-equal condition on PARTKEY, the root key of the DI21PART database. It has no selection criteria for STANKEY:
TABLE FILE DI21PART PRINT PARTKEY STANKEY IF PARTKEY NE '02AN960C10' END
The following is the SSA for both the PARTROOT and STANINFO segments:
set up SSA-Q: D7C1D9E3 D9D6D6E3 5C604DD7 C1D9E3D2 *PARTROOT*-(PARTK* C5E8405F 7EF0F2C1 D5F9F6F0 C3F1F040 *EY ¬=02AN960C10 * 40404040 40405D40 * ) * set up SSA-Q: E2E3C1D5 C9D5C6D6 5C604040 *STANINFO*- *
FOCUS applies the selection criteria to produce the desired report. The first several lines in the report follow:
PAGE 1 PARTKEY STANKEY ------- ------- 02CK05CW181K 02 02CSR13G104KL 02 02JAN1N976B 02 02MS16995-28 02 02N51P3003F000 02 02RC07GF273J 02 02TPART01 02 02106B1293P009 02 02250236-001 02 . . .
It is important to consider how qualified SSAs on lower level segments of a database may affect performance:
These facts make it advantageous to screen on the root segment, where an index or hash code makes retrieval efficient. One technique for preventing an exhaustive search of the database is to define fields in low level segments in the Master File as non-search fields. This approach forces control to return to FOCUS after retrieval of each segment instance, giving you the opportunity to limit processing with a READLIMIT test. For information on READLIMIT, see Search Limits.
When the Master File and FOCPSB define secondary indexes for a database, the adapter analyzes each request to determine the most efficient entry point into the database. For information on creating a Master File and FOCPSB for use with a database that has secondary indexes, refer to IMS Overview and Mapping Concepts, and Creating FOCUS Descriptions.
The adapter scans each request to determine if 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. The PATINFO Master File describes a secondary index named IXADMD on the ADMIT_DATE field.
In the following request, the field referenced in the IF condition is the field associated with the secondary index called IXADMD:
TABLE FILE PATINFO PRINT LAST_NAME SALARY ADMIT_DATE IF ADMIT_DATE EQ '19920925' END
The trace shows that the adapter generates a qualified SSA using the IXADMD index:
set up SSA-Q: D7C1E3C9 D5C6D640 5C604DC9 E7C1C4D4 *PATINFO *-(IXADM* C440407E 40F1F9F9 F2F0F9F2 F55D40 *D = 19920925) *
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 which PCB to use:
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 database via the primary index on the .KEY field.
However, if the database is an HDAM database, and if the request includes a range test on a field that is both an .HKY field and a .SKY field, the adapter accesses the database through the secondary index on the .SKY field. (Recall that only equality conditions on .HKY fields generate qualified SSAs.)
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. This Master File does not describe a secondary index on the key field (the DBD, PSB, and FOCPSB do include a secondary index description. See Sample File Descriptions):
FILE=AIHDAM ,SUFFIX=IMS ,$ SEGNAME=LANGUAGE ,SEGTYPE=S1 ,$ FIELD=EMPLOYEE_ID6 ,ALIAS=EMPL6.HKY ,I9 ,I4 ,$ FIELD=LANGUAGE ,ALIAS=LANG6.IMS ,A15,A15 ,$
A range test on the key field generates an unqualified SSA:
TABLE FILE AIHDAM > PRINT * > WHERE EMPLOYEE_ID6 FROM 5248 TO 6393 > END
set up SSA-Q: D3C1D5C7 E4C1C7C5 5C604040 *LANGUAGE*- *
Following is the same Master File, edited to describe the secondary index on the key field:
FILE=AIHDAM ,SUFFIX=IMS ,$ SEGNAME=LANGUAGE ,SEGTYPE=S1 ,$ FIELD=EMPLOYEE_ID6 ,ALIAS=EMPL6.HKY ,I9 ,I4 ,$ FIELD=LANGUAGE ,ALIAS=LANG6.IMS ,A15,A15 ,$ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ $$ SECONDARY INDEX DESCRIPTION $$ $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ GROUP=EMPLIX ,ALIAS=IXEMP6.SKY ,I9 ,I4 ,$ FIELD=EMPLY6 ,ALIAS=EMPLOYEE_ID6,I9 ,I4 ,$
Now, the adapter can use the Auto Index Selection feature on the same request to generate a qualified SSA:
> > TABLE FILE AIHDAM > PRINT * > WHERE EMPLOYEE_ID6 FROM 5248 TO 6393 > END
set up SSA-Q: D3C1D5C7 E4C1C7C5 5C604DC9 E7C5D4D7 *LANGUAGE*-(IXEMP* F640406E 7E000014 805CC9E7 C5D4D7F6 *6 >= *IXEMP6* 40404C7E 000018F9 5D40 * <= 9) *
There are two search limit tests available through the adapter.
When reporting against an IMS database, you may want to halt the retrieval after reading part of the database when any of the following conditions exists:
The syntax is
IF {READLIMT|RECORDLIMIT} EQ n
where:
Is a number greater than 0.
The Database Administrator can supply this screening condition directly in the Master File, in which case you cannot override it. If you include a READLIMIT or RECORDLIMIT test in a request, and the Database Administrator also includes one in the Master File, FOCUS uses the smaller value. (Refer to your FOCUS documentation for additional information on READLIMIT and RECORDLIMIT.)
FOCUS, not IMS, applies READLIMIT and RECORDLIMIT tests. Therefore, because IMS always searches as much of the database as necessary to locate an appropriate segment, the READLIMIT setting has no effect on retrieval in an optimized request.
To illustrate this point, the next examples reference fields from the PATINFO Master File (see Sample File Descriptions). The database contains the following values:
SEQFIELD | FIRST_NAME | LAST_NAME |
---|---|---|
100000 | ANDRE | ROSANO |
100001 | LARRY | LOVELACE |
100002 | JOHN | BORRERO |
100003 | JAMAL | SALEH |
Consider this request:
TABLE FILE PATINFO PRINT FNAME IF READLIMIT EQ 1 END
Since the field FNAME is not qualified, FOCUS applies the READLIMIT condition and locates the following value for FNAME in its one read:
ANDRE
The next request includes a screening condition on FNAME, an IMS search field. The adapter passes the screening condition to IMS in a qualified SSA, and IMS searches for a segment that passes the test, unaware of the READLIMIT condition:
TABLE FILE PATINFO PRINT FNAME IF FNAME GT 'JAMES' IF READLIMIT EQ 2 END
The report contains the following values for FNAME:
LARRY JOHN
FOCUS considers the retrieval of a qualified segment a single read, even though IMS may read many segments to locate each one that it returns to FOCUS. In the previous example, IMS had to read three records in order to locate the two records that it returned to FOCUS.
In the next example, the requested value does not exist in the database but, because FNAME is qualified by an optimized screening condition, IMS searches the entire database trying to locate it:
TABLE FILE PATINFO PRINT FNAME IF FNAME GT 'TOMMY' IF READLIMIT IS 1 END
To prevent this situation, the user should be aware of the valid range of values contained in the database when IMS search fields are part of a Master File.
Information Builders |