How to run DB2 export command in shell

Question

I am trying to run the following DB2 command through the Python pyodbc module:

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 it works fine for select statements, 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") 

I get an error:

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)')

When I use

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

I get another error:

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.

---

From what I came to know DB2 export is a command run in shell, not through SQL via ODBC.

can you please give me some more information on how to run the command in the shell i am confused and what does that mean ? any guide or small quick tutorial will be great

Answer 1

There are three (well, actually four...) ways to execute export in DB2.

There is the db2export API (.sh in Unix/Linux, .exe in Windows). This can be executed from anything that understands command shell.

There is the export command to the db2 command. This can be run from a DB2 interactive session, or from the command shell.

There is also the export command through the ADMIN_CMD stored procedure. This allows you to invoke the export from a stored procedure which is accessable through anything that can execute SQL against DB2. This is probably the option you are looking for if you want to do export via SQL through another tool.

There is also the db2move utility which can perform an export, but that is done via command shell as well.

• mustaccio: db2export is not a binary, it's an API function that you can use to create your own binary.
• Chris Aldrich: Corrected my answer.