How can we View the DB2 Procedure and How can we execute the DB2 Procedure and just see the output of the procedure using DB2

Question

How can we View the DB2 Procedure (I mean what logic they have written ) and How can we execute the DB2 Procedure and just see the output of the procedure using DB2

Themselves

Answer 1

DB2 stores the system related tables under syscat schema. So a query on syscat.routines will give you the Stored Procedure content.

A typical example: if you got a stored procedure by the name update_employee, the below query works:

select text from syscat.routines where routinename = 'update_employee'

Using the db2 describe command, you can see the table schema and can determine which all columns you want to view.

Invoking a stored procedure is already answered in "How to call a stored procedure in IBM System i Access for Windows GUI Tool" and you can refer that. (Eg: call myStoredProc(parm1, parm2, ?); )

I suggest you read the DB2 stored procedure details from IBM website.

Gibbon

For the AS/400, the SQL would be "select routine_definition from sysroutines where routine_name = 'update_employee';". You could further constrain by schema using "routine_schema = 'myschema'" – Magnetron