The following is what I have for my stored procedure and it is all working well.
CREATE TYPE occurrenceRow AS ROW (
CREATE TYPE occurrence_obj AS occurrenceRow ARRAY
CREATE PROCEDURE ins_occurrence_code (IN ECTN CHAR(12), IN OCC_CODES occurrence_obj)
INSERT INTO OCCURENCE_CODE
T.OCC_CD,T.CLHO_OCC_FROM_DT,T.CLHO_OCC_TO_DT FROM UNNEST(OCC_CODES)
AS T(OCC_CD, CLHO_OCC_FROM_DT, CLHO_OCC_TO_DT);
Here is my test for the Stored Procedure, it works as well:
DECLARE VAR_OCCURENCE_CODES occurrence_obj;
SET VAR_OCCURENCE_CODES = (select '01','2010-06-01','2010-06-01' FROM SYSIBM.SysDummy1);
SET VAR_OCCURENCE_CODES = (select '02','2010-06-02','2010-06-02' FROM SYSIBM.SysDummy1);
SET VAR_OCCURENCE_CODES = (select '03','2010-06-03','2010-06-03' FROM SYSIBM.SysDummy1);
SET VAR_OCCURENCE_CODES = (select '04','2010-06-04','2010-06-04' FROM SYSIBM.SysDummy1);
CALL ins_occurrence_code('0123456789AB', VAR_OCCURENCE_CODES);
Where I'm stuck is setting the "occurrence_obj" type on the prepared statement inside Java. I can't find any mention of setting a custom type Array. Any references would be great.