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
 
Need suggestion on how to force referential integrity

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

New User


Joined: 22 Dec 2007
Posts: 90
Location: Bangalore

PostPosted: Wed Apr 25, 2018 9:22 am    Post subject: Need suggestion on how to force referential integrity
Reply with quote

Hi,

I need suggestion on below scenario.

We are trying to create 4 new tables. Details on the tables given below..

1. D1 table
2. D2 table
3. A0 table
4. T0 table


Now the data will be populated into these table from one file which we will receive from outside. The record will signify this..

D1 and D2 are 2 different main detail record type and it will have it's corresponding A0 and T0 records. Means data will come like D1/A0/T0 or D2/A0/T0. There is unique key (25 char) which will connect D1 or D2 with their respective A0 and T0.

Now we are thinking to insert all D1 only records to D1 table and D2 only records to D2 table. But all A0 and T0 (belongs to both D1 and D2) will got to A0 and T0 table.

Now I was thinking how to create a way that..

1. when D1 data will be deleted from D1 table then D1's corresponding (I mean same unique key which is there in A0 and T0) A0 and T0 records also should automatically gets deleted from A0 and T0 tables

2. when D2 data will be deleted from D2 table then D2's corresponding (I mean same unique key which is there in A0 and T0) A0 and T0 records also should automatically gets deleted from A0 and T0 tables

Could you please help me to find which would be the best way to implement the above concept (can we use 'ON DELETE CASCADE' or something like DB2 Trigger??)

NOTE - I was thinking to introduce Foreign key but as A0 and T0 tables are common to both D1 and D2 can we have that ?

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

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1191
Location: Bangalore,India

PostPosted: Wed Apr 25, 2018 10:03 am    Post subject:
Reply with quote

Hello there,

Yes your approach is correct. Lets take it like this

D1 primary key dpk1
D2 Primary key dpk2

Now you need to define A0 and T0 LIKE

A0 should have column dpk1 and dpk2
Similarly T0 should have dpk1 and dpk2

I am assuming there is no relationship between A0 AND T0

now you need to create 4 RIs like below

ALTER TABLE A0 FOREIGN KEY A0_FK1
(DPK1)
REFERENCES D1
(DPK1)
ON DELETE CASCADE ENFORCED ;

ALTER TABLE A0 FOREIGN KEY A0_FK2
(DPK2)
REFERENCES D2
(DPK2)
ON DELETE CASCADE ENFORCED ;

ALTER TABLE T0 FOREIGN KEY T0_FK1
(DPK1)
REFERENCES D1
(DPK1)
ON DELETE CASCADE ENFORCED ;

ALTER TABLE T0 FOREIGN KEY T0_FK2
(DPK2)
REFERENCES D2
(DPK2)
ON DELETE CASCADE ENFORCED ;

Please let me know if you need any other details
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 90
Location: Bangalore

PostPosted: Thu Apr 26, 2018 1:28 pm    Post subject:
Reply with quote

Hi Guptae,

Thanks for quick reply and I apologize for delay in my reply. Sure I will try below approach. I will come back to you if I face any issue or if there is minor requirement change (as you know client does changes abruptly)..

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

Senior Member


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

PostPosted: Fri Apr 27, 2018 7:38 am    Post subject:
Reply with quote

How come you don’t have Data Modeller in your firm? This questions ain’t suppose to be discussed here and made decisions.
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 90
Location: Bangalore

PostPosted: Sat Apr 28, 2018 2:15 pm    Post subject:
Reply with quote

Hi Rohit,

Your question is absolutely correct and the answer is 'yes' they have!. But the main issue is currently we were informed that our DBAs are very short handed so for our new project what we are doing is we are creating what all we can and understand from our end.

I wanted to make sure if my idea towards above subject is correct. We have create our compete data dictionary on our own in Excel and gave that to our DBA I guess they will pass the date to Powerbuilder tool to generate table design.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Sat Apr 28, 2018 8:45 pm    Post subject:
Reply with quote

DBAs comes in a play after Data Modeler instructs them with DDL to create. So they are different groups of people.

Anyways , why do you need 4 tables ? Why not D1D2 goes into one table and AO, TO goes to one table? Just add a identifier column in each of the table to know what is what entry, because imagin, if you get new D3,D4 and so on then you can’t keep creating new tables right? Just change the identifier type and insert into one D table.
Moreover use triggers to achieve what you want.
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 90
Location: Bangalore

PostPosted: Tue May 08, 2018 1:23 pm    Post subject:
Reply with quote

Hi Rohit,

I do agree with your suggestion and I suggested them to add both D1 and D2 into one table (if they need they can simply query with record types along with other details in where clause) but the issue is both these types of records will be needed by two different streams (outside to our application). So they decided to keep them separate so they two different streams can make use those.

We are expecting D3 rec type soon icon_smile.gif

I used Trigger to achieve the result.

Again Thanks! for replying. I always need all your suggestions.

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

Senior Member


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

PostPosted: Tue May 08, 2018 7:26 pm    Post subject:
Reply with quote

You are Welcome!!
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 force a select query to abend neo4u DB2 7 Mon Apr 23, 2018 1:36 pm
No new posts Need urgent suggestion on DFSORT subratarec DFSORT/ICETOOL 6 Wed Mar 21, 2018 8:46 pm
No new posts Need Suggestion on COBOL program vickey_dw COBOL Programming 5 Thu Jan 05, 2017 10:55 pm
No new posts DB2 SQL help/idea/suggestion bshkris DB2 4 Thu Oct 06, 2016 3:04 pm
No new posts force windows ftp client user to defa... meykh2014 All Other Mainframe Topics 0 Sun Feb 14, 2016 12:57 pm

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