Clarity PPM

Expand all | Collapse all

How to find table column used in GEL processes

  • 1.  How to find table column used in GEL processes

    Posted 04-22-2015 02:25 AM

    We have over 180 processes created using GEL. We would like to find in all the processes where one of the column of "odf_ca_project" table is being used. Anybody done this before? Any clues will be appreciated.

     

    Regards,

    Manas



  • 2.  Re: How to find table column used in GEL processes

    Posted 04-22-2015 03:38 AM

    Do a search to the scripts table (something like CMN_SCRIPTS or CMN_CUSTOM_SCRIPTS) then trace them to the validated and active process through process steps, process stages and process versions.



  • 3.  Re: How to find table column used in GEL processes

    Posted 04-22-2015 03:46 AM

    select * from  cmn_custom_scripts

    where script_text like '%odf_ca_project%'



  • 4.  Re: How to find table column used in GEL processes

    Posted 04-22-2015 03:57 AM

    That will pick the table. You could also search for the field. Once you find the matches you have to manually verify if they are true or false positives That is if the field is in the ODF_CA_PROJECT table or for some other table and that it is the exact string and not a pari in a longer string. You have to use the like %% construct to find the matches in the script text and that will pick also those false matches and occurences where the field name is pre or post suffixed with thinks like (). which are valid matches.



  • 5.  Re: How to find table column used in GEL processes

    Posted 04-22-2015 04:00 AM

    With 180 scripts you are better of querying just for the script ID's and once you got them then export the script text to validate the matches before you start tracking the processes.



  • 6.  Re: How to find table column used in GEL processes

    Posted 04-22-2015 04:43 AM

    This might be better if you're on Oracle (I vaguely recall that the SQL Server collation for Clarity is case insensitive)...

     

    select * from  cmn_custom_scripts

    where LOWER(script_text) like '%odf_ca_project%'



  • 7.  Re: How to find table column used in GEL processes

    Posted 04-22-2015 04:31 AM

    Thanks Aurora and Urmas for your very quick response.



  • 8.  Re: How to find table column used in GEL processes

    Posted 04-22-2015 06:41 AM

    There is no ER for processes any more in the Tech ref.

    There is one in

    https://communities.ca.com/message/18353106#18353106

    but that goes more to instances that have run and not so much to definitions, but you could replace run with def in the table name. There is a better one in the Tech ref of 7.5 if you can get  hold of that.

    Anyway the tables are listed in the thread.