Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: application migration from 8.2 to 9.5.1 - SQL0029N INTO clause required SQLSTATE=4

    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?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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?
    Last edited by MarkhamDBA; 01-27-09 at 23:51.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkhamDBA

    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...
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by n_i
    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?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    is shown embedded SQL statement dynamic or static? i found interesting thing abt dynamic embedded SQL in v9...
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is static.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marcus_A
    It is static.
    then i have to think abt something else. FP3A is installed.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Either add a host variable and an INTO statement, or call IBM support (I assume your company has a support contract).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkhamDBA
    I thought I explained why
    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.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by stolze
    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.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •