none
DB2 adapter - calling in/out stored procedure in batch RRS feed

  • Question

  • Hi,

    we are using BTS 2013R2 with the DB2 adapter of HIS 2013.

    We are calling an in/out stored procedure on DB2400.

    Based on the generated schema, we saw it is possible to call the stored procedure multiple times in one xml request, either by duplicating the <sync> node or the <StoredProcedure> node; this seemed very handy as we are also receiving on the other side a multi occurences input, so debatching wasn't required.

    ... <sync>
    <StoredProcedure>
    <SPC ENTITY="1" REGION="290" DATE="blabla" RESULTCODE="" ERRORTEXT=""/>
    </StoredProcedure>
    <StoredProcedure>
    <SPC ENTITY="1" REGION="290" DATE="20160104" RESULTCODE="1" RESULTCODE="" ERRORTEXT=""/>
    </StoredProcedure>
    </sync>

    ...

    This works fine and fast,  but the problem is in the response of the adapter. As some of the calls might return an error due to functional errors, we receive a sequence of result nodes, of which is not clear to what call they are linked to (input parameters are empty). Moreover, the order of the individual calls is not preserved : the first call should be returned an error, but in the response, the error is put in the second place.

    A possible solution for this is to change the stored procedure so that the output parameters mirror the values of the input parameters, but maybe there is another solution in BizTalk itself?

    ...

    <Success>
    <SPC ENTITY="" REGION="" DATE="" RESULTCODE="J" ERRORTEXT=""/>
    <ResultSets/>
    </Success>
    <Success>
    <SPC ENTITY="" REGION="" DATE="" RESULTCODE="" ERRORTEXT="Date invalid"/>
    <ResultSets/>
    </Success>
    Tuesday, May 31, 2016 2:40 PM

Answers

  • The issue was avoided by changing the stored procedure at the AS/400 DB2 side (as suggested in my original question): IN parameters were changed INOUT parameters. Thereby the input values were returned in the response allowing to determine the incorrect entries.

    CREATE PROCEDURE D#FCW7/SPC0003                                      
     (INOUT ENTITEITCODE CHAR( 1),                                        
     INOUT STELPLAATSNUMMER CHAR( 3),                                     
     INOUT DATUM CHAR ( 8),                                               
     INOUT CODEDAGVANWEEK CHAR ( 1),                                      
     INOUT TYPEDAG CHAR ( 2),                                             
      OUT CODEVERWERKT CHAR ( 1),                                         
      OUT ERRORTEKST CHAR ( 30))                                          
      LANGUAGE RPGLE SPECIFIC                                             
     D#FCW7/SPC0003 NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL    
     INPUT EXTERNAL NAME 'LIBL/SRPCKL' PARAMETER STYLE GENERAL WITH NULLS


    • Marked as answer by Lieven De Moor Wednesday, August 17, 2016 1:37 PM
    Friday, August 12, 2016 6:43 AM

All replies

  • Lieven, is this still an issue?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, August 9, 2016 10:41 PM
    Owner
  • The issue was avoided by changing the stored procedure at the AS/400 DB2 side (as suggested in my original question): IN parameters were changed INOUT parameters. Thereby the input values were returned in the response allowing to determine the incorrect entries.

    CREATE PROCEDURE D#FCW7/SPC0003                                      
     (INOUT ENTITEITCODE CHAR( 1),                                        
     INOUT STELPLAATSNUMMER CHAR( 3),                                     
     INOUT DATUM CHAR ( 8),                                               
     INOUT CODEDAGVANWEEK CHAR ( 1),                                      
     INOUT TYPEDAG CHAR ( 2),                                             
      OUT CODEVERWERKT CHAR ( 1),                                         
      OUT ERRORTEKST CHAR ( 30))                                          
      LANGUAGE RPGLE SPECIFIC                                             
     D#FCW7/SPC0003 NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL    
     INPUT EXTERNAL NAME 'LIBL/SRPCKL' PARAMETER STYLE GENERAL WITH NULLS


    • Marked as answer by Lieven De Moor Wednesday, August 17, 2016 1:37 PM
    Friday, August 12, 2016 6:43 AM