CA Gen CSE SQL to update Dynamic Linking properties for Business Systems and RI Triggers

Products

Gen

Issue/Introduction

We intend to change all CA Gen 8.6 z/OS server code to use dynamic linking.
While some screens|procedure steps|action blocks set this explicitly most defer to the Business System default value and the vast majority of those Business Systems configure this feature as no|no|no i.e. LKEDOPTS property is set to NNN. The only other configuration is NYN.
Our plan is to create some SQL to update all to YYY, but we would like some advice on this matter and also for RI triggers to be dynamic via SQL as well.

Environment

Release : 8.6
Component : CA Gen Client Server Encyclopedia

Resolution

At Business System level the Dynamic Linking property is schema object TECHSYS property LKEDOPTS which implements the Business System (BUSSYS) object.
The TECHSYS-BUSSYS association is stored in the CSE in the direction BUSSYS (58) -> IMPLBY (170) -> TECHSYS (236).
The RI Triggers Dynamic Link property is schema object TECHDESN property LKEDRIT

SQL SELECT STATEMENTS:
1a. The TECHSYS property LKEDOPTS is type 196 and stored in DTXT table. This SQL returns all the LKEDOPTS property values for all Business Systems in a particular model:
select text_value from DTXT where text_model_id=(select model_id from DMDL where model_name='<model_name>') and text_prop_code=196;
b. To find the LKEDOPTS value for a particular Business System name within a particular model this SQL can be used:
select text_value from DTXT where text_prop_code=196 and text_obj_id = (select assoc_to_obj_id from DASC where assoc_type_code=170 and assoc_from_obj_id=(select obj_id from DOBJ where obj_type_code=58 and obj_name='<bus_sys_name>' and obj_model_id=(select model_id from DMDL where model_name='<model_name>')));

2. The TECHDESN property LKEDRIT is type 932 and is also stored in the DTXT table. This SQL returns the LKEDRIT property value in a particular model:
select text_value from DTXT where text_model_id=(select model_id from DMDL where model_name='<model_name>') and text_prop_code=932;
There can only be 1 Technical Design (TECHDESN) object in a model

SQL UPDATE STATEMENTS:
NOTE: Before applying any SQL updates to a CSE Encyclopedia database please ensure that a current Encyclopedia database backup has been taken and all models & subsets have been checked in.
To update the values the select statements shown above can be replaced with required corresponding update statements e.g.
TECHSYS property LKEDOPTS:
To update the LKEDOPTS property value to 'YYY' for all Business Systems in a particular model this SQL can be used:
update DTXT set text_value ='YYY' where text_model_id=(select model_id from DMDL where model_name='<model_name>') and text_prop_code=196;
To update the LKEDOPTS value to 'YYY' for a particular Business System name within a particular model this SQL can be used:
update DTXT set text_value ='YYY' where text_prop_code=196 and text_obj_id = (select assoc_to_obj_id from DASC where assoc_type_code=170 and assoc_from_obj_id=(select obj_id from DOBJ where obj_type_code=58 and obj_name='<bus_sys_name>' and obj_model_id=(select model_id from DMDL where model_name='<model_name>')));

TECHDESN property LKEDRIT:
To update the LKEDRIT property value to 'Y' in a particular model:
update DTXT set text_value ='Y' where text_model_id=(select model_id from DMDL where model_name='<model_name>') and text_prop_code=932;

Additional Information

NOTES:
1. It is possible that the MVS Environment Parameters have never been set for a Business System in which case there will be no existing row for TECHSYS property LKEDOPTS stored in DTXT and the value will default to 'NNN'. Previous generations may just have used values set in the Generation Defaults on the CSE to override at generation time or alternatively they may have been set by a 3rd party tool like IET's GuardIEn.