Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: sqlstate 42509 from Cobol program

    A Cobol program that was converted from DB2/zOS to DB2/Linux is receiving sqlstate 42509. The program uses declared global temp tables. It does not get this sqlstate on DB2/zOS. It's believed this sqlstate is returned when the program tries to issue a drop table statement for a declared global temp table.

    Any ideas what the problem might be?

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    mainframe cobol with embedded SQL to linux? Which cobol compiler? Do you pre-compile with db2prep? Is db2 also converted to LUW on linux? Which SQL block results in the error?

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I only know the answer to some of your questions. DB2 was also converted to LUW on Linux. Code provided to me is in the attached file.
    Attached Files Attached Files
    Last edited by db2girl; 11-05-10 at 09:14.

  4. #4
    Join Date
    Oct 2010
    Posts
    6
    Bella,

    Could you check what option you are using for the DYNAMICRULES clause in the bind statement? The bind should have DYNAMICRULES RUN specified, if not, could you rebind with the RUN option and retest the program.

    Check out the manual for SQL0549N and the general rules for the DROP statement under Database reference->SQL->Statements.

    Hope this helps

    Alex

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Thank you. Changing to DYNAMICRULES RUN worked.


    I see the same restrictions for the DROP statement in the LUW and zOS manuals:

    LUW:
    This statement can be embedded in an application program or issued through the use of dynamic SQL statements.
    It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).


    zOS:
    This statement can be embedded in an application program or issued interactively.
    It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.




    The information in Table 1 looks the same to me:

    LUW:
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    zOS:
    IBM Information Management Software for z/OS Solutions Information Center


    I'm not sure why they were not getting this sqlstate on the mainframe IF using the same DYNAMICRULES (BIND was originally used) option. Do you know?


    This is way outside of my area so it was a good learning experience.
    Last edited by db2girl; 11-05-10 at 16:44.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    I'm not sure why they were not getting this sqlstate on the mainframe IF using the same DYNAMICRULES (BIND was originally used) option. Do you know?
    One explanation could be that they bind and run the packages using the same authid.

Posting Permissions

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