Data Changes During Long Queries

[gunnypatel]

Let's say I have a SQL query that takes 3 minutes to execute. If the data being accessed by the query changes during execution, do I get the data as of the start of the query or as of the end or somewhere in between?

[TedHolt]

It depends. First, are you using commitment control or not? Isolation level "uncommitted read" lets you see changes in other jobs, but "cursor stability" won't let you see them. There are four isolation levels that you need to consider when using commitment control.

Second, a query accesses data in pages, and if someone updates a row that you haven't paged into memory yet, then you'll see the update. If they update a row that is in the page that is currently in memory, you won't see the change.

[gunnypatel]

It depends. First, are you using commitment control or not? Isolation level "uncommitted read" lets you see changes in other jobs, but "cursor stability" won't let you see them. There are four isolation levels that you need to consider when using commitment control.

Second, a query accesses data in pages, and if someone updates a row that you haven't paged into memory yet, then you'll see the update. If they update a row that is in the page that is currently in memory, you won't see the change.

We are not using commitment control (we do journaling for HA replication though) and we are using isolation level *NONE. I read the documentation on isolation levels but it was a bit confusing. I'm unsure how much the isolation level applies in my scenario where I am running a statement in a cursor and loading an array data structure with all the results.

Does IBM i not do snapshots of the data for select queries? Is there a way to have the database engine do that for particular sessions/queries? It sounds like maybe cursor stability is what I should look into.

[TedHolt]

If your applications don't use commitment control, then isolation level is irrelevant. Queries will see changes only for rows that have not yet been paged into memory.

[gunnypatel]

If your applications don't use commitment control, then isolation level is irrelevant. Queries will see changes only for rows that have not yet been paged into memory.

Thanks. So it sounds like there's not a way to guarantee that a given execution of the query will not be affected by changes to the data by another process. Are there any session/query parameters that I can use to change that?