The essence of push down filtering is to delegate all or part of the Analyzer or ConnectPlus filtering process to the Arbutus zSeries Server host system in order to optimize overall performance. Push down filtering is only available using the host system's (DB2, IMS, ADABAS and VSAM) NATIVE fields and keys. For more information see Native and Key Fields.
When push down filtering is performed, the filter reporting ("x of y met the test") in Analyzer's status bar and Command Log will differ from filter reporting for all other sequential file types when referencing NATIVE fields.
For example, if a "global filter" using a NATIVE field is applied to a DB2 table, then only the records that meet the NATIVE field filter criteria are read from the underlying database. Analyzer is not aware of any records that did not meet the test, so a subsequent count would report that "10 of 10 met the test: global filter". Conversely, when processing other sequential file types using a global filter, Analyzer would read the entire file, apply the global filter and report that "10 of 5834 met the test: global filter".
If an additional local filter is then applied to a subsequent Count command using a field that is NOT a NATIVE field, the records counted would be a subset of the 10 that matched the earlier global filter. Since filter comparisons to fields that are not NATIVE cannot be passed on to the database, they are filtered internally using Analyzer logic.
There are a couple of other filtering differences when referencing comparisons to NATIVE keys with these data sources.
| 1. | Differing length comparisons - if a comparison string is specified that is shorter than the actual NATIVE field length, Analyzer filter logic would treat this as a "generic" search returning any field value starting with the specified comparison string. However, for push down filtering of IMS, the shorter comparison string is padded with blanks to match the length of the NATIVE field and then only exact matches are returned. Therefore, results may differ depending on whether the filtering is pushed down to IMS or performed within Analyzer. |
Note: For IMS, if a comparison string is specified that is longer than the actual NATIVE field length, the following error message occurs: "Test not pushed down, as comparison value longer than field".
| 2. | Sequencing differences - for filtering on an IMS Native field, the search will start with the first NATIVE field value that meets the criteria and ends when the criteria is no longer met. This means that if a database is out of sequence the number of records returned will be different than if Analyzer had performed the filtering. |
When considering push down filtering, it is also important to understand which filter operations can be pushed down:
| • | For VSAM, any comparisons of a NATIVE key character field to a constant value using " =, >= ", as long as the comparison either stands on its own or is connected to the rest of the filter expression using an AND operator |
| • | For IMS, any comparisons of a NATIVE key character field to a constant value using " >, <, =, <=, >=, <>", as long as the comparison either stands on its own or is connected to the rest of the filter expression using an AND operator |
| • | For other keyed data sources (including DB2 and ADABAS), any comparisons of a NATIVE numeric or character field to a constant, as long as it either stands on its own or is connected using either an AND or OR operators. |
Note: The use of any other concatenation, function or arithmetic operators will eliminate that portion of the filter expression from being pushed down to the host.
The simple expression CITY='Seattle' AND SALARY>100000 is considered a valid expression. In this case any portion containing NATIVE fields will be pushed down to the host individually. If all fields in the example above are NATIVE to the host, then all desired records from the host would be returned. If CITY is the only NATIVE field, the host would return all Seattle records and Analyzer would then filter them to remove the remaining unwanted records where Salary is less than or equal to 100000. This is preferable to returning all the records from the host and then filtering them on the Analyzer side.
Consider the following complex filter expression:
(SALARY>100000 OR DEPT='101') AND CITY='Seattle'
This expression will select all Seattle employees from Department 101 or whose Salary is greater than $100,000. The expression is scanned for any portions containing NATIVE field comparisons that can be pushed down to the host. These portions are isolated from the filter expression and sent to the host. This can only be done where it is possible for the host to produce an appropriate subset of the data.
If CITY is the only NATIVE field then the host can only return all Seattle records, as the final selections are a subset of these. On the other hand, if the only NATIVE field was SALARY, then no push down would be possible as this would exclude Seattle records from Department 101 where the Salary is less than or equal to $100,000.
Similarly, if the expression was QTY*PRICE<>VALUE AND CUSTNO='123456' and the CUSTNO was the only NATIVE key, then only the CUSTNO portion of the filter expression (connected by an AND operator) could be pushed down to the host. The final filtering would be performed by Analyzer to select only the desired records.