topic Re: DB2 Sequences in Talend Studio

DB2 Sequences

Anonymous — Sat, 16 Nov 2024 14:16:17 GMT

Hallo,
a question from a newbie to talend.
I have to use DB2 sequences in the following way:
1.: selecting data from a table (from one schema)
2.: inserting the data into another table (another schema), adding a sequence key to each row (the sequence object of the target table is defined; via command line I can insert the data)
(for testing, i changed this step to: output to a file, but using the sequence object in the database)

I've tried it in the following way:
connecting to the Database (done it in other examples, it is okay; DB2: Client 8.1; Server: v9.1)
- selecting the sequence object
- selecting the data
'join' them and output them to a file (for testing)
But this seems to be wrong:
Starte Job test_kk am 15:04 01/08/2008.
java.lang.NoClassDefFoundError: test/test_kk_0_1/test_kk
Caused by: java.lang.ClassNotFoundException: test.test_kk_0_1.test_kk
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
Exception in thread "main"
Job test_kk endet am 15:04 01/08/2008.
The problem seems to be the selection of the sequence.
Any Idea?
Thanks

Re: DB2 Sequences

Anonymous — Fri, 01 Aug 2008 19:56:37 GMT

Why not just define a column in the DB2 table where you are going to insert the data into to be a sequence number column and let DB2 handle it for you?
Here's sample DDL...
CREATE TABLE MYTABLE1 (
MY_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +9000000
NO CYCLE
CACHE 20
NO ORDER ) ,
BATCH_ID SMALLINT NOT NULL ,
TABLE_ID SMALLINT NOT NULL ,
SEQ SMALLINT NOT NULL ,
PROC_KEY CHAR(5)
);

Re: DB2 Sequences

Anonymous — Mon, 04 Aug 2008 07:18:00 GMT

Sorry, unfortunately we can't change the DB2 in that way, it has "historical reasons".
Any other ideas?
Thanks a lot.

Re: DB2 Sequences

Anonymous — Mon, 04 Aug 2008 17:48:04 GMT

You can use the numeric.sequence function in your tMap. Var Expression: Numeric.sequence("seqnum",1,1)
Then you'll just need to initialize that variable by doing a SQL select max(IDColumn) on your table.

Re: DB2 Sequences

Anonymous — Tue, 05 Aug 2008 08:16:31 GMT

Dear SMaz,
I've tried this, but then Talend is't able to generate code (see messagebox below). The appropriate error message is:
Starte Job test_kk am 08:24 05/08/2008.
java.lang.NoClassDefFoundError: test/test_kk_0_1/test_kk
Caused by: java.lang.ClassNotFoundException: test.test_kk_0_1.test_kk
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
Exception in thread "main"
Job test_kk endet am 08:24 05/08/2008.

Perhaps it is the best to describe the situation (what I want to do) a little more detailed.
We have an existing DB2 database (v8 / v9) where sequence objects are created like
create sequence SCHEMANAME.SEQ_<REFERENCED_TABLENAME> as INTEGER
start with 1
increment by 1
minvalue 1
maxvalue 2147483647
cache 20
order;
The sequence has, except of the 'seq'-prefix, the same name as the table where it is 'used' (in the table exists a corresponding integer-key-column).
To fill the table with a new row, a select of the next value from the sequence object has to be made and the result is then the new value of the new row (sorry, we can't change this behavior).
So, what do I have to do to insert new data with talend?
I tried out the following to test it (I don't need to use the Number.sequence in this way): selecting the sequence and writing it into a file, joined with the inserted data:
- connect to the db (tDB2Connection)
- - onSubjobOk: select the next number from the sequence (tDB2Row: SELECT (nextval for schemaname.SEQ_AKT_AKTIVITAET) as seq from sysibm.sysdummy1))
- - onSubjobOk: using a tDB2Input to collect the other data
- - - join the two '- -' actions (the sequence as a 'Lookup', the other as 'Main') via the tMap (joining the outputs) and output them to a tFileOutputDelimited object
When doing it in this way, a similar error as the above one pops up:
Starte Job test_kk am 09:15 05/08/2008.
java.lang.NoClassDefFoundError: test/test_kk_0_1/test_kk
Caused by: java.lang.ClassNotFoundException: test.test_kk_0_1.test_kk
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
Exception in thread "main"
Job test_kk endet am 09:15 05/08/2008.

Not very complicated things (but somehow wrong?)...
I think, this should be solveable in Talend.
Thanks for an answer

Re: DB2 Sequences

Anonymous — Tue, 05 Aug 2008 22:21:47 GMT

I think your flow will end up something like this:
tDB2Connection
-- On Subjob OK
tDB2Input --> tMap --> tDB2Row --> Output
On the tDB2Row, you can keep your select as it is.
There is an option, on the Advanced settings tab of the tDB2Row to allow you to put the result of the query into a column.
Once you check "Propagate QUERY's recordset" a drop down box saying "use column" will appear and you choose what column you want to put the result in.
Of course, this means that you need to have a "blank" column to put the answer in. That will be the purpose of your tMap. Just add an extra column to hold your sequence number on the outgoing flow.
I created a real simple example with just one incoming column - see the attached pictures.
P.S. -> I wish my German were as good as your English. It's been many, many years since I had a chance to use it. Seeing your dialog box brought back some fond memories.

Re: DB2 Sequences

Anonymous — Wed, 06 Aug 2008 14:23:34 GMT

Thank's for Your endeavors to assist, but this answer leads to another problem:
it seems that Talend "don't know", that the result of the sequence query ist only one integer and not a complete resultset.
As a result, another error message was generated:
Starte Job test_xxx am 15:00 06/08/2008.
Exception in thread "main" java.lang.Error: Unresolved compilation problem:
Type mismatch: cannot convert from ResultSet to Integer
at test.test_xxx_0_1.test_xxx.tDB2Input_1Process(test_xxx.java:1124)
at test.test_xxx_0_1.test_xxx.tDB2Connection_1Process(test_xxx.java:216)
at test.test_xxx_0_1.test_xxx.runJobInTOS(test_xxx.java:1371)
at test.test_xxx_0_1.test_xxx.main(test_xxx.java:1286)
Job test_xxx endet am 15:00 06/08/2008.
Or had I done something wrong once again?
Thanks for Your patience!
PS: Thank You for jour praise of my english, but I think it isn't really so good (the dictionary is my best friend)!
Where did You learned the german language? Or are You from germany?

Re: DB2 Sequences

Anonymous — Fri, 08 Aug 2008 15:19:15 GMT

DB2 sequences seems to be some kind of "unsopported" by talend, am I right?
I reduced the components to three actions:
1.: connecting
2.: selecting via tDB2Row and the SQL statement "SELECT (nextval for KBP.SEQ_AKT_AKTIVITAET) as seq from sysibm.sysdummy1)" (perhaps another component should be used?)
3.: directing the poutput to a csv file
The second step comes up with a compilation error:
Starte Job seq_test am 16:04 08/08/2008.
Exception in thread "main" java.lang.Error: Unresolved compilation problem:
OnSubjobOk cannot be resolved
at tests.seq_test_0_1.seq_test.tDB2Row_1Process(seq_test.java:494)
at tests.seq_test_0_1.seq_test.tDB2Connection_1Process(seq_test.java:206)
at tests.seq_test_0_1.seq_test.runJobInTOS(seq_test.java:673)
at tests.seq_test_0_1.seq_test.main(seq_test.java:588)
Job seq_test endet am 16:04 08/08/2008.
What is wrong with it?
The code ist very straightforward and, when even this easy thing goes wrong, what ist with the "real life problems"?
Does someone have an idea (a tested solution: selecting a sequence and writing it to a file (the output should be one line))?

Re: DB2 Sequences

_AnonymousUser — Fri, 27 Mar 2009 18:47:06 GMT

Has anyone sole the DB2 sequence problem? We use them all the time with Informatica.

Re: DB2 Sequences

Luiz_Filipe — Mon, 05 Oct 2015 19:04:50 GMT

Has anyone sole the DB2 sequence problem? We use them all the time with Informatica.

Well, here is what I did.
1) A query in DB2 as a lookup in tMap just before the Load, with the query "SELECT 1 AS COD, (NEXTVAL FOR <SEQUENCE>) AS SEQ FROM SYSIBM.SYSDUMMY1."
2) A join with this loopkup, passing "1" fixed as Key. This will return the same latest value from sequence from all records.
3) 2 Variables in the tMap, one to receive the lookup value, and other to increase + 1. This will increment the latest value from sequence in the tMap.
4) Map the calculated variable to the sequence field in the target, and load the data.
5) After the loading (I did in a Postjob), a DB2 SP procedure running "ALTER SEQUENCE <SEQUENCE> RESTART WITH <LAST VALUE FROM TMAP>"
To catch the latest value calculated inside a tMap, i did a little trick in 3 parts. 1) A new output to a tAggregateRow with only the sequence variable with a MAX, 2) a new context variable in the Job called MaxId, 3) a tJavaRow to assign the Max value from the tAggregate to the context variable.
This worked fine for me...
Regards