Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 How to insert a value on specific pos... Poha Eater DFSORT/ICETOOL 9 Sun Oct 01, 2017 3:04 am
No new posts ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts PLI - printing from multiple programs Pedro PL/I & Assembler 8 Fri Sep 08, 2017 6:36 am
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us