Results 1 to 7 of 7
  1. #1
    Join Date
    May 2007
    Posts
    5

    Unanswered: Does one NOT always see tables Java uses in PACKAGEDEP?

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2007
    Posts
    5

    First, thanks for your help thus far, but if you're right....

    .....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?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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?

  5. #5
    Join Date
    May 2007
    Posts
    5

    The problem I'm trying to solve for Java and DB2....

    .....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.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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 ...
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •