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
 

 

Find all view names which start with P_

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

New User


Joined: 21 Jul 2005
Posts: 12

PostPosted: Mon Oct 09, 2006 6:23 pm    Post subject: Find all view names which start with P_
Reply with quote

I need to find all view names which start with P_ (P and an underscore symbol). So, I wrote this query....

Code:
SELECT * FROM SYSIBM.SYSTABLES
 WHERE NAME LIKE 'P_%'
 FETCH FIRST 10 ROWS ONLY   


Code:
NAME                                                 
---------+---------+---------+---------+---------+----
PLAN_TABLE                                           
PLAN_TABLE                                           
POL                                                   
PROD                                                 
PROMO                                                 
P_CODE_TRANSLATION                                   
P_HASH_TOTAL                                         
PLAN_TABLE                                           
PAB_DESC                                             
PAPEROFF#T   

clearly not what I was looking for... right?

So, what is wrong with this SQL? Can you correct it to satisfy my requirement ?
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Mon Oct 09, 2006 7:49 pm    Post subject: Re: db2
Reply with quote

Try adding one more condition in Where clause

AND TYPE = 'V'
Back to top
View user's profile Send private message
bandi

New User


Joined: 21 Jul 2005
Posts: 12

PostPosted: Tue Oct 10, 2006 12:03 pm    Post subject:
Reply with quote

Hi ,
Could you please let me know give me correct sql still I'm not getting currect result?
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Tue Oct 10, 2006 3:17 pm    Post subject:
Reply with quote

Hi bandi,

'_' represents any one single charector and '%' represents group of charectors.

So as per your query after P there must be one charector.

I think it will help you.
Back to top
View user's profile Send private message
meetreks

New User


Joined: 28 Sep 2006
Posts: 26
Location: Edinburgh

PostPosted: Tue Oct 10, 2006 6:52 pm    Post subject:
Reply with quote

Hi,

in the SYSIBM tables, a table is represented as type 'T' for table, Type 'V' for view and Type 'A' for alias. So either qualify your view with Type = V or you will get the same result as before.

The corresponding SQL is

Code:
SELECT * FROM SYSIBM.SYSTABLES
WHERE NAME LIKE 'P_%'  AND
TYPE = 'V'
FETCH FIRST 10 ROWS ONLY


Hope this helps.
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Tue Oct 10, 2006 7:57 pm    Post subject:
Reply with quote

you are correct ram. Here the problem is if run the above query we will get names like : PLAN_TABLE, PLANTABLE, etc.

But his requirement is to get the Tables like P_TABLE. The table name should start with ' P_'. first char must be P and second char must be '_'

Is thr any way to get this type of info
Back to top
View user's profile Send private message
meetreks

New User


Joined: 28 Sep 2006
Posts: 26
Location: Edinburgh

PostPosted: Wed Oct 11, 2006 2:33 am    Post subject: Re: Find all view names which start with P_
Reply with quote

There are two ways I can think of.

1. Qualify the table with the full Schema

2. Use WHERE name not in () and give all the system table names:))
Back to top
View user's profile Send private message
bonniem

New User


Joined: 09 Aug 2005
Posts: 67

PostPosted: Wed Oct 11, 2006 9:06 am    Post subject: Re: Find all view names which start with P_
Reply with quote

Why not code where clause as

WHERE SUBSTR(NAME,1,2)='P_'
Back to top
View user's profile Send private message
bonniem

New User


Joined: 09 Aug 2005
Posts: 67

PostPosted: Mon Oct 16, 2006 9:15 am    Post subject: Re: Find all view names which start with P_
Reply with quote

hi i thought i will explain what is wrong with like 'P_%'. Db2 replaces '_' with any character. So 'P_%' resolves to 'P%'. That is why results are not correct.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Mon Oct 16, 2006 9:48 am    Post subject: Re: Find all view names which start with P_
Reply with quote

Bandi...

Thats where you code ESCAPE Clause to give their literal meaning to "%" (percentage) and "_" (underscore) signs.

So in your case, you would like to go something like this...
Code:
SELECT * FROM SYSIBM.SYSTABLES
WHERE NAME LIKE 'P+_%' ESCAPE '+';
FETCH FIRST 10 ROWS ONLY;


See here for a complete reference...
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 find particular member name in PDS us... ravi243 CLIST & REXX 10 Mon Dec 19, 2016 6:44 pm
No new posts CICS START AND CANCEL blayek CICS 1 Wed Dec 07, 2016 3:27 am
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
No new posts Find out a active PGM jpsager JCL & VSAM 10 Fri Oct 07, 2016 4:32 pm
No new posts How to find a CICS resource used in C... Arunkumar Chandrasekaran CICS 8 Thu Sep 29, 2016 1:45 pm


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