Introduction › Deleting an Index Entry from an Open Table

Deleting an Index Entry from an Open Table

CA Datacom Version 15.0 supports 24X7 operations by allowing an index entry (key definition) to be deleted from an open CA Datacom/DB table. A CA Datacom Datadictionary attribute, KEY-USAGE, can be used to deny access in 24X7 real time to the key definition to be deleted. Index entries can also be added while a table is open. A designation of open for a table means that the table is open for use by any number of user applications. The deletion or addition of a key to an open table is designed to prevent any effect upon the operation of any executing applications that are using the table.

The alternate process for deleting a key definition requires an outage and involves the following steps:

1. Close the database containing the table with the key to be deleted.
2. Delete the key definition from CA Datacom Datadictionary.
3. Catalog the database.
4. Execute DBUTLTY with function RETIX, including the keyword MINIMAL=YES.

Before deleting a key definition by any method, make certain that no existing Record-At-A-Time (RAAT) program requires using the key definition that is to be deleted. Ensuring that no RAAT program is using of the key is not always an easy task, but it is an important step. You can query the Dynamic System Table DIR_KEY field USES_RAAT, but if the count is just under 4 billion, the number has reached its maximum stored value and is not useful in determining key usage. The "uses" counts are reset during a DBUTLTY function LOAD of a data area, a RETIX function execution of a data area (without KEYNAME=), and during a CXXMAINT function execution with the following options specified:

CXXMAINT OPTION=ALTER,OPTION2=RESET_KEY_USES,DBID=n

After verifying that no RAAT program usage of the key exists, you can test the deletion of the key definition by using the CA Datacom Datadictionary KEY-USAGE attribute. For details about the KEY_USAGE attribute, see the CA Datacom Datadictionary Attribute Reference Guide.

You can set the CA Datacom Datadictionary KEY-USAGE attribute to N (no access). With KEY-USAGE N, neither single record-at-a-time (RAAT) access programs nor CBS SAAT (Compound Boolean Selection Set-At-A-Time) programs nor SQL programs can use the key. RAAT programs generate a return code 03 to tell you if an invalid key name is detected on a new request. In SAAT or SQL programs, a missing key is ignored on new requests, but a SAAT or SQL program that currently uses the missing key can run noticeably slower than before the key had its KEY-USAGE set to N.

You can consider setting an N (none) as the KEY-USAGE to be a logical (test) deletion that allows a decision-making time period before the key is physically deleted. A key used in a production environment that has existed for decades or even years could require an extended decision-making time period to ensure it is not needed by rarely executed programs. In a test system, however, the status might only need to be tested a short period of time. CA Datacom cannot, of course, help make site-specific determinations for you. CA Datacom honors your request and physically deletes a key at any time the KEY-USAGE is currently set to N.

During the logical (test) deletion, if you discover the key undergoing the test deletion needs to be restored, so that a program that uses that key can execute, the key can be quickly restored and made available by setting KEY-USAGE to allow the key to be used by RAAT, either with or without CBS and SQL.

After a test deletion using KEY-USAGE set to N has been in place a sufficient time, the full deletion process can start. The process involves the following steps:

1. As previously discussed, KEY-USAGE should already be set to N (none). If, however, KEY-USAGE has not yet been set to N, set KEY-USAGE to N now.

   Note: There are consequences to preventing the usage of a key that is being used by a currently executing program.

2. To drive the 24x7 execution, use batch CA Datacom Datadictionary with a –DEL header transaction and APPLYCXX to delete the key definition.
3. CA Datacom/DB performs validation and accepts the request as ‘committed’.
4. CA Datacom Datadictionary continues with a full delete of the key definition.
5. CA Datacom/DB schedules the actual delete process in parallel with access to the same table and database by user applications.
6. After the index area is fully updated to remove all the individual index values, the key definition is removed from the CXX.

The execution of the deletion or addition occurs in a MUF System Task area with a job name of ***DBKEY. The ***DBKEY task area is dedicated to 24x7 asynchronous index definition additions and deletions.

Note: ***DBKEY is reported in the JOBNAME of the DBUTLTY COMM STATUS report.

Restrictions require that the MUF reject requests for the deletion of a key definition for the following reasons:

• If two or MUF’s are enabled in a MUFPLEX (a Shadow MUF is not enabled and so can be present). The database outage option is available to users who require the simultaneous execution of multiple MUF instances.
• If the database containing the key definition was defined in the MUF startup option ACCESS with the value NOOPT.

  Note: A best practice is to always allow the MUF default of ACCESS OPTIMIZE.

• If the database containing the key definition is currently defined as VIRTUAL.
• For a Master Key or a Native Sequence key.
• For a key that is part of an SQL constraint.
• For a key in a table that is loaded with URI=NO.

  Note: A best practice is to execute with URI.

• For a key in an area that was last loaded with SORT=0.

  Note: A best practice is to use sorted LOADs.

Individual keys are added to a list of work to be done when a CA Datacom Datadictionary1000 APPLYCXX batch transaction request is processed by CA Datacom/DB.

When an entry is added to the list of work to be done, the system task is scheduled. At the start of the asynchronous processing for an individual key, the system task opens a URT for the table that is involved. The URT is subject to normal ACCESS rules. After the table is successfully opened, the index work associated with the deletion or addition of the key is processed. When the index work is complete, the key is deleted or added from the CXX and the URT is closed. The system task then processes the next key entry in the list, if there are any.

A module named DBKEYPR processes the asynchronous key maintenance. DBKEYPR is eligible for NEWCOPY

If a deletion or addition request is executing, the deletion or addition of the index area entries is subject to a REQABORT, if the user has a reason to stop the process. An executing deletion or addition request is also interrupted if a MUF EOJ is issued. If a deletion or addition execution is stopped, the deletion or addition process for the key definition is restored to the list when the first URT for the table is opened for non DBUTLTY processing in MUF.

After the delete is confirmed, the key cannot be used for any CA Datacom command. Maintenance to data rows does not have the index updated to reflect changes.

In processing the key definition item, the system task has messages that reflect the start and end of each definition and other useful information.

If a database is closed and is subject to a LOAD of the data area that contains a key set to be deleted, or if a RETIX function of the area or database is done, the key is processed as part of the LOAD or RETIX. Also, if the index is subject to an INIT, the key definition is also deleted.

The DBUTLTY function REPORT with AREA=CXX and not TYPE=A/I/K/P is considered the full CXX report. In the key area under status that formerly showed LD for index loaded and NL for index not loaded, can now have NL DELING to indicate that the index is not loaded and is being deleted.

A key set to this 24x7 form of deletion cannot be accessed by user programs and cannot be subject to participation in maintenance requests. That is, the key name and key ID can be used for a key definition in CA Datacom Datadictionary and part of a database catalog, but neither the key name nor key ID can be used in a key catalog (the 24x7 form of key addition) until the asynchronous delete completes.