search

ibmdb / python-ibmdb

Automatically exported from code.google.com/p/ibm-db
Apache License 2.0
308 stars 192 forks source link

Can't establish connection #440

Closed alanbmcgill-ibm closed 3 years ago

alanbmcgill-ibm commented 5 years ago

I am a Windows user trying to connect to the Marketing Reporting Database and keep getting the following error:

conn_str='database=MKTRPT;hostname=b01cxnp19025.gho.pok.ibm.com;port=60015;protocol=tcpip;uid=****;pwd=****' mkt_conn = ibm_db.connect(conn_str, '', '')

Exception Traceback (most recent call last)

in () ----> 1 mkt_conn = ibm_db.connect(conn_str, '', '') SQLCODE=-1042on: [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004 Does anyone have any suggestions?
SabaKauser commented 5 years ago

Hello, Would be able to collect and share the traces.

cd clidriver install path cd adm db2trc on -f trc.dmp run your application db2trc off db2trc flw trc.dmp trc.flw db2trc fmt trc.dmp trc.fmt db2trc fmt -c trc.dmp trc.fmtc db2trc fmt -cli trc.dmp trc.cli and share the trc.flw, trc.fmt, trc.fmtc and trc.cli files

alanbmcgill-ibm commented 5 years ago

trc.zip

SabaKauser commented 5 years ago

Hi , From the traces, I see you are attempting connection to : 9.57.53.16 or b01cxnp19025.gho.pok.ibm.com at port 60015. Your server seem to be down or port number seem to be incorrect. Can you check that db2 instance at server is started at correct port i.e 60015.

12118 data DB2 UDB call level interface trace SQLGetDiagRec api (3.3.191.292.3.20) pid 2440 tid 2820 cpid -1 node 0 probe 20 bytes 427

Data1   (PD_TYPE_SINT,2) signed integer:
31
Data2   (PD_TYPE_NCONST,8) Num.Const:
0x0000000000000005
Data3   (PD_TYPE_STRINGN,5) String with size:
08001
Data4   (PD_TYPE_SINT,4) signed integer:
-30081
Data5   (PD_TYPE_NCONST,8) Num.Const:
0x000000000000014a
Data6   (PD_TYPE_STRINGN,330) String with size:
[IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "9.57.53.16".  Communication function detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".  SQLSTATE=08001

sql30081N says this:

alanbmcgill-ibm commented 5 years ago

I really appreciate your help. I am certain that b01cxnp19025.gho.pok.ibm.com at port 60015 is correct as that is what I use to connect via QMF Workstation. I have tried to establishing a python connection many times whilst the QMF connection was working fine so I don't think it is a matter of the server being down either.

SabaKauser commented 5 years ago

Have you been able to connect from python earlier to same db2 instance and that problem is seen only recently?

SabaKauser commented 5 years ago

Also is 60015 the Db2 DRDA port. Python driver uses clidriver which implements the DRDA protocol for connection and hence you need to have the server listening on drda port for connections from ODBC client.

alanbmcgill-ibm commented 5 years ago

I have never been able to establish this connection with Python. I don't know if 60015 is the DRDA port, it's the only port number I was given when I first applied for access to this database.

SabaKauser commented 5 years ago

Who gave you this access? Can you check with them to see if this is the correct one?

alanbmcgill-ibm commented 4 years ago

The guy who initially sent me the connection info has since changed roles but I have checked with his successor and confirmed that 60015 is correct. That guy uses Apache Spark to connect which I may try next although it looks a lot more complicated than python-ibmdb is supposed to be.

SabaKauser commented 4 years ago

Looking further into the traces, I see that you are using a Db2 full client. The traces has this error captured. Data6 (PD_TYPE_STRINGN,107) String with size: [IBM][CLI Driver] SQL1039C An I/O error occurred while accessing the database directory. SQLSTATE=58031

A little above, I can see that the user logged on does not seem to have necessary read permissions on : 6531 SYSTEM ERROR DB2 UDB oper system services sqloopenp cei (5.3.15.823.2.100) pid 2440 tid 2820 cpid -1 node 0 probe 100 Error ZRC = 0x840F0001 = -2079391743 = SQLO_ACCD Func.Called: CreateFile System Errno: 5

bytes 69

Data1   (PD_TYPE_FILE_NAME,53) File name:
C:\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR\SQLNODIR

6547 SYSTEM ERROR DB2 UDB oper system services sqloopenp cei (5.3.15.823.2.100) pid 2440 tid 2820 cpid -1 node 0 probe 100 Error ZRC = 0x840F0001 = -2079391743 = SQLO_ACCD Func.Called: CreateFile System Errno: 5

bytes 69

Data1   (PD_TYPE_FILE_NAME,53) File name:
C:\ProgramData\IBM\DB2\DB2COPY1\DB2\SQLNODIR\SQLNOBAK

Can you give the user necessary permissions and retry connection.

SabaKauser commented 4 years ago

Hi, Is your problem solved

jibardhan commented 4 years ago

i am trying to connect :- (Python 3.7) ibm_db.connect("DATABASE=SOMEDB;HOSTNAME=10.1.2.13;PORT=446;PROTOCOL=TCPIP;UID=username;PWD=passwordname", "", "")

It says as follows : SQLCODE=-1598M][CLI Driver] SQL1598N An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968

If i change the PORT to 8471 , it hangs up indefinitely. I am working from a Windows machine and trying to access a DB2 Connection from another machine.

I had setup IBM_DB_HOME variable in the env settings of windows and also the LIB Settings

**When i try to access the DB from DBeaver ,it connects to the DB

jibardhan commented 4 years ago

hi sabaKausar , can you help me ?

SabaKauser commented 4 years ago

If you are connecting to non-luw database, you need to have a client license. Please check https://github.com/ibmdb/python-ibmdb#-license-requirements-for-connecting-to-databases

kadler commented 4 years ago

@jibardhan If you're connecting to an IBM i database, you may want to look at using PyODBC with our IBM i Access ODBC Driver. It's going to be much easier to get up and running that way than to deal with the licensing for Db2 Connect.

jibardhan commented 4 years ago

Sorry for the late reply !! I am trying to connect to IBM DB2 server from a remote machine . This machine doesnot have any local DSN setup . Do i need to setup IBM DB2 ODBC on local ? . I had used Dbeaver IDE and data is coming good.

pyODBC would be a good option though .

Please let me know details on pyODBC connection on DB2 .

jibardhan commented 4 years ago

@jibardhan If you're connecting to an IBM i database, you may want to look at using PyODBC with our IBM i Access ODBC Driver. It's going to be much easier to get up and running that way than to deal with the licensing for Db2 Connect.

Please help me setup a connection , stepwise.

kadler commented 4 years ago

We have a guide here, though it doesn't have any Python info yet.

chungleu commented 3 years ago

Hi team. Very similar issue with my connection.

conn = ibm_db.connect('DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-lon02-13.services.eu-gb.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=me;PWD=password;', '', '')

gives Exception: [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004 SQLCODE=-1042

My DB2 instance is a service hosted in IBM Cloud and my DSN was retrieved from the credentials page. I could have sworn that it was working before ...

amukherjee28 commented 3 years ago

Hi @chungleu

Can you provide us a bit more information in order to understand the problem in details.

  1. Are you using using the default clidriver provided with the python driver or are you using any separate client driver for connection. In case you are using separate driver did you set the IBM_DB_HOME path?
  2. Can you provide us the traces. Please follow the following steps to collect the traces :

cd clidriver install path cd adm db2trc on -f trc.dmp run your application db2trc off db2trc flw trc.dmp trc.flw db2trc fmt trc.dmp trc.fmt db2trc fmt -c trc.dmp trc.fmtc db2trc fmt -cli trc.dmp trc.cli and share the trc.flw, trc.fmt, trc.fmtc and trc.cli files

can you please provide us the follow details and we can have a look.

Thanks.

chungleu commented 3 years ago

Thanks Arnab. I just installed the python package _ibmdb. I presume it uses the default drivers _ibmdb and _ibm_dbdbi. Does this come with a clidriver?

amukherjee28 commented 3 years ago

Hi,

In case your IBM_DB_HOME Path is not set the python driver downloads the default clidriver to be used for connection. In case the IBM_DB_HOME path is set, the download of the clidriver is skipped and connection is made using the driver as per set in IBM_DB_HOME path.

Thanks.

chungleu commented 3 years ago

Thanks Arnab. Is the process to collect a trace different on a mac? I've found a couple of clidriver folders. Each seems to have a bin folder (but no adm) which contains a db2trc executable.

amukherjee28 commented 3 years ago

If you are using the default clidriver that is downloaded with the python driver, goto the pip install path followed by clidriver. Then to the adm folder and run the following commands to collect trace.

db2trc on -l 2M

db2trc dump trc.dmp db2trc off db2trc flw trc.dmp trc.flw db2trc fmt trc.dmp trc.fmt db2trc fmt -c trc.dmp trc.fmtc db2trc fmt -cli trc.dmp trc.cli and share the trc.flw, trc.fmt, trc.fmtc and trc.cli files In case you have IBM_DB_HOME path set to any driver, then you can go the adm folder of the same and collect the trace using the commands provided above. In case the adm folder is missing then you need to download the proper clidriver. In that case try removing the IBM_DB_HOME path and then install python driver. This will download the clidriver into PIP install directory. Thanks.
bimalkjha commented 3 years ago

Thanks Arnab. Is the process to collect a trace different on a mac? I've found a couple of clidriver folders. Each seems to have a bin folder (but no adm) which contains a db2trc executable.

@chungleu you can find db2trc under bin directory if adm dir is not present. Both is same and it would be in either directory based on how clidriver is installed. The clidriver that is automatically downloaded by open source drivers contains db2trc under bin dir. Content of adm is also copied under bin only. Hope it clarifies. thanks.

amukherjee28 commented 3 years ago

Please follow this issue in case you still have the problem.

https://github.com/ibmdb/python-ibmdb/issues/599

Let me know if this can be closed?

jibardhan commented 3 years ago

Yes. Its resolved . I had made a connection via DSN.

On Wed, Jun 23, 2021 at 4:03 PM arnab mukherjee @.***> wrote:

Please follow this issue in case you still have the problem.

599 https://github.com/ibmdb/python-ibmdb/issues/599

Let me know if this can be closed?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ibmdb/python-ibmdb/issues/440#issuecomment-866724606, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHH6B6HCEIGXVJ6ABSZKV6DTUGZ73ANCNFSM4JKOYWSQ .