8 Replies Latest reply on Jan 21, 2021 11:11 AM by Nico Heinze

    How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?

    user151564 New Member

      AS-IS: We use Dynamic Lookups to check if records exist in the target tables (Db2) before trying to insert them. We use Bulk load because sometimes there are millions of records.

       

      Problem: Sometimes we get a Deadlock because the Lookup Cache has not been created and the Writer starts to load the traget table (and this requires a SUPERLOCK on the Db2 table using Bulk load ( Db2: LOAD INSERT) ).

       

      Strange thing: Even if we use ... where 1=2 ... in the Lookup SQL Override - this results in an empty LookupCache - (Yes, that's what we want!), the Writer starts the Load process before the Lookup Cache (with 0 records) has been created.

       

      In the majority of the cases we do NOT get Deadlock. It happens occasionally.

       

      So ... Is there a way to ensure that Dynamic Lookup Cache will be created BEFORE the Writer starts?

        • 1. Re: How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?
          David Lopez Cruz Guru

          Hi,

          Dynamic Lookup Cache is actually created when the session starts.

          Is it possible the lookup table is lock by any other user/process?

           

          Here you can read a Dymanic Lookup Cache example in 10.4.1 documentation:

          Dynamic Lookup Cache Example

           

          Regards,

          David

          • 2. Re: How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?
            user151564 New Member

            The lookup/target table is NOT locked by any other user/process. Definitely not. I can guarantee this.

             

            We've setup Db2 Event Monitor and we have seen :

            • the process requesting the LOCK was "SELECT query for the creation of the Lookup Cache"
            • the process holding LOCK on the table (SUPERLOCK in Db2 when using the LOAD utility) was the Writer process

             

            -------------------------------------------------------

            Event ID : 4519

            Event Type             : DEADLOCK

            Event Timestamp        : 2021-01-14-02.11.10.120421

            Partition of detection : 0

            -------------------------------------------------------

             

            Deadlock Graph

            --------------

            Total number of deadlock participants : 2

            Participant that was rolled back      : 1

            Type of deadlock : local

             

            ...

             

            Participant No 1 requesting lock

            ----------------------------------

            Lock Name            : 0x0A004000000000000000000054

            Lock wait start time : 2021-01-14-02.10.57.628083

            Lock wait end time   : 2021-01-14-02.11.10.120421

            Lock Type            : TABLE

            Lock Specifics       :

            Lock Attributes      : 00000000

            Lock mode requested  : Intent None

            Lock mode held       : Super Exclusive

            Lock Count           : 0

            Lock Hold Count      : 0

            Lock rrIID           : 0

            Lock Status          : Waiting

            Lock release flags   : 00000000

            Tablespace TID       : 10

            Tablespace Name      : ZUOADC

            Table FID            : 64

            Table Schema         : ZUOC_CL

            Table Name           : L_VEHICLESTATE_CONTROLUNIT

             

            Participant No 2 requesting lock

            ----------------------------------

            Lock Name            : 0x01000000FC7F0000E01A4B2DC3

            Lock wait start time : 2021-01-14-02.11.01.104635

            Lock wait end time   : 2021-01-14-02.11.10.120421

            Lock Type            : CATALOG

            Lock Specifics       : loadingID:00000000,loadingBytes:00007FFC 2D4B1AE0

            Lock Attributes      : 00000000

            Lock mode requested  : Exclusive

            Lock mode held       : Exclusive

            Lock Count           : 0

            Lock Hold Count      : 0

            Lock rrIID           : 0

            Lock Status          : Waiting

            Lock release flags   : 00000000

            Tablespace TID       :

            Tablespace Name      :

            Table FID            :

            Table Schema         :

            Table Name           :

             

            ...

             

            Current Activities of Participant No 1

            ----------------------------------------

            Activity ID : 0

            Uow ID             : 1

            Package Name       : SYSSH200

            Package Schema     : NULLID

            Package Version    :

            Package Token      : SYSLVL01

            Package Sectno     : 4

            Reopt value : none

            Incremental Bind   : no

            Eff isolation      : RR

            Eff degree         : 0

            Actual degree      : 0

            Eff locktimeout    : -2

            Stmt first use     :

            Stmt last use      :

            Stmt unicode       : no

            Stmt query ID      : 0

            Stmt nesting level : 0

            Stmt invocation ID : 0

            Stmt source ID     : 0

            Stmt pkgcache ID   : 0

            Stmt type          : Dynamic

            Stmt operation     : Statement not prepared

            Stmt no : -1

            Stmt text          : SELECT  distinct L_VEHICLESTATE_HID AS MD5KEY_HK1,  L_PART_CONTROLUNIT_HID AS MD5KEY_HK2 FROM ZUOC_IL.I_VEHICLE_VEHICLESTATE_CONTROLUNIT where exists (select 1 from ZUOC_CL.L_VEHICLESTATE_CONTROLUNIT z where z.VEHICLESTATE_CONTROLUNIT_LID = L_VEHICLESTATE_CONTROLUNIT_LID)  AND 1=2 ORDER BY MD5KEY_HK1,MD5KEY_HK2

             

            ...

             

            Current Activities of Participant No 2

            ----------------------------------------

            Activity ID : 0

            Uow ID             : 5

            Package Name       : SYSSH200

            Package Schema     : NULLID

            Package Version    :

            Package Token      : SYSLVL01

            Package Sectno     : 4

            Reopt value : none

            Incremental Bind   : no

            Eff isolation      : RR

            Eff degree         : 0

            Actual degree      : 0

            Eff locktimeout    : -2

            Stmt first use     :

            Stmt last use      :

            Stmt unicode       : no

            Stmt query ID      : 0

            Stmt nesting level : 0

            Stmt invocation ID : 0

            Stmt source ID     : 0

            Stmt pkgcache ID   : 0

            Stmt type          : Dynamic

            Stmt operation     : Statement not prepared

            Stmt no            : -1

            Stmt text          : INSERT INTO ZUOC_CL.L_VEHICLESTATE_CONTROLUNIT VALUES ( ?, ?, ?, ?, ?, ?, ?)

             

            Activity ID : 1

            Uow ID             : 4

            Package Name       : SYSSH200

            Package Schema     : NULLID

            Package Version    :

            Package Token      : SYSLVL01

            Package Sectno     : 0

            Reopt value : none

            Incremental Bind   : no

            Eff isolation      : RR

            Eff degree         : 1

            Actual degree      : 1

            Eff locktimeout    : 1800

            Stmt first use     : 2021-01-14-02.10.57.556247

            Stmt last use      : 2021-01-14-02.10.57.556247

            Stmt unicode       : no

            Stmt query ID      : 0

            Stmt nesting level : 0

            Stmt invocation ID : 0

            Stmt source ID     : 0

            Stmt pkgcache ID   : 0

            Stmt type : Static

            Stmt operation     : Statement not prepared

            Stmt no            : -1

            Stmt text          : INSERT INTO ZUOC_CL.L_VEHICLESTATE_CONTROLUNIT VALUES ( ?, ?, ?, ?, ?, ?, ?)

             

            Participant 1 ==> Lookup process

                 Lock wait start time : 2021-01-14-02.10.57.628083

             

            Participant 2 ==> Writer process

                 Lock wait start time : 2021-01-14-02.11.01.104635

                 Stmt first use     : 2021-01-14-02.10.57.556247

             

            "Stmt first use of the participant 2 (Writer) < "Lock wait start time of the Participant 1 (Lookup)"

            • 3. Re: How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?
              Nico Heinze Guru

              Funny idea (no idea whether this will work, I've never tried it myself):

              Assuming that a Dynamic Lookup cache can utilise a so-called "persistent cache file", you could create the cache file in another mapping running before your load mapping (or, to be exact, you can insert a new "target load path" in your current mapping where this lookup file is created and nothing else happens, just make sure in the "Target Load Plan" of the mapping that this new target load path is executed before the actual load starts). Then you can simply set up the dynamic lookup to re-use this cache file which has been produced before your actual load mapping starts.

               

              Regards,

              Nico

              • 4. Re: How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?
                user151564 New Member

                Assuming that a Dynamic Lookup cache can utilise a so-called "persistent cache file", you could create the cache file in another mapping running before your load mapping ... Then you can simply set up the dynamic lookup to re-use this cache file which has been produced before your actual load mapping starts.

                Nice!

                I'll try this.

                • 5. Re: How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?
                  Asmita Kesarwani Guru

                  Hi ,

                   

                  Please refer the below KB article which talks about a similar issue as your issue:

                   

                  Support

                   

                  Please let us know if this helps.

                   

                  Thanks,

                  Asmita

                  • 6. Re: How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?
                    user151564 New Member

                    inuser468357 THX! This workarround seems to work.

                     

                    I've tested it in our DEV envioronment.

                     

                    1. New (Additional) data flow in the mapping:
                      • Read 1 record from the source table (Source filter    1=1 fetch first 1 rows only )
                      • Dynamic Lookup lkp_PreLoad_Persistent_Cache
                        • Cache File Name Prefix = lkp_L_VEHICLESTATE_CONTROLUNIT
                          • this creates:
                            • lkp_L_VEHICLESTATE_CONTROLUNIT.dat0

                            • lkp_L_VEHICLESTATE_CONTROLUNIT.dat1

                            • lkp_L_VEHICLESTATE_CONTROLUNIT.idx0

                            • lkp_L_VEHICLESTATE_CONTROLUNIT.idx1

                            • lkp_L_VEHICLESTATE_CONTROLUNIT.lck

                      • DUMMY target
                    2. Old (Existing) data flow in the mapping:
                      • Dynamic Lookup lkp_Use_Persistent_Cache
                        • Cache File Name Prefix = lkp_L_VEHICLESTATE_CONTROLUNIT (same as above)
                        • Re-cache from lookup source = DEACTIVATED
                        • Session Log File says:
                          • Lookup Transformation lkp_Use_Persistent_Cache...
                          • Using existing cache files lkp_L_VEHICLESTATE_CONTROLUNIT.[dat/idx] in directory...

                     

                    So ... the workaround is fine. Thanks a lot!

                     

                     

                    I'm still interested in the "real" solution for this problem. From my point of view it could help to set:

                    • Session : Additional Concurrent Pipelines for Lookup Cache Creation = 0 (instead of the default setting Auto)
                    • Lookup : Pre-build lookup cache = Always allowed (instead of the default setting Auto)

                     

                    It's nearly impossible to test this, because the problem (DEADLOCK) occurs occasionally.

                    • 7. Re: How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?
                      user151564 New Member

                      Hello Asmita00u1ftyxp7tny4u3H1d8

                       

                      Thanks a lot!
                      This would not solve my problem, because our SQLOverride for the Dynamic Lookp Cache has 1=2 in the where clause already.

                      The problem is that - sometimes ! - the Writer process (Bulk load = LOAD INSERT) "starts" before the Lookup Cache has been created. And the LOAD Utility in DB2 sets SUPERLOCK on the target table. For this reason the LOOKUP Reader cannot read the target table ==> DEADLOCK.

                       

                      We have solved this problem for some other tables with this solution:

                      • SQ on Source Table + Lookup on <Target_table> + Bulk Load into <Target_table_TEMP>
                      • SQ on <Target_table_TEMP> + Bulk Load into <Target_table>

                      I don't want to reuse this workaround for multiple tables.

                       

                      I just thought that there might be a configuration which ensures that a Lookup Cache is created BEFORE the Target Writer process starts.

                      • 8. Re: How to ensure (finished) creation of the Lookup Cache before the Writer starts to load data into the target table?
                        Nico Heinze Guru

                        As far as I know, there's no such thing as an "easy workaround" for this behaviour because it depends on the DBMS. As of my understanding nothing that Informatica can cater for in a generic way.

                         

                        Regards,

                        Nico