Error while importing file into DB2 from python script

Question

Getting the below error while trying to import a ^ delimited file into a DB2 database using python 2.4.3.

Error:

Traceback (most recent call last):
  File "C:\Python25\Usefulscripts\order.py", line 89, in <module>
    load_order_stack() 
  File "C:\Python25\Usefulscripts\order.py", line 75, in load_order_stack
    conn2.execute(importTmp)
ProgrammingError: ('42601', '[42601] [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "orders_extract"

was found following "import from ".

Code:

import pyodbc

def load_order_stack():
    try:
        conn2 = pyodbc.connect('DSN=db2Database;UID=ueserid;PWD=password')
        importTmp = ("import from orders_extract of del modified by coldel0x5E"
                     "insert_update into test.ORDERS_Table (ORDER_ID,item,price);")
        conn2.execute(importTmp)
        conn2.commit()

Please tell us what did you try, what do you think is the problem. People are much more happy to help when you show them work toward solving it, not just throw work at them :). — Aida Paul
– Aida Paul, Commented May 15, 2013 at 10:32
Try putting the filename with full path between single quotes. Does the file exist on the database server? — Ansgar Wiechers
– Ansgar Wiechers, Commented May 15, 2013 at 11:01
Hi Puciek, yes I did try to load the file in various ways - including quotes withe escape characters, with fully qualified path as well, but got the same error — user2385306
– user2385306, Commented May 17, 2013 at 8:16
Hi Ansgar, no the file does not exist on the database server, actually its on a separate app server where i have my python script running. Would this be an issue ? — user2385306
– user2385306, Commented May 17, 2013 at 8:19

mustaccio · Accepted Answer · 2013-05-15 11:43:24Z

2

IMPORT is not an SQL statement. It is a DB2 Command Line Processor (CLP) command and as such can only be run by the said CLP.

There is an SQL interface to some CLP commands via calls to the ADMIN_CMD() stored procedure, please check the manual: IMPORT using ADMIN_CMD

answered May 15, 2013 at 11:43

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2385306 Over a year ago

I was able to get rid of the original error by using the "ADMIN_CMD" command as below. However, getting a different error now -> "Error: (' ', '[ ] [IBM][CLI Driver][DB2/LINUXX8664] SQL3030C An I/O error (reason = "sqlofopn -2029060079") occurred while opening the input file.\r\n (-3030) (SQLExecDirectW)')"

mustaccio Over a year ago

Clearly DB2 cannot open the file. I hope you realize that the file you import from must reside on the database server and should be readable by the instance owner user.

user2385306 Over a year ago

Is there an alternate way whereby i can import a file which exists on my app server (where the python script resides) different from the db server ? I don't have access to the database server file system

mustaccio Over a year ago

You could execute the DB2 CLP and pass the IMPORT command to it, making sure the DB2 environment is properly initialized. I think one way to do it is write the CONNECT and IMPORT commands to a file, then call db2cmd.exe -c db2 -f yourfile.txt

Bryan · Accepted Answer · 2013-05-15 13:34:43Z

1

You also have the option of reading the file, line by line, and inserting into your database. This will definitely be slower than any native import operation. Assuming your delimited file structure is, and the file is named input.txt:

ORDER_ID^item^price
1^'bat'^50.00
2^'ball'^25.00

Code:

import csv
import pyodbc

connection = pyodbc.connect('DSN=db2Database;UID=ueserid;PWD=password')
cursor = connection.cursor()

with open('input.txt', 'rb') as f:
    rows = csv.reader(f, delimiter='^')
    # get column names from header in first line
    columns = ','.join(next(rows))
    for row in rows:
        # build sql with placeholders for insert
        placeholders = ','.join('?' * len(row))
        sql = 'insert into ({}) values ({});'.format(columns, placeholders)

        # execute parameterized database insert
        cursor.execute(sql, row)
        cursor.commit()

Play around with commit() placement, you probably want to commit in batches to improve performance.

answered May 15, 2013 at 13:34

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

1 Comment

Ajay Venkata Raju Over a year ago

I tried this solution and i got an error saying "iSeries Access ODBC Driver SQL7008 INCLU00001 not valid for operation. (-7008) (SQLExecDirectW)')" I fixed by creating a journal to the table i am inserting records into and it solved the problem. Thanks

Collectives™ on Stack Overflow

Error while importing file into DB2 from python script

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related