we installed 9.5.1 on the test server, backuped a db on 8.2 and restored it on 9.5 and re-binding C++ applications on 9.5 server. We get bind errors:

LINE MESSAGES FOR bsint.bnd
------ ---------------------------------------------------------
SQL0061W The binder is in progress.
1188 SQL0029N INTO clause required. SQLSTATE=42601
SQL0082C An error has occurred which has terminated processing.
SQL0092N No package was created because of previous errors.
SQL0091N Binding was ended with "3" errors and "0" warnings.

Here is an embedded SQL:

long bsIntStepRatesSqlGet(struct CsbsIntStepRates* pStruct)
{ /* copy parms to host variables */
strncpy(hvIntStepRates.Account_COID, pStruct->Account_COID,
sizeof(hvIntStepRates.Account_COID)-1);
hvIntStepRates.Product_Seq = pStruct->Product_Seq;
strncpy(hvIntStepRates.Feature_Id, pStruct->Feature_Id,
sizeof(hvIntStepRates.Feature_Id)-1);
EXEC SQL SELECT account_coid
FROM bs_IntStepRates
WHERE Account_COID= :hvIntStepRates.Account_COID
AND Product_Seq = :hvIntStepRates.Product_Seq
AND Feature_Id = :hvIntStepRates.Feature_Id;
return SQLCODE;

The erros message says we need INTO in the SQL statement but in v8.2 rebind was fine and application worked with no problems.

Any takers?

I do not really believe that this has been working fine on V8.2. (But who knows, maybe some sort of check was added in V9.)

The question is actually what you want to do with this query. You don't have any function calls that may trigger some external action and you don't receive any result. So DB2 sees a useless SELECT statement and tells you that. Thus, I would fix the application...

it's not useless - this sql is checking if there are any row in the table for these conditions/variables. It checks the sqlcode and if sqlcode=100 (no records) it will follow certain logic.

The question is why rebind worked on v8.2 and does not work in v9.5?

OK, suppose there was a bug in v8.2 which has been fixed in v9.5 - what now? You still have to go back and fix the application...

I thought I explained why we have this query in the application. Do you see anything wrong with this SQL aside from that you think that it is useless?

First try FP3a, and if that does work then call IBM support. What people are saying is that it may be that DB2 requires an INTO statement, but for some reason the syntax rule was not enforced correctly in V8.2 and now it is enforced.

is shown embedded SQL statement dynamic or static? i found interesting thing abt dynamic embedded SQL in v9...

It is static.

then i have to think abt something else. FP3A is installed.

Either add a host variable and an INTO statement, or call IBM support (I assume your company has a support contract).

What I am trying to tell you is that you are wasting your energy and time on non-essential things. It does not matter that your application had been working until migration, or that there were good reasons to code it like it was. You will have to fix it unless you want to go back to v8.2.

A select statement retrieves data from the database and you must provide some place for it to store those data. You can do it by declaring either a host variable and doing SELECT INTO (if you know only one record will be returned ever), or a cursor. If you really are curious how your application was treated under v8.2 you could look at the C preprocessor output.

I had a look at the manuals, and a SELECT outside a cursor is not permitted (unless you have a SELECT INTO statement). Therefore, it is an application bug, which wasn't detected by DB2 V8.2, which means that DB2 V8.2 had a bug too.

yes, we realized that. I guess it's 2 bugs - one in our applications, and another in DB2 v8. We are going to fix ours.

Thanks Knut and everyone else for productive discussion.