Talend community forum / DB2 ERROR when using tDB2Output

homero · 2008-02-14 01:20:55

Hi there,
I'm getting the following error when executing a simple job that reads from a flat file and try to insert into DB2:

********************* LOG ***************************
Starting job load_Client_DIM at 11:04 14/02/2008.
[statistics] connecting to socket on port 4190
[statistics] connected
Exception in component tDB2Output_1
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: ZESPEF.CLIENT_DIM
[statistics] disconnected
at com.ibm.db2.jcc.a.rf.e(rf.java:1680)
at com.ibm.db2.jcc.a.rf.a(rf.java:1239)
at com.ibm.db2.jcc.b.jb.h(jb.java:139)
at com.ibm.db2.jcc.b.jb.a(jb.java:43)
at com.ibm.db2.jcc.b.w.a(w.java:30)
at com.ibm.db2.jcc.b.cc.g(cc.java:160)
at com.ibm.db2.jcc.a.rf.n(rf.java:1219)
at com.ibm.db2.jcc.a.sf.gb(sf.java:1790)
at com.ibm.db2.jcc.a.sf.d(sf.java:2266)
at com.ibm.db2.jcc.a.sf.Y(sf.java:540)
at com.ibm.db2.jcc.a.sf.executeUpdate(sf.java:523)
at dva_dw.load_client_dim.load_Client_DIM.tFileInputDelimited_1Process(load_Client_DIM.java:918)
at dva_dw.load_client_dim.load_Client_DIM.runJob(load_Client_DIM.java:1102)
at dva_dw.load_client_dim.load_Client_DIM.main(load_Client_DIM.java:1029)
Job load_Client_DIM ended at 11:04 14/02/2008. [exit code=1]

********************* LOG ***************************

I've checked a few things before asking for help, but with no luck.
It looks like the problem is the setting of the SCHEMA in db2, or the lack of it, as when I set my properties for the tdb2Output component there is no place to specify what SCHEMA to use????...so I'm guessing, at execution time, the component uses the USERNAME (in my case ZESPEF) as default schema, but this is completing different to the schema where the table resides....does it make any sense? How can i specify the schema where the table resides in DB2?

Just in case, to avoid confusion, schema here is refered as per DB2 concept and not Talend schema definitions...

I will really appreciate your help ....
Thank you very much...

/H

shong · 2008-02-14 03:36:40

Hi

when I set my properties for the tdb2Output component there is no place to specify what SCHEMA to use????...

First, you should make sure you can connect to database.
There is a 'Edit Schema' button in properties panel, click this button to define schema. Make sure the Db columns are the same as the fileds in real table.(see the screenshot)

Best regards

shong

Uploaded Images

yanns · 2008-02-17 23:43:30

Hi,

The catch is that db2will perform differently when u do a simple connect than when u do an INSERT SQL, i.e. it will expect the schema name of the database before the table for INSERT.
Thus when u set your db connection without database schema, the check will return "OK". but it will not work under tDB2Ouput stage

The trick is to add your schema after your database name in the db connection setup.
The string should look like this:
Database: databasename:currentSchema=MySchema; where databasename is your db name and MySchema your schema name. Important !!! : Do not forget the ";" at the end of the string otherwise, it does not work!!!

Hope this help

Regards

Yann

homero · 2008-02-18 01:42:19

Thanx Yann. Very good tip!!!!

EricB · 2009-06-26 15:12:46

Can someone help me with this?

I am also trying to connect to a db2 database. I don't see where to use this 'currentSchema' statement as mentioned above. I am using TOS 3.0.2.

Using the IBM DB2 db type, the only place I see that makes sense to put this is in the Sid field of the db connection setup. But when I tried that I got an error message saying "The 'currentSchema' property is not allowed on the target server."

I also was wondering if I can do this using an ODBC connection or if there is anyway around having to actually specify the schema in the db connection in order to extract data from a db2 table.

Thanks to anyone that can help.

Eric

shong · 2009-06-29 08:32:14

Hello

I don't see where to use this 'currentSchema' statement as mentioned above. I am using TOS 3.0.2.

Please see my screenshot.

Best regards

shong

Uploaded Images

rarcega · 2012-01-19 07:29:57

Similarly, if you are using the tDB2Connection component you can enter the following in the 'Database' field:

<database>:currentSchema=<schema>;

[img]http://i.imgur.com/GHqVg.png[/img]

Announcement

Unanswered posts

#1 2008-02-14 01:20:55

DB2 ERROR when using tDB2Output

#2 2008-02-14 03:36:40

Re: DB2 ERROR when using tDB2Output

#3 2008-02-17 23:43:30

Re: DB2 ERROR when using tDB2Output

#4 2008-02-18 01:42:19

Re: DB2 ERROR when using tDB2Output

#5 2009-06-26 15:12:46

Re: DB2 ERROR when using tDB2Output

#6 2009-06-29 08:32:14

Re: DB2 ERROR when using tDB2Output

#7 2012-01-19 07:29:57

Re: DB2 ERROR when using tDB2Output

Board footer