Python ibm_db: "select 'Hello, World!' from sysibm.sysdummy1" returns [IBM][CLI Driver] SQL1224N error

[Andrey Sukhanov]

Hello,

Need help on connecting to DB2 from Python using ibm_db module.

I have CentOS 5.4 and Python 2.6. I installed DB2 Express-C 9.7.1,
then checked out ibm_db module sources (is there an egg for Python
2.6?), built and installed it. I also added /opt/ibm/db2/V9.7/lib32 in
LD_LIBRARY_PATH.

In general, everything is fine. I made a new database, and then I can
establish a connection, create a table, insert and select data using
Python program.

But any SELECT from sysibm.sysdummy1 table doesn't work.

I tried to execute "select 'Hello, World!' from sysibm.sysdummy1". It
works from native DB2 client. But Python script fails with the
following error:
---
Traceback (most recent call last):
File "sysdummy.py", line 7, in <module>
cursor.execute("select 'Hello, World!' from sysibm.sysdummy1")
File "/usr/local/py26/lib/python2.6/site-packages/ibm_db-1.0-py2.6-
linux-i686.egg/ibm_db_dbi.py", line 1141, in execute
self._execute_helper(None, parameters)
File "/usr/local/py26/lib/python2.6/site-packages/ibm_db-1.0-py2.6-
linux-i686.egg/ibm_db_dbi.py", line 1071, in _execute_helper
raise _get_exception(inst)
ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: Statement
Execute Failed: [IBM][CLI Driver] SQL1224N The database manager is
not able to accept new requests, has terminated all requests in
progress, or has terminated the specified request because of an error
or a forced interrupt. SQLSTATE=55032 SQLCODE=-1224
---

Here is my Python example:
---
import ibm_db
ibm_db_conn = ibm_db.connect('test', 'db2inst1', 'prometeus')
import ibm_db_dbi
conn = ibm_db_dbi.Connection(ibm_db_conn)

cursor = conn.cursor()
cursor.execute("select 'Hello, World!' from sysibm.sysdummy1")

row = cursor.fetchone()

print row

conn.close()
---

I hope there is an obvious mistake and somebody will point me to it :)

Thanks,
Andrey

[Rahul]

Hi Andrey,

You are not doing anything wrong in your code.
You got this error due to any of the following causes :
1) The database manager was stopped.
2) The database agent was forced off by the system administrator.
3) The database agent was terminated due to an abnormal
termination of a key database manager process.
For more detail information about this error, refer following link
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.messages.sql.doc/doc/msql01224n.html

Thanks,
Rahul Priyadarshi

[Andrey Sukhanov]

Hi Rahul,

Thank you, I've read the error description. It recommends to tune the
database server or improve the client application if it might take too
much resources...

The problem is that the same query works fine from the command line
processor:
---
db2 => connect to test

Database Connection Information

Database server = DB2/LINUX 9.7.1
SQL authorization ID = DB2INST1
Local database alias = TEST

db2 => select 'Hello, World!' from sysibm.sysdummy1

1
-------------
Hello, World!

1 record(s) selected.
---

So, most of information in the documentation is useless because I use
the same instance and same database for executing my query both from
Python and from command line processor.

The instance works fine. All queries work fine from the command line
processor. So, there is no need to fix the database server itself as
the article you mention recommends to do.

I believe that something is wrong with the ibm_db module in Python. I
probably need to adjust something in the Python connectivity module.

Thanks,
Andrey

On Mar 12, 5:02 pm, Rahul <rahul.priyadar...@in.ibm.com> wrote:
> Hi Andrey,
>
> You are not doing anything wrong in your code.
> You got this error due to any of the following causes :
>      1) The database manager was stopped.
>      2) The database agent was forced off by the system administrator.
>      3) The database agent was terminated due to an abnormal
> termination of a key database manager process.

> For more detail information about this error, refer following linkhttp://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=...

[Rahul]

Did you try same test program more then one times? Because it may
possible, that particular instance of time connection from databases
have been interrupted .

Thanks,
Rahul Priyadarshi

[Andrey Sukhanov]

Yes, sure, I ran the example many times, and always have same result.

Thanks,
Andrey

[Rahul]

Are you getting same error for select query with other tables. Or,
this error only comes with sysibm.sysdummy1 table.

Thanks,
Rahul Priyadarshi

[Andrey Sukhanov]

Any query runs well if sysibm.sysdummy1 table is not used in it.

For instance, I have the following example (I named it db2.py) and it
runs successfully.

Source:
---
import ibm_db
ibm_db_conn = ibm_db.connect('test', 'db2inst1', '***')

import ibm_db_dbi
conn = ibm_db_dbi.Connection(ibm_db_conn)

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

cursor = conn.cursor()
cursor.execute("DROP TABLE test")
cursor.execute("CREATE TABLE test (f varchar(50))")
cursor.execute("INSERT INTO test VALUES ('Hello, World!')")
cursor.execute("SELECT * FROM test")

row = cursor.fetchone()

print row

conn.close()
---

Result:
---
[db2inst1@regress ~]$ python db2.py
/usr/local/py26/lib/python2.6/site-packages/ibm_db-1.0-py2.6-linux-
i686.egg/ibm_db_dbi.py:25: DeprecationWarning: the sets module is
deprecated
from sets import ImmutableSet
(u'Hello, World!',)
---

Thanks,
Andrey

[Rahul]

Hi Andrey,
I am now able to reproduce this problem on Express-C DB2. I am
looking into the reason, why this error only comes on Express-C
edition of DB2.

Thanks,
Rahul Priyadarshi

[Rahul]

Hi Andrey,

This error comes not due to ibm_db module. Actually this is an issue
of CLI in Express-C edition.

This one is reproducible through following CLI script

----
opt echo on

quickc 1 1 test
sqlallocstmt 1 1

sqlsetstmtoption 1 sql_attr_cursor_type 3
sqlexecdirect 1 "select 'Hello, World!' from sysibm.sysdummy1" -3

sqlgetdiagrec SQL_HANDLE_STMT 1 1

fetchall 1
sqltransact 1 1 SQL_ROLLBACK
killenv 1
------

I am raising a defect for this issue on DB2 CLI. I will update you
about this when it get solved.

Is this issue blocking your application or you are using this as a
testing purpose. If this one is blocking your application then i will
give you some workaround.

Thanks,
Rahul Priyadarshi

[Andrey Sukhanov]

Hi Rahul,

Thank you for your help!

I'm doing some investigations for our application and preparing a test
bed. DB2 is going to be next DBMS the product will support.

Right now we are not blocked. But we will start working on DB2 support
next month or in two months. Do you expect that the defect will be
fixed soon? If not, a workaround will be very appreciated.

Thanks,
Andrey

[Rahul]

Hi Andrey,
have you purchased 'support' for Express-C, If yes then you can call/
mail into IBM support.

Thanks,
Rahul Priyadarshi

[Rahul]

Hi Andrey,

I have created a patch to increase performance of dbi wrapper. This
patch will be able to solve your problem also.

Because your reported bug arises only when we execute “select 'Hello,
World!' from sysibm.sysdummy1” type of query after setting cursor
attribute to static.
In this patch we are not setting cursor attribute to static by
default. So, now your query will work fine.

The modified patch of dbi is attached on
http://groups.google.com/group/ibm_db/browse_thread/thread/2f6bdffa68524ce1
discussion thread. You can download it from there and replace the
existing ibm_db_dbi.py with this one(at location “../site-packages/
ibm_db.py2.x-xxx.egg/”).

Give it a try and let me know hows it works.

Thanks,
Rahul Priyadarshi

[Rahul]

Hi Andrey,
I contacted with the db2 server team. They already fixed it in DB2 v97
FP2.
So, with official release of DB2 v97 fp2 you will get it fixed.

If you have any problem with this then let me know.

Thanks,
Rahul Priyadarshi

On Apr 8, 1:22 pm, Rahul <rahul.priyadar...@in.ibm.com> wrote:
> Hi Andrey,
>

> I have created a patch to increase performance of dbi wrapper.  This
> patch will be able to solve your problem also.
>
> Because your reported bug arises only when we execute “select 'Hello,
> World!' from sysibm.sysdummy1” type of query after setting cursor
> attribute to static.
> In this patch we are not setting cursor attribute to static by
> default. So, now your query will work fine.
>

> The modified patch of dbi is attached onhttp://groups.google.com/group/ibm_db/browse_thread/thread/2f6bdffa68...

> ...
>
> read more »

--
You received this message because you are subscribed to the Google Groups "ibm_db" group.
To post to this group, send email to ibm...@googlegroups.com.
To unsubscribe from this group, send email to ibm_db+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/ibm_db?hl=en.

[Anurag Jain]

Hi Andrey Sukhanov,
Here is my code :
import ibm_db
conn = ibm_db.connect("texts","db2inst1","fusion123")
 
I am getting this error :
Exception: [IBM][CLI Driver] SQL1531N  The connection failed because the name specified with the DSN connection string keyword could not be found in either the db2dsdriver.cfg configuration file or the db2cli.ini configuration file.  Data source name specified in the connection string: "TEXTS". SQLCODE=-1531

and weird thing is that, there is not file named as "db2dsdriver.cfg" or "db2cli.ini" .

I am using Ubuntu 14.04 and i have install db2 Express-C v10.5. I have file named as "db2dsdriver.cfg.sample" or "db2cli.ini.sample"

Can you help me out here?

Thanks in advance!
Anurag Jain

[Saba Kauser]

Hello Anurag,

The error means, there is db2dsdriver.cfg( or db2cli.ini) that is being read from some where in your system and you do not have a matching alias in either of these.
Have you catalogued the db you are trying to connect to . Because, with just the db name, user id and password, you either need to have the db catalogged or db2dsdriver.cfg/db2cli.ini file with correct mapping for the db and host/port.

you could also run:
"db2cli validate -all"  from command line and observe the db2dsdriver.cfg/db2cli.ini path there to confirm the location from where the files are being read.