ere is a proces to speed up QMF for Workstation creating AOTs.
QMF for TSO supplies a Db2 Stored Procedure called Q.DSQQMFSP. This Stored Procedure can be called from an external client. QMF for Workstation uses a synonym called RUNTSO to invoke the stored procedure. This stored procedure will start QMF for TSO and run a specified QMF object.
The Q.DSQQMFSP takes four parameters: The QMF Object to run, the log level, the log file location, and the language to run QMF for TSO in. QMF for Workstation supplies a Procedure command synonym for the SQL CALL Q.DSQQMFSP statement. It is called RUNTSO
Here is an example:
RUNTSO TWSHAWN.AOTP1 (L2DESTINATION=NONE LANGUAGE=E TRACELEVEL=NONE
The instructions for setting up Q.DSQQMFSP are in the document Installing and Managing QMF for TSO
and CICS IBM GC27-8877 in the section called
Installing the QMF stored procedure interface (TSO only)
In the PROCLIB for the WLM is a line that specifies where to find the DSQSCMDE member for the db2 subsystem you are connecting to.
In this member set the value of DSQSMRFI = "YES"
This will activate Multirow Insert and vastly speed up the process of creating an AOT.
Running this PROC in QMF for Workstation
RUNTSO TWSHAWN.AOTP1 (L2DESTINATION=NONE LANGUAGE=E TRACELEVEL=NONE
Causes QMF for TSO to run this PROC AOTP1
SET GLOBAL (DSQEC_SAV_ALLOWED=2 --force the SAVE DATA to an AOT
SET CONFIRM=NO
RUN QUERY TWSHAWN.AOT100KQ --retrieve the data
BOTTOM --force retrieval of all the data
RUN QUERY TWSHAWN.TTESTQ(CONFIRM=NO –-record the start time for comparison
SAVE DATA AS AOT1C9 (ACCELERATOR=IDAAS04 –save data to the accelerator
RUN QUERY TWSHAWN.TTESTQ(CONFIRM=NO –-record the end time for comparison
This query was run via the RUNTSO command with SQL altered each time to pull
10,000 rows 5 seconds
25,000 rows 9.5 seconds
50,000 rows 17 seconds
100,000 rows 34.3 seconds
200,000 rows 74 seconds
400,000 rows 168 seconds < 3 minutes
800,000 rows 294 seconds < 5 minutes
1,600,000 rows 625 seconds > just over 10 minutes.
The results are tabulated and chart below.
Note the time is the wall clock time to save the data as an AOT. Also, the record length used is 100 bytes. For longer record lengths set the DSQEC_BUFFER_SIZE global as high as 256 (KB)
Here is a chart of the number of records vs time
#QMF
#Db2Toolsforz/OS#IDAA