Dataquery DQBATCH Timing Considerations

Products

• Datacom
• DATACOM - AD

Issue/Introduction

This article discusses batch Dataquery for Datacom (PGM=DQBATCH) considerations if encountering DATAQUERY WAITING FOR RESOURCES or

DATAQUERY RESOURCES NOT AVAILABLE messages.

Environment

z/OS
z/VSE

Dataquery

Resolution

The CA Datacom Core - 15.1 section "DQOPTLST Parameters" defines RTIMDQW as follows:

• RTIMDQW=
  Specifies the number of seconds batch Dataquery is to wait before reusing a partition of the Work Table (DQW) which is marked as being "in use." Partitions may be left marked "in use" after a system failure or batch Dataquery abend. When Dataquery is unable to allocate a partition, the message "Dataquery WAITING FOR RESOURCES" appears.
  
  • Valid Entries:
    60 -- 32000
  • Default Value:
    7200

In general, the large default value (7200 which is 2 hours) for RTIMDQW was suggested to avoid interfering with a currently running query.
It is recommended that DQOPTLST RTIMDQW is set to the projected length of the longest running query that may exceed 2 hours.

It may also be that RESOURCES NOT AVAILABLE is encountered when there are no Dataquery queries running at the moment.
Here is a possible scenario...

Let's say there is a two partition DQW table.
Partition 1 has a query that is currently running, and so the partition is marked as "in use." Also, the query in Partition 1 has been running longer than the time specified in RTIMDQW.
Partition 2 is marked "in use" from another query that had been running previously but was just recently cancelled.
A third query is submitted and because both partitions are now marked "in use," Dataquery looks to see if any have exceeded RTIMDQW.
Under these conditions, Dataquery will attempt to run the third query in Partition 1.
Since there is already a query running in that partition, DATAQUERY WAITING FOR RESOURCES or DATAQUERY RESOURCES NOT AVAILABLE results.

RESOURCES NOT AVAILABLE may also be encountered when there are no Dataquery queries running at the moment since some cancellation or abend.
Dataquery as designed does not have the ability to distinguish between a DQW  "in use" partition that is still processing a currently running query or one that has been cancelled or abended.

In either case, reformatting the DQW table becomes necessary.

To reformat the DQW table, use the DQWFINIT batch utility. DQWFINIT formats every record in the DQW table and can be time consuming. The larger the DQW is, the longer DQWFINIT takes.
So, it may be good to run the DQWFINIT from another Multi-user environment then take and retain a DBUTLTY BACKUP of the reformatted DQW.
That BACKUP could then be used to INIT and LOAD for a quicker rebuild of the DQW since the DBUTLTY LOAD would run much faster than reformatting the DQW with program DQWFINIT.

Either of these methods require closing Dataquery Base 003 in that environment until the process is complete.

Note, the DQW is a scratch file that only contains critical data when a batch query is actually executing.

Additional Information

To determine the RTIMDQW value that DQOPTLST was generated with, run the DBCGSPR utility to decode the DQSYSTBL load module.
Refer to Generating Source Members from CUSLIB Content

To determine how many DQW partitions have been formatted before, run DQWFINIT using SYSIN card "DQW ???". 
//STEP  EXEC PGM=DQWFINIT,REGION=4M 
...
//SYSIN DD * 
DQW ??? 
/* 
The report will show :
NUMBER OF DQW PARTITIONS = nn

To free the DQW partitions from "in use" or to increase the DQW, reformat DQW.

First, in CICS, close any Dataquery URT that is open to that Multi-user.
Then, run DBUTLTY to close and reinitialize Dataquery Base 003 Area DQW followed by DQWFINIT to set up for the number of partitions:
//STEP1 EXEC PGM=DBUTLTY,REGION=6M
...
//SYSIN     DD *
ACCESS STATUS=OFF,DBID=003,USERS=WAIT
COMM OPTION=CLOSE,DBID=003
INIT AREA=DQW,DBID=003
LOAD AREA=DQW,DBID=003,FORMAT=NONE
ACCESS STATUS=WRITE,DBID=003
/*
//STEP2 EXEC PGM=DQWFINIT,REGION=4M
...
//SYSIN    DD *        
DQW nn
/*

Or, run DBUTLTY INIT and LOAD of the DQW BACKUP taken of the last DQWFINIT.

For more, see DQWFI Initializing the DQE, DQF, and DQW (DQWFINIT)