Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: Db2 - Stored Procedure Size

    I want to write a script to get the size(In bytes) of all the Stored procedures in DB2, but i am afraid there isnt any system tablewhich contain this information.
    Please message me at asharies@rediffmail.com

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    SELECT procschema,procname,length(text) from syscat.procedures
    (gives length of each procedure)


    SELECT SUM(length(text)) from syscat.procedures
    (gives total length)

    This works for DB2 V7.2 LUW and only for SQL procedures.

    HTH

    Andy

  3. #3
    Join Date
    Jul 2003
    Posts
    2
    Thanks Andy, This script works, very well indeed.

    But i was just wondering that there is some equivalent of "user_object_size" table as we have in ORACLE, where from we can have the exact size of all the database objects.

    Thanks in advance.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no simple way. You could come up with a query that can give you the upper limit of the size of the tables. Look at the SYSCAT definations in the appendix of the SQL manual. Tables, for example,
    can be derived using the npages column. i.e

    SELECT t.tabschema,t.tabname,t.npages*ts.pagesize from syscat.tables as t,syscat.tablespaces as ts where t.tbspaceid = ts.tbspaceid

    For the other objects look at their respective SYSCAT definition to see if a size can be determined.

    Note: for the above to work, the statistics must be up-to-date. i.e. run runstats before you run the query.

    HTH

    Andy

    Originally posted by asharies
    Thanks Andy, This script works, very well indeed.

    But i was just wondering that there is some equivalent of "user_object_size" table as we have in ORACLE, where from we can have the exact size of all the database objects.

    Thanks in advance.

Posting Permissions

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