| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-23-11, 05:28
|
|
Registered User
|
|
Join Date: Oct 2007
Posts: 200
|
|
|
sql conconate help
|
|
hi
i am trying to get an output like this from syscat.tables,
count(*) tabname
--------- ---------
100 product
200 sales
300 purchase
but not able get the tabname what i tried
db2 -x "select 'select count(*), ''','||tabname||',''' from '||tabschema||'.'||tabname||' union ' from syscat.tables where tabschema = 'PRYDEV' and type = 'T' " > select .txt;
ill get output in select.txt like
select count(*), ' ||tabname|| ' from PRYDEV.FACT_EMP1 union
select count(*), ' ||tabname|| ' from PRYDEV.FACT_EMP1_STAGE union
.....
;
at end i replace the last statement union with ; which makes it as single statement with unions, but i am not able print the tabname its gives the output like this
1 2
----------- -------------
100 ||tabname||
200 ||tabname||
300 ||tabname||
400 ||tabname||
700 ||tabname||
if i use this
db2 -x "select 'select count(*), '||tabname||', from '||tabschema||'.'||tabname||' union ' from syscat.tables where tabschema = 'PRYDEV' and type = 'T' " > select .txt;
select count(*), FACT_EMP1 from PRYDEV.FACT_EMP1 union
select count(*), FACT_EMP1_STAGE from PRYDEV.FACT_EMP1_STAGE union
.....
;
desired output in select.txt
select count(*) , 'FACT_EMP1' FROM PRYDEV.FACT_EMP1
union
select count(*), 'FACT_EMP1_STAGE' from PRYDEV.FACT_EMP1_STAGE union
......
;
hope i have not confused, any help how to get the tablename.
regds
Paul
|
|
12-23-11, 05:50
|
|
Registered User
|
|
Join Date: Apr 2006
Location: Belgium
Posts: 1,159
|
|
the output of count(*) is numeric and can not be used for concatenate
convert this to characters with char function
char(count(*)||'any data'
__________________
Best Regards, Guy Przytula
Database Software Consultant
DB2 UDB LUW Certified V7-V8-V9-V9.7 DB Admin - Dprop..
Information Server Datastage Certified
http://www.infocura.be
|
|
12-23-11, 08:21
|
|
Registered User
|
|
Join Date: May 2010
Location: India
Posts: 34
|
|
|
|
Try this
db2 -x " select 'select count(*) , ''' || tabname || ''' from ' || trim(tabschema) || '.' || tabname || ' union' as c1 from syscat.tables " > select.txt
Satya...
|
|
12-23-11, 09:48
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Here is an example to generate a query to get number of rows in some tables.
Example 1-1: Generate a query statement.
Code:
SELECT SUBSTR(
'SELECT ''' || tabschema || '.' || tabname || ''' AS table_name , COUNT(*) AS count_rows FROM '
|| tabschema || '.' || tabname
|| CASE
WHEN LEAD(tabname) OVER(ORDER BY tabschema , tabname) IS NULL
THEN ' ORDER BY 1;'
ELSE ' UNION ALL '
END
, 1 , 150)
FROM syscat.tables
WHERE tabschema = 'SYSTOOLS'
AND type = 'T'
ORDER BY
tabschema , tabname
;
------------------------------------------------------------------------------
1
------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT 'SYSTOOLS.HMON_ATM_INFO' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.HMON_ATM_INFO UNION ALL
SELECT 'SYSTOOLS.HMON_COLLECTION' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.HMON_COLLECTION UNION ALL
SELECT 'SYSTOOLS.POLICY' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.POLICY ORDER BY 1;
3 record(s) selected.
Example 1-2: Result of execution of the generated query.
Code:
------------------------------ Commands Entered ------------------------------
SELECT 'SYSTOOLS.HMON_ATM_INFO' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.HMON_ATM_INFO UNION ALL
SELECT 'SYSTOOLS.HMON_COLLECTION' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.HMON_COLLECTION UNION ALL
SELECT 'SYSTOOLS.POLICY' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.POLICY ORDER BY 1;
------------------------------------------------------------------------------
TABLE_NAME COUNT_ROWS
------------------------ -----------
SYSTOOLS.HMON_ATM_INFO 326
SYSTOOLS.HMON_COLLECTION 0
SYSTOOLS.POLICY 5
3 record(s) selected.
|
Last edited by tonkuma; 12-23-11 at 10:06.
Reason: Add SUBSTR() and ORDER BY
|
12-23-11, 13:56
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Make a generated query in a line.
Example 2-1: Generate a query statement.
Note: You might want to remove CAST, if more tables were selected.
Code:
------------------------------ Commands Entered ------------------------------
SELECT CAST(
LISTAGG(subquery_of_union_all)
WITHIN GROUP( ORDER BY tabschema , tabname )
AS VARCHAR(500)
) AS generated_query
FROM (SELECT 'SELECT ''' || tabschema || '.' || tabname
|| ''' AS table_name , COUNT(*) AS count_rows FROM '
|| tabschema || '.' || tabname
|| CASE
WHEN LEAD(tabname)
OVER( ORDER BY tabschema , tabname ) IS NULL
THEN ' ORDER BY 1;'
ELSE ' UNION ALL '
END AS subquery_of_union_all
, tabschema , tabname
FROM syscat.tables
WHERE tabschema = 'SYSTOOLS'
AND type = 'T'
GROUP BY
tabschema , tabname
)
;
------------------------------------------------------------------------------
GENERATED_QUERY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT 'SYSTOOLS.HMON_ATM_INFO' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.HMON_ATM_INFO UNION ALL SELECT 'SYSTOOLS.HMON_COLLECTION' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.HMON_COLLECTION UNION ALL SELECT 'SYSTOOLS.POLICY' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.POLICY ORDER BY 1;
1 record(s) selected.
Example 2-2: Result of an execution of the generated query.
Code:
------------------------------ Commands Entered ------------------------------
SELECT 'SYSTOOLS.HMON_ATM_INFO' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.HMON_ATM_INFO UNION ALL SELECT 'SYSTOOLS.HMON_COLLECTION' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.HMON_COLLECTION UNION ALL SELECT 'SYSTOOLS.POLICY' AS table_name , COUNT(*) AS count_rows FROM SYSTOOLS.POLICY ORDER BY 1;
------------------------------------------------------------------------------
TABLE_NAME COUNT_ROWS
------------------------ -----------
SYSTOOLS.HMON_ATM_INFO 326
SYSTOOLS.HMON_COLLECTION 0
SYSTOOLS.POLICY 5
3 record(s) selected.
|
Last edited by tonkuma; 12-23-11 at 20:05.
Reason: Edit the format of code.
|
12-29-11, 07:34
|
|
Registered User
|
|
Join Date: Oct 2007
Posts: 200
|
|
thanks all, it works
regds
Paul
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
sql conconate help
