CA Datacom DDUPDATE error DDPST10001

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

Running DDUPDATE to update datadictionary getting error message

DDPST10001 SQL REQUIRES LOGGING AND RECOVERY

This can happen running DDUPDATE with member SDCTRNS or when upgrading from release 12.0 to 14.0 in job BDUPG07 or to 15.0/15.1 in job BDUPG06.

Environment

Release:
Component: DB

Cause

For any table that has the SQL-INTENT = Y  attribute defined, the LOGGING and RECOVERY attributes must also  be Y. 

Resolution

To correct the table getting the error, run DDUPDATE with:

-UPD TABLE,table_name(PROD,DD,PRIV)
1500 LOGGING,Y
1500 RECOVERY,Y
​-END

There may be more tables that need to be corrected than the one that got the error.
To find out if other table entities have the same problem run the following DQL query via DQRY in CICS (or via DQBATCH ):

FIND ALL AGGREGATE WITH SQL-INTENT = 'Y' AND STATUS = 'P' AND   
(LOGGING = 'N' OR RECOVERY = 'N' )   
PRINT ENTITY-NAME   
   ENTITY-VER   
   DATABASE-ID   
   LOGGING   
   RECOVERY 
 

The ENTITY-NAME is the name of the table which needs to be changed . 
Since for any table that has SQL-INTENT = Y the LOGGING and RECOVERY attributes must also be Y, the query can be modified to do a mass update:

UPDATE ALL AGGREGATE WITH SQL-INTENT = 'Y' AND STATUS = 'P'   
SET LOGGING = 'Y'   
SET RECOVERY = 'Y'

Run these two requests once for STATUS = 'P' and then also for STATUS = 'T' 

CA Datadictionary tables can only be updated via Dataquery online and in DQL mode, not SQL and the proper security authorizations are needed in order to update the tables in database DATA-DICT, DBID 2.
If CA Dataquery is not available, the update needs to be performed for each table via DDUPDATE and the 1500 transactions mentioned above.

After this recatalog the user database structure so that the CXX is in sync with the table definition in Datadictionary.

Then rerun the job from the DDUPDATE step that was getting the error.