Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433

    Unanswered: getting parameters of SP

    apart from looking in catalog tables ,is there any way by which
    i can get ddl for SP or atleast their parameters
    same way as we can do in tables or indexes

    describe table eone.tsys_user_mstr
    describe indexes for table eone.tsys_user_mstr

    --Rahul Singh
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    "describe table" also only looks at the catalog - and reformats the output a bit.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If the SP is SQL, then "select text from syscat.routines where routineschema = ? and routinename = ?" will get you the entire CREATE PROCEDURE statement. I do not know how to get this for a non_SQL SP.

    If you just want the parameters:

    select parmname,case rowtype when 'B' then 'INOUT' when 'C' then 'Result after Casting' when 'O' then 'OUT' when 'P' then 'IN' when 'R' then 'Result before Casting' else '??' as type, typeschema,typename,length,scale from syscat.routineparms where routineschema = ? and routinename = ?

    HTH

    Andy

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    All metadata for the various database objects is stored in the DB2 catalog tables (which should only be accessed through the views in the SYSCAT schema). You can use this metadata to piece together the CREATE PROCEDURE statements for non-SQL SPs. This is exactly what the "db2look" tool is doing.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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