Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem with a batch insert ; Inserts only 1 row

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Lkmi

New User


Joined: 04 Mar 2005
Posts: 53
Location: Newyork

PostPosted: Thu Jul 27, 2006 8:03 pm    Post subject: Problem with a batch insert ; Inserts only 1 row
Reply with quote

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
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Jul 28, 2006 6:16 am    Post subject: Re: Problem with a batch insert ; Inserts only 1 row
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts How does a called pgm know if its cal... Graeme Westerman COBOL Programming 4 Tue Nov 29, 2016 9:25 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
This topic is locked: you cannot edit posts or make replies. MIPS reduction for Batch job Virendra Shambharkar All Other Mainframe Topics 8 Mon Nov 07, 2016 4:02 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us