jdbcItemWriter

[richardmoore]

I am having a issue inserting anything into one of my tables and have spent about 6 hours trying to figure it out. I have included the writer jsl and data, and the straight java I used that works. For the life of me I can't figure out what is different. This is the first time we have tried to insert something other than String values.

I am getting -

JBERET000007: Failed to run job AIS_Daily_ScmAllowanceIfaceLoad, loadInterfaceTable, org.jberet.job.model.Chunk@2c1f4842

com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.16.53] Batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch.

Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null

My map for the insert is -

{

EFFECTIVE_DATE=2018-03-19 00:00:00,

INT_DEAL_NBR=,

ACTION_CODE=A,

REVERT_DATE=2018-04-16 00:00:00,

ITEM_CD=0010110,

FACL=00,

EXT_GRP_ID=K300,

TIME_STAMP=0001-01-01 00:00:00,

WHSE=01,

INT_ARB_NBR=,

VEN_CD=0003730,

AMOUNT=28.8000,

EXT_ARB_ID=,

USR_DEAL_NUMBER=1127350,

CLOSED_DATE=,

STORE_CD=00020

}

My jsl for the writer -

<writer ref="jdbcItemWriter">

<properties>

<property name="dataSourceLookup" value="#{jobProperties['interfaceDataSource']}" />

<property name="sql" value="

insert into SCCI_DEAL_EXPENSE (

CPY_CD

, ALLNC_REBATE_TYPE

, PERCENT

, PCENT_BASIS

, APPLY_ARB

, ROW_SOURCE

, SUB_TYPE

, ACCOUNT

, ORDER_TYPE

, DESCRIPTION

, PROMO_ID

, CONTROL_NO

, ACL_CD

, DATE_ADDED

, TIME_ADDED

, FUNDS_TYPE

, TIME_STAMP

, INT_ARB_NBR

, INT_DEAL_NBR

, CLOSED_DATE

, FACL

, WHSE

, ITM_CD

, USR_DEAL_NUMBER

, EFFECTIVE_DATE

, REVERT_DATE

, AMOUNT

, STORE_CD

, EXT_GROUP_ID

, VEN_CD

, EXT_ARB_ID

, ACTION_CODE

) values (

'AWG'

, 'A'

, 0

, ' '

, 'Y'

, 'AWGTPB'

, 'RO'

, ' '

, ' '

, 'Promo'

, 'ADM'

, '00989898'

, ' '

, current date

, current time

, 'E'

, to_date(?, 'YYYY-MM-DD HH24:MI:SS')

, ?

, ?

, ?

, ?

, ?

, ?

, ?

, to_date(?, 'YYYY-MM-DD HH24:MI:SS')

, to_date(?, 'YYYY-MM-DD HH24:MI:SS')

, ?

, ?

, ?

, ?

, ?

, ?

)

" />

<property name="parameterNames" value="

TIME_STAMP

, INT_ARB_NBR

, INT_DEAL_NBR

, CLOSED_DATE

, FACL

, WHSE

, ITM_CD

, USR_DEAL_NUMBER

, EFFECTIVE_DATE

, REVERT_DATE

, AMOUNT

, STORE_CD

, EXT_GROUP_ID

, VEN_CD

, EXT_ARB_ID

, ACTION_CODE

" />

<property name="parameterTypes" value="

String

, String

, String

, String

, String

, String

, String

, String

, String

, String

, BigDecimal

, String

, String

, String

, String

, String

" />

<property name="beanType" value="java.util.Map" />

</properties>

</writer>

================================================================================================================

If I run this from a just java it works using the following -

My map data for the parameters -

record1.put("TIME_STAMP", "0001-01-01 00:00:00");

record1.put("INT_ARB_NBR", "");

record1.put("INT_DEAL_NBR", "");

record1.put("CLOSED_DATE", "");

record1.put("FACL", "00");

record1.put("WHSE", "01");

record1.put("ITM_CD", "0010110");

record1.put("USR_DEAL_NUMBER", "1127350");

record1.put("EFFECTIVE_DATE", "2018-03-19 00:00:00");

record1.put("REVERT_DATE", "2018-04-16 00:00:00");

record1.put("AMOUNT", new BigDecimal("28.8000"));

record1.put("STORE_CD", "00020");

record1.put("EXT_GROUP_ID", "K300");

record1.put("VEN_CD", "0003730");

record1.put("EXT_ARB_ID", "");

record1.put("ACTION_CODE", "A");

Based on the type of object in the map (above) I use either -

stmt.setBigDecimal(position, value);

stmt.setString(position, value);

insert into SCCI_DEAL_EXPENSE (

CPY_CD

, ALLNC_REBATE_TYPE

, PERCENT

, PCENT_BASIS

, APPLY_ARB

, ROW_SOURCE

, SUB_TYPE

, ACCOUNT

, ORDER_TYPE

, DESCRIPTION

, PROMO_ID

, CONTROL_NO

, ACL_CD

, DATE_ADDED

, TIME_ADDED

, FUNDS_TYPE

, TIME_STAMP

, INT_ARB_NBR

, INT_DEAL_NBR

, CLOSED_DATE

, FACL

, WHSE

, ITM_CD

, USR_DEAL_NUMBER

, EFFECTIVE_DATE

, REVERT_DATE

, AMOUNT

, STORE_CD

, EXT_GROUP_ID

, VEN_CD

, EXT_ARB_ID

, ACTION_CODE

) values (

'AWG'

, 'A'

, 0

, ' '

, 'Y'

, 'AWGTPB'

, 'RO'

, ' '

, ' '

, 'Promo'

, 'ADM'

, '00989898'

, ' '

, current date

, current time

, 'E'

, to_date(?, 'YYYY-MM-DD HH24:MI:SS')

, ?

, ?

, ?

, ?

, ?

, ?

, ?

, to_date(?, 'YYYY-MM-DD HH24:MI:SS')

, to_date(?, 'YYYY-MM-DD HH24:MI:SS')

, ?

, ?

, ?

, ?

, ?

, ?

)

[cfang]

I'll take a close look.  Did you get any root cause exceptions from the jdbc driver?  I remember we fixed jdbcItemWriter a while ago to display all nested sql exceptions.   The root cause from db2 driver will help determine the failure.  [JBERET-310] jdbcItemWriter should output more info from SQLException when jdbc batch update fails - JBoss Issue Tracker  was fixed in Feb 2017, and is in version 1.3.0.Beta5 and later (as of now the latest version is 1.3.0.Beta7).

A typical cause in general is it may be a data type mismatch, between java types in the data in your input map, and those expected by the corresponding cql data type.

[richardmoore]

I double checked the data types for the columns -

"TIME_STAMP" // TIMESTAMP

, "INT_ARB_NBR" // CHARACTER

, "INT_DEAL_NBR" // CHARACTER

, "CLOSED_DATE" // CHARACTER

, "FACL" // CHARACTER

, "WHSE" // CHARACTER

, "ITM_CD" // CHARACTER

, "USR_DEAL_NUMBER" // CHARACTER

, "EFFECTIVE_DATE" // DATE

, "REVERT_DATE" // DATE

, "AMOUNT" // DECIMAL(9, 5)

, "STORE_CD" // CHARACTER

, "EXT_GROUP_ID" // CHARACTER

, "VEN_CD" // CHARACTER

, "EXT_ARB_ID" // CHARACTER

, "ACTION_CODE" // CHARACTER

I used the same data and datatypes for the parameters in the jdbcItemWriter as I did in my stand alone java program.

[richardmoore]

I do not have the most current version, I have been too buried with work to get to testing it. That is the only message I get from Jberet. It works in the stand alone program so messages to help there.

[cfang]

you can use more recent version of jberet-support to get the nested sql exception, or use a debugger to inspect the nested sql exception.  Have you tried lower the chunk size (item-count) to 1, that should be the most similar to what you did with the standalone teset progam?  What's the ddl create table statement look like, so I can try it? 

[cfang]

I deleted the schema just for security reason.

[cfang]

2 columns EXT_GROUP_ID and FUNDS_TYPE are in the insert statement, but not in the table definition you posted yesterday.  Can you check?

[richardmoore]

Man I appreciate it and sorry for such a small issue that had nothing to do with framework. I was going crazy, guess I had looked at the problem too long. Again thanks.