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

"describe table" also only looks at the catalog - and reformats the output a bit.

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

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.