Showing posts with label Bussiness Objects. Show all posts
Showing posts with label Bussiness Objects. Show all posts

Sunday, December 26, 2010

Display Odd/ Even number of records?


Display Odd/ Even number of records?

i:Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Output:-
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
Output:-
2
4
6

ii) for even number of records

select*from emp where rowid in(select decode(mod(rownum,2),0,rowid) from emp);


for odd number of records


select*from emp where rowid in(select decode(mod(rownum,2),1,rowid) from emp);

What is a cursor N Details

Question : What is a cursor?

Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

Cursor is a variable.
it is similar to a 2D array .
used for processing multiple rows.
used for storing data temporarily.
cursors is a private sql area and are 2 types implict cursor and explicit.implict cursor are predefined and explicit cursor defined by the programmer.

Question : What is use of a cursor variable? How it is defined?

A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. Acursor variable is reference type (like a pointer in C).
Declaring a cursor variable:
TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list
that will eventually be returned by the cursor variable



Question : Difference between an implicit & an explicit cursor.

     PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL
statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.


The implicit cursor is used to process INSERT, UPDATE,DELETE, and SELECT INTO statements. During the processing ofan implicit cursor,Oracle automatically performs the OPEN,FETCH, and CLOSE operations.

Where as in explicit cursors,the process of its working isdone in 4 steps namely DECLARE a cursor,OPEN a cursor,FETCH from cursor and CLOSE a cursor.

IMPLICT CURSOR:- Automatically porvide by oracle which perform DML statements. queries returns only one row.

EXPLICT CURSOR:- Defined by user. queries returns more than rows.

Explicit Cursor:-We are not able to Handle NO_DATA_FOUND Exception.

Implicit Cursor:-We are able to Handle NO_DATA_FOUND Exception.

According to pl/sql where the data is temporarily stored is called cursor.
The Implicit cursor is created by Oracle engine itself while Explicit cursor is created by programmer by mean of programming statement.
The Exception can be handled in the Implicit cursor whereas Explicit cursor is unable to handle Exceptions.


There are properties of cursor


%open - check cursor is open or not.
%found - Row found out or not.
%not found - yes if no row found.
%row count - Rows affected by statement.


There are 4 steps of working of cursor named DECLARE-cursor is declared, OPEN- cursor is opened, FETCH- data fetched, CLOSE- close the cursor.

Thursday, December 23, 2010

Informatica PowerCenter


Informatica Repository Manager


A. Informatica PowerCenter includeds following type of repositories :
  • Standalone Repository : A repository that functions individually and this is unrelated to any other repositories.
  • Global Repository : This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts.
  • Local Repository : Local repository is within a domain and it not a global repository. Local repository can connect to a global repository using global shortcuts and can use objects in it shared folders.
  • Versioned Repository : This can either be local or global repository but it allows version control for the repository. A versioned repository can store multiple copies, or versions of an object. This features allows to efficiently develop, test and deploy metadata in the production environment.

A. A code page contains encoding to specify characters in a set of one or more languages. The code page is selected based on source of the data. For example if source contains Japanese text then the code page should be selected to support Japanese text.
When a code page is chosen, the program or application for which the code page is set, refers to a specific set of data that describes the characters the application recognizes. This influences the way that application stores, receives, and sends character data.


A. PowerCenter Server on Windows can connect to following databases:
  • IBM DB2
  • Informix
  • Microsoft Access
  • Microsoft Excel
  • Microsoft SQL Server
  • Oracle
  • Sybase
  • Teradata

A. PowerCenter Server on UNIX can connect to following databases:
  • IBM DB2
  • Informix
  • Oracle
  • Sybase
  • Teradata
Infomratica Mapping Designer


A. Stored Procedure (SP) transformation can be used to execute PL/SQL Scripts. In SP Transformation PL/SQL procedure name can be specified. Whenever the session is executed, the session will call the pl/sql procedure.


A. A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions. For example, an Aggregator transformation performs calculations on groups of data. Below are the various transformations available in Informatica:
  • Aggregator
  • Application Source Qualifier
  • Custom
  • Expression
  • External Procedure
  • Filter
  • Input
  • Joiner
  • Lookup
  • Normalizer
  • Output
  • Rank
  • Router
  • Sequence Generator
  • Sorter
  • Source Qualifier
  • Stored Procedure
  • Transaction Control
  • Union
  • Update Strategy
  • XML Generator
  • XML Parser
  • XML Source Qualifier

A. Source Qualifier represents the rows that the PowerCenter Server reads from a relational or flat file source when it runs a session. When a relational or a flat file source definition is added to a mapping, it is connected to a Source Qualifier transformation.
PowerCenter Server generates a query for each Source Qualifier Transformation whenever it runs the session. The default query is SELET statement containing all the source columns. Source Qualifier has capability to override this default query by changing the default settings of the transformation properties. The list of selected ports or the order they appear in the default query should not be changed in overridden query.


A. The Aggregator transformation allows performing aggregate calculations, such as averages and sums. Unlike Expression Transformation, the Aggregator transformation can only be used to perform calculations on groups. The Expression transformation permits calculations on a row-by-row basis only.
Aggregator Transformation contains group by ports that indicate how to group the data. While grouping the data, the aggregator transformation outputs the last row of each group unless otherwise specified in the transformation properties.
Various group by functions available in Informatica are : AVG, COUNT, FIRST, LAST, MAX, MEDIAN, MIN, PERCENTILE, STDDEV, SUM, VARIANCE.


A. Whenever a session is created for a mapping Aggregate Transformation, the session option for Incremental Aggregation can be enabled. When PowerCenter performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculations incrementally.


A. The union transformation is a multiple input group transformation that can be used to merge data from various sources (or pipelines). This transformation works just like UNION ALL statement in SQL, that is used to combine result set of two SELECT statements.


A. Yes, joiner transformation can be used to join data from two flat file sources.


A. This transformation is used to lookup data in a flat file or a relational table, view or synonym. It compares lookup transformation ports (input ports) to the source column values based on the lookup condition. Later returned values can be passed to other transformations.

A. Yes.


A. Connected lookup takes input values directly from other transformations in the pipleline.
Unconnected lookup doesn take inputs directly from any other transformation, but it can be used in any transformation (like expression) and can be invoked as a function using :LKP expression. So, an unconnected lookup can be called multiple times in a mapping.


A. A mapplet is a reusable object that is created using mapplet designer. The mapplet contains set of transformations and it allows us to reuse that transformation logic in multiple mappings.


A. Reusable transformations can be used multiple times in a mapping. The reusable transformation is stored as a metadata separate from any other mapping that uses the transformation. Whenever any changes to a reusable transformation are made, all the mappings where the transformation is used will be invalidated.


A. Informatica processes the source data row-by-row. By default every row is marked to be inserted in the target table. If the row has to be updated/inserted based on some logic Update Strategy transformation is used. The condition can be specified in Update Strategy to mark the processed row for update or insert.
Following options are available for update strategy :
  • DD_INSERT : If this is used the Update Strategy flags the row for insertion. Equivalent numeric value of DD_INSERT is 0.
  • DD_UPDATE : If this is used the Update Strategy flags the row for update. Equivalent numeric value of DD_UPDATE is 1.
  • DD_DELETE : If this is used the Update Strategy flags the row for deletion. Equivalent numeric value of DD_DELETE is 2.
  • DD_REJECT : If this is used the Update Strategy flags the row for rejection. Equivalent numeric value of DD_REJECT is 3.

Wednesday, December 22, 2010

Do's and Dont's while using Sorted Input in Aggregator transformation

In general, follow this check list to ensure that you are handling aggregator with sorted inputs correctly:
1. Do not use sorted input if any of the following conditions are true:


a.The aggregate expression uses nested aggregate functions.
b.The session uses incremental aggregation.
c. Input data is data driven.
You select data driven for the Treat Source Rows as Session Property, or the Update Strategy transformation appears before the Aggregator transformation in the mapping.
If you use sorted input under these circumstances, the Informatica Server reverts to default aggregate behavior, reading all values before performing aggregate calculations.

Remember, when you are using sorted inputs, you pass sorted data through the Aggregator.
Data must be sorted as follows:
1. By the Aggregator group by ports, in the order they appear in the Aggregator transformation.
2. Using the same sort order configured for the session.

If data is not in strict ascending or descending order based on the session sort order, the Informatica Server fails the session.

Tuesday, December 21, 2010

JOIN MORE THAN 2 TABLES

JOIN MORE THAN 2 TABLES
=========================

SELECT E.ENAME,E.SAL,D.DEPTNO,A.LOC FROM

EMP E JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

JOIN

ADDR A

ON D.LOC=A.LOC



WITHOUT JOIN
=================


SELECT E.ENAME,E.SAL,D.DEPTNO,A.LOC FROM

EMP E,DEPT D,ADDR A

WHERE 

E.DEPTNO=D.DEPTNO

AND

D.LOC=A.LOC

Monday, December 20, 2010

UNIX Commands Interview Questions

UNIX Commands Interview Questions

1.What is Event-Based Scheduling?

When you use event-based scheduling, the Informatica Server starts a session when it locates the specified indicator file. To use event-based scheduling, you need a shell command, script, or batch file to create an indicator file when all sources are available. The file must be created or sent to a directory local to the Informatica Server. The file can be of any format recognized by the Informatica Server operating system. The Informatica Server deletes the indicator file once the session starts.
Use the following syntax to ping the Informatica Server on a UNIX system:
pmcmd ping [{user_name | %user_env_var} {password | %password_env_var}] [hostname:]portno
Use the following syntax to start a session or batch on a UNIX system:
pmcmd start {user_name | %user_env_var} {password | %password_env_var} [hostname:]portno [folder_name:]{session_name | batch_name} [:pf=param_file] session_flag wait_flag
Use the following syntax to stop a session or batch on a UNIX system:
pmcmd stop {user_name | %user_env_var} {password | %password_env_var} [hostname:]portno[folder_name:]{session_name | batch_name} session_flag
Use the following syntax to stop the Informatica Server on a UNIX system:
pmcmd stopserver {user_name | %user_env_var} {password | %password_env_var} [hostname:]portno

2. Explain the following commands.
$ ls > file1
$ banner hi-fi > message
$ cat par.3 par.4 par.5 >> report
$ cat file1>file1
$ date ; who
$ date ; who > logfile
$ (date ; who) > logfile

3.wht is the significance of "tee" cmd? 

It reads the standard input and sends it to the standard output while redirecting a copy of what it has read to
the file specified by the user.

4. What does the command “ $who | sort –logfile > newfile” do?

The input from a pipe can be combined with the input from a file . The trick is to use the special symbol “-“
(a hyphen) for those commands that recognize the hyphen as std input.
In the above command the output from who becomes the std input to sort , meanwhile sort opens the file
logfile, the contents of this file is sorted together with the output of who (rep by the hyphen) and the sorted
output is redirected to the file newfile.

5. What does the command “$ls | wc –l > file1” do?

ls becomes the input to wc which counts the number of lines it receives as input and instead of displaying
this count , the value is stored in file1.

6. Which of the following commands is not a filter man , (b) cat , (c) pg , (d) head

Ans: man
A filter is a program which can receive a flow of data from std input, process (or filter) it and send the result
to the std output.

7. How is the command “$cat file2 “ different from “$cat >file2 and >> redirection operators ?

is the output redirection operator when used it overwrites while >> operator appends into the file.

9. Explain the steps that a shell follows while processing a command.

After the command line is terminated by the key, the shel goes ahead with processing the command line in
one or more passes. The sequence is well defined and assumes the following order.

Parsing
quoted. All consecutive occurrences of a space or tab are replaced here with a single space.

: The shell first breaks up the command line into words, using spaces and the delimiters, unless

Variable evaluation : Any command surrounded by backquotes is executed by the shell which then: The shell finally scans the command line for wild-cards (the characters *, ?, [, ]).
Command substitution:Any command surrounded by backquotes is executed by the shell which then 
replaces the standard output of the command into the command line.
Wild-card interpretation:Any word containing a wild-card is replaced by a sorted list of
filenames that match the pattern. The list of these filenames then forms the arguments to the command.
PATH evaluation: It finally looks for the PATH variable to determine the sequence of directories it has to search in order to hunt for the command.
 
12. What is the difference between cat and more command?

Cat displays file contents. If the file is large the contents scroll off the screen before we view it. So
command 'more' is like a pager which displays the contents page by page.
13. Write a command to kill the last background job?


Kill $!
14. Which command is used to delete all files in the current directory and all its sub-directorories?


rm -r *

15. Write a command to display a file’s contents in various formats?

$od -cbd file_name
c - character, b - binary (octal), d-decimal, od=Octal Dump.

16. What will the following command do?

$ echo *
It is similar to 'ls' command and displays all the files in the current directory.

17. Is it possible to create new a file system in UNIX?

Yes, ‘mkfs’ is used to create a new file system.

18. Is it possible to restrict incoming message?

Yes, using the ‘mesg’ command.

19. What is the use of the command "ls -x chapter[1-5]"

ls stands for list; so it displays the list of the files that starts with 'chapter' with suffix '1' to '5', chapter1,
chapter2, and so on.

20. Is ‘du’ a command? If so, what is its use?

Yes, it stands for ‘disk usage’. With the help of this command you can find the disk capacity and free space
of the disk.

21. Is it possible to count number char, line in a file; if so, How?

Yes, wc-stands for word count.
wc -c for counting number of characters in a file.
wc -l for counting lines in a file.



22. Name the data structure used to maintain file identification?

‘inode’, each file has a separate inode and a unique inode number.

23. How many prompts are available in a UNIX system?

Two prompts, PS1 (Primary Prompt), PS2 (Secondary Prompt).
24. How does the kernel differentiate device files and ordinary files?


Kernel checks 'type' field in the file's inode structure.

25. How to switch to a super user status to gain privileges?
Use ‘su’ command. The system asks for password and when valid entry is made the user gains super user
(admin) privileges.

26. What are shell variables?
Shell variables are special variables, a name-value pair created and maintained by the shell.
Example: PATH, HOME, MAIL and TERM

27. What is redirection?
Directing the flow of data to the file or from the file for input or output.
Example : ls > wc

28. How to terminate a process which is running and the specialty on command kill 0?
With the help of kill command we can terminate the process.
Syntax: kill pid
Kill 0 - kills all processes in your system except the login shell.

29. What is a pipe and give an example?
A pipe is two or more commands separated by pipe char '|'. That tells the shell to arrange for the output of
the preceding command to be passed as input to the following command.
Example : ls -l | pr
The output for a command ls is the standard input of pr.
When a sequence of commands are combined using pipe, then it is called pipeline.

30. Explain kill() and its possible return values.

There are four possible results from this call:
‘kill()’ returns 0. This implies that a process exists with the given PID, and the system would allow you to
send signals to it. It is system-dependent whether the process could be a zombie.
‘kill()’ returns -1, ‘errno == ESRCH’ either no process exists with the given PID, or security enhancements
are causing the system to deny its existence. (On some systems, the process could be a zombie.)
‘kill()’ returns -1, ‘errno == EPERM’ the system would not allow you to kill the specified process. This means
that either the process exists (again, it could be a zombie) or draconian security enhancements are present
(e.g. your process is not allowed to send signals to *anybody*).
‘kill()’ returns -1, with some other value of ‘errno’ you are in trouble! The most-used technique is to assume
that success or failure with ‘EPERM’ implies that the process exists, and any other error implies that it
doesn't.
An alternative exists, if you are writing specifically for a system (or all those systems) that provide a ‘/proc’
filesystem: checking for the existence of ‘/proc/PID’ may work.

31. What is relative path and absolute path.

Absolute path : Exact path from root directory.
Relative path : Relative to the current path.

32.Construct pipes to execute the following jobs.
 
1. Output of who should be displayed on the screen with value of total number of users who have logged in
displayed at the bottom of the list.
2. Output of ls should be displayed on the screen and from this output the lines containing the word ‘poem’
should be counted and the count should be stored in a file.
3. Contents of file1 and file2 should be displayed on the screen and this output should be appended in a file
.
From output of ls the lines containing ‘poem’ should be displayed on the screen along with the count.
4. Name of cities should be accepted from the keyboard . This list should be combined with the list present
in a file. This combined list should be sorted and the sorted list
should be stored in a file ‘newcity’.
5. All files present in a directory dir1 should be deleted any error while deleting should be stored in a file
‘errorlog’.
11. What is the use of ‘grep’ command?

‘grep’ is a pattern search command. It searches for the pattern, specified in the command line with
appropriate option, in a file(s).
Syntax : grep
Example : grep 99mx mcafile


10. What difference between cmp and diff commands?
cmp - Compares two files byte by byte and displays the first mismatch
diff - tells the changes to be made to make the files identical