V_S_H_Satish wrote:
The following colums i need in the daily check lists
Instance Name
Instance Status - if open means online, if not offline
Database Name
Database Status - if open means online, if not offline
Free space
error logs
Backup start
Backup end
Quite a few of these are probably covered by the GET HEALTH SNAPSHOT
command. Here's some example outputs from one of my test boxes which
just has a single instance (db2inst1) and a single almost empty
database (DOCTEST)...
When used with "FOR DATABASE MANAGER" you get info on the instances:
$ db2 GET HEALTH SNAPSHOT FOR DATABASE MANAGER
Database Manager Health Snapshot
Node type = Enterprise Server
Edition with local and remote clients
Instance name = db2inst1
Snapshot timestamp = 04/22/2006
01:03:08.563202
Number of database partitions in DB2 instance = 1
Start Database Manager timestamp = 04/04/2006
21:45:56.106610
Instance highest severity alert state = Attention
Health Indicators:
Indicator Name = db2.db2_op_stat us
Value = 0
Evaluation timestamp = 04/22/2006
01:00:57.263136
Alert state = Normal
Indicator Name = db2.sort_privme m_util
Value = 0
Unit = %
Evaluation timestamp = 04/22/2006
01:00:57.263136
Alert state = Normal
Indicator Name = db2.mon_heap_ut il
Value = 43
Unit = %
Evaluation timestamp = 04/22/2006
01:00:57.263136
Alert state = Normal
When used with "FOR ALL DATABASES" you get (unsurprisingly ) info on all
the databases:
$ db2 GET HEALTH SNAPSHOT FOR ALL DATABASES
Database Health Snapshot
Snapshot timestamp = 04/22/2006 01:03:27.617192
Database name = DOCTEST
Database path =
/var/db2inst1/NODE0000/SQL00004/
Input database alias =
Operating system running at database server= LINUX
Location of the database = Local
Database highest severity alert state = Attention
Health Indicators:
Indicator Name = db.db_op_status
Value = 0
Evaluation timestamp = 04/22/2006
01:00:57.263136
Alert state = Normal
Indicator Name = db.sort_shrmem_ util
Value = 0
Unit = %
Evaluation timestamp = 04/22/2006
01:00:57.263136
Alert state = Normal
[...lots more rather boring output snipped...]
Indicator Name = db.db_backup_re q
Value = 1
Evaluation timestamp = 04/21/2006
23:45:57.365087
Alert state = Attention
As you can see from the above there's quite clear indicator lines
beginning with "Alert state" which you could use for grepping through
the output. For example, from the above it shouldn't be too tricky to
bash together a little cron script which could periodically run the two
GET HEALTH SNAPSHOT commands above, and only mail you the result if
either contain "Alert state = Attention" (or better still "Alert state
= [anything other than normal]").
For descriptions of the indicators (e.g. "db.db_backup_r eq") use the
GET DESCRIPTION FOR HEALTH INDICATOR command:
$ db2 GET DESCRIPTION FOR HEALTH INDICATOR db.db_backup_re q
DESCRIPTION FOR db.db_backup_re q
This health indicator tracks the need for a backup on the database.
Backups should be taken regularly as part of a recovery strategy to
protect your data against the possibility of loss in the event of a
hardware or software failure.
This health indicator determines when a database backup is required
based on the time elapsed and amount of data changed since the last
backup. Refer to the DB2 Information Center for information about how
to update these settings.
[snip]
Next ... free space. Okay, the LIST TABLESPACES SHOW DETAIL command
should take care of that (though it's output is rather verbose, so you
might want to process it a bit ... I've got a shell script lying around
somewhere that tidied it up a bit, I'll see if I can dig it up):
$ db2 CONNECT TO DOCTEST
Database Connection Information
Database server = DB2/LINUX 8.2.4
SQL authorization ID = DAVE
Local database alias = DOCTEST
$ db2 LIST TABLESPACES SHOW DETAIL
Tablespaces for Current Database
[snip]
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4064
[here's the interesting bit vvvv]
Used pages = 1824
Free pages = 2240
High water mark (pages) = 1824
[here's the interesting bit ^^^^]
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
[snip]
As shown above, an active database connection is needed for this, so
you'll need to write a little script which iterates over all the local
databases connecting to each, running the above command, and possibly
deciding whether to bother including the verbose output (e.g. if a
tablespace is getting full).
That said, with the introduction of AUTOMATIC STORAGE (i.e. automatic
expansion) for DMS tablespaces in whichever fixpak introduced it, this
is less of an issue than it used to be (if you're using AUTOMATIC
STORAGE you can probably get away with just checking the available
space on whatever partitions are home to your tablespaces).
Hmmm ... error logs. Not sure exactly what you're looking for here. I
guess db2diag.log might be worth a look (take a look at the db2diag
command in the Command Reference for more information:
http://tinyurl.com/fsgat)
Final item on the shopping list ... backup history. Easy one this: use
the LIST HISTORY BACKUP command. For example, to list all backups taken:
$ db2 LIST HISTORY BACKUP ALL FOR DB DOCTEST
List History File for DOCTEST
Number of matching file entries = 0
(yeah, it's a test database, so I've never backed it up)
Or to list backups since a particular date/time:
$ db2 LIST HISTORY BACKUP SINCE 20060417000000 FOR DB DOCTEST
The timestamp format is YYYYMMDDHHMMSS. Wouldn't be difficult to knock
together a little cron script that lists the backup history over the
last week, to be mailed to you. Though, the db.db_backup_re q health
parameter mentioned above may be enough for your purposes.
HTH,
Dave.
--
