I need to select all routines names from a DB2 luw.
I saw the following catalog tables:
- sysibm.routines
- syscat.routines
If I run the following queries:
select count(*) from sysibm.routines;
select count(*) from syscat.routines;
There is a big Difference between the results.
Can anyone tell what is the difference between these views and which one should be used for the task of selecting all routines names?
Best Answer
Both SYSIBM.ROUTINES and SYSCAT.ROUTINES are actually views over the "real" catalog table, SYSIBM.SYSROUTINES. You can grab the SQL definition for each view like so:
I won't post the results here as I've no idea if that's considered proprietary by IBM (it is technically source from DB2 itself after all). Anyway, if you look through the view source (you'll want to re-format it first with some SQL lint-like tool!) you'll notice several things:
Some background:
In other words, I'd generally treat the SYSCAT results as "gospel" but if you want to understand exactly what they're showing then you need to dive into their source.