Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    246

    Unanswered: 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

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Try this

    db2 -x " select 'select count(*) , ''' || tabname || ''' from ' || trim(tabschema) || '.' || tabname || ' union' as c1 from syscat.tables " > select.txt


    Satya...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 11:06. Reason: Add SUBSTR() and ORDER BY

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 21:05. Reason: Edit the format of code.

  6. #6
    Join Date
    Oct 2007
    Posts
    246
    thanks all, it works

    regds
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •