View previous topic :: View next topic
|
Author |
Message |
Lkmi
New User
Joined: 04 Mar 2005 Posts: 53 Location: Newyork
|
|
|
|
Hello,
Iam creating a stored proc which does the following functionality.
I have to populate a temp array in my stored proc with data from a cursor and once the array is filled, have to do a mass insert into a global temp table.
The data in global temp table is going to be available to the calling application as a result set.
To achieve this,I did the following:
This is my cobol declaration for the temp array .The max no of records will be 100 so I gave an occurs clause of 100.
01 WS-TEMP-TABLE.
05 WS-TEMP-ARRAY OCCURS 100 TIMES DEPENDING ON WS-ROWS.
10 PARTY-ID2 PIC X(13).
10 CLCT-ORGZN-NUM2 PIC S9(9) USAGE COMP.
10 PARTY-SRC-SYS-CD2 PIC S9(4) USAGE COMP.
10 ENPRS-ID2 PIC X(13).
01 WS-ROWS pic s9(04) comp.
This is my insert statement:
EXEC SQL
INSERT INTO R13130_TEMP
(PARTY_ID
,CLCT_ORGZN_NUM
,PARTY_SRC_SYS_CD
,ENPRS_ID )
VALUES (:WS-TEMP-ARRAY)
FOR :WS-ROWS ROWS
ATOMIC
END-EXEC
My temp table has 26 records but only 1 record gets inserted via the insert statement.I tried giving data which filled all the 100 occurances in temp table?still only 1 row got inserted into the temp table.
Iam using DB2 V8
Can some one help me????? |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
Don?t know if I?ll be much help on this. We are not on V8 yet.
One silly question: You did populate WS-ROWS with the number of rows you are going to insert?
Reading the Documentation, are you sure that the insert shouldn?t be:
EXEC SQL
INSERT INTO R13130_TEMP
(PARTY_ID
,CLCT_ORGZN_NUM
,PARTY_SRC_SYS_CD
,ENPRS_ID )
VALUES (:PARTY-ID2, :CLCT-ORGZN-NUM2, :PARTY-SRC-SYS-CD2)
FOR :WS-ROWS ROWS
ATOMIC
END-EXEC
Dave |
|
Back to top |
|
|
|