need a query...

[annecarterfredi]

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.

[Knut Stolze]

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

[Artur]

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

[annecarterfredi]

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

[jiangqs]

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

[annecarterfredi]

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

[Tonkuma]

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
;

[annecarterfredi]

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
;

[Knut Stolze]

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