-
15. Re: SQL Chunk with writer table truncate
I created a test app using JdbcItemWriter and upsert / merge sql statement:
jsr352/test-apps/upsertWriter at master · jberet/jsr352 · GitHub
The test uses the following db2 upsert / merge statement:
MERGE INTO MOVIES AS tab
USING (VALUES
(?,?,?,?)
) AS merge (rank,tit,grs,opn)
ON tab.rank = merge.rank
WHEN MATCHED THEN
UPDATE SET tab.tit = merge.tit,
tab.grs = merge.grs
WHEN NOT MATCHED THEN
INSERT (rank,tit,grs,opn)
VALUES (merge.rank, merge.tit, merge.grs, merge.opn)
The test data is from https://raw.githubusercontent.com/jberet/jsr352/master/jberet-support/src/test/resources/movies-2012.csv
Before running the test, I already added 2 rows into the table. And after running test:
1 row is updated / merged with batch data item property (#1);
1 row is not matched and so is untouched (#9999);
99 row is not matched and so are inserted as new records (#2 - #100).
The output after running test:
Records after running tests:
1 MARVEL'S THE AVENGERS 623357910.00 TBD
9999 TBD TBD TBD
2 THE DARK KNIGHT RISES 448139099.00 2012-07-20
3 THE HUNGER GAMES 408010692.00 2012-03-23
4 SKYFALL 304360277.00 2012-11-09
5 THE HOBBIT: AN UNEXPECTED JOURNEY 303003568.00 2012-12-14
6 THE TWILIGHT SAGA: BREAKING DAWN PART 2 292324737.00 2012-11-16
7 THE AMAZING SPIDER-MAN 262030663.00 2012-07-03
... ...
As you may have noticed, one diff between my sql and yours is, I keep ? markers inside the source table definition, and referencing the source tabel cols in both the matched and non-match sections.
-
16. Re: SQL Chunk with writer table truncate
I appreciate it. I reworked mine. I was till getting that strange generic message. I took the data and found that it was an insert and the source table had a few columns that were larger than my target table. I was only able to discover that when I ran it through SQL Developer. Is there a way to get the jdbcItemWriter to produce all of the messages from the batch execute so I get the clear message that a value is too large for the column <name>?
-
17. Re: SQL Chunk with writer table truncate
we should be able to extract more info from SQLException in JdbcItemWriter class. It currently jsut throw any exception during sql execution out, but it looks like some sql-specific info is not included in the standard Throwable print.
-
18. Re: SQL Chunk with writer table truncate
jdbcItemWriter should output more info from SQLException when jdbc batch update fails