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
 

 

How to get SQLCODE for Multiple Insert

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

New User


Joined: 21 Jul 2007
Posts: 27
Location: Chennai

PostPosted: Fri Mar 13, 2009 9:53 am    Post subject: How to get SQLCODE for Multiple Insert
Reply with quote

Hi,

In COBOL-DB2 program(BMC), How to get SQLCODE for each row on Multiple Insert?. Depending on the return value of each row I have to do different process.

Is it possible to get?.
Back to top
View user's profile Send private message

abhishek mitra

New User


Joined: 24 Feb 2008
Posts: 48
Location: Kolkata

PostPosted: Fri Mar 13, 2009 10:32 am    Post subject:
Reply with quote

It depends on the process of insert. If you are using a cobol loop for multiple insert for each loop you will get SQLCODE easily.

but in case your are inserting more than one rows 'in a go' (INSERT through SELECT in sub query) then it is not possible to get SQLCODE for each row. Here you have to understand that COBOL replaces an 'EXEC SQL - END-EXEC' by API calls and the SQLCA gets updated after each API finish. Here an INSERT with SUB-SELECT (For muliple insert) generates a single API.

any thoughts are welcome on this !! icon_question.gif
Back to top
View user's profile Send private message
kalaikala

New User


Joined: 21 Jul 2007
Posts: 27
Location: Chennai

PostPosted: Fri Mar 13, 2009 10:59 am    Post subject:
Reply with quote

I not using cobol loop & also sub-query.

My method is,

You can insert the number of rows that are specified in the host variable NUM-ROWS by using the following INSERT statement:

EXEC SQL
INSERT INTO DSN8810.ACT
(ACTNO, ACTKWD, ACTDESC)
VALUES (:HVA1, :HVA2, :HVA3 :IVA3)
FOR :NUM-ROWS ROWS
END-EXEC.

My expectation is, getting SQLCODE for each row by array.[/b]
Back to top
View user's profile Send private message
abhishek mitra

New User


Joined: 24 Feb 2008
Posts: 48
Location: Kolkata

PostPosted: Fri Mar 13, 2009 11:14 am    Post subject:
Reply with quote

This is a good learning for me icon_biggrin.gif . I was unaware of this technique. But still my idea stays same here. A single 'EXEC SQL ... END-EXEC' generates a single API and SQLCA gets updated after each API execution (so is our SQLCODE). Hence Possibly getting "SQLCODE for each row by array" is looking difficult to me if not impossible.


Regards
Abhishek
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Fri Mar 13, 2009 12:44 pm    Post subject:
Reply with quote

Yes, I agree too. That we can't get sqlcode for each insert
Back to top
View user's profile Send private message
kalaikala

New User


Joined: 21 Jul 2007
Posts: 27
Location: Chennai

PostPosted: Fri Mar 13, 2009 2:15 pm    Post subject:
Reply with quote

Is there anyway to insert mutiple rows at a time?. and also i want to manipulate the return code.

Can anyone help me?!!!
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Mar 13, 2009 5:43 pm    Post subject:
Reply with quote

You can use get diagnostics.
GET DIAGNOSTICS
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 Changeman get multiple "Browse C... elixir1986 Compuware & Other Tools 4 Fri Jul 14, 2017 1:48 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm
No new posts Validating file with multiple header/... mohitsaini DFSORT/ICETOOL 6 Thu Apr 13, 2017 1:53 am


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