CA DATA QUERY FIND TERMINATED BECAUSE: MAX WORK FILE BLOCKS EXCEEDED

Products

• Datacom
• DATACOM - AD

Issue/Introduction

DQW file is allocated at 12000 trks and has 12000 used.
do I need to expand file or can it be cleaned up

Current Dataquery FIND/SELECT Statistics. 
--------------------------------------------------------------------------DQEF0
DATAQUERY: FIND STATISTICS QUERY NAME: GAT-SNSL-HSTRY 
-------------------------------------------------------------------------------

NUMBER REQUESTED: ALL COMPLETION DATE: 07/11/2018 
NUMBER FOUND: 9589 COMPLETION TIME: 10:51:32 

FIND TERMINATED BECAUSE: MAX WORK FILE BLOCKS EXCEEDED 

OVERALL OPTIMIZATION SEARCH 
------- ------------ ------ 
ELAPSED TIME (SECONDS): 5 0 5 
I/O EVENTS: 3768 0 3768 
SELFR TOTAL: 19182 3 19179 
SELNR TOTAL: 9588 9588 
TOTAL BYTES: 1164

Cause

  
FIND TERMINATED BECAUSE: MAX WORK FILE BLOCKS EXCEEDED                       
                                                                             
Explanation: Your batch query required more find blocks than allowed by the current DQW file allocation.                                                 
                                                                             
User Response: Increase the size of the DQW table or decrease the number of  partitions in the current DQW table allocation.         


So,the DQW file is either
- too small for the request
or
- the request is generating too many rows (output too large).                     

Environment

z/os, CA Datacom/DB 15.1 

Resolution

Steps to enlarge the DQW file:

1- Use a DBOC CLOSE=003 command to close the URT in CICS.

2- Run a DBUTLTY step with SYSIN:
ACCESS  DBID=003,STATUS=OFF,USERS=WAIT
COMM    DBID=003,OPTION=CLOSE

3- Enlarge DQW file and execute DBUTLTY step to INIT and LOAD FORMAT=NONE the DQW file using SYSIN:
INIT AREA=DQW,DBID=003             
LOAD AREA=DQW,DBID=003,FORMAT=NONE 

4-Run a DBUTLTY step with SYSIN: 
ACCESS DBID=003,STATUS=WRITE

5- Run DQWFINIT  see *.CABDSAMP(DQSIM01) extracted below
//*      EXECUTE DQWFINIT TO INIT DATAQUERY DQW DATASET 
//*                                                     
//FINIT2  EXEC PGM=DQWFINIT,REGION=4M                   
//STEPLIB  DD ....                      
//SYSPRINT DD  SYSOUT=*                           
//SYSOUT   DD  SYSOUT=*                           
//SYSPUNCH DD  DUMMY                                    
//DDSNAP   DD  SYSOUT=*                           
//SNAPER   DD  SYSOUT=*                           
//BATCHOUT DD  SYSOUT=*                           
//SYSUDUMP DD  SYSOUT=*                           
//SYSIN  DD  * 
DQW 03                ==> Please see note below
/*                    

6 - Issue a DBOC OPEN=003 command to open the URT  in CICS

Note: to know how many partitions you currently have:
Execute program DQWFINIT with input card DQW ??? . 
DQW in positions 1-3 and ??? in positions 5-7 .
(Default is 3).

see knowledge document 000067739 for more info about number of DQW partitions:
https://ca--c.na60.visual.force.com/apex/CustomKnowledgeEdit?articleId=ka00c000000yEAMAA2

Note that DQW always show 99% full.