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.deWeb
http://www.seg.deLink zur Datenschutzerklärung
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich
Original Message:
Sent: 5/11/2022 2:37:00 PM
From: Daniel Luksetich
Subject: Db2 12 for z/OS Looking for a volunteer to experiment
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