DB2 - L

 View Only
Expand all | Collapse all

Db2 12 for z/OS Looking for a volunteer to experiment

  • 1.  Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 11, 2022 02:37 PM

    Hello all,

    I've been doing some work with array variables lately and run into a bit of a roadblock. I have been able to call a stored procedure passing an array as input. Imagine a process that calls a stored procedure 10 times instead call it once with an array to process 10 things. The performance impact is significant to say the least. Now, there are limitations, especially when trying to run the stored procedure. I can run it from IBM Data Studio using a right click run and typing in an array for the input parameter. I can also call the stored procedure using Java and passing a Java sql array, but only for a type 2 driver. One last way to test is using Db2 CLP. So, let's discuss using Db2 CLP. The format of the statement would be:

     

    CALL MULTPROC(ARRAY['123','123'])

     

    And that will work just fine. However, since some languages and connectivity don't allow for an array to be passed there is a documented alternative which is to create a global variable of the array data type and make the call using the global variable. This is documented:

     

    The final example here shows setting up the variable

    https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-create-variable

     

    Call procedure says that "expression" in a parameter can be a session global variable. There is an example here that shows a call to a stored procedure passing a global array variable.

    https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-call

     

    However, when I try to do it, it does not work.

     

    This works:

    SET ARRAYVAR = ARRAY['123','123']

    But then this passes NULL to the stored procedure in the same UOW

    CALL MULTPROC(ARRAYVAR)

     

    Anyone out there will to try this and confirm? I would think you can simply copy and paste the examples at the bottom of the CREATE PROCEDURE doc.

     

    Thanks,

    Dan



  • 2.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 11, 2022 02:48 PM
    Sorry, correction "only for a type 2 driver" should be "only for a type 4 driver"




  • 3.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 12, 2022 03:29 AM
    Hi Dan!
    I just did the PHONENUMBERS example:

    -- CREATE AN ARRAY TYPE
    CREATE TYPE PHONENUMBERS AS VARCHAR(20) CCSID UNICODE ARRAY[10];
    -- CREATE INPUT ARRAY VARIABLE
    CREATE VARIABLE PNUMBER_ARRAY PHONENUMBERS;
    -- CREATE OUTPUT ARRAY VARIABLE
    CREATE VARIABLE PNUMBER_ARRAY_OUT PHONENUMBERS;
    COMMIT ;
    --#SET TERMINATOR %
    ------------------------------------------------------------------------
    -- CREATE AN SQL PROCEDURE WITH ARRAY PARAMETERS. THE ARRAY PARAMETERS
    -- ARE
    -- DEFINED WITH THE PHONENUMBERS ARRAY TYPE. THE PROCEDURE SEARCHES FOR
    -- NUMBERS IN IN_PHONENUMBERS THAT BEGIN WITH THE GIVEN PREFIX, AND
    -- RETURNS
    -- THE PHONE NUMBERS IN THE NUMBERS_OUT PARAMETER.
    ------------------------------------------------------------------------
    CREATE PROCEDURE FIND_CUSTOMERS(
    IN NUMBERS_IN PHONENUMBERS,
    IN PREFIX CHAR(3) CCSID UNICODE, -- HAD TO ADD CCSID!
    OUT NUMBERS_OUT PHONENUMBERS)
    BEGIN
    DECLARE I, J INTEGER;
    SET I = 1;
    SET J = 1;
    -- INITIALIZE NUMBERS_OUT TO AN EMPTY ARRAY USING AN ARRAY CONSTRUCTOR
    -- WITH NO ELEMENTS
    SET NUMBERS_OUT = ARRAY[ ];
    WHILE I < CARDINALITY(NUMBERS_IN) DO
    IF SUBSTR(NUMBERS_IN[I], 1, 3) = PREFIX THEN
    SET NUMBERS_OUT[J] = NUMBERS_IN[I];
    SET J = J + 1;
    END IF;
    SET I = I + 1;
    END WHILE;
    END %
    --#SET TERMINATOR ;
    COMMIT ;

    Then started up DS 4.1.4 and executed the stored proc by typing in the examples data:
    '416-305-3745', '905-414-4565', '416-305-3746'
    And
    416
    And got the expected output:

    Name Type Data type Value Value (OUT)
    ----------- ------ ------------ ---------------------------------------------- ------------
    RETURN_PARM OUTPUT INTEGER 0
    NUMBERS_IN INPUT PHONENUMBERS '416-305-3745', '905-414-4565', '416-305-3746'
    PREFIX INPUT CHAR 416
    NUMBERS_OUT OUTPUT PHONENUMBERS 416-305-3745

    Don't know if that helps you...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 4.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 12, 2022 03:41 AM
    What is odd, is that I see only one telephone number in the stored proc output in DS... I guess that is a limitation of DS...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 5.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 12, 2022 06:58 AM
    Roy,
    You are right - there should be 2 numbers in the output.  I think the problem is the WHILE - WHILE I < CARDINALITY(NUMBERS_IN) DO.   I think it should be <=.

    I think the documentation link Dan provided did say that the SP parameter had to be Unicode, which is why you had to add that.  Maybe that's why it passed in NULL without it.

    Joe

    ------------------------------
    Joe Geller
    ------------------------------



  • 6.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 12, 2022 08:00 AM
    Great spot Joe!

    Change it to be <= and Tra La! Two lines of output!

    Name Type Data type Value Value (OUT)
    ----------- ------ ------------ ---------------------------------------------- --------------------------
    RETURN_PARM OUTPUT INTEGER 0
    NUMBERS_IN INPUT PHONENUMBERS '416-305-3745', '905-414-4565', '416-305-3746'
    PREFIX INPUT CHAR 416
    NUMBERS_OUT OUTPUT PHONENUMBERS 416-305-3745, 416-305-3746

    Much better!!!

    Looks like IBM have two little bugs in the docu to fix...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 7.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 12, 2022 08:17 AM
    OK, my SP parameter is EBCDIC, so I'll try it with a Unicode parameter.
    Thanks,
    Dan




  • 8.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 12, 2022 01:17 PM
    OK, I have tested this, and I realized that Roy's example falls short. You tested the stored procedure but did not test the array global variable. I have recreated the test and passing the global variable does not work.

    This is what I did using the IBM supplied example:
    1) defined the data type and variable
    2) created the stored procedure
    3) executed the stored procedure passing the same parameters as Roy using IBM Data Studio

    All of the above worked as prescribed.

    Then I did this:

    1) opened a CLP session
    2) called the stored procedure FIND_CUSTOMERS(ARRAY['416-305-3745', '905-414-4565', '416-305-3746'],'416',?)

    This worked just fine

    Then I did this in CLP
    1) set PNUMBER_ARRAY = ARRAY['416-305-3745', '905-414-4565', '416-305-3746']
    2) select * from unnest(PNUMBER_ARRAY)

    This worked great

    Finally with CLP:
    1) set PNUMBER_ARRAY = ARRAY['416-305-3745', '905-414-4565', '416-305-3746']
    2) called the stored procedure FIND_CUSTOMERS(PNUMBER_ARRAY ,'416',?)

    This did not work as it appears to pass NULL to the procedure. The CALL statement in the SQL reference example (towards the end) demonstrates exactly this scenario, but I cannot get it to work.

    Dan




  • 9.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 12, 2022 02:07 PM
    Dan,

    In addition to make defining the variable as Unicode inside the SP, did you also create the global array variable as Unicode?

    Joe

    ------------------------------
    Joe Geller
    ------------------------------



  • 10.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 12, 2022 03:01 PM
    Absolutely!




  • 11.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 13, 2022 03:56 AM
    Looks also good for me when I use data studio:

    Name Type Data type Value Value (OUT)
    ----------- ------ ------------ ---------------------------------------------- --------------------------
    RETURN_PARM OUTPUT INTEGER 0
    NUMBERS_IN INPUT PHONENUMBERS '416-305-3745', '905-414-4565', '416-305-3746'
    PREFIX INPUT CHAR 416
    NUMBERS_OUT OUTPUT PHONENUMBERS 416-305-3745, 416-305-3746

    ------------------------------
    PaulJansenATOS
    ------------------------------



  • 12.  RE: Db2 12 for z/OS Looking for a volunteer to experiment

    Posted May 13, 2022 08:12 AM
    Here's the thing. Using Data Studio it's impossible to call a stored procedure passing an array in any manner other that choosing the RUN button or right-click run and typing in the parameter. Any other method does not work. I am using CLP to set the global variable and then pass that global variable as a parameter and that does not work.

    This is what I am attempting to do:
    set PNUMBER_ARRAY = ARRAY['416-305-3745', '905-414-4565', '416-305-3746']
    CALL FIND_CUSTOMERS(PNUMBER_ARRAY ,'416',?)

    The SQL reference gives this exact example and yet I cannot get it to work.

    Dan