Exception: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N unexpected token Error

Question

I have the following data frame

        CALL_DISPOSITION  CITY      END                                     INCIDENT_NUMBER 
0       ADV-Advised       Waterloo  Fri, 23 Mar 2018 01:13:27 GMT           6478983
1       AST-Assist        Waterloo  Sat, 18 Mar 2017 12:41:47 GMT           724030
2       AST-Assist        Waterloo  Sat, 18 Mar 2017 12:41:47 GMT           999000

I am trying to push this to an IBM DB2 Database.

I have the following code:

# IBM DB2 imports
import ibm_db

# instantiate db2 connection
connection_id = ibm_db.connect(
            conn_string,
            '',
            '',
            conn_option,
            ibm_db.QUOTED_LITERAL_REPLACEMENT_OFF)

# create list of tuples from df
records = [tuple(row) for _, row in df.iterrows()]

# Define sql statement structure to replace data into WATERLOO_911_CALLS table
column_names = df.columns
df_sql = "VALUES({}{})".format("?," * (len(column_names) - 1), "?")
sql_command = "REPLACE INTO WATERLOO_911_CALLS {} ".format(df_sql)


# Prepare SQL statement 
try:
    sql_command = ibm_db.prepare(connection, sql_command)
except Exception as e:
     print(e)

# Execute query
try:
    ibm_db.execute_many(sql_command, tuple(temp_records))
except Exception as e:
    print('Data pushing error {}'.format(e))

However, I keep getting the following error:

Exception: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "REPLACE INTO WATERLOO_911_CALLS" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<space>".  SQLSTATE=42601 SQLCODE=-104

I don't understand why that is the case. I followed the steps outlined in this repo but I can't seem to get this to work. What am I doing wrong? Please let me know there are any clarifications I can make.

Db2 is throwing this exception because Db2 does not have a statement `REPLACE INTO ` (such as offered by other RDBMS ). If a plain `INSERT INTO ` is not workable, use a different design, for example insert into a temporary or session-table, and then MERGE into the target table. — mao, Feb 29 '20 at 09:44

score 0 · Answer 1 · answered Feb 28 '20 at 20:04

It hints about missing spaces, maybe it needs one between the fields in the VALUE() string. Like df_sql = "VALUES({}{})".format("?, " * (len(column_names) - 1), "?") instead of df_sql = "VALUES({}{})".format("?," * (len(column_names) - 1), "?")

Just a hunch. I find that printing sql_command before executing it could also help troubleshooting.

Exception: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N unexpected token Error

1 Answers1