Automated snapshot data

[Civilian_Target]

Is there any way in DB2 to automate the taking of a snapshot, for
example, a function that will cause a db2 snapshot to be taken every 5
minutes and written to a uniquely named file?

Am I better off attempting to do this with my poor knowledge of dos
batch files?

I'm running UDB 8.2 on Windows 2000.

Thanks.

[Udo Weigl]

Hi Civilian,

our product takes automated Snapshots and a lot more than this. We put
the Snapshot Data into a Database offer a GUI to analyze the data and
so on...

http://www.itgain.de/en/speedgain.html

If you have any Questions feel free to send me an email.

Udo Weigl
Civilian_Target <ta****@bigfoot.com> wrote in message news:<3b*************@individual.net>...

Is there any way in DB2 to automate the taking of a snapshot, for
example, a function that will cause a db2 snapshot to be taken every 5
minutes and written to a uniquely named file?

[Buck Nuggets]

Civilian_Target wrote:

Is there any way in DB2 to automate the taking of a snapshot, for
example, a function that will cause a db2 snapshot to be taken every 5 minutes and written to a uniquely named file?

Am I better off attempting to do this with my poor knowledge of dos
batch files?

Personally, I'd recommend working on your knowledge of batch files,
perl, python, or ruby. It's a "give a man a fish vs teach him how to
fish kind of thing": there are tons of administrative and development
tasks that can be automated via the shell, or a good interpreted
language. And even when commercial applications can perform the same
task, I find the script solutions often superior: since they can be
easily managed within a standard version control system, and easily
promoted along with the rest of the application code base. And you
don't waste a lot of time in procurement for commercial tools, only to
end up with insufficient licenses to cover every server you need.

And it's mostly very easy stuff. In the above example, I'd have a very
small script that connected to the database, ran a sql query to insert
the results into a dedicated table (using a snapshot table function),
then closed the connection. Scheduling & execution managed by the task
scheduler. Then you could query or export the data whenever you
wanted. Alternatively, you could use the snapshot command if you
preferred the formatting. More info on the snapshot table function:
http://publib.boulder.ibm.com/infoce...n/r0000294.htm
Good luck,

buck

[Hildo Biersma]

In article <3b*************@individual.net>,
Civilian_Target <ta****@bigfoot.com> wrote:

Is there any way in DB2 to automate the taking of a snapshot, for
example, a function that will cause a db2 snapshot to be taken every 5
minutes and written to a uniquely named file?

Snapshots are taken with the administrative API; the DB2 CLP uses the
administrative API to get snapshot data and then displays it in textual
format.

At work, I have written a perl XS module (XS is the perl-to-C API) that
invokes the Admin API to collect snapshot data every minute and stores
it (we keep 2 hours); this is used to drive performance monitoring,
troubleshooting, etc. (Not open sourced - I'm working on that.)

If you're comfortable with C, look at the IBM-supplied samples - they
are quite nice and show how to get snapshot data, parse and display it,
etc.

What I like most about the admin API is that you can get snapshot data
for multiple areas at the same time, e.g. a snapshot for both the
instance, all active databases, and the tablespaces, all in one chunk of
data. Also, getting a snapshot and then analyzing it in
your own code is less expensive for the database than using the snapshot
SQL functions and cheaper in terms of CPU time than running the DB2 CLP
and then parsing the output.

[Norm]

Here is a fairly cheap non-program method I use:

Create tables to hold snapshots:
Examples:

CREATE TABLE DB2ADMIN.SNAP_DBM
AS (SELECT * FROM TABLE( SYSPROC.SNAPSHOT_DBM(-1)) as SNAPSHOT_DBM)
WITH NO DATA
IN USERSPACE1;

CREATE TABLE DB2ADMIN.SNAP_DB
AS (SELECT * FROM TABLE( SYSPROC.SNAPSHOT_DATABASE( 'SAMPLE', -1 ))
as SNAPSHOT_DB)
WITH NO DATA
IN USERSPACE1;

CREATE TABLE DB2ADMIN.SNAP_BP
AS (SELECT * FROM TABLE( SYSPROC.SNAPSHOT_BP( 'SAMPLE', -1 )) as
SNAPSHOT_BP)
WITH NO DATA
IN USERSPACE1;

Insert into the tables using cron, windows scheduler, or DB2
scheduler:
CONNECT TO SAMPLE;

INSERT INTO DB2ADMIN.SNAP_DBM SELECT * FROM TABLE(
SYSPROC.SNAPSHOT_DBM(-1)) as SNAPSHOT_DBM;

INSERT INTO DB2ADMIN.SNAP_DB SELECT * FROM TABLE(
SYSPROC.SNAPSHOT_DATABASE( 'SAMPLE', -1 )) as SNAPSHOT_DB;

INSERT INTO DB2ADMIN.SNAP_BP SELECT * FROM TABLE( SYSPROC.SNAPSHOT_BP(
'SAMPLE', -1 )) as SNAPSHOT_BP;
Once they are in tables it is easy to run queries to look for problems
or roll them up for long term trend analysis.

I run clean-up jobs to delete snapshots older than a week.

Norm
hp*@guest.lunatech.com (Hildo Biersma) wrote in message news:<11***************@yin.lunatech.com>...

In article <3b*************@individual.net>,
Civilian_Target <ta****@bigfoot.com> wrote:

Is there any way in DB2 to automate the taking of a snapshot, for
example, a function that will cause a db2 snapshot to be taken every 5
minutes and written to a uniquely named file?

Snapshots are taken with the administrative API; the DB2 CLP uses the
administrative API to get snapshot data and then displays it in textual
format.

At work, I have written a perl XS module (XS is the perl-to-C API) that
invokes the Admin API to collect snapshot data every minute and stores
it (we keep 2 hours); this is used to drive performance monitoring,
troubleshooting, etc. (Not open sourced - I'm working on that.)

If you're comfortable with C, look at the IBM-supplied samples - they
are quite nice and show how to get snapshot data, parse and display it,
etc.

What I like most about the admin API is that you can get snapshot data
for multiple areas at the same time, e.g. a snapshot for both the
instance, all active databases, and the tablespaces, all in one chunk of
data. Also, getting a snapshot and then analyzing it in
your own code is less expensive for the database than using the snapshot
SQL functions and cheaper in terms of CPU time than running the DB2 CLP
and then parsing the output.