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?

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?

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.

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

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.

One explanation could be that they bind and run the packages using the same authid.