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
 
Compare the data in flat file with DB2 Table

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

New User


Joined: 12 Mar 2009
Posts: 21
Location: chennai

PostPosted: Fri May 21, 2010 7:37 am    Post subject: Compare the data in flat file with DB2 Table
Reply with quote

Hi

I have one requirement like I need to compare the flat file data(which is having two cloumn) with DB2 table. I need get the matched records and unmatched records. It would be great if some one help me out. I am new to DB2 programming.

Thanks,
Bala
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 21, 2010 8:42 am    Post subject:
Reply with quote

Hello,

Depending on the content of the file and the table, there would be different ways to do this.

You need to post some example data from the file and from the table and show the output you want from this sample data.

Keep in mind that you know exactly what you are talking about and no one here does. . .

One way to do what you want is to unload the table, sort the table data and the sequential file into the same "key sequence", and then compare the sorted files.
Back to top
View user's profile Send private message
balajipradeep

New User


Joined: 12 Mar 2009
Posts: 21
Location: chennai

PostPosted: Fri May 21, 2010 9:21 am    Post subject:
Reply with quote

Hi,

Actually i have the dataset with 2 cloumns for example policy(10 bytes ), plan(4 bytes). I have around 1200 policies. I need to chek this policy& plan combination available in the DB2 table.I cannot unload the table since the table size is large.

Please let me know if you need any other information.

Thanks,
Bala
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 21, 2010 9:27 am    Post subject:
Reply with quote

Hello,

Yes, you need to post more information. . . As i mentioned before - you know all about what you want to do - we have no idea. So, you have to explain completely with examples of the data (both input and output).

Is policy/plan a key to the table? How large is large? Does this mean there will typically be more "keys" in the table than the file? What should be done about keys that are in the table but not the file?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri May 21, 2010 2:18 pm    Post subject:
Reply with quote

without unloading the table I see no way to do this without programming a little program :
Code:
read record
perform until eof
       select row
       if sqlcode +100 => not present
       read record
end-perform
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 21, 2010 7:16 pm    Post subject:
Reply with quote

Hello,

And if the table is high volume and the selection criteria is not a key, there will be an entire table traversal for every record in the sequential file. . . Thus dimming the lights in the data center. . .

The people who run the system may come looking for you with heavy/sharp objects with which to inflict pain/damage. . . icon_neutral.gif

So answering the questions and providing info about the data will be in your best interest.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri May 21, 2010 7:45 pm    Post subject:
Reply with quote

Minimal knowledge and performance consideration is essential. When I tell someone to drive from Paris to Rome, I expect him to follow the roads and preferably the highways, because driving through people backyards and rivers would be cumbersome and will not be appreciated.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri May 21, 2010 10:10 pm    Post subject: Reply to: Compare the data in flat file with DB2 Table
Reply with quote

Quote:
because driving through people backyards and rivers would be cumbersome and will not be appreciated.
But think of all of the neat "stuff" that would be missed by staying on the pavement. . . icon_smile.gif
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat May 22, 2010 6:01 am    Post subject:
Reply with quote

Surely, sounds like an adventure....
Back to top
View user's profile Send private message
PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Sat May 22, 2010 4:39 pm    Post subject:
Reply with quote

Quote:
without unloading the table I see no way to do this without programming a little program


No EComp can easily do that ( and more ).

Cross compare : DB2-VSAM-SAM-MQ-SDSF-SPUFI output-...
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat May 22, 2010 7:54 pm    Post subject:
Reply with quote

Balaji,

It can be done using SAS with DB2, but first you need to setup a few things like,
Code:
//HXSULLA  JOB (T,TEST),'SAS DB2',MSGCLASS=X,CLASS=B,             
//      NOTIFY=HXSULL                                             
//********************* COMMENTS *********************************
//** CREATE SASLIB & DESCRIPTOR                                ***
//****************************************************************
//STEP1       EXEC PGM=IEFBR14                                   
//SASLIB     DD    DSN=HXSULL.DOPR.SASDB2.BOBT1.DESCLIB,         
//            DISP=(NEW,CATLG,DELETE),                           
//            SPACE=(TRK,(25,5)),                                 
//            UNIT=TEST                                           
//****************************************************************
//STEP1       EXEC SAS                                           
//DB2LIB     DD    DSN=HXSULL.DOPR.SASDB2.BOBT1.DESCLIB,         
//         DISP=OLD                                               
//SYSIN      DD     *                                             
PROC ACCESS DBMS=DB2;                                             
CREATE DB2LIB.TSBOBBY.ACCESS; /* DESCRIPTOR MEMBER NAME*/         
SSID=DB2D;                                                       
TABLE= BOBT1.COUNTRY;          /* FULL DB2 DATA TABLE NAME */         
RENAME                                                               
    /* ****************************** */                             
   /* DB2 NAME          = SAS NAME */                                 
   /* ****************************** */                               
ID                   = OID                                           
NAME                 = ONAME                                         
DESCRIPTION          = ODETAIL;                                       
                                                                     
CREATE DB2LIB.TSBOBBY.VIEW; /* DESCRIPTOR MEMBER NAME FOR VIEW */     
SELECT ALL;                                                           
RUN;                                                                 
                                                                     
PROC CONTENTS DATA=DB2LIB._ALL_;                                     
/*                                                                   


In the above code, you have to specify your sub-system & creator.tablename
Code:
SSID=subsystem-id;                                                       
TABLE= creator.tablename;          /* FULL DB2 DATA TABLE NAME */         


And these are, db2-table-column-names = sas-variables
Code:
ID                   = OID                                           
NAME                 = ONAME                                         
DESCRIPTION          = ODETAIL;                                       


After setting all the above, you can modify the below as per your requirement. And if you are new to SAS as me, try step-by-step.
Code:
//SASSTEP EXEC  SAS                                               
//INFILE  DD DISP=SHR,DSN=HXSULL.QUERY.TEST                       
//SASLIB  DD DISP=OLD,DSN=HXSULL.DOPR.SASDB2.BOBT1.DESCLIB         
//SYSIN  DD *                                                     
OPTIONS NOCENTER;                                                 
DATA FILE;                                                         
INFILE INFILE;                                                     
 INPUT ID 3.0                                                     
       @5 NAME & $CHAR10.                                         
 ;                                                                 
PROC SORT DATA=FILE; BY ID;                                       
PROC PRINT DATA=FILE;                                             
 TITLE2 'DATA FILE';                                               
                                                                   
DATA DB2DATA;                                                     
  SET SASLIB.TSBOBBY;                                             
  ID    = OID;                                                     
  NAME  = ONAME;                                                   
  KEEP ID NAME;                                     
PROC SORT DATA=DB2DATA; BY ID;                     
PROC PRINT DATA=DB2DATA;                           
 TITLE2 'DB2 DATA';                                 
                                                   
DATA MATCH NMATCH;                                 
 MERGE FILE(IN = A) DB2DATA(IN = B); BY ID;         
 IF A AND B THEN OUTPUT MATCH;                     
 IF A AND NOT B THEN OUTPUT NMATCH;                 
 PROC PRINT DATA=MATCH;                             
 TITLE2 'MATCH';                                   
 PROC PRINT DATA=NMATCH;                           
 TITLE2 'UNMATCH';                                 



Reading the input file,
Code:
DATA FILE;                                                         
INFILE INFILE;                                                     
 INPUT ID 3.0                                                     
       @5 NAME & $CHAR10.                                         
 ;                                                                 


And reading the table using the below
Code:
DATA DB2DATA;                                                     
  SET SASLIB.TSBOBBY;                                             
  ID    = OID;                                                     
  NAME  = ONAME;                                                   
  KEEP ID NAME;                                     
PROC SORT DATA=DB2DATA; BY ID;                     
PROC PRINT DATA=DB2DATA;                           
 TITLE2 'DB2 DATA';                                 


This will get your result
Code:
DATA MATCH NMATCH;                                 
 MERGE FILE(IN = A) DB2DATA(IN = B); BY ID;         
 IF A AND B THEN OUTPUT MATCH;                     
 IF A AND NOT B THEN OUTPUT NMATCH;                 
 PROC PRINT DATA=MATCH;                             
 TITLE2 'MATCH';                                   
 PROC PRINT DATA=NMATCH;                           
 TITLE2 'UNMATCH';                                 


Quote:
I am new to DB2 programming.
I think, this is more of SAS than DB2

Happy Learnings,
Sushanth
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts opening a dataset after reading it fr... arunsoods DFSORT/ICETOOL 5 Wed Oct 04, 2017 3:54 pm
No new posts Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am

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