By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

need a query...

P: n/a
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
Share this Question
Share on Google+
8 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.