Informatica and TPT

Tools & Utilities
Enthusiast

Informatica and TPT

The application team experienced a deadlock issue.

There were three different jobs running:  Job A was blocked by Job B.  Job B was blocked by Job A.  Job C was blocked by both Job A and Job B.

When checking viewpoint, I see that the point of contention was on the background processing that Informatica does for TPT... creating and dropping temporary views.

Here are the SQL and BLOCKED BY statements.

For session 78296:

DROP VIEW PM_VCRL4GLVCLNFS75S7QHXQH4TLJM;

For session 78297:

CREATE VIEW PM_VQT3MDELLGH2OPVSH4X7DH6SZ4A (ALRT_CD, ALRT_RESPNS_TYP_CD, SYS_FUNC_CD, DLVRY_SYS_CD, UPDKEY_EFF_STRT_TS, UPD_END_TS) AS

    SELECT                    ALRT_CD,

        ALRT_RESPNS_TYP_CD,

        SYS_FUNC_CD,

        DLVRY_SYS_CD,

        UPDKEY_EFF_STRT_TS,

        UPD_END_TS

        FROM tIDWLoadReady.LR_ALRT_RESPNS

        WHERE INS_UPD_FLG ='U';

For session 78318:

CREATE VIEW PM_VMCHC5HG56ARRQNO7I6IRLJOUHU (ACCT_ID, EFF_STRT_TS, EFF_END_TS, ACCT_TYP_CD, ACCT_NBR, SRC_LAST_UPDTD_TS, SRC_SYS_IDN_CD, ABC_ID, DATA_QLTY_CATG_CD) AS

    SELECT  ACCT_ID,

        EFF_STRT_TS,

        EFF_END_TS,

        ACCT_TYP_CD,

        ACCT_NBR,

        SRC_LAST_UPDTD_TS,

        SRC_SYS_IDN_CD,

        ABC_ID,

        DATA_QLTY_CATG_CD

        FROM tIDWLoadReady. LR_ACCT_ALS;

What causes this state and what can be done to prevent it?

1 REPLY
Enthusiast

Re: Informatica and TPT

This is quite a very comon problem seen many times. If you are using 13.10 or earlier, and using the same user for multiple jobs which are trying to write into the same database (can be other tables also), this is a more common deadlock that occurs and fails the jobs. This is because of the PI of the dbc.accessrights table being on 2 columns userid and databaseid. Usually the error tables and log tables for the TPT job will be created on the same database and while doing so it will lock this table. Hence the issue. These should not be on the system for a long time and will be completing soon. Ideally there should not be an issue/failure due to this(only blocking would be seen). To avoid this create multiple users/change the location of the error/log tables to other DB's.