OLQ GET vs. OLQ SELECT
Article ID: 18720
Updated On:
Products
Issue/Introduction
Description:
In OLQ, both a GET & SELECT will perform retrieval. Yet they can produce different results, and sometimes one succeeds where the other fails. This document describes the differences between the two.
Solution:
There are a number of things which are different between a SELECT and a GET under OLQ. The first is that the GET indicates a specific physical access method to use in accessing the data, whereas for the SELECT the path will be chosen by the optimizer. The GET will retrieve the records in the order specified by the GET and will return the records in that order; the SELECT may use an index, calckey sequence, or other access method. To elaborate, GET is in essence a physical I/O command: it dictates the physical path used to access the data (GET FIRST PHYSICAL, GET NEXT IN AREA, etc). The SELECT is an SQL query, and like all SQL statements is only a logical request for data. It indicates the data items which should be returned and the data sources (tables or records) in which those data items are stored, but does not indicate the physical path to take in order to satisfy that request. This is true of any implementation of SQL, and is part of the ANSI SQL standard. Every vendor has their own optimizer which evaluates every SQL logical request, queries the database structure and statistics, and determines the most efficient way to retrieve the data based on that information. The physical route taken to satisfy the logical SQL request is often called the access path. There are a couple of Knowledge Documents (KDs) on our online support site which talkabout this, if you would like more information: FAQ260935, FAQ360172, & FAQ360173).
A second difference is that SELECT will always do set-at-a-time processing. It will retrieve all rows which meet the specified criteria, and only then will it turn control back to OLQ. The OLQ interrupt count will only get applied at that point, and will control how many rows to display at one time. So with the SELECT, the OLQ Interrupt count will impact only the display results, not the I/O required to satisfy the request nor the performance time. The GET does record-at-a-time processing, so that OLQ gets control back after each I/O; thus the Interrupt Count is applied during the actual data retrieval and has an impact on the amount of I/O performed before each set of rows is displayed. Because of this difference in how the Interrupt Count is implemented, a SELECT will almost always use more scratch than a GET, unless the SELECT is specific enough that it returns fewer rows than the value specified in Interrupt Count.
This difference in scratch utilization can have frequent impact in comparing GET to SELECT. As noted above, a SELECT will always use the scratch area to store rows until the report is complete, and will then display them. On a GET statement, the scratch area will only be used to house an incomplete report if that is necessary because of something in the statement; or to sort a report; or save a report if that is explicitly requested. In the case of a simple GET FIRST 100 PHYSICAL.. DISPLAY, the scratch utilization could be radically different than that of a SELECT. If you want to see the differences in how these two commands use the DCLDCR scratch area, you can run them both in local mode using the same OLQBatch JCL, with the following on both jobs:
//SYSIDMS DD *
IDMSDBUG=ON
/*
The IDMSDBUG parameter will produce a large amount of output and will require assistance from technical support to interpret.
A third consideration is the mode in which the jobs were run. Both commands can be run in batch mode or online. If differences are observed in their output, it should be confirmed that both were run the same way. If they were both run in batch mode, was the same JCL used? If they were run batch and the DCLSCR DD statement was dummied in the SELECT job, or was specified differently in the two jobs, that could explain differences in the outcome. Or if one batch job was run local mode and the other through the CV< that could cause differences.
Also, there are two ways in which a SELECT could be evaluated, depending on the value specified for the ACCESS parameter in the sysgen OLQ statement (or via an override for this specific OLQ session). With queries submitted through OCF or BCF, the standard IDMS SQL optimizer is invoked. However, when submitting a SELECT through OLQ, there are two options., controlled by the OLQ sysgen options ACCESS IS OLQSQL vs. ACCESS IS IDMSSQL. This option indicates which optimizer and syntax parser OLQ will use to evaluate the OLQ Select. OLQ has its own, which is what will be used when the sysgen specifies ACCESS IS OLQSQL. Unfortunately, with this access mode there's no way to view which path OLQ has chosen. If you specify ACCESS IS IDMSSQL in the sysgen, then you could take the same SELECT and execute it in OCF or BCF. There you could issue an EXPLAIN statement for the SELECT. The output of the EXPLAIN is a table which can be read to determine what physical access path was used to retrieve the data. With ACCESS IS IDMSSQL, the access used by OLQ, OCF, & BCF are all the same, so that the output of the EXPLAIN in OCF applies equally to how the SELECT will be optimized in OLQ. Another KD which talks about how to more easily interpret the results of an EXPLAIN is TEC477873.
Since the scratch area will play a major role in the performance of a SELECT, there are a few aspects of it that deserve some attention.
- The scratch area is used for temporary storage and we must be able to read from it as well as write to it. If the scratch area is defined as an output file (e.g.: DD DYDOUT=*), there will be no way we could read back whatever we've temporarily written there.
- If you specify the SYSIDMS parm XA_SCRATCH=ON, then scratch is allocated in memory instead of using the DDLDCSCR file defined in the DMCL. The size of XA_SCRATCH allocated is the same size as the DDLDCSCR file in the DMCL. This is what most sites do. For this to work, you may need a larger REGION size for any batch jobs that use scratch (IDMSDDDL, IDMSBCF, ADSOBCOM, OLQBATCH, IDMSDMLx, etc). We recommend using
REGION=0M. If you didn't specify the REGION =0M then running with XA_SCRATCH=ON may fail with "DC045018 Scratch area full".
- If your batch jobs are running local mode, we recommend that a DDLOCSCR area is defined in the DMCL and that all local mode batch jobs use this area.
- Depending on whether your job runs local mode or through CV, the DD statement in your job can override what is specified in the DMCL. A local mode job can override a dataset name specified in the DMCL, and the CV startup JCL can override the global DMCL, but the JCL for a cv batch job cannot override what's in the DMCL. If the JCL specifies a scratch area, then it's important to know whether this will override the DMCL definition (which it will in local mode) or whether the DMCL definition will be used (which will happen when running through the CV). The scratch file that is used can impact whether adequate scratch space is available to hold the full report, which as noted before is what SELECT will do but GET will not.
Environment
Component:
Feedback
