Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1542
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: 6968
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: 1542
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: 6968
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: 6968
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: 1542
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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Working with hex values in Filemanage... Emile Straker IBM Tools 0 Tue Sep 12, 2017 1:42 am
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm
No new posts Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us