1. Create a Microsoft Data Link File (UDL) and use this from your application, or
2. Call the provider using a connection string (This is the method from SSIS).
We have a lot of projects in our organizations that require us to work with database systems other than SQL Server. While we obviously have our preference, these other systems are a fact of life in enterprise organizations and each lends it's unique pros and cons to any challenging project.
Recently I had a requirement to pull some data from DB@ UDB on AS400 ISeries for a prototyping project and this led to some confusion on my part as we did not have a DB2 DBA at the client to help us get the connectivity information. After working our way through that, I thought I would share some of the points we learned. 🙂
We were using the Microsoft OLE DB Provider for DB2, which is the primary method when using SSIS. The other way would include using the ISeries downloader to dump a large file and then bulk load it, but this makes handling type 1 and Type 2 dimensions difficult due to the amount of data you would need to export. We chose this driver, even though data coming out of DB2 can typically be slower than other systems due to the requirements for handling certain types of changes.
In order to use this provider, you need to either
The data source information for each host data source need to be configured using the Data Links Button in the provider setup.
Important parameters include:
Data Source: Use this to describe the data source
User Name: Remember the AS/400 is case sensitive to ID and password. It only accepts them in uppercase. IF it fails, the provider will resend forcing the uppercase.
Initial Catalog: This is the first part of a three part fully qualified table name. In DB2/AS400 this is referred to as the RDBNAM. Run the WRKRDBDIRE command from the AS/400 console to get this if it is unknown.
Default Schema: This is where the provider looks for catalog information. This issued to restrict result sets for things like enumerating table lists, etc..
Package Collection: This could be the same as the Default Schema, but it is the name of the DRDA COLLECTION where you want the driver to store and bind DB@ packages. This is a required parameter.
Also in SSIS you will get a warning when previewing the data regarding the code page, make sure you set the PC Code Page Property in the all section to 1252 for Latin 1 which is the most common.
With these common settings you should be able to get up and running and get some data out of DB2 efficiently and get working on your solution.
As always, don't forget to post questions in the BIDN forums.
