Question Bi File Doubts

[DBA_learner]

Hello Team,

I am new to DBA activities and started learning the same. Please help me out with below mentioned queries regarding BI files.

1. When .bi file gets updated. I thought is should be only when we update any DB field and transaction hasn’t completed yet or DB changes are partially committed. I tried this and found that it’s initially updated at the time we connect to the database using command “pro db-name”. So this BI file remained open after DB connection or gets closed after this connection.

2. When .bi file store previous value (before image). As per my understanding, whenever we update a DB field value and transaction is still running then previous value should get stored in .bi file and .bi file should get updated before transaction ends. I tried below mentioned scenario and found that initially .bi gets updated on pause statement but later-on .bi gets updated only when transaction ends (after END of for each customer).

For example:

 FOR EACH customer exclusive-lock:

 UPDATE customer.name.

 PAUSE 100.

 END.

3. I have taken above scenario and added DO TRANSACTION above this code. BI update with DO TRANSACTION is also non understandable. Sometime .bi gets updated within the transaction (with chunk of records) and sometime only after the transaction.


4. Size of BI file keeps growing whenever we are in a long transaction, so .bi file should get updated (by time) when we keep updating values inside a transaction (that’s not happening). As I observed, .bi file gets updated for chunk of updated records (inside transaction).


5. When transaction ends successfully then what happen to the before images (I think before images should get deleted and space is vacated) and when transaction aborted then when will happen to these bi images (Before images are copied to DB for recovering previous value), Please suggest.


6. Whenever we enable AI images for any database then BI file is automatically truncated, why?


Please suggest.

 

[RealHeavyDude]

Why doubts?

To answer some of your questions:

1. As soon as any process accesses the database at least the last open time stamp gets updated - which of course can only be updated as part of a database transaction.
2. I don't understand what you mean - the behavior you describe is absolutely in line with my understanding of how the before image gets updated. You need to take into account that the before image doesn't get updated synchronously - it gets updated asynchronously in the background. If you have an enterprise database license, this update is done by the before image writer. Furthermore there is an before image write delay which defaults to 3 seconds. Which means that it can take up to 3 seconds until transaction notes are written to the before image on disk.
3. See above (2.)
4. See above (2.)
5. The before image is logally structured in clusters. All clusters which do not contain any transaction notes of open transactions will be reused. Long running transactions cause before image growth on disk because any clusters occupied by that transaction - from the one where the transaction started to the one where the transaction either is commited or rolled back - can't be reused.
6. After image only works with backups of the database. Usually you restore the last good backup and roll forward all after image entents since when the backup started. When you backup the database with the probkup utility the it will automatically swith to the next free after image extent at the beginning of the backup. The last upate time stamp of the backup must match the begin time stamp of the after image. As soon as you start after imaging, there can't be any open transactions that's why the before image gets truncated. The truncate before image is a 2-way process: It undos all open transactions and redos all committed transactions that have not been propogated to the database. That is because all disk activity is happen asynchronously in the background in a particular order: Before image - after image - database.

You need to be aware that, for performance reasons, any disk writes are happen asynchronously in the background. That means that you might detect disk writes that are not particualy in line with the code executed in an ABL client session. Furthermore you should not confuse the client executing ABL statements versus the database server handling the database transactions.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Doubts = questions. It's a poor translation.

 

[DBA_learner]

Thanks a lot RealHeavyDude for sharing this important information!!


Almost all of my questions are cleared except one:


I understood that bi file update is an asynchronous process and this update will not be in-line with ABL code execution. But as per below mention code bi file is not getting updated on pause statement (prior transaction end) even if time of this pause statement is very long and its being updated every time transaction ends, at the end of every iteration of for each. Apparently, bi file is getting updated for every transaction (for each iteration) for clearing data from bi file because transaction end).

FOR EACH customer EXCLUSIVE-LOCK:

  UPDATE customer.name.

  PAUSE  1000. 

END.

Please suggest.

Best Regards.

 

[RealHeavyDude]

Why do you expect the before image on disk get updated on the pause statement?

Updating the before image on disk on the pause statement does not make any sense to me.
It will get updated at the end of the buffer and transaction scope - which is the end statement of the for each block.

Buffer and transaction scope and the infamous release statement play a role as to when something gets written back on disk. If the buffer scope is smaller as the transaction scope or you release the buffer before the end of the transaction scope, then the changes get written back on disk prematurely. But they can still be undone when the transaction is rolled back for whatever reason. A negative side-effect of this could be that another client reading the same record with no-lock might fetch uncommitted values. I can't think of scenarios where a behavior like this might be desired - but I don't know everything. IMHO, having premature writes to the database on disk is almost always bad coding - be it deliberately or not. Ideally, for best performance and avoiding negative side effects the buffer and transaction scope should always be identical.

Whenever you see a release statement or a buffer scope not matching the transaction in the code you should ask the responsible developer some serious questions.

Heavy Regards, RealHeavyDude.

 

[DBA_learner]

Hi RealHeavyDude,

I thought it should get updated on pause statement because customer.name field is already updated before this pause statement. I was expecting this on pause statement because as you said writing data to bi file is an asynchronous process that’s way time taken on pause statement was long and was waiting for next bi file write.

Example:-

Suppose we are in a FOR EACH loop and updating each customer’s name one by one. On first iteration of for each loop (before transaction end), I updated customer’s name from abc ->> xyz. Once I update the name then where this previous value (abc) will go. I think it will go to bi file asynchronously (that’s why expecting bi update on pause statement) . Later-on, if transaction is committed successfully (End of FOR EACH loop executes) then updated value from buffer pool (xyz) will be written to disk (.d file) and abc will be truncated/flushed from bi file. If transaction is unsuccessful then previous value from bi file will be used and copied to disk and later-on flushed from bi file.

Please suggest.

 

[RealHeavyDude]

Can it be that you are hearing the grass grow? Please don't get me wrong, but, does it really matter exactly at which point in time relative to the exectuion of an ABL statement the before image on disk gets updated? I've never put that much thought into the timing details of the before image.

I've never had any issue with this in 25+ years of database administration with Progress databases with the before image in particular - except the usual suspects like:

1. Unexpected before image growth due to bad code causing transaction scope to be larger than need be.
2. Timestamp mismatch on the before image due to some system admins fiddling with individual files of the database.

To me in the end, the only thing that matters with the before image is:

• Does the crash recovery ensure that the database is consistent and any uncommitted transaction are backed out?

And I can only answer: Yes - it does.

IMHO - as a DBA, you should take more care about real issues like performance probles or an insufficient desaster recovery strategy. But, that is IMHO of course.


Heavy Regards, RealHeavyDude.

 

[TomBascom]

Progress works very hard to avoid writing to disk unless it is absolutely necessary. The transaction is still active and is NOT yet committed at the PAUSE so it is not necessary to write anything to disk (or to shared memory for that matter). If your code contains an error and fails prior to the END statement everything needed to rollback is in local memory (or the LBI file if too large). In this case if you kill the session or the db while paused the data on disk is exactly what it needs to be and recovery is simple and fast.

 

[Rob Fitzpatrick]

If you are really interested in the under-the-covers workings of the transaction subsystem in the OpenEdge RDBMS then these two sessions are required reading/listening.

From the PUG Challenge Americas 2013 conference:

Before Image File, Checkpoints, and Crashes
Gus Björklund, Progress Software
slides: http://pugchallenge.org/downloads2013/224_bi_checkpoints_crashes_v03.pdf
audio: http://pugchallenge.org/downloads2013/audio/224_bi_checkpoints_crashes_v03.mp3

Database I/O Mechanisms
Rich Banville, Progress Software
slides: http://pugchallenge.org/downloads2013/321_Database_IO.pptx
audio: http://pugchallenge.org/downloads2013/audio/321_Database_IO.mp3

 

[DBA_learner]

Thanks RealHeavyDude, Tom Bascom and Rob for your valuable inputs!!