Unable to connect to remote DB2 instance on Mac OSX #25

utilityboy · 2015-04-10T01:31:25Z

Hello,

I'm having trouble requiring the ibm_db gem inside an irb session, or plain ruby file.

I'm running Mac OS 10.10.2, rbenv with ruby 2.0.0-p353. I've downloaded and extracted the odbc_cli distributable from IBM.

I was able to install the gem properly after setting the IBM_HOME_DB environment variable pointing to the extracted assembly.

From an irb session, when requiring the ibm_db gem, I get the following:

irb(main):001:0> require 'ibm_db'
LoadError: dlopen(/usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.18-x86_64-darwin-14/lib/linux/rb20x/ibm_db.bundle, 9): Library not loaded: /opt/IBM/db2/V10.1/lib64/libdb2.dylib
  Referenced from: /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.18-x86_64-darwin-14/lib/linux/rb20x/ibm_db.bundle
  Reason: image not found - /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.18-x86_64-darwin-14/lib/linux/rb20x/ibm_db.bundle
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:118:in `require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:118:in `rescue in require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:124:in `require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.18-x86_64-darwin-14/lib/ibm_db.rb:7:in `<top (required)>'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:118:in `require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:118:in `rescue in require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:124:in `require'
    from (irb):1
    from /usr/local/opt/rbenv/versions/2.0.0-p353/bin/irb:12:in `<main>'

Can you provide any assistance?

The text was updated successfully, but these errors were encountered:

arvindgu · 2015-04-10T03:28:02Z

Hi,
Please set the DYLD_LIBRARY_PATH environment variable to the path where you have extracted the odbc_cli distributable from IBM and try again in irb session.

utilityboy · 2015-04-10T03:56:15Z

Thanks @arvindgu. Slightly different error:

irb(main):001:0> require 'ibm_db'
LoadError: dlopen(/usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/linux/rb20x/ibm_db.bundle, 9): Library not loaded: /Users/mario/sqllib/lib64/libdb2.dylib
  Referenced from: /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/linux/rb20x/ibm_db.bundle
  Reason: image not found - /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/linux/rb20x/ibm_db.bundle
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:118:in `require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:118:in `rescue in require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:124:in `require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/ibm_db.rb:7:in `<top (required)>'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:118:in `require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:118:in `rescue in require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/2.0.0/rubygems/core_ext/kernel_require.rb:124:in `require'
    from (irb):1
    from /usr/local/opt/rbenv/versions/2.0.0-p353/bin/irb:12:in `<main>'

Interestingly this:

Library not loaded: /Users/mario/sqllib/lib64/libdb2.dylib

utilityboy · 2015-04-10T04:04:20Z

Aside: If I get this working, I'll gladly write up a wiki page and submit a PR.

arvindgu · 2015-04-10T04:07:31Z

Could you unset the IBM_DB_HOME and try.

utilityboy · 2015-04-10T04:12:02Z

Slightly different again:

irb(main):001:0> require 'ibm_db'
LoadError: dlopen(/usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/linux/rb20x/ibm_db.bundle, 9): Library not loaded: /Users/mario/sqllib/lib64/libdb2.dylib
  Referenced from: /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/linux/rb20x/ibm_db.bundle
  Reason: image not found - /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/linux/rb20x/ibm_db.bundle
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/ibm_db.rb:7:in `require'
    from /usr/local/opt/rbenv/versions/2.0.0-p353/lib/ruby/gems/2.0.0/gems/ibm_db-2.5.26-universal-darwin-14/lib/ibm_db.rb:7:in `<top (required)>'
    from (irb):1:in `require'
    from (irb):1
    from /usr/local/opt/rbenv/versions/2.0.0-p353/bin/irb:12:in `<main>'

For completeness, I'm running v2.5.26 of the gem installed via bundler.

arvindgu · 2015-04-10T04:19:18Z

Could you please confirm that you have set the DYLD_LIBRARY_PATH to the directory where libdb2.dylib is present under location of extracted the odbc_cli distributable.

utilityboy · 2015-04-10T04:26:44Z

The package I've downloaded is the "Driver for ODBC and CLI (64-bit)" located here:

https://www-304.ibm.com/support/docview.wss?rs=4020&uid=swg27016878

There is no libdb2.dylib within this package. Do I need another package in this case?

arvindgu · 2015-04-10T04:32:15Z

You can download it from below location, extracted it and set the DYLD_LIBRARY_PATH to the directory where libdb2.dylib is present.
http://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/macos64_odbc_cli.tar.gz

arvindgu · 2015-04-10T04:35:57Z

FYI, we have these steps to automate in our TODO list.

utilityboy · 2015-04-10T04:41:05Z

That worked @arvindgu. Thank you.

Is there a linux version as well that you recommend to work with this gem?

arvindgu · 2015-04-10T04:53:48Z

In linux, while installing ibm_db gem the odbc_cli libraries will get downloaded and configured automatically. You do not need to do manually. FYI, for linux 64-bit you can download odbc_cli libraries from below location:
http://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/linuxx64_odbc_cli.tar.gz

utilityboy · 2015-04-10T18:17:56Z

Thanks for the information @arvindgu.

Requiring the gem is working properly now, but am unable to connect to my remote DB2 database running on an iSeries.

I've tried specifying a DSN configuration as well as passing in a connection string directly:

require 'ibm_db'
conn = IBM_DB.connect('DATABASE=MYDB; HOSTNAME=192.168.1.100; PORT=446; PROTOCOL=TCPIP; UID=MYUID; PWD=MYPASS;','','')

results in:

ArgumentError: uncaught throw :"Connection failed: [IBM][CLI Driver] SQL1042C  An unexpected system error occurred.  SQLSTATE=58004 SQLCODE=-1042"
    from (irb):2:in `connect'
    from (irb):2
    from /usr/local/opt/rbenv/versions/2.0.0-p353/bin/irb:12:in `<main>'

results from db2diag:


SQLAllocHandle( fHandleType=SQL_HANDLE_ENV, hInput=0:0, phOutput=&00007fa8dcc67fa0 )
    ---> Time elapsed - 0 seconds

SQLAllocHandle( phOutput=0:1 )
    <--- SQL_SUCCESS   Time elapsed - +3.500000E-005 seconds

SQLSetEnvAttr( hEnv=0:1, fAttribute=SQL_ATTR_ODBC_VERSION, vParam=3, cbParam=0 )
    ---> Time elapsed - +9.560000E-004 seconds

SQLSetEnvAttr( )
    <--- SQL_SUCCESS   Time elapsed - +3.750000E-004 seconds

SQLAllocHandle( fHandleType=SQL_HANDLE_DBC, hInput=0:1, phOutput=&00007fa8dcc67fa4 )
    ---> Time elapsed - +2.300000E-005 seconds

SQLAllocHandle( phOutput=0:1 )
    <--- SQL_SUCCESS   Time elapsed - +1.100000E-005 seconds

SQLSetConnectAttrW( hDbc=0:1, fOption=SQL_ATTR_AUTOCOMMIT, pvParam=&0000000000000001, iStrLen=-3 )
    ---> Time elapsed - +1.100000E-005 seconds

SQLSetConnectAttrW( )
    <--- SQL_SUCCESS   Time elapsed - +1.100000E-005 seconds

SQLDriverConnectW( hDbc=0:1, hwnd=0:0, szConnStrIn="DATABASE=MYDB; HOSTNAME=192.168.1.100; PORT=446; PROTOCOL=TCPIP; UID=MYUID; PWD=*****;", cbConnStrIn=87, szConnStrOut=<NULL pointer>, cbConnStrOutMax=0, pcbConnStrOut=<NULL pointer>, fDriverCompletion=SQL_DRIVER_NOPROMPT )
    ---> Time elapsed - +1.100000E-005 seconds

SQLDriverConnectW( )
    <--- SQL_ERROR   Time elapsed - +1.570000E-004 seconds

SQLGetDiagRecW( fHandleType=SQL_HANDLE_DBC, hHandle=0:1, iRecNumber=1, pszSqlState=&00007fa8da9cae90, pfNativeError=&00007fff560cbd04, pszErrorMsg=&00007fa8db05f400, cbErrorMsgMax=1025, pcbErrorMsg=&00007fff560cbd02 )
    ---> Time elapsed - +7.000000E-006 seconds
( iRowNumber=-1, iColumnNumber=-2 )

SQLGetDiagRecW( pszSqlState="HY000", pfNativeError=-1042, pszErrorMsg="[IBM][CLI Driver] SQL1042C  An unexpected system error occurred.  SQLSTATE=58004
", pcbErrorMsg=81 )
    <--- SQL_SUCCESS   Time elapsed - +4.900000E-005 seconds

SQLFreeHandle( fHandleType=SQL_HANDLE_DBC, hHandle=0:1 )
    ---> Time elapsed - +7.000000E-006 seconds
( Number of allocations left before we freed the Pool=1 )

SQLFreeHandle( fHandleType=SQL_HANDLE_ENV, hHandle=0:1 )
    ---> Time elapsed - +7.000000E-006 seconds

SQLFreeHandle( )
    <--- SQL_SUCCESS   Time elapsed - +2.100000E-005 seconds

arvindgu · 2015-04-13T05:19:53Z

@bjubinville , the above logs are from CLI traces. Can you please provide the logs from db2diag.log file.
Also can you confirm whether you are able to connect to your iseries machine from db2 command prompt.

utilityboy · 2015-04-13T22:37:43Z

@arvindgu please note that I have only downloaded the odbc drivers discussed previously. I've extracted these to a directory and have set environment variables based on that path.

In response to your question regarding connecting directly via a command prompt:

The DB Version on the iSeries is the same as the OS/400 Version. In this case it's V7R1M0.
I've verified TCP connectivity to the iSeries (via port 446).
Running db2cli from that package:

./db2cli execsql -connstring "DATABASE=MYDB; HOSTNAME=192.168.1.100; PORT=446; PROTOCOL=TCPIP; UID=MYUID; PWD=MYPASS;"
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
SQLError: rc = 0 (SQL_SUCCESS)
SQLGetDiagRec: SQLState     : S1000
          fNativeError : -1042
          szErrorMsg   : [IBM][CLI Driver] SQL1042C  An unexpected system error occurred.  SQLSTATE=58004

          cbErrorMsg   : 81

Running db2cli validate

./db2cli validate -database MYDB:192.168.1.100:446 -connect -user MYUID -passwd MYPASS

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type       : IBM Data Server Driver For ODBC and CLI
Client Version (level/bit): DB2 v10.5.0.5 (special_33360/64-bit)
Client Platform           : Darwin
Install/Instance Path     : /Users/myuser/odbc/clidriver
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : /Users/myuser/odbc/clidriver/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : /Users/myuser/odbc/clidriver/cfg/db2cli.ini
db2diag.log Path          : /Users/myuser/odbc/clidriver/db2dump/db2diag.log

==============
Connection attempt for database "MYDB:192.168.1.100:446":
===============================================================================

[FAILED]: [IBM][CLI Driver] SQL1042C  An unexpected system error occurred.  SQLSTATE=58004


===============================================================================
Error: The validation operation failed.
===============================================================================

===============================================================================
The validation is completed.
===============================================================================

**N.B. db2diag.log is not generated when running any of these commands.

I do appreciate the assistance on this even though it's not particularly an issue with ibm_db.

arvindgu · 2015-04-14T10:40:16Z

@bjubinville I was able to recreate this problem.

To overcome this problem, please add authentication=server in connection string.
Your connection string should be
conn = IBM_DB.connect('DATABASE=MYDB; HOSTNAME=192.168.1.100; PORT=446; PROTOCOL=TCPIP; UID=MYUID; PWD=MYPASS; authentication=server; ','','')

utilityboy · 2015-04-15T12:44:54Z

@arvindgu, one step closer! Thanks for your continued help here. This may be an issue with the DB2 instance, but I thought I'd post it here regardless.

[FAILED]: [IBM][CLI Driver] SQL1598N  An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968

mariobriggs · 2015-04-15T17:41:57Z

to connect to a iSeries DB2, you need a connect license. Please contact your DBA who should be able to help you get the license file

utilityboy · 2015-04-15T17:49:30Z

Will do @mariobriggs. Is that license file to reside somewhere in the ODBC/CLI installation path?

mariobriggs · 2015-04-16T05:30:08Z

in the license folder

utilityboy · 2015-04-17T18:56:30Z

@mariobriggs I'm trying to find information about this license, but have had little luck from people at IBM on the matter. I've cross-posted to StackOverflow here:

http://stackoverflow.com/questions/29707290/connecting-to-db2-database-running-on-an-iseries-as400-via-linux-osx

Any insight?

rangercairns · 2015-04-17T19:43:29Z

I use DB2 Connect with license from my Linux machine all the time (Ruby ibm_db, PHP ibm_db2, etc.).
I wrote a wiki page for Yips (IBM i folks), who knows, maybe help you get connected.

http://yips.idevcloud.com/wiki/index.php/PHP/DB2IBMDB2
http://yips.idevcloud.com/wiki/index.php/Tier2/DB2Connect
-- BTW - DB2 Connect costs money for connect licence to IBM i (no free option) --

Perhaps, when you figure this out, post here and i will add 'Mac' to list of stuff tried on wiki.

utilityboy · 2015-04-18T17:07:52Z

@rangercairns thanks for this. I'll check it out and report back. Also very happy to contribute to a wiki article here in this repo for others once I get this sorted out.

utilityboy changed the title ~~Unable to require ibm_db inside ruby~~ Unable to connect to remote DB2 instance Apr 10, 2015

utilityboy changed the title ~~Unable to connect to remote DB2 instance~~ Unable to connect to remote DB2 instance on Mac OSX Apr 10, 2015

mariobriggs mentioned this issue Apr 29, 2015

Running on OS X Yosemite 10.10.3 returns error ibmdb/node-ibm_db#34

Closed

arvindgu closed this as completed Jul 16, 2015

bhuten mentioned this issue Sep 8, 2015

Version 2.5.25 fails to install on Mac OS X #23

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Unable to connect to remote DB2 instance on Mac OSX #25

Unable to connect to remote DB2 instance on Mac OSX #25

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 13, 2015

utilityboy commented Apr 13, 2015

arvindgu commented Apr 14, 2015

utilityboy commented Apr 15, 2015

mariobriggs commented Apr 15, 2015

utilityboy commented Apr 15, 2015

mariobriggs commented Apr 16, 2015

utilityboy commented Apr 17, 2015

rangercairns commented Apr 17, 2015

utilityboy commented Apr 18, 2015

Unable to connect to remote DB2 instance on Mac OSX #25

Unable to connect to remote DB2 instance on Mac OSX #25

Comments

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 10, 2015

utilityboy commented Apr 10, 2015

arvindgu commented Apr 13, 2015

utilityboy commented Apr 13, 2015

arvindgu commented Apr 14, 2015

utilityboy commented Apr 15, 2015

mariobriggs commented Apr 15, 2015

utilityboy commented Apr 15, 2015

mariobriggs commented Apr 16, 2015

utilityboy commented Apr 17, 2015

rangercairns commented Apr 17, 2015

utilityboy commented Apr 18, 2015