top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to delete large number of rows without affecting Informatica replication?

+1 vote
487 views

I'm not exactly sure if it will cause an issue or not. But in our environment whenever selecting from large tables we need to include WITH(nolock) otherwise we get deadlocks between select statements and Informatica real time replication trying to populate those tables. Currently we are looking to use SQL jobs to purge old records from tables. Most tables will have less than 1000 rows deleted once a week. During initial purge process there will be several tables that will have millions of records deleted.

Not sure if it would be better to pause real time replication for duration of the purge or just to try to resolve conflicts once those arise.

I don't have a good way to test this in Dev environment because don't have same volume of people hitting server with request, real time updates and everything else that makes up normal load in production. In production this will be running once a week during slow hours that should minimize total load but still don't want to crash Informatica every time this is running.

Note: this question is NOT about how to write delete statements in SQL or breaking delete in small chunks but rather how to avoid conflicts with real time replication with Informatica product.

Database - SQL Server 2012
Replication - Informatica 9.0.1

posted May 20, 2014 by Rohini Agarwal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

How about escalating the lock for the time of purging? If you initially lock all pages needed (or set a table-level lock), perform the purge and then release the lock, no deadlocks shouls occure. And this will not require any updates for replication process.

answer May 21, 2014 by Shweta Singh
Similar Questions
0 votes

How to calculate number of rows in flat files in informatica.
As we are doing in Teradata as SEL COUNT (*) FROM ABC.ACCT.

0 votes

I have a requirement where I need to obtain the number of rows written to multiple targets in my mapping. There are 3 targets in my mapping (T1, T2 and T3). I need the number of rows written to each target separately. These values need to be used in subsequent sessions. I understand that there is a method where I can use separate counters and write them to a flat file and perform a lookup on this file in subsequent mappings. However, I am looking for a direct and better approach to this problem.

+2 votes

How can we delete duplicate rows from source database table using informatica. This has to be done without using any other table. Means source and target is same table in Oracle database.

+1 vote

I have data in table A as below

Assetid   attribute   value
    1546    Ins_date   05062011
    1546    status     active
    1546    X          10.4567
    1546    Y          27.56
    1546    size       17
    675     X          4.778
    675     Y          53.676
    675     depth      5
    675     st_date    06092010

I have data as above in table A. This table has many Assetids 1546,675,....etc. attributes might vary for assets.

I want output as below:

assetid  ins_date  status  X        Y       Size  depth  st_date
1546     05062011  active  10.4567  27.56   17    null   null
675      null      null    4.778    53.676  null  5      06092010

I have created Stored procedure, then called in Informatica to achieve this output. However, since i have large volume of data, it is taking much time to load.

Please suggest me other easy and best way to load it.

0 votes

Can anyone tell me how to find the list of mapping and the workflow that are using a particular table say X either as a source or target table?

...