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
 
Sqlcode =-253

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

Active User


Joined: 15 Dec 2005
Posts: 259

PostPosted: Sun Jul 22, 2018 3:13 pm    Post subject: Sqlcode =-253
Reply with quote

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.
http://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
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8410
Location: Dubuque, Iowa, USA

PostPosted: Sun Jul 22, 2018 6:57 pm    Post subject:
Reply with quote

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 https://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.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 259

PostPosted: Sun Jul 22, 2018 8:57 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8410
Location: Dubuque, Iowa, USA

PostPosted: Sun Jul 22, 2018 11:11 pm    Post subject:
Reply with quote

Quote:
I am getting sqlcode 80L (duplicate row) but the row is not an duplicate row.
As long as you refuse to believe what the computer is telling you, you CANNOT resolve this issue -- period.
Quote:
I have validated the multi row records and table data it doesn't have any duplicate record.
Well, the computer tells you that there is duplicate data -- so either you believe what the computer tells you and fix that issue, or you continue to believe there is no duplicate data and keep having the problem.
Quote:
-803
AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X record-id
And note that the error message from the manual does NOT indicate a duplicate row -- it tells you that the index has duplicated data (so if a non-index column is different, the row is not duplicated but the error message is still true since it applies ONLY to the index columns).
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Mon Jul 23, 2018 3:24 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 259

PostPosted: Mon Jul 23, 2018 7:23 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Mon Jul 23, 2018 4:50 pm    Post subject:
Reply with quote

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.
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 SQLCODE 518 while executing DB2 module. abdulrafi DB2 8 Wed Feb 07, 2018 10:46 am
No new posts SQLCODE=100 and SQLSTAE 02000 pshongal DB2 12 Fri Jan 19, 2018 10:14 am
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am
No new posts SQLCODE-000000080N priya91 DB2 1 Mon Feb 01, 2016 4:35 pm
No new posts Need to understand SQLCODE = 12 rohanthengal DB2 3 Thu Nov 05, 2015 11:22 am

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