InfoSphere Optim

 View Only

  • 1.  Long execution time of lookup function with error messages "Access Violation" in trace file

    Posted Wed March 03, 2021 04:27 PM
    Edited by System Fri January 20, 2023 04:22 PM
    Welcome, colleagues of OPTIM TDM!

    When using the loopup function (https://www.ibm.com/support/knowledgecenter/SSMLQ4_11.3.0/com.ibm.nex.designer.doc/privacy/opdesdp-r-lookup_function.html)
    I see a strange situation. I will describe it. I am using windows version of client and server Optim 11.3.0.8.
    When using the lookup function to mask data, an error message is generated in the trace file for each record of the source file:
    ERRWERRH ERR_RetryMgr (0248) EXCP 03/03/2021 20:04:17 000046 00001830 00001968
    Win32 Exception=C0000005 Desc=Access Violation
    ERRWERRH ERR_RetryMgr (0000) ENVE 03/03/2021 20:04:17 000047 00001830 00001968
    Exception:C0000005 (Access Violation) at 0000000005002FA4
    Exception occured on 03/03/2021 at 20:04:17
    PN0COL00 colccmpr.c COLCmprExec(01542-00012FA4)
    Registers at Failure:
    CS=0033 DS=002B ES=002B FS=0053 GS=002B SS=002B
    RIP=0000000005002FA4 RAX=000000004A82406C RBX=0000000003807B98 RCX=00000000075A29A8
    RDX=0000000000000008 RBP=0000000000000000 RDI=000000000353F5C8 RSI=00000000075A2424
    RSP=0000000006B832D0 FLG=00010202 PID=00001830 TID=00001968
    Current Stack Len:118064 Attempt to Read at Address 000000004A82406C
    PN0ERR00 errwstak.c ERR_StakMap(00243-00008DD3)
    PN0ERR00 errwerrh.c ERR_RetryMgr(00368-00008A49)
    PN0COL00 colccmpr.c COLCmprExec$filt$0(?????-001DA453)
    MSVCR90 Unknown Unknown(?????-00055610)
    ntdll Unknown Unknown(?????-000A35AA)
    ntdll Unknown Unknown(?????-00004AAA)
    ntdll Unknown Unknown(?????-000A2319)
    PN0COL00 colccmpr.c COLCmprExec(01542-00012F9F)
    PN0COL00 colctdnd.c COL_TdSrchNode(01348-000F4C92)
    PN0COL00 colctdnd.c COL_TdSrchTree(01838-000F707C)
    PN0COL00 colctdnd.c COL_TdGetByKey(00868-000F2EC6)
    PN0COL00 colctrnx.c COLCMTransformExec(03867-0014A8C9)
    PN0XFM00 xfmcrow.c XFMIntGetRow(02485-00041EB8)
    PN0XFM00 xfmcrow.c XFMIntGet(02045-00040A3A)
    PN0XFM00 xfmcrow.c XFMGetByRRN(00580-00038A9F)
    PR0XCNV pswrxcnv.cpp XcnvWorker DoConvert(04059-000312E0)
    PR0XCNV pswrxcnv.cpp XcnvWorker DoWork(02041-000284D4)
    PR0RCCI rtrccsvr.cpp RtRccWorkerThread Create(01132-00070B5A)
    PR0CTLS rtthread.cpp RtThread InitInstance(01710-001FB03D)
    mfc90u Unknown Unknown(?????-0006D8D4)
    MSVCR90 Unknown Unknown(?????-00002FDA)
    MSVCR90 Unknown Unknown(?????-0000307B)
    KERNEL32 Unknown Unknown(?????-00017E8F)
    ntdll Unknown Unknown(?????-00067ACC)
    COLCmprExec Stack Frame Length:16
    0000000006B832D0 000000 4033B806 00000000 60F35303 00000000 @3�.....`�S.....
    ERR_StakMap Stack Frame Length:16
    0000000006B832E0 000000 08000000 00000000 00000000 00000000 ................
    ERRWERRH ERR_RetryMgr (0248) EXCP 03/03/2021 20:04:17 000048 00001830 00001968

    The text of the function is simple, I use the template offered by Optim Designer:
    LOOKUP (DICT1_CD, DEST = (ADDRESS), OPTIM2DB.OPTIMDICT.OPTIM_UA_ADDRESS (SEQ, VALUES = (ADDRESS1)))

    After completion of the conversion service in the console, a message about successful completion, in the Service Summary report,
    as well as all messages about successful work:

    Process status: 0 Errors 0 Warnings 0 Information 0 Procedure / Exit

    Total tables 1
    Rows extracted 410
    Rows in error 0
    Rows converted 410

    When viewing the target XF file, the target ADDRESS field is set to a new value from the OPTIM2DB.OPTIMDICT.OPTIM_UA_ADDRESS lookup table.

    With a larger number of records, 12000 records, the trace file has a size of more than 150MB and a runtime of 15 minutes.

    On the same configuration, when using the hash_lookup function, the field masking conversion service works much faster,
    80 thousand records are processed in 90 seconds. There are no error messages in the trace file for hash_lookup.

    Share your experience of using the lookup function for masking data, maybe there are some peculiarities of its use?

    Respectfully,
    Valentine

    ------------------------------
    Valentin Sukhanyuk
    ------------------------------
    #InfoSphereOptim
    #Optim


  • 2.  RE: Long execution time of lookup function with error messages "Access Violation" in trace file

    Posted Thu March 04, 2021 02:13 AM
    Hi Valentin,

    What type of column is DICT1_CD? Is it similar like SEQ in the lookup-table?

    Regards,
    Fritz

    Friedrich Pfneisl 
    Client Technical Specialist
    Data and AI-Information Architecture

    Phone: +43 664 6186302
    E-mail:     pfneisl@at.ibm.com






    Original Message


  • 3.  RE: Long execution time of lookup function with error messages "Access Violation" in trace file

    Posted Thu March 04, 2021 02:42 AM
    Hi Fritz!

    The data type of the DICT1_CD and SEQ columns is NUMBER.

    Respectfully,
    Valentine


    ------------------------------
    Valentin Sukhanyuk
    ------------------------------

    Original Message


  • 4.  RE: Long execution time of lookup function with error messages "Access Violation" in trace file

    Posted Thu March 04, 2021 02:51 AM
    Hi Valentin,

    Is an index defined on both columns?

    Regards,
    Fritz




    Original Message


  • 5.  RE: Long execution time of lookup function with error messages "Access Violation" in trace file

    Posted Thu March 04, 2021 03:02 AM
    Hi Fritz!

    The index is set for the SEQ column. Column DICT1_CD, no index.


    Respectfully,
    Valentine

    ------------------------------
    Valentin Sukhanyuk
    ------------------------------

    Original Message


  • 6.  RE: Long execution time of lookup function with error messages "Access Violation" in trace file

    Posted Thu March 04, 2021 10:29 AM
    Hi Valentin,
    This happens when the Database which holds Lookup tables is down (even for a moment).
    Another instance is, when your job reaches internal memory maximum utilization, as your job holds SEED reference for each record.
    Try splitting job into at least two or three and try it will work.

    ------------------------------
    Harshavardhan Peddireddy
    ------------------------------

    Original Message


  • 7.  RE: Long execution time of lookup function with error messages "Access Violation" in trace file

    Posted Thu March 04, 2021 01:00 PM
    Hi, Harshavardhan Peddireddy!

    In the extraction service, in the selection conditions, set rownum = 1.     Executed the conversion service, there is no error in the trace file.

    I change the selection condition to rownum <= 2, perform the conversion service, in the trace file 4 error messages, "Access Violation".

    How can the job be split? Did you mean to reduce the number of records in the original file?

     The lookup reference table was hosted on the DB2 and Oraсle DB server. In each case, there was an error in the trace file.

    Is it possible to adjust the size of the OPTIM task stack? Windows task manager reports 4.3GB of free memory.


    Respectfully,
    Valentine

    ------------------------------
    Valentin Sukhanyuk
    ------------------------------

    Original Message


  • 8.  RE: Long execution time of lookup function with error messages "Access Violation" in trace file

    Posted Thu March 04, 2021 07:37 PM
    Hi Valentin,

    Instead of ROWNUM, use Where conditions to slit the volume into separate Extract files. Then apply convert on each Extract files.

    Here you have some ROWNUM known issues readthrough ..
    https://oraclefrontovik.com/2014/05/16/using-oracle-sql-doesnt-rownum-2-work/
    https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results

    ------------------------------
    Harshavardhan Peddireddy
    ------------------------------

    Original Message