SQLCODE -480

Brian Haas · New User Joined: 22 Jun 2013 Posts: 3 Location: USA

I coded a stored procedure (pgm 1) that calls another stored procedure (pgm 2) which creates a result set. When I tested it using IBM Data Studio it works fine, but our .Net developer is getting an SQLCODE -480.

After calling pgm 2, pgm 1 checks the sqlcode.

Then I execute the following:

EXEC SQL
ASSOCIATE RESULT SET LOCATOR (:R-SET-J01)
WITH PROCEDURE :WS-PROGRAM
END-EXEC.

Then I check SQLCODE and I am getting -480.

There's more code that follows to allocate the cursor, etc.. But I'm getting the error after the ASSOCIATE statement.

It works fine when tested via Data Studio. Does anyone have any ideas?

dick scherrer · Posted: Sat Jun 29, 2013 1:12 am

Hello and welcome to the forum,

Your question is about DB2 LUW, right?

Ours is a mainframe forum and we have very little dialog about DB2 LUW.

See if this helps:
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnmcj10/2.1.6.200?

Akatsukami · Posted: Sat Jun 29, 2013 1:12 am

SQLCODE -480 indicates that the procedure has not been called since the last commit. Check the value of WS-PROGRAM; ensure that it is exactly the same (including qualification) as the name the procedure is invoked by.

Brian Haas · New User Joined: 22 Jun 2013 Posts: 3 Location: USA

The value of WS-PROGRAM is exactly the same. And there is no commit in this program.

EXEC SQL
CALL :WS-PROGRAM ( :RTACM20A-NUM-OFFICE
,:RTACM20A-NUM-ACCT
,:RTACM20A-KEY-ACCT
,:RTACM20A-PROCESS-REQ
,:RTACM20A-CDE-SYMBOL
,:RTACM20A-DT2-TRDE-FROM
,:RTACM20A-DT2-TRDE-TO
,:RTACM20A-FIELD-ORDER-BY
,:RTACM20A-ORDER-SEQUENCE
,:RTACM20A-PAGE-REQUEST
,:RTACM20A-SCREEN-ITEMS
,:RTACM20A-PAGE-COUNT-REQUES
,:RTACM20A-CLA-ASSET
,:RTACM20A-RETRN-CODE
,:RTACM20A-RETRN-MSG)
END-EXEC.

....

EXEC SQL
ASSOCIATE RESULT SET LOCATOR (:R-SET-J01)
WITH PROCEDURE :WS-PROGRAM
END-EXEC.

Akatsukami · Posted: Sat Jun 29, 2013 1:35 am

Could the commit be implicit rather than explicit? Are the call to the procedure and the ASSOCIATE LOCATOR statement immediately adjacent?

Brian Haas · New User Joined: 22 Jun 2013 Posts: 3 Location: USA

That's a good question. I was wondering what might cause an implicit commit. For performance testing (I planned to remove this later) I added a statement to get and then display the current timestamp. Would that cause an implicit commit?

Akatsukami · Posted: Sat Jun 29, 2013 1:58 am

If you're getting the timestamp by, e.g., SELECT CURRENT TIMESTAMP INTO some-variable FROM SYSIBM.SYSDUMMY1, that wouldn't cause an implicit commit. I'm afraid that you'll need a better DB2 programmer than I.