Correcting invalid CA Datacom/DB Encryption fields

Document ID : KB000105692
Last Modified Date : 06/07/2018
Show Technical Document Details
Introduction:
When using DDBTGLM to create Datadictionary definitions for transferring to another MUF, it is possible to carry Encryption values from the old system to the new system that are not valid, as DDBTGLM does not validate the definition of the entity. Once the entity is copied to Production status and catalogued to the CXX, a job to initialize or load the related data area could fail with a RC 13(11) and this message:
DB13001E - UNEXPECTED RETURN CODE 13 (11) UNEXPECTED CONDITION OTHER 

Correcting these bad values is fairly easy, and the methods in this article will show how to find all the bad values and how to correct them.
Instructions:
To find invalid values for the Encryption-Type and Encryption-Method, you can use either a CXX report, a Datadictionary report, or an SQL report.

For the CXX report, run a standard DBUTLTY job with this input:
//SYSIN    DD  *
 REPORT AREA=CXX
/*
In the output, find the string "ENCRYPTION" and note the value. If the Encryption Type is not a B, or the Encryption Method is not A, B, or C, then this is a bad setting.


For the Datadictionary report, run program DDUTILTY or DDUPDATE with this input:
//SYSIN        DD *                          
+USR DATACOM-INSTALL,NEWUSER                 
-DEF PATH,PATH1                              
-DEF TRACE,DATABASE.AREA,$INTERNAL           
-DEF TRACE,AREA.TABLE,$INTERNAL              
-END                                         
-RPT START,DATABASE,ALL(ALL),PATH1  
-RPT INDENT                                  
-RPT NEST,TABLE                              
-RPT DETAIL                                  
-RPT NESTEND                                 
-END    
/*

In the output, look for the ENCRYPTION-TYPE and ENCRYPTION-METHOD values. If they are not as explained above, then this table has a problem. Note that this will run for all Databases and all versions. You can restrict this report to only PROD status versions by changing the RPT START line to
-RPT START,DATABASE,ALL(PROD),PATH1  

For the SQL report, run the DBSQLPR program with this input:
//SYSIN    DD  *                                                       
SELECT AGR.DATABASE_ID         AS  DBID,                               
       AGR.DATACOM_NAME        AS  TBL,                                
       AGR.ENTITY_NAME         AS  TABLE_NAME,                         
       AGR.STATUS  || '  ' ||                                          
           CASE                                                        
               WHEN AGR.STATUS = 'P' THEN '(PROD)'                     
               WHEN AGR.STATUS = 'T' THEN                              
                    '(T' || SUBSTR(DIGITS(AGR.ENTITY_VER),3,3) || ')'  
               ELSE '('  || SUBSTR(DIGITS(AGR.ENTITY_VER),2,4) || ')'  
           END                 AS  "ST   VERS",                        
       AGR.ENCRYPTION_TYPE     AS  ENC_TYPE,                           
       AGR.ENCRYPTION_METHOD   AS  ENC_METHOD                          
                                                                       
FROM SYSADM.AGGREGATE AGR                                              
    WHERE AGR.STATUS       IN ('T', 'P')                               
      AND AGR.ENTITY_TYPE  = 'TBL'                                     
      AND ENTITY_NAME      IN                                          
             (SELECT ALL ENTITY_NAME                                   
              FROM SYSADM.AGGREGATE                                    
                  WHERE STATUS IN ('T', 'P')                           
                    AND ENTITY_TYPE = 'TBL'                            
                    AND (                                              
                         (ENCRYPTION_TYPE   NOT IN (X'40','B')) OR     
                         (ENCRYPTION_METHOD NOT IN (X'40','A','B','C'))
                        )                                              
             )                                                         
                                                                       
    ORDER BY DATABASE_ID, ENTITY_NAME   ;                              
/*                                                                     

This will produce a report of all production and test status defintions with a problem.

Once you have identified the problem there are a couple ways to correct the situation - by updating each table individually, or by updating all tables generically.

To update the tables individually, run DDUPDATE with a input like this:
//SYSIN    DD  *
-USR DATACOM-INSTALL,NEWUSER 
-UPD TABLE,TABLE-NAME-1(PROD) 
1500 ENCRYPTION-TYPE,& 
1500 ENCRYPTION-METHOD,& 
-END
/*
Repeat the UPD, 1500, and END transaction sets for each table that needs to be changed. The ampersand (&) indicates that the value should be reset to the default value, which in these fields is a blank.

To update all tables or a generic-names set of tables in the MUF, resetting their Encryption values to blanks, run DDUPDATE with this input (not that you need a set for PROD entities and a set for TEST entities):
//SYSIN    DD  *
-USR DATACOM-INSTALL,NEWUSER
-UPG TABLE,ALL(PROD) 
1500 ENCRYPTION-TYPE,& 
1500 ENCRYPTION-METHOD,& 
-END
-UPG TABLE,ALL(TEST) 
1500 ENCRYPTION-TYPE,& 
1500 ENCRYPTION-METHOD,& 
-END
/*

Note that this will change all tables in the MUF. To change all tables starting with SALES, you can use this UPG command:
-UPG TABLE,SALES*(PROD)
If you are using encryption on any tables, this method should not be used.

To prevent this problem in future changes, please be sure that you have applied this solution:
RO98204 (DD 15.1 #24 [OS]), "DDBDMLM DOES NOT ENFORCE UNIQUE HSD ROW" (Published 27 Sep 2017)  
RO97808 (DD 15.0 #66 [OS]), "DDBDMLM DOES NOT ENFORCE UNIQUE HSD ROW" (Published 11 Jan 2018)  

In addition, an HSD reset should be done to ensure that the DDBTGLM program has the correct values to extract to the transfer file. To run the HSD reset, execute program DDCFBLD with this input:

//SYSIN    DD  *
+USR DATACOM-INSTALL,NEWUSER
+HSD RESET                  
+END                        
/*                          

As a final safeguard, if you are not using encryption on a table, review the BTG output and remove any 3154 transactions, or set the 3154 transaction to spaces from column 6 to the end.
Additional Information:
As always, please contact CA Technologies support for CA Datacom if you have further questions.