hi all,
what is the better way to join 2files or 1file&1databasetable or 2database tables.
how can we achieve join perform in serverjobs.i am using 7.5.1A
joining different sources in server jobs
Moderators: chulett, rschirm, roy
joining different sources in server jobs
Thanx&Regards
scorpion
scorpion
You join two database tables - if they are in the same database - in the database. Any other combination either involves hashed files or getting them together in the same database. This choice could also depend on the structures involved - do they lend themselves to direct joining or does the data need to be manipulated in some fashion in order to get them into a 'joinable' state?
For example - two files? If the metadata is the same and 'join' just means 'combine' you can concatenate them together in the operating system. Otherwise you need to put one in a hashed file and join the other to it on key columns as it is streamed in. Or use the Merge stage. Or load them both up into database work tables and join them there.
1 file and 1 table? Put one (whichever is appropriate) into a hashed file and do your lookup / join against it while you stream in the other. Or, again, load the file into a work table and join directly in the database.
2 database tables? Put one (whichever is appropriate) into a hashed file and do your lookup / join against it while you stream in the other. Or, again, unload the data from one database into a work table in the other database and join them directly there.
Sensing a theme here? And by no means is this an all inclusive list of How It Could Be Done. I'm always amazed at the many different ways clever people can come up with to solve the same problem with this tool. Or other tools.
For example - two files? If the metadata is the same and 'join' just means 'combine' you can concatenate them together in the operating system. Otherwise you need to put one in a hashed file and join the other to it on key columns as it is streamed in. Or use the Merge stage. Or load them both up into database work tables and join them there.
1 file and 1 table? Put one (whichever is appropriate) into a hashed file and do your lookup / join against it while you stream in the other. Or, again, load the file into a work table and join directly in the database.
2 database tables? Put one (whichever is appropriate) into a hashed file and do your lookup / join against it while you stream in the other. Or, again, unload the data from one database into a work table in the other database and join them directly there.
Sensing a theme here? And by no means is this an all inclusive list of How It Could Be Done. I'm always amazed at the many different ways clever people can come up with to solve the same problem with this tool. Or other tools.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi scorpion,
If you want to join 2 or more source file to one file, You need make sure Metadata is common or You need to map source file columns to common Metadata and link to Link Collector.
1). Make sure 2 source file / Database Table have common Metadata. Or
2). Use Transform Stage to Map to Common Metadata
3). Use Link Collector Stage and Link from Files.
4). Link LCC output to one file or to Database Stage.
5). Enable row buffer to Inter Process in Job Properties Performance tab.
Have Fun
Martin
If you want to join 2 or more source file to one file, You need make sure Metadata is common or You need to map source file columns to common Metadata and link to Link Collector.
1). Make sure 2 source file / Database Table have common Metadata. Or
2). Use Transform Stage to Map to Common Metadata
3). Use Link Collector Stage and Link from Files.
4). Link LCC output to one file or to Database Stage.
5). Enable row buffer to Inter Process in Job Properties Performance tab.
Have Fun
Martin