Example 2: Multirow fetch with large row sizes
One example of a large row size table is the DB2 system catalog table SYSIBM.SYSTABLES which is about 2968 bytes long. For this length of result set, only 1 row would completely fit in the default 4K buffer size per data base trip. This makes QMF multirow fetch feature act no differently than single row fetch. To change this behavior and boost multirow fetch performance, administrators can now increase the value of the DSQEC_BUFFER_SIZE global variable.
Let’s say we are running the QMF DISPLAY command to look at a similarly sized table. The table has 10000 rows. The raw buffer size of our table is 2800. So let’s use 2800 as a number to work out the math. As we can see in the table below, as the buffer size increases over 4K, the number of rows fetched per multirow fetch request increases, and this decreases the trips to the database. As the table shows, when the buffer size is increased from 4K to 12K, there is a 75% decrease in fetch trips, and when the buffer size is maximized to 256K, the fetch I/O decreases by almost 99%.
|
|
EXAMPLE #2: LARGE ROW SIZE
|
|
|
|
QMF Fetch Buffer size
|
Row width
|
Number of rows in result set
|
Number of rows fetched per database request
|
Number of trips to DB
|
Fetch I/O reduction %
|
|
#1
|
4K (4096)
|
2800
|
10,000
|
4096/2800= 1
|
10,000/1=10000
|
EXAMPLE 2 BASE Measurement
|
|
#2
|
DSQEC=12
==>12K (12288)
|
2800
|
10,000
|
12288/2800=4
|
10,000/4=2500
|
(10000-2500)/10000 = .75
75% decrease
|
|
#3
|
DSQEC=256
==>256K (262144)
|
2800
|
10,000
|
262144/2800=93
|
10,000/93=107
|
(10000-107)/10000 = .989
98.9% decrease
|
Appendix A: Determining the optimum fetch buffer size
The QMF sample REXX procedure, QMFSAMP_BUFFSIZE, can help you in determining an optimum DSQEC_BUFFER_SIZE setting for a given query. The sample QMF procedure can run an input query and give you time estimates for total result set fetch for both the default 4K DSQEC_BUFFER_SIZE value and an optimum value calculated specifically for the query given. During lab testing, time estimates were run using the multi row fetch feature (DSQSMRFI=YES).
The time estimates are based on REXX elapsed time to run the BOTTOM command for the input query using both the default buffer and optimum buffer sizes. The diagnostic information is put into a small table created for you by the sample procedure. Row width is calculated using the DSQAO_ROW_WIDTH global variable setting retrieved when running the query the first time with the default buffer size. Note that during lab testing, significant time savings were seen when starting QMF with program parameter DSQSPTYP set to 64BIT so that QMF spill activities were written to 64 bit storage rather than a file. DSQSPTYP=64BIT is a performance enhancement all its own and should be explored even if buffer sizes are not changed.
Alternately, you can choose not to receive time estimates and provide the tool an estimate result set size. In this case, the tool will only run the query one time optimized for fetch of one row only simply to be able to determine the row width for you.
Prereqs for running QMFSAMP_BUFFSIZE:
- QMFSAMP_BUFFSIZE must be run in a QMF/TSO 12 session. PI73765 must be applied. APAR PI73765 adds the global variable DSQAO_ROW_WIDTH to the QMF/TSO 12 product.
- Download the procedure as a text file and save it to your z/OS system in a sequential or partitioned data set. Import the sample procedure into your QMF/TSO 12 system.
- Example of importing the procedure from a sequential data set:
- IMPORT PROC QMFSAMP_BUFFSIZE FROM ‘tsoid.QMFSAMP.PROC’
- Example of importing the procedure from a partitioned data set:
- IMPORT PROC QMFSAMP_BUFFSIZE FROM ‘tsoid.QMFSAMP.PROC(BUFFSIZE)’
- Create a query called QMFSAMP_AMPQQ by running the following steps:
-
- From the QMF command line enter the command: SET PROFILE (LANGUAGE SQL
- From the QMF command line enter the command: RESET QUERY
- On the SQL Query panel, type in ‘&Q’ (without the single quotes) in the query contents area (not on the command line).
- SAVE QUERY AS QMFSAMP_AMPQQ
Screenshot of creating the QMFSAMP_AMPQQ query:
Example of running the QMFSAMP_BUFFSIZE procedure:
The complete QMF product library can be found in the IBM Knowledge Center. The link is provided here:
https://www.ibm.com/support/knowledgecenter/SS9UMF_12.1.0/home/qmfhome.html
Appendix B: How do I know the width of my result set?
QMF/TSO 12 APAR PI73765 is here to help with that answer. APAR PI73765 introduced a new state global variable, DSQAO_ROW_WIDTH. When a query is run or the DISPLAY tablename command is entered, the DSQAO_ROW_WIDTH will contain the value of the current data row width in the QMF data object. This value can be used to help you determine an optimum DSQEC_BUFFER_SIZE value.
If you prefer to do the math yourself, you can reference the chart in the QMF Installing and Managing Guide in section ‘Spilling report data to a file in TSO’
Appendix C: How does QMF fetch data with multirow fetch work
Prior to QMF 8, rows were retrieved from the data base a single row at a time. A result set of 6000 rows with a row width less than 4096 bytes meant 6000 trips to the data base.
In QMF 8, the multirow fetch capability was introduced. This capability makes use of DB2’s ‘rowset cursor’ or multirow fetch capability. Using multirow fetch allows QMF to retrieve ‘as many rows as possible’ from a single trip to the data base. ‘As many rows as possible’ is determined by the fetch buffer size and how many rows can fit into that fetch buffer size. Using the default 4096 byte (4K) buffer size as the limiting factor, a result set having a row size of 500, QMF would be able to retrieve 8 rows per trip to the data base. Our 6000 row table previously took 6000 trips to the data base in single row fetch. Now with multirow fetch, it takes only 750 trips to the data base.
QMF’s multirow fetch capability is enabled through the QMF program parameter DSQSMRFI (MR for short). When QMF is started with the DSQSMRFI=YES option, QMF will make use of its multirow fetch capability.
If you are not sure if multirow fetch has been implemented in your QMF environment, you can look at the QMF state global variable DSQAO_DSQSMRFI in your active QMF session. When this global is shown to be a value of ‘1’, you are running with the multirow fetch feature enabled. This support was implemented in QMF Version 8 (circa 2003) and we hope that everyone is already taking advantage of this easy to implement performance boost.
Please be aware that data containing LOB or XML data types will always use single row fetch, regardless of the DSQSMRFI program parameter setting.