IBM Query Management Facility (QMF)

Speeding up the creation of AOT in IDAA using QMF for Workstation

By shawn sullivan posted Tue June 29, 2021 12:18 PM

  

Here 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

 



0 comments
5 views

Permalink