|
View previous topic :: View next topic
|
| Author |
Message |
balajipradeep
New User
Joined: 12 Mar 2009
Posts: 21
Location: chennai
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
balajipradeep
New User
Joined: 12 Mar 2009
Posts: 21
Location: chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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. . . 
So answering the questions and providing info about the data will be in your best interest. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
| 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. . .  |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
| Surely, sounds like an adventure.... |
|
| Back to top |
|
|
PeD
Active User
Joined: 26 Nov 2005
Posts: 459
Location: Belgium
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
|
|
