By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Fluctuating size of db2 backup, also db2 backup dates have problems

P: 6
Hi, i've spent all resources, except this one. Hopefully anyone there out could help me with a idea for this problem.

We have a db2 8.2 Enterprise Edition, on AIX 5.3 Platform, this is our production database. We had to do a full restore over this database for a problem with a tablespace. The problem cames after 5 days after full restore.

The problem is the following, we are having erratic, fluctuating size on our backups, and this is happening since 2 weeks ago, 5 days after full restore, as you can the following list of ls -l on 3 backups shows the error.

-rw-r----- 1 db2inst1 db2grp1 36435939328 Jan 26 01:10 PICASO.0.db2inst1.NODE0000.CATN0000.20080126004501 .001
Sun Jan 27 00:10:00 EST 2008

-rw-r----- 1 db2inst1 db2grp1 29490171904 Jan 27 01:05 PICASO.0.db2inst1.NODE0000.CATN0000.20080127004501 .001
Mon Jan 28 00:10:00 EST 2008

total 76685448
-rw-r----- 1 db2inst1 db2grp1 39262900224 Jan 28 01:12 PICASO.0.db2inst1.NODE0000.CATN0000.20080128004501 .001

The first day was : 33.9 G
Second day was:27.5G
Third day was:36.56G

The database according to control center in storage management is having a size of 27440MB, snapshot taken at:2008-01-26.

As you can see we have been having the up and down of the size of backups without reason, the database cant grow 9G in just one day, the max size of growning of this backup in one is 1G, and besides we don't delete huge information on this db, actually this database is transaccional is not a datawarehouse.

In the other hand, the backups dates according to db2 are different to OS. For example, according to DB2 the backup process finished at 01:40 am, but OS date says the backup finished at 01:08 am. Im pasting the db2 list history backup, so you can see other error with date:

Start Time: 20080126004501
End Time: 20080126014126
Status: A

Start Time: 20080127004501
End Time: 20080127013707
Status: A

Start Time: 20080128004501
End Time: 20080128014341
Status: A

DB2 does not show errors neither db2diag.log, neither db2ckbkp. Actually we have a shell to do automatize backup, the shell always runs a db2ckbkp, and always shows :
the Image Verification Complete - successful.

We ran a db2dart over our database and didnt say anything error, we restarted the database, and also operating system.

Now im going to tell you the operations what we did over database before this problem presented.

Four days before we cleaned 2 huges tables, one with 48G of size, and the other with 16G of size.

We started to run a shell to move the archive logs to a backup server 3 days before started problem, using rsync --remove-sent-files, and this shell ran every 10 mins, but we stoped this shell to see if this could be the reason for the problem.

These have been all changes or activities over this database.

Hopefully anyone over there out can give me a idea, why this is happing and what we could do.

Thanks in advance,
Jan 31 '08 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 127
Did you run reorg after cleaning your huge tables. Also, just cleaning of the tables don't move the highwatermark for tablespaces to a lower level and will still use the larger number of pages.
I believe you are taking online backups. This backup could actually be more than the total db size as along with all the data, their are some control information also which are stored in the backup. Do you have a DPF environment (I don't think so as there is only one file and that contains NODE0000 only)?
What is the size of the DB calculated from "call sysproc.GET_DBSIZE_INFO(?,?,?,0) "?
Feb 1 '08 #2

P: 6
Hi sakumar9, yes, i ran reorg and runstats over those tables, actually i run runstats and reorg over those tables every.
And you are rigth about the backup, we do backup online everyday on database. I was thinking about the highwatermark, and that was my explanation for the frist days when started the issue, but, for example today our backups sizes 10G when the last day was 15G, the backup size should grow, it should not ungrow.
What another thing it could cause this anomaly??
And my last question : Can i run sysproc.GET_DBSIZE_INFO(?,?,?,0)? in production hours??

Thanks in advance,
Feb 1 '08 #3

P: 6
Hi, the output of call get_dbsize_info(?,?,?,0) is the following :

Value of output parameters
Parameter Value : 2008-02-01-

Parameter Name : DATABASESIZE
Parameter Value : 34477105152

Parameter Value : 204347929088

Return Status = 0
Feb 1 '08 #4

Expert 100+
P: 297

is DB2 BACKUP PICASO ONLINE the complete backup command or are there any additional parameters?

Usually if your database gets UPDATEs mostly, it's the transaction logs that are growing quiet fast. Is your rsync script running again, or is it still stopped?

Perhaps you could do a nightly test: Create a fifo and backup the database to it while running a "wc -bytes" reading from that pipe. Do a reorg and repeat the backup/wc. Compare the number of bytes. Are they nearly the same or do they differ significantly?

Some other options for using with the BACKUP command are "COMPRESS" and "EXCLUDE LOGS".


Feb 2 '08 #5

P: 6
This is our script to backup database:
db2 connect to production ;
time db2 "BACKUP DATABASE production ONLINE TO "/backup_tsm/backup" WITH 2 BUFFERS B
db2 terminate;
time db2ckbkp -p /backup_tsm/backup/PRODUCTION*

About rsync --remote-sent-files, its for moving logs, after copy logs, remove them from database server, and Yes, its running currently, but crontab is running it 03:00 til 23:00, our backup is perfomed since 12:45 and normally finishs at 1:45 am, so during backups logs are not moved.
We tested stopping the shell to move logs and it seemed it was not the problem, still presenting the problem
Feb 2 '08 #6

Expert 100+
P: 297

is there some scheduled REORG, and how long does a REORG take? I'd try one right before doing a backup and see if the size starts leveling.


Feb 2 '08 #7

P: 6
yes, actually we run runstats and reorgs after the backup is made.
Backups finishs at 1:45am runstats and reorgs run at 4:00am.
Since saturday im monitoring the tablespaces, what i found interesting is one tablespaces, which stores the tables most big at database is having the same pages used as the highwatermark.
Let me explain this more clearly with a piece of the list of tablespaces show detail:

This was taken at Feb 2:

Total pages = 3072000
Useable pages = 3071808
Used pages = 818496
Free pages = 2253312
High water mark (pages) = 818496

This was taken at Feb 3:

Total pages = 3072000
Useable pages = 3071808
Used pages = 834816
Free pages = 2236992
High water mark (pages) = 834816

This was taken at Feb 4:

Total pages = 3072000
Useable pages = 3071808
Used pages = 859648
Free pages = 2212160
High water mark (pages) = 859648

This seems strange for me. What do you think about this?
Feb 4 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.