We have a request from a customer to use their mainframe as a database server with a .NET client application as the front end. My plan is to try this out with a small test app written in VB or C# that targets the 3.5 framework and uses the latest LLBLGen version before we consider the feasibility of their request. If anyone has any experience with this type of architecture I would appreciate any of the following information:

1. Does the DB2 driver in LLBLGen support a mainframe database or just a PC DB2 database?

2. When we use LLBLGen to converse with an Oracle database from .NET we also need to install ODP .NET. Is there a similar provider for DB2?

3. If you know of any pitfalls to this plan or have horror stories, I'm interested in those as well.

About the driver/provider This is copied form manual (LLBLGenPro Help - Concepts - Database drivers):

IBM DB2 UDB

• IBM DB2 UDB v7.x/8.x/9x.
• Tables, views, sequences, identity columns and procedures.
• All native IBM DB2 UDB types, including *LOB.
• No support for user defined types.
• No support for table / view aliasses. A table / view alias is a public alias for an existing schema table and defined as such in the schema. Aliasses for tables in queries are supported.
• No support for iSeries DB2 installations.

Required for DB2: IBM DB2 .NET provider, shipped with the latest ClientAccess version, also available through the DB2 personal edition installation, or through the IBM website for DB2 licensees.

Compiling the generated code (Ref: LLBLGenPro Help - Using the generated code - compiling the code):

If you're using the IBM DB2 v8.1.2.1 provider instead of the newer v9.0.0.2, please reference the DQE dll for IBM DB2 v8.1.2.1 from the IBMDB2v8121 folder in the DotNetxy folder of your .NET version in the RuntimeLibraries folder instead. This DQE is compiled against v8.1.2.1 of the IBM DB2 provider.

Many months later I have finally successfully connected to the mainframe DB2 database. I'm trying to create an LLBLGen project now. After entering the DB2 connection information in the LLBLGen GUI, the connection was successfully established, but I received the following error when I pressed the Create button:

ERROR [42704] [IBM][DB2] SQL0204N "SYSIBM.SYSSCHEMATA" is an undefined name. SQLSTATE=42704

I have read elsewhere in the forum that this is probably because the login I'm using does not have authority to access this table/view. Indeed I cannot query this table/view when I'm logged into the mainframe with the same user id. I want to avoid requesting access to system tables one at a time. Do you have a published list of DB2 system tables/views that are used by LLBLGen?

Components used:

DB2 Database v7.1 DB2 .NET provider v8 .NET Framework 1.1 LLBLGen 1.0.2005.1

you can check out the sourcecode of the DB2 driver in the SDK

To make it easier, I've enlisted the tables accessed below (all read-only access, so no writing/updates) SYSIBM.SYSSCHEMATA SYSIBM.SQLFOREIGNKEYS SYSIBM.SYSTABCONST SYSIBM.SYSKEYCOLUSE SYSIBM.SYSSEQUENCES SYSIBM.SYSTABLES SYSIBM.SYSROUTINES sysibm.columns sysibm.syscolumns SYSIBM.SQLPRIMARYKEYS SYSIBM.SYSROUTINEPARMS

Thanks. I did search through all the source code in the LLBLGen installation directory for "SYSIBM", but I didn't find the things in your list. I guess that means the SDK is an optional download. I'll check the site later to find it.

Yes the SDK is an optional download for llblgen customers.