Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

search for a string in all stored procedures and functions

Hi,

  I'm on DB2 UDB 8.2
  Is there a way to query the database against all the procedures/functions/triggers to see if a particular string has been used?
  Please advise.
0
pvsbandi
Asked:
pvsbandi
  • 4
  • 4
1 Solution
 
pvsbandiAuthor Commented:
Thanks! I queried something like this.
select distinct routinename from SYSCAT.ROUTINES 
where upper(text) like '%SUBSIDY%'

Open in new window


But upper is not working on this column. If i don't use upper or lower, i'm loosing many procedures which use subsidy in different case sensitivities.

How can i run this? Please help.
0
 
momi_sabagCommented:
what is subsidy?
is it an object name?
if so, you can search for it in the tables that document object dependencies
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
pvsbandiAuthor Commented:
The whole purpose of this search is to get all the routines that use Subsidy, as it is changing into something else. So, this search should get us a list of all procedures/functions etc that use subsidy.

  Can't we use UPPER() or LOWER() on a CLOB?
0
 
momi_sabagCommented:
you can't
and i will ask again
is subsidy a name of another object (table / view) ?
0
 
pvsbandiAuthor Commented:
subsidy is a part of different columns like adoption_subsidy;guardian_subsidy, pay_subsidy etc.
0
 
momi_sabagCommented:
so this is not good

you can try to use the db2look utility to generate create statements of all your objects, and then use a regular text search tool (like notepad) to find that value

or, you can write a procedure that loops through the clob column, cuts it into smaller varchar values and check against them but it would be far more complicated
0
 
pvsbandiAuthor Commented:
Thanks Momi!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now