Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

error 42884 - reason code -440 sqlmessage [SQL0440] calling a cl procedure from a db2 stored procedure

Hi,
I have a stored defined as follows:
CREATE PROCEDURE RESTTEST.GETPRC4 (
      IN P1 CHAR(10) ,
      INOUT P2 DECIMAL(10, 0) )
      DYNAMIC RESULT SETS 1
      LANGUAGE CL
      SPECIFIC RESTTEST.GETPRC4
      NOT DETERMINISTIC
      MODIFIES SQL DATA
      CALLED ON NULL INPUT
      EXTERNAL NAME 'FBSOBJ/DTARA0C'
      PARAMETER STYLE SQL ;

that calls 'FBSOBJ/DTARA0C'  a cl program defined as follows:


PGM        PARM(&DTNAME &DTVAL)                          
                                                                       
             DCL        VAR(&DTNAME) TYPE(*CHAR) LEN(10)              
             DCL        VAR(&DTVAL)  TYPE(*DEC)  LEN(10 0)              
                                                                       
             ALCOBJ     OBJ((&DTNAME *DTAARA *EXCL *N))                
                                                                       
             RTVDTAARA  DTAARA(&DTNAME *ALL) RTNVAR(&DTVAL)            
                                                                       
      /* set output value                 */  
             CHGVAR     VAR(&DTVAL) VALUE(&DTVAL + 1)                  
                                                                       
             CHGDTAARA  DTAARA(&DTNAME *ALL) VALUE(&DTVAL)            
                                                                       
             DLCOBJ     OBJ((&DTNAME *DTAARA *EXCL *N))                                                                    
ENDPGM

calling stored as

 call resttest.getprc4('1234567890',0)

I get the error 42884 - reason code -440  sqlmessage [SQL0440]
I have only the italian version:  La routine DTARA0C in FBSOBJ non รจ stata trovata con i parametri specificati.
Causa. . . . . :   Una funzione o procedura con il nome specificato e gli argomenti compati
bili non sono stati trovati. Correzione   . . . :   Specificare il numero e il tipo di parame
tri corretti sull'istruzione CALL o richiamo di funzione. Immettere di nuovo la richiesta.

It states that no compatible CL routine was found: wrong parameters.

Any suggestion?  I already changed parameter style but no effect, always the same message.
Plese note that if I use a different number of parameter in call (es call resttest.getprc4('1234567890',0,?) )
I get the same message but referred to  getprc4.
In my opinion this means that the call finds out the CL routine but it does not match for parameter number.

Thank you in advance.
Francesco
0
_ecpirosis_
Asked:
_ecpirosis_
1 Solution
 
PortletPaulCommented:
my only contribution here, and it's by google translate:

The routine DTARA0C in FBSOBJ was not found with the specified parameters .
Cause . . . . . : A function or procedure with the specified name and arguments compati
bili were not found . Correction . . . : Specify the number and type of parame
tri correct education CALL or function call . Enter the request again .

Seems to be indicating it has found it, but the parameter [ numbers-of | datatypes-of | order-of ] isn't correct

do you have any successful call to that external procedure?
any documentation?
the code of it?
can you call it manually?
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Personally, I've never had much success passing a numeric value into a CL stored procedure. I always change the parameter to a *CHAR and do a translation within the CL.

See if that works for you. Obviously, it's not ideal, but it gets the job done.

HTH,
DaveSlash
0
 
_ecpirosis_Author Commented:
Thank you Dave, it seems impossible in CL to pass decimals. I cannot understand why it is not clearly stated.
Ciao,
Francesco
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now