Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 table - cobol file - insurance policy

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
kumar119119

New User


Joined: 31 May 2010
Posts: 25
Location: Pune

PostPosted: Fri Jul 30, 2010 10:20 pm    Post subject: DB2 table - cobol file - insurance policy
Reply with quote

Hi Folks,
Advance Happy friendsip day to you all folks,

L&T interview question:

Interviewr:

I have an Insurance policy,

In my policy table there are 100 rows,
150 records are comes from third party file,

both record and row have same length,

I want to insert all records into my policy table,

But condition is:

1) If file record is already present in policy table...no need to insert ,
2) If record is not present...I need to insert in table,
3) If record is presnt in table...If any change I need to update,
4) If policy-num is not in file and :hv-pol-num is in table....I need to delete :hv-pol-num in DB2 policy table,

How you do all these?

So, please folks, help me in this,

I tried to explain..but he didn't satisfy,
If anyone have an idea please tell me,

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

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jul 30, 2010 11:15 pm    Post subject:
Reply with quote

With COBOL or some other programming language, it should be very easy for a software engineer.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Sat Jul 31, 2010 1:03 am    Post subject:
Reply with quote

As the question involves comparing table with file, file with table and as the total number of records to be handled is just 250, I would prefer a job with following steps,

1. Unload the table.
2. Delete all rows in the table.
3. Use sort utility to create the appropriate records to be inserted/updated in the table.
4. Load the table.

Of course, as Craq Giegerich has suggest, a simple COBOL program can also be used to accomplish this.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Jul 31, 2010 6:14 pm    Post subject:
Reply with quote

You might also choose to use CURSORs...
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Sun Aug 01, 2010 2:34 pm    Post subject:
Reply with quote

Hi Kumar,
This is something we did earlier....

for each 100 columns we made a standard copybook in which we have change_flags and then the actual records.

For example, if a db2 table had 3 columns then copybook will be

Code:

01 record.
   05  rec-flags.
      10   data-change pic x(01).
          88  add                             value 'A'.
          88  update                        value 'U'.
          88  delete                         value 'D'.
      10   empno-flag   pic x(01) value 'N'.
         88 empno_change          value 'Y'.
      10   empname-flag   pic x(01) value 'N'.
         88 empname_change          value 'Y'.
      10   empadd-flag   pic x(01) value 'N'.
         88 empadd_change          value 'Y'.
   05  rec-data.
      10 empno     pic  x(05).
      10 empname   pic  x(25).
      10 empadd    pic  x(50).



and now whenever we get data from 3rd party, we'll read the data from the input file and will check the data-change field and made the program to take the necessary action.
Later this turned to be the standard copybook and whenever we have to update the database we used the same copybook.

Note: this was just an example. You need to work around with your resources.

Thanks,
Santosh G.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Sun Aug 01, 2010 5:20 pm    Post subject:
Reply with quote

Arvind,
when you can get your user/third-party to annotate the data via the update/add/delete flag, you are in luck.

in kumar119119's situation, his user/third-party is just sending a file of data and it is up to kumar119119 to determine if update/insert/delete is the appropriate action based on a match against the existing db.

As Anuj indicated, use of a cursor can be used to perform the match merge/decision logic or unload as rochish suggested and then match merge the two qsam files and create a file like Arvind has suggested.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Aug 03, 2010 3:53 pm    Post subject:
Reply with quote

One more option assuming there is a timestamp field in the table.

Iterate the following process for all the records in the file.
1) Read the record from the file
2) Find for the policy number in the table and insert/update the record in the table.
Update timestamp even if there are no changes found between the record in the file and the table. This will make sure every record that needs to have an entry in the table is touched.

After reaching end of file, you can delete all the rows from the table which were not touched that day.
Back to top
View user's profile Send private message
kumar119119

New User


Joined: 31 May 2010
Posts: 25
Location: Pune

PostPosted: Tue Aug 10, 2010 12:52 am    Post subject:
Reply with quote

Thanx a lot to all of you for sharing your valuable time and replies for my post,

Thanx again.
Back to top
View user's profile Send private message
razesh84

New User


Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India

PostPosted: Fri Sep 24, 2010 2:25 pm    Post subject:
Reply with quote

considering the criteria, policy table will hold only the records coming from the file.so i guess a load replace will be a better way to achieve this.
Back to top
View user's profile Send private message
santosh.shet82

New User


Joined: 15 Apr 2009
Posts: 1
Location: Bangalore

PostPosted: Mon Sep 27, 2010 3:17 pm    Post subject:
Reply with quote

1) If file record is already present in policy table...no need to insert ,
2) If record is not present...I need to insert in table,
3) If record is presnt in table...If any change I need to update,
4) If policy-num is not in file and :hv-pol-num is in table....I need to delete :hv-pol-num in DB2 policy table,

There is another way for this:

Read the File and do a Update on the Policy Table,
If the SQLCODE is 00 then Point 3 is satisfied.
If the SQLCODE is -803 which is duplicate then skip processing the data then Point 1 is satisfied.
If the SQLCODE is +100, then write a Insert statement, Point 2 is satisfied.

Then Write a DELETE statement on Plicy table whose UPT_TIMESTAMP is Less than Current date, Point 4 is satisfied.

Hopefully I am correct.
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 10 Fri Aug 18, 2017 10:50 am
No new posts ALPHABETIC check in COBOL vidyaa COBOL Programming 8 Thu Aug 17, 2017 7:13 pm
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm
No new posts Multiple VSAM files single output fil... Mohan Kothakota DFSORT/ICETOOL 11 Wed Aug 09, 2017 7:57 pm
This topic is locked: you cannot edit posts or make replies. rexx code to create a ps file Bharath Vikraman CLIST & REXX 4 Mon Aug 07, 2017 10:30 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us