Compare 76 inbound fields with 76 fields of table

[jayram99]

Hi,

When i tried to compare the Inbound record with the existing record(table) for 4 fields(primary key), I could retrieve the record.

Actually I have 74 inbound fields which has to be compared with Existing Columns(Table), I am getting Deadlock condition if i use the DB2 Stage as lookup.

Which is the best process to compare 76 fields from file to a table.

Thanks in Advance.

Jayaram

[jenkinsrob]

Hi Jayaram,

Perform the lookup based on your 4 key fields and read all of the fields (Source and Lookup) into your job.

Concatenate all the fields from your source record together into a stage variable (remembering to handle NULLS on each field). Then use the crc32 or checksum function on your concatenated string.

Do the same for your existing record and then compare the values of your 2 stage variables. If they do not match then something between the 2 records has changed.

[kumar_s]

Hi Jayaram,

May i know what is excat approach you follow now?
Is that the 4 field from source need to look up for the 76 columns in db2 table?
Hi jenkinsrob , will crc32 help in this case.
I guess checksum will be helpful if the lookup key as well as the key to lookup should be of same length and type. I guess in this case lookup key as many combination of 4 columns.

-Kumar

[ray.wurlod]

If you need to be able to report specific differences, the best approach is to use 74 stage variables. Plus a few more stage variables to assemble the diagnostic message(s).

[jayram99]

Ray,

When i tried to use Stage Variable field, getting the result. For the fields having spaces in both inbound field and also space in DB2 Table, I couldn't match.

I am using COALESCE(Field,' ') to convert for a null value but the field is already having space, so i am getting space.

For the inbound record I am using IsNull Function.

Ex. If IsNull(Field) then ' ' else Field. I am getting Space.

If i compare the two fields they are not matching.

Could you Pls. guide me to resovle this issue.

Thanks & Regards,
Jayaram

[vmcburney]

If the table you are comparing is also the table you are loading here is a good design: cleanse, compare then load. Clean your data in a transformer, compare it in another transformer and then load the inserts and updates. You want cleansed data in your DB2 table. You don't want to have to guess whether a null is a blank or an empty string.

If your DB2 data is currently a mismatch of different cleansing decision, such as empties and nulls and blanks, try to standardise it to one method and then use that method in your DataStage job. You may need to do some tidying up before this process works.

[ray.wurlod]

Can't help you without knowing precisely what values are coming in and precisely how you want to compare them. DataStage does not have a COALESCE function (though you could write one, but it would need to have a fixed nujmber of arguments).

How about posting the clear specification of your comparison requirement?

[jayram99]

Hi,

I am comparing the Inbound Data to an Table.

Initially I am converting the Inbound Data as

If the Field is Character
If IsNull(Input.Div13AccountNo) then ' ' else Input.Div13AccountNo and I am doing for all Character Fields

If the Field is Numeric
If IsNull(Input.ProfitUnit) then 0 else Input.ProfitUnit and I am doing for all the Numberic Fields.

In DB2 Stage i am using COALESCE Function as below.

SELECT CUSTDBA.TPOL_CLNT_ADDR.ASCO_NO,
CUSTDBA.TPOL_CLNT_ADDR.POLICY_NO,
CUSTDBA.TPOL_CLNT_ADDR.POL_INCEPT_DATE,
CUSTDBA.TPOL_CLNT_ADDR.CLIENT_PARTY_ID,
CUSTDBA.TPOL_CLNT_ADDR.TRANSACTION_SQN,
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.POLICY_PREFIX_CD,' '),
CUSTDBA.TPOL_CLNT_ADDR.POLICY_EXP_DATE,
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.MODULE_NO,0),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.PRIOR_POL_PRFX_CD,' '),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.PREVIOUS_POL_1_NO,' '),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.PRIOR_MODULE_NO,0),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.CONTRACT_NO,' '),
CUSTDBA.TPOL_CLNT_ADDR.CONTRACT_EFF_DT,
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.ACCOUNT_NO,' '),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.LOB_CD,' '),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.MAJOR_CLASS_CD,0),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.DIVISION_NO,0),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.SECTION_NO,0),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.PUC_CD,0),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.DIRECT_BILL_CD,' '),
CUSTDBA.TPOL_CLNT_ADDR.DATA_SOURCE_ID,
CUSTDBA.TPOL_CLNT_ADDR.SOURCE_SYSTEM_ID,
CUSTDBA.TPOL_CLNT_ADDR.TRANSACTION_CD,
CUSTDBA.TPOL_CLNT_ADDR.TRANSACTION_EFF_DT,
CUSTDBA.TPOL_CLNT_ADDR.POL_STATUS_CD,
CUSTDBA.TPOL_CLNT_ADDR.POLICY_STAT_EFF_TS,CUSTDBA.TPOL_CLNT_ADDR.TAX_ID_TYPE,
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.FEIN_NO,' '),
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.DUN_BRADSTREET_NO,' '),
CUSTDBA.TPOL_CLNT_ADDR.INSURED_NAME,CUSTDBA.TPOL_CLNT_ADDR.INSRD_NM_ADDL_INFO,
COALESCE(CUSTDBA.TPOL_CLNT_ADDR.NAME_BLOK_VRBL_TX,' '),
CUSTDBA.TPOL_CLNT_ADDR.NAME_BLOK_VRBL2_TX,CUSTDBA.TPOL_CLNT_ADDR.NAME_BLOK_VRBL3_TX,CUSTDBA.TPOL_CLNT_ADDR.NAME_HASH_KEY_ID,CUSTDBA.TPOL_CLNT_ADDR.NAME_SUFFIX_TX,CUSTDBA.TPOL_CLNT_ADDR.NAME_WORD_1_TX,CUSTDBA.TPOL_CLNT_ADDR.NAME_WORD_2_TX,CUSTDBA.TPOL_CLNT_ADDR.NAME_WORD_3_TX,CUSTDBA.TPOL_CLNT_ADDR.NAME_WORD_4_TX,CUSTDBA.TPOL_CLNT_ADDR.NAME_WORD_5_TX,CUSTDBA.TPOL_CLNT_ADDR.NAME_WORD_CT,CUSTDBA.TPOL_CLNT_ADDR.INSRD_ADDRESS_SQN,CUSTDBA.TPOL_CLNT_ADDR.INSRD_LINE_1_ADDR,CUSTDBA.TPOL_CLNT_ADDR.INSRD_LINE_2_ADDR,CUSTDBA.TPOL_CLNT_ADDR.INSRD_LINE_3_ADDR,CUSTDBA.TPOL_CLNT_ADDR.INSRD_CITY_TOWN_NM,CUSTDBA.TPOL_CLNT_ADDR.INSRD_STATE_CD,CUSTDBA.TPOL_CLNT_ADDR.INSRD_POSTAL_CD,CUSTDBA.TPOL_CLNT_ADDR.INSRD_COUNTRY_CD,CUSTDBA.TPOL_CLNT_ADDR.BILL_ADDRESS_SQN,CUSTDBA.TPOL_CLNT_ADDR.BILL_LINE_1_ADDR,CUSTDBA.TPOL_CLNT_ADDR.BILL_LINE_2_ADDR,CUSTDBA.TPOL_CLNT_ADDR.BILL_LINE_3_ADDR,CUSTDBA.TPOL_CLNT_ADDR.BILL_CITY_TOWN_NM,CUSTDBA.TPOL_CLNT_ADDR.BILL_STATE_CD,CUSTDBA.TPOL_CLNT_ADDR.BILL_POSTAL_CD,CUSTDBA.TPOL_CLNT_ADDR.BILL_COUNTRY_CD,CUSTDBA.TPOL_CLNT_ADDR.ADDR_BLOK_VRBL_TX,CUSTDBA.TPOL_CLNT_ADDR.HS_NO_BLOK_VRBL_TX,CUSTDBA.TPOL_CLNT_ADDR.BOX_BLOK_VRBL_TX,CUSTDBA.TPOL_CLNT_ADDR.CITY_BLOK_VRBL_TX,CUSTDBA.TPOL_CLNT_ADDR.ZIP3_BLOK_VRBL_TX,CUSTDBA.TPOL_CLNT_ADDR.RURL_ROUTE_VRBL_TX,CUSTDBA.TPOL_CLNT_ADDR.HOUSE_NO_SUFFX_TX,CUSTDBA.TPOL_CLNT_ADDR.STREET_PRFX_DIR_TX,CUSTDBA.TPOL_CLNT_ADDR.STREET_NM,CUSTDBA.TPOL_CLNT_ADDR.STRT_SUFFX_QUAL_TX,CUSTDBA.TPOL_CLNT_ADDR.STRT_SUFFX_DIR_TX,CUSTDBA.TPOL_CLNT_ADDR.FLOOR_VALUE_TX,CUSTDBA.TPOL_CLNT_ADDR.CREATE_USER_ID,CUSTDBA.TPOL_CLNT_ADDR.CREATE_TS,CUSTDBA.TPOL_CLNT_ADDR.UPDATE_USER_ID,CUSTDBA.TPOL_CLNT_ADDR.UPDATE_TS,CUSTDBA.TPOL_CLNT_ADDR.SAP_INSD_NM_EFF_DT,CUSTDBA.TPOL_CLNT_ADDR.SAP_INSD_NM_EXP_DT,CUSTDBA.TPOL_CLNT_ADDR.STREET_TYPE_TX,CUSTDBA.TPOL_CLNT_ADDR.UNIT_VALUE_TX FROM CUSTDBA.TPOL_CLNT_ADDR WHERE CUSTDBA.TPOL_CLNT_ADDR.ASCO_NO=? AND CUSTDBA.TPOL_CLNT_ADDR.POLICY_NO=? AND CUSTDBA.TPOL_CLNT_ADDR.POL_INCEPT_DATE=? AND CUSTDBA.TPOL_CLNT_ADDR.CLIENT_PARTY_ID=?;


In Stage Variable , I am comparing by triming as below.

If ((Format.ExpirationDate = Tpol_clnt_addr.POLICY_EXP_DATE) and
(trim(Format.PolicyPrefix) = trim(Tpol_clnt_addr.POLICY_PREFIX_CD)) and
(trim(Format.PriorPolicyNo) = trim(Tpol_clnt_addr.PREVIOUS_POL_1_NO)) and
(trim(Format.PriorPolicyPrefix) = trim(Tpol_clnt_addr.PRIOR_POL_PRFX_CD)) and
(trim(Format.PriorPolicyVersion) = trim(Tpol_clnt_addr.PRIOR_MODULE_NO)) and
(trim(Format.ContractNo) = trim(Tpol_clnt_addr.CONTRACT_NO)) and
(trim(Format.Div13AccountNo) = trim(Tpol_clnt_addr.ACCOUNT_NO)) and
(trim(Format.PolicyLineofBusiness) = trim(Tpol_clnt_addr.LOB_CD)) and
(trim(Format.MajorClass) = trim(Tpol_clnt_addr.MAJOR_CLASS_CD)) and
(trim(Format.Division) = trim(Tpol_clnt_addr.DIVISION_NO)) and
(trim(Format.Section) = trim(Tpol_clnt_addr.SECTION_NO)) and
(trim(Format.ProfitUnit) = trim(Tpol_clnt_addr.PUC_CD)) and
(trim(Format.DirectBillIndicator) = trim(Tpol_clnt_addr.DIRECT_BILL_CD)) and
(trim(Format.PolicyVersion) = trim(Tpol_clnt_addr.MODULE_NO))) then 1 else 0


I could match the field but the processing is very slow. I don't want to use COALESCE Function while doing query.

Pls. guide me for an altenate method. Can i use the Len Function in both File and Table.

Jayaram