search

ibmdb / python-ibmdb

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

[IBM][CLI Driver] SQL1013N The database alias name or database name "" could not be found. SQLSTATE=42705 SQLCODE=-1013 #807

Closed vishakraj25 closed 1 year ago

vishakraj25 commented 2 years ago

Hello,

I am new to ibm cloud, I have a task to create a db2 database and access it using python.

In the ibm cloud, i created the db2 service, and using UI, I created a table and added a row.

Then, from the 'Service credentials', I created new credential - 

{
  "connection": {
    "cli": {
      "arguments": [
        [
          "-u",
          "mgz06002",
          "-p",
          "jGZM5CNPdkdcCdCA",
          "--ssl",
          "--sslCAFile",
          "1dd14d0c-1b52-4f63-a606-53ecba28771d",
          "--authenticationDatabase",
          "admin",
          "--host",
          "2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:32328"
        ]
      ],
      "bin": "db2",
      "certificate": {
        "certificate_base64": "LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0tCk1JSURIVENDQWdXZ0F3SUJBZ0lVT3dvMC9va09CUEN5RjFWeFJxVGhKRW9ubDBVd0RRWUpLb1pJaHZjTkFRRUwKQlFBd0hqRWNNQm9HQTFVRUF3d1RTVUpOSUVOc2IzVmtJRVJoZEdGaVlYTmxjekFlRncweU1EQTRNRFF3TWpVMwpNalphRncwek1EQTRNREl3TWpVM01qWmFNQjR4SERBYUJnTlZCQU1NRTBsQ1RTQkRiRzkxWkNCRVlYUmhZbUZ6ClpYTXdnZ0VpTUEwR0NTcUdTSWIzRFFFQkFRVUFBNElCRHdBd2dnRUtBb0lCQVFEb0ZFNGQ0SGdOeXZMUVIwR3gKQTB0amRXQnM4NVBjTDNyRStjN1R3K2diRUdQSUxJU0VZV3o4Y1g1TG1XQk0rY1FnOG9VeSsrQXJ3OEoxaXdRZQpySmlIU2I1clF4WTM0c3BQeGRFVEZkWEhScnJhMGU2VmM4MW42TllJL0ZHSnl1Q3hrTG5GMUtFQW9hbHYwaDM2CnhDT0FvcXRwTlFrTzNpMTRGeU0yRDRiajkxckI4RGk4Vy9XMVpVdVhMNGwzZXVLZUVCeTRuZmhJV3kySVc3aUMKbGpMZ3RlN3hZTDVHbVpKOUdsYWtrSnJ1cnpNREFQLzVUYnRlUUIydElodTBRSVRFZHlESVFYUEZGRDBHYzloZAo3M29JdnpVZUJ3VC9uRHN3OTJNNC82SkdtZWpKN0lpdFBTN3Y2a2dlUVhINDlBaUVJNXpQdUVpVzNOYi9GR0pYCmY2a2JBZ01CQUFHalV6QlJNQjBHQTFVZERnUVdCQlR2RzZ2RU5MRjFVbWZnQ003MmxOcmMzSDI2bURBZkJnTlYKSFNNRUdEQVdnQlR2RzZ2RU5MRjFVbWZnQ003MmxOcmMzSDI2bURBUEJnTlZIUk1CQWY4RUJUQURBUUgvTUEwRwpDU3FHU0liM0RRRUJDd1VBQTRJQkFRQTgvdFVnUTZlaTZYWHZndDJ0dUdrbkpva1Y5UWNkaTNZbFVFWkNDUytjClVQZ3NnMnVBMldxcHlWTm1mRkhjcHZ1Vmp0VHRYTmk2NUM2WlZsRnYxc3p1cU9zdFB5bkJ4blN4cUs0dkc0dTkKVjBWRUgxcE1tZnZBSmxkV3c4UEJTZGJtTk1HdGM4SzlwT0o5OVdBQ1ZFRXVXVGdDeHJKTXFBZnpYUXlidUV0dwp0cW1pV2swTmVXNGk5ZEY4S2dTWUVaQWFodXVBSlRldXB2R2RPV1U0eEV4bm03aEVRbmZPV2ZITThDd08xNWFZClRGQ2s0Q0pDUmR4Mlg5U284V3o1Z3MzcncyRkFDQlJyZ0NYeFFDZnZrZTZUdVNHNkxFRHJHbmpWaXVSQkpZdW4KT1RxWXROaVBHaHpuTHJrL0Fzam1LMzBxQmFLTmFyNUdQajhqalpNb2RiZ04KLS0tLS1FTkQgQ0VSVElGSUNBVEUtLS0tLQo=",
        "name": "1dd14d0c-1b52-4f63-a606-53ecba28771d"
      },
      "composed": [
        "db2 -u mgz06002 -p jGZM5CNPdkdcCdCA --ssl --sslCAFile 1dd14d0c-1b52-4f63-a606-53ecba28771d --authenticationDatabase admin --host 2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:32328"
      ],
      "environment": {},
      "type": "cli"
    },
    "db2": {
      "authentication": {
        "method": "direct",
        "password": "jGZM5CNPdkdcCdCA",
        "username": "mgz06002"
      },
      "certificate": {
        "certificate_base64": "LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0tCk1JSURIVENDQWdXZ0F3SUJBZ0lVT3dvMC9va09CUEN5RjFWeFJxVGhKRW9ubDBVd0RRWUpLb1pJaHZjTkFRRUwKQlFBd0hqRWNNQm9HQTFVRUF3d1RTVUpOSUVOc2IzVmtJRVJoZEdGaVlYTmxjekFlRncweU1EQTRNRFF3TWpVMwpNalphRncwek1EQTRNREl3TWpVM01qWmFNQjR4SERBYUJnTlZCQU1NRTBsQ1RTQkRiRzkxWkNCRVlYUmhZbUZ6ClpYTXdnZ0VpTUEwR0NTcUdTSWIzRFFFQkFRVUFBNElCRHdBd2dnRUtBb0lCQVFEb0ZFNGQ0SGdOeXZMUVIwR3gKQTB0amRXQnM4NVBjTDNyRStjN1R3K2diRUdQSUxJU0VZV3o4Y1g1TG1XQk0rY1FnOG9VeSsrQXJ3OEoxaXdRZQpySmlIU2I1clF4WTM0c3BQeGRFVEZkWEhScnJhMGU2VmM4MW42TllJL0ZHSnl1Q3hrTG5GMUtFQW9hbHYwaDM2CnhDT0FvcXRwTlFrTzNpMTRGeU0yRDRiajkxckI4RGk4Vy9XMVpVdVhMNGwzZXVLZUVCeTRuZmhJV3kySVc3aUMKbGpMZ3RlN3hZTDVHbVpKOUdsYWtrSnJ1cnpNREFQLzVUYnRlUUIydElodTBRSVRFZHlESVFYUEZGRDBHYzloZAo3M29JdnpVZUJ3VC9uRHN3OTJNNC82SkdtZWpKN0lpdFBTN3Y2a2dlUVhINDlBaUVJNXpQdUVpVzNOYi9GR0pYCmY2a2JBZ01CQUFHalV6QlJNQjBHQTFVZERnUVdCQlR2RzZ2RU5MRjFVbWZnQ003MmxOcmMzSDI2bURBZkJnTlYKSFNNRUdEQVdnQlR2RzZ2RU5MRjFVbWZnQ003MmxOcmMzSDI2bURBUEJnTlZIUk1CQWY4RUJUQURBUUgvTUEwRwpDU3FHU0liM0RRRUJDd1VBQTRJQkFRQTgvdFVnUTZlaTZYWHZndDJ0dUdrbkpva1Y5UWNkaTNZbFVFWkNDUytjClVQZ3NnMnVBMldxcHlWTm1mRkhjcHZ1Vmp0VHRYTmk2NUM2WlZsRnYxc3p1cU9zdFB5bkJ4blN4cUs0dkc0dTkKVjBWRUgxcE1tZnZBSmxkV3c4UEJTZGJtTk1HdGM4SzlwT0o5OVdBQ1ZFRXVXVGdDeHJKTXFBZnpYUXlidUV0dwp0cW1pV2swTmVXNGk5ZEY4S2dTWUVaQWFodXVBSlRldXB2R2RPV1U0eEV4bm03aEVRbmZPV2ZITThDd08xNWFZClRGQ2s0Q0pDUmR4Mlg5U284V3o1Z3MzcncyRkFDQlJyZ0NYeFFDZnZrZTZUdVNHNkxFRHJHbmpWaXVSQkpZdW4KT1RxWXROaVBHaHpuTHJrL0Fzam1LMzBxQmFLTmFyNUdQajhqalpNb2RiZ04KLS0tLS1FTkQgQ0VSVElGSUNBVEUtLS0tLQo=",
        "name": "1dd14d0c-1b52-4f63-a606-53ecba28771d"
      },
      "composed": [
        "db2://mgz06002:jGZM5CNPdkdcCdCA@2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:32328/bludb?authSource=admin&replicaSet=replset"
      ],
      "database": "bludb",
      "host_ros": [
        "2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30874"
      ],
      "hosts": [
        {
          "hostname": "2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud",
          "port": 32328
        }
      ],
      "jdbc_url": [
        "jdbc:db2://2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:32328/bludb:user=<userid>;password=<your_password>;sslConnection=true;"
      ],
      "path": "/bludb",
      "query_options": {
        "authSource": "admin",
        "replicaSet": "replset"
      },
      "replica_set": "replset",
      "scheme": "db2",
      "type": "uri"
    }
  },
  "instance_administration_api": {
    "deployment_id": "crn:v1:bluemix:public:dashdb-for-transactions:eu-gb:a/8f5fdb8125b5410190351d7ce5015ff9:369faf5c-553c-4a30-b634-b792c8c86c0e::",
    "instance_id": "crn:v1:bluemix:public:dashdb-for-transactions:eu-gb:a/8f5fdb8125b5410190351d7ce5015ff9:369faf5c-553c-4a30-b634-b792c8c86c0e::",
    "root": "https://apieugb.db2.cloud.ibm.com/v5/ibm"
  }
}

I deleted this credential,

Now, in my laptop, I installed the ibm_db using pip install ibm_db Then, in the python file,

import ibm_db

host_name = '2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud'
port = '32328'
uid = 'mgz06002'
passcode = 'jGZM5CNPdkdcCdCA'
DRIVER ='{IBM DB2 ODBC DRIVER}'
database = 'bludb'
protocol = 'direct'

conn_str='DRIVER='+DRIVER+';'+\
        'DATABASE='+database+';'+\
        'HOSTNAME='+host_name+';'+\
        'PORT='+port+';'+\
        'PROTOCOL='+protocol+';'+\
        'UID='+uid+';'+\
        'PWD='+passcode

print(conn_str)

try:
    ibm_db_conn = ibm_db.connect(conn_str,'','')
except:
    print("Error in connection, sqlstate = ")
    errorState = ibm_db.conn_error()
    print(errorState)
    error_msg = ibm_db.conn_errormsg()
    print(error_msg)

By running this code, I got this error in connecting db2

Error in connection, sqlstate = 08001 [IBM][CLI Driver] SQL1013N The database alias name or database name "" could not be found. SQLSTATE=42705 SQLCODE=-1013

when i use the protocol as tcpip, it takes much more time to execute and shows this error

Exception: [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: "158.176.91.122". Communication function detecting the error: "recv". Protocol specific error code(s): "104", "*", "0". SQLSTATE=08001 SQLCODE=-30081

How to solve this, How to connect the db2 database using python, thanks

imavo commented 2 years ago

The protocol has to be TCPIP when connecting from python to remote Db2 databases ( you cannot use protocol=direct).

Specify security=SSL in your connection string.

Are you using a public (lite plan) or private instance on ibm-cloud? You may also need to specify a certificate file containing the public key for ssl/tls (SSLServerCertificate=...).

Additionally, on your laptop, the software-firewall has to be configured to allow inbound and outbound traffic for TCP on the specified port number your Db2-server uses (as specified in PORT=... in the connection string).

vishakraj25 commented 2 years ago

Hi, thanks for writing

i added the SSL parameters -

    SECURITY = 'SSL'
    SSLServerCertificate = "LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0tCk1JSURIVENDQWdXZ0F3SUJBZ0lVT3dvMC9va09CUEN5RjFWeFJxVGhKRW9ubDBVd0RRWUpLb1pJaHZjTkFRRUwKQlFBd0hqRWNNQm9HQTFVRUF3d1RTVUpOSUVOc2IzVmtJRVJoZEdGaVlYTmxjekFlRncweU1EQTRNRFF3TWpVMwpNalphRncwek1EQTRNREl3TWpVM01qWmFNQjR4SERBYUJnTlZCQU1NRTBsQ1RTQkRiRzkxWkNCRVlYUmhZbUZ6ClpYTXdnZ0VpTUEwR0NTcUdTSWIzRFFFQkFRVUFBNElCRHdBd2dnRUtBb0lCQVFEb0ZFNGQ0SGdOeXZMUVIwR3gKQTB0amRXQnM4NVBjTDNyRStjN1R3K2diRUdQSUxJU0VZV3o4Y1g1TG1XQk0rY1FnOG9VeSsrQXJ3OEoxaXdRZQpySmlIU2I1clF4WTM0c3BQeGRFVEZkWEhScnJhMGU2VmM4MW42TllJL0ZHSnl1Q3hrTG5GMUtFQW9hbHYwaDM2CnhDT0FvcXRwTlFrTzNpMTRGeU0yRDRiajkxckI4RGk4Vy9XMVpVdVhMNGwzZXVLZUVCeTRuZmhJV3kySVc3aUMKbGpMZ3RlN3hZTDVHbVpKOUdsYWtrSnJ1cnpNREFQLzVUYnRlUUIydElodTBRSVRFZHlESVFYUEZGRDBHYzloZAo3M29JdnpVZUJ3VC9uRHN3OTJNNC82SkdtZWpKN0lpdFBTN3Y2a2dlUVhINDlBaUVJNXpQdUVpVzNOYi9GR0pYCmY2a2JBZ01CQUFHalV6QlJNQjBHQTFVZERnUVdCQlR2RzZ2RU5MRjFVbWZnQ003MmxOcmMzSDI2bURBZkJnTlYKSFNNRUdEQVdnQlR2RzZ2RU5MRjFVbWZnQ003MmxOcmMzSDI2bURBUEJnTlZIUk1CQWY4RUJUQURBUUgvTUEwRwpDU3FHU0liM0RRRUJDd1VBQTRJQkFRQTgvdFVnUTZlaTZYWHZndDJ0dUdrbkpva1Y5UWNkaTNZbFVFWkNDUytjClVQZ3NnMnVBMldxcHlWTm1mRkhjcHZ1Vmp0VHRYTmk2NUM2WlZsRnYxc3p1cU9zdFB5bkJ4blN4cUs0dkc0dTkKVjBWRUgxcE1tZnZBSmxkV3c4UEJTZGJtTk1HdGM4SzlwT0o5OVdBQ1ZFRXVXVGdDeHJKTXFBZnpYUXlidUV0dwp0cW1pV2swTmVXNGk5ZEY4S2dTWUVaQWFodXVBSlRldXB2R2RPV1U0eEV4bm03aEVRbmZPV2ZITThDd08xNWFZClRGQ2s0Q0pDUmR4Mlg5U284V3o1Z3MzcncyRkFDQlJyZ0NYeFFDZnZrZTZUdVNHNkxFRHJHbmpWaXVSQkpZdW4KT1RxWXROaVBHaHpuTHJrL0Fzam1LMzBxQmFLTmFyNUdQajhqalpNb2RiZ04KLS0tLS1FTkQgQ0VSVElGSUNBVEUtLS0tLQo"

    conn_str='DRIVER='+DRIVER+';'+\
            'DATABASE='+database+';'+\
            'HOSTNAME='+host_name+';'+\
            'PORT='+port+';'+\
            'PROTOCOL='+protocol+';'+\
            'UID='+uid+';'+\
            'PWD='+passcode+';'+\
            'SECURITY='+SECURITY+';'+\
            'SSLServerCertificate='+SSLServerCertificate

Then, I got this error -

`sqlstate = HY507

[IBM][CLI Driver] CLI0157E Error opening a file. SQLSTATE=HY507 SQLCODE=-99999`

I am using the Lite[free] version on ibm db2, and i am using in ubuntu os

I checked the ports in my system, using this command -sudo lsof -i -P -n | grep LISTEN and got this -

    systemd-r   729 systemd-resolve   14u  IPv4  25215      0t0  TCP 127.0.0.53:53 (LISTEN)
    anydesk     925            root   32u  IPv4  27392      0t0  TCP *:7071 (LISTEN)
    apache2    1022            root    3u  IPv6  25577      0t0  TCP *:80 (LISTEN)
    teamviewe  1091            root   12u  IPv4  28769      0t0  TCP 127.0.0.1:5939 (LISTEN)
    apache2   11348        www-data    3u  IPv6  25577      0t0  TCP *:80 (LISTEN)
    apache2   11349        www-data    3u  IPv6  25577      0t0  TCP *:80 (LISTEN)
    apache2   11350        www-data    3u  IPv6  25577      0t0  TCP *:80 (LISTEN)
    apache2   11351        www-data    3u  IPv6  25577      0t0  TCP *:80 (LISTEN)
    apache2   11352        www-data    3u  IPv6  25577      0t0  TCP *:80 (LISTEN)
    cupsd     11363            root    6u  IPv6 115049      0t0  TCP [::1]:631 (LISTEN)
    cupsd     11363            root    7u  IPv4 115050      0t0  TCP 127.0.0.1:631 (LISTEN)

Could you please help me to understand the db2, thanks

imavo commented 2 years ago

The error message should give you a clue! SSLServerCertificate is looking for a file in your filesystem, but you specified a hex key value instead of a filename.

Sometimes the driver is able to automatically download the certificate file from the Db2-server instance, and in this case you only need Security=SSL in the connection string (don't need SSLServerCertificate=...). So try that simple case first.

If you need a certificate file then it must be in a file on your laptop accessible to python, and you must specify the fully qualified pathname to the certificate file in the value you give for SSLServerCertificate in the connection string (it is not enough to only give the filename, the whole path must be included, for example SSLServerCertificate=/home/user1/Downloads/DigiCertGlobalRootCA.crt;

If you are running ubuuntu on your laptop with the firewall enabled, you must allow tcp traffic to flow on the specified port. For example, if you are using ufw then you could do this with commands similar to ufw allow in 32328/tcp and ufw allow out 32328/tcp (if that is the correct port number) . Refer to the documentation of ufw (or the documentation of whatever firewall you are running).

No process on your laptop will be listening for connections on that port-number (unless you put a Db2-server locally on your laptop of course) , instead it is the remote Db2-server instance that will be listening on that port number. So your command lsof ... | grep LISTEN has no meaning for a db2 client (it only has meaning for a Db2-server). Between your laptop and the IBM cloud there can be no firewalls that block that port (otherwise the connection will fail).

bimalkjha commented 2 years ago

@vishakraj25 Do not use SSLServerCertificate in your connection string for bludb. Just remove the last line from your conn_str and connection should work fine. Thanks.

vishakraj25 commented 2 years ago

@imavo , @bimalkjha , I removed the SSLServerCertificate from the connection 

conn_str='DRIVER='+DRIVER+';'+\
        'DATABASE='+database+';'+\
        'HOSTNAME='+host_name+';'+\
        'PORT='+port+';'+\
        'PROTOCOL='+protocol+';'+\
        'UID='+uid+';'+\
        'PWD='+passcode+';'+\
        'SECURITY='+SECURITY

It works, thanks you for helping me in this

I am requesting one help, could you please share good content for learning the ibm cloud, i needed to get expert in this area, thanks

bimalkjha commented 1 year ago

@vishakraj25 The good content for learning ibm cloud is : https://cloud.ibm.com/docs . Otherwise, google about it. Thanks.