Connecting to DB2 on AS/400 via OLE DB

 

To connect to a DB2 database via OLE DB, you’ll need the  Microsoft OLEDB Provider for DB2 (IBM also has one), which is part of the Microsoft SQL Server 2008 Feature Pack. 

After installing the provider, there are several ways you can test connectivity: creating a UDL file, using the “Data Access Tool” that ships with the Microsoft provider, or creating an OLE DB Connection in BIDS to name a few.  Below is a screen shot of the test connection’s property grid.  As you might expect, I’ve replaced the values of user id, IP address, schema, etc., with fake ones.  Properties to note are:

• DBMS Platform: in my case, this is DB2\AS400
• Default Schema: same as the library name in DB2.  Ex., the library name in the following query is “LIBNAME”: “SELECT * FROM LIBNAME.FILENAME”
• Package Collection: same as above
• Initial Catalog: Your AS/400 DBA should be able to provide this.  Or, if you have access to a linked server, expand it in SSMS and you should see the catalog name.

At this point, my test connections were unsuccessful.  But the Microsoft OLEDB Provider for DB2 ships with a handy “SNA Trace Utility” that revealed more information:

|00000f50.000008b0 DDM   001B1153 E4E2C5D9 40C5E7C9 E340C4C5 D5C9C5C4 40C1C3C3 C5E2E2 
|00000f50.000008b0 DRDA AR message: Name: RDBATHRM, Severity: Error, Diagnostic: USER EXIT DENIED ACCESS, Database: S1033BC1

Notice the DRDA “DENIED ACCESS” message. Interestingly, the Microsoft OLEDB Provider for DB2 uses the DRDA protocol.  Below is a screenshot from a 5250 session with the AS/400.  Notice the configuration setting “DDM / DRDA request access”, which is configured to reject all DDM / DRDA connections.  Changing this value to “*OBJAUT” resolved the issue.  For more information, see the CHGNETA Command Description and look for DDMACC  (DDM / DRDA request access).  See also Microsoft KB article 246714.