SQL Chunk with writer table truncate

[richardmoore]

Is there a way to do a truncate on my target table before the inserts while using a chunk?

[cfang]

the truncate operation is one time only, so it doesn't fit into the chunk-oriented processing step.  You can have another step running prior to it, a batchlet step that truncates the table.

We might already have one batchlet, or similar one, in jberet, that runs a sql command.

[richardmoore]

Instead of the truncate we are doing an upsert (shown below). It works fine until it hits the item-count and then I get -

DB2 SQL Error: SQLCODE=-117, SQLSTATE=42802, SQLERRMC=null, DRIVER=4.16.53

I tried 3000 then 10 with the same results. Here is the output at 10 -

ProcessingInfo{count=10, timerExpired=false, itemState=RUNNING, chunkState=RUNNING, checkpointPosition=-1, readPosition=9, failurePoint=null}

item-count=10, time-limit=0, skip-limit=-1, skipCount=0, retry-limit=-1, retryCount=0

JBERET000007: Failed to run job RPE_Daily_AIMAPVNDR, upsertTable, org.jberet.job.model.Chunk@76c3cd5a

Is there something taking place at the end of the item-count that is causing this?

merge into db2pdba.aim_ap_vndr as vndr using (

  select * from table (values (?))

  ) as vndr2(ap_vndr_cd) on vndr2.ap_vndr_cd = vndr.ap_vndr_cd

  when matched then update set

  AP_VNDR_CD = ?,

  AP_NM = ?,

  AP_STRT1_AD = ?,

  AP_STRT2_AD = ?,

  AP_CITY_NM = ?,

  AP_STT_CD = ?,

  AP_ZIP_CD = ?,

  AP_ZIP4_CD = ?,

  AP_PHN_NB = ?,

  AP_FAX_NB = ?,

  AR_NM = ?,

  AR_STRT1_AD = ?,

  AR_STRT2_AD = ?,

  AR_CITY_NM = ?,

  AR_STT_CD = ?,

  AR_ZIP_CD = ?,

  AR_ZIP4_CD = ?,

  INSRT_TS = ?,

  UPDT_TS = ?

  when not matched then insert (

  AP_VNDR_CD,

  AP_NM,

  AP_STRT1_AD,

  AP_STRT2_AD,

  AP_CITY_NM,

  AP_STT_CD,

  AP_ZIP_CD,

  AP_ZIP4_CD,

  AP_PHN_NB,

  AP_FAX_NB,

  AR_NM,

  AR_STRT1_AD,

  AR_STRT2_AD,

  AR_CITY_NM,

  AR_STT_CD,

  AR_ZIP_CD,

  AR_ZIP4_CD,

  INSRT_TS,

  UPDT_TS

  ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"/>

  <property name="parameterNames" value="AP_VNDR_CD,

  UPDT_AP_VNDR_CD,

  UPDT_AP_NM,

  UPDT_AP_STRT1_AD,

  UPDT_AP_STRT2_AD,

  UPDT_AP_CITY_NM,

  UPDT_AP_STT_CD,

  UPDT_AP_ZIP_CD,

  UPDT_AP_ZIP4_CD,

  UPDT_AP_PHN_NB,

  UPDT_AP_FAX_NB,

  UPDT_AR_NM,

  UPDT_AR_STRT1_AD,

  UPDT_AR_STRT2_AD,

  UPDT_AR_CITY_NM,

  UPDT_AR_STT_CD,

  UPDT_AR_ZIP_CD,

  UPDT_AR_ZIP4_CD,

  UPDT_INSRT_TS,

  UPDT_UPDT_TS,

  INSRT_AP_VNDR_CD,

  INSRT_AP_NM,

  INSRT_AP_STRT1_AD,

  INSRT_AP_STRT2_AD,

  INSRT_AP_CITY_NM,

  INSRT_AP_STT_CD,

  INSRT_AP_ZIP_CD,

  INSRT_AP_ZIP4_CD,

  INSRT_AP_PHN_NB,

  INSRT_AP_FAX_NB,

  INSRT_AR_NM,

  INSRT_AR_STRT1_AD,

  INSRT_AR_STRT2_AD,

  INSRT_AR_CITY_NM,

  INSRT_AR_STT_CD,

  INSRT_AR_ZIP_CD,

  INSRT_AR_ZIP4_CD,

  INSRT_INSRT_TS,

  INSRT_UPDT_TS

  "/>

  <property name="parameterTypes" value="STRING,

  STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, DATE, DATE,

  STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, STRING, DATE, DATE"/>

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

  </properties>

  </writer>.

[cfang]

at the end of a chunk, it's doing commit.  I guess it is when it failed.  There may be something in the long upsert statement caused db2 to fail to commit.

db2 error code -117

THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER OF SPECIFIED OR IMPLIED COLUMNS

[cfang]

in a regular item writer sql statement, it's usual in the form:

INSERT INTO PERSON(NAME, ADDRESS, AGE) VALUES(?, ?, ?)

then jdbcItemWriter set parameter values for each ? marker, getting value from the incoming item data indexed by the parameterNames.

In your upsert statement, you have 2 sets of (?, ?, ?, ...) markers, one for insert and one for update.  So I guess the 2nd set of markers are not filled with values.

[richardmoore]

I did another count of ? and I miscounted in the insert portion of the statement. I removed the extra ? and got the following -

Invalid parameter 1: Parameter is not set nor registered. ERRORCODE=-4461, SQLSTATE=42815

Is there a way to see how the statement is being constructed with the values since it still looks like there is a problem with the values getting set? Do you think this will even work?

[cfang]

if my suspicion turns true, to fix it you can repeat the values in parameterNames and parameterTypes batch properties, such that each element of parameterNames and parameterTypes exactly match one ? in the entire upsert statement.

This should work since you are using beanType of java.util.Map, which supplies value by key, no matter how many times the value is retrieved.

for ex, if your current parameterNames is:

name, address, age,

then double it to

name, address, age, name, address, age

if your current parameterTypes is:

String, String, Int

then double it to

String, String, Int, String, String, Int

[update] ok i see you already have different paremeter names for all ? marker, so ignore that repeating parameter name part.

but in your batch data passed to item writer as a Map, do you really have all these keys as specified in parameterNames?  It seems most of them are duplicate pairs, like UPDT_AP_NM and   INSRT_AP_NM,

[richardmoore]

I had already done that in the prior examples. I want to override the jdbc delivered code to see what is being pulled in an attempted to be assigned. Which class and method do I need to look at? I was looking at the JdbcItemWrite.determineParameterNames but I couldn't get any output in my log.

[cfang]

you can set a debugger breakpoint in

private void setParameter(final int i, final Object val)

and inspect each call to this method, and the parameter name, and value.

This method is called once for each ? marker.

[richardmoore]

I am starting to think that a merge cannot be run with "addBatch".

[richardmoore]

I did try that on the setParameter and they are all getting set and to the correct values.

[richardmoore]

I'm really stuck, can't seem to figure out what is going on. I setup a java class that does an upsert with the chunk sql and data listed that fails and it works. I have checked the assignments in the mapParameters method of the jdbcItemWriter and it seems to assign everything correctly but it still fails with the - Invalid parameter 1: Parameter is not set nor registered. ERRORCODE=-4461, SQLSTATE=42815

If you have access to DB2 can you test the merge statement to see if you can see the problem?

[cfang]

using upsert/merge in ItemWriter is an interesting idea.  I'll try to create a sample app and see how it goes.

[cfang]

Your typeParameters all use UPPERCASE for STRING, DATE, etc.  By looking at org.jberet.support.io.JdbcItemWriter#setParameter method, they will not match any expected types, and so will not be set to the statement.

Did you try

<property name="parameterTypes" value="String, String, ... Date..."/>

?

1. JBERET-311

jdbcItemWriter should fail on unexpected parameterTypes values

[richardmoore]

Ahh. I made that change and now I have something different to work with. I'll see what I can do with this. Thanks.

2017-02-14 14:25:15.433 [jberet-1    ]                              jberet - [ERROR] ProcessingInfo{count=1, timerExpired=false, itemState=RUNNING, chunkState=RUNNING, checkpointPosition=-1, readPosition=0, failurePoint=null}

2017-02-14 14:25:15.448 [jberet-1    ]                              jberet - [ERROR] item-count=1, time-limit=0, skip-limit=-1, skipCount=0, retry-limit=-1, retryCount=0

2017-02-14 14:25:15.449 [jberet-1    ]                              jberet - [ERROR] JBERET000007: Failed to run job RPE_Daily_AIMAPVNDR, upsertTable, org.jberet.job.model.Chunk@5cc6a407

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

  at com.ibm.db2.jcc.am.fd.a(fd.java:467) ~[db2jcc4.jar:?]

  at com.ibm.db2.jcc.am.Agent.endBatchedReadChain(Agent.java:414) ~[db2jcc4.jar:?]

  at com.ibm.db2.jcc.am.uo.a(uo.java:5192) ~[db2jcc4.jar:?]

  at com.ibm.db2.jcc.am.uo.c(uo.java:4779) ~[db2jcc4.jar:?]

  at com.ibm.db2.jcc.am.uo.executeBatch(uo.java:3007) ~[db2jcc4.jar:?]

  at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:345) ~[commons-dbcp2-2.1.jar:2.1]

  at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:345) ~[commons-dbcp2-2.1.jar:2.1]

  at org.jberet.support.io.JdbcItemWriter.writeItems(JdbcItemWriter.java:108) ~[jberet-support-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.runtime.runner.ChunkRunner.doCheckpoint(ChunkRunner.java:528) ~[jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.runtime.runner.ChunkRunner.readProcessWriteItems(ChunkRunner.java:317) ~[jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.runtime.runner.ChunkRunner.run(ChunkRunner.java:201) [jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.runtime.runner.StepExecutionRunner.runBatchletOrChunk(StepExecutionRunner.java:226) [jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecutionRunner.java:147) [jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(CompositeExecutionRunner.java:164) [jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.runtime.runner.CompositeExecutionRunner.runFromHeadOrRestartPoint(CompositeExecutionRunner.java:88) [jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.runtime.runner.JobExecutionRunner.run(JobExecutionRunner.java:60) [jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at org.jberet.spi.JobExecutor$1.run(JobExecutor.java:99) [jberet-core-1.3.0.Beta3.jar:1.3.0.Beta3]

  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_73]

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_73]

  at java.lang.Thread.run(Thread.java:745) [?:1.8.0_73]