long and huge backup

[nibble]

guys, i have this informix in my ux box. previously, its takes 2 hours for its level 0 db backup (ontape) consuming 2 tapes.

we then deleted some records, reducing the database size to 50% of the original size. after then, we tried the backup and to our surprise, the backup time is still 2hours and still consumes 2 tapes.

why is this so? what should we do to reduce the backup time and database size

[Michael Tully · Accepted solution]

You may have deleted records, but the database still remains the same size. To reduce the size of a database generally there must be some type of purge fucntion, otherwise when data is removed, there are spaces left behind for newer records to occupy table space. For Informix, one would have to export the database and import it.

[Steven E. Protter]

Most databases alloocate space when they take extents. Reducing data alone does not decrease the amount of space they take on a backup.

You would have to go through a compression procedure to reduce the amount of space the data takes up.

This varies from database to database.

You might also need to defragement your data unless you are at one of the newer database releases that automatically defragments its data.

You might also want to consider what is called a OnlineJFS snapshot to get a quick copy of your database while its down bring it up and then let your backup run at a liesurely pace on the snapshot.

SEP

[A. Clay Stephenson]

You need to rethink the problem because no matter how fast a backup is -- it's not fast enough. Try something like this:
1) Shutdown database.
2) Using OnlineJFS snapof= mount option, make a snapshot mount of all your database filesystems --- takes only a few seconds
3) Restart the database
4) Backup the snapshots
5) Unmount the snapshots

Now for almost all the uptime of a "hot" backup, you have the safety of a "cold" backup --- and speed no longer matters because the database is running.

[Greg OBarr]

I know that in Oracle, the database size is dependent on the size of the tablespaces, not the number of records. You can have a 10Gb tablespace that has no records in it, but it will take the same amount of time and space to back it up as it would if it were full. If you want to reduce the backup time and size of the database, you have to reorg the database.

Say you have Tablespace A that is 10Gb in size, but only 10% used and you don't expect a lot of growth.Create a 1.5Gb temoorary tablespace, move the tables contained in Tablespace A into the temp tablespace, then delete Tablespace A and re-create it large enough to hold your data plus the growth buffer you need. THen move the tables back and delete the temporary tablespace.

[nibble]

thanks guys