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
 

 

Multi row insert

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Multi row insert
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: 94
Location: India

PostPosted: Mon Mar 29, 2010 5:58 pm    Post subject: Reply to: Multi row insert
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    Post subject: Reply to: Multi row insert
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    Post subject:
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: 3158
Location: Tucson AZ

PostPosted: Mon Mar 29, 2010 8:19 pm    Post subject:
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: 2150
Location: At my coffee table

PostPosted: Mon Mar 29, 2010 8:40 pm    Post subject:
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    Post subject:
Reply with quote

http://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

Senior Member


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

PostPosted: Mon Mar 29, 2010 9:34 pm    Post subject:
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    Post subject:
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    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 Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts How to insert a lengthy string havin... vidyaa DB2 7 Thu Aug 25, 2016 5:20 pm
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Reading selected volumes of a multi-v... RickBig JCL & VSAM 6 Wed Jul 13, 2016 7:26 pm
No new posts How do I right justify a string in an... rakesh17684 DB2 1 Wed Jun 08, 2016 8:01 pm


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