How to maintain the CA Datacom/DB Dataquery System Tables (Table ID DQ*).

Document ID : KB000027864
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction:

This article explains how to maintain the CA Datacom/DB Dataquery system tables.

Instructions:

Maintaining the SQL Found Table (DQE), Found Table (DQF), SQL Recovery Table (DQR), and Spool Table (DQS)

The SQL Found Table (DQE) and Found Table (DQF) are work files used to store information about rows retrieved by queries. They should not be loaded from a backup, but should be null loaded, reinitialized and reformatted using the Found Table Maintenance Utility DQWFINIT as noted below.

Information on DQWFINIT can be found in the CA Dataquery for CA Datacom Administration Guide (see below for links).

The Spool Table (DQS) is used to store and control the hardcopy report output of a query that has been routed to an online network printer. The contents of this table are maintained by the CA Dataquery Printer Control Administrative function. It is not necessary to back up and restore this table, and reinitializing the table prepares it for use.

The SQL Recovery Table (DQR) is used to recover the SQL found sets in the SQL Found Table (DQE) table, reinitializing the table restores the SQL Recovery Table. Omit this table if CA Datacom SQL is not installed.

Note: When reinitializing the Found Table (DQF), you must also null load and reinitialize the SQL Found Table (DQE), SQL Recovery Table (DQR), and Spool Table (DQS) to maintain synchronization.

Executing the DQWFINIT completely reinitializes the DQF table and destroys any deferred KEEP or EXTRACT sets currently on the table. Queued network print requests and deferred batch jobs are also lost from the Spool Table (DQS).

If either the SQL Found Table (DQE) or the Found Table (DQF) are lost, you must:

  1. Allocate new files for the data areas. (Skip the SQL Found Table (DQE) if CA Datacom SQL is not installed.)
  2. Initialize the data areas using the CA Datacom/DB DBUTLTY program with the INIT Data Area function. Information on the DBUTLTY program can be found in the CA Datacom/DB DBUTLTY Reference Guide.
  3. Null load the tables using the DBUTLTY LOAD Data Area function (using FORMAT=NONE).
  4. Reformat both tables using the CA Dataquery DQWFINIT program. Information on DQWFINIT can be found in the CA Dataquery for CA Datacom Administration Guide.

If either the SQL Recovery Table (DQR) or the Spool Table (DQS) are lost, you must:

  1. Allocate new files for the data areas (Skip the SQL Recovery Table (DQR) if CA Datacom SQL is not installed.)
  2. Initialize the data areas using the CA Datacom/DB DBUTLTY program with the INIT Data Area function.
  3. Null load the tables using the DBUTLTY LOAD Data Area function (using FORMAT=NONE).

Maintaining the DQM Area

The DQM area contains four tables:

  • Literal Table (DQM) -Used to store literal values of keywords or phrases
  • Panel Table (DQP) - Used to store panel images
  • Vocabulary Table (DQV) - Used to store Language and Command keywords
  • Report Facility Error Message Table (DQZ) - Used to store Report Facility error messages

The DQM area should be backed up using the DBUTLTY program when the software is installed, and then later if it is changed. If any of these tables are lost, check the DQM area section in the CA Dataquery for CA Datacom Administration Guide for details on recovering these tables.

Maintaining the Query Library Table (DQQ)

The Query Library Table (DQQ) is used to store query text members, JCL members, CA Dataquery procedures, terms, conditions, restrictions, dialogs, and Report Facility text source.

The contents of this table are maintained by the CA Dataquery Administrator using the LIBRARY option of the CA Dataquery Administrative menu or the CA Dataquery batch utility DQLIBRMT.

Information on DQLIBRMT can be found in the CA Dataquery for CA Datacom Administration Guide. This utility is used to report, add, delete, backup, and restore members to maintain the content of the table, but it is not intended for complete backups. Use the DBUTLTY program for standard table maintenance.

The Query Library Table (DQQ) should be backed up frequent (we recommend that it be done daily). To restore a backup, use the DBUTLTY LOAD Data Area function.

If the Query Library Table is lost, you must:

  1. Allocate a new file for the DQQ data area
  2. Initialize the data area using the CA Datacom/DB DBUTLTY program with the INIT Data Area function
  3. Reload the table from the most recent backup using the DBUTLTY LOAD Data Area function

Maintaining the Report Facility Work Table (DQT)

The Report Facility Work Table (DQT) is used to store and control Report Facility work records and compiled reports. Since this table is used as a temporary work file for the Report Facility and records are added and deleted as needed when producing reports using the Report Facility, it is advisable to do a DBUTLTY utility BACKUP and LOAD to reorganize the table. To restore a backup use the DBUTLTY LOAD Data Area function.

If the Report Facility Work Table (DQT) is lost, you must:

  1. Allocate a new file for the DQT data area
  2. Initialize the data area using the CA Datacom/DB DBUTLTY program with the INIT Data Area function
  3. Reload the table from the most recent backup using the DBUTLTY LOAD Data Area function

Maintaining the User Table (DQU)

The CA Dataquery User Table (DQU) is used to store the definitions of valid CA Dataquery users. It contains user attributes such as name, password, language, system options and function options. The information validates a user signon and verifies function authorizations. This table is maintained through the USER option of the CA Dataquery Administrative menu or the CA Dataquery batch utility DQUSERMT.

Information on DQUSERMT can be found in the CA Dataquery for CA Datacom Administration Guide.

This table should be backed up as changes occur, using the DBUTLTY BACKUP Data Area function. It can be restored using the DBUTLTY LOAD Data Area function.

If you reload the CA Dataquery User Table (DQU), you should then null load, initialize, and format the Found Table (DQF), Spool Table (DQS), SQL Found Table (DQE), and SQL Recovery Table (DQR) as noted above.

If the CA Dataquery User Table (DQU) is lost, you must:

  1. Allocate a new file for the DQU data area
  2. Initialize the data area using the CA Datacom/DB DBUTLTY program with the INIT Data Area function
  3. Reload the table from the most recent backup using the DBUTLTY LOAD Data Area function, and then null load, initialize, and format the Found Table (DQF), Spool Table (DQS), SQL Found Table (DQE), and SQL Recovery Table (DQR) as noted above. 

Maintaining the Batch Work Table (DQW)

The Batch Work Table (DQW) is the batch equivalent of the Found Table (DQF). The Batch Work Table stores and manages found sets that result from batch queries. If this table is large, reinitializing it may take significant resources. Consider taking a backup of this area immediately after formatting it with the DQWFINIT utility, and loading this backup instead of reinitializing.

If the Batch Work Table (DQW) is lost, you must:

  1. Allocate a new file for the DQW data area.
  2. Initialize the data area using the CA Datacom/DB DBUTLTY program with the INIT Data Area function.
  3. Null load the table using the DBUTLTY LOAD Data Area function (using FORMAT=NONE).
  4. Reformat the table using the CA Dataquery DQWFINIT program. Information on DQWFINIT can be found in the CA Dataquery for CA Datacom Administration Guide.

Additional Information:

For more information on maintaining the CA Dataquery tables, refer to the following guides:

CA Dataquery for CA Datacom Version 14.02 Administration Guide

CA Dataquery for CA Datacom Version 15.0 Administration Guide

For more information about the DBUTLTY functions discussed above, refer to the following guides:

CA Datacom/DB Version 14.02 DBUTLTY Reference Guide 

CA Datacom/DB Version 15.0 DBUTLTY Reference Guide 

As always, please contact CA Technologies support for CA Datacom if you have further questions.