DQRY DQL errors DQ001E (DQPPGPR) with DQ358E

[Steve Roy]

We are trying to query a table for which we have just now extended the row length to over 9,000.  I have reassembled DQOPTLST with a change to BUFSIZE to be equal to the new row length.  This test environment was restarted after the assembly and the table change (with a data load).

What else might I need to do?

[Don Johnson]

Hi Steve, as you have seen, there are some interdependencies between
different work tables and values in the Options list.

First, when looking at Dataquery Limitations, and the Maximums for both SQL
and DQL
<https: techdocs.broadcom.com us en ca-mainframe-software database-management ca-datacom 15-1 reference ca-dataquery-reference limitations-in-ca-dataquery.html#:~:text=Maximums%20for%20Both%20Modes>,
you can find:

The maximum row size of a table that can be accessed by Dataquery is
determined by the BUFSIZE parameter in the System Option Table.


Then, when looking at the *BUFSIZE *description in the DQOPTLST Parameters
<https: techdocs.broadcom.com us en ca-mainframe-software database-management ca-datacom 15-1 administrating administrating-ca-dataquery dqoptlst-parameters.html>,
it says:

Code BUFSIZE as the length of the largest user Datacom/DB row to be
accessed by Dataquery.
The value should not be larger than what is coded in DATALN in the
Datacom/DB Master List.
Also, the value should not be smaller than what is coded for DQESIZE=.

If this is the longest rowsize of any table, then you should code BUFSIZE
to this length.

Then, look at your Datacom/DB Master List, and ensure that the DATALN value
is at least or larger than this length.
Next, look further at the Option List value for DQESIZE, and ensure
that DQESIZE is equal or smaller than this BUFSIZE value.

In looking at *DQESIZE*, you may need to redefine this and re-initialize
the table. Here is the description:

Specifies the record size of the DQE table (block size minus 14). At
installation, the block size is set to 4096.
A record size 4082 means that the largest total column length for data is
4030 (record size minus 52).
If this is not sufficient for SQL retrieval of one row, redefine the DQE
table through Datacom Datadictionary,
reallocate and reinitiate the DQE table, and change this parameter to
reflect the new size.

Therefore, if you will be using SQL to retrieve data from this table, you
may need to adjust this value.

I hope this is helpful for you. If you continue to have a problem, please
open a support case and attach the failed job output, the source for your
DQOPTLST, and a full CXX report of DB 3.

Don Johnson

Sr. Principal Support Engineer | Mainframe Software
Support File Transfer Lead

CADRE/Datacom Community Champion

Broadcom
office: 609.493.5891 | mobile: [phone]
[e-mail] | broadcom.com

[Steve Roy]

Thanks, Don.

Before I create a case, just to make sure I'm not missing something:

Previous longest row 4647 and BUFSIZE was set to 8600.

New longest row 9179 and newly assembled/linked BUFSIZE also 9179.

DQESIZE unchanged at 4090.

DATALN unchanged at 32767.

This is my DQ batch output.  The length of the key field is 16 bytes.

FIND 9    TLN          PRINT TLN KEY-TLN

EXECUTE *                                                                
 DQ471I - ROWS FOUND: 000000009                                          
 DQ001E - AN INTERNAL ERROR HAS BEEN DETECTED WITHIN DATAQUERY (DQPPGPR )

If I need a case, I will open tomorrow.  End of my day approaching.

[Owen Williams]

Hi Steve,

As Don says, the sizes of the Dataquery work files, and some of the DQOPTLST/DQSYSTBL parameters are inextricably linked.

Several years ago we re-blocked all of the Dataquery work files to half-track blocking, to provide significant performance benefits and to increase several of the row length and other size limits. More recently we have converted to 24K block sizes, and VSAM Linear datasets for even greater efficiency. Here's what we have now for DQOPTLST/DQSYSTBL...

         DQOPTLST OPSYS=OS,MXREQ=100,MXTLR=10,
               BUFSIZE=24562,DQESIZE=24562,   
               EPAGES=10,ETMSO=30,LINPRTP=55, 
               DATEFMT=ISO,TIMEFMT=ISO,       
               SECINF=YES,SQLPRTY=3,URTPRTY=3,
               QRYGRPS=NO,QPAGES=4,           
               DDDBID=002,DQDBID=003          
         END    

When changing DQESIZE you need to increase the length of one of the fields in the DQE table, to be 50 bytes less than your chosen block size...             

-UPD AREA,DATAQUERY-DQE(PROD,,PRIV)
3002                           24576
-END
-UPD DATABASE,DATAQUERY(PROD,DD,PRIV)
1000 RESTORE,T001
-END
-UPD FIELD,DATAQUERY-DQE.DQEF0037-$END(T001,,PRIV)
4012         24526
-END

-CPY DATABASE,DATAQUERY(T001,DD,PRIV),PROD
-END
-DEL DATABASE,DATAQUERY(T001,DD,PRIV)
-END

-CXX CATALOG,DATABASE,DATAQUERY(PROD,DD,PRIV)
-END
-UPD DATABASE,DATAQUERY(PROD,DD,PRIV) 
1000 ENABLE
-END

You need to convert all of the Dataquery work files (DQE,DQF,DQR,DQS,DQW) at the same time, and obviously reassemble/link the DQOPTLST/DQSYSTBL to match. (DQE is the only one that needs the adjustment to the field length, the others just need the new block size). Once done you will need to run DQWFINIT for each of the work files.
You will then enjoy life with more efficient Dataquery execution, and increased limits.

Let me know if you have any further questions.

Cheers,

Owen

[Steve Roy]

Thanks, Owen. 

BUT -

This looks a bit overwhelming, especially considering that the table row extension that brought this up is for a 'pressing' business change. We are not using VSAM datasets for DATACOM.

I guess my question is:

What is the minimum that I need to change so that DQL will work with our updated table layout?

All of the areas in DBID 3 are blocked at 4096. 

The application table in question is at 27998. 

DQE record length is 4082.

My application table is changing from row length 3425 to 9179.

Our longest application table row length has been 4647 for a number of years.

Our DQOPTLST, has been as below for years.  

DQOPTLST OPSYS=OS,MXREQ=100,MXTLR=10,
      BUFSIZE=8600,                  
      EPAGES=10,                     
      QRYGRPS=NO,QPAGES=4,           
      DDDBID=002,DQDBID=003,         
      DQESIZE=4090,                  
      CADATE='MON DD, YEAR',         
      MAXIO=999999,                  
      MFTIME=0600,                   
      RTIMDQE=60,                    
      RTIMDQW=60,                    
      SECINF=YES,                    
      SRCHLIM=999999,                
      XTRSETL=16                     
END