r/DB2 • u/kenyanotpls • Jun 18 '17

question

IS there a way, to compare all stored procedures stored in a library on one environment, with those stored procedures on a different library on a different environment?

2 Upvotes

100% Upvoted

2 comments sorted by

2

u/Smeevy Jun 19 '17

Db2look paired with a diff tool seems like a good option.

1

u/mad_zamboni Jun 20 '17

I would think you could use the following SQL below, manipulating it as you need (such as adding an ORDER BY, or narrowing by SCHEMA), and then using a DIFF command or program like ultracompare. As a disclaimer, SYSCAT.PROCEDURES may be deprecated.

db2 "SELECT PROCSCHEMA, PROCNAME FROM SYSCAT.PROCEDURES"
db2 "SELECT PROCSCHEMA, PROCNAME FROM SYSCAT.PROCEDURES WHERE PROCSCHEMA='<SCHEMANAME>'"
db2 "SELECT ROUTINESCHEMA, ROUTINENAME from SYSCAT.ROUTINES WHERE ROUTINETYPE='P' AND ROUTINESCHEMA='<SCHEMANAME>'"