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

DQF: Found Table

The CA Dataquery Found Table is used to store the results of a query. It can contain:

• Found sets - which consist of pointer information to the original data in the queried database when the FIND command is used
• Saved sets - which consist of pointer information to the original data in the queried database when the KEEP command is used
• Extract sets - which consist of the actual values for the columns specified in the query.
• Temporary sets used for offline print and network print - which consist of a copy of the request table, a copy of the found set, and optionally, the find statistics or the query text
• The deferred batch queue - which consists of entries for each deferred batch request

CA Dataquery writes the pointers to the data or the data that result from a request to the DQF after the request is processed. The DQF is read to report on the stored results.

Description

The logical row length of the DQF (Found Table) is 772 bytes long. The default block size is 4096 bytes. Each physical block holds five logical blocks, which are used as an intermediate area for holding the logical rows selected during a query execution.

The DQF (Found Table) is one physical database table, but because of its row structure it appears as multiple logical tables. Subsets of the DQF table are dynamically assigned to individual tasks when needed and later released back to the system. When row collection sets are saved with the KEEP and EXTRACT commands, entries that provide specific information about the sets are placed in the DQF table directory.

Preparing the DQF

To prepare the DQF:

1. Determine the space needed.

   The actual number of logical rows that can belong to a row collection at one time during selection varies per query. The number of RELATED clauses and SET statements and the length of a SORT key are factors.

   Since the logical row length is variable, you cannot always tell exactly how many can be contained in a physical block. For instructions on estimating the appropriate DQF allocation for your site, see DQF: Found Table.

2. Allocate the space.
3. Initialize the space using the DBUTLTY INIT Data Area function. See the CA Datacom/DB DBUTLTY Reference Guide for details.
4. Null load both the DQF and the DQS using the DBUTLTY LOAD Data Area function. See the CA Datacom/DB DBUTLTY Reference Guide for details.
5. Run the DQWFINIT utility. For information about executing DQWFINIT, see Initializing the DQE, DQF, and DQW (DQWFINIT).

SYNCHRONIZE. When you null load and initialize the DQF, you must also null load and initialize the DQE, DQR, and DQS to maintain synchronization. (Omit the DQR and DQE tables if the SQL option is not installed.) Failure to synchronize these tables can result in failure to reclaim DQF space.

Executing DQWFINIT completely reinitializes the table and destroys any KEEP or EXTRACT row collections currently on the table. Queued network print requests are all lost. Unprocessed deferred batch jobs are lost.

Calculating the Allocation for DQF

The following parameters of the System Option Table limit allocations of space from DQF.

FNDBLKS=

Sets a limit on the maximum space that can be allocated during execution of the FIND steps of a query. You can override the value for FNDBLKS= for any individual by updating the FNDBLKS= parameter on panel DQUM0. The override value may be more or less than FNDBLKS=. If it is larger, consider the extra blocks when allocating table space.

MXSETS=

Limits the total number of KEEP or EXTRACT row collections an individual user can own at one time.

XTRBLKS=

Sets a limit on the maximum space that can be assigned to an EXTRACT row collection by a user.

The actual number of logical rows that can belong to a row collection at one time during selection varies per query. The number of RELATED clauses and SET statements and the length of a SORT key are factors.

Since the logical row length is variable, you cannot always tell exactly how many can be contained in a physical block. The following is provided as an aid in approximating the values.

First determine a value for each of the following:

LREC-LIMIT

Number of logical rows to be allowed in a row collection.

AVG-TABLES

Average number of tables to be related in a query (average number of RELATED clauses plus 1). For SQL queries, the AVG-TABLES is always 1.

AVG-SETS

Average number of SET statements in a query. For SQL queries, the AVG-SETS is always 0.

AVG-SORT

Average combined length of columns or keys named in a DQL Mode SORT statement. For SQL queries, the AVG-SORT is always the sum of the lengths of the columns in the ORDER BY statement.

NUM-USERS

Number of CA Dataquery users.

For DQF calculations, you should count SQL queries too if your site has installed the SQL option. The average tables for an SQL query is always one (1). The average sets for an SQL query is always zero (0). The average sort is the sum of the lengths of the columns in the ORDER BY statement.

Perform the following calculations next.

Step 1:

Find the average logical row length:

(average logical row length) = 6 + (18 x AVG-TABLES)

Step 2:

Calculate the number of logical rows per logical block:

(logical rows per block) = 760/n

where n = average logical row length from Step 1.

Note: Round down the result of 760/n.

Step 3:

Determine the value for the DQOPTLST macro FNDBLKS= parameter:

(value of FNDBLKS= parameter) = LREC-LIMIT/y

where y = the logical rows per logical block from Step 2.

Step 4:

Find the total logical row length:

(total logical row length) = n + (17 x AVG-SETS) + AVG-SORT

where n = the average logical row length from Step 1.

Step 5:

Divide the result of Step 4 by the result of Step 1:

(total logical row length)/(average logical row length)

Step 6:

Find the number of logical blocks per user:

(number of blocks per user) = (z x FNDBLKS=) x 2

where z is the result of Step 5.

Step 7:

Find the minimum number of logical blocks required if KEEP and EXTRACT sets are not kept by doing this:

(number of blocks per user from Step 6) x NUM-USERS

Step 8:

Determine the minimum number of logical blocks required if KEEP or EXTRACT sets are allowed to be kept by doing this:

[(result of Step 7)/2] x w

where w = the value you gave the MXSETS= parameter in the DQOPTLST macro.

Step 9:

Find the total logical blocks:

(total logical blocks) = (result of Step 7) + (result of Step 8)

Step 10:

Calculate the number of physical blocks:

(number of physical blocks) = (result of Step 9)/5

Step 11:

Use the result of Step 10 to calculate the tracks or blocks for your device type.

• For Count Key Data devices:

  (number of tracks) = (outcome of Step 10)/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 10) 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 DQF.

Enlarging the DQF

To enlarge your DQF, perform the following steps:

1. After increasing the size of the DQF, INIT and null load it with DBUTLTY.
2. INIT and null load the DQS table, and if the SQL option is installed, INIT and null load the DQE and DQR tables.
3. Format the DQF table with the DQWFINIT utility.
4. If the SQL option is installed, format the DQE table with the DQWFINIT utility.

Recovering the DQF

If the DQF is lost due to catastrophe:

• Allocate the table.
• Initialize the DQF and DQS tables (and also the DQE and DQR tables if SQL is installed) using DBUTLTY.
• Null load the DQF and DQS (and DQE and DQR if SQL is installed) using DBUTLTY.
• Run the DQWFINIT utility to format the DQF (and DQE) tables.

Maintaining the DQF

CA Dataquery indicates that the DQF needs to be enlarged by the frequent display of the error message DQ026I at one or more CA Dataquery terminals. DQ026E specifies that work space was not available to complete the requested function.

In DBUTLTY Directory (CXX) reports, the DQW always shows 99 percent full after DQWFINIT has been run. A lack of available work space on the DQW is indicated by error messages. The percent full value on the report for the DQF and the DQE shows the maximum used at one time (a "high-water mark"). Space that has been used and freed shows on the report as being in use.

Backing Up the DQF

No backup of the DQF is required.

Restoring the DQF

The DQF cannot be restored from a backup. It must be reinitialized. See the information above on recovering the DQF.