Gen Encyclopedia SQL to find Action Blocks that use RI Triggers

Products

• Gen
• Gen - Host Encyclopedia

Issue/Introduction

What Gen Encyclopedia SQL can be used to identify Action Blocks that use Referential Integrity (RI) Triggers.

Resolution

Table of Contents

• Background
• Host Encyclopedia SQL
• Client Server Encyclopedia (CSE) SQL

Background

When doing analysis on a data model or considering changes to a data model that will affect Referential Integrity (RI) Triggers, it is useful to identify all of the action blocks that use RI triggers. Any change to a relationship or to the identifier for an entity type has the potential to require regeneration of RI triggers and the action diagrams that use them.

The SQL below looks at all DELETE, DISASSOCIATE, and TRANSFER statements in a model to find action blocks that contain those statements since those are the statements that use RI triggers. This article lists the SQL, for the Host Encyclopedia and the CSE, and then describes the differences between the two.

For reference, the metamodel codes used are as follows and can be found in Gen windows Installation file "%Gen86%\Gen\odrpta.chm"

Object type codes
21      ACBLKBSD
23      ACBLKBAA
27      DELETE
39      PRDRD (disassociate)
40      PRDRT (transfer)

Association type codes
3        ACTDON
6        ACTSON
25      CNTENTS (entity action view group)
26      CNTIMPS (import view group)
31      CNTOUTS (export view group)
36      CONTAINS
76      DTLBYP
88      GRPBY

Property type code
224      NAME

 

Host Encyclopedia SQL

SELECT DISTINCT P.PROP_CHAR_VAL, H.OBJ_ID, OT_OBJ_NAME FROM    
DMDL A, DOBJ H, DASC G, DASC F, DASC D, DPRP P, DASC C, DOBJ B,
SOBJ WHERE A.MODEL_NAME = 'MODEL_NAME' AND      
H.OBJ_MODEL_ID = A.MODEL_ID AND                                
H.OBJ_TYPE_CODE = 27 AND                                       
OT_OBJ_CODE = H.OBJ_TYPE_CODE AND                              
OT_RELEASE = 'RELEASE' AND                                      
H.OBJ_ID = G.ASSOC_TO_OBJ_ID AND                               
G.ASSOC_TYPE_CODE = 3 AND                                      
G.ASSOC_FROM_OBJ_ID = F.ASSOC_TO_OBJ_ID AND                    
F.ASSOC_TYPE_CODE = 36 AND                                     
D.ASSOC_TO_OBJ_ID = F.ASSOC_FROM_OBJ_ID AND                    
D.ASSOC_TYPE_CODE IN (25, 31, 26) AND                          
C.ASSOC_TO_OBJ_ID = D.ASSOC_FROM_OBJ_ID AND                    
C.ASSOC_TYPE_CODE = 88 AND                                     
C.ASSOC_FROM_OBJ_ID = B.OBJ_ID AND                             
B.OBJ_TYPE_CODE IN (21, 23) AND                                
P.PROP_OBJ_ID = B.OBJ_ID AND                                   
P.PROP_TYPE_CODE = 224                                         
UNION                                                          
SELECT DISTINCT P2.PROP_CHAR_VAL, H2.OBJ_ID, OT_OBJ_NAME FROM  
DMDL A2, DOBJ B2, DASC C2, DASC D2, DASC F2,                   
DOBJ H2, DASC J2, DASC I2, DPRP P2, SOBJ                       
WHERE A2.MODEL_NAME = 'MODEL_NAME' AND          
H2.OBJ_MODEL_ID = A2.MODEL_ID AND                              
H2.OBJ_TYPE_CODE IN (39, 40) AND                               
OT_OBJ_CODE = H2.OBJ_TYPE_CODE AND                             
OT_RELEASE = 'RELEASE' AND                                      
I2.ASSOC_FROM_OBJ_ID = H2.OBJ_ID AND                           
I2.ASSOC_TYPE_CODE = 6 AND                                     
J2.ASSOC_TO_OBJ_ID = I2.ASSOC_TO_OBJ_ID AND                    
J2.ASSOC_TYPE_CODE = 76 AND                   
J2.ASSOC_FROM_OBJ_ID = F2.ASSOC_TO_OBJ_ID AND 
F2.ASSOC_TYPE_CODE = 36 AND                   
D2.ASSOC_TO_OBJ_ID = F2.ASSOC_FROM_OBJ_ID  AND
D2.ASSOC_TYPE_CODE IN (26, 31, 25) AND        
C2.ASSOC_TO_OBJ_ID = D2.ASSOC_FROM_OBJ_ID AND 
C2.ASSOC_TYPE_CODE = 88 AND                   
C2.ASSOC_FROM_OBJ_ID = B2.OBJ_ID AND          
B2.OBJ_TYPE_CODE IN (21, 23) AND              
P2.PROP_OBJ_ID = B2.OBJ_ID AND                
P2.PROP_TYPE_CODE = 224;   

 

Client Server Encyclopedia (CSE) SQL

SELECT DISTINCT B.OBJ_NAME, H.OBJ_ID, OT_OBJ_NAME  FROM DMDL A, DOBJ H, DASC G, DASC F, DASC D, 
DASC C, DOBJ B, SOBJ WHERE A.MODEL_NAME = 'MODEL_NAME' AND 
H.OBJ_MODEL_ID = A.MODEL_ID  AND 
H.OBJ_TYPE_CODE = 27 AND 
OT_OBJ_CODE = H.OBJ_TYPE_CODE AND 
OT_RELEASE = 'RELEASE' AND 
H.OBJ_ID = G.ASSOC_TO_OBJ_ID AND 
G.ASSOC_TYPE_CODE = 3  AND 
G.ASSOC_FROM_OBJ_ID = F.ASSOC_TO_OBJ_ID AND 
F.ASSOC_TYPE_CODE = 36 AND 
D.ASSOC_TO_OBJ_ID = F.ASSOC_FROM_OBJ_ID AND 
D.ASSOC_TYPE_CODE IN (25, 31, 26) AND 
C.ASSOC_TO_OBJ_ID = D.ASSOC_FROM_OBJ_ID AND 
C.ASSOC_TYPE_CODE = 88 AND 
C.ASSOC_FROM_OBJ_ID = B.OBJ_ID AND 
B.OBJ_TYPE_CODE IN (21, 23) 
UNION 
SELECT DISTINCT B2.OBJ_NAME, H2.OBJ_ID, OT_OBJ_NAME  FROM DMDL A2, DOBJ B2, DASC C2, DASC D2, DASC F2, 
DOBJ H2, DASC J2, DASC I2, SOBJ  WHERE A2.MODEL_NAME = 'MODEL_NAME' AND 
H2.OBJ_MODEL_ID = A2.MODEL_ID  AND 
H2.OBJ_TYPE_CODE IN (39, 40) AND 
OT_OBJ_CODE = H2.OBJ_TYPE_CODE AND 
OT_RELEASE = 'RELEASE' AND 
I2.ASSOC_FROM_OBJ_ID = H2.OBJ_ID AND 
I2.ASSOC_TYPE_CODE = 6 AND 
J2.ASSOC_TO_OBJ_ID = I2.ASSOC_TO_OBJ_ID  AND 
J2.ASSOC_TYPE_CODE = 76 AND 
J2.ASSOC_FROM_OBJ_ID = F2.ASSOC_TO_OBJ_ID AND 
F2.ASSOC_TYPE_CODE = 36 AND 
D2.ASSOC_TO_OBJ_ID = F2.ASSOC_FROM_OBJ_ID AND 
D2.ASSOC_TYPE_CODE IN (26, 31, 25) AND 
C2.ASSOC_TO_OBJ_ID = D2.ASSOC_FROM_OBJ_ID AND 
C2.ASSOC_TYPE_CODE = 88 AND 
C2.ASSOC_FROM_OBJ_ID = B2.OBJ_ID AND 
B2.OBJ_TYPE_CODE IN (21, 23);

NOTES:

• When using the SQL replace all references to 'MODEL_NAME' with the actual model name and references to 'RELEASE' to the relevant schema release for the model e.g. for current Gen 8.x models the value is '9.2.A6'.
  
  
• The difference between the two sets of SQL reflects a difference in the way properties are stored on the different encyclopedia types.
  The NAME property is stored in the DOBJ table in the CSE while on the Host Encyclopedia it is stored in the DPRP table.
  
  
• In both sets of SQL, the action block name, action block object id and the statement type (DELETE, PRDRD, or PRDRT) are displayed in the output.