InfoSphere Optim

 View Only
Expand all | Collapse all

Handling null,space values in LUA - Hash lookup

  • 1.  Handling null,space values in LUA - Hash lookup

    Posted Tue March 31, 2020 02:31 AM
    Edited by System Fri January 20, 2023 04:44 PM
    Hello Everyone,

    Hope everyone is staying hale and healthy in a dire situation like this.

    I would need your expertise to solve the below issue with using Hash lookup in LUA for masking first name and last name.
    There are rows with spaces for first name and Last name in the table that I'm trying to mask . In order to handle these spaces or nulls , I have already inserted the seq# values  -3,-2,-1 in the lookup tables. But still when the program encounters a row with spaces , it throws the following error.  

    "Attempted an Optim.mask request with an invalid or unspecified flddef length ".

    I even tried to map some random hash value in my LUA program instead of these  default values (-3,-2,-1). But that didnt work as well.

    Could you please help me on how to handle this scenario in LUA ?



    ------------------------------
    Hari
    ------------------------------

    #InfoSphereOptim
    #Optim


  • 2.  RE: Handling null,space values in LUA - Hash lookup

    Posted Tue March 31, 2020 11:21 AM
    We have seen ODPP choke on such values, so we have code that evaluates the value of the field prior to masking it. If the value is blank or null, then just leave it as-is and don't try to mask it and move to the next record.  


    ------------------------------
    Keith Tidball
    Progressive Insurance
    ------------------------------



  • 3.  RE: Handling null,space values in LUA - Hash lookup

    Posted Wed April 01, 2020 04:33 AM
    Thanks Keith for your response. I will have to go the same route.
    This option has worked for me in the past . So sought out your expertise  to ensure that I'm not missing anything here.

    ------------------------------
    Hariharan Paramasivam
    ------------------------------



  • 4.  RE: Handling null,space values in LUA - Hash lookup

    Posted Tue March 31, 2020 04:17 PM
    Hi Hari
    Try below lines under Source section ( specially line 3, 4 , 5)

    pro=hash_lookup, hashfld="seq",
    source="Source Column name",
    PRESERVENULL ="Source Column name",
    PRESERVESPACES ="Source Column name",
    PRESERVEZEROLENGTH ="Source Column name",
    rep="lookup table field",​

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



  • 5.  RE: Handling null,space values in LUA - Hash lookup

    Posted Wed April 01, 2020 04:16 AM
    Edited by System Fri January 20, 2023 04:09 PM
    Thanks Harshavardhan for your reply.  Yes I already did add all the three preserve parameters as part of the source columns. Still it is not taking those  spaces into account.

    ------------------------------
    Hari
    ------------------------------



  • 6.  RE: Handling null,space values in LUA - Hash lookup

    Posted Wed April 01, 2020 09:25 AM
    ​Hi Hari
    Try trim function

    str1 = optim.source.getcolumnvalue()
    str = str1:gsub(""%s+"", "" "")
    if (str ~= nil)
    then

    Your Lookup script

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



  • 7.  RE: Handling null,space values in LUA - Hash lookup

    Posted Thu April 02, 2020 07:17 AM
    Hello Hari,
    if you are seeing the Lua failing, you can use a Protected Call. 
    This will capture the error and you can determine how you want to handle the Column/Row being processed prior to the Lua Chunk failing. 
    I use this for various reasons to capture the err.code and err.message for evaluation of how to process the error
    here is a link for Lua Error Handling. 
    I hope this help, 
    Regards
    https://www.ibm.com/support/knowledgecenter/SSMLNW_11.3.0/com.ibm.nex.designer.doc/privacy/optdesdp-r-lua_error_handling.html



    ------------------------------
    Bruce Fischer
    ------------------------------



  • 8.  RE: Handling null,space values in LUA - Hash lookup

    Posted Wed April 08, 2020 02:10 AM
    Hello Bruce ,

    Thanks for your response. I tried to apply the pcall option earlier against the OPTIM.MASK function , where I tried to set the column a default value when the status is not true . The outcome was all the records got masked to the default value irrespective of whether the OPTIM.MASK function is a success or not. Is there an option for us to pass a default value when the OPTIM.MASK function fails to find a lookup value ? Please let me know.

    ------------------------------
    Hariharan Paramasivam
    ------------------------------



  • 9.  RE: Handling null,space values in LUA - Hash lookup

    Posted Wed April 08, 2020 07:56 AM

    Hello Hari,

    I have 2 examples in this reply.

    The first is using Optim Privacy Table OPTIM_US_PERSON.

    This example will preform a "LOOKUP" and if the value is not found, the default value "Default Value" would be used

    Good luck!

     

    Example #1

    function cm_transform()

    stat_parm = 'PRO=LOOKUP,SOURCE="SSN",SEARCH="SSN",'

    stat_parm = stat_parm .. 'DEST="FIRSTNAME",REPLACE="LASTNAME",'

    stat_parm = stat_parm .. 'LIB=DB2LUW,CONN=PRIVACY,'

    stat_parm = stat_parm .. 'ID="PRIVACY.OPTIM_US_PERSON",'

    stat_parm = stat_parm .. 'FLDDEF1=(NAME="SSN",dt=WVARCHAR_SZ),'

    stat_parm = stat_parm .. 'FLDDEF2=(NAME="FIRSTNAME",dt=WVARCHAR_SZ)'

    --stat_parm = stat_parm .. 'WHENNOTFOUND=PRESERVE'

    --stat_parm = stat_parm .. 'PRESERVENULL="FIRSTNAME"'

     

    iFname = optim.source.getcolumnvalue("FIRSTNAME")

    issn = optim.source.getcolumnvalue("SSN")

    print('SSN --> ' .. issn)

        

    rcstatus, maskValue1 =                                           

             pcall(optim.mask,issn,stat_parm)     

    if rcstatus == true then

     optim.target.setcolumnvalue(maskValue1)

    else 

        optim.target.setcolumnvalue("Default Value")

        print('value was nil')

    end

    end                              

     

    The second example is similar that a default value will be provided if the LOOKUP replacement is not found,

    However, this Lua Chunk will provide the Optim ODPP error.code and error.reason.

    Good luck,

     

    Example #2

    function cm_starttable()

    cntx = 0

    cnt_not_found = 0

    cnt_found = 0

    end

     

    function cm_transform()

    stat_parm = 'PRO=LOOKUP,SOURCE="xMASK_RULEID,PLAN_ID,xSYSTEM_CODE,GROUP_NUM,PLAN_NUM",'

    stat_parm = stat_parm ..'SEARCH="MASK_RULEID,PLAN_ID,SYSTEM_CODE,GROUP_NUM,PLAN_NUM",'

    stat_parm = stat_parm .. 'DEST="LONG_NAME",REPLACE="SHORT_NAME",'

    stat_parm = stat_parm .. 'LIB=DB2LUW,CONN=PRIVACY,'

    stat_parm = stat_parm .. 'ID="LKUP.REFRESH_MAPPING_PLAN_NAME",'

    stat_parm = stat_parm .. 'FLDDEF1=(NAME="xMASK_RULEID",dt=WVARCHAR_SZ),'

    stat_parm = stat_parm .. 'FLDDEF2=(NAME="PLAN_ID",dt=LONG_LONG),'

    stat_parm = stat_parm .. 'FLDDEF3=(NAME="xSYSTEM_CODE",dt=WVARCHAR_SZ),'

    stat_parm = stat_parm .. 'FLDDEF4=(NAME="GROUP_NUM",dt=WVARCHAR_SZ),'

    stat_parm = stat_parm .. 'FLDDEF5=(NAME="PLAN_NUM",dt=WVARCHAR_SZ),'

    stat_parm = stat_parm .. 'FLDDEF6=(NAME="LONG_NAME",dt=WVARCHAR_SZ)'

     

    cntx = cntx + 1

     

    --print(string.format("cntx --> %d",cntx))

    iMaskRuleID = 'SSDEMQ'

    --iMaskRuleID = optim.source.getcolumnvalue("MASK_RULEID")

    iSystemCD = 'VSYS'

    --iSystemCD = optim.source.getcolumnvalue("SYSTEM_CODE")

    iShortName = optim.source.getcolumnvalue("SHORT_NAME")

    iGroupNum = optim.source.getcolumnvalue("GROUP_NUM")

    iLongName = optim.source.getcolumnvalue("LONG_NAME")

    iPlanNum = optim.source.getcolumnvalue("PLAN_NUM")

    iPlanID = optim.source.getcolumnasdouble("PLAN_ID")

     

    rcstatus, maskValue1 =                                           

             pcall(optim.mask,iMaskRuleID,iPlanID,iSystemCD,iGroupNum,iPlanNum,stat_parm)     

    if rcstatus == true then

     --print('rcstatus = true')

    cnt_found = cnt_found + 1

     print(string.format("cnt_found default --> %d",cnt_found))

    --print(cntfound,string.format("cnt_found default --> %d",cnt_found))

    optim.target.setcolumnvalue("LONG_NAME",maskValue1)

    else 

        cnt_not_found =  cnt_not_found + 1

        process_error(maskValue1,"optim.mask(()")

        optim.print("**** optim.mask failed ****. See return values " ..          

                     "above.\n\n")                               

        optim.target.setcolumnvalue("Default Value")

        print(string.format("cnt_not_found --> %d",cnt_not_found))

    --    print('value was nil ' .. cntx1)

    end

     

    end 

     

    -- Function To Trim  Space characters                                  

    function all_trim(s)

       return s:match( "^%s*(.-)%s*$" )

    end      

     

    function process_error(error, funcname)

      print('in function')                                        

      colname = optim.target.getcolumnname()                                       

      -- optim.mask() error processor will report an error on row                  

      -- failure.                                                                   

      -- error.code   : set to 1 to indicate the "drop row condition"              

      -- error.reason : set to ODPP reason code (6030 for example                  

      --                when LOOKUP row not found)                                 

      -- error.message: for example: IOQDP0439E: Lookup data not found             

      --                                                                            

      -- Other potential errors may return other reason and message values.        

      --                                                                           

      if type(error) == "table" then

    --     print('1st if')                                              

        if error.code ~= nil then   

          print(string.format("%d",error.code) .. " " .. string.format("%d",error.reason))

          print('error message --> ' .. error.message)                                                 

        else                                                                       

          optim.print(string.format("The error code returned by"..                 

                     "function %s is nil.\n",funcname))                            

        end                                                                        

      else                                                                         

        optim.print(string.format("Function %s for column %s" ..                   

                  " returned the following"..                                      

                  " error: %s\n", funcname, colname, error))                       

      end                                                                          

    end                                                                            

           

     

     

     

     

     

     

     

    Bruce Fischer | Complex Data Privacy Specialist

    Information Management

    Phone +1(218)252-8726

    bfischer@abmartin.com

    image001.png@01D5362F.B31DB8D0

    1999 S. Bascom Ste 700

    Campbell, CA 95008

     

    Unstructured Data Masking

    Data Privacy/Test data Management - fabrication, optimization, obfuscation

     






  • 10.  RE: Handling null,space values in LUA - Hash lookup

    Posted Thu April 09, 2020 11:59 AM
    Thanks a lot for the examples Bruce.  The two examples by itself was unique and was enough for me to grasp the usage of PCALL function . Thanks again.

    ------------------------------
    Hariharan Paramasivam
    ------------------------------



  • 11.  RE: Handling null,space values in LUA - Hash lookup

    Posted Thu April 09, 2020 12:04 PM

    Hello Hari,

    Good luck,

     

     

     

     

    Bruce Fischer | Complex Data Privacy Specialist

    Information Management

    Phone +1(218)252-8726

    bfischer@abmartin.com

    image001.png@01D5362F.B31DB8D0

    1999 S. Bascom Ste 700

    Campbell, CA 95008

     

    Unstructured Data Masking

    Data Privacy/Test data Management - fabrication, optimization, obfuscation

     






  • 12.  RE: Handling null,space values in LUA - Hash lookup

    Posted Wed April 08, 2020 01:58 AM
    Thanks Harsha.. I did something on the similar lines.

    ------------------------------
    Hariharan Paramasivam
    ------------------------------