I'm a DB2 UDB DBA in a place where I've been looking at the production database. I was looking to see which packages (SYSCAT.PACKAGEDEP), stored procedures (ROUTINEDEP) or functions (FUNCDEP) are using our tables. Usually you'd see any DB2 object and its schema represented in PACKAGEDEP under the columns BNAME and BSCHEMA. However, not only am I not seeing any tables in our database that are referenced in PACKAGEDEP, but not in ROUTINEDEP or FUNCDEP either.

Is there some weird way DB2 UDB can be set up with Java so that you'd never see a reference to any of the tables a package, stored procedure, or function uses in the system catalog? I'd thought binding the package was required, and if a package accessed 17 tables, you'd see 17 rows for the package, maybe more if you look at indexes, but I'm not seeing ANY rows for our tables. This is a PRODUCTION database, not some flaky dev database where no one has done the binds yet.

I am not sure I understand your question. However, if you are talking about java applicaiton programs, they use dynamic SQL and the packages are not stored in the catalog for those.

.....and EVERYTHING in a typical JDBC non-SQLJ Java shop is going to be dynamic SQL and thus not stored in PACKAGEDEP, and if they're also unfortunately NOT using Stored Procedures and functions, how would one trace the usage of tables or other DB2 LUW objects in terms of usage? Perhaps this becomes more of a Java question, but it appears that, even if the SQL is being ISSUED from a Java program, you're saying that unlike, say, COBOL on z/OS, that there's no way from a database DBA level to trace usage. Is there some way of tracing usage that a Java architect would know?

What do you mean by "trac[ing] the usage of tables or otherobjects in terms of usage"? Could you describe the problem you're trying to solve?

.....is I need to figure out which Java programs use which DB2 objects, particularly tables. If you have a program using static SQL, there's a record of every use of DB2 objects by that program in the system catalog. With dynamic SQL, you've got no record in the system catalog. I'm guessing from the looks of things that there are probably tables, even in production, THAT AREN'T EVEN BEING USED - AT ALL - and I'd love to have some sort of way of knowing what Java programs - if any - use a particular table. I'm guessing there's some sort of way of doing that in Java itself in some sort of administrative tool (if not, there sure OUGHT to be a way!). So if there IS a way, I'd love to know THAT, and what that way is. Thanks.

You can monitor aggregate table usage via the MON_GET_TABLE() function or using the db2pd tool. I don't think there is a reliable way to match object usage to an individual application - this is by nature of dynamic SQL, which can be shared between multiple applications.

On the application side, you could enable the JDBC trace and parse the trace file to extract object referenced in the SQL statements there.

You could also refer to the application source code and design documentation.

You can use a Snapshot for Dynamic SQL to see all dynamic SQL that hits a database during the snapshot period (which is since last time dynamic package cache memory heap has been flushed). It is flushed by one the following:

db2 flush package cache dynamic (this is probably the simplist to use)
instance restart
database activation (not sure about this one)

You must have the Statement Monitor turned on, either in the DBM config, or with db2 update monitor switches ...