Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Column having values <STRING1>

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Tue Nov 08, 2011 9:14 pm    Post subject: Column having values <STRING1>
Reply with quote

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
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Nov 08, 2011 9:41 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Wed Nov 09, 2011 5:46 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Nov 09, 2011 6:55 pm    Post subject:
Reply with quote

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,
  1. combine all the stored
    1. SELECT's
    2. the FROM
    3. the WHERE/AND clauses
  2. write the sql to file
  3. 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.
Code:

WHERE POSSTR(column,part1) > 0
      AND
      POSSTR(column,part2) = 0
   OR (
      POSSTR(column,part1) > 0
      AND
      POSSTR(column,part2) > POSSTR(column,part1)
      AND
      POSSTR(column,part3) = 0
      )
   OR (
      POSSTR(column,part1) > 0
      AND
      POSSTR(column,part2) > POSSTR(column,part1)
      AND
      POSSTR(column,part3) > POSSTR(column,part2)
      AND     
      POSSTR(column,part4) = 0
      )


My code could generate unlimited sql for unlimited parts.
but that would get silly.
you would go out of your mind doing this by hand,
but you can generate a file containing all this sql,
then in batch, execute the sql, then parse the output sysouts
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Nov 09, 2011 7:00 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Wed Nov 09, 2011 7:06 pm    Post subject:
Reply with quote

I was planning to combine the Sql with some Rexx and execute
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us