I am running a task in workflow. I have 2 sources and I am able to fetch the no of records from it. But, unable to insert into target table. All the data types are fine.
I have attached the session log for the same.
I've double checked all the data types in all the transformations.
here's the mapping logic,
Source 1 \
Source 2 > EXP / JNR > UPD > TGT
Could anyone please point me out what I'm doing wrong?
Severity Timestamp Node Thread Message Code Message
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6014 Initializing session [s_m_MASTRA_MARKET_COVERAGE_LOAD] at [Tue Dec 17 09:50:22 2019].
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6683 Repository Name: [TMnD_CRM_Prod]
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6684 Server Name: [Integration_TMnD_CRM_Prod]
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6686 Folder: [mASTRA_NEW]
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6685 Workflow: [wf_MASTRA_MARKET_COVERAGE] Run Instance Name: [] Run Id: [397741]
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6101 Mapping name: m_MASTRA_MARKET_COVERAGE_LOAD.
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6964 Date format for the Session is [MM/DD/YYYY HH24:MI:SS.US]
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6708 Using configuration property [EnableDataEncryption,no]
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6708 Using configuration property [StoreHAPersistenceInDB,no]
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR TM_6703 Session [s_m_MASTRA_MARKET_COVERAGE_LOAD] is run by 64-bit Integration Service [node01_prdeim], version [10.2.0 HotFix1], build [0819].
INFO 17-12-2019 09:50:22 AM node01_prdeim MANAGER PETL_24091 Thread [MANAGER] has the ID [1].
INFO 17-12-2019 09:50:22 AM node01_prdeim MANAGER PETL_24058 Running Partition Group [1].
INFO 17-12-2019 09:50:22 AM node01_prdeim MANAGER PETL_24000 Parallel Pipeline Engine initializing.
INFO 17-12-2019 09:50:22 AM node01_prdeim MONITOR PETL_24091 Thread [MONITOR] has the ID [2].
INFO 17-12-2019 09:50:22 AM node01_prdeim MANAGER PETL_24001 Parallel Pipeline Engine running.
INFO 17-12-2019 09:50:22 AM node01_prdeim MANAGER PETL_24003 Initializing session run.
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING PETL_24091 Thread [MAPPING] has the ID [3].
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING CMN_1569 Server Mode: [UNICODE]
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING CMN_1570 Server Code page: [ISO 8859-1 Western European]
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TM_6151 The session sort order is [Binary].
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TM_6156 Using low precision processing.
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TM_6180 Deadlock retry logic will not be implemented.
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TM_6187 Session target-based commit interval is [10000].
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TM_6307 DTM error log disabled.
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TE_7022 TShmWriter: Initialized
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING DBG_21694 jnr_MARKET_COVER_ORG_EXT Partition [0]: Index cache size = [178956970], Data cache size = [357913940]
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TT_11098 The Joiner transformation [jnr_MARKET_COVER_ORG_EXT] is configured with the join algorithm: Nested-Loop.
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TM_6007 DTM initialized successfully for session [s_m_MASTRA_MARKET_COVERAGE_LOAD]
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR PETL_24033 All DTM Connection Info: [<NONE>].
INFO 17-12-2019 09:50:22 AM node01_prdeim MANAGER PETL_24004 PETL_24004 Starting pre-session tasks. : (Tue Dec 17 09:50:22 2019)
INFO 17-12-2019 09:50:22 AM node01_prdeim PRE-SESS PETL_24091 Thread [PRE-SESS] has the ID [4].
INFO 17-12-2019 09:50:22 AM node01_prdeim MANAGER PETL_24027 PETL_24027 Pre-session task completed successfully. : (Tue Dec 17 09:50:22 2019)
INFO 17-12-2019 09:50:22 AM node01_prdeim DIRECTOR PETL_24006 Starting data movement.
INFO 17-12-2019 09:50:22 AM node01_prdeim MAPPING TM_6660 Total Buffer Pool size is 7298376 bytes and Block size is 268088 bytes.
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_1_1 PETL_24091 Thread [READER_1_1_1] has the ID [5].
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_1_1 DBG_21438 Reader: Source is [OLTPSMIS], user [SIEBEL]
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_1_1 BLKR_16051 Source database connection [OLTPSMIS] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_1_1 BLKR_16003 Initialization completed successfully.
INFO 17-12-2019 09:50:22 AM node01_prdeim TRANSF_1_1_1 PETL_24091 Thread [TRANSF_1_1_1] has the ID [6].
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_2_1 PETL_24091 Thread [READER_1_2_1] has the ID [7].
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_2_1 DBG_21438 Reader: Source is [OLAPSMIS], user [tableau]
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_2_1 BLKR_16051 Source database connection [TABLEAU] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_2_1 BLKR_16003 Initialization completed successfully.
INFO 17-12-2019 09:50:22 AM node01_prdeim TRANSF_1_2_1 PETL_24091 Thread [TRANSF_1_2_1] has the ID [8].
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 PETL_24091 Thread [WRITER_1_*_1] has the ID [9].
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8147 Writer: Target is database [MASTRAPROD], user [mastra], bulk mode [OFF]
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8221 Target database connection [mAstra] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8124 Target Table MASTRA_MARKET_COVERAGE :SQL INSERT statement:
INSERT INTO MASTRA_MARKET_COVERAGE(MARKET_ID,MARKET_NAME,WDCODE,POP_GROUP,DISTRICT,SUB_DISTRICT,PLAN_FLAG,ACTUAL_FLAG,TRANSDATE) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8124 Target Table MASTRA_MARKET_COVERAGE :SQL UPDATE statement:
UPDATE MASTRA_MARKET_COVERAGE SET POP_GROUP = ?, DISTRICT = ?, SUB_DISTRICT = ?, PLAN_FLAG = ?, ACTUAL_FLAG = ?, TRANSDATE = ? WHERE MARKET_ID = ? AND MARKET_NAME = ? AND WDCODE = ?
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8124 Target Table MASTRA_MARKET_COVERAGE :SQL DELETE statement:
DELETE FROM MASTRA_MARKET_COVERAGE WHERE MARKET_ID = ? AND MARKET_NAME = ? AND WDCODE = ?
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8270 Target connection group #1 consists of target(s) [MASTRA_MARKET_COVERAGE]
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8003 Writer initialization complete.
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_1_1 BLKR_16007 Reader run started.
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_2_1 BLKR_16007 Reader run started.
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_1_1 RR_4029 SQ Instance [SQ_S_ORG_EXT] User specified SQL Query [select wd.integration_id,mkt.name from s_org_ext wd,s_region mkt,s_Synd_Data b where wd.row_id=b.ou_ext_id and B.AREA_ID=mkt.row_id and mkt.type_cd = 'BASE GOI']
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_1_1 RR_4049 RR_4049 SQL Query issued to database : (Tue Dec 17 09:50:22 2019)
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_2_1 RR_4029 SQ Instance [SQ_MAKKET_SERVICE_MODE_MASTER] User specified SQL Query [select a.BRANCH_CODE BRANCH,
a.BASE_GOI_MARKET_ID MARKET_ID,
a.BASE_GOI_MARKET_NAME MARKET_NAME,
(case when a.BASE_GOI_POP_GROUP in ('2-5','<2') then '<5k'
when a.BASE_GOI_POP_GROUP in ('10-20') then '10-20k'
when a.BASE_GOI_POP_GROUP in ('5-10') then '5-10k'
when a.BASE_GOI_POP_GROUP in ('>1000','500-1000','100-500','51-100','21-50') then '21-50k'
else a.BASE_GOI_POP_GROUP end )POP_GROUP,
b.X_DISTRICT DISTRICT,b.X_SUB_DISTRICT SUB_DISTRICT,a.TYPE,
'Y' PLAN_FLG from TABLEAU.MAKKET_SERVICE_MODE_MASTER A, olap.w_regn_d b
where TO_CHAR(a.BASE_GOI_MARKET_ID)=b.X_MARKET_CODE
and A.PLAN_FLG is not null]
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8005 Writer run started.
INFO 17-12-2019 09:50:22 AM node01_prdeim READER_1_2_1 RR_4049 RR_4049 SQL Query issued to database : (Tue Dec 17 09:50:22 2019)
INFO 17-12-2019 09:50:22 AM node01_prdeim WRITER_1_*_1 WRT_8158
*****START LOAD SESSION*****
Load Start Time: Tue Dec 17 09:50:22 2019
Target tables:
MASTRA_MARKET_COVERAGE
INFO 17-12-2019 09:50:23 AM node01_prdeim READER_1_2_1 RR_4050 RR_4050 First row returned from database to reader : (Tue Dec 17 09:50:23 2019)
INFO 17-12-2019 09:50:23 AM node01_prdeim TRANSF_1_2_1 DBG_21077 DBG_21077 Create joiner cache on master relation : (Tue Dec 17 09:50:23 2019)
INFO 17-12-2019 09:50:23 AM node01_prdeim TRANSF_1_2_1 TE_7212 Increasing [Index Cache] size for transformation [jnr_MARKET_COVER_ORG_EXT] from [178956970] to [178985984].
INFO 17-12-2019 09:50:23 AM node01_prdeim TRANSF_1_2_1 TE_7212 Increasing [Data Cache] size for transformation [jnr_MARKET_COVER_ORG_EXT] from [357913940] to [357915648].
INFO 17-12-2019 09:50:23 AM node01_prdeim TRANSF_1_2_1 CMN_1690 Created new data file [/infa714/Informatica_Linux/server/infa_shared/Cache/PMJNR6767_5_0_397741.dat] and index file [/infa714/Informatica_Linux/server/infa_shared/Cache/PMJNR6767_5_0_397741.idx] for Joiner [jnr_MARKET_COVER_ORG_EXT].
INFO 17-12-2019 09:50:23 AM node01_prdeim READER_1_1_1 RR_4050 RR_4050 First row returned from database to reader : (Tue Dec 17 09:50:23 2019)
INFO 17-12-2019 09:50:23 AM node01_prdeim READER_1_2_1 BLKR_16019 Read [67277] rows, read [0] error rows for source table [MAKKET_SERVICE_MODE_MASTER] instance name [MAKKET_SERVICE_MODE_MASTER]
INFO 17-12-2019 09:50:23 AM node01_prdeim READER_1_2_1 BLKR_16008 Reader run completed.
INFO 17-12-2019 09:50:23 AM node01_prdeim TRANSF_1_2_1 DBG_21214 DBG_21214 Finished joiner cache on master relation : (Tue Dec 17 09:50:23 2019)
INFO 17-12-2019 09:50:23 AM node01_prdeim TRANSF_1_2_1 DBG_21216 Finished transformations for Source Qualifier [SQ_MAKKET_SERVICE_MODE_MASTER]. Total errors [0]
INFO 17-12-2019 09:50:23 AM node01_prdeim TRANSF_1_1_1 DBG_21603 DBG_21603 Open master relation cache for detail joiner : (Tue Dec 17 09:50:23 2019)
INFO 17-12-2019 09:50:32 AM node01_prdeim READER_1_1_1 BLKR_16019 Read [621046] rows, read [0] error rows for source table [S_ORG_EXT] instance name [S_ORG_EXT]
INFO 17-12-2019 09:50:32 AM node01_prdeim TRANSF_1_1_1 DBG_21215 DBG_21215 Finished processing detail relation : (Tue Dec 17 09:50:32 2019)
INFO 17-12-2019 09:50:32 AM node01_prdeim TRANSF_1_1_1 CMN_1795 The index cache size that would hold [67277] input rows from the master for [jnr_MARKET_COVER_ORG_EXT], in memory, is [52736] bytes
INFO 17-12-2019 09:50:32 AM node01_prdeim TRANSF_1_1_1 CMN_1794 The data cache size that would hold [67277] input rows from the master for [jnr_MARKET_COVER_ORG_EXT], in memory, is [206674944] bytes
INFO 17-12-2019 09:50:32 AM node01_prdeim TRANSF_1_1_1 DBG_21216 Finished transformations for Source Qualifier [SQ_S_ORG_EXT]. Total errors [0]
INFO 17-12-2019 09:50:32 AM node01_prdeim WRITER_1_*_1 WRT_8167 Start loading table [MASTRA_MARKET_COVERAGE] at: Tue Dec 17 09:50:31 2019
INFO 17-12-2019 09:50:32 AM node01_prdeim WRITER_1_*_1 WRT_8168 End loading table [MASTRA_MARKET_COVERAGE] at: Tue Dec 17 09:50:31 2019
INFO 17-12-2019 09:50:32 AM node01_prdeim WRITER_1_*_1 WRT_8035 Load complete time: Tue Dec 17 09:50:31 2019
LOAD SUMMARY
============
WRT_8036 Target: MASTRA_MARKET_COVERAGE (Instance Name: [MASTRA_MARKET_COVERAGE])
WRT_8044 No data loaded for this target
INFO 17-12-2019 09:50:32 AM node01_prdeim WRITER_1_*_1 WRT_8043 *****END LOAD SESSION*****
INFO 17-12-2019 09:50:32 AM node01_prdeim READER_1_1_1 BLKR_16008 Reader run completed.
INFO 17-12-2019 09:50:32 AM node01_prdeim WRITER_1_*_1 WRT_8006 Writer run completed.
INFO 17-12-2019 09:50:32 AM node01_prdeim MANAGER PETL_24031
***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****
Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_S_ORG_EXT] has completed.
Total Run Time = [9.725447] secs
Total Idle Time = [0.000000] secs
Busy Percentage = [100.000000]
Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [SQ_S_ORG_EXT] has completed.
Total Run Time = [8.698872] secs
Total Idle Time = [8.597273] secs
Busy Percentage = [1.167953]
Transformation-specific statistics for this thread were not accurate enough to report.
Thread [READER_1_2_1] created for [the read stage] of partition point [SQ_MAKKET_SERVICE_MODE_MASTER] has completed.
Total Run Time = [1.042087] secs
Total Idle Time = [0.000000] secs
Busy Percentage = [100.000000]
Thread [TRANSF_1_2_1] created for [the transformation stage] of partition point [SQ_MAKKET_SERVICE_MODE_MASTER] has completed.
Total Run Time = [0.687992] secs
Total Idle Time = [0.527125] secs
Busy Percentage = [23.382139]
Transformation-specific statistics for this thread were not accurate enough to report.
Thread [WRITER_1_*_1] created for [the write stage] of partition point [MASTRA_MARKET_COVERAGE] has completed. The total run time was insufficient for any meaningful statistics.
INFO 17-12-2019 09:50:32 AM node01_prdeim MANAGER PETL_24005 PETL_24005 Starting post-session tasks. : (Tue Dec 17 09:50:32 2019)
INFO 17-12-2019 09:50:32 AM node01_prdeim POST-SESS PETL_24091 Thread [POST-SESS] has the ID [10].
INFO 17-12-2019 09:50:32 AM node01_prdeim MANAGER PETL_24029 PETL_24029 Post-session task completed successfully. : (Tue Dec 17 09:50:32 2019)
INFO 17-12-2019 09:50:32 AM node01_prdeim MAPPING TM_6018 The session completed with [0] row transformation errors.
INFO 17-12-2019 09:50:32 AM node01_prdeim MANAGER TE_7216 Deleting cache files [/infa714/Informatica_Linux/server/infa_shared/Cache/PMJNR6767_5_0_397741.dat] for transformation [jnr_MARKET_COVER_ORG_EXT].
INFO 17-12-2019 09:50:32 AM node01_prdeim MANAGER TE_7216 Deleting cache files [/infa714/Informatica_Linux/server/infa_shared/Cache/PMJNR6767_5_0_397741.idx] for transformation [jnr_MARKET_COVER_ORG_EXT].
INFO 17-12-2019 09:50:32 AM node01_prdeim MANAGER PETL_24002 Parallel Pipeline Engine finished.
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR PETL_24012 Session run completed successfully.
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR TM_6022
SESSION LOAD SUMMARY
================================================
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR TM_6252 Source Load Summary.
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR CMN_1740 Table: [SQ_S_ORG_EXT] (Instance Name: [SQ_S_ORG_EXT])
Output Rows [621046], Affected Rows [621046], Applied Rows [621046], Rejected Rows [0]
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR CMN_1740 Table: [SQ_MAKKET_SERVICE_MODE_MASTER] (Instance Name: [SQ_MAKKET_SERVICE_MODE_MASTER])
Output Rows [67277], Affected Rows [67277], Applied Rows [67277], Rejected Rows [0]
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR TM_6253 Target Load Summary.
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR CMN_1740 Table: [MASTRA_MARKET_COVERAGE] (Instance Name: [MASTRA_MARKET_COVERAGE])
Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR TM_6023
===================================================
INFO 17-12-2019 09:50:32 AM node01_prdeim DIRECTOR TM_6020 Session [s_m_MASTRA_MARKET_COVERAGE_LOAD] completed at [Tue Dec 17 09:50:32 2019].
I can see two possibilities - 1. May be main reason - Joiner is not producing any data. Have you cheked when you join Source 1 and Source 2 they are joined on correct key column(s). chances are the data from both system arent matching. Sometime there can be space/special characters in the data and they can cause mismatch. Debug to figure out the reason. 2. If above reason is not the cause, please check if you set one or more columns as primary key in target. This helps on update part.