IN or EXISTS operators in Oracle, which is better?
EXISTS is faster than IN because, IN doesn’t use indexes at the time of fetching but EXISTS uses Index at the time of fetching.
Can you please tell me some achievements done by you in your project?
My greatest achievement was completing a challenging project before the given timeline while working as a Informatica Developer. I organized and motivated my team, resulting in a successful and timely project completion. This accomplishment showed my ability work as a team under tight deadlines.
Can you tell your project architecture?
In the current project we have got the requirement in the form of BR (Business Requests) and CR (change Requests). Based on BRs and CRs we have implemented the project.
My Current project is Wesco. Wesco is a major Electrical supplier in US.
In this Project, we have got the sources in the form of Tables and Files. We have loaded these table and files into Staging Area by using incremental loading using mapping variable.
From Staging area, we have loaded the data to Dimension tables by using SCD type-1 or Scd Type-2 based on the Business logic. From Dimensions tables, we have loaded the data to fact table.
My Roles and responsibilities in the current project are
Tell any 5 roles and responsibilities as per CV.
How does informatica tool works?
- Informatica in an ETL Tool, which is having Server tools and Client tools.
- Informatica PowerCenter can access the data from any database like oracle, SQL Server, Teradata also from flat files, Transform the data as per the business logic and writes the data to any Target Database as well as flat files.
- The Informatica instructs the integration service to execute the workflow. Then the integration service reads workflow details from the repository. The integration service starts the execution of the tasks inside the workflow.
What is the flow of development in your project?
- ETL Developers will do the mapping development in Dev Repository using the tables in Development (DEV) Database (Which have sample data).
- Once the mapping Development is completed, Developers will run the workflow and load the data to Dev Database tables.
- Also, Developers will Complete unit testing and move the code from Dev to Test Environment.
What is the difference between function and procedure in oracle?
Function:
- Function must always return a value.
- Function can be used in SQL statement.
- Function used for computational purpose.
- We cannot write directly DML statements in Function (Using Autonomous transaction we can write DML statement in Function).
- In function, we use RETURN keyword for return the value.
Procedure:
- Procedure cannot return value but using IN and INOUT parameter procedure can return the value.
- We cannot run procedure in SQL statement.
- Procedure is basically use for the business logic.
- In procedure, we can write DML statement.
- In procedure, we can use RETURN keyword also but the behaviour of RETURN keyword is change in the procedure. RETURN keyword means you want to exit to program.
In case of job failure in production whom will you report?
As a L2 Team member, if any job failed in Production, first we will try to restart the workflow. If it is still failed, we will check the session log and try to find out the error and will resolve it.
If any code change is required for any job failure, we will report to L3 Support team to do the code change.
How you troubleshooting when workflow is failed?
- If any workflow failed check the session log and try to find out the error and will resolve it. Check any Rows are rejected
- Check the session log and identify the Error, which is causing the rejection.
- Check the Target table for any kind of datatype issues or constraint issues or missing required fields etc.
- Validate the Rejected records in the source data to verify the reason for rejection.
Difference between session log and workflow log?
The PowerCenter Server can create log files for each workflow it runs. These files contain information about the tasks the PowerCenter Server performs, plus statistics about the workflow and all sessions in the workflow.
Session Log:
Session log mainly contains about the information about session.
It also Contains information about the tasks that the PowerCenter Server performs during a session, plus load summary and transformation statistics. By default, the PowerCenter Server creates one session log for each session it runs. If a workflow contains multiple sessions, the PowerCenter Server creates a separate session log for each session in the workflow.
Workflow Log:
Workflow log can be used to see the status of any failure tasks like email, command, Event wait and Event Raise.
It Contains information about the workflow run such as workflow name, tasks executed, and workflow errors. By default, the PowerCenter Server writes this information to the server log or Windows Event Log, depending on how you configure the PowerCenter Server. If you wish to create a workflow log, enter a workflow file name in the workflow properties.
Do you know about migration?
Yes, I have migrated / Deployed the informatica code from Dev to Test Environment.
How to rectify Source bottleneck?
1. Optimizing the Query:
If a session joins multiple source tables in one Source Qualifier, you might be able to improve performance by optimizing the query with optimizing hints.
The database administrator (DBA) can create optimizer hints to tell the database how to execute the query for a particular set of source tables.
2. Increasing Database Network Packet Size:
If you read from Oracle, Sybase ASE, or Microsoft SQL Server sources, you can improve the performance by increasing the network packet size. Increase the network packet size to allow larger packets of data to cross the network at one time.
For more information about bottlenecks, pls read the below link.
How to find second row in a file using UNIX command?
head -2 filename | tail -1
How we will get to know whether previous command executed in a UNIX shell script is successful or not?
By using $? .
$? special variable expands to the exit status of the last executed command
How you worked in sprint basis or what is the sprint time?
We are using Agile methodology in my project. Our project is divided into different sprints. Each Sprint is having a time period of 2 weeks. Scrum master will assign the task in Jira and should be competed with in a sprint.
Leave a comment