I have a friend that has a tool that can search for text in any column in any table/physical file. The tool uses sysColumns to build a query. The query passes back schema/library, table/file, and relative record. We use it to run a second query to look at data and find the actual field with the search value. I suspect the dynamic query looks some thing like:
select <table_schema>, <table_name>, rrn(x) from <table_schema>.<table_name> x where <field1> like  '%<searchText>%' or <field2> like '%<searchText>%' or ...<field_n> like '%<searchText>%'

I'm curious if you can think of another way perform the search. The DSPPFM shows each record as a character string. Is there a sql way to search a whole record for a string instead of each field?
Some thing like:
select <table_schema>, <table_name>, rrn(x) from <table_schema>.<table_name> x where regExp_like( wholeRec(x), <searchText> )

Any thoughts?

------------------------------
Patrick Conner
------------------------------
#SQL

I'm not aware of any built-in SQL way to search the whole row.  

If you're interested in good performance for the ColumnName LIKE '%<predicate>%', you may want to look to change the search to use the IBM OmniFind Text Search Server.  There's a white paper at:  ibm.biz/db2iPapers

------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Thu June 30, 2022 04:54 PM
From: Patrick Conner
Subject: Search for text in any field in any file

I have a friend that has a tool that can search for text in any column in any table/physical file. The tool uses sysColumns to build a query. The query passes back schema/library, table/file, and relative record. We use it to run a second query to look at data and find the actual field with the search value. I suspect the dynamic query looks some thing like:
select <table_schema>, <table_name>, rrn(x) from <table_schema>.<table_name> x where <field1> like  '%<searchText>%' or <field2> like '%<searchText>%' or ...<field_n> like '%<searchText>%'

I'm curious if you can think of another way perform the search. The DSPPFM shows each record as a character string. Is there a sql way to search a whole record for a string instead of each field?
Some thing like:
select <table_schema>, <table_name>, rrn(x) from <table_schema>.<table_name> x where regExp_like( wholeRec(x), <searchText> )

Any thoughts?

------------------------------
Patrick Conner
------------------------------
#SQL