sql - Error with TRIMBLANKS argument when loading CSV data into DB2 using ibm_db in Python - Stack Overflow

admin管理员组

文章数量:1394138

I am using Python with the ibm_db library (version 3.2.6) to load data from a CSV file into a DB2 table. The following query is used for the data insertion process:

INSERT INTO DWHD1.USERS
SELECT * FROM external 'USERS.csv'
USING (
    CCSID 923
    DELIMITER '~'
    MAXERRORS 1
    CTRLCHARS 'ON'
    FILLRECORD 'TRUE'
    REMOTESOURCE 'JDBC'
    TIMESTAMP_FORMAT 'YYYY-MM-DD HH24:MI:SS'
    ESCAPECHAR '\'
    NULLVALUE ''
    LOGDIR '/tmp'
    SKIPROWS 1
    TRIMBLANKS 'BOTH'
)

However, when executing this query using ibm_db, I encounter the following error:

ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: Statement Execute Failed: 
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "TRIMBLANKS" was found following "SKIPROWS 1".  
Expected tokens may include:  "TRIM_NULLS".  
SQLSTATE=42601 SQLCODE=-104

I have already reviewed the official IBM documentation on external table creation IBM DB2 Documentation, and according to it, TRIMBLANKS is a valid argument. However, the error persists.

Additional Details:

• I am using the icr.io/db2_community/db2 Docker image.
• I have tried modifying the query in various ways, but the error continues to occur.
• db2level output:

DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL12011" with level
identifier "02020110".
Informational tokens are "DB2 v12.1.1.0", "s2412161033", "DYN2412161033AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V12.1".

Could someone help me understand why this error occurs and how to resolve the issue? Specifically, is there an issue with the use of TRIMBLANKS, or might the problem be related to the DB2 version or the Docker image?

Thank you in advance for your help!

I am using Python with the ibm_db library (version 3.2.6) to load data from a CSV file into a DB2 table. The following query is used for the data insertion process:

INSERT INTO DWHD1.USERS
SELECT * FROM external 'USERS.csv'
USING (
    CCSID 923
    DELIMITER '~'
    MAXERRORS 1
    CTRLCHARS 'ON'
    FILLRECORD 'TRUE'
    REMOTESOURCE 'JDBC'
    TIMESTAMP_FORMAT 'YYYY-MM-DD HH24:MI:SS'
    ESCAPECHAR '\'
    NULLVALUE ''
    LOGDIR '/tmp'
    SKIPROWS 1
    TRIMBLANKS 'BOTH'
)

However, when executing this query using ibm_db, I encounter the following error:

ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: Statement Execute Failed: 
[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "TRIMBLANKS" was found following "SKIPROWS 1".  
Expected tokens may include:  "TRIM_NULLS".  
SQLSTATE=42601 SQLCODE=-104

I have already reviewed the official IBM documentation on external table creation IBM DB2 Documentation, and according to it, TRIMBLANKS is a valid argument. However, the error persists.

Additional Details:

• I am using the icr.io/db2_community/db2 Docker image.
• I have tried modifying the query in various ways, but the error continues to occur.
• db2level output:

DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL12011" with level
identifier "02020110".
Informational tokens are "DB2 v12.1.1.0", "s2412161033", "DYN2412161033AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V12.1".

Could someone help me understand why this error occurs and how to resolve the issue? Specifically, is there an issue with the use of TRIMBLANKS, or might the problem be related to the DB2 version or the Docker image?

Thank you in advance for your help!

• sql
• db2

Share Improve this question Follow edited Mar 13 at 10:54 Ryuuk asked Mar 12 at 15:35 RyuukRyuuk 10111 gold badge33 silver badges1414 bronze badges 6

• Does the same INSERT statement work outside of python ? for example either at the Db2 CLP (if you have that installed), or from a java gui front-end tool, or in the db2 server container using its local CLP? – mao Commented Mar 12 at 15:57
• I tried using dbsql and I got the same error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "TRIMBLANKS" was found following "/tmp' SKIPROWS 1". Expected tokens may include: "TRIM_NULLS". SQLSTATE=42601 – Ryuuk Commented Mar 13 at 10:15
• 1 It is a Db2-server behaviour (nothing to do with python or dbi or ibm_db). Shell into the container and become the db2 instance owner and edit your question to add the plain text output of the db2level command (which is in ~/sqllib/bin directory of the instance-owner home directory). ALSO, if your target table uses VARCHAR (not CHAR(...)) then you may not need TRIMBLANKS . – mao Commented Mar 13 at 10:37
• Thank you for your response. I added the db2level output – Ryuuk Commented Mar 13 at 10:57
• As you are at the currently available levels (which may change in coming months), you need a workaround, for example by ensuring the target table for the insert (assuming it is a regular table) uses varchar columns as previously mentioned. Alternately if your employer has a Db2-support contract then you could get a support-ticket opened with IBM db2-support who can give a formal response. – mao Commented Mar 13 at 11:05

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

Don't put `BOTH` in quotes in your TRIMBLANKS option.

• Good: TRIMBLANKS BOTH

• Bad: TRIMBLANKS 'BOTH'

The same goes for the CTRLCHARS, FILERECORD, REMOTESOURCE options as well.

本文标签： sqlError with TRIMBLANKS argument when loading CSV data into DB2 using ibmdb in PythonStack Overflow