Home > IT Answers > AS/400 > Error executing stored procedure calling a CL...
fhelix72
0 pts.
 Error executing stored procedure calling a CL that run an STRQMQRY
AS/400, RPG, Database, DB2 Universal Database
I have created a stored procedure that call a CLP in which there is a STRQMQRY for creating a file in output.
this is the command of creation:

CREATE PROCEDURE TESMC/WP3007P
(IN SALD CHAR ( 1), IN ORDIN CHAR (1))
LANGUAGE CL NOT DETERMINISTIC
MODIFIES SQL DATA
EXTERNAL NAME 'TESMC/WP3007' PARAMETER STYLE GENERAL

when I execute the stored procedure it gave me an error
this is the error (the original in Italian and translated in English):

IN ITALIAN:
Il programma trigger o una routine esterna ha rilevato un errore.
Il collegamento non puo essere modificato. Codice causa 4.
Il comando START non e riuscito con SQLCODE -752.
Il comando START ha avuto esito negativo a causa di errori.
Il comando STRQMQRY non e riuscito.
Errore di funzione. QWM2701 non e stato controllato da WP3007 alla
specifica 6500, istruzione X'0085'.

IN ENGLISH:
ID messaggio . . . . . : CPA0701
The trigger program or an external routine has noticed an error.
The connectione can't be modified. Reason code 4.
The START command didn't function with SQLCODE -752.
The START command didn't function for errors.
The STRQMQRY command didn't function.
Function error. QWM2701 has not been controlled by WP3007 at line 6500 statement X'0085'.


Note that the problem is not in the cl because if I execute directly the cl from prompt there are no problem.
Someone could help me thank you

Lorenzo
Italy
ASKED: Oct 26, 2005  12:15 AM GMT
UPDATED: October 27, 2005  11:02:04 AM GMT
RELATED QUESTIONS
TheQuigs
0 pts.
Answer Wiki:
The problem is described by reason code '4'. Message SQL0752 in QSQLMSG lists the reason codes.

"4 -- The connection is locked by another invocation of Interactive SQL, or there is a level mismatch between the Interactive SQL product and the DB2 UDB for iSeries product."

You already have a connection to the database in your job. STRQMQRY tries to establish a new connection. I don't believe you'll be able to do execute a Query Management Query (*QMQRY) in a stored procedure. However, chould you move the logic from the *QMQRY directly to the stored procedure?
Last Wiki Answer Submitted:  Oct 26, 2005  1:11 PM (GMT)  by  TheQuigs   0 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

such as previous post, I think you can not qmqry in a cl in a sqlproc. I had same kind of issue some month ago (don’t remember exactly the issue).

But

I have many sqlprocs that call rpg that runs embedded sql without any error. More : my files are not journaled.

To simply run a sql in a cl, have a look at http://jplamontre.free.fr/AS400/EXECUTESQL.htm

JPLamontre
 0 pts.