Test Data Manager

Hi,

I am facing a strange issue with Db2DataReader for bulk copy records. My requirement is put BulkCopy in a try-catch block with a while loop to retry bulk copy for maximum 3 times incase of DB2 timeout errors.

My Code looks gud but when I am executing it.

In 1st try, DB2DataReader throws an error (Say DataRead successfully but failed to write in Stage table due to DDL Mismatch). My code jumps to Catch block and loops back to try bulk copy

In 2nd try, DB2DataReader is able read source data but javelin is skipping Copy to Stage step and making this block as success and come out. Ideally it should keep throwing same error in all 3 tries and code should come-out after 3rd try

Here screenshot of my code attached

Log::

Running flow...
2017-08-11 15:37:11 - Run flow: abc.vwf
2017-08-11 15:37:11 - Running Test Case: Flowchart
2017-08-11 15:37:12 - Executing Step: LogActivity
2017-08-11 15:37:12 - Querying TABLE1 for 1 time(s)

2017-08-11 15:37:12 - Executing Step: DB2ReadSrcData
2017-08-11 15:37:12 - Executing Step: Db2CopyToStg
2017-08-11 15:37:12 - Error executing Db2CopyToStg: There are more source columns the there are at the destination table.
2017-08-11 15:37:12 - Executing Step: Db2TruncateStgTbl
2017-08-11 15:37:12 - Connecting to DB2 database...
2017-08-11 15:37:12 - Executing query: TRUNCATE TABLE TDMTEMP1450.TABLE1
2017-08-11 15:37:12 - Executing Step: LogActivity
2017-08-11 15:37:12 - Querying TABLE1 for 2 time(s)

2017-08-11 15:37:12 - Executing Step: DB2ReadSrcData
2017-08-11 15:37:12 - Running Test Case: Flowchart
2017-08-11 15:37:12 - Test Case: Flowchart Execution Complete
Execution complete

I would need to see the whole flowchart, your image cuts off much of it which may be relevant.

Thanks,

Ben

Hi prince.munjal, are you able to provide the whole flowchart like Ben requested?

I am unable to find a way to attach file while reply. Shall I start over a new thread ?

Hi prince.munjal, if you click on 'Use advanced editor', you should have an option to attach the file. 

If you still cannot attach, please open a support case on support.ca.com and we can continue troubleshooting from there.

I didn't get that option in IE browser.

but here I have found an option to upload and independent file

https://communities.ca.com/docs/DOC-231176448-db2datareadervwf

Can you pls have a look.

I am open if there is a different way of implementing this scenario. But we really have to knock out

Hi, yes, we will review this file and get back to you by tomorrow EOD. 

So I looked through the file, of course, I don't have your DB2 instance, or in fact any DB2 instance so I was limited in what I could do (I am also having a colleague look into this in the meantime):

1. 

I noticed that you were setting V_Retry to the max (plus some number) tries, rather than incrementing it like so:

2. I also noticed that with the Db2CopyToStg activity, you hadn't mapped any columns? Was this intended?:

3. I assume all of the variables were blanked out for security/privacy purposes, fair enough.

Regards,

Ben

Hi Ben,

Here is my logic.

Initially V_RETRY=0 and MAX_RETRY=3

Loop 1 (0 < 3) : If DB2DataReader success, then V_RETRY will set to 0+100 so that it can come out of loop

                          if DB2DataReader throws exception (like time out, data constrains or something), then code will jump to catch block. In catch block, I am truncating target table and incrementing V_RETRY

Incase of failure Loop 2 ( 1<3 ) : It should execute DB2Reader and process in loop until 3 times

Incase of success loop 2 (100 < 3) : Log should print 'Retry Count : 100'

Map columns doesn't required to do if source and target definitions are same. I have implemented it same way in all Bulk Data readers.

I have tweak my code for testing. I have made target DDL with an additional column so that Data reader should give me error saying 'Target has more columns then source' . Since its a definitive error, I should get it all three time. But during 2nd loop, DB2 Data Reader is just reading data but not trying to put it in target.

Hi Prince, 

Thank you for the further details. I have discussed this more with our Presales team and we really need a WebEx to continue troubleshooting. Can you please open a support case? Then we can setup a call. 

You can open a support case by going to Contact CA Support - CA Technologies . 

Best regards,

Taylor

Hi,

Here is the Case Id 00816383

Thank you for opening a support case. Our support engineers will reach out to you to setup a WebEx. 

So definitely the data reader not being reset or disposed of.

Could we scope the variable to just inside the While?

I think that might work

I have changed scope but that didn't help. I think it requires DB2DataReader block for 3 times in code as mentioned in above screenshot.

Can you try Assigning the DataReader variable to null, at the top of each loop iteration:

So as it turns out, null in vb.net is = "nothing"

So you use the Assign block, and set it to 

nothing

Regards,
Ben

I tried ben already. It didn't worked.

Regards,

Prince Munjal

To summarize issue for future readers:

Prince was trying to repeatedly retry a bulk copy action within Javelin, I.e. it might occasionally fail, so he placed it within a try-catch block, and encase that within a while loop. When the bulk copy was successful, he’d increment a counter (controlling the while loop) to above the value necessary to quit the while loop, and when it wasn’t successful, he incremented the ‘RetryCount’ by one.

The trouble is, when the IDataReader within the bulk copy failed for a second time, it would always totally exit the while loop. It seems it wasn’t being properly disposed and re-initialised or something similar.

Prince got around this by building an if-else structure, ensuring each loop iteration would result in a new IDataReader being used, which hadn’t been used before, which avoided the disposal problem, (or so I suspect). You can see what I mean below

This is just a workaround and we are working with development on a better solution. 

A patch was provided for this issue. If you experience the same, please open a support case and we can evaluate if the patch is applicable for your environment as well.