Query optimization techniques

The query processor optimizes queries that are written on the databases. However, this optimization is limited to the information that the query processor knows about in the databases and the organization of the databases.

Write effective queries and define logical tables to maximize query performance. Query optimization is based on the extent to which the database or file system performs the filtering that is required to obtain the final result set.

Example: A three-segment IMS HIDAM DBD contains 10,000 instances of the lowest level segment (also called the leaf segment).

If the query processor can build a segment search argument (SSA), which contains a search argument for every segment, a single access is required. In this case, the query processor retrieves the final result set, and the connector or the query processor does not need to perform additional filtering.
If the query processor cannot build an SSA, 10,000 IMS GET commands are issued. In this case, the connector or the query processor must filter the intermediate result set to obtain a single row result set.

The full retrieval of the mapped segments (or an entire VSAM file) is called a full table scan. The IMS retrieval of the single row is faster if the SSA contains primary or secondary index fields. The scenario is the same for VSAM access that involves primary and alternate indexes rather than SSAs.

If the TRACELEVEL configuration parameter for the query processor service is set to 2, the filtering information that obtains the final result set is written to the log. For IMS, you use the TRACELEVEL configuration parameter for the IMS service.