473,324 Members | 2,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

need a query...

query the syscat.columns table and prepare SELECT statement (SELECT
all_columns FROM table_name). For example, let's say that TAB_1 table
has total of three columns, and the statement should be:

SELECT COL1, COL2, COL3 FROM TABSCHEMA.TAB_1

for all tables in syscat.columns

I am not able to comeup with that query...need help...Thanks.

Sep 29 '06 #1
8 4828
an*************@gmail.com wrote:
query the syscat.columns table and prepare SELECT statement (SELECT
all_columns FROM table_name). For example, let's say that TAB_1 table
has total of three columns, and the statement should be:

SELECT COL1, COL2, COL3 FROM TABSCHEMA.TAB_1

for all tables in syscat.columns

I am not able to comeup with that query...need help...Thanks.
You need aggregation. You can either use a recursive query (have a look in
the manual for "Bill of materials") or you could use XML functionality:

SELECT 'SELECT ' || substr(replace(replace(
xmlserialize(content xmlagg(
xmlelement(name \"c\", colname) order by colno)
as varchar(500)), '<c>', ','), '</c>', ''), 2) || ' FROM T'
FROM syscat.columns where tabname = 'T'

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 29 '06 #2
Anne,

the recursive query is a litle bit tricky:

WITH result(level, text) AS (
SELECT 1, colname
FROM syscat.columns
WHERE tabname = 'T1' AND tabschema = 'DB2ADMIN' AND colno = 0

UNION ALL

SELECT level+1, r.text ||', '|| colname
FROM syscat.columns c, result r
WHERE tabname = 'T1' AND tabschema = 'DB2ADMIN' AND c.colno =
r.level
)
SELECT 'SELECT '||text||' FROM '||'DB2ADMIN.T1' from result
ORDER BY level DESC
FETCH FIRST 1 ROW ONLY;

Keep in mind, that you have to modify the query to handle case
sensitive column names (just concat " to colname and to "text".
>From the programming view, it would be a lot of easier to write
procedure with simple loop than prepare such SQLs, which might be
difficult to understand.
-- Artur Wronski

Sep 30 '06 #3
Excellent! Thank you Stolze and Wronski for the query. Both the queries
work fine...one issue...
Actually, the query needs to make the 'SELECT ...' statements for all
tables in 'Table_Schema'. I have about 350 tables in my OLTP
environment...can you please change the query for that..? I tried to
change the query, but no use..
Thanks again,
Anne Carter

Oct 2 '06 #4
Create a stored proc, define a cursor to loop through syscat.columns
and append the results to a temp table.

an*************@gmail.com wrote:
Excellent! Thank you Stolze and Wronski for the query. Both the queries
work fine...one issue...
Actually, the query needs to make the 'SELECT ...' statements for all
tables in 'Table_Schema'. I have about 350 tables in my OLTP
environment...can you please change the query for that..? I tried to
change the query, but no use..
Thanks again,
Anne Carter
Oct 2 '06 #5
I cannot go for stored proc. If it is not possible with out a stored
proc, I just have to prepare 350 statements (one statement for one
table) and run it..
Thanks

ji*****@gmail.com wrote:
Create a stored proc, define a cursor to loop through syscat.columns
and append the results to a temp table.

an*************@gmail.com wrote:
Excellent! Thank you Stolze and Wronski for the query. Both the queries
work fine...one issue...
Actually, the query needs to make the 'SELECT ...' statements for all
tables in 'Table_Schema'. I have about 350 tables in my OLTP
environment...can you please change the query for that..? I tried to
change the query, but no use..
Thanks again,
Anne Carter
Oct 2 '06 #6
Actually, the query needs to make the 'SELECT ...' statements for all
tables in 'Table_Schema'. I have about 350 tables in my OLTP
environment...can you please change the query for that..?
How about this?

WITH result(level, tabname, tabschema, text) AS (
SELECT 0, tabname, tabschema, CAST('' AS VARCHAR(4000))
FROM syscat.tables
WHERE tabschema = 'DB2ADMIN'
AND type = 'T'

UNION ALL

SELECT level+1, r.tabname, r.tabschema, r.text ||', '||
RTRIM(colname)
FROM syscat.columns c
, result r
WHERE level < 10000
AND c.tabname = r.tabname
AND c.tabschema = r.tabschema
AND c.colno = r.level
)
SELECT 'SELECT '||SUBSTR(text,3)||' FROM '||RTRIM(r.tabname)||';'
FROM result r
, syscat.tables t
WHERE t.tabname = r.tabname
AND t.tabschema = r.tabschema
AND t.colcount = r.level
;

Oct 3 '06 #7
Amazing!! Thanks a lot Tonkuma!
Tonkuma wrote:
Actually, the query needs to make the 'SELECT ...' statements for all
tables in 'Table_Schema'. I have about 350 tables in my OLTP
environment...can you please change the query for that..?
How about this?

WITH result(level, tabname, tabschema, text) AS (
SELECT 0, tabname, tabschema, CAST('' AS VARCHAR(4000))
FROM syscat.tables
WHERE tabschema = 'DB2ADMIN'
AND type = 'T'

UNION ALL

SELECT level+1, r.tabname, r.tabschema, r.text ||', '||
RTRIM(colname)
FROM syscat.columns c
, result r
WHERE level < 10000
AND c.tabname = r.tabname
AND c.tabschema = r.tabschema
AND c.colno = r.level
)
SELECT 'SELECT '||SUBSTR(text,3)||' FROM '||RTRIM(r.tabname)||';'
FROM result r
, syscat.tables t
WHERE t.tabname = r.tabname
AND t.tabschema = r.tabschema
AND t.colcount = r.level
;
Oct 3 '06 #8
an*************@gmail.com wrote:
Excellent! Thank you Stolze and Wronski for the query. Both the queries
work fine...one issue...
Actually, the query needs to make the 'SELECT ...' statements for all
tables in 'Table_Schema'. I have about 350 tables in my OLTP
environment...can you please change the query for that..? I tried to
change the query, but no use..
I believe it would be better if we help you to understand how SQL works
instead of simply posting the query. Hence, my question: What were your
changes? And which results or errors did you get?
This should do (untested): We group by table and aggregate in each group.

SELECT 'SELECT ' || substr(replace(replace(
xmlserialize(content xmlagg(
xmlelement(name \"c\", colname) order by colno)
as varchar(500)), '<c>', ','), '</c>', ''), 2) || ' FROM ' ||
tabname
FROM syscat.columns
GROUP BY tabname

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Oct 4 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: lawrence | last post by:
I've been bad about documentation so far but I'm going to try to be better. I've mostly worked alone so I'm the only one, so far, who's suffered from my bad habits. But I'd like other programmers...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: pw | last post by:
Hi, I am having a mental block trying to figure out how to code this. Two tables: "tblQuestions" (fields = quesnum, questype, question) "tblAnswers" (fields = clientnum, quesnum, questype,...
7
by: K. Crothers | last post by:
I administer a mechanical engineering database. I need to build a query which uses the results from a subquery as its input or criterion. I am attempting to find all of the component parts of...
3
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems...
10
by: L. R. Du Broff | last post by:
I own a small business. Need to track a few hundred pieces of rental equipment that can be in any of a few dozen locations. I'm an old-time C language programmer (UNIX environment). If the only...
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: id ------------ name
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: ----------------
1
by: write2ashokkumar | last post by:
hi... i have the table like this, Table Name : sample Total Records : 500000 (Consider like this) Sample Records: ----------------
3
by: pbd22 | last post by:
Hi. I need some help with structuring my query strings. I have a form with a search bar and some links. Each link is a search type (such as "community"). The HREF for the link's anchor looks...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.