1 2 Previous Next 18 Replies Latest reply on Feb 16, 2017 2:35 PM by Cheng Fang

    SQL Chunk with writer table truncate

    Richard Moore Master

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

        • 1. Re: SQL Chunk with writer table truncate
          Cheng Fang Master

          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.

          • 2. Re: SQL Chunk with writer table truncate
            Richard Moore Master

            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>.

            • 3. Re: SQL Chunk with writer table truncate
              Cheng Fang Master

              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

              • 4. Re: SQL Chunk with writer table truncate
                Cheng Fang Master

                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.

                • 5. Re: SQL Chunk with writer table truncate
                  Richard Moore Master

                  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?

                  • 6. Re: SQL Chunk with writer table truncate
                    Cheng Fang Master

                    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,

                    • 7. Re: SQL Chunk with writer table truncate
                      Richard Moore Master

                      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.

                      • 8. Re: SQL Chunk with writer table truncate
                        Cheng Fang Master

                        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.

                        • 9. Re: SQL Chunk with writer table truncate
                          Richard Moore Master

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

                          • 10. Re: SQL Chunk with writer table truncate
                            Richard Moore Master

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

                            • 11. Re: SQL Chunk with writer table truncate
                              Richard Moore Master

                              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?

                              • 12. Re: SQL Chunk with writer table truncate
                                Cheng Fang Master

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

                                • 13. Re: SQL Chunk with writer table truncate
                                  Cheng Fang Master

                                  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

                                  • 14. Re: SQL Chunk with writer table truncate
                                    Richard Moore Master

                                    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]

                                    1 2 Previous Next