Tech Monger

Often you need information about session runs which you can obtain by getsessionstatistics command of informatica pmcmd however to get selective information you need to rely on Informatica Monitor. Extracting information manually from Power Center Client can be tedious and not practical. In such cases you can query infromatica repository database and fetch required details like success rows, failed rows, error code, total errors, associated mappings, bad file location, log file location, session runs time etc. Informatica stores these details inside view REP_SESS_LOG. Below are the some of the useful columns from this view.

REP_SESS_LOG

SUBJECT_AREA

WORKFLOW_NAME

MAPPING_NAME

SESSION_NAME

SUCCESSFUL_SOURCE_ROWS

FAILED_SOURCE_ROWS

SUCCESSFUL_ROWS

FAILED_ROWS

TOTAL_ERR

BAD_FILE_LOCATION

SESSION_LOG_FILE

ACTUAL_START

Informatica Basic Session Information Queries

Find failed workflow sessions from specific folder

SELECT * FROM REP_SESS_LOG 
WHERE RUN_STATUS_CODE != 1 AND 
SUBJECT_AREA='Folder Name';

Find the location of Bad File generated by specific workflow or session

SELECT BAD_FILE_LOCATION FROM REP_SESS_LOG
WHERE WORKFLOW_NAME='WORKFLOW NAME';

SELECT BAD_FILE_LOCATION FROM REP_SESS_LOG
WHERE SESSION_NAME='SESSION NAME';

Find the location of Session Log File generated by specific session

SELECT SESSION_LOG_FILE FROM REP_SESS_LOG
WHERE SESSION_NAME='SESSION NAME';

Find Success and Failed Rows of Specific Run by Session, Workflow or Mapping

SELECT SUCCESSFUL_ROWS, FAILED_ROWS FROM REP_SESS_LOG
WHERE SESSION_NAME='SESSION NAME';

SELECT SUCCESSFUL_ROWS, FAILED_ROWS FROM REP_SESS_LOG
WHERE WORKFLOW_NAME='WORKFLOW NAME';

SELECT SUCCESSFUL_ROWS, FAILED_ROWS FROM REP_SESS_LOG
WHERE MAPPING_NAME='MAPPING NAME';

Find Success and Failed Source Rows of Specific Run by Session, Workflow or Mapping

SELECT SUCCESSFUL_SOURCE_ROWS, FAILED_SOURCE_ROWS FROM REP_SESS_LOG
WHERE SESSION_NAME='SESSION NAME';

SELECT SUCCESSFUL_SOURCE_ROWS, FAILED_SOURCE_ROWS FROM REP_SESS_LOG
WHERE WORKFLOW_NAME='WORKFLOW NAME';

SELECT SUCCESSFUL_SOURCE_ROWS, FAILED_SOURCE_ROWS FROM REP_SESS_LOG
WHERE MAPPING_NAME='MAPPING NAME';

Total number of Errors encountered during session run

SELECT TOTAL_ERR FROM REP_SESS_LOG
WHERE SESSION_NAME='SESSION NAME';

List of session / workflow runs between specific dates.

SELECT * FROM REP_SESS_LOG 
WHERE ACTUAL_START BETWEEN TO_DATE('01/JAN/2017','dd/mon/yyyy') AND
TO_DATE('31/JAN/2017','dd/mon/yyyy');

You can combine above query with first query (failure/success) to find list of sessions failed during specific dates.

Failed Runs

SELECT * FROM REP_SESS_LOG 
WHERE ACTUAL_START BETWEEN TO_DATE('01/JAN/2017','dd/mon/yyyy') AND
TO_DATE('31/JAN/2017','dd/mon/yyyy') AND
RUN_STATUS_CODE != 1;

Successful Runs

SELECT * FROM REP_SESS_LOG 
WHERE ACTUAL_START BETWEEN TO_DATE('01/JAN/2017','dd/mon/yyyy') AND
TO_DATE('31/JAN/2017','dd/mon/yyyy') AND
RUN_STATUS_CODE = 1;

Conclusion