Gary_Cherlet

Finding where an IDMS/SQL VIEW name is referenced

Discussion created by Gary_Cherlet on Mar 5, 2013
Latest reply on Mar 17, 2013 by Eddy_G
[color=#3847cb]Finding where an IDMS/SQL VIEW name is referenced[color]

I was unable to perform a DROP VIEW operation because there were still references to the VIEW – but no information that I could see about where the references were!
No worries! Since I was using SQL anyway a simple query gave me the answer I was looking for to get a "where used" list for the VIEW named CODEVALUES:

[font=Courier New][color=#3847cb]select table.schema, table.name, syntax from system.table, system.syntax
where syntax LIKE '%CODEVALUES%' and table.name = syntax.table;
[color]
*+                                                                        
*+ SCHEMA  NAME                  SYNTAX
*+ ------  ----                  ------                             
*+ RSQLCIS CLANGROUP             FROM    RSQLSHR.CODEVALUES                                             
*+                                                                        
*+ RSQLCIS CULTURALGROUP         FROM    RSQLSHR.CODEVALUES                                             
*+                                                                        
*+ RSQLCIS DETERMINATIONTYPE     FROM    RSQLSHR.CODEVALUES                                             
*+                                                                        
*+ RSQLCIS INVOLVEMENTTYPE       FROM    RSQLSHR.CODEVALUES                                             
*+                                                                        
*+ RSQLCIS REFSUBJOUTCTYPE       FROM    RSQLSHR.CODEVALUES                                                                           
*+                                                                        
*+ 5 rows processed  [font]


I hope that this may help others who may find themselves in a similar situation – cheers - Gary

Outcomes