search for a string in all stored procedures and functions


  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.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

momi_sabagConnect With a Mentor Commented:
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
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.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

what is subsidy?
is it an object name?
if so, you can search for it in the tables that document object dependencies
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?
you can't
and i will ask again
is subsidy a name of another object (table / view) ?
pvsbandiAuthor Commented:
subsidy is a part of different columns like adoption_subsidy;guardian_subsidy, pay_subsidy etc.
pvsbandiAuthor Commented:
Thanks Momi!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.