A cursor is a window into the result set of a SQL query. From this window, you can work with a few rows of data at a time instead of the entire result set. This is useful when working with large tables or database servers over a network.

Cursor support improves the importing of data in two important areas. Because the cursor acts as a window to the data, the integration platform does not need to read the entire result set into memory before using it. You can work with very large tables without running out of memory and without having to wait for the data to display. The other benefit of cursors is that the integration platform can read directly any record from the result set. For transformations, cursors help with record ranges or record sampling, as the integration platform does not need to read each record sequentially.

Connections may offer one or more of the following cursor options:

The Forward Only cursor option offers the most efficient performance for large transformations in SQL sources. Connection is made almost immediately and transformation speed is at its highest. In a test case with SQL Server, using a Static cursor on a 150,000 record table added about 4 minutes to the transformation and record reading was twice as slow. The startup cost of using the Static cursor was greater than the total transformation time when using the Forward Only cursor.

However, with the Forward Only cursor, the integration platform still has to iterate over each record in the result set. This means that things still have to be done sequentially, making the Forward Only cursor option somewhat clumsy for browsing source data. The Source Browser loads quickly, but any time you move from one section of the table to another, you have to wait for it to load completely again.

Static and dynamic cursors improve browsing of source data. Initial loading of the data may be slow, but you can move from the beginning of the table to the end, or stop in the middle, with little or no delay in browser display.

The Static cursor option does not detect updates to data. If the data changes while you are browsing the table, you must exit the browser and reopen it to see the changes. The Dynamic cursor option does keep track of changes to data. This difference may be relevant if you keep a target browser open while running an integration. The Dynamic cursor option, however, demands more computing resources, so the Static cursor option is generally preferred.

With dynamic cursors, all committed changes (including insertions) made by anyone and all uncommitted changes made by the cursor owner are visible to the cursor owner. Deletions are not flagged as missing—the row simply no longer appears. Updates made by anyone can affect membership and ordering of the result set, including updates that do not directly affect result set values, such as a change to another table referenced in a subselection.

With a keyset cursor, the membership and ordering of the result set are fixed at open time but values can change. If a change causes a row to fail to qualify for membership in the result set, the row remains visible until the cursor is reopened. If a change affects where a row should appear in the result set, the row does not move until the cursor is reopened. If a row is deleted, the key acts as a placeholder in the result set to permit a fetch by absolute position within the result set. Insertions by others are not visible. Insertions by the cursor owner should appear at the end of the result set. The advantage of this type of cursor is its ability to access the most up-to-date values and yet be able to fetch rows based on absolute position within the result set.