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. 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
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
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
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
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
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
;
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
;
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
----------------
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
----------------
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |