ibm_db against HADR primary?

[Lennart Jonsson]

Not sure what happened to my previous post, if it reappears I'll remove one of them. When doing two consecutive connections against an HADR primary, the second one hangs for a couple of minutes before it bails out with an error: 

SQL1776N  The command cannot be issued on an HADR database. Reason code = "1". SQLCODE=-1776

Howto reproduce:

]$ python

Python 2.7.5 (default, May  3 2017, 07:55:04) 

[GCC 4.8.5 20150623 (Red Hat 4.8.5-14)] on linux2

Type "help", "copyright", "credits" or "license" for more information.

>>> import ibm_db

>>> import ibm_db_dbi

>>> 

>>> cfg = (db, user, pwd)

>>> ibm_db_conn = ibm_db.connect("DATABASE=%s;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=%s; PWD=%s" % cfg, "", "")

>>> conn = ibm_db_dbi.Connection(ibm_db_conn)

>>> conn.tables('SYSCAT', '%')

[{'TABLE_CAT': None, 'TABLE_TYPE': u'VIEW', ...

 

>>> ibm_db_conn2 = ibm_db.connect("DATABASE=%s;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=%s; PWD=%s" % cfg, "", "")

Hangs for a couple of minutes, before exit

I tried closing the first connection before initializing the second one, but that does not help. Running the same test on a stand-alone DB works.

I straced a script, and it appears as if it really tries to connect to the IP-number for the HADR-standby on the second attempt.

Anyone got a clue on what is going on?

Regards

/Lennart

 

[Saba Kauser]

 This problem is not in ibm_db scope. However, I can assist you with debugging this further.

Can you collect db2 traces both at client and server and share the traces.
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 trc.flw, trc.fmt and trc.fmtc files with us to assist you further.

on ibm_db driver, db2trc is located under '<clidriver install path>'/adm.
On server, it is under sqllib.

[Lennart Jonsson]

Thanks for the response. I think I will need to narrow the tracedump some how, I ran it for prox 10 seconds and it produced a 40Gb dmp with a resulting flw file of 21Gb. I'll look into the options db2trc and see if I can come up with something. 

[Lennart Jonsson]

Thanks for your response, do I need to wait for it to time out, or can I kill it after say 15 seconds?

Kind regards

/Lennart

On Friday, June 8, 2018 at 2:03:40 PM UTC+2, Saba Kauser wrote:

[Saba Kauser]

db2trc on client side is producing 40gb trace? just for connection? I doubt so.
You can wait for 5 secs and turn tracing off. There are options to trace PIDs. You can try that as well.
or trace with -m SQLJR .
db2trc on -m SQLJR,SQLJC -f trc.dmp on client

and

db2trc on -m SQLJR,SQLJS on server.

[Lennart Jonsson]

No, the db2trc was from the server, do you only need the trc for the client (well, the client is actually just a small pythonscript running locally on the server)?  

[Saba Kauser]

Yeah, client side trace would confirm if problem is happening before sending connection request to server or after sending the request.
If problem is after sending the request, you can open PMR to IBM support to assist with this.
If problem is in python driver layer, which is very unlikely, I can assist further.

[Saba Kauser]

ok.

SQL1776N  The command cannot be issued on an HADR database. Reason code = "1". SQLCODE=-1776

If I read through error description and other cases, it seem like connection is attempted to standby instead of primary. This is just one possibility.
Need traces to confirm where exactly is the problem. This does not seem like python driver issue.
can you collect cli traces as follows:
edit db2cli.ini file(under clidriver/cfg) and add following entries, add an empty last line: change tracepathname value to your path

[common]
trace=1
tracepathname=/home/skauser/sample_cases/trace
traceflush=1

and db2traces for client:

db2trc on -m CLI,SQLEU,SQLJC -f trc.dmp
run python script

db2trc off
db2trc flw trc.dmp trc.flw
db2trc fmt trc.dmp trc.fmt
db2trc fmt -c trc.dmp trc.fmtc

and share trc.flw, trc.fmt, trc.fmtc and the CLI trace files under 'tracepathname'. The CLI trace files are with naming convention p<pid>t<tid or identifier>.cli . eg: p26390t726578976.cli


If you have service support with IBM, I suggest that you open PMR.

[Lennart Jonsson]

Saba, thanks for your feedback. I'll open a PMR on monday and report back the result.

--
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.

[Lennart Jonsson]

Something seems wrong with the supportpage for creating a PMR, I keep ending up on that page that states "Yikes, to many cookies, try clean them up" (tested on three different computers, two of which I have never used before. Tested with two different browsers on win 10 and Fedora 28), so I have not been able to create one. 

However, changing localhost to an ip-number in the connection string resolves the problem (see https://dba.stackexchange.com/questions/209074/ibm-db-on-hadr-primary-database?noredirect=1#comment409821_209074). I suspect that: http://www-01.ibm.com/support/docview.wss?uid=swg21986199 may be related to what is happening here.

I also followed the instructions below and the dmp file was much smaller now (couple of hundred Mb), but the resulting files (trc.flw, trc.fmt, trc.fmtc) are way to big to post here.

Once again thanks for your support.

Regards

/Lennart

On Fri, 2018-06-08 at 20:23 -0700, Saba Kauser wrote:

--

[Saba Kauser]

Its wonderful to hear that the problem is solved.
Thank you!

To unsubscribe from this group and stop receiving emails from it, send an email to ibm_db+unsubscribe@googlegroups.com.