Sqlcode =-253

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Hi all

I am posting this becoz the admin thinks I have posted the query after 8 years. Since the issue is same I have used the thread.
ibmmainframes.com/viewtopic.php?p=344297#344297
Please let me know why I am getting duplicate error message for -180 date issue while I am having date format mentioned in the above thread.

Thanks
Murali

Robert Sample · Posted: Sun Jul 22, 2018 6:57 pm

So which is it -- are you getting a -253 SQL code, or are you getting a -180 SQL code?

Google is your friend -- Googling sql code -253 returned about 246,000,000 results and the first one (for me) was www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/codes/src/tpc/db2z_n.html which describes the cause of -180 AND -253 SQL codes.

And considering your posts, perhaps you should consider going to Beginners and Students Forum instead of this one, as it appears the other forum is more suited to your ability. And ability is NOT related to "years" of experience -- you may well have one year of experience repeated by however many years you have been in IT.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Robert

I got -253 and when I use get diagnostics condition to identify which row out of the row set causing the issue I am getting sqlcode 80L (duplicate row) but the row is not an duplicate row. The actual sqlcode expected is -180 since I have modified the record in the date field to get -180 but in actual I got -803. I have validated the multi row records and table data it doesn't have any duplicate record.

EXEC SQL GET DIAGNOSTICS CONDITION :i
:ret_sqlcode = DB2_RETURNED_SQLCODE,
:ret_sqlstate = RETURNED_SQLSTATE,
:row_num = DB2_ROW_NUMBER;

So Robert if anyone get this kind of error and posting this in a forum would determine his ability?

I will send the data relevant to the post tommorow.

Robert Sample · Posted: Sun Jul 22, 2018 11:11 pm

Rohit Umarjikar · Posted: Mon Jul 23, 2018 3:24 am

The row can be duplicate in the result set and not in the insert table. Put them in the file to see duplicate row to troubleshoot the cause.

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Assume I have input file as

Date name phone
------------------------------------------------
2018-12-12 test0 1800-180-01
2018-12-12 test0 1800-180-01
2018-20-12 test1 1800-200-01
2018-20-12 test1 1800-201-01

The index in db2 is date and phone

If I have row set for 10 rows and I use insert query I am getting sqlcode -253 and in get diagnostics condition I am getting-803 For all 4 rows. Where as foe third and fourth row I have to get -180. I am giving this as example. Will share the code and sql error message

Rohit Umarjikar · Posted: Mon Jul 23, 2018 4:50 pm

Why do you think you should get it -180 in case of multi-insert? First do what db2 asks to do, so as said above the duplicates are part of your result set ( 3 and 4 row) which gives you -803 Because at this point db2 is thinking to insert them all at one shot ( which is not the case with Singleton inset) and then it first encountered duplicate on index and then later comes the date valdidation so make them unique first with bad dates and then you should get -180. Plus your phone number values looks truckented as well by two bytes, you might as well wants to fix that too.