Craig Tobias wrote:

Jean-Marc,

In v8.1, we created some stored procedures to capture and save storage
snapshots. Snapshots can be captured for Databases, Table spaces, Tables,
Indexes, and Database Partition Groups. To capture storage data (including
estimated size) for all objects (including tables) in a database, you should
capture a database snapshot.

Before capturing a snapshot, you must create the storage management tables
(where all snapshot data is stored). Run the following command to create the
storage tables, and specify a table space for the tables.

db2 connect to <db-alias>
db2 call create_storagemgmt_tables('<tablespace>')

Now, capture a snapshot at the database level... this can take a while for
large databases. You may also want to update your table statistics using
the Runstats utility before running the storage snapshot. To capture a
snapshot, run the following command:

db2 call capture_storagemgmt_info(0, ' ', '<db-alias>')

The table related data is stored in the SYSTOOLS.STMG_TABLE table. You also
need to do a name lookup in the SYSTOOLS.STMG_OBJECT table. To query the
storage management tables for estimated table size, run the following
command...

SELECT
MAX(tables.STMG_TIMESTAMP) as SNAPSHOT_TIMESTAMP,
tables.OBJ_ID,
object.OBJ_NAME,
object.obj_schema,
tables.ESTIMATED_SIZE
FROM
SYSTOOLS.STMG_TABLE tables
INNER JOIN
SYSTOOLS.STMG_OBJECT object
ON tables.obj_id = object.obj_id AND tables.stmg_timestamp =
object.stmg_timestamp
GROUP BY tables.STMG_TIMESTAMP, tables.ESTIMATED_SIZE, tables.obj_id,
object.obj_name, object.obj_schema


The number of bytes used by the table is displayed in the ESTIMATED_SIZE
column.

You can also delete historical snapshots by deleting entries from the
SYSTOOLS.STMG_ROOT_OBJECT table. To delete all historical data, run the
following query:

DELETE FROM SYSTOOLS.STMG_ROOT_OBJECT

There is also a stored procedure which will drop all of the storage
management tables.

db2 call drop_storagemgmt_tables(0)


All of the snapshot data can easily be viewed using the storage management
tool which is launched from the DB2 Control Center, Database > Manage
Storage...

The storage management tool provides detailed historical analysis of the
captured snapshot data for all storage related objects in the database, as
well as a way to schedule regular snapshots, and delete historical
snapshots.

Craig Tobias
DB2 Admin. Tools Development
IBM Toronto Lab

"Jean-Marc Blaise" <blaise@nospam.com>; wrote in message
news:c3e50t$ao8c$1@news.boulder.ibm.com...

>> Dear all,
>>
>> It seems to me the only way to estimate the size of tables (besides doing
>> your own stuff) is still only available in the Control Center.
>>
>> When will we have a SQL Table function that allows to automatize the
>> calculation, very interesting, especially for datawarehouses ?
>>
>> Regards,
>>
>> Jean-Marc
>>
>>

Hi Jean-Marc,

You can capture a snapshot at the tablespace level using the same stored procedure, but with different parameters.

The stored proc. is defined as follows...

sysproc.capture_storagemgmt_info(INTEGER obj_type, VARCHAR obj_schema, VARCHAR obj_name)

Where obj_type is the type of object as defined in table systools.stmg_object_type, obj_schema is the schema for the object (only applicable for table and index snapshots), and obj_name is the name of the database object you want to capture a snapshot of.

ex. To capture a snapshot of tablespace 'USERSPACE1', issue the following command:

db2 call capture_storagemgmt_info(2, ' ', 'USERSPACE1')

As an alternative, you can select the "Manage Storage..." option from a table space in the Control Center.

The option of performing a Runstats before capturing a storage snapshot would be available through the Storage Management GUI, and would be fully customizable and would provide the same functionality as the Runstats GUI in the Control Center.

Thanks for your interest.

-- Craig


"Jean-Marc Blaise" <blaise@nospam.com>; wrote in message
news:c4chj5$97t8$1@news.boulder.ibm.com...

>> Jason,
>>
>> Thanks for pointing out this article. I was refering to Craig's comment:
>> "capture a snapshot at the database level... this can take a while for

large

>> databases."
>>
>> So if you added a runstat ability, would it be customizable fully,

according

>> to the runstats command ? And of course, running it on a full database

would

>> take a long long time (even in a EEE context).
>>
>> In a datawarehouse, I find very practical and less troublesome to process

on

>> a tablespace basis, because maintenance is smoother, so the issue is not
>> really scheduling.
>>
>> Cheers,
>>
>> Jean-Marc
>>
>> <jgartner_nospam@yahoo.com>; a Иcrit dans le message de
>> news:c4c0al$5bu6$2@news.boulder.ibm.com...
>> Jean-Marc,
>> If you use the Control Center, snapshots can definitely be scheduled.
>> You can get to the graphical way through Database or Tablespace ->
>> Manage Storage. Depending on what object you launch Manage Storage
>> from is the type of snapshot you will get.
>>
>> Also, we are looking into adding the ability to do runstats just before
>> the
>> storage snapshot so your information can be up to date.
>>
>> If you want more information on our scheduling capabilities, I suggest you
>> try:
>>
>>

http://www-106.ibm.com/developerworks/db2/library/techarticle/0301gartner/0301gartner.html

>>
>> JG
>>
>
>>> >Hi Craig,
>>> >
>>> >Thanks for your detailed explanation.
>>> >
>>> >Your comment about VLDB that would take time make me think that it would
>
>> be
>
>>> >nice to be able to schedule a snapshot on a per tablespace basis. In a
>>> >datawarehouse specifically, you organize your tables carefully (I hope
>
>> :-))
>
>>>> >>regarding dependencies in exploitation, scheduling (weekly, monthly
>
>> ..),
>
>>> >and you can run stats after data feeding process.
>>> >
>>> >So you could think about taking a snapshot for storage after each
>
>> tablespace
>
>>> >has been filled and after a runstat has eventually been done.
>>> >
>>> >What do you think about this "feature request" ?
>>> >
>>> >Best regards,
>>> >
>>> >Jean-Marc
>
>>
>>
>> "Craig Tobias" <ctobias@ca.ibm.com>; a Иcrit dans le message de
>> news:c49sv3$3i8$1@news.boulder.ibm.com...
>
>>> > Jean-Marc,
>>> >
>>> > In v8.1, we created some stored procedures to capture and save storage
>>> > snapshots. Snapshots can be captured for Databases, Table spaces,
>
>> Tables,
>
>>> > Indexes, and Database Partition Groups. To capture storage data
>
>> (including
>
>>> > estimated size) for all objects (including tables) in a database, you
>
>> should
>
>>> > capture a database snapshot.
>>> >
>>> > Before capturing a snapshot, you must create the storage management
>
>> tables
>
>>> > (where all snapshot data is stored). Run the following command to create
>
>> the
>
>>> > storage tables, and specify a table space for the tables.
>>> >
>>> > db2 connect to <db-alias>
>>> > db2 call create_storagemgmt_tables('<tablespace>')
>>> >
>>> > Now, capture a snapshot at the database level... this can take a while
>
>> for
>
>>> > large databases. You may also want to update your table statistics
>
>> using
>
>>> > the Runstats utility before running the storage snapshot. To capture a
>>> > snapshot, run the following command:
>>> >
>>> > db2 call capture_storagemgmt_info(0, ' ', '<db-alias>')
>>> >
>>> > The table related data is stored in the SYSTOOLS.STMG_TABLE table. You
>
>> also
>
>>> > need to do a name lookup in the SYSTOOLS.STMG_OBJECT table. To query
>
>> the
>
>>> > storage management tables for estimated table size, run the following
>>> > command...
>>> >
>>> > SELECT
>>> > MAX(tables.STMG_TIMESTAMP) as SNAPSHOT_TIMESTAMP,
>>> > tables.OBJ_ID,
>>> > object.OBJ_NAME,
>>> > object.obj_schema,
>>> > tables.ESTIMATED_SIZE
>>> > FROM
>>> > SYSTOOLS.STMG_TABLE tables
>>> > INNER JOIN
>>> > SYSTOOLS.STMG_OBJECT object
>>> > ON tables.obj_id = object.obj_id AND tables.stmg_timestamp =
>>> > object.stmg_timestamp
>>> > GROUP BY tables.STMG_TIMESTAMP, tables.ESTIMATED_SIZE, tables.obj_id,
>>> > object.obj_name, object.obj_schema
>>> >
>>> >
>>> > The number of bytes used by the table is displayed in the ESTIMATED_SIZE
>>> > column.
>>> >
>>> > You can also delete historical snapshots by deleting entries from the
>>> > SYSTOOLS.STMG_ROOT_OBJECT table. To delete all historical data, run
>
>> the
>
>>> > following query:
>>> >
>>> > DELETE FROM SYSTOOLS.STMG_ROOT_OBJECT
>>> >
>>> > There is also a stored procedure which will drop all of the storage
>>> > management tables.
>>> >
>>> > db2 call drop_storagemgmt_tables(0)
>>> >
>>> >
>>> > All of the snapshot data can easily be viewed using the storage
>
>> management
>
>>> > tool which is launched from the DB2 Control Center, Database > Manage
>>> > Storage...
>>> >
>>> > The storage management tool provides detailed historical analysis of the
>>> > captured snapshot data for all storage related objects in the database,
>
>> as
>
>>> > well as a way to schedule regular snapshots, and delete historical
>>> > snapshots.
>>> >
>>> > Craig Tobias
>>> > DB2 Admin. Tools Development
>>> > IBM Toronto Lab
>>> >
>>> > "Jean-Marc Blaise" <blaise@nospam.com>; wrote in message
>>> > news:c3e50t$ao8c$1@news.boulder.ibm.com...
>>
>>>> > > Dear all,
>>>> > >
>>>> > > It seems to me the only way to estimate the size of tables (besides
>
>> doing
>
>>>> > > your own stuff) is still only available in the Control Center.
>>>> > >
>>>> > > When will we have a SQL Table function that allows to automatize the
>>>> > > calculation, very interesting, especially for datawarehouses ?
>>>> > >
>>>> > > Regards,
>>>> > >
>>>> > > Jean-Marc
>>>> > >
>>>> > >
>
>>
>>