Search for a String in DB2 Database

Manju-Pacha · New User Joined: 21 Mar 2007 Posts: 7 Location: Bangalore

Hi,

I need to search tables in DB2 database in Mainframe Z/OS for a particular string irrespective of which column that search string lies.
Is there any method to do so using any mainframe DB2 utility or any third party tools like SQL Squirrel Assistant which we use to connect to DB2 database?

Thanks,
Manju

vini_srcna · Posted: Wed Mar 21, 2007 1:12 pm

Upto my knowledge i dont think you can do that.
I would have unloaded the data into PS file and put a F command.
This would be OK if you have less data.

Manju-Pacha · New User Joined: 21 Mar 2007 Posts: 7 Location: Bangalore

we need to do this for almost entire tables in client DB..quite a huge amount of data

I heard about freetext search feature in SQL SERVER, query is of the format
Select * from table_name where FREETEXT(*, 'search string?)

* indicates search entire columns

Would like to know whether anything of similar type is available for DB2 thru some add on DB2 products.

vini_srcna · Posted: Wed Mar 21, 2007 3:02 pm

Is that on Z/OS platform or LUW ..?

Manju-Pacha · New User Joined: 21 Mar 2007 Posts: 7 Location: Bangalore

Its Z-OS.

But if we can perform search using some products that can be installed in Windows machine and then connect to mainframe Db2, that would also be fine.

vini_srcna · Posted: Wed Mar 21, 2007 6:42 pm

I dont think we have such option now. I'm wondering howcome you have such requirement. You have to search for a string..that is ok.
But searching for a string in a DB2 table irrespective of column name makes no sense to me. In fact i have never come across this kind of situtaion. I dont think there would be some add on product or third party product for this kind of search. Please let me know if you find solution for this. thanks

dick scherrer · Posted: Thu Mar 22, 2007 12:26 am

Hello,

Is this something you need to do once? Is the data constantly maintained or is it historical data. What is considered huge at your location?

One thing you could do is to create a "new" DB2 table that only has one column that is the entire width of the problem table (or you could strip out all but the pic x(nn) fields as the text search wouldn't include prices, quantities, rates, etc. You could unload the "real" data and load the data you want to search.

Once unloaded, you could also transfer the data to an NT box (with today's xfer speed even high volumes can be sent rather quickly) and load it into SQL Server and use FREETEXT there.

I too am curious how a given text value could be "just anywhere"

Manju-Pacha · New User Joined: 21 Mar 2007 Posts: 7 Location: Bangalore

Thanks Dick & Vini for your feedback.
This is part of an impact analysis for a retail client where we just have few IP adresses and domain names as input and we need to find the impact of IP/domain migration in entire client applications.
As of now we don't have inputs on which all tables/columns in db, these info are stored. For programs and other components we used search options in ISPF, but for DB2 we didn't find any such help.

Just was trying to find out if any options are available incase we don't get specific info about tables from SMEs.

Thanks a lot!

Manju

vini_srcna · Posted: Thu Mar 22, 2007 10:47 am

You are welcome..!

dick scherrer · Posted: Thu Mar 22, 2007 6:59 pm

You're welcome and good luck