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
 

 

Error handling in Multi Row Fetch/Insert

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

New User


Joined: 16 Aug 2006
Posts: 28
Location: india

PostPosted: Wed Feb 19, 2014 2:58 pm    Post subject: Error handling in Multi Row Fetch/Insert
Reply with quote

Hi DB2 Experts,
In case of Multi row Fetch/Insert how the SQL error handling is done to display the problematic record in to SPOOL.Reason is, it will be having set of records with in the MRF array of columns and how will we identify the problematic record .(Ex) if its -811/-803 in our MRF fetch/Insert ,how will we display the specific problematic record among the fetched rowset ? If is singleton query we will display it directly but in case of MRF/Insert I couldn't find the proper way of displaying the error record.Any help is much appreciated.

Thanks
Yogi
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Feb 19, 2014 3:22 pm    Post subject:
Reply with quote

Hi Yogi,

Look at SQLERRD(3)

Thanks,
Sushanth
[Always an amature]
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Wed Feb 19, 2014 3:30 pm    Post subject:
Reply with quote

As you were told by sushanth, SQLERRD(3) will have

Quote:
After a successful multirow insert, update, or delete operation, this field contains the number of rows that were processed. After a multirow insert, update, or delete operation that ends with an error, this field contains the number of rows that were successfully processed before the error was detected.


This page will give you more details about SQLERRD.
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst189.htm
Back to top
View user's profile Send private message
singhju

New User


Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon

PostPosted: Wed Feb 19, 2014 3:44 pm    Post subject:
Reply with quote

Hi,

I don't think -811 will ever occur in Multi Row Fetch as Multiple rows are fetched using a ROWSET POSITIONING cursor and cursors never fail with -811. Please correct me if this statement is wrong.
Back to top
View user's profile Send private message
yogi.47eie

New User


Joined: 16 Aug 2006
Posts: 28
Location: india

PostPosted: Wed Feb 19, 2014 5:52 pm    Post subject:
Reply with quote

Thanks team.. SQLERRD(3) I was using for number of rows fetched.Yes,as per your suggestion it will hold the last successful record processed.From whcih I can find the problematicc record.I wil use the same for coding purpose.
Apart from that,-811 will never occur in cursor,I realized after posting it.Thanks for your quick response team.
FYI:
SQLERRD(3)
After a successful multirow insert, update, or delete operation, this field contains the number of rows that were processed. After a multirow insert, update, or delete operation that ends with an error, this field contains the number of rows that were successfully processed before the error was detected.

Thanks
Yogi
Back to top
View user's profile Send private message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Thu Apr 03, 2014 11:50 pm    Post subject: Reply to: Error handling in Multi Row Fetch/Insert
Reply with quote

Use Get diagnostic ..
Just do some Google lot of info is available
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Fri Apr 04, 2014 10:35 am    Post subject:
Reply with quote

Prasun, I think TS has got what he wants more than a month back.

Quote:
Use Get diagnostic ..


It is good to have an alternative but this would add one more I/O to the process than using the existing SQLERRD(3) of SQLCA.
Back to top
View user's profile Send private message
prasun dhara

New User


Joined: 01 Mar 2008
Posts: 49
Location: kolkata

PostPosted: Fri Apr 04, 2014 10:53 am    Post subject: Reply to: Error handling in Multi Row Fetch/Insert
Reply with quote

Hi Rohit,
Thanks for pointing out the performance factor.. But normally whenever we use multi row fetch we put the get diagnostic in the error handling.. I have seen this is being followed in multiple site.
SQLERRD(3) will only give the number ..

Thanks
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Apr 04, 2014 12:33 pm    Post subject:
Reply with quote

Hi,

I second Prasun for using GET DIAGNOSTIC.

But its better to use only when multi fetch statement returns sqlcode 354 considering performance factor pointed by Rohit

354 is returned when rowset FETCH statement encountered one or more warning conditions.

May be I am missing something here so GuyC or senior members inputs would be helpful

Regards,
Chandan
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Fri Apr 04, 2014 1:48 pm    Post subject:
Reply with quote

Chandan, yes you are right. but not just +354 but usually GET DIAGNOSTIC is invoked in case anything apart from SQLCODE 0 or +100 comes.

So Prasun, Your are too right and usually we see them more often in Stored Procedures as to have error log updated with detailed stats.
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 S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm
No new posts IDMS/DC-COBOL program - SNAP error wh... rakeshsekar1987 IDMS/ADSO 5 Tue Sep 13, 2016 8:28 pm


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