Preparing and Maintaining the CA Dataquery System Tables › DQE: SQL Found Table

DQE: SQL Found Table

The DQE provides space for storing the rows retrieved by SQL queries. Pointers to these rows are kept in a found set on the DQF table like a DQL query.

Description

The default block size for the DQE is 4096 (record size 4082). This means the maximum row length for a row retrieved by an SQL query is 4030, because of the header, pointer, and length fields used in the block by CA Dataquery.

Preparing the DQE

To prepare the DQE:

1. Allocate space (see instructions for calculating size, below).
2. Initialize space using DBUTLTY.
3. Null load using DBUTLTY.
4. Run DQWFINIT with a control statement to format DQE.

Note: At installation, the DQE block size for DQE defaults to 4096. The block size of the DQE can be changed using standard procedures in CA Datacom Datadictionary for changing the definition of a table and cataloging. See the CA Datacom Datadictionary documentation or the CA Datacom Datadictionary Administrator for assistance. After changing the definition of the DQE and cataloging it, it must be reallocated and reinitialized as above. Also, the DQESIZE parameter on the System Option Table must be changed to reflect the new record size (block size minus 14) and reassembled.

Calculating the Allocation for DQE

For SQL queries, determine:

• AVG-ROW-LEN selected = A
• AVG-ROW-COUNT = R
• USER-COUNT = U

Step 1:

Calculate the number of rows per block.

4096/A (round down)

Step 2:

Calculate the number of blocks per user.

R/(result of Step 1)

Step 3:

Calculate the total blocks.

(result of Step 2) x U

Step 4:

Multiply the result of step 3 by two.

(result of Step 3) x 2

Step 5:

This gives the number of physical 4096-byte blocks. Use the result of Step 4 to calculate the tracks or blocks for your device type.

• For Count Key Data devices:

  (number of tracks) = (outcome of Step 4)/b

  where b = number of 4096-byte rows that will fit on a track on the device used to hold the table.

• For FBA devices:

  (number of FBA blocks) = (outcome of Step 4) x 8

  Note: You multiply by 8 because it is the minimum number of 512-byte blocks needed to accommodate the 4096-byte row size of the DQE.

Maintaining the DQE

No backup of the DQE is required since no sets are kept or extracted in SQL Mode. CA Dataquery uses the DQE as work space during SQL Mode query execution.

SYNCHRONIZE. When you null load and initialize the DQE, you must also null load and initialize the DQF, DQR, and DQS to maintain synchronization. Failure to synchronize these tables can result in failure to reclaim DQE space.

Recovering the DQE

If the DQE is lost due to catastrophe:

• Allocate the table.
• Initialize the table using DBUTLTY.
• Null load the table using DBUTLTY.
• Run the DQWFINIT utility to format the DQE.