How to connect DB2 with Python pyodbc

There are very few infromation about it and this is how I connect to DB2 from python and web2py.

My environment
-----------------------------------------------------------
OS: Windows 7 x86
iSeries Access: V5R3M0 - SI26600
Python: 2.5
pyodbc: pyodbc-2.1.7.win32-py2.5.exe
web2py: 1.98.2 (Source Code)
-----------------------------------------------------------

Pre-Requirements
IBM iSeries Access is installed on your machine so that you have the iSeries Access ODBC Driver.If you use DB2, it's already installed on you machine, isn't it?

1. Install pyodbc
Download and install from http://code.google.com/p/pyodbc/downloads/list.

2. Create ODBC Data Source

2.1 Go to: Control Panel - System and Security - Administrative Tools - Data Sources (ODBC)
2.2 Click Add and select iSeries Access ODBC Driver

2.3 Type Data source name and System (Your AS400 Name)

2.4 On Server tab, type SQL default library

2.5 Click Advanced button at the bottom 

2.6 Change Commit mode: from (*CHG) to (*NONE)

Very important:  If you don't change, you might get the following error.

Error: ('HY000', '[HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - TEST in MYLIB not valid for operation. (-7008)

Ref: https://www-304.ibm.com/support/docview.wss?uid=nas131821151773d0613862569b200550644

3. Connect from web2py

Now you are ready to connect.

3.1 Restart your web2py if it's already started

3.2 Create new app like AS400

3.3 Edit DB.PY as follows.

# -*- coding: utf-8 -*-
db = DAL('db2://DSN=MYDSN;UID=YourID;PWD=YourPassword')
db.define_table('test',
    Field('name'))

3.4 Save and click "database administration"

3.5 Check on AS400

3.6 Now click on "Insert new test" from web2py, input Name and click submit

3.7 Check on AS400 - Oh my got! It works !!!

* You can create separate DSN to connect different libraries

4 Connect from Python Shell

Notice: I'm using "." instead of "/". If you want,  you can change "Naming Conversion" to *SYS on the Server tab in the data source you created.

4.1 SELECT

>>> import pyodbc
>>> conn = pyodbc.connect('DSN=MYDSN;UID=YourID;PWD=YourPassword')
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT * FROM MYLIB.TEST")
<pyodbc.Cursor object at 0x01E6A598>
>>> for row in cursor:
print row

4.2 INSERT

(1, 'Omi Chiba')
>>> cursor.execute("INSERT INTO MYLIB.TEST VALUES(DEFAULT, 'PYTHON ROLL')")
<pyodbc.Cursor object at 0x01E6A598>

4.3 Check on AS400