We encountered a problem recently where some code worked fine on our main Oracle test system but did not work correctly on z/OS DB2.
We could not see anything wrong, so ran the code in trace using xTrace and it was then that it was noticed that there was a LIMIT 1 clause set on a READ EACH properties. This limits the rows returned to 1, but only for MS/SQL or DB2. The statement should not have had the property set, but had been copied from another READ EACH that did have the LIMIT since there was an ESCAPE after the first READ and hence it worked correctly in all databases.
This highlights the danger of using properties that affect the results set, for example, LIMIT or DISTINCT since these are not visible to the designer when viewing the action diagram.
My recommendation would be that if you decide to use these properties, add a NOTE statement indicating this above the statement. This will act as a warning if the statement is copied, and also should also indicate that the properties might need modifying.
No comments:
Post a Comment