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

Improve company productivity with a Business Account.Sign Up

x
 
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
0
 
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
Upgrade your Question Security!

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

 
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
 
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
 
pvsbandiAuthor Commented:
Thanks Momi!
0
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.