Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Data Access between DB2 databases 1
- Thread starter damianp
- Start date
-
1
- #2
Check on federation to have direct access to AS/400 tables from other DB2 system. It is included in DB2 Connect or DB2 ESE.
You create mappings for your server and then you can access the table data using nicknames. For a quick start open Control Center for a Windows database and let it guide you through Federated Database Objects.
Remote updates are possible, but no 2-phase commit (2-phase commit is needed when updating local and remote data in one transaction)
Another option is to catalog your AS/400 on Windows and define it as an ODBC data source.
If it is "just" about moving data, I guess db2move is a good option to get data from one platform to another.
Juliane
You create mappings for your server and then you can access the table data using nicknames. For a quick start open Control Center for a Windows database and let it guide you through Federated Database Objects.
Remote updates are possible, but no 2-phase commit (2-phase commit is needed when updating local and remote data in one transaction)
Another option is to catalog your AS/400 on Windows and define it as an ODBC data source.
If it is "just" about moving data, I guess db2move is a good option to get data from one platform to another.
Juliane
I am currently handling this by using MS Access pass-through queries to extract the data from our AS400 into flat files, and then using DB2's load command to load into my target database. How would DB2's federation compare to this in ease of use and performance?
ddiamond,
Two different stories:
one is moving data: this is what you are doing.
another is accessing the data but leaving it in the source db, that is what federation is about.
Once setup, federation is easy to handle. Nicknames will be used, so it will be normal SQL. It is intelligent enough only to send data it actually needs. Since the aim here is not so much moving data, I have no idea how to compare. The moving data staement would look like
insert into ... select .. from ...
Still - for me it 'feels' performing ok
Anyway, when you use pass-through queries it is like a straight SQL export against the AS/400 DB2, so at least here it should be similar.
still, if you need to move data on a regular base, federation is easy enough. Another option for regular data movement would be replication. With MQ this is really fast, in V8 they put a lot of effort in ease of use ...
if it is done only once, I guess I'd go with db2move. Fine with the host, I have no experiences on AS/400 itself doing it.
Juliane
Two different stories:
one is moving data: this is what you are doing.
another is accessing the data but leaving it in the source db, that is what federation is about.
Once setup, federation is easy to handle. Nicknames will be used, so it will be normal SQL. It is intelligent enough only to send data it actually needs. Since the aim here is not so much moving data, I have no idea how to compare. The moving data staement would look like
insert into ... select .. from ...
Still - for me it 'feels' performing ok
Anyway, when you use pass-through queries it is like a straight SQL export against the AS/400 DB2, so at least here it should be similar.
still, if you need to move data on a regular base, federation is easy enough. Another option for regular data movement would be replication. With MQ this is really fast, in V8 they put a lot of effort in ease of use ...
if it is done only once, I guess I'd go with db2move. Fine with the host, I have no experiences on AS/400 itself doing it.
Juliane
Julian,
Thanks for your reply. We are moving the data on a nightly basis. For most of our queries, insert into... select... from... would work fine. But for some of our larger queries, this would be too slow. For example, one of our queries returns 1.3 million records. The Insert Into statement takes about 35 minutes. Loading it from a flat file only takes one minute...
...but I wonder. The load command can use a cursor instead of a flat file. Could I get similar performance by defining a cursor against a federated object, and then using the load command with that cursor? My goal is to pull MS Access and flat files out of the loop.
- Dan
Thanks for your reply. We are moving the data on a nightly basis. For most of our queries, insert into... select... from... would work fine. But for some of our larger queries, this would be too slow. For example, one of our queries returns 1.3 million records. The Insert Into statement takes about 35 minutes. Loading it from a flat file only takes one minute...
...but I wonder. The load command can use a cursor instead of a flat file. Could I get similar performance by defining a cursor against a federated object, and then using the load command with that cursor? My goal is to pull MS Access and flat files out of the loop.
- Dan
Well,
I just re-read in the documentation and yes, load with cursor appears to be a valid option.
You declare the cursor and open it and then using it as input for load sounds do-able with federation. Unfortunately I forgot to test that when fooling around with II, I'll sure give it a try next week.
I have no idea about performance. I mean the load can't be faster than the extract and network, but this will be very fast.
With federation you actually are able to create an MQT locally based on remote table data. Then overnight you could do a refresh - and there you are. Still - I have no idea how long the recalculation takes, but it is an option to copy the data. Performance would be a point to try out as well. And I have no idea whether this is actually logged or not ...
when you have a licence also for II replication and MQ, then you could give replication a try.
That is asynchronous reading the logfiles, and MQ is a really fast and safe pump. Setup is easier than expected, but of course licence fees are quite huge ....
SQL replication is not bad either, and asynchron as well. And they did quite a job on making it more robust and faster. Even the setup tools and GUIs in V8 actually can be used now ... ;-) So this is a similar option without MQ.
Replication is good from technology point of view, but it is expexive in licenses.
You have to automate with flat files anyway, so why not using db2move or extracting the data using export and pumping it using load ? I can't see the point why in the first place you have to use Access in between at all. I mean you need the client anyway for the ODBC interface, then you can export there as well directly and load it into the local db2 ? This would not avoid flat files, but it avoids Access at least.
Juliane
I just re-read in the documentation and yes, load with cursor appears to be a valid option.
You declare the cursor and open it and then using it as input for load sounds do-able with federation. Unfortunately I forgot to test that when fooling around with II, I'll sure give it a try next week.
I have no idea about performance. I mean the load can't be faster than the extract and network, but this will be very fast.
With federation you actually are able to create an MQT locally based on remote table data. Then overnight you could do a refresh - and there you are. Still - I have no idea how long the recalculation takes, but it is an option to copy the data. Performance would be a point to try out as well. And I have no idea whether this is actually logged or not ...
when you have a licence also for II replication and MQ, then you could give replication a try.
That is asynchronous reading the logfiles, and MQ is a really fast and safe pump. Setup is easier than expected, but of course licence fees are quite huge ....
SQL replication is not bad either, and asynchron as well. And they did quite a job on making it more robust and faster. Even the setup tools and GUIs in V8 actually can be used now ... ;-) So this is a similar option without MQ.
Replication is good from technology point of view, but it is expexive in licenses.
You have to automate with flat files anyway, so why not using db2move or extracting the data using export and pumping it using load ? I can't see the point why in the first place you have to use Access in between at all. I mean you need the client anyway for the ODBC interface, then you can export there as well directly and load it into the local db2 ? This would not avoid flat files, but it avoids Access at least.
Juliane
- Status
Similar threads
- Locked
- Question