"Write Database overwrite mode"

deepak_talimdeepak_talim MemberPosts:5Contributor I
edited June 2019 inHelp

Hi,

We are trying to load data from csv into IBM Big SQL table using 'Write Database', but its failing.
Below are various options we tried:
1. 'overwrite mode' set to 'append' to insert data into table for first, it fails.
SQLCODE=-206, SQLSTATE=42703, SQLERRMC=COLUMN_1, DRIVER=3.71.22.
The JDBC driver has thrown an SQLException.

2. 'overwrite mode' set to 'overwrite', it inserts the data but changes the table structure and priviledges.
After executing RapidMiner process it inserts data into table but select query for individual column fails and gives below error:
SELECT COLUMN_1 FROM TABLE_1

SQL0206N "COLUMN_1" is not valid in the context where it is used.
SQLSTATE=42703

Tagged:

Answers

  • Thomas_OttThomas_Ott RapidMiner Certified Analyst, RapidMiner Certified Expert, MemberPosts:1,761Unicorn
    This looks like a possible JDBC driver issue. Did you correctly configure the JDBC driver ?
  • 艾丁_Klapic艾丁_Klapic Moderator, Employee, RMResearcher, MemberPosts:299RM Data Scientist

    Hi deepak_talim,

    The 'append' mode is more or less the equivalent to an INSERT statement. Thus, the table and its columns have to be created upfront.

    The 'overwrite' mode is more or less the equivalent to DROP IF EXISTS followed by CREATE TABLE and INSERT. Thus, the new table only consists of the data you wrote in your last process execution.

    The error message of your second attempt is explained in the IBM Knowledge Base entryhttps://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00206n.htmland states that the Column does not exist in the table you selected the data from.

    Hope this helps,

    艾丁

    Thomas_Ott
  • deepak_talimdeepak_talim MemberPosts:5Contributor I

    Hi Thomas,

    We are using following jdbc drivers : db2jcc.jar & db2jcc4.jar (db2_db2driver_for_jdbc_sqlj_v11.1).

    Can you please specify which drivers are compatible with RapidMiner ver 7.2 and Big SQL ver 4.2

    Thanks

    Deepak T

  • deepak_talimdeepak_talim MemberPosts:5Contributor I

    Hi Edin,

    If "'overwrite' mode is more or less the equivalent to DROP IF EXISTS followed by CREATE TABLE and INSERT" then after creating new table I should be able to query the table specifying the columns "created" in the SELECT but getting above mentioned error.

    Can you please help to understand why am getting the error?

    Thanks

    Deepak T

  • 艾丁_Klapic艾丁_Klapic Moderator, Employee, RMResearcher, MemberPosts:299RM Data Scientist

    Hi Deepak,

    Which operator did you use?Execute SQLorRead Database?

    While the first can execute data manipulation queries and does not deliver any output (i.e. no data is fetched by RapidMiner) the latter is used to read data.

    What output did you get from

    SELECT * FROM TABLE

    Best regards,

    艾丁

  • deepak_talimdeepak_talim MemberPosts:5Contributor I

    Hi,

    We were able to solve the issue, it was related the way table is created in Big SQL.

    If we create table WITHOUT 'quotes' / "" in table name or column name as below, it gives error once data is loaded using RapidMiner.
    ex: create table dbo.table_test ( column_1 varchar(5), column_2 varchar(5))

    If we create table WITH 'quotes' / "" in table name or column name as below, it works without any error even after data is loaded using RapidMiner.
    ex: create table "dbo"."table_test" ( "column_1" varchar(5), "column_2" varchar(5))

    For data load we can use 'overwrite' or 'overwrite first, append later', it works.

    Thanks & Regards,

    Deepak Talim

    Thomas_Ott 艾丁_Klapic
  • 艾丁_Klapic艾丁_Klapic Moderator, Employee, RMResearcher, MemberPosts:299RM Data Scientist

    Great to hear that your problem is solved:)

    and Thank you for posting your solution!

    Best,

    艾丁

    Thomas_Ott
Sign InorRegisterto comment.