Select all the rows from tables starting with a string

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

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

socker_dad · Posted: Fri Aug 31, 2007 11:58 pm

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.

stodolas · Posted: Sat Sep 01, 2007 3:09 am

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

stodolas · Posted: Sat Sep 01, 2007 3:10 am

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

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

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

dick scherrer · Posted: Sat Sep 01, 2007 4:09 am

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

If you describe what you are trying to do (rather than how you might set up a query), someone here may have suggestions.

stodolas · Posted: Sat Sep 01, 2007 4:41 am

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.

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

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

stodolas · Posted: Tue Sep 04, 2007 10:15 pm

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.

dick scherrer · Posted: Tue Sep 04, 2007 10:30 pm

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?

syed-1919 · New User Joined: 26 Oct 2006 Posts: 12 Location: Chennai

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

Anywayz,

Thank You very much guyz...

Regards,
Syed

dick scherrer · Posted: Wed Sep 05, 2007 2:11 am

You're welcome

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.

stodolas · Posted: Wed Sep 05, 2007 2:20 am

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.