Error Connecting to z/OS DB2

[RoyM]

Hi, I'm getting an error when I attempt to connect to z/OS DB2 from MS Access.

My PC is running Windows 64 and my MS Access 2016 is 64-bit.

I have checked the ODBC DB2 64 drivers(?) by executing odbcad32.exe in the c:\windows\system32 directory, and can connect to the DB2 environment, DB2U, from within odbcad32.exe.

In MS Access, to connect to DB2:
1. I click on the "External Data" tab
2. Then click on "New Data Source"
3. Then click on "From Other Sources"
4. Then click on "ODBC Database"
5. Then click on "Link to the data source by creating a linked table". A "Select Data Source" window pops up.
6. Then click on the "Machine Data Source" tab.
7. Then I scroll down "System Data Sources" to the DB2 environment, DB2U I want to open and click on it. It is a 64-bit driver.
8. At which point I get an error window:
ODBC--call failed.
Specified driver could not be loaded due to system error 1114: A dynamic link library (DLL) initialization routine failed. (IBM DB2 ODBC DRIVER 0 DB2COPY1, C:\PROGRA~1\IBM\SQLLIB\BIN\DB2CLIO.DLL). (#160)

So, I executed odbcad32.exe in the c:\windows\sysWOW64 directory and created a 32-bit driver for the DB2 Environment, which I named DB2USER. When I attempt to connect to it, using the same steps I outlined above (except I click on DB2USER, not DB2U), I get an error window:
ODBC--call failed.

I then requested full Administrator rights from the appropriate team in my company and opened MS Access as an Administrator. When I attempt to connect to DB2U (64-bit), I get the exact same error window as specified above in 8.

I then used the steps outlined above, but selected DB2USER (32-bit) instead, and was then able to view the z/OS DB2 tables, for which I selected one of them. I can then view the data; however I cannot INSERT/DELETE any rows.

So, then I re-opened MS Access with normal authority (non-Admin), and I tried to fix the link to the z/OS DB2 table:
1. By right-clicking on the linked table
2. Then clicked on "Linked Table Manager", at which point a "Microsoft Access Security Notice" window pops up warning me that it's possible the content is untrustworthy, blah, blah, blah.
3. Click the "Open" button
4. Click on the little box to the left of the table name, which has to the far right "DSN=DB2USER
5. Click on the "Ok" button, at which point the "Select Data Source" window pops up (see the first 5. above)
6. I select "DB2USER"
7. And an eror window pops up:
Microsoft Access has stopped working
Windows can try to recover information and restart the program
8. And it gives me choices to click on:
Restart the program
Debug the program

When my PC was Windows 7, I was able to INSERT/DELETE without any difficulty, and without needing Admin authority.

Does anyone know how to get this working?

Thanks, Roy

 

[sonic8]

I then used the steps outlined above, but selected DB2USER (32-bit) instead, and was then able to view the z/OS DB2 tables, for which I selected one of them. I can then view the data; however I cannot INSERT/DELETE any rows.

From your description of the issue I conclude you have got 32Bit Access installed, not 64Bit!


Were you referring to Windows Administrator permissions or DB2 admin permissions?



For the issue of the data being read-only, I would check the usual suspects such as ODBC-Driver settings as well as the structure of the involved tables (PK?).

 

[RoyM]

Hi Sonic8, No, you are mistaken. The version is: "Microsoft Access 2016 MSO (16.0.8431.2046) 64-bit". Yes, I am referring to Windows Admin rights. I am a DB2 Admin. Regardless, I have looked at the ODBC connections and there is no place that I can find where I can select read-only or read-write for the DB2 connection.

 

[sonic8]

No, you are mistaken. The version is: "Microsoft Access 2016 MSO (16.0.8431.2046) 64-bit".

Well, I can't rule that out. However, then something peculiar is going on with your configuration. The 32Bit driver is not supposed to work with 64Bit Access at all.

I suggest you double check to which driver your DSNs are pointing.


DB2CLI.DLL is the 32bit driver
DB2CLIO.DLL is the 64bit driver

Regardless, I have looked at the ODBC connections and there is no place that I can find where I can select read-only or read-write for the DB2 connection.

I wasn't thinking about a simple ReadOnly=Yes/No setting but about one of the dozens of compatibility settings that might affect how Access can deal with the DB2 data. - Unfortunately it's been a very long time since I used a DB2 backend for Access, I can't remember any of the settings I used back then.

 

[RoyM]

How do I see which driver the datasets are pointing to?

How can I re-load the DB2CLIO.DLL file? When I attempted to register it:
c:\windows\sysem32\regsvr32.exe db2clio.dll

I get an error window:
The module "db2clio.dll" was loaded but the entry-point DIIRegisterServer was not found.
Make sure that the "db2clio.dll" is a valid DLL or OCX file and then try again.

 

[sonic8]

How do I see which driver the datasets are pointing to?

The settings are stored either in (User DSN):
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI

...or (Machine DSN):
HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI

Unfortunately, I do not know how these entries are separated between 32 and 64 bit.

How can I re-load the DB2CLIO.DLL file? When I attempted to register it:
c:\windows\sysem32\regsvr32.exe db2clio.dll

Reload? I don't understand what you are trying to do. I'm not sure if you can use regsvr32 to register ODBC drivers. - I rather think not.