0

how to specify no of records to delete in Tibco JDBC Update activity in batch update mode.

Actually I need to delete 25 million of records from the database so I wrote Tibco code to do the same and it is taking lot of time .. So I am planning to use Batch mode in Delete query so I don't know how to specify no of records in JDBC Update activity.

Help me if any one has any idea.. thanks

Raj
  • 1
  • 1
  • 1
  • 2

3 Answers3

1

From the docs for the Batch Update checkbox:

This field is only meaningful if there are prepared parameters in the SQL statement (see Prepared Parameters).

In which case the input will be an array of records. It will execute the statement once for each record.

To avoid running out of memory, you will still need to iterate over the 25mil, but you can iterate in groups of 1000 or 10000.

If this is not something you would do often (deleting 25M rows, sounds pretty one-off), an alternative is to use BW to create a file containing the delete statements and then giving the file to a DBA to execute.

Tom Howard
  • 6,516
  • 35
  • 58
  • Thanks Tom for your reply. I did the same but the problem is it is taking long time. I will tell my BW design ..please review it and tell me the suggetsion to improve it. – Raj May 03 '13 at 16:58
  • Hi Raj. I've added an alternative that might be viable, if it isn't something that will be done very often. – Tom Howard May 06 '13 at 05:39
0

please use subset feature of jdbc palette!! Let me know if you face any issues?

mahesh
  • 274
  • 2
  • 4
  • 20
0

I would suggest two points:

  1. If this is an one time activity then it is not adviced to use Tibco BW code for that. SQL script should be the better alternative.

  2. When you say 25 million records- what criteria is this based on. It can be achieved through subset iteration .But there should be proper load testing in the Pre - Prod environment to check that the process is not causing any memory/DB issue.

You can also try using SQL procedure and invoking the same through BW.

Saurav Kumar
  • 11
  • 5