Common Issues For ETL Testing

1.    Unique constraint violation error occurs.

Issue Details: While running a job, if job gets failed and in workflow session logs unique constraint violation error occurs.

Trouble Shooting:

Ø  Check the records in the corresponding table and delete the violated record.

Ø  Check the run date mentioned in the transfer control table and try incrementing it.

2.    Table or view does not exist issue.

Issue Details: While running a job if job gets failed and in session log we got an error like table or view does not exist.

Trouble Shooting:

Ø  Check the profile file for the corresponding Autosys job.

Ø  Change the values accordingly for corresponding phase of testing respective to correct servers.

Ø  Check the connection string in power center

Ø  Change the source and target Servers/DBs accordingly

Ø  If above two things are updated, Login to the corresponding server with BL ids used for the phase of testing and verify the table and view there.

3.    Error related to integration service.

Issue Details: While running a job if job gets failed and in session log we got an error related to integration service.

Trouble Shooting:

Ø  Check the profile file for the corresponding Autosys job.

Ø  Check for the correct repository of Power center corresponding to job.

Ø  Verify that the Integration service should be updated correctly.

Ø  Open the Power Center Monitor and verify that the workflow folder should be available under the same Integration service.

4.    Batch launched id access issue.

Issue Details: While running a job if job gets failed and in logs, Batch launched id access issue is occurred.

Trouble Shooting:

Ø  Check the access of the BL id for corresponding server/Database.

Ø  If not, get the access of the same Server/DB for Batch Launcher id.

Ø  If the id is accessible, check in the profile file if it has the right database/server name.

 

5.    Record counts mismatch issue

Issue Details: If during record counts check in source and target if it’s mismatched in large account.

Trouble Shooting:

Ø  Check the load type, if its full load this is an issue and raise the defect.

Ø  If its incremental load, check the source extract time and stop extract time, Change the timestamp in the parameter table and re run the job.

Ø  Check the no of processed rows and the no of loaded rows in the load summary of the session log.

6.    Workflow and session logs generation issue.

Issue Details: If Autosys job got failed and no workflow or session logs got generated.

Trouble Shooting:

Ø  Check the JIL source of the job: In Command line of JIL source, Hyphen ‘–‘anddot ‘.’ Should be placed at appropriate position.

Ø  Check if the profile files and the connections strings are pointing to the right databases/servers.

7.    Data loading issue for Full load.

Issue Details: If after running Autosys job data is not loaded at target side and in logs in the load summary section there is 0 extraction and transformation for Full load.

Trouble Shooting:

Ø  Check in the source table, there should be data in the source table. There is also a possibility that the source is having the older data than the cut of data in the control table or the last processed time stamp.

Ø  If data is loading from STG to DW side, in the main STG table data should be present.

Ø  Check the max load date in the DW table and process date in the stage table; if it is already matching then increment the process date.

8.    Data loading issue for Full load.

Issue Details: If after running AutoSys job data is not loaded at target side and in logs in the load summary section there is 0 extraction and transformation for Incremental load.

Trouble Shooting:

Ø  Check the transfer parameter entries for source and stop extract time and then check the same in the logs. Time period for extraction of data load to till data load should be corrected in the transfer parameter table.

9.    Incremental Job failure.

Issue Details: If Autosys Incremental Job got failed.

Trouble Shooting:

Ø  Check the transfer parameter table and check the parameter values corresponding to the incremental Job.

10.Autosys Job got failed which don’t have work flow.

Issue Details: If the AutoSys Job which don’t have workflow got failed.

Trouble Shooting:

Ø  Check the AutoSys logs in .err file. If the job got failed file size of .err becomes none zero byte and if the job got succeeded vice versa.

11.  AutoSys Job failure during flat file data loading.

Issue Details: If the job got failed when loading data from flat files to table.

Trouble Shooting:

Ø  Check the AutoSys logs and catch the error from .err file.

Ø  If the issue is related with files for example: invalid file or any other issues found, run the gunzip –t filename command where the file is placed. It will return the exact error for that file.

12.  Data comparison issue in large extent.

Issue Details: During data comparison for source and target if large number of differences are found in DB comparator result.

Trouble Shooting:

Ø  Check the metadata columns at the target end and remove those columns from the target end query of DB comparator.

Ø  Check the order by in the both queries and modify the queries with proper order by clause by using primary or unique key in both source and target end.

Ø  Remove the timestamps from the comparison rule as they are intercepted differently by the Sybase and the oracle database.

13.  Box Job failure.

Issue Details: If box job got failed.

Trouble Shooting:

Ø  Check all the sub jobs under box jobs.

Ø  Pick the failed job and check the session logs for that job and check for the above issues if any.

14.  Box Job running for long time issue.

Issue Details: If the box jobs keep running for long time.

Resolution:

Ø  Verify that there should not be any job under Box job should be on hold status.

Ø  Change the status of the on hold sub job to off hold and trigger the box job.

Ø  Put the failed sub-jobs on ice if it’s not a mandatory/critical dependent job

15.  Workflow monitors issue.

Issue Details: If not able to see the workflow status after running job in workflow monitor, getting error while opening.

Trouble shooting:

There is a network down reason issue and testing team need to contact to support team for Informatica Power Center.

Reference: NA

Posted in: ETL Testing