Hi guys, I have the following problem. I am trying to get the r-package 'ibmdbR' to work. Since I am brand new to the whole IBM matter, please forgive me beginner mistakes.

First I downloaded the Db2 Community Edition () and installed it on my Windows PC. Here I did the following things in the installation client:

1. Installed the 'Db2 Version 11.5.8.0 Server Edition'. I used the default installation type, installed DB2 locally, used 'admin' for username and password and created a local contact list.

2. Then I created an ODBC connection according to this page (https://www.ibm.com/docs/en/iad/7.2.1?topic=s2iccwpa-configuring-odbc-data-source-db2-data-warehouse-1) and named it 'BLUDB'. I didn't add anything to the alias and clicked ok).

Then I went into R, loaded the package and ran the following command.

con <- con <- idaConnect('BLUDB','admin','admin').

But when I do this, I always get this error:

Warning messages:

1: In RODBC::odbcDriverConnect("DSN=BLUDB;UID=admin;PWD=admin", believeNRows = FALSE) :

[RODBC] ERROR: Status IM004, Code 0, Message [Microsoft][ODBC Driver Manager] Error in SQLAllocHandle call for driver on SQL_HANDLE_ENV

2: In RODBC::odbcDriverConnect("DSN=BLUDB;UID=admin;PWD=admin", believeNRows = FALSE) :

ODBC connection failed

What did I do wrong and how do I get it to work? I need to get this to work for university unfortunately.

You can find the guide here:

https://cran.r-project.org/web/packages/ibmdbR/vignettes/ibmdbR.pdf

------------------------------
Lorenz Wagner
------------------------------

#Db2forz/OS

Hi Lorenz, you posted your question in the Db2 z/OS forum which is actually a different flavor of Db2 running on the mainframe platform. But anyway, perhaps we can figure this out here. Just for my understanding:
You installed the Db2 11.5.8 Community Edition on your Windows PC and assigned user "admin" to the Db2 services, right? Does "admin" have administrator permissions on your Windows PC?
Your ODBC application is supposed to run on your Windows PC and should connect to the local Db2 instance. Did you create a database named "BLUDB" on your Db2 server? Can you open a Db2 Command Window (Windows button, then "IBM DB2 DB2COPY1 (Default)", then "DB2 Command Window") and enter:
db2 list db directory
This should show all available databases. If BLUDB is listed, then open MS ODBC Administrator (run "odbcad32" from command window) and check whether BLUDB is registered as either a System or User DSN. If it is listed, run a connection test from the ODBC Administrator window and see whether that works.
If it is not listed, then run
db2 catalog system odbc data source BLUDB
if you are running the Db2 Command Window with local administrator privileges. If you did not open the Db2 Command Window with administrator privileges, replace "system" with "user". Then open MS ODBC Administrator again and check that BLUDB is listed here (it is supposed to be). Retry the connection test. Before you start working with your R application we need to make sure that the basic Db2 and ODBC setup is correct.

------------------------------
Christoph Theisen
Rocket Software
------------------------------

Hello, thank you for the detailed reply. Sorry, I'm really a total beginner
1) How do I connect my pc to the local db2 unit?
2) When I run 'db2 list db directory', I get the following error:
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031

3) Consequently the command 'db2 catalog system odbc data source BLUDB' does not work either.
DB21036E The CATALOG SYSTEM ODBC DATA SOURCE command failed.
------------------------------
Lorenz Wagner
------------------------------

Original Message:
Sent: Tue November 08, 2022 03:19 AM
From: Christoph Theisen
Subject: Problems installing and using DB2

Hi Lorenz, you posted your question in the Db2 z/OS forum which is actually a different flavor of Db2 running on the mainframe platform. But anyway, perhaps we can figure this out here. Just for my understanding:
You installed the Db2 11.5.8 Community Edition on your Windows PC and assigned user "admin" to the Db2 services, right? Does "admin" have administrator permissions on your Windows PC?
Your ODBC application is supposed to run on your Windows PC and should connect to the local Db2 instance. Did you create a database named "BLUDB" on your Db2 server? Can you open a Db2 Command Window (Windows button, then "IBM DB2 DB2COPY1 (Default)", then "DB2 Command Window") and enter:
db2 list db directory
This should show all available databases. If BLUDB is listed, then open MS ODBC Administrator (run "odbcad32" from command window) and check whether BLUDB is registered as either a System or User DSN. If it is listed, run a connection test from the ODBC Administrator window and see whether that works.
If it is not listed, then run
db2 catalog system odbc data source BLUDB
if you are running the Db2 Command Window with local administrator privileges. If you did not open the Db2 Command Window with administrator privileges, replace "system" with "user". Then open MS ODBC Administrator again and check that BLUDB is listed here (it is supposed to be). Retry the connection test. Before you start working with your R application we need to make sure that the basic Db2 and ODBC setup is correct.

------------------------------
Christoph Theisen
Rocket Software
------------------------------

Hi Lorenz,
you have a "fresh" installation of Db2 Community Edition and no databases exist. I am not familiar with R but the document (ibmdbR.pdf) is from 2017 and there has been a lot of changes in the packaging of the Db2 editions since then. The document mentions IBM Db2 Warehouse on Cloud but that requires that you sign up for the cloud service, see here: https://www.ibm.com/products/db2/warehouse
I assume you can get a free trial access for a limited period of time.
If you want to continue with your local installation you can have a try and create a database upfront.
Go to the Db2 Command Window (mentioned earlier in this thread) and enter
db2 create database bludb pagesize 8 k
This will create an (almost) empty database. You can now continue and create tables, load data etc. but that's beyond the scope of the document (ibmdbR.pdf).
When this command completes successfully, enter
db2 catalog system odbc data source bludb
Now MS ODBC Administrator should list that data source as well.
As the next step go to your R application and retry to connect to bludb.

------------------------------
Christoph Theisen
Rocket Software
------------------------------

Original Message:
Sent: Tue November 08, 2022 10:22 AM
From: Lorenz Wagner
Subject: Problems installing and using DB2

Hello, thank you for the detailed reply. Sorry, I'm really a total beginner
1) How do I connect my pc to the local db2 unit?
2) When I run 'db2 list db directory', I get the following error:
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031

3) Consequently the command 'db2 catalog system odbc data source BLUDB' does not work either.
DB21036E The CATALOG SYSTEM ODBC DATA SOURCE command failed.
------------------------------
Lorenz Wagner
------------------------------


Original Message:
Sent: Tue November 08, 2022 03:19 AM
From: Christoph Theisen
Subject: Problems installing and using DB2

Hi Lorenz, you posted your question in the Db2 z/OS forum which is actually a different flavor of Db2 running on the mainframe platform. But anyway, perhaps we can figure this out here. Just for my understanding:
You installed the Db2 11.5.8 Community Edition on your Windows PC and assigned user "admin" to the Db2 services, right? Does "admin" have administrator permissions on your Windows PC?
Your ODBC application is supposed to run on your Windows PC and should connect to the local Db2 instance. Did you create a database named "BLUDB" on your Db2 server? Can you open a Db2 Command Window (Windows button, then "IBM DB2 DB2COPY1 (Default)", then "DB2 Command Window") and enter:
db2 list db directory
This should show all available databases. If BLUDB is listed, then open MS ODBC Administrator (run "odbcad32" from command window) and check whether BLUDB is registered as either a System or User DSN. If it is listed, run a connection test from the ODBC Administrator window and see whether that works.
If it is not listed, then run
db2 catalog system odbc data source BLUDB
if you are running the Db2 Command Window with local administrator privileges. If you did not open the Db2 Command Window with administrator privileges, replace "system" with "user". Then open MS ODBC Administrator again and check that BLUDB is listed here (it is supposed to be). Retry the connection test. Before you start working with your R application we need to make sure that the basic Db2 and ODBC setup is correct.

------------------------------
Christoph Theisen
Rocket Software

Thank you. I was able to run the commands successfully and a system DSN was created. Also the connection under --> Configure --> with admin/admin was successful.
Unfortunately in R still comes the same error:

Warning messages:
1: In RODBC::odbcDriverConnect("DSN=blud;UID=admin;PWD=admin", believeNRows = FALSE) :
[RODBC] ERROR: State 58031, code -1031, message [IBM][CLI Driver] SQL1031N Database directory not found in the specified file system. SQLSTATE=58031
2: In RODBC::odbcDriverConnect("DSN=blud;UID=admin;PWD=admin", believeNRows = FALSE) :
ODBC connection failed

What else could I try?
I did nothing within the database. I just executed both commands and then opened R.

------------------------------
Lorenz Wagner
------------------------------

Original Message:
Sent: Tue November 08, 2022 12:34 PM
From: Christoph Theisen
Subject: Problems installing and using DB2

Hi Lorenz,
you have a "fresh" installation of Db2 Community Edition and no databases exist. I am not familiar with R but the document (ibmdbR.pdf) is from 2017 and there has been a lot of changes in the packaging of the Db2 editions since then. The document mentions IBM Db2 Warehouse on Cloud but that requires that you sign up for the cloud service, see here: https://www.ibm.com/products/db2/warehouse
I assume you can get a free trial access for a limited period of time.
If you want to continue with your local installation you can have a try and create a database upfront.
Go to the Db2 Command Window (mentioned earlier in this thread) and enter
db2 create database bludb pagesize 8 k
This will create an (almost) empty database. You can now continue and create tables, load data etc. but that's beyond the scope of the document (ibmdbR.pdf).
When this command completes successfully, enter
db2 catalog system odbc data source bludb
Now MS ODBC Administrator should list that data source as well.
As the next step go to your R application and retry to connect to bludb.

------------------------------
Christoph Theisen
Rocket Software
------------------------------


Original Message:
Sent: Tue November 08, 2022 10:22 AM
From: Lorenz Wagner
Subject: Problems installing and using DB2

Hello, thank you for the detailed reply. Sorry, I'm really a total beginner
1) How do I connect my pc to the local db2 unit?
2) When I run 'db2 list db directory', I get the following error:
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031

3) Consequently the command 'db2 catalog system odbc data source BLUDB' does not work either.
DB21036E The CATALOG SYSTEM ODBC DATA SOURCE command failed.
------------------------------
Lorenz Wagner


Original Message:
Sent: Tue November 08, 2022 03:19 AM
From: Christoph Theisen
Subject: Problems installing and using DB2

Hi Lorenz, you posted your question in the Db2 z/OS forum which is actually a different flavor of Db2 running on the mainframe platform. But anyway, perhaps we can figure this out here. Just for my understanding:
You installed the Db2 11.5.8 Community Edition on your Windows PC and assigned user "admin" to the Db2 services, right? Does "admin" have administrator permissions on your Windows PC?
Your ODBC application is supposed to run on your Windows PC and should connect to the local Db2 instance. Did you create a database named "BLUDB" on your Db2 server? Can you open a Db2 Command Window (Windows button, then "IBM DB2 DB2COPY1 (Default)", then "DB2 Command Window") and enter:
db2 list db directory
This should show all available databases. If BLUDB is listed, then open MS ODBC Administrator (run "odbcad32" from command window) and check whether BLUDB is registered as either a System or User DSN. If it is listed, run a connection test from the ODBC Administrator window and see whether that works.
If it is not listed, then run
db2 catalog system odbc data source BLUDB
if you are running the Db2 Command Window with local administrator privileges. If you did not open the Db2 Command Window with administrator privileges, replace "system" with "user". Then open MS ODBC Administrator again and check that BLUDB is listed here (it is supposed to be). Retry the connection test. Before you start working with your R application we need to make sure that the basic Db2 and ODBC setup is correct.

------------------------------
Christoph Theisen
Rocket Software

Hi Lorenz, if you can connect to the database via the ODBC Administrator then the basic configuration looks good.
When installing the Db2 Server, you should have created two local groups on your Windows system, DB2ADMNS and DB2USERS. The user who "owns" the Db2 installation, "admin" in your case, should already be member of these two groups.
Could you also add the user who launches the R application to these two groups? I found this link which might apply in your case:
https://www.ibm.com/support/pages/odbc-connection-windows-db2-client-failed-sqlallochandle-sqlhandleenv-failed

------------------------------
Christoph Theisen
Rocket Software
------------------------------

Original Message:
Sent: Tue November 08, 2022 03:32 PM
From: Lorenz Wagner
Subject: Problems installing and using DB2

Thank you. I was able to run the commands successfully and a system DSN was created. Also the connection under --> Configure --> with admin/admin was successful.
Unfortunately in R still comes the same error:

Warning messages:
1: In RODBC::odbcDriverConnect("DSN=blud;UID=admin;PWD=admin", believeNRows = FALSE) :
[RODBC] ERROR: State 58031, code -1031, message [IBM][CLI Driver] SQL1031N Database directory not found in the specified file system. SQLSTATE=58031
2: In RODBC::odbcDriverConnect("DSN=blud;UID=admin;PWD=admin", believeNRows = FALSE) :
ODBC connection failed

What else could I try?
I did nothing within the database. I just executed both commands and then opened R.

------------------------------
Lorenz Wagner
------------------------------


Original Message:
Sent: Tue November 08, 2022 12:34 PM
From: Christoph Theisen
Subject: Problems installing and using DB2

Hi Lorenz,
you have a "fresh" installation of Db2 Community Edition and no databases exist. I am not familiar with R but the document (ibmdbR.pdf) is from 2017 and there has been a lot of changes in the packaging of the Db2 editions since then. The document mentions IBM Db2 Warehouse on Cloud but that requires that you sign up for the cloud service, see here: https://www.ibm.com/products/db2/warehouse
I assume you can get a free trial access for a limited period of time.
If you want to continue with your local installation you can have a try and create a database upfront.
Go to the Db2 Command Window (mentioned earlier in this thread) and enter
db2 create database bludb pagesize 8 k
This will create an (almost) empty database. You can now continue and create tables, load data etc. but that's beyond the scope of the document (ibmdbR.pdf).
When this command completes successfully, enter
db2 catalog system odbc data source bludb
Now MS ODBC Administrator should list that data source as well.
As the next step go to your R application and retry to connect to bludb.

------------------------------
Christoph Theisen
Rocket Software


Original Message:
Sent: Tue November 08, 2022 10:22 AM
From: Lorenz Wagner
Subject: Problems installing and using DB2

Hello, thank you for the detailed reply. Sorry, I'm really a total beginner
1) How do I connect my pc to the local db2 unit?
2) When I run 'db2 list db directory', I get the following error:
SQL1031N The database directory cannot be found on the indicated file system.
SQLSTATE=58031

3) Consequently the command 'db2 catalog system odbc data source BLUDB' does not work either.
DB21036E The CATALOG SYSTEM ODBC DATA SOURCE command failed.
------------------------------
Lorenz Wagner


Original Message:
Sent: Tue November 08, 2022 03:19 AM
From: Christoph Theisen
Subject: Problems installing and using DB2

Hi Lorenz, you posted your question in the Db2 z/OS forum which is actually a different flavor of Db2 running on the mainframe platform. But anyway, perhaps we can figure this out here. Just for my understanding:
You installed the Db2 11.5.8 Community Edition on your Windows PC and assigned user "admin" to the Db2 services, right? Does "admin" have administrator permissions on your Windows PC?
Your ODBC application is supposed to run on your Windows PC and should connect to the local Db2 instance. Did you create a database named "BLUDB" on your Db2 server? Can you open a Db2 Command Window (Windows button, then "IBM DB2 DB2COPY1 (Default)", then "DB2 Command Window") and enter:
db2 list db directory
This should show all available databases. If BLUDB is listed, then open MS ODBC Administrator (run "odbcad32" from command window) and check whether BLUDB is registered as either a System or User DSN. If it is listed, run a connection test from the ODBC Administrator window and see whether that works.
If it is not listed, then run
db2 catalog system odbc data source BLUDB
if you are running the Db2 Command Window with local administrator privileges. If you did not open the Db2 Command Window with administrator privileges, replace "system" with "user". Then open MS ODBC Administrator again and check that BLUDB is listed here (it is supposed to be). Retry the connection test. Before you start working with your R application we need to make sure that the basic Db2 and ODBC setup is correct.

------------------------------
Christoph Theisen
Rocket Software