Using only ibm_db, still can't connect

[Alex Hall]

Hey all,
I've cut back to a very, very basic script, using only ibm_db and
nothing else. I'm running into exactly what I did when trying to use
sqlalchemy: one of two errors, or an endless waiting period as I wait
in vain for an answer from the server or a timeout. My script:

import ibm_db
dbConnection = ibm_db.pconnect("DATABASE=myLibraryName;HOSTNAME=1.2.3.4;PORT="+port+";PROTOCOL=TCPIP;UID=username;PWD=password",
"", "")
print ibm_db.conn_errormsg()

I got the connection string from
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.swg.im.dbclient.python.doc/doc/t0054368.html

I made the port number a variable because it is the thing that keeps
giving me different results. The docs say to use 8471 or 9471 for
database access (the latter for SSL), but those are when I get no
response whatsoever. I've tried 50000, 60000, and 446, all of which
return errors immediately. The high numbers give me SQLCode-30081, and
446 gives me -30020. I even tried a DSN, but I got an error claiming
it couldn't locate the specified DSN even though said DSN is right in
the list when I open up ODBC Manager.

The thing is, we have at least five computers that talk to this 400
for hours every day, so I know it can accept incoming connections. The
computer on which I'm running this stuff can even do it, using the
same software the other stations use, so I know my machine has the
right drivers. Is there anything else I could try? I don't know much
about the 400 itself, and it definitely works with all our current
stations with no problems at all. That said, is there something on it
that I should check? Anything anyone can think of will help. Thanks.

[Saba Kauser]

Hello Alex,

30081 is a communication protocol error. Your server might be listening on a different port and not on the
one you are trying to communicate from client.

more information on different ports and resolutions can be found here:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.messages.sql.doc/doc/msql30081n.html?lang=en
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.messages.doc/doc/r0058740.html?lang=en
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.qb.dbconn.doc/doc/c0005607.html?lang=en

Could you please make sure your client is attempting to connect through the port server is enabled for listening.
For Iseries, DRDA port 446 should be used for db2 connections from client.

[Alex Hall]

Well, this is fun. Using port 446, my error is now this:

Traceback (most recent call last):
File "DBInterface3.py", line 7, in <module>
dbConnection =
ibm_db.pconnect("DATABASE=someLibraryName;HOSTNAME=serverIPAddress;P
ORT="+port+";PROTOCOL=TCPIP;UID="+username+";PWD="+password, "", "")
Exception: [IBM][CLI Driver] SQL30020N Execution of the command or SQL statemen
t failed because of a syntax error in the communication data stream that will af
fect the successful execution of subsequent commands and SQL statements: Reason
SQLCODE=-30020"0206")"". SQLSTATE=58009

I've searched for this particular problem, but I can't find any
mention of the 0206 subcode. 30020 and 58009 are pretty common, but
not 0206. It's not hanging anymore, so I guess this is progress.
Still, it's been days since I started trying to connect and I don't
know why things are proving so difficult. As always, I'll greatly
appreciate any suggestions anyone might have. I should say that I'm
seeing this in the above script, as well as the tests.py script in the
ibm_db folder.

On 2/18/16, Alex Hall <ah...@autodist.com> wrote:
> Thanks, I'll have a look at the link once I get to work and can double check
> the exact error message. It also helps to know to definitely use 446. The
> docs say that 8471 is for "as-database", so I assumed I should use that as
> I'm trying to do only database operations. Why the connection hangs
> indefinitely on 8471 I'm not sure, but at least now I can ignore that and
> concentrate on getting 446 working. Speaking of ports, I asked yesterday,
> and was told that our 400 is definitely set up with the usual ports in
> place. That is, nothing has been changed, so the numbers generally
> recommended will work here.

>> --
>> You received this message because you are subscribed to the Google Groups
>> "ibm_db" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to ibm_db+un...@googlegroups.com
>> <mailto:ibm_db+un...@googlegroups.com>.
>> To post to this group, send email to ibm...@googlegroups.com
>> <mailto:ibm...@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/ibm_db
>> <https://groups.google.com/group/ibm_db>.
>> For more options, visit https://groups.google.com/d/optout
>> <https://groups.google.com/d/optout>.
>
>

[Alex Hall]

Thanks, I'll have a look at the link once I get to work and can double check the exact error message. It also helps to know to definitely use 446. The docs say that 8471 is for "as-database", so I assumed I should use that as I'm trying to do only database operations. Why the connection hangs indefinitely on 8471 I'm not sure, but at least now I can ignore that and concentrate on getting 446 working. Speaking of ports, I asked yesterday, and was told that our 400 is definitely set up with the usual ports in place. That is, nothing has been changed, so the numbers generally recommended will work here.

On Feb 17, 2016, at 23:38, Saba Kauser <saka...@in.ibm.com> wrote:

--
You received this message because you are subscribed to the Google Groups "ibm_db" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ibm_db+un...@googlegroups.com.
To post to this group, send email to ibm...@googlegroups.com.
Visit this group at https://groups.google.com/group/ibm_db.
For more options, visit https://groups.google.com/d/optout.

[Saba Kauser]

-30020 is DRDA syntax error.
Would it be possible for you to collect db2 traces and share with us.

steps:
----------------------------

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

and share the .flw, .fmt and .fmtc files.

[Saba Kauser]

by running application, I meant just the failing connect command .

[Alex Hall]

Hi,
I asked about this yesterday. I was told there wasn't even a log of my
attempts on the server, so I wasn't reaching it for some reason. Yes,
my IP was right--I wish it were that easy. :)

Now, here's where it gets extremely weird. Using straight pyodbc, I
can connect to the server and run queries all I want. The moment I use
ibm_db_sa, I get the dreaded "pyodbc.Connection object has no
attribute dbms_ver". If I remove "+pyodbc" from my connection string,
it doesn't work, but at least I don't get that error.

I know you guys have said before that ibm_db isn't well supported with
pyodbc, but I'm told on the sqlalchemy list that this is the only way
I can use the proper dialect with SA. More importantly, pyodbc is, so
far, the only package that successfully connects me to the server at
all. I believe this is because I can specify the driver to pyodbc,
whereas I can't do that in SA.

Right now, my connection in SA looks like this:

def iSeriesConnector():
conn = pyodbc.connect(driver=driver, system=server, uid=username, pwd=password)
return conn
#end def iSeriesConnector

dbEngine = create_engine("db2+pyodbc://", creator=iSeriesConnector)
#works, or at least doesn't throw
print "connecting" #prints
dbEngine.connect() #throws the AttributeError exception
print "connected" #never reached

I'm at a total loss. I guess I could use pyodbc exclusively in my app,
but I'd rather not. I'm also confused about why ibm_db itself won't
work, where pyodbc will. What am I doing wrong? The credentials are
the same, as is the IP, for both. The only differences are that pyodbc
lets me give it a driver name, and it doesn't ask for a port number.

As before, I have the latest everything to the best of my knowledge.
Python 2.7 x 64 on Windows 7.

[Jose Paul]

I suggest ,try with 32 bit also .I have seem some weird problem in 64 bit.