Column having values <STRING1>

Pandora-Box · Posted: Tue Nov 08, 2011 9:14 pm

Hi all,

I have got a requirement to find the column names belong to a particular creator say DBAT which has values as %<STRING2> % cosidering the string2 can be anywhere in the column

Is there any better idea rather creating a Dynamic sql which I had thought of??

I thought to list down all columns from SYSIBM.SYSCOLUMNS then using the output of first query thought of running another dynamic Sql statement to search for the occurence of the string %<string2>% So the count(*) would tell fields having the string will be greater or equak to one

If anyone can guide me for a better approach will be helpful

Thanks

dbzTHEdinosauer · Posted: Tue Nov 08, 2011 9:41 pm

in your where clause for the syscolumn select, only select char col as large or larger than you need to contain your search-string.

personally, i perfer the POSITION function POS(....) GT 0 to find the strings in each column. the pos value could be an offset for any substr function to update the column.

Pandora-Box · Posted: Wed Nov 09, 2011 5:46 pm

Hi Dbz,

Thanks.

My string is something like '%&%.%'
But not sure how far POS will be effective sorry for not being more expressive on my String

dbzTHEdinosauer · Posted: Wed Nov 09, 2011 6:55 pm

can't use wildcards in the search key for POSITION or POSSTR.

though you could construct a lot of LIKE predicates,
being a bit&bytes type, i would

as you are pounding down thru the sysibmsyscolumns table cursor,
(sort by table, col pos, col len)
compare cursor_table to current_table in FROM clause
IF table name different,

combine all the stored
1. SELECT's
2. the FROM
3. the WHERE/AND clauses
write the sql to file
CLEAR ALL build areas

end-if

i would take your searchkey mask, and isolate the literal searchkey(s) from the wildcards.
i recently did this kind of thing and found that allowing only one type of wildcard made things easier.

parse/unstring the searchkey
generate a POS for each
then build parenthetically enclosed AND/OR's
if you only have one searchkey part, then you only need one POS
problem is if you have more than one, the second can not start until the first is found/not found.

dbzTHEdinosauer · Posted: Wed Nov 09, 2011 7:00 pm

l would strongly advise againt
executing the sql in an interactive session/ in the foreground.

and,
keep in mind, the part1, part2 ...etc need to be replaced with an apostrophied-enclosed string/field contents.

Pandora-Box · Posted: Wed Nov 09, 2011 7:06 pm

I was planning to combine the Sql with some Rexx and execute