if creating a tablespace using "file system caching"option, then when dirty data is being flushed,it is actually being transfered into the filesystem's cache,but from the dbms' standpoint, the data has been wrote to the disk and will be there even sometime later the system be crashed. if at this time,the system really get crashed, the data is not in on the disk,but in the db-logs,dbms figurs out that the lsn witch is commited just now has already been write to disk,so it won't replay that step,whose data is actually not on the disk!!! the db will be in a inconsistent situation and will accept transactions continuously.

if the filesystem is like ntfs rather than jfs, how to explain this problem?

The transaction logs are for disaster recovery. They are used to take the DB from a consistent know state (backup) to the current state consistent state. So even if it is the OS's fault that a system crashes, so long as you have your backup image and your logs, you can still recover. It does not matter that if when the data was finally being written to disk that the system crashed, if you have the logs and the backup image, those committed transactions in the logs will be properly applied to the recovered DB.

Andy

thanks for your post.
but it seems you misunderstood what i was saying

"if at this time,the system really get crashed, the data is not in on the disk,but in the db-logs,dbms figurs out that the lsn witch is commited just now has already been write to disk,so it won't replay that step,whose data is actually not on the disk!!! "

The DBMS only replays the logs during recovery. These are structured to guarantee that the DB goes from one consistent state to the next. One part of a DB is not marked as consistent while another is not--if coming from the same transaction. So if the DB is in consistent state A and the logs have a transaction that moves it to consistent state B and the entire DB is not marked as being transitioned to state B, then during recovery, this transaction is will be applied to completion. Note that the DBMS marks a transaction complete as the very last step of the process. So if the OS is caching all of this and fails to mark the transaction as complete due to a failure, then during recovery, that failure will be noted and the transaction will be applied.

Andy

It is the job of the operating system to ensure that data is written to the disk if a synchronous I/O operation is requested. Period.

In case of a system crash, DB2 knows when the last sync point/check point happened. From that point on forward, it will replay the logs during crash recovery. The replay includes applying the log records from committed transactions and removing all changes from rolled-back and/or not-committed transactions. The nice thing is that DB2 can apply the same log record multiple times w/o changing the final result.

"DB2 knows when the last sync point/check point happened"
the lsn is stored somewhere? where is it stored?
I know that oracle deal with it by read the scn in every database file's header.

"So if the OS is caching all of this and fails to mark the transaction as complete due to a failure, then during recovery, that failure will be noted and the transaction will be applied."


if a lsn has been marked committed and flushed,but the "has-been-flushed" data actually is in the filesystem cache,and later losed due to a system crash, then during the db restarting, because the lsn is recognized by db2 that it has already been committed and flushed,so it won't repaly this lsn,but the data against this lsn is actually not on the disk.

You are not getting it. The lsn has to be on the disk to have the transaction marked as completed. It does not matter where it is stored. If it is not there then the transaction is not complete-PERIOD. DB2 does not store it in more than one place, so it is not marked as completed in one place and not the other. "Flushing" has nothing to do with it. During recovery, DB2 uses strictly what is on the disk, not what was in memory before a crash.

Andy

"Flushed to disk" means that the data is on disk. Period. If the operating system doesn't guarantee that the data is on disk, you should throw the operating system into the trash.

Note that each operating system allows applications to specify how disk I/O should be performed (sync vs. async, cached vs. not cached, etc.) Naturally, DB2 will use the best performing and correct flags for disk I/O to guarantee the consistency of the data.

"If the operating system doesn't guarantee that the data is on disk, you should throw the operating system into the trash.
"

if you use "use filesystem cache" option,then it would write to fs with async mode, so it cannot garantee the data be on the disk when fs return success to dbms.

Please read the rest of my posting as well. There, I wrote:
Thus, one can (and DB2 does where necessary) force the operating system to ensure the data is written on disk. Every decent operating system available these days supports that kind of functionality.

Also, the USE FILESYSTEM CACHE clause applies to tablespaces. Logs - and that is what is really important for a DBMS - are definitively forced to disk at the proper point in time. That's why it is generally a good idea to place logs on fast disks and not on tape devices, for example.