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
 
Select all the rows from tables starting with a string

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

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Fri Aug 31, 2007 11:05 pm    Post subject: Select all the rows from tables starting with a string
Reply with quote

Hi,

Can we write a query where we select the table names from sysibm.systables starting with a particular string say 'SYS' and then extract all the rows from this list of tables?

I am looking something like this:

Select * from (select NAME from sysibm.systables where name like 'SYS%')

I understand that this can be done using CLI or Dynamic SQL with cursors. But just wanted to know if we can do this without cursors in a single query.

I feel that its quite possible but....

Thanks,
Syed
Back to top
View user's profile Send private message

socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Fri Aug 31, 2007 11:58 pm    Post subject: Reply to: Select all the rows from tables starting with a st
Reply with quote

Have you tried it in a cursor? If so, did it work or did you have any problems? Post your code and results and we can answer any questions you may still have.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sat Sep 01, 2007 3:09 am    Post subject:
Reply with quote

Why not use this query intead of a full subquery? It will get the exact same results.

Code:

SELECT Name
From SYSIBM.SYSTABLES
WHERE Name like 'SYS%'
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sat Sep 01, 2007 3:10 am    Post subject:
Reply with quote

But if you really want all info on those tables just do this, still not using a subquery

Code:

Select *
FROM SYSIBM.SYSTABLES
WHERE Name LIKE 'SYS%'
Back to top
View user's profile Send private message
syed-1919

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Sat Sep 01, 2007 3:58 am    Post subject:
Reply with quote

Thanks steve and socker_dad...

I guess i was not clear with my requirment...

Lemme give my requirement with an example...

Suppose i have the following table:

SYED_1 --> 1000 rows and 10 columns
SYED_2 --> 5000 rows and 15 columns
SYED_3 --> 500 rows and 5 columns
and the list goes on...

Now my requirement is to select the content of all these rows of the tables starting with 'SYED' in a single query....

Normallly one would use 'n' number of selects for 'n' number of tables...

The condition here is that I dont know the number of tables begining with 'SYED'...Hence I use the sysibm.systables...

Hope the requirement is understandable...

Thanks and Regards,
Syed
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Sep 01, 2007 4:09 am    Post subject:
Reply with quote

Hello,

I believe you have a bigger issue than an uncertain number of tables with the same "prefix".

How will data that has completely different formats be used? Please describe how this ("select the content of all these rows") selection would work with different numbers of columns and content icon_confused.gif

If you describe what you are trying to do (rather than how you might set up a query), someone here may have suggestions.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sat Sep 01, 2007 4:41 am    Post subject:
Reply with quote

String substitution can't happen in the FROM part of your statement. You need N queries for N tables here. You could get the list of tables names into memory then generate the queries individually in Cobol or eztrieve or something, but you can't do this on a single query to the DB.
Back to top
View user's profile Send private message
syed-1919

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Tue Sep 04, 2007 10:11 pm    Post subject: Reply to: Select all the rows from tables starting with a st
Reply with quote

Thanks dick and stodolas,


Per Dick's request I am elaborating my requirement...

I have some 600 odd table starting with a string 'TACE'. Now I am supposed to copy the content off all this tables into an Excel. The simplest way is using Batch Queries with 600 odd selects.

I wanted to know if we can do this with a single query using Sysibm.systables.

What stodolas mentioned appears to be convincing. But I still believe that there should be a way thru Dynamic SQL (CLI)...

Thanks again,
Syed
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Sep 04, 2007 10:15 pm    Post subject:
Reply with quote

There is not. The object of the FROM statement must be a table. Anything you put there, DB2 will assume to be a table, not a string.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 04, 2007 10:30 pm    Post subject:
Reply with quote

Hello,

You could generate the 600 queries and then run them in batch.

If these tables have a different number of columns and content, trying to put them into an Excel spreadsheet won't provide anyting useful (unless there is more that i do not understand).

The content of 600 tables will not likely fit into a spreadsheet . . .

How will this "spreadsheet" be used?
Back to top
View user's profile Send private message
syed-1919

New User


Joined: 26 Oct 2006
Posts: 12
Location: Chennai

PostPosted: Wed Sep 05, 2007 1:39 am    Post subject: Reply to: Select all the rows from tables starting with a st
Reply with quote

Thanks again guyz,

stodolas,

Don't you think that we use Strings in Dynamic SQL even for the table name in the FROM clause?

Sorry Dick, for not being clear,

I meant 1 sheet for 1 table. Those 600 tables are look-up tables which would be made available to the developers in an Excel.

Now I have to execute 600 or more selects to meet this requirement icon_cry.gif



Anywayz,

Thank You very much guyz...

Regards,
Syed
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 05, 2007 2:11 am    Post subject:
Reply with quote

You're welcome icon_smile.gif

Is there some reason that 600 spreadsheets would be preferred to having them just read the existing reference tables?

None of the developers i support would prefer a spreadsheet when they could look at the data directly in the database.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Sep 05, 2007 2:20 am    Post subject:
Reply with quote

What do you see as the difference between Dynamic SQL and non-dynamic SQL? And why would that difference allow for what you are attempting to do?

I faced this same problem a year ago with a vendor system where we only had access to views. The view for the prior day was named like stats20070904. We didn't have direct access to the tables and had to write a report against a view with a changing name. We had to concatenate the select statement inside the program (VB) and then execute that statement. We had to get the info we wanted into memory then build the statement in the program from that data and then execute it.

Some java'ish pseudo code
//Get yesterdays-date into ws-date
//build SQL statement
statement = "SELECT * FROM " & ws-date;
//so statement would hold the string "SELECT * FROM call20070904"
statement.execute();

In your case you would have to loop to build the select string. However I don't know if you can do something like this in Cobol. Either way it needs to be done in a program.
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 Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 1 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts Selecting a row from multiple tables! Vignesh Sid DB2 2 Thu Oct 26, 2017 6:09 pm
No new posts line 52: Name or string > 250 char... Ashishpanpaliya CLIST & REXX 5 Sat Oct 14, 2017 2:29 am
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm

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