Tracking the Flow of Data

This is a greatly simplified view of the flow of data to, from, and inside an Informix database server. The differences between OnLine and IDS are minimal here. There are some differences in areas such as transport mechanisms for the connectivity tools, but they are not germane to the understanding of the overall architecture.

We are assuming that the query initiates from a client application such as dbaccess, SQL Editor, or ESQL/C. Don't worry too much about the details now. Just get a feel for how the data flows. We're glossing over such items as data concurrency, transactions, and isolation levels for the time being. In this simplified view, we are also glossing over the differences between simply reading data and performing an update or an insert.

The data flows something like this:

• Client program formulates its SQL request. The client generates SQL requests to be presented to the server.

• Client transmits it to the server. The request is transferred through UNIX pipes, in the case of the client and server being on the same machine. When the client and server are on different machines, the transmission is via UNIX pipes and a transport program such as INFORMIX-Net or INFORMIX-Star.

• Server optimizes the query and formulates a plan to retrieve the data. The server process uses its cost-based optimizer to calculate the most effective order in which to access the database tables and which indexes to use.

• Server requests a page from the database. This may be a data page or an index page. The query may need the entire page or only one row. Either way, it still pulls in an entire page.

• Server searches for the page in the shared memory buffer pool.

• If the page is in the buffer pool, server tries to get a lock on the buffer page. If the lock succeeds, server process can read and/or modify the buffer.

• If the page is not in the buffer pool, look for a free buffer. If there is no buffer free, go to the LRU queue and look for a free buffer. If no free buffer is found, flush out the oldest buffer and load the data into it.

• Server writes a copy of the unchanged original page to the physical log buffer.

• If logging is turned on and if data has been modified, the changes are logged to the logical log.

• Server returns data to the client The server returns the data either over UNIX pipes, using INFORMIX-Net or INFORMIX-Star if the client and server are on separate machines.

• Server updates its disk with the new data. At some time, either through a foreground process or through the actions of the page flushers, pages in shared memory that have been modified are flushed to the hard disk. These flushes do not empty the buffer: they just copy it to disk.

To fully understand the flow of data, remember that just about everything is buffered in shared memory and that access to any resource requires obtaining a latch on the resource. A latch is a temporary lock that controls access to a resource. In IDS systems latches are known as mutexes.