Row
Error Logging in Informatica
Informatica
Inbuilt Error Logging feature can be utilized to implement Row Error logging
in a central location. When a row level error occurs, the Integration service
logs the error information which can be used to determine the cause and source of
the error.
These row level errors can be captured either in a relational table or in a flat file. When the
error logging is enabled, the Integration service creates the error table or error
log file the first time when it runs the session. If the error table or
error log file exists already, then the error data will be appended.
Following
are the activities that need to be performed to implement the Informatica Row
Error Logging:
1.
In the “Config object” tab of
“Error Handling” option , set the
“Error Log type” attribute to “Relational database” or “Flat File”.
By Default error logging is disabled.
2.
SET Stop On Errors = 1
3.
If the Error Log Type is set to “Relational”, specify the Database
connection & Table Name Prefix
Following are the tables which will be created by Integration service and which will be populated as and when the error occurs.
PMERR_DATA
Stores
data and metadata about a transformation row error and its corresponding source
row.
PMERR_MSG
Stores
metadata about an error and the error message.
PMERR_SESS
Stores
metadata about the session.
PMERR_TRANS
Stores
metadata about the source and transformation ports, such as name and datatype,
when a transformation error occurs.
4.
If the Error Log Type is set to “Flatfile”, specify the “Error log
file directory” and “Error log file name”
Database
Error Messages and the Error messages that Integration service writes to Bad
File/ Reject file can also be captured and stored in the Error log tables /
Flat files.
Following
are the few database error messages which will be logged in the Error Log
Tables / Flat files.
|
Error Messages
|
|
Cannot Insert the value
NULL into column ‘<<Column name>>’, table
‘<<Table_name>>’
|
|
Violation of PRIMARY
KEY constraint ‘<<Primary key constraint name>>’
|
|
Violation of UNIQUE KEY
constraint ‘<<Unique Key Constraint>>’
|
|
Cannot Insert Duplicate
key in object ‘<<Table_name>>’
|
Row
Error Logging Implementation
Advantages
Since the Informatica Inbuilt feature
is leveraged, the Error log information would be very accurate with very
minimal development effort.
Drawback
Enabling Error logging will have an
impact to performance, since the integration service processes one row at a
time instead of block of rows i.e. row-level processing as opposed to block
processing. In order to overcome this there is a another approach which I will explain
in another post.
Additional Info:
DDL for the creating Informatica Error Tables:
__________________________________________________________
PMERR_MSG:
CREATE TABLE PMERR_MSG
(
REPOSITORY_GID
VARCHAR2(240 ),
WORKFLOW_RUN_ID NUMBER(15, 0) NOT NULL,
WORKLET_RUN_ID NUMBER(15, 0),
SESS_INST_ID NUMBER(15, 0) NOT NULL,
TRANS_MAPPLET_INST VARCHAR2(240 ),
TRANS_NAME VARCHAR2(240 ) NOT NULL,
TRANS_GROUP VARCHAR2(240 ),
TRANS_PART_INDEX NUMBER(15, 0),
TRANS_ROW_ID NUMBER(15, 0) NOT NULL,
ERROR_SEQ_NUM NUMBER(15, 0) NOT NULL,
ERROR_TIMESTAMP TIMESTAMP(6),
ERROR_UTC_TIME NUMBER(15, 0),
ERROR_CODE NUMBER(15, 0),
ERROR_MSG VARCHAR2(2000 ),
ERROR_TYPE NUMBER(15, 0),
LINE_NO NUMBER(15, 0) NOT NULL,
CONSTRAINT PMERR_MSG_PK PRIMARY KEY
(
WORKFLOW_RUN_ID,
SESS_INST_ID,
TRANS_NAME,
TRANS_ROW_ID,
ERROR_SEQ_NUM,
LINE_NO
)
)
__________________________________________________________
PMERR_SESS:
CREATE TABLE PMERR_SESS
(
REPOSITORY_GID
VARCHAR2(240 ),
WORKFLOW_RUN_ID NUMBER(15, 0) NOT NULL,
WORKLET_RUN_ID NUMBER(15, 0),
SESS_INST_ID NUMBER(15, 0) NOT NULL,
SESS_START_TIME TIMESTAMP(6),
SESS_UTC_TIME NUMBER(15, 0),
REPOSITORY_NAME VARCHAR2(240 ),
FOLDER_NAME VARCHAR2(240 ),
WORKFLOW_NAME VARCHAR2(240 ),
TASK_INST_PATH VARCHAR2(240 ),
MAPPING_NAME VARCHAR2(240 ),
LINE_NO NUMBER(15, 0) NOT NULL,
CONSTRAINT PMERR_SESS_PK PRIMARY KEY
(
WORKFLOW_RUN_ID,
SESS_INST_ID,
LINE_NO
)
)
PMERR_TRANS:
CREATE TABLE PMERR_TRANS
(
REPOSITORY_GID
VARCHAR2(240 ),
WORKFLOW_RUN_ID NUMBER(15, 0) NOT NULL,
WORKLET_RUN_ID NUMBER(15, 0),
SESS_INST_ID NUMBER(15, 0) NOT NULL,
TRANS_MAPPLET_INST VARCHAR2(240 ),
TRANS_NAME VARCHAR2(240 ) NOT NULL,
TRANS_GROUP VARCHAR2(240 ),
TRANS_ATTR VARCHAR2(2000 ),
SRC_MAPPLET_INST VARCHAR2(240 ),
SOURCE_NAME VARCHAR2(240 ),
SOURCE_ATTR VARCHAR2(2000 ),
LINE_NO NUMBER(15, 0) NOT NULL,
CONSTRAINT PMERR_TRANS_PK PRIMARY KEY
(
WORKFLOW_RUN_ID,
SESS_INST_ID,
TRANS_NAME,
LINE_NO
)
)
__________________________________________________________
PMERR_DATA:
CREATE TABLE PMERR_DATA
(
REPOSITORY_GID
VARCHAR2(240 ),
WORKFLOW_RUN_ID NUMBER(15, 0) NOT NULL,
WORKLET_RUN_ID NUMBER(15, 0),
SESS_INST_ID NUMBER(15, 0) NOT NULL,
TRANS_MAPPLET_INST VARCHAR2(240 ),
TRANS_NAME VARCHAR2(240 ) NOT NULL,
TRANS_GROUP VARCHAR2(240 ),
TRANS_PART_INDEX NUMBER(15, 0),
TRANS_ROW_ID NUMBER(15, 0) NOT NULL,
TRANS_ROWDATA VARCHAR2(2000 ),
SOURCE_ROW_ID NUMBER(15, 0),
SOURCE_ROW_TYPE NUMBER(15, 0),
SOURCE_ROW_DATA VARCHAR2(2000 ),
LINE_NO NUMBER(15, 0) NOT NULL,
CONSTRAINT PMERR_DATA_PK PRIMARY KEY
(
WORKFLOW_RUN_ID,
SESS_INST_ID,
TRANS_NAME,
TRANS_ROW_ID,
LINE_NO
)
)
__________________________________________________________
No comments:
Post a Comment