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
 
Insert giving -530

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

New User


Joined: 20 Dec 2006
Posts: 5
Location: mumbai

PostPosted: Thu Oct 08, 2009 5:42 am    Post subject: Insert giving -530
Reply with quote

I have multiple insert in my COBOL program and one of them is giving SQLCODE -530
The Program logic is as follows:

Enter program

Read a record

My first insert is on a table which has a Primary key ... This insert goes fine.. say Primay Key inserted was P1
.
.
.
.
My second insert is on table which has a Foriegn key contraint from the first table and now when I try inserting on this table with the same Primary Key P1 it throws an SQLCODE of -530 ..
.
.
.
If success
Comitt
Else
Rollback
.
.
.Read Next Record


----------------------------------
I suspect that the second INSERT statement cannot see the Primary key that was inserted in the First table since the commit is down below.
And I dont want to put the commit between them coz there are many insert lined up and the requirement is if one fails all should rollback.

I tried with changing the ISOLATION LEVEL to UR in the BIND but i'm still getting the same error.
I would appreciate your suggestions in fixing this issue.
Thanks
Back to top
View user's profile Send private message

satish.ms10

Active User


Joined: 10 Aug 2009
Posts: 161
Location: India

PostPosted: Thu Oct 08, 2009 9:52 am    Post subject:
Reply with quote

Hi,

As of my knowledge, until unless you commits the data on the first table for the newly inserted row you will not insert a row on second table which has a Foreign key constraint from the first table.

Please correct me if I am wrong.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Thu Oct 08, 2009 10:15 am    Post subject: Reply to: Insert giving -530
Reply with quote

Hi shoaib

As you hint about the error, it is a foreign key violation. DB2 is using buffer to process its data. DB2 will not immediately write all the data into the table from the buffer unless the program issues a COMMIT or the buffer get filled. So the possible correction is to issue a commit on the UOW by redefining your business logic.

Regards
Raghu
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 08, 2009 12:31 pm    Post subject:
Reply with quote

I don't think the previous answers are correct. (well I'm pretty sure)
probably something else is wrong.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Oct 08, 2009 2:48 pm    Post subject:
Reply with quote

Can you please let us know what is happening in case you bring the commit in between the two inserts ?
Test a single case and post the result pls
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Thu Oct 08, 2009 5:02 pm    Post subject: Reply to: Insert giving -530
Reply with quote

Quote:
with the same Primary Key P1


you are not, that is why you are receiving a -530 for the second insert.

Forget all the crap about commits.
It is your task,
all of your uncommited work is always available to your task.

the talk about a commit is simply rookie bullshit.

your problem is the data that you are using for the second insert does not jive with the parent table.

stop trying to find fault with db2. double check the values of your variables.
therein lies the problem.
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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm

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