Multi Row Fetch Experiences (2)

A second issue with multi-row fetch (see previous posts) affects application design.

With a normal READ EACH, each row is fetched one at a time, so if the row fetched has been affected by previous processing within the READ EACH, the fetched row's column values will be up to date.

However with a multi-row fetch, blocks of n rows are fetched into an array at the same time. If you then update row n+1 whilst processing row n, then when you come to process row n+1, the values in the entity action views will not be the latest values since they are current as of the time that they were fetched and hence not include any updated values.

This should be a rare occurrence, but worth bearing in mind when deciding if multi-row fetch is applicable.

Multi Row Fetch Experiences (1)

We have now started the development of the next release of our products using gen r8.0. One of the new features of r8.0 that we are looking forward to using is multi-row fetch because of the potential for serious performance improvements (see previous posting).

We have developed a new check in VerifIEr to calculate what the optimum fetch size should be for a READ EACH statement and then use this information to automatically update the READ EACH statement.

However our initial testing has highlighted some issues with multi-row fetch.

The first affects DB2 and relates to errors or warnings issued during the fetch. If there are any warnings or errors issued, then DB2 issues an sqlcode +354 and you have to issue further GET DIAGNOSTICS statements to get the particular warnings. We have found several instances of warnings related to truncation of data. The warning is an sqlcode 0 with sqlstate 1004. This was caused by having an attribute defined in the model that was shorter than the database column due to differences between the same column in the Host Ency and Client/Server Ency.

Because Gen does not check the sqlstate (only the sqlcode), without a multi-row fetch, you will never see the warning, but with a multi-row fetch, because the generated code does not handle the +354, the application terminates with a runtime error. Unfortunately you cannot tell what the cause was without amending the generated code to add in the GET DIAGNOSTICS statements.

So far we have been working through the warnings and eliminating them, but we are also considering a post processor for the generated code to add in the diagnostics to make debugging easier, or to ignore the +354 sqlcode if there are only warnings.

The second issue is described in the next posting.