Re: identify changes for level 1 backup?

davidegrove@gmail.com wrote: > On Oct 20, 1:11 pm, Fernando Nunes <domusonl...@gmail.com> wrote: >> davidegr...@gmail.com wrote: > >>> When I see a >>> sudden, large increase in the size of a Level 1, I would like to know >>> what caused it. >> What I tried to explain is that although it would be possible, You'd have to >> run a query that would need to read all the pages in the engine. It's the same >> process the backup uses... >> >> The information stored in a level 1 image is useless without the level 0. It's >> just a bunch of pages that changed since the last level 0. >> > > Thank you. > > At the risk of beating a dead horse, that kind of info could tell me > what tables are being changed. I don't think I really care about what > data is actually being changed. For instance, a list of table names, > and the number of changed pages foreacht table would, I think, be > useful. > > Regards, > > DG Don't worry about the horse... I'm not the best person to explain this, but I'll try. Each page in the instance has a "field" called timestamp. Unfortunately this "timestamp" is not (and could not be) a "human timestamp". You can think about it as an internal clock that ticks every time somethings are changed in the instance. Whenever a level 0 backup starts, the backup utility starts to copy ALL the pages and the timestamp that was current at the begining of the backup is saved. Any page that is changed during the backup get's it's original image copied to the temporary dbspaces. After the end of each dbspace backup, these images are copied to the backup device (this is needed to get a consistent physical image without holding transactions). Now... anytime a page is changed, it's timestamp is changed. The current timestamp get's "stamped" on the page. Whenever a level 1 backup starts, the engine will read the pages, checking if it's timestamp is higher than the one saved when you did the level 0. If it's higher, that page was changed after the level 0 started and as such it needs to be saved into this level 1 backup. Now, you have access to all the pages metadata (timestamp included) through sysmaster views. Theoretically, you could query these views, and read all the pages that contain a timestamp higher than the one saved by the level 0. Why do I say "theoretically"? Because running this query would force the engine to read all the pages (full page, not only metadata). With the page physical address (also included in the metadata of the page), you could find out what partition/table the page belongs to. This is highly resource intensive, and your performance would fall. It would be like doing to backups... Other options you have: . Reset instance statistics at around midnight (onstat -z) . Check what were the partitions more heavily used with things like "onstat -g ppf" (ppf = partition profiles. You'll need TBLSPACE_STATS 1 to use this) This info is also available through sysmaster views This would allow you to see the most used partitions without too much overhead (TBLSPACE_STATS will make the engine work more, but it's acceptable in most situations - I use it everywhere) Please check the manuals for: - TBLSPACE_STATS - onstat -g ppf - sysmaster views Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...