IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Multi row insert


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
asirisha09

New User


Joined: 29 Mar 2010
Posts: 2
Location: bangalore

PostPosted: Mon Mar 29, 2010 5:55 pm
Reply with quote

Hi,

I am trying to use Multi row insert in DB2 version 8 but every time when the insert runs only one row is inserted.Is there any setting that needs to be enabled.

Can any one provide a suggestion for this
Back to top
View user's profile Send private message
Ranjithkumar

New User


Joined: 10 Sep 2008
Posts: 93
Location: India

PostPosted: Mon Mar 29, 2010 5:58 pm
Reply with quote

Please post the query you used in your code.
Back to top
View user's profile Send private message
asirisha09

New User


Joined: 29 Mar 2010
Posts: 2
Location: bangalore

PostPosted: Mon Mar 29, 2010 7:25 pm
Reply with quote

Here is the code.

Code:
INSERT INTO CUST_INCL_ON_RQST               
    (STMT_RQST_ID                           
   , CUSTOMER_ID                           
   , SMPL_CUST_YN                           
   , RSLT_CD)                               
VALUES                                     
    (:ZNHDCUIR-STMT-RQST-ID                 
   , :WS-CUSTOMER                           
   , :ZNHDCUIR-SMPL-CUST-YN                 
   , :ZNHDCUIR-RSLT-CD)                     
     FOR :NUM-ROWS ROWS                     
     NOT ATOMIC CONTINUE ON SQLEXCEPTION   


Declaration
Code:
05 WS-CUSTOMER          OCCURS 1000 TIMES.         
   07 WS-CUSTOMER-ID               PIC S9(09)  COMP
05 NUM-ROWS                        PIC S9(06)  COMP


all others are table variables.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Mar 29, 2010 8:01 pm
Reply with quote

First of all your array declaration is not same as your insert.

Change your array to

Code:


01 CUSTOMER.
   05 WS-STMT-ID  ... OCCURS 1000 TIMES.
   05 WS-CUSTOMER-ID PIC S9(9) COMP OCCURS 1000 TIMES.



Here the group name should not be using the occurs, instead each element should be defined with occurs for mass inserts.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Mon Mar 29, 2010 8:19 pm
Reply with quote

Please check out Facing problem with varchar col in multi-row insert...
dbzTHEdinosauer wrote:
nor did I ever find an example in the documentation
(Redbooks and tech manuals)
concerning multi-row UPDATE of varchar with an array.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Mon Mar 29, 2010 8:40 pm
Reply with quote

Host-variable-arrays in PL/I, C, C++, and COBOL
Quote:
Host-variable-arrays are defined by statements of the host language as explained in DB2 Application Programming and SQL Guide, SC18-9841-00.
Does anybody have a url for this or IBM's DB2 shelf?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Mar 29, 2010 9:33 pm
Reply with quote

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/FINDBOOK?filter=SC18-9841&SUBMIT=Find
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Mon Mar 29, 2010 9:34 pm
Reply with quote

CICS Guy wrote:
Does anybody have a url for this or IBM's DB2 shelf?
is this the one?

Alternate Link
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Tue Mar 30, 2010 11:48 am
Reply with quote

Hi,

Asrisha whether the problem is solved?

Thanks,
Thangaprabhu
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Insert header record with record coun... DFSORT/ICETOOL 14
No new posts Insert system time/date (timestamp) u... DFSORT/ICETOOL 5
No new posts Identify Program Insert DB2 7
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
No new posts Insert trailer for non empty file only DFSORT/ICETOOL 6
Search our Forums:

Back to Top