sql import export command error using pyodbc module python

Question

I am trying to run the following db2 command through the python pyodbc module

IBM DB2 Command : "DB2 export to C:\file.ixf of ixf select * from emp_hc"

i am successfully connected to the DSN using the pyodbc module in python and works fine for select statement

but when i try to execute the following command from the Python IDLE 3.3.2

cursor.execute(" export to ? of ixf select * from emp_hc",r"C:\file.ixf") pyodbc.ProgrammingError: ('42601', '[42601] [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "db2 export to ? of" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "". SQLSTATE=42601\r\n (-104) (SQLExecDirectW)')

or cursor.execute(" export to C:\file.ixf of ixf select * from emp_hc")

Traceback (most recent call last): File "", line 1, in cursor.execute("export to C:\myfile.ixf of ixf select * from emp_hc") pyodbc.ProgrammingError: ('42601', '[42601] [IBM][CLI Driver][DB2/LINUXX8664] SQL0007N The character "\" following "export to C:" is not valid. SQLSTATE=42601\r\n (-7) (SQLExecDirectW)')

am i doing something wrong ? any help will be greatly appreciated.

Answer 1

As EXPORT is a utility and not an SQL statement, you can't run it directly via a normal SQL interface.

Fortunately, IBM provides the SYSPROC.ADMIN_CMD() stored procedure to allow you do to just what you are looking for.

Be aware, however, that using SYSPROC.ADMIN_CMD() can only write the exported data to a location on the database server. (This is as opposed to using db2 export ... which writes the file to whatever machine you are starting the utility on (whether it's the database server or a client connecting across the network).

Answer 2

db2 export is a command run in the shell, not through SQL via odbc.

It's possible to write database query results to a file with python and pyodbc, but db2 export will almost certainly be faster and effortlessly handle file formatting if you need it for import.