MVSFORUMS.com

[dbzTHEdinosauer]

I almost made this a PM since I have posted this question in other forums. But, I have not obtained an acceptable response, and the discussion might help others. An acceptable response will be the results of a test (see below) using DB2 Vsn6.

BackgroundAn SQLCODE -811 means someone wrote an imbedded singleton SELECT (SELECT INTO) and the results table consisted of more than 1 row.

I don't have host access currently. I am analyizing modules on the PC and designing corrective code. That I can do. What I can't do is test on the host.

My new client asserts that DB2, Vsn6, will populate the HV (host variables) prior to throwing the -811. I think he is deluded, 90% of his selects use the same HV in both the WHERE clause and the INTO clause (which means he has already populated the HV). The few cases that the HV in the INTO clause are different than the HV of the WHERE clause, he never uses the INTO clause HV, so he has never had a problem. But, he is migrating to Vsn7 and has been apprised of the fact that Vsn7 will not populate the HV if it is a singleton select and does not have a FETCH 1 ROW clause.

-305's (null indicator variable not included) are only thrown when DB2 needs to populate the null indicator variable. If you code a select without a null indicator variable and are lucky enough to never select a row that has a column IS NULL, your code will make it into production, where it will oneday break. So, I know that DB2 does things, which I am unaware of, and I am never positive about something until I have personnally tested it. In this case, I need someone else to test for me.

DB2 Vsn 6: Imbedded Singleton Select, the WHERE clause HV are different than the INTO HV, and you are assured of a -811. Are your INTO HV populated when you receive a -811?

thx, dbz
_________________
Dick Brenholtz
American living in Varel, Germany