Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    70

    Unanswered: describe table funtion

    Could someone tell me how I can easily get the columnnames of a table function?
    I tried with:

    describe select * from table(<TABLE_FUNCTION_NAME>(null,null, -1)) as t1

    but the parameters in brackets are not the same for all table functions, and I don't know a quick way to have this information (other than check for each of those numerous table functions in the documentation).

    Thanks in advance
    Regards
    Renaud

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    This example CLP scriptlet might help - you can drop the table and view after you've got what you need:

    Code:
    update command options using s on c off;
    
    create or replace view someschema.high_reads as 
    SELECT varchar(tabschema,20) as tabschema, 
           varchar(tabname,20) as tabname, 
           sum(rows_read) as total_rows_read, 
           sum(rows_inserted) as total_rows_inserted, 
           sum(rows_updated) as total_rows_updated, 
           sum(rows_deleted) as total_rows_deleted 
    FROM TABLE(MON_GET_TABLE('','',-2)) AS t 
    GROUP BY tabschema, tabname 
    with ur;
    
    
    create table myschema.my_high_reads like someschema.high_reads ;
    
    commit;
    -- 
    -- show column definitions in columnar format...
    --
    ! db2 describe table myschema.my_high_reads ;
    --
    --
    -- show column definitions as DDL...
    --
    ! db2look  -d sample -e -t myschema.my_high_reads ;

  3. #3
    Join Date
    Aug 2012
    Posts
    70
    thanks for the reply, but unfortunately this is not exactly what I need.

    For eyample:
    I would like to have the names of the columns of the table functions:
    MON_GET_CONNECTION
    and
    MON_GET_WORKLOAD

    I cannot find them in the catalog view syscat.colnames because they are not regular tables

    I can use the following commands:
    for MON_GET_CONNECTION
    db2 "describe select * from table(MON_GET_CONNECTION(null, null, -1)) as T1"
    => 3 Parameters : null,null,-1

    for MON_GET_WORKLOAD
    db2 "describe select * from table(MON_GET_WORKLOAD(null, null, -1)) as T1"
    => 2 Parameters : null,-1

    The parameters are not the same for all table functions, so to get the list of the columns of any table function I need to know how many parameters are requested:
    db2 "describe select * from table(<TABLE_FUNCTION_NAME>(parameter1, parameter2,... )) as t1"

    Where can I get rapidly this information (without having to check in the documentation for each table function)

    Thanks
    Renaud

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Renaud, ever thought of studying examples and running them ?

    Here is a sample with MON_GET_CONNECTION, and remember you can use db2look (as shown previously if you want the DDL in addition to the columnar output of describe):

    Code:
    update command options using s on c off;
    
    create or replace view my_mon_get_connection as 
    SELECT *
    FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t 
    ;
    
    create table tb_mon_get_connection like my_mon_get_connection ;
    
    
    
    commit;
    
    describe table my_mon_get_connection ;
    
    drop view my_mon_get_connection;
    drop table tb_mon_get_connection; 
    commit;

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Renaud,

    try this:
    Code:
    select p.rowtype, p.ordinal, p.parmname
    from syscat.routines r
    join syscat.routineparms p on r.routineschema=p.routineschema and r.specificname=p.specificname
    where r.routineschema='SYSPROC' and r.routinename='MON_GET_CONNECTION'
    order by p.rowtype, p.ordinal
    Look at the syscat.routineparms for the rowtype values description.
    Regards,
    Mark.

  6. #6
    Join Date
    Aug 2012
    Posts
    70
    Hi mark
    Thanks a lot, this is what I was looking for.
    best regards
    Renaud

Posting Permissions

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