Hi Guys ,

I am taking DB2 Online backups through TSM API daily. When i check the database configuration parameters , it shows me " Number of database backup verions to maintain"= 12.

This is also shown when i check " db2 list history db " it showed me 12 versions of backups which is ok, but problem is that when i run "db2adutl query" it showed me 69 or 70 entries like that:::

1 Time: 20060730142501 Oldest log: S0014176.LOG DB Partition Number: 0
Sessions: 1
2 Time: 20060729142501 Oldest log: S0014141.LOG DB Partition Number: 0
Sessions: 1
3 Time: 20060728142502 Oldest log: S0014100.LOG DB Partition Number: 0
Sessions: 1
4 Time: 20060727142502 Oldest log: S0014074.LOG DB Partition Number: 0
Sessions: 1
5 Time: 20060726142502 Oldest log: S0014048.LOG DB Partition Number: 0
Sessions: 1


So my question is that how can i know whether DB2 is expiring backup versions or not, should i try " Expire inventory " explicitly for expiring DB2 backups or if there are any scripts to expire DB2 backups????

And most annoying , why is the difference between db2 list history db and db2adutl command, On which command should i rely ????

Env is Db2 8.1.3 with TSM 5.3 on AIX

Regards

Khurram

Have you tried db2adutl with option delete?

Well, not much to get annoyed if you understand where the information come from :

list history command is the one that extracts information as db2 'knows' and that's why the number of backups is in line with config parm value.

db2adutl is a db2 command that is a wrapper to a TSM command. db2adutl gives an easy option to access DB2 related information. for those who do not have access to TSM directly or do not have experience with TSM to know all command options. db2 does not control TSM. Therefore, unless you 'delete' from TSM, the backups are still going to stick around ...

Even though history has been deleted on the db2 side, you can still restore older backups that are available in TSM. As you can see, you do not have to expire all backups in TSM just because DB2 is aware of only the last 12 ... Therefore, you can retain, for example. month-end or year-end backups for as long as you like ...

HTH

Sathyaram

Hi

First of all many thanks for this great explanation and support!

So as per your explanation , Db2 backups are still there in TSM storage pools
Infact i really do not need all the older backups as our policy is to keep only latest 12 or 13 online backups and that's it

So question now comes , how to expire all these older DB2 backups ( retaining only latest 12 or 13 backups ), Should i try db2adutl with delete command???.... Does anybody have any scripts to delete these older db2 backups??? I have heared that any IBM red book contain scripts for such backups expiry but really miss name of this red book...

And another question is that if DB2 has forgetten these older backups ( except 12 or 13 backups ) how we can restore it as these are now not present in db2 history file .....

I' ll look forward to have views and suggestions on the subject!!

Regards

Huyed Jones

Sorry that I do not have a script ... You should be able to find the syntax and option for db2adutl delete , in the db2 manuals. You will be able to expire both backup images and logs.

http://publib.boulder.ibm.com/infoce...help/index.jsp

When you restore the database, you will have to make a reference to the backup image , by using the 'TAKEN AT' clause in the backup command. Therefore DB2 will be able to identify and restore the image ... [ Read the RESTORE command section in the manuals ]
Of course, you cannot use the automatic incremental restore if you do not have history entires, but doing manually is possible

Sathyaram

The syntax for delete is (f.ex):

"db2adutl delete older than <timestamp> database <database>"

Just type db2adutl without flags at the command prompt to
see the complete syntax.

The PROBLEM in my experience is that the instance owner typically
does not have the privilege to perform these operations on the
backup media - and will be refused.

You can try asking your root to do it but sometimes the retention period
of backup images is solely dependent on the TSM backup policy for that node
and I don't think it can be superseeded.

However most times this will result in the Backup Images becoming inactive
and they will disappear from your list unless you specifically ask for them

In other words you need a long hard talk with your backup man
to make them totally disappear :-)

Good Luck