Atoz Consulting Services--atozcs.com

Quality Service Provider in
BPO -Data Processing,data entry,form processing
KPO
Web/Software Application development.


Atoz consulting Services.
Vidhya Nagar East,
District -Beed-431122
State-Maharastra,
India.
Contact No:08087641720
site:
www.atozcs.com
email:
info@atozcs.com

Shell Scripting

• cat filename1 > filename2 :
This overwrite contents of filename2 with filename1
• cat filename1 » filename2 :
This appends contents of filename1 to filename2.
• cd /usr:
Changes current directory to /usr directory
• cd .. :
Change current directory to previous higher directory.
• cd :
Change to home of current userid.
• chgrp group1 filename1 :
Change group id to group 1 for filename1.
• chgrp -R group1 * :
Changes group id of all files in current and subdirectories to group 1.
• chmod ugo+rwx filename1:
Add read/write/execute permission to filename1 for user/owner, group and others (world).
• chmod o x filename1 :
Remove execute from others (world)
• chmod 751 filename1 :
Set rwx for user/owner, group and others; r=4, w=2, x=1, rwx=7 rx=5, rw=6, wx=3, none=0.
• chown sam filename1 :
Change owner of file filename1 to sam.
• chown -R sam * :
Change owner to sam for all files in current and subdirectories.
• clear :
Clear the terminal screen.
• compress filename1 :
Compress file filename1 and replace it with filename1.Z
• cp filename1 filename2 :
Copy file filename1 to filename2 destroying the contents of filename2.
• cp -i * /usr/local/bin :
Copy all files in current directory to /usr/local/bin directory; Prompt before overwriting files (i)
• cpio :
Copy file archives to disk/tape
• crontab -e :
Edit crontab file for current user
• crontab -e bill :
Edit crontab file for user bill
• crontab -l :
List crontab entries for current userid
• csh :
Start the c shell process
• cut -d: -f1,5 /etc/passwd :
Extract the 1st and 5th fields (username & real name) from file /etc/passwd where delimiter is colon
• who | cut -d" " -f1:
List login names from who command
• date :
Display current date string
• date +%D :
Display current date, MM/DD/YY format
• dd :
Copy file(s) to/from raw devices
• df :
Displays free disk blocks and modes on file systems.
• df -k :
Displays free space in kilobytes for mounted file systems
• echo name :
Displays literal "name" on screen
• echo $PATH :
Displays PATH environment variable
• ed/edit/ex :
Alternative line editors, see vi
• env Displays current environment variables or allows setting
• file filename1 :
Determines and displays type of file for filename1 (text, data, executable, directory, symbolic link...etc...)
• find /usr/opt/bin -name "filename1*" -print :
Starts searching in /usr/opt/bin for files starting with
• filename1, If found prints the full file names and continues searching subdirectories
• find . -type f -print | xargs grep -i [PATTERN] :
Recursive grep for a pattern in a file (searches through files in subdirectories)
• find . ! -mtime - | /usr/bin/xargs rm -rf :
Finds and removes files older than specified
• finger sam :
Displays data about user session for sam
• ftp solar :
Establishes a File Transfer Protocol session over the network between current host and a host named solar; See man pages for additional ftp commands
• grep jdoe /etc/passwd :
Searches the file /etc/passwd searching for string "jdoe"; If found, displays
• grep -i Sam filename1:
Search filename1 for upper or lower case string of Sam and display lines found
• groups sam :
List groups that sam is a member of
• head filename1 :
Display first few lines of filename1
• head -50 filename1:
Display first fifty lines of filename1
• id :
List current user id and any group ids
• kill -9 1351 :
Terminate process number 1351
• ksh:
Start Korn Shell command interpreter; See man pages; Preferred shell for most users
• ln -s filename1 /usr/opt/filename2 :
Create a symbolic link named /usr/opt/filename2 that points to filename1; See man pages
• lp -d lp1 filename1 :
Print filename1 on destination printer lp1
• lpstat -d :
Displays name of default printer, if any
• lpstat -a :
Lists printers accepting print requests
• lpstat -s :
Displays most everything regarding printing
• lpstat -u sam :
Displays status of sam's print jobs
• ls -al :
Displays all files in wide listing
• ls -al *.doc :
Displays files ending with .doc
• ls -al /bin/k*:
Displays files starting with k in /bin directory
• man --is Online UNIX manual:
• man-pages:
It gives detailed instructions on all of the commands listed here and a lot more.See man man for more...
• mail sam :
Starts mail message to sam
• mailx sam :
Nicer looking mail utility
• mkdir -p /usr/opt/dirx:
Creates dirx below /usr/opt
• make:
Code compilation utility
• mkdir dirx:
Creates directory dirx
• more filename1 :
Displays single pages from filename1 pausing after each page.for many options see man pages
• mv filename1 /usr/opt/ :
Moves filename1 to directory /usr/opt; Unlike the cp (copy) command, mv removes file from origin
• netstat -i:
Show the TCP/IP network interfaces
• netstat -r:
Show network route table
• netstat -rn:
Displays routing information but bypasses hostname lookup
• netstat -a | more :
Show the state of all sockets
• newgrp group1 :
Changes current group to group 1
• news :
Displays unread files from /usr/news or /var/news
• nice/renice :
Adjusts process’ execution priority
• Unix Commands from p to z:
• passwd:
Allows changing your login password
• ps :
Lists all of current user's live processes
• ps -ef :
List all users processes that are executing
• pwd:
Displays current working directory you are in
• rcp filename1 hostb:
/usr/local/bin :
Copies filename1 from current host system to hostb, and places it in the /usr/local/bin directory; The .rhosts or hosts.equiv files must be setup to allow action
• rksh:
Starts restricted Korn Shell session
• rlogin hostb :
Logs into remote host name hostb
• rm filename1 :
Deletes filename1 from the disk without recourse
• rm -i filename1 :
Deletes filename1 after prompting for verification
• rmdir dirx :
Deletes directory dirx
• rmdir -r dirx:
Deletes directory and all contents
• rsh:
Restricted version of Bourne Shell for security
• sar :
System Activity Reporter
• sh :
Bourne Shell command interpreter; Alternative to Korn Shell and C shell but is the default on most systems; Older version that is losing popularity
• sleep 3 :
Pauses for 3 seconds and continues
• stty sane:
Attempts to restore terminal settings after they are hosed; Use CTRL-J with this command
• stty erase ^H :
Use to reset backspace/delete; ^H is the key you may want to use for backspace/delete;
• su sam:
Log on as user sam
• su - sam:
Log on as sam, and execute his profile too
• tail filename1 :
Display last few lines of filename1
• tail -50 filename1 :
Display last fifty lines of filename1
• tail -f :
Continually reads updating file; Great for monitoring a log file while being written
• tar -cvf /dev/rmt/0c /usr/bin/* :
Tape archive utility; Copies all files in /usr/bin directory to tape device /dev/rmt/
• tar -xvf /dev/rmt/0 /usr/bin/ :
Extracts all files from tape device /dev/rmt/0 and writes them to /usr/bin/
• tar -tvf /dev/rmt/0c :
Read tape on device /dev/rmt/0c and lists contents in verbose mode
• tee:
Used in scripts to split output to two outputs. Usually used with a pipe command (|)
• tee -a /tmp/filename1:
Append the output to filename1 without overwriting its original content
• telnet hostb:
Create a remote terminal on hostb
• touch filename1 :
Creates an empty file named filename1; Changes modification time to current time if the file already exists
• uname -a:
Lists 0/S revision, host name, hardware
• uncompress filename1 :
Uncompresses file with .Zsuffix, created by compress command previously
• uptime :
Displays current time, time logged-in,number of users etc
• users:
Displays current logged-in users in a listing
• uucp :
UNIX-to-UNIX copy utility; See man pages
• view filename1:
A read only version of vi editor
• w :
Combination of uptime, who and ps -a commands
• wall :
Write to all; Allows entry of message to be sent to line 25 of all terminals; End message with a CTRLD command
• wc -l filename1:
Utility that counts the number of lines in filename1
• wc -c filename1 :
Utility that counts the number of bytes in filename1; -m provides number of characters
• wc -w filename1 :
Utility that counts the number of the words in filename1
• whence filename2:
Prints path name location of executable filename2
• which filename2 :
Similar to whence command
• who Displays login name, terminal name, date, and time of login, of users currently logged in
• who am i :
Displays effective user id of user
• who -b :
Displays date/time of last reboot
• who -r :
Displays current system run level

1 List the files in current directory sorted by size ?
ls -l | grep ^- | sort -nr

2. List the hidden files in current directory ?
- ls -a1 | grep "^\."

3. Delete blank lines in a file ?
– cat sample.txt | grep -v ‘^$’ > new_sample.txt

4. Search for a sample string in particular files ?
grep .Debug. *.conf
Here grep uses the string .Debug. to search in all files with extension..conf. under current directory.

5. Display the last newly appending lines of a file during appending data to the same file by some processes ?
tail .f Debug.log
Here tail shows the newly appended data into Debug.log by some processes/user.

6. Display the Disk Usage of file sizes under each directory in currentDirectory ?
du -k * | sort .nr (or) du .k . | sort -nr

7. Change to a directory, which is having very long name ?
cd CDMA_3X_GEN*Here original directory name is . .CDMA_3X_GENERATION_DATA..

8. Display the all files recursively with path under current directory ?
find . -depth -print

9. Set the Display automatically for the current new user ?
export DISPLAY=`eval ‘who am i | cut -d"(" -f2 | cut -d")" -f1′`
Here in above command, see single quote, double quote, grave ascent is used. Observe carefully.

10. Display the processes, which are running under yourusername ?
ps .aef | grep Maheshvj
Here, Maheshvj is the username.

11. List some Hot Keys for bash shell ?
- Ctrl+l . Clears the Screen.
Ctrl+r . Does a search in previously given commands in shell.
Ctrl+u - Clears the typing before the hotkey.
Ctrl+a . Places cursor at the beginning of the command at shell.
Ctrl+e . Places cursor at the end of the command at shell.
Ctrl+d . Kills the shell.
Ctrl+z . Places the currently running process into background.
12. Display the files in the directory by file size ?
ls .ltr | sort .nr .k 5

13. How to save man pages to a file ?
man | col .b > Example : man top | col .b > top_help.txt

14. How to know the date & time for . when script is executed ?
Add the following script line in shell script.
eval echo "Script is executed at `date`" >> timeinfo.inf
Here, .timeinfo.inf. contains date & time details ie., when script is executed and history related to execution.

15. How do you find out drive statistics ?
iostat -E

16. Display disk usage in Kilobytes ?
du -k

17. Display top ten largest files/directories ?
- du -sk * | sort -nr | head

18. How much space is used for users in kilobytes ?
quot -af

19. How to create null file ?
cat /dev/null > filename1

20. Access common commands quicker ?
ps -ef | grep -i $@

21. Display the page size of memory ?
pagesize -a

22. Display Ethernet Address arp table ?
arp -a

23. Display the no.of active established connections to localhost ?
netstat -a | grep EST

24. Display the state of interfaces used for TCP/IP traffice ?
netstat -i

25. Display the parent/child tree of a process ?
ptree Example: ptree 1267

26. Show the working directory of a process ?
pwdx Example: pwdx 1267

27. Display the processes current open files ?
pfiles Example: pfiles 1267

28. Display the inter-process communication facility status ?
ipcs

29. Display the top most process utilizing most CPU ?
top .b 1

30. Alternative for top command ?
prstat -a
want to create a shell script which will check whether any integration services is down or not.
If it is down than i should get alert mails for the same.



#!/usr/bin/ksh

pmcmd pingservice -service Integration_Service_Name -domain Domain_Name -timeout 5 > status

if [ `grep -c "Integration Service is alive." status` -eq 1 ]
then
echo "Failure" | mailx -s "Integration Service is not running" xyz@...com

fi

ETL and Data Warehousing Performance Tuning

ETL and Data Warehousing Performance Tuning
=======================================================
Query Performance
I. General

II. Indexing

III. Clustering

2. Partitioning

4. Cache Performance

5. ETL performaing tune for source,target,mapping/job,session


Query Performance--------------------------------------

For any production database, SQL query performance becomes an issue sooner or later.
Having long-running queries not only consumes system resources that makes the server
and application run slowly, but also may lead to table locking and data corruption issues.
So, query optimization becomes an important task.

First, principles for query optimization:

1. Understand how your database is executing your query

Nowadays all databases have their own query optimizer, and offers a way for users
to understand how a query is executed. For example, which index from which table is
being used to execute the query? The first step to query optimization is understanding
what the database is doing. Different databases have different commands for this.
For example, in MySQL, one can use "EXPLAIN [SQL Query]" keyword to see the query plan.
In Oracle, one can use "EXPLAIN PLAN FOR [SQL Query]" to see the query plan.

2. Retrieve as little data as possible

The more data returned from the query, the more resources the database needs to expand
to process and store these data. So for example, if you only need to retrieve one column
from a table, do not use 'SELECT *'.

3. Store intermediate results

Sometimes logic for a query can be quite complex. Often, it is possible to achieve
the desired result through the use of subqueries, inline views, and UNION-type statements.
For those cases, the intermediate results are not stored in the database, but are
mmediately used within the query. This can lead to performance issues, especially
when the intermediate results have a large number of rows.

The way to increase query performance in those cases is to store the intermediate

Below are several specific query optimization strategies.

II. Indexing

Use Index
Using an index is the first strategy one should use to speed up a query.
In fact, this strategy is so important that index optimization is also discussed.

Aggregate Table
Pre-populating tables at higher levels so less amount of data need to be parsed.

III. Clustering

Vertical Partitioning
Partition the table by columns. This strategy decreases the amount of data a SQL query
needs to process.

Horizontal Partitioning
Partition the table by data value, most often time. This strategy decreases the amount
of data a SQL query needs to process.

Denormalization
The process of denormalization combines multiple tables into a single table.
his speeds up query performance because fewer table joins are needed.

Server Tuning
Each server has its own parameters, and often tuning server parameters so that
it can fully take advantage of the hardware resources can significantly speed
up query performance.

Informatica Certification Questions/Dump

Mapplet definition
Refer to the mapping illustrated below. Assume that the mapplet definition is valid and that the individual transformation expressions are valid. Select the statement that is correct.
Choose Answer
(A)
The mapping will be valid as it is.
(B)
The mapping is valid because the third mapplet output group, Update Changes, does not have any output ports connected to a transformation or Target definition.
(C)
The mapping is invalid because you cannot concatenate two or more data flow paths together into one mapplet input group.
(D)
The mapplet is invalid because the number of output ports in each output group does not match.
Input transformation in mapplet
Select the statement below that is true.
Choose Answer
(A)
A mapplet that does not contain an Input transformation is always an active mapplet.
(B)
A mapplet that does not contain an Input transformation is an active mapplet only if it contains at least one active transformation.
(C)
A passive mapplet can be made into an active mapplet by adding one or more Joiner transformations to the mapplet.
(D)
A passive mapplet can be made into an active mapplet by adding one or more Lookup transformations to the mapplet.
Output transformations in a mapplet
How many Output transformations can be placed in a mapplet?
Choose Answer
(A)
One
(B)
One or more
(C)
One or more unless there is at least one Target definition in the mapplet, in which case zero Output transformations are permitted.
(D)
Any number
Homogeneous Source Definitions
How many homogeneous Source Definitions can be placed in a Mapplet?
Choose Answer
(A)
One
(B)
One for each Source Qualifier transformation.
(C)
Zero to one if the mapplet does not contain an Input or ERP Source Qualifier transformation.
(D)
Unlimited
Difference between the Router and Filter transformations
A major difference between the Router and Filter transformations is that filtered rows can be captured in the Router. (T/F)
Choose Answer
(A)
True
(B)
False
Filter transformation
A Filter transformation contains the following condition: ITEM_COST > 100.00. The value for ITEM_COST for a specific row is 35. What will happen to that row?
Choose Answer
(A)
It is dropped from the mapping flow.
(B)
It is dropped from the mapping flow and written to the appropriate reject (bad) file.
(C)
It is dropped from the mapping flow and written to the appropriate reject (bad) file if the Filter property "Forward Filtered Rows" is set to True.
(D)
If the Filter property "Forward Filtered Rows" is set to True, the row is sent to downstream transformations, if any, but not written to the target table.
Router transformation that has three groups defined
Specify which statement below best describes the behavior of a Router transformation that has three groups defined.
Choose Answer
(A)
The condition for each group will be evaluated for each row, and the first group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports.
(B)
The condition for each group will be evaluated for each row, and each group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports.
(C)
The condition for each group will be evaluated for each row, and the first group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports only if those ports are connected to another transformation.
(D)
The condition for each group will be evaluated for each row, and each group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports only if those ports are connected to another transformation.
Update Strategy transformation
It is desired to run a session where every row that is processed by the session updates the target table based on the primary key value. Assume that the target table definition has a primary key, and all other session and mapping properties are left to their defaults. Select the statement below that is true.
Choose Answer
(A)
The only action that is necessary is to set the Treat Source Rows As session property to Update.
(B)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table.
(C)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table, and the Treat Source Rows As session property must be set to Update.
(D)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table, and the Treat Source Rows As session property must be set to Data Driven.
Expressions used in an Update Strategy
What type of expressions can be used in an Update Strategy transformation?
Choose Answer
(A)
Immediate If ( IIF)
(B)
Immediate If (IIF) or DECODE
(C)
Immediate If (IIF), DECODE, or IF THEN ELSE
(D)
Any valid non-aggregate expression that is supported by the transformation language.
Forward Rejected Rows in Update Strategy
Refer to the diagram below. The Update Strategy transformation contains an expression that may tag each row as insert, delete or reject. It is desired to count ALL rows that pass through the Aggregator, regardless of how they are tagged. Assume that the other parts of the mapping are finished and that the Update Strategy transformation property Forward Rejected Rows is set to True. Select the statement that is true.
Choose Answer
(A)
The mapping will work as desired without any further changes.
(B)
The mapping will work as desired only if the Aggregator transformation is moved to be upstream of the Update Strategy transformation.
(C)
The mapping will work as desired only if it is redesigned to use a Router transformation in place of the Update Strategy transformation, and the row operation for each row (insert, delete, or reject) is set by an individual Update Strategy transformation for each flow, and then counted by an individual Aggregator transformation for each flow.
(D)
The mapping will work as desired only if the Update Strategy transformation tags each row only as insert or delete (no rows tagged as reject). Any rows that are not desired in the target table can then be removed from the flow by adding a Filter transformation DOWNSTREAM of the Aggregator transformation, and setting the Filter condition to be logically equivalent to the condition that was used in the Update Strategy transformation to tag the rows as reject.
"Treat source rows as" session property
A user sets the "Treat source rows as" session property to "Update". What is the effect of making this selection?
Choose Answer
(A)
All records processed by the session will be treated as update statements on the target table(s).
(B)
All records processed by the session will be treated as update statements on the target table(s), provided that a primary key constraint exists on the corresponding target table definition(s).
(C)
This selection allows the use of the Update Strategy transformation, provided that one or more Update Strategy transformations are present in the mapping run by the session.
(D)
This selection allows the use of the Update Strategy transformation, provided that one or more Update Strategy transformations are present in the mapping run by the session, and provided that a primary key constraint exists on the corresponding target table definition(s).
Case-sensitive string comparison
Can the Joiner transformation perform a case-sensitive string comparison as part of the join condition?
Choose Answer
(A)
Yes, always
(B)
No, the comparison is never case sensitive.
(C)
Yes when the Informatica server is running on the Unix operating system, no when it is running on a Windows operating system.
(D)
Yes, when the database that is being used for the join supports case-sensitive string comparisons.
Full outer join
A developer is using a Joiner transformation to join two data streams (sources), and a full outer join is desired. Select the statement below that is true.
Choose Answer
(A)
A Joiner transformations may perform a full outer join under all conditions.
(B)
A Joiner transformation may perform a full outer join only when one or more sides of the join are from a relational database.
(C)
A Joiner transformation may perform a full outer join only when all sides of the join originate from relational databases, and one or more of those databases support outer joins.
(D)
A Joiner transformation may perform a full outer join only when no file sources are involved in the join (both sides are relational).
3 or more data sources connected to a Joiner
Choose Answer
(A)
The mapping is valid as it is.
(B)
The mapping is invalid because there is an active transformation between SQ_ITEMS and the Joiner transformation. If the Aggregator transformation was eliminated or replaced with a passive transformation, the mapping would be valid.
(C)
The mapping is valid only if the SQ_ITEMS side of the join is designated as the master side in the Joiner transformation.
(D)
The mapping is invalid because it is not permitted to have three or more data sources connected to a Joiner transformation under any circumstances.
Joiner
Refer to the diagram below, which shows part of a mapping. Assume that each individual transformation and transformation expression in the mapping is valid, and that the part of the mapping that is not visible (downstream of the Joiner) is valid. Select the statement that is true.
Choose Answer
(A)
The mapping is valid as it is.
(B)
The mapping is invalid because a Joiner transformation must be the first transformation after the Source Qualifier, and in this mapping there are two transformations between SQ_ITEMS and the Joiner transformation.
(C)
The mapping will be valid only if the SQ_ITEMS side of the join is designated as the detail side in the Joiner transformation.
(D)
The mapping will be valid only if the SQ_ITEMS side of the join is designated as the master side in the Joiner transformation.


Refer to the diagram below, which shows part of a mapping. Assume that each individual transformation and transformation expression is valid. Select the statement that is true.
Joiner transformations
It is desired to join together a database table, a flat file and an XML file. All three sources have a single common field called CUSTOMER_ID. What is the minimum number of Joiner transformations that are needed to join these sources?
Choose Answer
(A)
One
(B)
Two
(C)
Three
(D)
One if the flat file and the XML file are located in the same directory, two if they are located in different directories.
Set a Lookup condition
A developer is attempting to set a Lookup condition and finds that the New Condition button is disabled, making it impossible to set a condition. Assume that it is a connected Lookup. What is a possible reason for this?
Choose Answer
(A)
There are no designated Lookup ports in the ports tab section.
(B)
There are no designated Input ports in the ports tab section.
(C)
There are no ports with compatible data types.
(D)
At least one Output port has not been connected to another transformation.
Override the SQL of a non-shared, uncached Lookup transformation
It is desired to override the SQL of a non-shared, uncached Lookup transformation in order to add a WHERE clause. Select the action below that MUST be taken to allow the session to run as designed. Assume that the session has already been tested prior to the override and it is satisfactory.
Choose Answer
(A)
Cache the Lookup
(B)
Include an ORDER BY clause in the SQL that sorts the lookup field(s) in descending order.
(C)
Generate a default query after the override SQL has been entered.
(D)
Validate the override SQL on the actual database table.



Lookup transformation where cache is persistent
Select the Lookup transformation scenario below which is not supported. Assume that the Lookup cache is persistent, it is shared and named, and there is only one Lookup transformation used in the mapping or mapplet.

Choose Answer

(A)
Static cacheCondition: sales_amt > sales_amt1Unconnected
(B)
Static cacheCondition: sales_amt = sales_amt1 Unconnected
(C)
Dynamic cacheCondition: sales_amt > sales_amt1Connected
(D)
Dynamic cacheCondition: sales_amt = sales_amt1Connected


Dynamic lookup cache
Select the statement below that most accurately describes how a dynamic lookup cache is designed to function.

Choose Answer

(A)
The Informatica server compares the target data to the cache data as each row is passed to the target and updates the cache as required.
(B)
The Informatica server inserts rows into the cache or updates rows in the cache as it passes rows to the target.
(C)
The Informatica server updates the cache as the target table data changes.
(D)
The Informatica server inserts, updates or deletes rows in the cache as it passes rows to the target.



Source Type Lookup transformation
What type of data sources are supported in the Source Type Lookup transformation property?

Choose Answer

(A)
Database
(B)
Database and flat file
(C)
Database, flat file, and COBOL file
(D)
Database, flat file, COBOL file, and XML file



Unconnected Lookup transformation
What is one disadvantage of using an unconnected (sometimes called function mode) Lookup transformation?

Choose Answer

(A)
If there is no match in the Lookup condition, the Informatica server returns a transformation error.
(B)
A reusable unconnected Lookup can not have more than one instance in a mapping.
(C)
An unconnected Lookup transformation does not support user-defined default values.
(D)
An unconnected Lookup transformation does not support a static lookup cache.



NEXTVAL port of a Sequence Generator
Can the NEXTVAL port of a Sequence Generator transformation be connected to the PRIMARY KEY column of two different target table definitions in a mapping? Assume that the two definitions represent different physical tables on the database server.

Choose Answer

(A)
Yes, always.
(B)
Yes, but only if the two tables share a primary - foreign key (referential integrity) relationship.
(C)
No, because the Sequence Generator produces unique values (within the selected range) and this would result in a unique constraint error (duplicate key values) in one or both of the tables.
(D)
No, this is not allowed in the Mapping Designer.



Sequence Generator transformation
What is the difference between the NEXTVAL and the CURRVAL ports of the Sequence Generator transformation? Assume that they are both connected to the input ports of another transformation.

Choose Answer

(A)
The Informatica server generates a sequence of numbers for NEXTVAL, and CURRVAL is set to NEXTVAL plus one.
(B)
The Informatica server generates a sequence of numbers for NEXTVAL, and CURRVAL is set to NEXTVAL minus one.
(C)
The Informatica server generates a sequence of numbers for CURVALL, and NEXTVAL is set to CURRVAL plus one.
(D)
The Informatica server generates a sequence of numbers for CURVALL, and NEXTVAL is set to CURRVAL plus the integer value set for the "Increment By" Sequence Generator property.



Active transformations
Why is the sorter transformation considered an active transformation?

Choose Answer

(A)
When the Case Sensitive property is set to true (checked), rows that do not meet the case criteria in the sort may be discarded.
(B)
When more than one sort key is selected, nested sorts may result in a single input row being outputted more than one time within multiple sort sequences.
(C)
When the Distinct Output Rows property is set to true (checked), duplicate rows are discarded.
(D)
When the sort direction properties are not the same for all sort keys (not all ascending or all descending), a single input row may be outputted more than one time.



Sort a very large data set
When using the sorter transformation to sort a very large data set, additional RAM resources may be required to accommodate the data. Which machine would provide this RAM?

Choose Answer

(A)
The Informatica server machine.
(B)
The source database server machine.
(C)
The target database server machine.
(D)
The source database server machine when reading from a relational source, or the target database server machine when reading from a flat file, COBOL or XML source.



Sorter transformation
When a sorter transformation is included in a Mapping that is run in a Workflow, when does the data sort occur?

Choose Answer

(A)
Before the Workflow runs.
(B)
After the Workflow runs.
(C)
Either before or after the Workflow runs, depending on the sorter properties.
(D)
During the time the Workflow runs (Session Task run time).



Sorter transformation
Select the statement below that is true.

Choose Answer

(A)
The Sorter transformation must be used immediately downstream of a Source Qualifier transformation.
(B)
The Sorter transformation must be used immediately downstream of a Source Qualifier transformation that reads from a relational source.
(C)
The Sorter transformation supports multiple sort levels, so any sort key can have a secondary sort associated with it.
(D)
The user is allowed to modify the ORDER BY clause generated by the Sorter transformation.



Aggregator data RAM cache
By default, the Aggregator data RAM cache size is 2 Mbytes, and the index RAM cache is 1 Mbyte. What would happen if a user ran a session that required a data RAM cache of 3 Mbytes and the data cache size was left at the default?

Choose Answer

(A)
The session would fail when the Aggregator begins processing data.
(B)
The session would fail when the data RAM cache exceeds 2 Mbytes.
(C)
The Informatica server would automatically expand the data RAM cache to accommodate the additional data (provided the RAM is available on the machine).
(D)
The Informatica server would automatically page the data to a file on the local disk.



Aggregator data cache size.
An Aggregator transformation will process 50 million rows per session, and the width of each row is 3 KBytes. There are two group-by ports, MONTH and CUSTOMER. Assuming that the data set contains two years worth of data, and that there are 500 customers who are represented for each month in the data set, estimate the required data cache size.

Choose Answer

(A)
6 MBytes
(B)
12 MBytes
(C)
18 MBytes
(D)
36 MBytes




Aggregate cache
The aggregate cache always consists of

Choose Answer

(A)
An index and data cache in RAM, and an index and data file on the local disk.
(B)
An index and data cache in RAM, and an index and data cache on the local disk if the RAM caches cannot contain all of the intermediate results in the data set.
(C)
An index and data cache in RAM, and an index and data file on the local disk if the file names and directory (or Server Variable) have been specified as an Aggregator property.
(D)
An index and data cache in RAM, and an index and data file on the local disk if the Incremental Aggregation property has been set to true when the session runs.



Sorted Input in Aggregator transformation
An Aggregator transformation has two group-by ports: CUSTOMER_ID and ORDER_DATE, in that order. The Sorted Input property is set to true. The Order By clause in the SQL for the Source Qualifier contains this syntax: ORDER BY CUSTOMER_ID, ORDER_DATE, MANF_ID. Assuming that the sort order on the database server matches the sort order of the session and everything else is valid, what would happen when this session started?

Choose Answer

(A)
The session would run normally.
(B)
The session would fail, because the columns in the ORDER BY clause in the SQL do not exactly match the group-by ports in the Aggregator transformation.
(C)
The session would fail, because when the Sorted Input property is set to true, no more than one group-by port is allowed.
(D)
The session would run, but sorted data would not be used because the columns in the ORDER BY clause in the SQL does not exactly match the group-by ports in the Aggregator transformation, and this may adversely affect performance.



Aggregator transformation variable port
What type of expressions can be used in an Aggregator transformation variable port?

Choose Answer

(A)
Aggregate or non-aggregate expressions, provided they are not used together in a single variable port.
(B)
Aggregate and non-aggregate expressions, together in the same variable port.
(C)
Aggregate, non-aggregate and conditional expressions, together in the same variable port, provided that the variable port is also made a "group-by" port.
(D)
Non-aggregate and conditional expressions only.



Aggregator transformation
What type of expressions can be used in an Aggregator transformation output port?

Choose Answer

(A)
Aggregate or non-aggregate expressions, provided they are not used together in a single output port.
(B)
Aggregate and non-aggregate expressions, together in the same output port.
(C)
Aggregate, non-aggregate and conditional expressions, together in the same output port.
(D)
Aggregate only.



Outer join
Can a Source Qualifier be used to perform an outer join when joining two database tables?

Choose Answer

(A)
Yes, but it requires that the user modify the join SQL by hand.
(B)
Yes, but it requires that the user select left or right outer join as the Source Qualifier Join Type property.
(C)
No, because this may produce null values in ports and the Source Qualifier does not support default values.
(D)
No, this function is not supported by the Source Qualifier- a Joiner transformation would need to be used.



Join two relational sources
A user desires to join two relational sources with a single Source Qualifier. The sources and the Source Qualifier have been correctly imported into the mapping. Next, she must establish a primary / foreign key relationship between two ports in the source definitions. Assuming that the relationship does not currently exist on the database server, what are the required steps to do this?

Choose Answer

(A)
Use the Source Analyzer to establish the primary / foreign key relationship and save the repository.
(B)
Use the Source Analyzer to establish the primary / foreign key relationship, save the repository, and run the appropriate SQL statement on the database tables to create the actual primary / foreign key relationship.
(C)
Use the Source Analyzer to establish the primary / foreign key relationship, open the SQL Editor in the Source Qualifier and generate the join statement, modify the join statement if required, and save the repository.
(D)
Use the Source Analyzer to establish the primary / foreign key relationship, regenerate the Source Qualifier SQL using the SQL Editor, press the Okay button, and save the repository.



Data source(s)
What type of data source(s) can be read with the Source Qualifier transformation?

Choose Answer

(A)
Relational databases
(B)
Relational databases and flat files
(C)
Relational databases, flat files and Cobol
(D)
Relational databases, flat files, Cobol and certified ERP sources such a Peoplesoft and SAP/R3.



Override the default Source Qualifier join
Under what circumstances should a user override the default Source Qualifier join?

Choose Answer

(A)
The datatypes of the columns used in the join condition do not match.
(B)
The Source Qualifier is being used to join two tables located on different database servers (a heterogeneous join).
(C)
The Source Qualifier is being used to join two or more flat files.
(D)
The order of the Source Qualifier ports has been changed since the mapping was first saved.



Generate a default query
A user finds that she is unable to generate a default query in the Source Qualifier SQL Editor. What is a possible reason for that?

Choose Answer

(A)
The datatypes in the Source Qualifier do not match the corresponding datatypes in the source definition(s) for each field and port.
(B)
The Source Qualifier does not have one or more ports connected to another transformation or target in the mapping.
(C)
The Source Qualifier property "Allow default query" is unchecked (set to false).
(D)
The Source Qualifier is not connected to the source database.



Alter the datatypes in the Source Qualifier
Under what circumstances is it desirable to alter the datatypes in the Source Qualifier?

Choose Answer

(A)
When the precision required by the mapping is less than the precision of the data stored in the table or flat file being read by the Source Qualifier.
(B)
When the precision required by the mapping is greater than the precision of the data stored in the table or flat file being read by the Source Qualifier.
(C)
To alter the way the source database binds data when it is imported into the mapping.
(D)
Never. The datatypes in the Source Qualifier and the corresponding source(s) must match.


Informatica expression reserved word
Can a port in an Expression transformation be given the name DISTINCT ?

Choose Answer

(A)
Yes
(B)
No, because DISTINCT is an ANSI SQL reserved word.
(C)
No, because DISTINCT is an Informatica expression reserved word.
(D)
No, because DISTINCT is both an ANSI SQL and an Informatica expression reserved word.



Informatica Test functions
The following are valid Informatica Test functions:

Choose Answer

(A)
ISNULL, IS_DATE, IS_NUMBER, IS_SPACES
(B)
ISNOTNULL, ISDATE, ISHERE
(C)
IS NULL, ISANUMBER, ISADATE, ISADECIMAL
(D)
NO_DOUBT, IS_LIVE, ISILDOR, ISENGARD



Interpreting expression
A user enters the following expression into an Expression transformation:( PRICE - COST ) QUANTITY. The Expression transformation is part of a mapping with a relational database source definition. Assuming that the expression is valid, when the mapping runs as part of a session, how will the Informatica server treat the above expression?

Choose Answer

(A)
It will interpret the expression directly.
(B)
It will embed the expression into the designated source code file for the session and then compile it so that it can be executed.
(C)
It will include the statement in the SQL select statement that will be submitted to the source database.
(D)
It will embed the expression into the primary session stored procedure script that will be executed on either the source database or another database that was specified by the user.



LTRIM
For a row where the value of the port LAST_NAME is equal to the string 'Damato' what would be the return value of the expression LTRIM ( LAST_NAME, 'A' )?

Choose Answer

(A)
Dmato
(B)
Dmto
(C)
amato
(D)
Damato



Mapping validity
Which situation would always cause a mapping to be invalid?

Choose Answer

(A)
A target definition with no data flowing into it
(B)
Two ports with different data types connected together
(C)
Two active transformations connected to the same transformation
(D)
A Source Qualifier with port names that do not match a field name in the corresponding Source definition.



Data types
A developer wishes to connect two ports that have different data types. Is this allowed?

Choose Answer

(A)
Yes, always
(B)
Yes, provided that the data types are compatible. The Informatica server will automatically convert the original type to match the destination type.
(C)
Yes, provided that the data types are compatible, and the developer writes a valid conversion function to convert the original type to match the destination type.
(D)
No, never.


Working with Expressions
A transformation has the following ports: Port_1: String, Input port; Port_2: String, Input port; Port_3: String, Output port. Which of the expressions shown below would be valid? Assume that it is associated with Port_3 and that the string ENDSTRING is not a parameter or mapping variable.

Choose Answer

(A)
Port_1 Port_2 'ENDSTRING'
(B)
Port_1 Port_2 "ENDSTRING"
(C)
Port_1 Port_2 ENDSTRING
(D)
CONCAT(Port_1, CONCAT(Port_2, "ENDSTRING"))
Posted by ghostrider30 at 12:16 PM 0 comments
Transformation expressions
A transformation has the following ports: Port_1: Decimal, Input port; Port_2: Decimal, Input port; Port_3: String, Input port; Port_4: Decimal, Output port; Port_5: Decimal, Output port. Which of the expressions shown below would be invalid? Assume it is associated with Port_4.

Choose Answer

(A)
IIF ( Port_1 > 5, Port_2 10, Port_2 15 )
(B)
IIF ( Port_1 > 5, Port_1 10 )
(C)
IIF ( Port_5 > 7, Port_5 10, Port_5 15 )
(D)
IIF ( Port_2 > 8, Port_2 10, Port_2 15 )


Session Task
Whether or not a Session Task can be considered to have heterogeneous targets is determined by

Choose Answer

(A)
The Mapping properties alone.
(B)
The Session Task properties alone.
(C)
Either the Mapping properties or the Session Task properties.
(D)
Either the Mapping properties or the Workflow properties.


Editing Target definition
Under what circumstances can a Target definition be edited from the Mapping Designer, within the mapping where that Target definition is being used?

Choose Answer

(A)
When the Target definition is not shared (not a shortcut).
(B)
When the Target definition is not shared (not a shortcut) and when the user has write permission on the folder where the mapping is stored.
(C)
Always
(D)
Neve


Target definitions
What types of Target definitions are supported in a mapping?

Choose Answer

(A)
Relational
(B)
Relational and XML
(C)
Relational, XML and flat file
(D)
Relational, XML, flat file and COBOL

Flat File Wizard
A developer has used the Flat File Wizard to create a fixed-width flat file source definition. This source definition is used in several mappings. The next day, she discovers that the actual flat file that will be accessed by the Informatica server will be comma delimited. What action, if any, should be taken?

Choose Answer

(A)
No action need be taken provided the new file format is valid.
(B)
Edit the Source definition properties to make them correct and save the changes to the repository.
(C)
Launch the Flat File Wizard using the Source definition and edit the properties to make them correct. Save the changes to the repository.
(D)
Delete the Source definition, save the repository, re-import it using the Flat File Wizard and define the properties to make them correct. Add the new Source definition to the affected mappings and save the changes to the repository.
Posted by ghostrider30 at 12:14 PM 0 comments
Primary and foreign key relationship
Can a developer manually define a primary and foreign key relationship between two columns in two different relational Source definitions?

Choose Answer

(A)
Yes, always.
(B)
Yes, provided that the data types and precision matches between the two columns.
(C)
Yes, provided that the data types and precision matches between the two columns and the relationship exists on the physical database tables when the session runs.
(D)
Yes, provided that the data types and precision matches between the two columns. The relationship can be set between the Source definitions regardless of whether the relationship exists on the physical database tables, but if the relationship does not exist in the database, the session may fail if referential integrity is faulty.



Tasks on a port level
Which tasks can be performed on a port level (using one specific port)?

Choose Answer

(A)
Enter expressions, define port variables, override default values
(B)
Enter expressions, define port and Mapping variables, override default values
(C)
Define local and Mapping variables, define parameters, override default values
(D)
Enter expressions, define port and Mapping variables, define parameters
Posted by ghostrider30 at 12:14 PM 0 comments
Supported transformation ports
What types of transformation ports are supported?

Choose Answer

(A)
Input, Output, Variable
(B)
Input, Output, Input / Output, Variable
(C)
Input, Output, Variable, Aggregate
(D)
Input, Output, Input / Output, Variable, Parameter


link a port
A user is attempting to link a port on the left to another port in a different transformation on the right. The Designer will not establish the connector link (blue arrow). What are the possible reasons for this?

Choose Answer

(A)
Link columns mode not set, or left port not an output port
(B)
Link columns mode not set, or left port not an output port, or data type mismatch
(C)
Link columns mode not set, or left port not an input port, or right port not an output port, or data type mismatch
(D)
Link columns mode not set, or left port not an output port, or right port not an input port, or data type mismatch


Copy a Source Definition from a shared folder to a non-shared folder
A user desires to copy a Source Definition from a shared folder to a non-shared folder. Assuming that the user has write permission on the non-shared folder, what must be done in order for this operation to work as intended?

Choose Answer

(A)
The Source Analyzer must be active and the non-shared folder must be open.
(B)
The Source Analyzer must be active, the non-shared folder must be open, and the user must hold down the Ctrl key while dragging.
(C)
The Source Analyzer must be active and the shared folder must be open.
(D)
The Source Analyzer must be active, the shared folder must be open, and the user must hold down the Ctrl key while dragging.


Designer option cannot be set locally
Specify which Designer option cannot be set locally (differently for each client machine).

Choose Answer

(A)
Whether to display column names or business names for all Source Qualifiers
(B)
Whether to import primary keys when importing a target definition
(C)
Whether to import data type lengths and precisions for Source Definitions
(D)
Whether to display targets grouped by database in the Navigator Window
Posted by ghostrider30 at 12:12 PM 0 comments
"Find" search tool
The Designer includes a "Find" search tool as part of the standard toolbar. What can it be used to find?

Choose Answer

(A)
Column or port names in any transformation, source or target definition
(B)
Column or port names in any transformation, source or target definition, and strings in any of the output windows
(C)
Column or port names in any transformation, source or target definition, strings in any of the output windows, and expressions in any transformation
(D)
Column or port names in any transformation, source or target definition, strings in any of the output windows, expressions in any transformation, and descriptive text
Mapplet definition
Refer to the mapping illustrated below. Assume that the mapplet definition is valid and that the individual transformation expressions are valid. Select the statement that is correct.
Choose Answer
(A)
The mapping will be valid as it is.
(B)
The mapping is valid because the third mapplet output group, Update Changes, does not have any output ports connected to a transformation or Target definition.
(C)
The mapping is invalid because you cannot concatenate two or more data flow paths together into one mapplet input group.
(D)
The mapplet is invalid because the number of output ports in each output group does not match.
Input transformation in mapplet
Select the statement below that is true.
Choose Answer
(A)
A mapplet that does not contain an Input transformation is always an active mapplet.
(B)
A mapplet that does not contain an Input transformation is an active mapplet only if it contains at least one active transformation.
(C)
A passive mapplet can be made into an active mapplet by adding one or more Joiner transformations to the mapplet.
(D)
A passive mapplet can be made into an active mapplet by adding one or more Lookup transformations to the mapplet.
Output transformations in a mapplet
How many Output transformations can be placed in a mapplet?
Choose Answer
(A)
One
(B)
One or more
(C)
One or more unless there is at least one Target definition in the mapplet, in which case zero Output transformations are permitted.
(D)
Any number
Homogeneous Source Definitions
How many homogeneous Source Definitions can be placed in a Mapplet?
Choose Answer
(A)
One
(B)
One for each Source Qualifier transformation.
(C)
Zero to one if the mapplet does not contain an Input or ERP Source Qualifier transformation.
(D)
Unlimited
Difference between the Router and Filter transformations
A major difference between the Router and Filter transformations is that filtered rows can be captured in the Router. (T/F)
Choose Answer
(A)
True
(B)
False
Filter transformation
A Filter transformation contains the following condition: ITEM_COST > 100.00. The value for ITEM_COST for a specific row is 35. What will happen to that row?
Choose Answer
(A)
It is dropped from the mapping flow.
(B)
It is dropped from the mapping flow and written to the appropriate reject (bad) file.
(C)
It is dropped from the mapping flow and written to the appropriate reject (bad) file if the Filter property "Forward Filtered Rows" is set to True.
(D)
If the Filter property "Forward Filtered Rows" is set to True, the row is sent to downstream transformations, if any, but not written to the target table.
Router transformation that has three groups defined
Specify which statement below best describes the behavior of a Router transformation that has three groups defined.
Choose Answer
(A)
The condition for each group will be evaluated for each row, and the first group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports.
(B)
The condition for each group will be evaluated for each row, and each group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports.
(C)
The condition for each group will be evaluated for each row, and the first group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports only if those ports are connected to another transformation.
(D)
The condition for each group will be evaluated for each row, and each group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports only if those ports are connected to another transformation.
Update Strategy transformation
It is desired to run a session where every row that is processed by the session updates the target table based on the primary key value. Assume that the target table definition has a primary key, and all other session and mapping properties are left to their defaults. Select the statement below that is true.
Choose Answer
(A)
The only action that is necessary is to set the Treat Source Rows As session property to Update.
(B)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table.
(C)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table, and the Treat Source Rows As session property must be set to Update.
(D)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table, and the Treat Source Rows As session property must be set to Data Driven.
Expressions used in an Update Strategy
What type of expressions can be used in an Update Strategy transformation?
Choose Answer
(A)
Immediate If ( IIF)
(B)
Immediate If (IIF) or DECODE
(C)
Immediate If (IIF), DECODE, or IF THEN ELSE
(D)
Any valid non-aggregate expression that is supported by the transformation language.
Forward Rejected Rows in Update Strategy
Refer to the diagram below. The Update Strategy transformation contains an expression that may tag each row as insert, delete or reject. It is desired to count ALL rows that pass through the Aggregator, regardless of how they are tagged. Assume that the other parts of the mapping are finished and that the Update Strategy transformation property Forward Rejected Rows is set to True. Select the statement that is true.
Choose Answer
(A)
The mapping will work as desired without any further changes.
(B)
The mapping will work as desired only if the Aggregator transformation is moved to be upstream of the Update Strategy transformation.
(C)
The mapping will work as desired only if it is redesigned to use a Router transformation in place of the Update Strategy transformation, and the row operation for each row (insert, delete, or reject) is set by an individual Update Strategy transformation for each flow, and then counted by an individual Aggregator transformation for each flow.
(D)
The mapping will work as desired only if the Update Strategy transformation tags each row only as insert or delete (no rows tagged as reject). Any rows that are not desired in the target table can then be removed from the flow by adding a Filter transformation DOWNSTREAM of the Aggregator transformation, and setting the Filter condition to be logically equivalent to the condition that was used in the Update Strategy transformation to tag the rows as reject.
"Treat source rows as" session property
A user sets the "Treat source rows as" session property to "Update". What is the effect of making this selection?
Choose Answer
(A)
All records processed by the session will be treated as update statements on the target table(s).
(B)
All records processed by the session will be treated as update statements on the target table(s), provided that a primary key constraint exists on the corresponding target table definition(s).
(C)
This selection allows the use of the Update Strategy transformation, provided that one or more Update Strategy transformations are present in the mapping run by the session.
(D)
This selection allows the use of the Update Strategy transformation, provided that one or more Update Strategy transformations are present in the mapping run by the session, and provided that a primary key constraint exists on the corresponding target table definition(s).
Case-sensitive string comparison
Can the Joiner transformation perform a case-sensitive string comparison as part of the join condition?
Choose Answer
(A)
Yes, always
(B)
No, the comparison is never case sensitive.
(C)
Yes when the Informatica server is running on the Unix operating system, no when it is running on a Windows operating system.
(D)
Yes, when the database that is being used for the join supports case-sensitive string comparisons.
Full outer join
A developer is using a Joiner transformation to join two data streams (sources), and a full outer join is desired. Select the statement below that is true.
Choose Answer
(A)
A Joiner transformations may perform a full outer join under all conditions.
(B)
A Joiner transformation may perform a full outer join only when one or more sides of the join are from a relational database.
(C)
A Joiner transformation may perform a full outer join only when all sides of the join originate from relational databases, and one or more of those databases support outer joins.
(D)
A Joiner transformation may perform a full outer join only when no file sources are involved in the join (both sides are relational).
3 or more data sources connected to a Joiner
Choose Answer
(A)
The mapping is valid as it is.
(B)
The mapping is invalid because there is an active transformation between SQ_ITEMS and the Joiner transformation. If the Aggregator transformation was eliminated or replaced with a passive transformation, the mapping would be valid.
(C)
The mapping is valid only if the SQ_ITEMS side of the join is designated as the master side in the Joiner transformation.
(D)
The mapping is invalid because it is not permitted to have three or more data sources connected to a Joiner transformation under any circumstances.
Joiner
Refer to the diagram below, which shows part of a mapping. Assume that each individual transformation and transformation expression in the mapping is valid, and that the part of the mapping that is not visible (downstream of the Joiner) is valid. Select the statement that is true.
Choose Answer
(A)
The mapping is valid as it is.
(B)
The mapping is invalid because a Joiner transformation must be the first transformation after the Source Qualifier, and in this mapping there are two transformations between SQ_ITEMS and the Joiner transformation.
(C)
The mapping will be valid only if the SQ_ITEMS side of the join is designated as the detail side in the Joiner transformation.
(D)
The mapping will be valid only if the SQ_ITEMS side of the join is designated as the master side in the Joiner transformation.


Refer to the diagram below, which shows part of a mapping. Assume that each individual transformation and transformation expression is valid. Select the statement that is true.
Joiner transformations
It is desired to join together a database table, a flat file and an XML file. All three sources have a single common field called CUSTOMER_ID. What is the minimum number of Joiner transformations that are needed to join these sources?
Choose Answer
(A)
One
(B)
Two
(C)
Three
(D)
One if the flat file and the XML file are located in the same directory, two if they are located in different directories.
Set a Lookup condition
A developer is attempting to set a Lookup condition and finds that the New Condition button is disabled, making it impossible to set a condition. Assume that it is a connected Lookup. What is a possible reason for this?
Choose Answer
(A)
There are no designated Lookup ports in the ports tab section.
(B)
There are no designated Input ports in the ports tab section.
(C)
There are no ports with compatible data types.
(D)
At least one Output port has not been connected to another transformation.
Override the SQL of a non-shared, uncached Lookup transformation
It is desired to override the SQL of a non-shared, uncached Lookup transformation in order to add a WHERE clause. Select the action below that MUST be taken to allow the session to run as designed. Assume that the session has already been tested prior to the override and it is satisfactory.
Choose Answer
(A)
Cache the Lookup
(B)
Include an ORDER BY clause in the SQL that sorts the lookup field(s) in descending order.
(C)
Generate a default query after the override SQL has been entered.
(D)
Validate the override SQL on the actual database table.



Lookup transformation where cache is persistent
Select the Lookup transformation scenario below which is not supported. Assume that the Lookup cache is persistent, it is shared and named, and there is only one Lookup transformation used in the mapping or mapplet.

Choose Answer

(A)
Static cacheCondition: sales_amt > sales_amt1Unconnected
(B)
Static cacheCondition: sales_amt = sales_amt1 Unconnected
(C)
Dynamic cacheCondition: sales_amt > sales_amt1Connected
(D)
Dynamic cacheCondition: sales_amt = sales_amt1Connected



Dynamic lookup cache
Select the statement below that most accurately describes how a dynamic lookup cache is designed to function.

Choose Answer

(A)
The Informatica server compares the target data to the cache data as each row is passed to the target and updates the cache as required.
(B)
The Informatica server inserts rows into the cache or updates rows in the cache as it passes rows to the target.
(C)
The Informatica server updates the cache as the target table data changes.
(D)
The Informatica server inserts, updates or deletes rows in the cache as it passes rows to the target.


Source Type Lookup transformation
What type of data sources are supported in the Source Type Lookup transformation property?

Choose Answer

(A)
Database
(B)
Database and flat file
(C)
Database, flat file, and COBOL file
(D)
Database, flat file, COBOL file, and XML file



Unconnected Lookup transformation
What is one disadvantage of using an unconnected (sometimes called function mode) Lookup transformation?

Choose Answer

(A)
If there is no match in the Lookup condition, the Informatica server returns a transformation error.
(B)
A reusable unconnected Lookup can not have more than one instance in a mapping.
(C)
An unconnected Lookup transformation does not support user-defined default values.
(D)
An unconnected Lookup transformation does not support a static lookup cache.
Posted by ghostrider30 at 12:26 PM 1 comments
NEXTVAL port of a Sequence Generator
Can the NEXTVAL port of a Sequence Generator transformation be connected to the PRIMARY KEY column of two different target table definitions in a mapping? Assume that the two definitions represent different physical tables on the database server.

Choose Answer

(A)
Yes, always.
(B)
Yes, but only if the two tables share a primary - foreign key (referential integrity) relationship.
(C)
No, because the Sequence Generator produces unique values (within the selected range) and this would result in a unique constraint error (duplicate key values) in one or both of the tables.
(D)
No, this is not allowed in the Mapping Designer.



Sequence Generator transformation
What is the difference between the NEXTVAL and the CURRVAL ports of the Sequence Generator transformation? Assume that they are both connected to the input ports of another transformation.

Choose Answer

(A)
The Informatica server generates a sequence of numbers for NEXTVAL, and CURRVAL is set to NEXTVAL plus one.
(B)
The Informatica server generates a sequence of numbers for NEXTVAL, and CURRVAL is set to NEXTVAL minus one.
(C)
The Informatica server generates a sequence of numbers for CURVALL, and NEXTVAL is set to CURRVAL plus one.
(D)
The Informatica server generates a sequence of numbers for CURVALL, and NEXTVAL is set to CURRVAL plus the integer value set for the "Increment By" Sequence Generator property.



Active transformations
Why is the sorter transformation considered an active transformation?

Choose Answer

(A)
When the Case Sensitive property is set to true (checked), rows that do not meet the case criteria in the sort may be discarded.
(B)
When more than one sort key is selected, nested sorts may result in a single input row being outputted more than one time within multiple sort sequences.
(C)
When the Distinct Output Rows property is set to true (checked), duplicate rows are discarded.
(D)
When the sort direction properties are not the same for all sort keys (not all ascending or all descending), a single input row may be outputted more than one time.



Sort a very large data set
When using the sorter transformation to sort a very large data set, additional RAM resources may be required to accommodate the data. Which machine would provide this RAM?

Choose Answer

(A)
The Informatica server machine.
(B)
The source database server machine.
(C)
The target database server machine.
(D)
The source database server machine when reading from a relational source, or the target database server machine when reading from a flat file, COBOL or XML source.
Posted by ghostrider30 at 12:25 PM 1 comments
Sorter transformation
When a sorter transformation is included in a Mapping that is run in a Workflow, when does the data sort occur?

Choose Answer

(A)
Before the Workflow runs.
(B)
After the Workflow runs.
(C)
Either before or after the Workflow runs, depending on the sorter properties.
(D)
During the time the Workflow runs (Session Task run time).



Sorter transformation
Select the statement below that is true.

Choose Answer

(A)
The Sorter transformation must be used immediately downstream of a Source Qualifier transformation.
(B)
The Sorter transformation must be used immediately downstream of a Source Qualifier transformation that reads from a relational source.
(C)
The Sorter transformation supports multiple sort levels, so any sort key can have a secondary sort associated with it.
(D)
The user is allowed to modify the ORDER BY clause generated by the Sorter transformation.



Aggregator data RAM cache
By default, the Aggregator data RAM cache size is 2 Mbytes, and the index RAM cache is 1 Mbyte. What would happen if a user ran a session that required a data RAM cache of 3 Mbytes and the data cache size was left at the default?

Choose Answer

(A)
The session would fail when the Aggregator begins processing data.
(B)
The session would fail when the data RAM cache exceeds 2 Mbytes.
(C)
The Informatica server would automatically expand the data RAM cache to accommodate the additional data (provided the RAM is available on the machine).
(D)
The Informatica server would automatically page the data to a file on the local disk.



Aggregator data cache size.
An Aggregator transformation will process 50 million rows per session, and the width of each row is 3 KBytes. There are two group-by ports, MONTH and CUSTOMER. Assuming that the data set contains two years worth of data, and that there are 500 customers who are represented for each month in the data set, estimate the required data cache size.

Choose Answer

(A)
6 MBytes
(B)
12 MBytes
(C)
18 MBytes
(D)
36 MBytes



Aggregate cache
The aggregate cache always consists of

Choose Answer

(A)
An index and data cache in RAM, and an index and data file on the local disk.
(B)
An index and data cache in RAM, and an index and data cache on the local disk if the RAM caches cannot contain all of the intermediate results in the data set.
(C)
An index and data cache in RAM, and an index and data file on the local disk if the file names and directory (or Server Variable) have been specified as an Aggregator property.
(D)
An index and data cache in RAM, and an index and data file on the local disk if the Incremental Aggregation property has been set to true when the session runs.



Sorted Input in Aggregator transformation
An Aggregator transformation has two group-by ports: CUSTOMER_ID and ORDER_DATE, in that order. The Sorted Input property is set to true. The Order By clause in the SQL for the Source Qualifier contains this syntax: ORDER BY CUSTOMER_ID, ORDER_DATE, MANF_ID. Assuming that the sort order on the database server matches the sort order of the session and everything else is valid, what would happen when this session started?

Choose Answer

(A)
The session would run normally.
(B)
The session would fail, because the columns in the ORDER BY clause in the SQL do not exactly match the group-by ports in the Aggregator transformation.
(C)
The session would fail, because when the Sorted Input property is set to true, no more than one group-by port is allowed.
(D)
The session would run, but sorted data would not be used because the columns in the ORDER BY clause in the SQL does not exactly match the group-by ports in the Aggregator transformation, and this may adversely affect performance.
Posted by ghostrider30 at 12:22 PM 0 comments
Aggregator transformation variable port
What type of expressions can be used in an Aggregator transformation variable port?

Choose Answer

(A)
Aggregate or non-aggregate expressions, provided they are not used together in a single variable port.
(B)
Aggregate and non-aggregate expressions, together in the same variable port.
(C)
Aggregate, non-aggregate and conditional expressions, together in the same variable port, provided that the variable port is also made a "group-by" port.
(D)
Non-aggregate and conditional expressions only.



Aggregator transformation
What type of expressions can be used in an Aggregator transformation output port?

Choose Answer

(A)
Aggregate or non-aggregate expressions, provided they are not used together in a single output port.
(B)
Aggregate and non-aggregate expressions, together in the same output port.
(C)
Aggregate, non-aggregate and conditional expressions, together in the same output port.
(D)
Aggregate only.


Outer join
Can a Source Qualifier be used to perform an outer join when joining two database tables?

Choose Answer

(A)
Yes, but it requires that the user modify the join SQL by hand.
(B)
Yes, but it requires that the user select left or right outer join as the Source Qualifier Join Type property.
(C)
No, because this may produce null values in ports and the Source Qualifier does not support default values.
(D)
No, this function is not supported by the Source Qualifier- a Joiner transformation would need to be used.



Join two relational sources
A user desires to join two relational sources with a single Source Qualifier. The sources and the Source Qualifier have been correctly imported into the mapping. Next, she must establish a primary / foreign key relationship between two ports in the source definitions. Assuming that the relationship does not currently exist on the database server, what are the required steps to do this?

Choose Answer

(A)
Use the Source Analyzer to establish the primary / foreign key relationship and save the repository.
(B)
Use the Source Analyzer to establish the primary / foreign key relationship, save the repository, and run the appropriate SQL statement on the database tables to create the actual primary / foreign key relationship.
(C)
Use the Source Analyzer to establish the primary / foreign key relationship, open the SQL Editor in the Source Qualifier and generate the join statement, modify the join statement if required, and save the repository.
(D)
Use the Source Analyzer to establish the primary / foreign key relationship, regenerate the Source Qualifier SQL using the SQL Editor, press the Okay button, and save the repository.



Data source(s)
What type of data source(s) can be read with the Source Qualifier transformation?

Choose Answer

(A)
Relational databases
(B)
Relational databases and flat files
(C)
Relational databases, flat files and Cobol
(D)
Relational databases, flat files, Cobol and certified ERP sources such a Peoplesoft and SAP/R3.



Override the default Source Qualifier join
Under what circumstances should a user override the default Source Qualifier join?

Choose Answer

(A)
The datatypes of the columns used in the join condition do not match.
(B)
The Source Qualifier is being used to join two tables located on different database servers (a heterogeneous join).
(C)
The Source Qualifier is being used to join two or more flat files.
(D)
The order of the Source Qualifier ports has been changed since the mapping was first saved.



Generate a default query
A user finds that she is unable to generate a default query in the Source Qualifier SQL Editor. What is a possible reason for that?

Choose Answer

(A)
The datatypes in the Source Qualifier do not match the corresponding datatypes in the source definition(s) for each field and port.
(B)
The Source Qualifier does not have one or more ports connected to another transformation or target in the mapping.
(C)
The Source Qualifier property "Allow default query" is unchecked (set to false).
(D)
The Source Qualifier is not connected to the source database.
Posted by ghostrider30 at 12:20 PM 0 comments
Alter the datatypes in the Source Qualifier
Under what circumstances is it desirable to alter the datatypes in the Source Qualifier?

Choose Answer

(A)
When the precision required by the mapping is less than the precision of the data stored in the table or flat file being read by the Source Qualifier.
(B)
When the precision required by the mapping is greater than the precision of the data stored in the table or flat file being read by the Source Qualifier.
(C)
To alter the way the source database binds data when it is imported into the mapping.
(D)
Never. The datatypes in the Source Qualifier and the corresponding source(s) must match.



Informatica expression reserved word
Can a port in an Expression transformation be given the name DISTINCT ?

Choose Answer

(A)
Yes
(B)
No, because DISTINCT is an ANSI SQL reserved word.
(C)
No, because DISTINCT is an Informatica expression reserved word.
(D)
No, because DISTINCT is both an ANSI SQL and an Informatica expression reserved word.



Informatica Test functions
The following are valid Informatica Test functions:

Choose Answer

(A)
ISNULL, IS_DATE, IS_NUMBER, IS_SPACES
(B)
ISNOTNULL, ISDATE, ISHERE
(C)
IS NULL, ISANUMBER, ISADATE, ISADECIMAL
(D)
NO_DOUBT, IS_LIVE, ISILDOR, ISENGARD



Interpreting expression
A user enters the following expression into an Expression transformation:( PRICE - COST ) QUANTITY. The Expression transformation is part of a mapping with a relational database source definition. Assuming that the expression is valid, when the mapping runs as part of a session, how will the Informatica server treat the above expression?

Choose Answer

(A)
It will interpret the expression directly.
(B)
It will embed the expression into the designated source code file for the session and then compile it so that it can be executed.
(C)
It will include the statement in the SQL select statement that will be submitted to the source database.
(D)
It will embed the expression into the primary session stored procedure script that will be executed on either the source database or another database that was specified by the user.



LTRIM
For a row where the value of the port LAST_NAME is equal to the string 'Damato' what would be the return value of the expression LTRIM ( LAST_NAME, 'A' )?

Choose Answer

(A)
Dmato
(B)
Dmto
(C)
amato
(D)
Damato



Mapping validity
Which situation would always cause a mapping to be invalid?

Choose Answer

(A)
A target definition with no data flowing into it
(B)
Two ports with different data types connected together
(C)
Two active transformations connected to the same transformation
(D)
A Source Qualifier with port names that do not match a field name in the corresponding Source definition.


Data types
A developer wishes to connect two ports that have different data types. Is this allowed?

Choose Answer

(A)
Yes, always
(B)
Yes, provided that the data types are compatible. The Informatica server will automatically convert the original type to match the destination type.
(C)
Yes, provided that the data types are compatible, and the developer writes a valid conversion function to convert the original type to match the destination type.
(D)
No, never.


Working with Expressions
A transformation has the following ports: Port_1: String, Input port; Port_2: String, Input port; Port_3: String, Output port. Which of the expressions shown below would be valid? Assume that it is associated with Port_3 and that the string ENDSTRING is not a parameter or mapping variable.

Choose Answer

(A)
Port_1 Port_2 'ENDSTRING'
(B)
Port_1 Port_2 "ENDSTRING"
(C)
Port_1 Port_2 ENDSTRING
(D)
CONCAT(Port_1, CONCAT(Port_2, "ENDSTRING"))
Posted by ghostrider30 at 12:16 PM 0 comments
Transformation expressions
A transformation has the following ports: Port_1: Decimal, Input port; Port_2: Decimal, Input port; Port_3: String, Input port; Port_4: Decimal, Output port; Port_5: Decimal, Output port. Which of the expressions shown below would be invalid? Assume it is associated with Port_4.

Choose Answer

(A)
IIF ( Port_1 > 5, Port_2 10, Port_2 15 )
(B)
IIF ( Port_1 > 5, Port_1 10 )
(C)
IIF ( Port_5 > 7, Port_5 10, Port_5 15 )
(D)
IIF ( Port_2 > 8, Port_2 10, Port_2 15 )
Posted by ghostrider30 at 12:16 PM 0 comments
Session Task
Whether or not a Session Task can be considered to have heterogeneous targets is determined by

Choose Answer

(A)
The Mapping properties alone.
(B)
The Session Task properties alone.
(C)
Either the Mapping properties or the Session Task properties.
(D)
Either the Mapping properties or the Workflow properties.
Posted by ghostrider30 at 12:15 PM 0 comments
Editing Target definition
Under what circumstances can a Target definition be edited from the Mapping Designer, within the mapping where that Target definition is being used?

Choose Answer

(A)
When the Target definition is not shared (not a shortcut).
(B)
When the Target definition is not shared (not a shortcut) and when the user has write permission on the folder where the mapping is stored.
(C)
Always
(D)
Neve
Posted by ghostrider30 at 12:15 PM 0 comments
Target definitions
What types of Target definitions are supported in a mapping?

Choose Answer

(A)
Relational
(B)
Relational and XML
(C)
Relational, XML and flat file
(D)
Relational, XML, flat file and COBOL
Posted by ghostrider30 at 12:15 PM 0 comments
Flat File Wizard
A developer has used the Flat File Wizard to create a fixed-width flat file source definition. This source definition is used in several mappings. The next day, she discovers that the actual flat file that will be accessed by the Informatica server will be comma delimited. What action, if any, should be taken?

Choose Answer

(A)
No action need be taken provided the new file format is valid.
(B)
Edit the Source definition properties to make them correct and save the changes to the repository.
(C)
Launch the Flat File Wizard using the Source definition and edit the properties to make them correct. Save the changes to the repository.
(D)
Delete the Source definition, save the repository, re-import it using the Flat File Wizard and define the properties to make them correct. Add the new Source definition to the affected mappings and save the changes to the repository.
Posted by ghostrider30 at 12:14 PM 0 comments
Primary and foreign key relationship
Can a developer manually define a primary and foreign key relationship between two columns in two different relational Source definitions?

Choose Answer

(A)
Yes, always.
(B)
Yes, provided that the data types and precision matches between the two columns.
(C)
Yes, provided that the data types and precision matches between the two columns and the relationship exists on the physical database tables when the session runs.
(D)
Yes, provided that the data types and precision matches between the two columns. The relationship can be set between the Source definitions regardless of whether the relationship exists on the physical database tables, but if the relationship does not exist in the database, the session may fail if referential integrity is faulty.



Tasks on a port level
Which tasks can be performed on a port level (using one specific port)?

Choose Answer

(A)
Enter expressions, define port variables, override default values
(B)
Enter expressions, define port and Mapping variables, override default values
(C)
Define local and Mapping variables, define parameters, override default values
(D)
Enter expressions, define port and Mapping variables, define parameters
Posted by ghostrider30 at 12:14 PM 0 comments
Supported transformation ports
What types of transformation ports are supported?

Choose Answer

(A)
Input, Output, Variable
(B)
Input, Output, Input / Output, Variable
(C)
Input, Output, Variable, Aggregate
(D)
Input, Output, Input / Output, Variable, Parameter



link a port
A user is attempting to link a port on the left to another port in a different transformation on the right. The Designer will not establish the connector link (blue arrow). What are the possible reasons for this?

Choose Answer

(A)
Link columns mode not set, or left port not an output port
(B)
Link columns mode not set, or left port not an output port, or data type mismatch
(C)
Link columns mode not set, or left port not an input port, or right port not an output port, or data type mismatch
(D)
Link columns mode not set, or left port not an output port, or right port not an input port, or data type mismatch



Copy a Source Definition from a shared folder to a non-shared folder
A user desires to copy a Source Definition from a shared folder to a non-shared folder. Assuming that the user has write permission on the non-shared folder, what must be done in order for this operation to work as intended?

Choose Answer

(A)
The Source Analyzer must be active and the non-shared folder must be open.
(B)
The Source Analyzer must be active, the non-shared folder must be open, and the user must hold down the Ctrl key while dragging.
(C)
The Source Analyzer must be active and the shared folder must be open.
(D)
The Source Analyzer must be active, the shared folder must be open, and the user must hold down the Ctrl key while dragging.


Designer option cannot be set locally
Specify which Designer option cannot be set locally (differently for each client machine).

Choose Answer

(A)
Whether to display column names or business names for all Source Qualifiers
(B)
Whether to import primary keys when importing a target definition
(C)
Whether to import data type lengths and precisions for Source Definitions
(D)
Whether to display targets grouped by database in the Navigator Window

"Find" search tool
The Designer includes a "Find" search tool as part of the standard toolbar. What can it be used to find?

Choose Answer

(A)
Column or port names in any transformation, source or target definition
(B)
Column or port names in any transformation, source or target definition, and strings in any of the output windows
(C)
Column or port names in any transformation, source or target definition, strings in any of the output windows, and expressions in any transformation
(D)
Column or port names in any transformation, source or target definition, strings in any of the output windows, expressions in any transformation, and descriptive text
Mapplet definition
Refer to the mapping illustrated below. Assume that the mapplet definition is valid and that the individual transformation expressions are valid. Select the statement that is correct.
Choose Answer
(A)
The mapping will be valid as it is.
(B)
The mapping is valid because the third mapplet output group, Update Changes, does not have any output ports connected to a transformation or Target definition.
(C)
The mapping is invalid because you cannot concatenate two or more data flow paths together into one mapplet input group.
(D)
The mapplet is invalid because the number of output ports in each output group does not match.
Input transformation in mapplet
Select the statement below that is true.
Choose Answer
(A)
A mapplet that does not contain an Input transformation is always an active mapplet.
(B)
A mapplet that does not contain an Input transformation is an active mapplet only if it contains at least one active transformation.
(C)
A passive mapplet can be made into an active mapplet by adding one or more Joiner transformations to the mapplet.
(D)
A passive mapplet can be made into an active mapplet by adding one or more Lookup transformations to the mapplet.
Output transformations in a mapplet
How many Output transformations can be placed in a mapplet?
Choose Answer
(A)
One
(B)
One or more
(C)
One or more unless there is at least one Target definition in the mapplet, in which case zero Output transformations are permitted.
(D)
Any number
Homogeneous Source Definitions
How many homogeneous Source Definitions can be placed in a Mapplet?
Choose Answer
(A)
One
(B)
One for each Source Qualifier transformation.
(C)
Zero to one if the mapplet does not contain an Input or ERP Source Qualifier transformation.
(D)
Unlimited
Difference between the Router and Filter transformations
A major difference between the Router and Filter transformations is that filtered rows can be captured in the Router. (T/F)
Choose Answer
(A)
True
(B)
False
Filter transformation
A Filter transformation contains the following condition: ITEM_COST > 100.00. The value for ITEM_COST for a specific row is 35. What will happen to that row?
Choose Answer
(A)
It is dropped from the mapping flow.
(B)
It is dropped from the mapping flow and written to the appropriate reject (bad) file.
(C)
It is dropped from the mapping flow and written to the appropriate reject (bad) file if the Filter property "Forward Filtered Rows" is set to True.
(D)
If the Filter property "Forward Filtered Rows" is set to True, the row is sent to downstream transformations, if any, but not written to the target table.
Router transformation that has three groups defined
Specify which statement below best describes the behavior of a Router transformation that has three groups defined.
Choose Answer
(A)
The condition for each group will be evaluated for each row, and the first group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports.
(B)
The condition for each group will be evaluated for each row, and each group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports.
(C)
The condition for each group will be evaluated for each row, and the first group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports only if those ports are connected to another transformation.
(D)
The condition for each group will be evaluated for each row, and each group that evaluates to a "true" result will pass the row out of the transformation through that group's output ports. If no group evaluates to a "true" result, the row will pass out of the transformation through the Default group output ports only if those ports are connected to another transformation.
Update Strategy transformation
It is desired to run a session where every row that is processed by the session updates the target table based on the primary key value. Assume that the target table definition has a primary key, and all other session and mapping properties are left to their defaults. Select the statement below that is true.
Choose Answer
(A)
The only action that is necessary is to set the Treat Source Rows As session property to Update.
(B)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table.
(C)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table, and the Treat Source Rows As session property must be set to Update.
(D)
At least one Update Strategy transformation must be present in the mapping, and the update expression condition must be written to be true for all rows that are desired to update the target table, and the Treat Source Rows As session property must be set to Data Driven.
Expressions used in an Update Strategy
What type of expressions can be used in an Update Strategy transformation?
Choose Answer
(A)
Immediate If ( IIF)
(B)
Immediate If (IIF) or DECODE
(C)
Immediate If (IIF), DECODE, or IF THEN ELSE
(D)
Any valid non-aggregate expression that is supported by the transformation language.
Forward Rejected Rows in Update Strategy
Refer to the diagram below. The Update Strategy transformation contains an expression that may tag each row as insert, delete or reject. It is desired to count ALL rows that pass through the Aggregator, regardless of how they are tagged. Assume that the other parts of the mapping are finished and that the Update Strategy transformation property Forward Rejected Rows is set to True. Select the statement that is true.
Choose Answer
(A)
The mapping will work as desired without any further changes.
(B)
The mapping will work as desired only if the Aggregator transformation is moved to be upstream of the Update Strategy transformation.
(C)
The mapping will work as desired only if it is redesigned to use a Router transformation in place of the Update Strategy transformation, and the row operation for each row (insert, delete, or reject) is set by an individual Update Strategy transformation for each flow, and then counted by an individual Aggregator transformation for each flow.
(D)
The mapping will work as desired only if the Update Strategy transformation tags each row only as insert or delete (no rows tagged as reject). Any rows that are not desired in the target table can then be removed from the flow by adding a Filter transformation DOWNSTREAM of the Aggregator transformation, and setting the Filter condition to be logically equivalent to the condition that was used in the Update Strategy transformation to tag the rows as reject.
"Treat source rows as" session property
A user sets the "Treat source rows as" session property to "Update". What is the effect of making this selection?
Choose Answer
(A)
All records processed by the session will be treated as update statements on the target table(s).
(B)
All records processed by the session will be treated as update statements on the target table(s), provided that a primary key constraint exists on the corresponding target table definition(s).
(C)
This selection allows the use of the Update Strategy transformation, provided that one or more Update Strategy transformations are present in the mapping run by the session.
(D)
This selection allows the use of the Update Strategy transformation, provided that one or more Update Strategy transformations are present in the mapping run by the session, and provided that a primary key constraint exists on the corresponding target table definition(s).
Case-sensitive string comparison
Can the Joiner transformation perform a case-sensitive string comparison as part of the join condition?
Choose Answer
(A)
Yes, always
(B)
No, the comparison is never case sensitive.
(C)
Yes when the Informatica server is running on the Unix operating system, no when it is running on a Windows operating system.
(D)
Yes, when the database that is being used for the join supports case-sensitive string comparisons.
Full outer join
A developer is using a Joiner transformation to join two data streams (sources), and a full outer join is desired. Select the statement below that is true.
Choose Answer
(A)
A Joiner transformations may perform a full outer join under all conditions.
(B)
A Joiner transformation may perform a full outer join only when one or more sides of the join are from a relational database.
(C)
A Joiner transformation may perform a full outer join only when all sides of the join originate from relational databases, and one or more of those databases support outer joins.
(D)
A Joiner transformation may perform a full outer join only when no file sources are involved in the join (both sides are relational).
3 or more data sources connected to a Joiner
Choose Answer
(A)
The mapping is valid as it is.
(B)
The mapping is invalid because there is an active transformation between SQ_ITEMS and the Joiner transformation. If the Aggregator transformation was eliminated or replaced with a passive transformation, the mapping would be valid.
(C)
The mapping is valid only if the SQ_ITEMS side of the join is designated as the master side in the Joiner transformation.
(D)
The mapping is invalid because it is not permitted to have three or more data sources connected to a Joiner transformation under any circumstances.
Joiner
Refer to the diagram below, which shows part of a mapping. Assume that each individual transformation and transformation expression in the mapping is valid, and that the part of the mapping that is not visible (downstream of the Joiner) is valid. Select the statement that is true.
Choose Answer
(A)
The mapping is valid as it is.
(B)
The mapping is invalid because a Joiner transformation must be the first transformation after the Source Qualifier, and in this mapping there are two transformations between SQ_ITEMS and the Joiner transformation.
(C)
The mapping will be valid only if the SQ_ITEMS side of the join is designated as the detail side in the Joiner transformation.
(D)
The mapping will be valid only if the SQ_ITEMS side of the join is designated as the master side in the Joiner transformation.


Refer to the diagram below, which shows part of a mapping. Assume that each individual transformation and transformation expression is valid. Select the statement that is true.
Joiner transformations
It is desired to join together a database table, a flat file and an XML file. All three sources have a single common field called CUSTOMER_ID. What is the minimum number of Joiner transformations that are needed to join these sources?
Choose Answer
(A)
One
(B)
Two
(C)
Three
(D)
One if the flat file and the XML file are located in the same directory, two if they are located in different directories.
Set a Lookup condition
A developer is attempting to set a Lookup condition and finds that the New Condition button is disabled, making it impossible to set a condition. Assume that it is a connected Lookup. What is a possible reason for this?
Choose Answer
(A)
There are no designated Lookup ports in the ports tab section.
(B)
There are no designated Input ports in the ports tab section.
(C)
There are no ports with compatible data types.
(D)
At least one Output port has not been connected to another transformation.
Override the SQL of a non-shared, uncached Lookup transformation
It is desired to override the SQL of a non-shared, uncached Lookup transformation in order to add a WHERE clause. Select the action below that MUST be taken to allow the session to run as designed. Assume that the session has already been tested prior to the override and it is satisfactory.
Choose Answer
(A)
Cache the Lookup
(B)
Include an ORDER BY clause in the SQL that sorts the lookup field(s) in descending order.
(C)
Generate a default query after the override SQL has been entered.
(D)
Validate the override SQL on the actual database table.



Lookup transformation where cache is persistent
Select the Lookup transformation scenario below which is not supported. Assume that the Lookup cache is persistent, it is shared and named, and there is only one Lookup transformation used in the mapping or mapplet.

Choose Answer

(A)
Static cacheCondition: sales_amt > sales_amt1Unconnected
(B)
Static cacheCondition: sales_amt = sales_amt1 Unconnected
(C)
Dynamic cacheCondition: sales_amt > sales_amt1Connected
(D)
Dynamic cacheCondition: sales_amt = sales_amt1Connected



Dynamic lookup cache
Select the statement below that most accurately describes how a dynamic lookup cache is designed to function.

Choose Answer

(A)
The Informatica server compares the target data to the cache data as each row is passed to the target and updates the cache as required.
(B)
The Informatica server inserts rows into the cache or updates rows in the cache as it passes rows to the target.
(C)
The Informatica server updates the cache as the target table data changes.
(D)
The Informatica server inserts, updates or deletes rows in the cache as it passes rows to the target.



Source Type Lookup transformation
What type of data sources are supported in the Source Type Lookup transformation property?

Choose Answer

(A)
Database
(B)
Database and flat file
(C)
Database, flat file, and COBOL file
(D)
Database, flat file, COBOL file, and XML file



Unconnected Lookup transformation
What is one disadvantage of using an unconnected (sometimes called function mode) Lookup transformation?

Choose Answer

(A)
If there is no match in the Lookup condition, the Informatica server returns a transformation error.
(B)
A reusable unconnected Lookup can not have more than one instance in a mapping.
(C)
An unconnected Lookup transformation does not support user-defined default values.
(D)
An unconnected Lookup transformation does not support a static lookup cache.



NEXTVAL port of a Sequence Generator
Can the NEXTVAL port of a Sequence Generator transformation be connected to the PRIMARY KEY column of two different target table definitions in a mapping? Assume that the two definitions represent different physical tables on the database server.

Choose Answer

(A)
Yes, always.
(B)
Yes, but only if the two tables share a primary - foreign key (referential integrity) relationship.
(C)
No, because the Sequence Generator produces unique values (within the selected range) and this would result in a unique constraint error (duplicate key values) in one or both of the tables.
(D)
No, this is not allowed in the Mapping Designer.



Sequence Generator transformation
What is the difference between the NEXTVAL and the CURRVAL ports of the Sequence Generator transformation? Assume that they are both connected to the input ports of another transformation.

Choose Answer

(A)
The Informatica server generates a sequence of numbers for NEXTVAL, and CURRVAL is set to NEXTVAL plus one.
(B)
The Informatica server generates a sequence of numbers for NEXTVAL, and CURRVAL is set to NEXTVAL minus one.
(C)
The Informatica server generates a sequence of numbers for CURVALL, and NEXTVAL is set to CURRVAL plus one.
(D)
The Informatica server generates a sequence of numbers for CURVALL, and NEXTVAL is set to CURRVAL plus the integer value set for the "Increment By" Sequence Generator property.



Active transformations
Why is the sorter transformation considered an active transformation?

Choose Answer

(A)
When the Case Sensitive property is set to true (checked), rows that do not meet the case criteria in the sort may be discarded.
(B)
When more than one sort key is selected, nested sorts may result in a single input row being outputted more than one time within multiple sort sequences.
(C)
When the Distinct Output Rows property is set to true (checked), duplicate rows are discarded.
(D)
When the sort direction properties are not the same for all sort keys (not all ascending or all descending), a single input row may be outputted more than one time.



Sort a very large data set
When using the sorter transformation to sort a very large data set, additional RAM resources may be required to accommodate the data. Which machine would provide this RAM?

Choose Answer

(A)
The Informatica server machine.
(B)
The source database server machine.
(C)
The target database server machine.
(D)
The source database server machine when reading from a relational source, or the target database server machine when reading from a flat file, COBOL or XML source.
Posted by ghostrider30 at 12:25 PM 1 comments
Sorter transformation
When a sorter transformation is included in a Mapping that is run in a Workflow, when does the data sort occur?

Choose Answer

(A)
Before the Workflow runs.
(B)
After the Workflow runs.
(C)
Either before or after the Workflow runs, depending on the sorter properties.
(D)
During the time the Workflow runs (Session Task run time).



Sorter transformation
Select the statement below that is true.

Choose Answer

(A)
The Sorter transformation must be used immediately downstream of a Source Qualifier transformation.
(B)
The Sorter transformation must be used immediately downstream of a Source Qualifier transformation that reads from a relational source.
(C)
The Sorter transformation supports multiple sort levels, so any sort key can have a secondary sort associated with it.
(D)
The user is allowed to modify the ORDER BY clause generated by the Sorter transformation.
Posted by ghostrider30 at 12:24 PM 1 comments
Aggregator data RAM cache
By default, the Aggregator data RAM cache size is 2 Mbytes, and the index RAM cache is 1 Mbyte. What would happen if a user ran a session that required a data RAM cache of 3 Mbytes and the data cache size was left at the default?

Choose Answer

(A)
The session would fail when the Aggregator begins processing data.
(B)
The session would fail when the data RAM cache exceeds 2 Mbytes.
(C)
The Informatica server would automatically expand the data RAM cache to accommodate the additional data (provided the RAM is available on the machine).
(D)
The Informatica server would automatically page the data to a file on the local disk.



Aggregator data cache size.
An Aggregator transformation will process 50 million rows per session, and the width of each row is 3 KBytes. There are two group-by ports, MONTH and CUSTOMER. Assuming that the data set contains two years worth of data, and that there are 500 customers who are represented for each month in the data set, estimate the required data cache size.

Choose Answer

(A)
6 MBytes
(B)
12 MBytes
(C)
18 MBytes
(D)
36 MBytes



Aggregate cache
The aggregate cache always consists of

Choose Answer

(A)
An index and data cache in RAM, and an index and data file on the local disk.
(B)
An index and data cache in RAM, and an index and data cache on the local disk if the RAM caches cannot contain all of the intermediate results in the data set.
(C)
An index and data cache in RAM, and an index and data file on the local disk if the file names and directory (or Server Variable) have been specified as an Aggregator property.
(D)
An index and data cache in RAM, and an index and data file on the local disk if the Incremental Aggregation property has been set to true when the session runs.
Posted by ghostrider30 at 12:23 PM 0 comments
Sorted Input in Aggregator transformation
An Aggregator transformation has two group-by ports: CUSTOMER_ID and ORDER_DATE, in that order. The Sorted Input property is set to true. The Order By clause in the SQL for the Source Qualifier contains this syntax: ORDER BY CUSTOMER_ID, ORDER_DATE, MANF_ID. Assuming that the sort order on the database server matches the sort order of the session and everything else is valid, what would happen when this session started?

Choose Answer

(A)
The session would run normally.
(B)
The session would fail, because the columns in the ORDER BY clause in the SQL do not exactly match the group-by ports in the Aggregator transformation.
(C)
The session would fail, because when the Sorted Input property is set to true, no more than one group-by port is allowed.
(D)
The session would run, but sorted data would not be used because the columns in the ORDER BY clause in the SQL does not exactly match the group-by ports in the Aggregator transformation, and this may adversely affect performance.



Aggregator transformation variable port
What type of expressions can be used in an Aggregator transformation variable port?

Choose Answer

(A)
Aggregate or non-aggregate expressions, provided they are not used together in a single variable port.
(B)
Aggregate and non-aggregate expressions, together in the same variable port.
(C)
Aggregate, non-aggregate and conditional expressions, together in the same variable port, provided that the variable port is also made a "group-by" port.
(D)
Non-aggregate and conditional expressions only.



Aggregator transformation
What type of expressions can be used in an Aggregator transformation output port?

Choose Answer

(A)
Aggregate or non-aggregate expressions, provided they are not used together in a single output port.
(B)
Aggregate and non-aggregate expressions, together in the same output port.
(C)
Aggregate, non-aggregate and conditional expressions, together in the same output port.
(D)
Aggregate only.



Outer join
Can a Source Qualifier be used to perform an outer join when joining two database tables?

Choose Answer

(A)
Yes, but it requires that the user modify the join SQL by hand.
(B)
Yes, but it requires that the user select left or right outer join as the Source Qualifier Join Type property.
(C)
No, because this may produce null values in ports and the Source Qualifier does not support default values.
(D)
No, this function is not supported by the Source Qualifier- a Joiner transformation would need to be used.



Join two relational sources
A user desires to join two relational sources with a single Source Qualifier. The sources and the Source Qualifier have been correctly imported into the mapping. Next, she must establish a primary / foreign key relationship between two ports in the source definitions. Assuming that the relationship does not currently exist on the database server, what are the required steps to do this?

Choose Answer

(A)
Use the Source Analyzer to establish the primary / foreign key relationship and save the repository.
(B)
Use the Source Analyzer to establish the primary / foreign key relationship, save the repository, and run the appropriate SQL statement on the database tables to create the actual primary / foreign key relationship.
(C)
Use the Source Analyzer to establish the primary / foreign key relationship, open the SQL Editor in the Source Qualifier and generate the join statement, modify the join statement if required, and save the repository.
(D)
Use the Source Analyzer to establish the primary / foreign key relationship, regenerate the Source Qualifier SQL using the SQL Editor, press the Okay button, and save the repository.



Data source(s)
What type of data source(s) can be read with the Source Qualifier transformation?

Choose Answer

(A)
Relational databases
(B)
Relational databases and flat files
(C)
Relational databases, flat files and Cobol
(D)
Relational databases, flat files, Cobol and certified ERP sources such a Peoplesoft and SAP/R3.



Override the default Source Qualifier join
Under what circumstances should a user override the default Source Qualifier join?

Choose Answer

(A)
The datatypes of the columns used in the join condition do not match.
(B)
The Source Qualifier is being used to join two tables located on different database servers (a heterogeneous join).
(C)
The Source Qualifier is being used to join two or more flat files.
(D)
The order of the Source Qualifier ports has been changed since the mapping was first saved.



Generate a default query
A user finds that she is unable to generate a default query in the Source Qualifier SQL Editor. What is a possible reason for that?

Choose Answer

(A)
The datatypes in the Source Qualifier do not match the corresponding datatypes in the source definition(s) for each field and port.
(B)
The Source Qualifier does not have one or more ports connected to another transformation or target in the mapping.
(C)
The Source Qualifier property "Allow default query" is unchecked (set to false).
(D)
The Source Qualifier is not connected to the source database.



Alter the datatypes in the Source Qualifier
Under what circumstances is it desirable to alter the datatypes in the Source Qualifier?

Choose Answer

(A)
When the precision required by the mapping is less than the precision of the data stored in the table or flat file being read by the Source Qualifier.
(B)
When the precision required by the mapping is greater than the precision of the data stored in the table or flat file being read by the Source Qualifier.
(C)
To alter the way the source database binds data when it is imported into the mapping.
(D)
Never. The datatypes in the Source Qualifier and the corresponding source(s) must match.



Informatica expression reserved word
Can a port in an Expression transformation be given the name DISTINCT ?

Choose Answer

(A)
Yes
(B)
No, because DISTINCT is an ANSI SQL reserved word.
(C)
No, because DISTINCT is an Informatica expression reserved word.
(D)
No, because DISTINCT is both an ANSI SQL and an Informatica expression reserved word.



Informatica Test functions
The following are valid Informatica Test functions:

Choose Answer

(A)
ISNULL, IS_DATE, IS_NUMBER, IS_SPACES
(B)
ISNOTNULL, ISDATE, ISHERE
(C)
IS NULL, ISANUMBER, ISADATE, ISADECIMAL
(D)
NO_DOUBT, IS_LIVE, ISILDOR, ISENGARD



Interpreting expression
A user enters the following expression into an Expression transformation:( PRICE - COST ) QUANTITY. The Expression transformation is part of a mapping with a relational database source definition. Assuming that the expression is valid, when the mapping runs as part of a session, how will the Informatica server treat the above expression?

Choose Answer

(A)
It will interpret the expression directly.
(B)
It will embed the expression into the designated source code file for the session and then compile it so that it can be executed.
(C)
It will include the statement in the SQL select statement that will be submitted to the source database.
(D)
It will embed the expression into the primary session stored procedure script that will be executed on either the source database or another database that was specified by the user.
Posted by ghostrider30 at 12:18 PM 0 comments
LTRIM
For a row where the value of the port LAST_NAME is equal to the string 'Damato' what would be the return value of the expression LTRIM ( LAST_NAME, 'A' )?

Choose Answer

(A)
Dmato
(B)
Dmto
(C)
amato
(D)
Damato



Mapping validity
Which situation would always cause a mapping to be invalid?

Choose Answer

(A)
A target definition with no data flowing into it
(B)
Two ports with different data types connected together
(C)
Two active transformations connected to the same transformation
(D)
A Source Qualifier with port names that do not match a field name in the corresponding Source definition.



Data types
A developer wishes to connect two ports that have different data types. Is this allowed?

Choose Answer

(A)
Yes, always
(B)
Yes, provided that the data types are compatible. The Informatica server will automatically convert the original type to match the destination type.
(C)
Yes, provided that the data types are compatible, and the developer writes a valid conversion function to convert the original type to match the destination type.
(D)
No, never.



Working with Expressions
A transformation has the following ports: Port_1: String, Input port; Port_2: String, Input port; Port_3: String, Output port. Which of the expressions shown below would be valid? Assume that it is associated with Port_3 and that the string ENDSTRING is not a parameter or mapping variable.

Choose Answer

(A)
Port_1 Port_2 'ENDSTRING'
(B)
Port_1 Port_2 "ENDSTRING"
(C)
Port_1 Port_2 ENDSTRING
(D)
CONCAT(Port_1, CONCAT(Port_2, "ENDSTRING"))



Transformation expressions
A transformation has the following ports: Port_1: Decimal, Input port; Port_2: Decimal, Input port; Port_3: String, Input port; Port_4: Decimal, Output port; Port_5: Decimal, Output port. Which of the expressions shown below would be invalid? Assume it is associated with Port_4.

Choose Answer

(A)
IIF ( Port_1 > 5, Port_2 10, Port_2 15 )
(B)
IIF ( Port_1 > 5, Port_1 10 )
(C)
IIF ( Port_5 > 7, Port_5 10, Port_5 15 )
(D)
IIF ( Port_2 > 8, Port_2 10, Port_2 15 )



Session Task
Whether or not a Session Task can be considered to have heterogeneous targets is determined by

Choose Answer

(A)
The Mapping properties alone.
(B)
The Session Task properties alone.
(C)
Either the Mapping properties or the Session Task properties.
(D)
Either the Mapping properties or the Workflow properties.



Editing Target definition
Under what circumstances can a Target definition be edited from the Mapping Designer, within the mapping where that Target definition is being used?

Choose Answer

(A)
When the Target definition is not shared (not a shortcut).
(B)
When the Target definition is not shared (not a shortcut) and when the user has write permission on the folder where the mapping is stored.
(C)
Always
(D)
Neve
Posted by ghostrider30 at 12:15 PM 0 comments
Target definitions
What types of Target definitions are supported in a mapping?

Choose Answer

(A)
Relational
(B)
Relational and XML
(C)
Relational, XML and flat file
(D)
Relational, XML, flat file and COBOL



Flat File Wizard
A developer has used the Flat File Wizard to create a fixed-width flat file source definition. This source definition is used in several mappings. The next day, she discovers that the actual flat file that will be accessed by the Informatica server will be comma delimited. What action, if any, should be taken?

Choose Answer

(A)
No action need be taken provided the new file format is valid.
(B)
Edit the Source definition properties to make them correct and save the changes to the repository.
(C)
Launch the Flat File Wizard using the Source definition and edit the properties to make them correct. Save the changes to the repository.
(D)
Delete the Source definition, save the repository, re-import it using the Flat File Wizard and define the properties to make them correct. Add the new Source definition to the affected mappings and save the changes to the repository.



Primary and foreign key relationship
Can a developer manually define a primary and foreign key relationship between two columns in two different relational Source definitions?

Choose Answer

(A)
Yes, always.
(B)
Yes, provided that the data types and precision matches between the two columns.
(C)
Yes, provided that the data types and precision matches between the two columns and the relationship exists on the physical database tables when the session runs.
(D)
Yes, provided that the data types and precision matches between the two columns. The relationship can be set between the Source definitions regardless of whether the relationship exists on the physical database tables, but if the relationship does not exist in the database, the session may fail if referential integrity is faulty.



Tasks on a port level
Which tasks can be performed on a port level (using one specific port)?

Choose Answer

(A)
Enter expressions, define port variables, override default values
(B)
Enter expressions, define port and Mapping variables, override default values
(C)
Define local and Mapping variables, define parameters, override default values
(D)
Enter expressions, define port and Mapping variables, define parameters



Supported transformation ports
What types of transformation ports are supported?

Choose Answer

(A)
Input, Output, Variable
(B)
Input, Output, Input / Output, Variable
(C)
Input, Output, Variable, Aggregate
(D)
Input, Output, Input / Output, Variable, Parameter


link a port
A user is attempting to link a port on the left to another port in a different transformation on the right. The Designer will not establish the connector link (blue arrow). What are the possible reasons for this?

Choose Answer

(A)
Link columns mode not set, or left port not an output port
(B)
Link columns mode not set, or left port not an output port, or data type mismatch
(C)
Link columns mode not set, or left port not an input port, or right port not an output port, or data type mismatch
(D)
Link columns mode not set, or left port not an output port, or right port not an input port, or data type mismatch


Copy a Source Definition from a shared folder to a non-shared folder
A user desires to copy a Source Definition from a shared folder to a non-shared folder. Assuming that the user has write permission on the non-shared folder, what must be done in order for this operation to work as intended?

Choose Answer

(A)
The Source Analyzer must be active and the non-shared folder must be open.
(B)
The Source Analyzer must be active, the non-shared folder must be open, and the user must hold down the Ctrl key while dragging.
(C)
The Source Analyzer must be active and the shared folder must be open.
(D)
The Source Analyzer must be active, the shared folder must be open, and the user must hold down the Ctrl key while dragging.


Designer option cannot be set locally
Specify which Designer option cannot be set locally (differently for each client machine).

Choose Answer

(A)
Whether to display column names or business names for all Source Qualifiers
(B)
Whether to import primary keys when importing a target definition
(C)
Whether to import data type lengths and precisions for Source Definitions
(D)
Whether to display targets grouped by database in the Navigator Window

"Find" search tool
The Designer includes a "Find" search tool as part of the standard toolbar. What can it be used to find?

Choose Answer

(A)
Column or port names in any transformation, source or target definition
(B)
Column or port names in any transformation, source or target definition, and strings in any of the output windows
(C)
Column or port names in any transformation, source or target definition, strings in any of the output windows, and expressions in any transformation
(D)
Column or port names in any transformation, source or target definition, strings in any of the output windows, expressions in any transformation, and descriptive text