Error handling in Multi Row Fetch/Insert

yogi.47eie · New User Joined: 16 Aug 2006 Posts: 45 Location: india

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

sushanth bobby · Posted: Wed Feb 19, 2014 3:22 pm

Hi Yogi,

Look at SQLERRD(3)

Thanks,
Sushanth
[Always an amature]

sureshpathi10 · Posted: Wed Feb 19, 2014 3:30 pm

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

singhju · New User Joined: 01 Dec 2010 Posts: 25 Location: Gurgaon

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.

yogi.47eie · New User Joined: 16 Aug 2006 Posts: 45 Location: india

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

prasun dhara · New User Joined: 01 Mar 2008 Posts: 49 Location: kolkata

Use Get diagnostic ..
Just do some Google lot of info is available

Rohit Umarjikar · Posted: Fri Apr 04, 2014 10:35 am

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

prasun dhara · New User Joined: 01 Mar 2008 Posts: 49 Location: kolkata

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

chandan.inst · Posted: Fri Apr 04, 2014 12:33 pm

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

Rohit Umarjikar · Posted: Fri Apr 04, 2014 1:48 pm

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.