ETL

Oracle database vsTeradata database

2012-02-04T07:02:00.000-08:00

The basic difference is that in teradata we have the concept of AMPs which takes care of the part of database virtually assigned to it. It takes care of all the operation relating to the data which is in that AMP. This is not the case of oracle

Even though oracle supports SMP and MPP it doesn't have shared nothing architecture but a distributed architecture.

it has only one type of index while oracle has many types of indexes especially there bitmap index.
==> TD has several types of indexes (primary index, unique secondary index, non unique secondary index, value ordered index, join index, hash index...)

teradata does not have materialize view. oracle has materialize view which decrease the IO band width and makes system more scalable.
==> TD does have materialized views (view + join index on the view)

Oracle has very wide variety of analytic functions for Sql.
==> TD too and they're all ANSI compliant

3 types of partitioning and in oracle 11g there are some new addition in partitioning
==> horizontal partitionning is possible in TD (since V2R5) and vertical partitionning is part of the design of TD.

the largest databases in the world run on Oracle
==> fair enough but in this case size does not matter... it's the ability to manipulate a large amount of data that maters.

Thanks and Regards,
Afzal
-----0-----------------
www.24tasmahiti.com

www.24tasmahiti.com

2011-11-19T02:02:00.000-08:00

(चोंविस तास माहिती .कॉम )24tasmahiti.com
-----------------0-------------------------
* माहिती सम्पूर्ण महाराष्ट्राची

* खरेदी/विक्री किवा सेवा

Information about Maharashtra.
One click site for Sell/Buy/Information in Maharashtra

Difference between Informatica and Ab Initio ,Some misconcept of Informatica

2011-05-11T00:28:00.000-07:00

>Misconcept 1:
Informatica unable to read file with more than one Delimiter.
Solution:
Step:Import the source flat file as fixed with
Use Java Transformation with import StringTokenizer utility and read the files with more than one delimiter.

>Ab Initio can read more than one delimiter file.
Ab inito having it's own DML method to read the any source before reading.
record
datatype(length/delimiter) fieldname;
datatype(length/delimiter) fieldname;
datatype(length/delimiter) fieldname;
end;

>Misconcept 2: Informatica unable to do looping.
Solution 1:
Yes directly Informatica does,nt show any looping mechanism even we can use it through
expression and router transformation
Solution 2:
Use Java Transformation /Custom Transformation

>As Ab Initio is code based ETL not as Engine based ETL like informatica .
Due to this it will always supports scripting like shell which do supports
looping.

DataStage 7.5

2010-06-10T07:54:00.000-07:00

IBM WebSphere IIS DataStage Enterprise Edition v7.5.
QUESTIONS
A customer wants to migrate only the performance-critical jobs of an existing DataStage
Server Edition environment to a clustered deployment of DataStage Enterprise Edition.
Which migration approach will allow the new parallel jobs to run on all nodes of the
cluster using DataStage Enterprise Edition?
A. Create a Server Shared Container containing the key stages for each job to be
migrated, and use this container in a parallel job flow with native parallel source and target stages.
B. Using requirements for the existing Server Edition jobs, create new parallel jobs using
native parallel stages that implement the required source to target logic.
C. Configure a DataStage Server Edition environment on each node in the DataStage
Enterprise Edition cluster, import the existing .dsx export file to each node, compile and
run the jobs in parallel using a parallel configuration file.
D. Export the Server Edition jobs to a .dsx file, import them into the new DataStage
Enterprise Edition environment, and re-compile.
Answer: B

QUESTIONS
A parts supplier has a single fixed width sequential file. Reading the file has been slow,
so the supplier would like to try to read it in parallel.
If the job executes using a configuration file consisting of four nodes, which two
Sequential File stage settings will cause the DataStage parallel engine to read the file
using four parallel readers? (Choose two.)
(Note: Assume the file path and name is /data/parts_input.txt.)
A. Set the read method to specific file(s), set the file property to '/data/parts_input.txt',
and set the number of readers per node option to 2.
B. Set the read method to specific file(s), set the file property to '/data/parts_input.txt',
and set the read from multiple nodes option to yes.
C. Set read method to file pattern, and set the file pattern property to
'/data/(@PART_COUNT)parts_input.txt'.
D. Set the read method to specific file(s), set the file property to '/data/parts_input.txt',
and set the number of readers per node option to 4.
Answer: B,D

QUESTIONS
A developer has separated processing into multiple jobs in order to support restartability.
The first job writes to a file that the second job reads. The developer is trying to decide
whether to write to a dataset or to a sequential file. Which factor favors using a dataset?
A. I/O performance
B. ability to archive
C. memory usage
D. disk space usage
Answer: A

QUESTIONS
In a Lookup operator, a key column in the stream link is VARCHAR and has Unicode set
in the Extended attribute while the corresponding column in a reference link, also
VARCHAR, does not.
What will allow correct comparison of the data?
A. Convert the column in the stream link to the default code page using the
UstringToString function in a Transformer operator prior to the Lookup operator and
remove Unicode from the Extended attribute of the column.
B. Convert the column in the reference link to the UTF-8 code page using the
StringToUstring function in a Transformer operator prior to the Lookup operator, and set
the Extended attribute of the column.
C. Convert both columns to CHAR, pad with spaces, and remove Unicode from the
Extended attribute in Transformer operators prior to the Lookup operator.
D. Remove Unicode from the Extended attribute of the column from the beginning of the
job to the Lookup operator and then set the Extended attribute of the column in the output
mapping section of the Lookup operator.
Answer: B

QUESTIONS
Detail sales transaction data is received on a monthly basis in a single file that includes
CustID, OrderDate, and Amount fields. For a given month, a single CustID may have
multiple transactions.
Which method would remove duplicate CustID rows, selecting the most recent
transaction for a given CustID?

A. Use Auto partitioning. Perform a unique Sort on CustID and OrderDate (descending).
B. Hash partition on CustID. Perform a non-unique Sort on CustID and OrderDate
(ascending). Use same partitioning, followed by a RemoveDuplicates on CustID (duplicateToRetain=Last).
C. Hash partition on CustID. Perform a unique Sort on CustID and OrderDate (ascending).
D. Use Auto partitioning on all links. Perform a non-unique Sort on CustID and
OrderDate (ascending) followed by a RemoveDuplicates on CustID and OrderDate (duplicateToRetain=Last).
Answer: B

QUESTIONS
Which two statements about performance tuning a DataStage EE environment are true?
(Choose two.)
A. Overall job design has a minimal impact in actual real-world performance.
B. A single, optimized configuration file will yield best performance for all jobs and be
easier to administer.
C. Only adjust buffer tuning parameters after examining other performance factors.
D. Performance tuning is an iterative process - adjust one item at a time and examine the
results in isolation.
Answer: C,D
QUESTIONS
Which two statements are true about the Lookup stage? (Choose two.)
A. The Lookup stage supports one and only one lookup table per stage.
B. A reject link can be specified to capture input rows that do not have matches in the
lookup tables.
C. The source primary, input link must be sorted.
D. The Lookup stage uses more memory than the Merge and Join stages.
Answer: B,D

QUESTIONS
Which three can be identified from a parallel job score? (Choose three.)
A. components inserted by DataStage parallel engine at runtime (such as buffers, sorts).
B. runtime column propagation was enabled to support dynamic job designs.
C. whether a particular job was run in a clustered or SMP hardware environment.
D. stages whose logic was combined together to minimize the number of physical
processes used to execute a given job.
E. actual partitioning method used at runtime for each link defined with "Auto" partitioning.
Answer: A,D,E

QUESTIONS
A DataStage EE job is sourcing a flat file which contains a VARCHAR field. This field
needs to be mapped to a target field that is a date. Which task will accomplish this?
A. Perform a datatype conversion using DateToString function inside the Transformer stage.
B. Use a Modify stage to perform the type conversion.
C. DataStage automatically performs this type conversion by default.
D. Use a Copy stage to perform the type conversion.
Answer: B

QUESTIONS
To encourage users to update the short description for a job, how can you make the short
description visible and updateable on the main canvas?
A. Click the Show Job Short Description option in the Job Properties.
B. Right-click on the job canvas and choose Show Job Short Description in the submenu.
C. Add an Annotation stage to the job canvas and copy and paste in the short description.
D. Add a Description Annotation field to the job canvas and select the Short Description
property.
Answer: D

QUESTIONS
A single sequential file exists on a single node. To read this sequential file in parallel,
what should be done?
A. Set the Execution mode to "Parallel".

B. A sequential file cannot be read in parallel using the Sequential File stage.
C. Select "File Pattern" as the Read Method.
D. Set the "Number of Readers Per Node" optional property to a value greater than 1.
Answer: D

QUESTIONS
What is the default data type produced by the Aggregator stage?
A. decimal [38,9]
B. integer
C. single precision floating point
D. double precision floating point
Answer: D
QUESTIONS
Which two statements are true of the Merge stage? (Choose two.)
A. The Merge stage supports inner, left outer, and right outer joins.
B. All the inputs to the Merge stage must be sorted by the key column(s).
C. The Merge stage supports only two input links.
D. The columns that are used to merge the incoming data much be identically named.
Answer: B,D

QUESTIONS
Job run details for a specific invocation of a multi-instance job can be viewed by which
two clients? (Choose two.)
A. dsjobinfo

B. DataStage Director
C. dsjob
D. DataStage Manager
Answer: B,C

QUESTIONS
Your job reads from a file using a Sequential File stage running sequentially. You are
using a Transformer following the Sequential File stage to format the data in some of the
columns. Which partitioning algorithm would yield optimized performance?
A. Hash
B. Random
C. Round Robin
D. Entire
Answer: C

QUESTIONS
Which three accurately describe the differences between a DataStage server root
installation and a non-root installation? (Choose three.)
A. A non-root installation enables auto-start on reboot.
B. A root installation must specify the user "dsadm" as the DataStage administrative user.
C. A non-root installation inherits the permissions of the user who starts the DataStage services.
D. A root installation will start DataStage services in impersonation mode.
E. A root installation enables auto-start on reboot.
Answer: C,D,E

QUESTIONS
When a sequential file is read using a Sequential File stage, the parallel engine inserts an
operator to convert the data to the internal format. Which operator is inserted?
A. import operator
B. copy operator
C. tsort operator
D. export operator

Answer: A

QUESTIONS
Which two statements regarding the usage of data types in the parallel engine are correct?
(Choose two.)
A. The best way to import RDBMS data types is using the ODBC importer.
B. The parallel engine will use its interpretation of the Oracle meta data (e.g, exact data
types) based on interrogation of Oracle, overriding what you may have specified in the Columns tabs.
C. The best way to import RDBMS data types is using the Import Orchestrate Schema
Definitions using orchdbutil.
D. The parallel engine and server engine have exactly the same data types so there is no
conversion cost overhead from moving data between the engines.
Answer: B,C
QUESTIONS
You must create a job that extracts data from multiple DB2/UDB databases on a
mainframe and AS400 platforms without any database specific client software other than
what is included with DataStage. Which two stages will let you parameterize the options
required to connect to the database and enable you to use the same job if the source
metadata matches all source tables? (Choose two.)
A. DB2/UDB Enterprise stage
B. ODBC Enterprise stage
C. DB2/UDB API stage
D. Dynamic RDBMS stage
Answer: B,D

QUESTIONS
Which environment variable controls whether performance statistics can be displayed in
Designer?
A. APT_NO_JOBMON
B. APT_PERFORMANCE_DATA
C. APT_PM_SHOW_PIDS
D. APT_RECORD_COUNTS
Answer: A

QUESTIONS
Data volumes have grown significantly in the last month. A parallel job that used to run
well is now using unacceptable amounts of disk space and running slowly. You have
reviewed the job and explicitly defined the sorting and partitioning requirements for each
stage but the behavior of the job has not changed.
Which two actions improve performance based on this information? (Choose two.)
A. Change the sort methods on all sorts to "Don't Sort - Previously Grouped".
B. Enable the environment variable APT_NO_SORT_INSERTION in the job.
C. Increase the value of environment variable APT_PARTITION_NUMBER to increase
the level of parallelism for the sorts.
D. Enable the environment variable APT_SORT_INSERTION_CHECK_ONLY in the job.
Answer: B,D

QUESTIONS
Which two job design techniques can be used to give unique names to sequential output
files that are used in multi-instance jobs? (Choose two.)
A. Use the DataStage DSJobInvocationId macro to prepend/append the Invocation Id to
the file name.
B. Use parameters to identify file names.
C. Use a Transformer Stage variable to generate the name.
D. Use the Generate Unique Name property of the Sequential File Stage.
Answer: A,B

QUESTIONS
Jobs that use the Sort stage are running slow due to the amount of data being processed.
Which Sort stage property or environment variable can be modified to improve
performance?
A. Sort Stage Max Memory Limit property
B. Sort Stage Restrict Memory Usage property
C. APT_SORT_MEMORY_SIZE
D. APT_AUTO_TRANSPORT_SIZE
Answer: B

QUESTIONS
A job has two input sources that need to be combined. Each input source exceeds
available physical memory. The files are in the same format and must be combined using
a key value. It is guaranteed that there will be at least one match.
Given the above scenario, which stage would consume the least amount of physical
memory?
A. Funnel
B. Merge
C. Lookup
D. Transformer
Answer: B

QUESTIONS
What determines the degree of parallelism with which the Teradata Enterprise operator
will read from a database?
A. the value of the sessionsperplayer option found on the Additional Connections Options panel
B. the number of Teradata AMPs
C. the value of the Teradata MAXLOADTASKS parameter
D. the number of nodes specified in the APT_CONFIG_FILE
Answer: B

QUESTIONS
What would require creating a new parallel Custom stage rather than a new parallel
BuildOp stage?
A. A Custom stage can be created with properties. BuildOp stages cannot be created with
properties.
B. In a Custom stage, the number of input links does not have to be fixed, but can vary,
for example from one to two. BuildOp stages require a fixed number of input links.
C. Creating a Custom stage requires knowledge of C/C++. You do not need knowledge
of C/C++ to create a BuildOp stage.
D. Custom stages can be created for parallel execution. BuildOp stages can only be built
to run sequentially.
Answer: B

QUESTIONS
Which environment variable, when set to true, causes a report to be produced which
shows the operators, processes and data sets in the job?
A. APT_DUMP_SCORE
B. APT_JOB_REPORT
C. APT_MONITOR_SIZE
D. APT_RECORD_COUNTS
Answer: A

QUESTIONS
The source stream contains customer records. Each record is identified by a CUSTID
field. It is known that the stream contains duplicate records, that is, multiple records with
the same CUSTID value. The business requirement is to add a field named NUMDUPS
to each record that contains the number of duplicates and write the results to a target DB2

table.
Which job design would accomplish this?
A. Send the incoming records to a Transformer stage. Use a Hash partitioning method
with CUSTID as the key and sort by CUSTID. Use stage variables to keep a running
count of the number of each new CUSTID. Add this count to a new output field named
NUMDUPS then load the results into the DB2 table.
B. Use a Modify stage to add the NUMDUPS field to the input stream then process the
data via an Aggregator stage Group and CountRows options on CUSTID with the result
of the sum operation sent to the NUMDUPS column in the Mapping tab for load into the
DB2 table.
C. Use a Copy stage to split the incoming records into two streams. One stream goes to
an Aggregator stage that groups the records by CUSTID and counts the number of
records in each group and outputs the results to the NUMDUPS field. The output from
the Aggregator stage is then joined to the other stream using a Join stage on CUSTID and
the results are then loaded into the DB2 table.
D. Use an Aggregator stage to group the incoming records by CUSTID and to count the
number of records in each group then load the results into the DB2 table.
Answer: C

QUESTIONS
A client requires that a database table be done using two jobs. The first job writes to a
dataset. The second job reads the dataset and loads the table. The two jobs are connected
in a Job Sequence. What are three benefits of this approach? (Choose three.)
A. The time it takes to load the table is reduced.
B. The database table can be reloaded after a failure without re-reading the source data.
C. The dataset can be used by other jobs even if the database load fails.
D. The dataset can be read if the database is not available.
E. The data in the dataset can be archived and shared with other external applications.
Answer: B,C,D

QUESTIONS
Which two tasks will create DataStage projects? (Choose two.)
A. Export and import a DataStage project from DataStage Manager.
B. Add new projects from DataStage Administrator.
C. Install the DataStage engine.
D. Copy a project in DataStage Administrator.
Answer: B,C

QUESTIONS
Which three stages support the dynamic (runtime) definition of the physical column
metadata? (Choose three.)
A. the Sequential stage
B. the Column Export stage
C. the CFF stage
D. the DRS stage
E. the Column Import stage
Answer: A,B,E

QUESTIONS
You are reading customer data using a Sequential File stage and Sorting it by customer
ID using the Sort stage. The data is to be written to a sequential file in sorted order.
Which collection method is more likely to yield optimal performance without violating
the business requirements?
A. Sort Merge on customer ID
B. Auto
C. Round Robin
D. Ordered
Answer: A

QUESTIONS
A new column is required as part of a sorted dataset to be set to 1 when the value of sort
key changes and to 0 when the value of sort key is the same as the prior record. Which
statement is correct?
A. This can be handled entirely within the Sort stage by setting the Create Key Change
Column to True.
B. This can be handled within the Sort stage by including a new column name in the
output tab of the stage and by placing an "if, then, else" expression in the Derivation field
in the stage Mapping tab to generate the value for the column.
C. This can be handled entirely within the Sort stage by setting the Create Cluster Key
Change Column to True.
D. This cannot be handled entirely within the Sort stage.
Answer: A

QUESTIONS
Which two statements describe functionality that is available using the dsjob command?
(Choose two.)
A. dsjob can be used to get a report containing job, stage, and link information.
B. dsjob can be used to add a log entry for a specified job.
C. dsjob can be used to compile a job.
D. dsjob can be used to export job executables.
Answer: A,B

QUESTIONS
You have a parallel job that does not scale beyond two nodes. After investigation you
find that the data has been partitioned on gender and you have an Aggregator stage that is
accumulating totals based on gender using a sort method. Which technique should you
use to allow the job to scale?
A. Change the preserve partitioning option on the stage ahead of the aggregator to clear partitioning.
B. Change the aggregation method to hash to eliminate the blocking sort operation.
C. Add an additional column for partitioning to result in additional data partitions.
D. Change the partitioning key to one that results in more partitions, add a second
Aggregator stage that re-aggregates based on gender.
Answer: D

QUESTIONS
You have three output links coming out of a Transformer. Two of them (A and B) have
constraints you have defined. The third you want to be an Otherwise link that is to
contain all of the rows that do not satisfy the constraints of A and B. This Otherwise link
must work correctly even if the A and B constraints are modified. Which two are
required? (Choose two.)
A. The Otherwise link must be first in the link ordering.
B. A constraint must be coded for the Otherwise link.
C. The Otherwise link must be last in the link ordering.
D. The Otherwise check box must be checked.
Answer: C,D
QUESTIONS
In which two scenarios should a sparse lookup be used in place of a normal lookup to
retrieve data from an Oracle database? (Choose two.)
A. A database function that returns the current value of an Oracle object is required as
part of the result set.
B. When the number of input rows is significantly smaller than the number of rows in the
lookup table.
C. When the Oracle database is on the same system as the DataStage server.
D. When the number of input rows is significantly larger than the number of rows in the
lookup table.
Answer: A,B

QUESTIONS
Which statement about job parameter usage is true?
A. You can change job parameters while a job is running and the changes will
immediately be applied mid-job.
B. You can use environment variables to set parameter values linked to the Job Sequence.
C. You can change the parameter values in an initialization file linked to a Job Sequence.ini file.
D. Changes to the job parameters in the Designer do not require a recompile to be applied to the job.
Answer: B

QUESTIONS
What are two causes for records being incorrectly identified as an Edit in the Change
Capture stage? (Choose two.)

A. The before and after input datasets are not in the same sort order.
B. At least one field containing unique values for all records is specified as a change value.
C. The key fields are not unique.
D. A key field contains null values in some records.
Answer: A,C

QUESTIONS
Which two statements describe the properties needed by the Oracle Enterprise stage to
operate in parallel direct load mode. (Choose two.)
A. The table can have any number of indexes but the job will run slower since the
indexes will have to be updated as the data is loaded.
B. The table must not have any indexes, or you must include the rebuild Index Mode
property unless the only index on the table is the primary key in which case you can use
the Disable Constraints property.
C. Only index organized tables allow you to use parallel direct load mode and you need
to set both DIRECT and PARALLEL properties to True, otherwise the load will be
executed in parallel but will not use direct mode.
D. Set the Write Method to Load.
Answer: B,D

QUESTIONS
How does a Join stage process an Inner join?
A. It transfers all values from the right data set and transfers values from the left data set
and intermediate data sets only where key columns match.
B. It transfers records from the input data sets whose key columns contain equal values to
the output data set.
C. It transfers all values from the left data set but transfers values from the right data set
and intermediate data sets only when key columns match.
D. It transfers records in which the contents of the key columns are equal from the left
and right input data sets to the output data set. It also transfers records whose key
columns contain unequal values from both input data sets to the output data set.
Answer: B

QUESTIONS
Which partitioning method requires specifying a key?
A. Random

B. DB2
C. Entire
D. Modulus
Answer: D
QUESTIONS
Detail sales transaction data is received on a monthly basis in a single file that includes
CustID, OrderDate, and Amount fields. For a given month, a single CustID may have
multiple transactions.
Which method would remove duplicate CustID rows, selecting the largest transaction
amount for a given CustID?
A. Hash partition on CustID. Perform a non-stable, unique Sort on CustID and Amount (descending).
B. Use Auto partitioning on all links. Perform a non-unique Sort on CustID and Amount
(ascending) followed by a RemoveDuplicates on CustID and Amount (duplicateToRetain=Last).
C. Use Auto partitioning. Perform a unique Sort on CustID and Amount (ascending).
D. Hash partition on CustID. Perform a non-unique Sort on CustID and Amount
(descending).Use same partitioning, followed by a RemoveDuplicates on CustID (duplicateToRetain=First).
Answer: D

QUESTIONS
Your business requirement is to read data from three Oracle tables that store historical
sales data from three regions for loading into a single Oracle table.The table definitions
are the same for all three tables, the only difference is that each table contains data for a
particular region.
Which two statements describe how this can be done? (Choose two.)
A. Create a job with a single Oracle Enterprise stage that executes a custom SQL
statement with a FETCH ALL operator that outputs the data to an Oracle Enterprise stage.
B. Create a job with a single Oracle Enterprise stage that executes a custom SQL
statement with a UNION ALL operator that outputs the data to an Oracle Enterprise stage.
C. Create a job with three Oracle Enterprise stages to read from the tables and output to a
Collector stage which in turns outputs the data to an Oracle Enterprise stage.
D. Create a job with three Oracle Enterprise stages to read from the tables and output to a
Funnel stage which in turn outputs the data to an Oracle Enterprise stage.
Answer: B,D

QUESTIONS
When importing a COBOL file definition, which two are required? (Choose two.)
A. The file you are importing is accessible from your client workstation.
B. The file you are importing contains level 01 items.
C. The column definitions are in a COBOL copybook file and not, for example, in a
COBOL source file.
D. The file does not contain any OCCURS DEPENDING ON clauses.
Answer: A,B

QUESTIONS
Which two statements are true about the Join stage? (Choose two.)
A. All the inputs to the Join stage must be sorted by the Join key.
B. Join stages can have reject links that capture rows without matches.
C. The Join stage supports inner, left outer, and right outer joins.
D. The Join stage uses more memory than the Lookup stage.
Answer: A,C

QUESTIONS
A client requires that any job that aborts in a Job Sequence halt processing. Which three
activities would provide this capability? (Choose three.)
A. Nested Condition Activity
B. Exception Handler
C. Sequencer Activity
D. Sendmail Activity
E. Job trigger
Answer: A,B,E

QUESTIONS
What is the default data type produced by the Aggregator stage?
A. integer
B. double precision floating point
C. decimal [38,9]
D. single precision floating point
Answer: B
QUESTIONS
You have set the "Preserve Partitioning" flag for a Sort stage to request that the next
stage preserves whatever partitioning it has implemented. Which statement describes
what will happen next?
A. The job will compile but will abort when run.
B. The job will not compile.
C. The next stage can ignore this request but a warning is logged when the job is run
depending on the stage type that ignores the flag.
D. The next stage disables the partition options that are normally available in the
Partitioning tab.
Answer: C

QUESTIONS
Which statement describes how to add functionality to the Transformer stage?
A. Create a new parallel routine in the Routines category that specifies the name, path,

type, and return type of a function written and compiled in C++.
B. Create a new parallel routine in the Routines category that specifies the name, path,
type, and return type of an external program.
C. Create a new server routine in the Routines category that specifies the name and
category of a function written in DataStage Basic.
D. Edit the C++ code generated by the Transformer stage.
Answer: A

QUESTIONS
Which two statements are true about usage of the APT_DISABLE_COMBINATION
environment variable? (Choose two.)
A. Locks the job so that no one can modify it.
B. Disabling generates more processes requiring more system resources and memory.
C. Must use the job design canvas to check which stages are no longer being combined.
D. Globally disables operator combining.
Answer: B,D

QUESTIONS
A job design consists of an input fileset followed by a Peek stage, followed by a Filter
stage, followed by an output fileset. The environment variable
APT_DISABLE_COMBINATION is set to true, and the job executes on an SMP using a
configuration file with 8 nodes defined. Assume also that the input dataset was created
with the same 8 node configuration file.
Approximately how many data processing processes will this job create?
A. 32
B. 8
C. 16
D. 1
Answer: A

QUESTIONS
A credit card company has about 10 million unique accounts. The company needs to
determine the outstanding balance of each account by aggregating the previous balance
with current charges. A DataStage EE job with an Aggregator stage is being used to
perform this calculation. Which Aggregator method should be used for optimal
performance?
A. Sort
B. Group
C. Auto
D. Hash
Answer: A

QUESTIONS
Your input rows contain customer data from a variety of locations. You want to select
just those rows from a specified location based on a parameter value. You are trying to
decide whether to use a Transformer or a Filter stage to accomplish this. Which statement
is true?
A. The Transformer stage will yield better performance because the Filter stage Where
clause is interpreted at runtime.
B. You cannot use a Filter stage because you cannot use parameters in a Filter stage
Where clause.
C. The Filter stage will yield better performance because it has less overhead than a
Transformer stage.
D. You cannot use the Transformer stage because you cannot use parameters in a
Transformer stage constraint.
Answer: A

QUESTIONS
You need to move a DataStage job from a development server on machine A to a
production server on machine B. What are two valid ways to do this? (Choose two.)

A. Use the command line export tool to create a .dsx file on machine A, then move the
.dsx file to machine B and use the command line import tool to load the .dsx file.
B. Connect the Manager client to the source project on machine A and create a .dsx file
of the job then connect the Manager client to the target project on machine B and import the .dsx file.
C. Use the command line export tool to create a .dsx file on machine A then move the
.dsx file to the client and use the Manager client to import it.
D. Connect to machine A with the Manager client and create a .dsx file of the job, then
move the .dsx file to machine B and use the command line import tool.
Answer: B,D

QUESTIONS
In a Lookup operator, a key column in the stream link is VARCHAR and has Unicode set
in the Extended attribute while the corresponding column in a reference link, also
VARCHAR, does not.
What will allow correct comparison of the data?
A. Convert both columns to CHAR, pad with spaces, and remove Unicode from the
Extended attribute in Transformer operators prior to the Lookup operator.
B. Convert the column in the reference link to the UTF-8 code page using the
StringToUstring function in a Transformer operator prior to the Lookup operator, and set
the Extended attribute of the column.
C. Convert the column in the stream link to the default code page using the
UstringToString function in a Transformer operator prior to the Lookup operator and
remove Unicode from the Extended attribute of the column.
D. Remove Unicode from the Extended attribute of the column from the beginning of the
job to the Lookup operator and then set the Extended attribute of the column in the output
mapping section of the Lookup operator.
Answer: B

QUESTIONS
Which two system variables must be used in a parallel Transformer derivation to
generate a unique sequence of integers across partitions? (Choose two.)
A. @PARTITIONNUM
B. @INROWNUM

C. @DATE
D. @NUMPARTITIONS
Answer: A,D

QUESTIONS
A job contains a Sort stage that sorts a large volume of data across a cluster of servers.
The customer has requested that this sorting be done on a subset of servers identified in
the configuration file to minimize impact on database nodes.
Which two steps will accomplish this? (Choose two.)
A. Create a sort scratch disk pool with a subset of nodes in the parallel configuration file.
B. Set the execution mode of the Sort stage to sequential.
C. Specify the appropriate node constraint within the Sort stage.
D. Define a non-default node pool with a subset of nodes in the parallel configuration
file.
Answer: C,D

QUESTIONS
You are reading customer data using a Sequential File stage and transforming it using the
Transformer stage. The Transformer is used to cleanse the data by trimming spaces from
character fields in the input. The cleansed data is to be written to a target DB2 table.
Which partitioning method would yield optimal performance without violating the
business requirements?
A. Hash on the customer ID field
B. Round Robin
C. Random
D. Entire

Answer: B

QUESTIONS
Which three defaults are set in DataStage Administrator? (Choose three.)
A. default prompting options, such as Autosave job before compile
B. default SMTP mail server name
C. project level default for Runtime Column Propagation
D. project level defaults for environment variables
E. project level default for Auto-purge of job log entries
Answer: C,D,E

QUESTIONS
A Varchar(10) field named SourceColumn is mapped to a Char(25) field named
TargetColumn in a Transformer stage. The APT_STRING_PADCHAR environment
variable is set in Administrator to its default value. Which technique describes how to
write the derivation so that values in SourceColumn are padded with spaces in
TargetColumn?
A. Include APT_STRING_PADCHAR in your job as a job parameter. Specify the C/C++
end of string character (0x0) as its value.
B. Map SourceColumn to TargetColumn. The Transformer stage will automatically pad with spaces.
C. Include APT_STRING_PADCHAR in your job as a job parameter. Specify a space as its value.
D. Concatenate a string of 25 spaces to SourceColumn in the derivation for TargetColumn.
Answer: C

QUESTIONS
Which statement is true about Aggregator Sort and Hash methods when the
APT_NO_SORT_INSERTION environment variable is set to TRUE?
A. If you select the Hash method, the Aggregator stage requires the data to have the
partition sorted by the group key.
B. If you select the Hash method, the Aggregator stage will partition sort the data by the
group key before building a hash table in memory.
C. If you select the Sort method, the Aggregator stage will partition sort the data by the
group key before performing the aggregation.

D. If you select the Sort method, the Aggregator stage requires the data to have been
partition sorted by the group key.
Answer: D

QUESTIONS
Using FTP, a file is transferred from an MVS system to a LINUX system in binary
transfer mode. Which data conversion must be used to read a packed decimal field in the
file?
A. treat the field as EBCDIC
B. treat the field as a packed decimal
C. packed decimal fields are not supported
D. treat the field as ASCII
Answer: B

QUESTIONS
Which two statements are true about DataStage Parallel Buildop stages? (Choose two.)
A. Unlike standard DataStage stages they do not have properties.
B. They are coded using C/C++.
C. They are coded using DataStage Basic.
D. Table Definitions are used to define the input and output interfaces of the BuildOp.
Answer: B,D

QUESTIONS
On which two does the number of data files created by a fileset depend ? (Choose two.)
A. the size of the partitions of the dataset
B. the number of CPUs
C. the schema of the file
D. the number of processing nodes in the default node pool
Answer: A,D

QUESTIONS
An XML file is being processed by the XML Input stage. How can repetition elements be
identified on the stage?
A. Set the "Nullable" property for the column on the output link to "Yes".
B. Set the "Key" property for the column on the output link to "Yes".
C. Check the "Repetition Element Required" box on the output link tab.
D. No special settings are required. XML Input stage automatically detects the repetition
element from the XPath expression.
Answer: B
QUESTIONS
A job requires that data be grouped by primary keys and then sorted within each group
by secondary keys to reproduce the results of a group-by and order-by clause common to
relational databases. The designer has chosen to implement this requirement with two
Sort stages in which the first Sort stage sorts the records by the primary keys.
Which set of properties must be specified in the second Sort stage?
A. Specify both sets of keys, with a Sort Key Option of "Don't Sort (Previously Sorted)"
on the primary keys, and "Don't Sort (Previously Grouped)", and the Stable Sort option set to True.
B. Specify only the secondary keys, with a Sort Key Option of "Don't Sort ( Previously
Sorted)" and the Stable Sort option set to True.
C. Specify both sets of keys, with a Sort Key Option of "Don't Sort (Previously
Grouped)" on the primary keys, and "Sort" on the secondary keys.
D. Specify only the secondary keys, with a Sort Key Option of "Sort" and the Stable Sort
option set to True.
Answer: C

QUESTIONS
Using a second (reject) output link from a Sequential File read stage, which two methods
will allow the number of records rejected by the Sequential File stage to be captured and
logged to a file? (Choose two.)
(Note: The log file should contain only the number of records rejected, not the records
themselves. Assume the file is named reject_count.stage_10.)
A. Send the rejected records to a dataset named rejects_stage_10.ds, and define an After
Job Subroutine to execute the command 'dsrecords rejects_stage_10.ds >
reject_count.stage_10'.
B. Send the rejected records to a Column Generator stage to generate a constant key
value, use the Aggregator stage with the generated key value to count the records, then
write the results to the log file using another Sequential File stage.
C. Send the rejected records to a Peek stage and define an After Job Subroutine to
execute the command 'dsjob -report peek_rejects > reject_count.stage_10' with the
appropriate -jobid and -project arguments.
D. Send the rejected records to a Change Capture stage, use a Sort Funnel stage to sort it
all out, then write the resulting record count to the log file using another Sequential File
stage.
Answer: A,B

QUESTIONS
Which requirement must be satisfied to read from an Oracle table in parallel using the
Oracle Enterprise stage?
A. Set the environment variable $ORA_IGNORE_CONFIG_FILE_PARALLELISM.
B. Configure the source table to be partitioned within the source Oracle database.
C. Oracle Enterprise stage always reads in parallel.
D. Specify the Partition Table option in the Oracle source stage.
Answer: D

QUESTIONS
When a sequential file is written using a Sequential File stage, the parallel engine inserts
an operator to convert the data from the internal format to the external format. Which
operator is inserted?
A. export operator
B. copy operator
C. import operator
D. tsort operator
Answer: A

QUESTIONS
Which "Reject Mode" option in the Sequential File stage will write records to a reject
link?
A. Output
B. Fail
C. Drop
D. Continue
Answer: A

QUESTIONS
A file created by a mainframe COBOL program is imported to the DataStage parallel
engine. The resulting record schema contains a fixed length vector of the three most
recent telephone numbers called by each customer. The processing requirements state
that each of the three most recent calls must be processed differently.
Which stage should be used to restructure the record schema so that the Transformer
stage can be used to process each of the three telephone numbers found in the vector?
A. the Make Vector stage followed by a Split Subrecord stage
B. the Split Subrecord stage followed by a Column Export stage

C. the Promote Subrecord stage
D. the Split Vector stage
Answer: D
QUESTIONS
The parallel dataset input into a Transformer stage contains null values. What should you
do to properly handle these null values?
A. Convert null values to a valid values in a stage variable.
B. Convert null values to a valid value in the output column derivation.
C. Null values are automatically converted to blanks and zero, depending on the target data type.
D. Trap the null values in a link constraint to avoid derivations.
Answer: A

QUESTIONS
Which two statements are true about XML Meta Data Importer? (Choose two.)
A. XML Meta Data Importer is capable of reporting syntax and semantic errors from an XML file.
B. XPATH expressions that are created during XML metadata import cannot be modified.
C. XML Meta Data Importer can import Table Definitions from only XML documents.
D. XPATH expressions that are created during XML metadata import are used by XML
Input stage and XML Output stage.
Answer: A,D

QUESTIONS
Which type of file is both partitioned and readable by external applications?
A. fileset
B. Lookup fileset
C. dataset
D. sequential file
Answer: A

QUESTIONS
Which two describe a DataStage EE installation in a clustered environment? (Choose
two.)

A. The C++ compiler must be installed on all cluster nodes.
B. Transform operators must be copied to all nodes of the cluster.
C. The DataStage parallel engine must be installed or accessible in the same directory on
all machines in the cluster.
D. A remote shell must be configured to support communication between the conductor
and section leader nodes.
Answer: C,D

QUESTIONS
What is the purpose of the uv command in a UNIX DataStage server?
A. Cleanup resources from a failed DataStage job.
B. Start and stop the DataStage engine.
C. Provide read access to a DataStage EE configuration file.
D. Report DataStage client connections.
Answer: B

QUESTIONS
Establishing a consistent naming standard for link names is useful in which two ways?
(Choose two.)
A. using less memory at job runtime
B. specifying link order without having to specify stage properties to generate correct results
C. easing use of captured job statistics (eg. row counts in an XML file) by processes or
users outside of DataStage
D. improving developer productivity and quality by distinguishing link names within stage editors
Answer: C,D
QUESTIONS
Which technique would you use to abort a job from within the Transformer stage?
A. Call the DSLogFatal function from a stage variable derivation.
B. Call the DSStopJob function from a stage or column derivation.
C. Create a dummy output link with a constraint that tests for the condition to abort on -
set the "Abort After Rows" property to 1.
D. Use the SETENV function to set the environmental APT_PM_KILL_NOWAIT.
Answer: C

QUESTIONS
Your job reads from a file using a Sequential File stage running sequentially. The
DataStage server is running on a single SMP system. One of the columns contains a
product ID. In a Lookup stage following the Sequential File stage, you decide to look up
the product description from a reference table. Which two partition settings would
correctly find matching product descriptions? (Choose two.)
A. Hash algorithm, specifying the product ID field as the key, on both the link coming
from the Sequential File stage and the link coming from the reference table.
B. Round Robin on both the link coming from the Sequential File stage and the link
coming from the reference table.
C. Round Robin on the link coming from the Sequential File stage and Entire on the link
coming from the reference table.
D. Entire on the link coming from the Sequential File stage and Hash, specifying the
product ID field as the key, on the link coming from the reference table.
Answer: A,C

QUESTIONS
You need to move a DataStage job from a development server on machine A to a
production server on machine B.
What are two valid ways to do this? (Choose two.)
A. Connect the Manager client to the source project on machine A and create a .dsx file
of the job then connect the Manager client to the target project on machine B and import the .dsx file.
B. Use the command line export tool to create a .dsx file on machine A then move the
.dsx file to the client and use the Manager client to import it.
C. Use the command line export tool to create a .dsx file on machine A, then move the
.dsx file to machine B and use the command line import tool to load the .dsx file.
D. Connect to machine A with the Manager client and create a .dsx file of the job, then
move the .dsx file to machine B and use the command line import tool.

Answer: A,D

QUESTIONS
Which two would cause a stage to sequentially process its incoming data? (Choose two.)
A. The execution mode of the stage is sequential.
B. The stage follows a Sequential File stage and its partitioning algorithm is Auto.
C. The stage follows a Sequential File stage and the Preserve partitioning has been set to Clear.
D. The stage has a constraint with a node pool containing only one node.
Answer: A,D

QUESTIONS
A customer is interested in selecting the right RDBMS environment to run DataStage
Enterprise Edition to solve a multi-file and relational database data merge. The customer
realizes the value of running in parallel and is interested in knowing which RDBMS stage
will match the internal data partitioning of a given RDBMS.
Which RDBMS stage will satisfy the customer's request?
A. DB2/UDB Enterprise
B. Oracle Enterprise
C. ODBC Enterprise
D. Sybase Enterprise
Answer: A

QUESTIONS
You have a compiled job and parallel configuration file. Which three methods can be
used to determine the number of nodes actually used to run the job in parallel? (Choose
three.)
A. within DataStage Designer, generate report and retain intermediate XML
B. within DataStage Designer, show performance statistics
C. within DataStage Director, examine log entry for parallel configuration file
D. within DataStage Director, examine log entry for parallel job score
E. within DataStage Director, open a new DataStage Job Monitor

Answer: C,D,E
QUESTIONS
Which three are valid trigger expressions in a stage in a Job Sequence? (Choose three.)
A. Equality(Conditional)
B. Unconditional
C. ReturnValue(Conditional)
D. Difference(Conditional)
E. Custom(Conditional)
Answer: B,C,E

QUESTIONS
Which three are keyless partitioning methods? (Choose three.)
A. Entire
B. Modulus
C. Round Robin
D. Random
E. Hash
Answer: A,C,D

QUESTIONS
What is the purpose of the Oracle Enterprise Stage Exception Table property?
A. Enables you to specify a table which is used to capture selected column data that
meets user defined criteria for debugging purposes. The table needs to exist before the job is run.
B. Enables you to specify a table which is used to record the ROWID information on
rows that violate constraints during upsert/write operations.
C. Enables you to specify a table which is used to record ROWID information on rows
that violate constraints when constraints are re-enabled after a load operation.
D. Enables you to specify that a table should be created (if it does not exist) to capture all
exceptions when accessing Oracle.
Answer: C

QUESTIONS
Which two statements about shared containers are true? (Choose two.)
A. You can make a local container a shared container but you cannot make a shared
container a local container.
B. Shared containers can be used to make common job components that are available
throughout the project.
C. Changes to a shared container are automatically available in all dependant jobs
without a recompile.
D. Server shared containers allow for DataStage Server Edition components to be placed
in a parallel job.
Answer: B,D

QUESTIONS
Which environment variable controls whether performance statistics can be displayed in
Designer?
A. APT_RECORD_COUNTS
B. APT_PM_SHOW_PIDS
C. APT_NO_JOBMON
D. APT_PERFORMANCE_DATA
Answer: C

QUESTIONS
You are reading data from a Sequential File stage. The column definitions are specified
by a schema. You are considering whether to follow the Sequential File stage by either a
Transformer or a Modify stage. Which two criteria require the use one of these stages
instead of the other? (Choose two.)
A. You want to dynamically specify the name of an output column based on a job
parameter, therefore you select a Modify stage.
B. You want to replace NULL values by a specified constant value, therefore you select a
Modify stage.
C. You want to add additional columns, therefore you select a Transformer stage.
D. You want to concatenate values from multiple input rows and write this to an output
link, therefore you select a Transformer stage.
Answer: A,D
QUESTIONS
During a sequential file read, you experience an error with the data. What is a valid
technique for identifying the column causing the difficulty?

A. Set the "data format" option to text on the Record Options tab.
B. Enable tracing in the DataStage Administrator Tracing panel.
C. Enable the "print field" option at the Record Options tab.
D. Set the APT_IMPORT_DEBUG environmental variable.
Answer: C

QUESTIONS
What does setting an environment variable, specified as a job parameter, to PROJDEF do?
A. Populates the environment variable with the value of PROJDEF.
B. Explicitly unsets the environment variable.
C. Uses the value for the environment variable as shown in the DataStage Administrator.
D. Uses the current setting for the environment variable from the operating system.
Answer: C

QUESTIONS
How are transaction commit operations handled by the DB2/UDB Enterprise stage?
A. Commit operations can only be defined by the number of rows since the start of a transaction.
B. Transaction commits can be controlled by defining the number of rows per transaction
or by a specific time period defined by the number of seconds elapsed between commits.
C. Commit operations can only be defined by the number of seconds since the start of a transaction.
D. Commit operations can be defined globally by setting APT_TRANSACTION_ROWS variable.
Answer: B

QUESTIONS
A customer is interested in selecting the right RDBMS environment to run DataStage
Enterprise Edition to solve a multi-file and relational database data merge. The customer
realizes the value of running in parallel and is interested in knowing which RDBMS stage
will match the internal data partitioning of a given RDBMS. Which RDBMS stage will
satisfy the customer's request?
A. Sybase Enterprise
B. ODBC Enterprise
C. Oracle Enterprise
D. DB2/UDB Enterprise
Answer: D

QUESTIONS
Which two statements are correct when using the Change Capture and Change Apply
stages together on the same data? (Choose two.)
A. You must apply a Differences stage to the output of the Change Capture stage before
passing the data into the Change Apply stage.
B. A Compare stage must be used following the Change Capture stage to identify
changes to the change_code column values.
C. The input to the Change Apply stage must have the same key columns as the input to
the prior Change Capture stage.
D. Both inputs of the Change Apply stage are designated as partitioned using the same
partitioning method.
Answer: C,D

QUESTIONS
Which two would require the use of a Transformer stage instead of a Copy stage?
(Choose two.)
A. Drop a column.
B. Send the input data to multiple output streams.
C. Trim spaces from a character field.
D. Select certain output rows based on a condition.
Answer: C,D
QUESTIONS
Which two statements are correct about XML stages and their usage? (Choose two.)
A. XML Input stage converts XML data to tabular format.
B. XML Output stage converts tabular data to XML hierarchical structure.
C. XML Output stage uses XSLT stylesheet for XML to tabular transformations.
D. XML Transformer stage converts XML data to tabular format.
Answer: A,B

QUESTIONS
Which three statements about the Enterprise Edition parallel Transformer stage are
correct? (Choose three.)
A. The Transformer allows you to copy columns.
B. The Transformer allows you to do lookups.
C. The Transformer allows you to apply transforms using routines.
D. The Transformer stage automatically applies 'NullToValue' function to all
non-nullable output columns.
E. The Transformer allows you to do data type conversions.
Answer: A,C,E

QUESTIONS
Which partitioning method would yield the most even distribution of data without
duplication?
A. Entire
B. Round Robin
C. Hash
D. Random

Answer: B

QUESTIONS
Using FTP, a file is transferred from an MVS system to a LINUX system in binary
transfer mode. Which data conversion must be used to read a packed decimal field in the
file?
A. treat the field as a packed decimal
B. packed decimal fields are not supported
C. treat the field as ASCII
D. treat the field as EBCDIC
Answer: A

QUESTIONS
To encourage users to update the short description for a job, how can you make the short
description visible and updateable on the main canvas?

A. Add a Description Annotation field to the job canvas and select the Short Description property.
B. Right-click on the job canvas and choose Show Job Short Description in the submenu.
C. Click the Show Job Short Description option in the Job Properties.
D. Add an Annotation stage to the job canvas and copy and paste in the short description.
Answer: A

QUESTIONS
The last two steps of a job are an Aggregator stage using the Hash method and a
Sequential File stage with a Collector type of Auto that creates a comma delimited output
file for use by a common spreadsheet program. The job runs a long time because data
volumes have increased. Which two changes would improve performance? (Choose two.)
A. Change the Sequential stage to use a Sort Merge collector on the aggregation keys.
B. Change the Aggregator stage to use the sort method. Hash and sort on the aggregation keys.
C. Change the Sequential stage to a Data Set stage to allow the write to occur in parallel.
D. Change the Aggregator stage to a Transformer stage and use stage variables to
accumulate the aggregations.
Answer: A,B

QUESTIONS
Your business requirement is to read data from three Oracle tables that store historical
sales data from three regions for loading into a single Oracle table.The table definitions
are the same for all three tables, the only difference is that each table contains data for a
particular region.
Which two statements describe how this can be done? (Choose two.)
A. Create a job with a single Oracle Enterprise stage that executes a custom SQL
statement with a UNION ALL operator that outputs the data to an Oracle Enterprise stage.
B. Create a job with three Oracle Enterprise stages to read from the tables and output to a
Collector stage which in turns outputs the data to an Oracle Enterprise stage.
C. Create a job with three Oracle Enterprise stages to read from the tables and output to a
Funnel stage which in turn outputs the data to an Oracle Enterprise stage.
D. Create a job with a single Oracle Enterprise stage that executes a custom SQL
statement with a FETCH ALL operator that outputs the data to an Oracle Enterprise stage.
Answer: A,C

QUESTIONS
In a Transformer you add a new column to an output link named JobName that is to
contain the name of the job that is running. What can be used to derive values for this
column?
A. a DataStage function
B. a link variable
C. a system variable
D. a DataStage macro
Answer: D

QUESTIONS
Which statement describes a process for capturing a COBOL copybook from a z/OS
system?
A. Select the COBOL copybook using the Browse button and capture the COBOL
copybook with Manager.
B. FTP the COBOL copybook to the server platform in text mode and capture the
metadata through Manager.
C. FTP the COBOL copybook to the client workstation in binary and capture the
metadata through Manager.
D. FTP the COBOL copybook to the client workstation in text mode and capture the
copybook with Manager.

Answer: D

QUESTIONS
A dataset needs to be sorted to retain a single occurrence of multiple records that have
identical sorting key values. Which Sort stage option can be selected to achieve this?
A. Stable Sort must be set to True.
B. Sort Group must include the sort key values.
C. Allow Duplicates must be set to False.
D. Unique Sort Keys must be set to True.
Answer: C

QUESTIONS
Which two requirements must be in place when using the DB2 Enterprise stage in LOAD
mode? (Choose two.)
A. Tablespace cannot be addressed by anyone else while the load occurs.
B. User running the job has dbadm privileges.
C. Tablespace must be placed in a load pending state prior to the job being launched.
D. Tablespace must be in read only mode prior to the job being launched.
Answer: A,B

QUESTIONS
Which statement describes a process for capturing a COBOL copybook from a z/OS
system?
A. FTP the COBOL copybook to the server platform in text mode and capture the
metadata through Manager.
B. Select the COBOL copybook using the Browse button and capture the COBOL
copybook with Manager.
C. FTP the COBOL copybook to the client workstation in text mode and capture the
copybook with Manager.
D. FTP the COBOL copybook to the client workstation in binary and capture the
metadata through Manager.
Answer: C

QUESTIONS
A job reads from a dataset using a DataSet stage. This data goes to a Transformer stage
and then is written to a sequential file using a Sequential File stage. The default
configuration file has 3 nodes. The job creating the dataset and the current job both use
the default configuration file. How many instances of the Transformer run in parallel?
A. 3
B. 1
C. 7
D. 9
Answer: A

QUESTIONS
Which three features of datasets make them suitable for job restart points? (Choose
three.)
A. They are indexed for fast data access.
B. They are partitioned.
C. They use datatypes that are in the parallel engine internal format.
D. They are persistent.
E. They are compressed to minimize storage space.
Answer: B,C,D

QUESTIONS
An Aggregator stage using a Hash technique processes a very large number of rows
during month end processing. The job occasionally aborts during these large runs with an
obscure memory error. When the job is rerun, processing the data in smaller amounts
corrects the problem. Which change would correct the problem?
A. Set the Combinability option on the Stage Advanced tab to Combinable allowing the
Aggregator to use the memory associated with other operators.
B. Change the partitioning keys to produce more data partitions.
C. Add a Sort stage prior to the Aggregator and change to a sort technique on the Stage
Properties tab of the Aggregator stage.
D. Set the environment variable APT_AGG_MAXMEMORY to a larger value.
Answer: C

QUESTIONS
Which two must be specified to manage Runtime Column Propagation? (Choose two.)
A. enabled in DataStage Administrator
B. attached to a table definition in DataStage Manager
C. enabled at the stage level
D. enabled with environmental parameters set at runtime
Answer: A,C

QUESTIONS
In a Teradata environment, which stage invokes Teradata supplied utilities?
A. Teradata API
B. DRS Teradata
C. Teradata Enterprise

D. Teradata Multiload
Answer: D

QUESTIONS
Which statement is true when Runtime Column Propagation (RCP) is enabled?
A. DataStage Manager does not import meta data.
B. DataStage Director does not supply row counts in the job log.
C. DataStage Designer does not enforce mapping rules.
D. DataStage Administrator does not allow default settings for environment variables.
Answer: C
QUESTIONS
What are two ways to delete a persistent parallel dataset? (Choose two.)
A. standard UNIX command rm
B. orchadmin command rm
C. delete the dataset Table Definition in DataStage Manager
D. delete the dataset in Data Set Manager
Answer: B,D

QUESTIONS
A source stream contains customer records, identified by a customer ID. Duplicate
records exist in the data. The business requirement is to populate a field on each record
with the number of duplicates and write the results to a DB2 table.
Which job design would accomplish this in a single job?
A. This cannot be accomplished in a single job.
B. Use a Copy stage to split incoming records into two streams. One stream uses an
Aggregator stage to count the number of duplicates. Join the Aggregator stage output
back to the other stream using a Join stage.
C. Use an Aggregator stage to group incoming records by customer ID and to count the
number of records in each group. Output the results to the target DB2 table.
D. Use stage variables in a Transformer stage to keep a running count of records with the
same customer ID. Add this count to a new output field and write the results to the DB2
table.
Answer: B

QUESTIONS
Which two field level data type properties are schema properties that are standard SQL
properties? (Choose two.)
A. the character used to mark the end of a record
B. values to be generated for a field
C. whether a field is nullable
D. the value that is to be written to a sequential file when the field is NULL
Answer: A,C

QUESTIONS
Which three are the critical stages that would be necessary to build a Job Sequence that:
picks up data from a file that will arrive in an directory overnight, launches a job once the
file has arrived, sends an email to the administrator upon successful completion of the
flow? (Choose three.)
A. Sequencer
B. Notification Activity
C. Wait For File Activity
D. Job Activity
E. Terminator Activity
Answer: B,C,D

QUESTIONS
Which two would determine the location of the raw data files for a parallel dataset?
(Choose two.)
A. the orchadmin tool
B. the Data Set Management tool
C. the DataStage Administrator
D. the Dataset stage
Answer: A,B

QUESTIONS
A bank receives daily credit score updates from a credit agency in the form of a fixed
width flat file. The monthly_income column is an unsigned nullable integer (int32)
whose width is specified as 10, and null values are represented as spaces. Which
Sequential File property will properly import any nulls in the monthly_income column of
the input file?

A. Set the record level fill char property to the space character (' ').
B. Set the null field value property to a single space (' ').
C. Set the C_format property to '"%d. 10"'.
D. Set the null field value property to ten spaces (' ').
Answer: D
QUESTIONS
Which two can be implemented in a Job Sequence using job parameters? (Choose two.)
A. All options of the Start Loop Activity stage.
B. The body of the email notification activity using the user interface.
C. A command to be executed by a Routine Activity stage.
D. Name of a job to be executed by a Job Activity stage.
Answer: A,C

QUESTIONS
In which situation should a BASIC Transformer stage be used in a DataStage EE job?

A. in a job containing complex routines migrated from DataStage Server Edition
B. in a job requiring lookups to hashed files
C. in a large-volume job flow
D. in a job requiring complex, reusable logic
Answer: A

QUESTIONS
Which command can be used to execute DataStage jobs from a UNIX shell script?
A. dsjob
B. DSRunJob
C. osh
D. DSExecute
Answer: A

QUESTIONS
Which three UNIX kernel parameters have minimum requirements for DataStage
installations? (Choose three.)
A. MAXUPROC - maximum number of processes per user
B. NOFILES - number of open files
C. MAXPERM - disk cache threshold
D. NOPROC - no process limit
E. SHMMAX - maximum shared memory segment size
Answer: A,B,E

QUESTIONS
Which two requirements must be in place when using the DB2 Enterprise stage in LOAD
mode? (Choose two.)
A. Tablespace must be in read only mode prior to the job being launched.

B. User running the job has dbadm privileges.
C. Tablespace must be placed in a load pending state prior to the job being launched.
D. Tablespace cannot be addressed by anyone else while the load occurs.
Answer: B,D

QUESTIONS
Which Oracle Enterprise Stage property can be set using the DB Options group to tune
the performance of your job with regard to the number of network packets transferred
during the execution of the job?
A. memsize
B. blocksize
C. arraysize

D. transactionsize
Answer: C
QUESTIONS
Which two statements are true of the column data types used in Orchestrate schemas?
(Choose two.)

A. Orchestrate schema column data types are the same as those used in DataStage stages.
B. Examples of Orchestrate schema column data types are varchar and integer.
C. Examples of Orchestrate schema column data types are int32 and string [max=30].
D. OSH import operators are needed to convert data read from sequential files into
schema types.
Answer: C,D

QUESTIONS
An XML file is being processed by the XML Input stage. How can repetition elements be
identified on the stage?
A. No special settings are required. XML Input stage automatically detects the repetition
element from the XPath expression.
B. Set the "Key" property for the column on the output link to "Yes".
C. Check the "Repetition Element Required" box on the output link tab.
D. Set the "Nullable" property for the column on the output link to "Yes".
Answer: B

QUESTIONS
The high performance ETL server on which DataStage EE is installed is networked with
several other servers in the IT department with a very high bandwidth switch. A list of
seven files (all of which contain records with the same record layout) must be retrieved
from three of the other servers using FTP.
Given the high bandwidth network and high performance ETL server, which approach
will retrieve and process all seven files in the minimal amount of time?
A. In a single job, use seven separate FTP Enterprise stages the output links of which
lead to a single Sort Funnel stage, then process the records without landing to disk.
B. Setup a sequence of seven separate DataStage EE jobs, each of which retrieves a
single file and appends to a common dataset,
then process the resulting dataset in an eighth DataStage EE job.
C. Use three FTP Plug-in stages (one for each machine) to retrieve the seven files and
store them to a single file on the fourth server, then use the FTP Enterprise stage to
retrieve the single file and process the records without landing to disk.
D. Use a single FTP Enterprise stage and specify seven URI properties, one for each file,
then process the records without landing to disk.
Answer: D

QUESTIONS
Which two statements are true for parallel shared containers? (Choose two.)
A. When logic in a parallel shared container is changed, all jobs that use the parallel
shared container inherit the new shared logic without recompiling.
B. Within DataStage Manager, Usage Analysis can be used to build a multi-job compile
for all jobs used by a given shared container.
C. All container input and output links must specify every column that will be defined
when the container is used in a parallel job.
D. Parallel shared containers facilitate modular development by reusing common stages
and logic across multiple jobs.
Answer: B,D

QUESTIONS
Which task is performed by the DataStage JobMon daemon?
A. writes the job's OSH script to the job log
B. provides a snapshot of a job's performance
C. graceful shutdown of DataStage engine
D. automatically sets all environment variables
Answer: B

QUESTIONS
The last two steps of a job are an Aggregator stage using the Hash method and a
Sequential File stage with a Collector type of Auto that creates a comma delimited output
file for use by a common spreadsheet program. The job runs a long time because data
volumes have increased. Which two changes would improve performance? (Choose two.)
A. Change the Aggregator stage to a Transformer stage and use stage variables to
accumulate the aggregations.
B. Change the Sequential stage to a Data Set stage to allow the write to occur in parallel.
C. Change the Aggregator stage to use the sort method. Hash and sort on the aggregation keys.
D. Change the Sequential stage to use a Sort Merge collector on the aggregation keys.
Answer: C,D
QUESTIONS
Which two stages allow field names to be specified using job parameters? (Choose two.)
A. Transformer stage
B. Funnel stage
C. Modify stage
D. Filter stage
Answer: C,D

QUESTIONS
Which two statements describe the operation of the Merge stage? (Choose two.)
A. Duplicate records should always be removed from the master data set and from the
update data sets if there is more than one update data set.
B. Merge stages can only have one reject link.
C. Duplicate records should always be removed from the master data set and from the
update data set even if there is only one update data set.
D. Merge stages can have multiple reject links.
Answer: A,D
~JeMuk

QUESTIONS
Which two statements describe the properties needed by the Oracle Enterprise stage to
operate in parallel direct load mode. (Choose two.)
A. Only index organized tables allow you to use parallel direct load mode and you need
to set both DIRECT and PARALLEL properties to True, otherwise the load will be
executed in parallel but will not use direct mode.
B. Set the Write Method to Load.
C. The table can have any number of indexes but the job will run slower since the
indexes will have to be updated as the data is loaded.
D. The table must not have any indexes, or you must include the rebuild Index Mode
property unless the only index on the table is the primary key in which case you can use
the Disable Constraints property.
Answer: B,D

QUESTIONS
Which feature in DataStage will allow you determine all the jobs that are using a Shared
Container?
A. Extended Job view
B. Reporting Assistant
C. Usage Analysis
D. Impact Analysis
Answer: C

QUESTIONS
Data volumes have grown significantly in the last month. A parallel job that used to run
well is now using unacceptable amounts of disk space and running slowly. You have
reviewed the job and explicitly defined the sorting and partitioning requirements for each
stage but the behavior of the job has not changed. Which two actions improve
performance based on this information? (Choose two.)
A. Change the sort methods on all sorts to "Don't Sort - Previously Grouped".
B. Increase the value of environment variable APT_PARTITION_NUMBER to increase
the level of parallelism for the sorts.
C. Enable the environment variable APT_SORT_INSERTION_CHECK_ONLY in the job.

D. Enable the environment variable APT_NO_SORT_INSERTION in the job.
Answer: C,D

QUESTIONS
You have a series of jobs with points where the data is written to disk for restartability.
One of these restart points occurs just before data is written to a DB2 table and the
customer has requested that the data be archivable and externally shared. Which storage
technique would optimize performance and satisfy the customer's request?
A. DB2 tables
B. filesets
C. sequential files
D. datasets
Answer: B
QUESTIONS
Which three actions are performed using stage variables in a parallel Transformer stage?
(Choose three.)
A. A function can be executed once per record.
B. A function can be executed once per run.
C. Identify the first row of an input group.
D. Identify the last row of an input group.
E. Lookup up a value from a reference dataset.
Answer: A,B,C

QUESTIONS
Which three are valid ways within a Job Sequence to pass parameters to Activity stages?
(Choose three.)
A. ExecCommand Activity stage
B. UserVariables Activity stage
C. Sequencer Activity stage
D. Routine Activity stage
E. Nested Condition Activity stage
Answer: A,B,D

QUESTIONS
Which three privileges must the user possess when running a parallel job? (Choose
three.)
A. read access to APT_ORCHHOME
B. execute permissions on local copies of programs and scripts
C. read/write permissions to the UNIX/etc directory
D. read/write permissions to APT_ORCHHOME
E. read/write access to disk and scratch disk resources
Answer: A,B,E

QUESTIONS
When importing a COBOL file definition, which two are required? (Choose two.)
A. The file you are importing is accessible from your client workstation.
B. The file you are importing contains level 01 items.
C. The column definitions are in a COBOL copybook file and not, for example, in a COBOL source file.
D. The file does not contain any OCCURS DEPENDING ON clauses.
Answer: A,B

PowerCenter7 Architecture and Administration

2010-06-10T07:33:00.000-07:00

QUESTIONS
When the power center server loads IBM DB2 the session fails when you use a database partition type on Numeric column
Answer: False

QUESTIONS
As400 table columns larger the 4000bytes will truncate(T/F)
Answer: False

QUESTIONS
Choose which one is false?
Answer: A valid workflow can be scheduled from workflow->edit option

QUESTIONS
Are loops supported in workflows?
Answer: No

QUESTIONS
The workflow manager receives notification messages from _____________.
Answer: repository server

QUESTIONS
Remember all the information that is attached to object shortcut. I cant remeber the exact question but you must get question shortcut.
Answer: The name of the user who created the short cut

QUESTIONS
What are Informatica data movement modes?
Answer: Ascii and Unicode

QUESTIONS
The repository server must be running before a repository is created - True/False?
Answer: x

QUESTIONS
I have got one question on Where can you create workflow tasks?
Answer: in the task developer, the workflow designer, the worklet designer

QUESTIONS
There will be 4 options.You have to select one option regarding the activity that is performed by Repository Manager.
Answer: Create folders, validate mappings and mapplets, create repository users

QUESTIONS
If you concatenate data from a normalizer transformation the ports concatenated cannot contain occurs
Answer: this is a known limitation in V7

QUESTIONS
If there is no space in the systema and you are going to save a large mapping then what will happen?
Answer: Informatica will shut down unexpectedly.
QUESTIONS
In a workflow manager you can create a session
Answer: by creating a task in the task developer.

QUESTIONS
Select the false statement.
Answer: Workflows must be within tasks to be executed

QUESTIONS
2 servers are registered to a repository. Say an existing workflow runs on Server A. What is required to run the same workflow on Server B
Answer: edit the workflow properties.

QUESTIONS
What happens if a user attempts to delete the owner of a connection object?
Answer: The workflow manager automatically assigns the ownership to the administrator user.

QUESTIONS
What is the definition of a local shortcut?
Answer: A shortcut to an object in the same repository

QUESTIONS
What privileges are required to an user to open a foler?
Answer: Does not have write permissions on the folder
QUESTIONS
Mug up all the operations that can be performed using pmrep command.You must get a question on that.
Select the operation that is not available in the pmrep command?
Answer: Change the password expiration date of a user

QUESTIONS
Which of the following is not a type of workflow task?
Answer: Mapping

QUESTIONS
There will be 4 options.You have to select one option regarding the activity that is performed using designer?
Answer: Run the debugger, validate mappings and mapplets and import source definitions

QUESTIONS
What are the views available in the workflow monitor?
Answer: Task view, Gantt chart view

QUESTIONS
Which types of objects can read values from parameter files?
Answer: Mappings, Sessions and workflows.

QUESTIONS
Which set of privileges would be sufficient to use the workflow monitor with a specific folder?
Answer: Use the work flow monitor and execute permission on the folder
QUESTIONS
Where can the database connection properties be configured?
Answer: From the workflow manager menu or session properties tab.

QUESTIONS
A user must create copy of an object within another folder in the same repository. Select the statement that is true?
Answer: The folder in which the object is being copied should be open

QUESTIONS
What permissions are required to copy objects from one folder to another?
Answer: x

QUESTIONS
Which service you need to start first in informatica - Repository server, power center server or agent?
Answer: Start the Repository server

QUESTIONS
What is the purpose of output window?
Answer: x

QUESTIONS
Select the statement that is false?
Answer: The repository agent helps us to configure and start the repository server
QUESTIONS
Read carefully the Repository Manager part.
You need to select one option regarding the activity that is performed by Repository Manager.
Answer: Delete workflows and tasks, start workflow, configure connections

QUESTIONS
If primary index is present in oracle then does the bulk option work? - true/false
Answer: x

QUESTIONS
The power center server loads nulls into oracle. Which of the following are true
Answer: None of the above

QUESTIONS
Read the timer task part carefully from manual. You will get one question.
Answer: x

QUESTIONS
With power centre 7 which set of objects can be scheduled?
Answer: x

QUESTIONS
When the Use connection variable option is selected in the relational browser, where is the database connection specified
Answer: x
QUESTIONS
If repository tables are deleted then can you recover those?
Answer: x

QUESTIONS
A user is installing power centre 7 client software in a new client machine? Does he need a database userid and password?
Answer: NO

QUESTIONS
Which console is required prior to upgrading a repository to version 7
Answer: A repository server administration console and running repository server.

QUESTIONS
IS a repository server required to uses power centre?
Answer: x

QUESTIONS
A work flow is defined as
Answer: A set of instructions that tell the Informatica server how execute a set of tasks

QUESTIONS
Remember all the components that are visible in repository manager
Answer: Business components, mappings reusable transformations.
QUESTIONS
The power centre server fails to connect to the oracle database for the following reason.- you have to choose one answer.
Answer: x

QUESTIONS
You will be shown an error message related to a database and you have to specify the database the message is related to? Probably the
answer will be oracle
Answer: x

QUESTIONS
What is the purpose of lik condition?
Answer: prevent certain tasks from being executed.

QUESTIONS
How can you change the date/time display format of workflow manager ?
Answer: x

QUESTIONS
What privileges and permissions will be required to create a new connection object?
Answer: x

QUESTIONS
What is the meaning of the term execute permission on a folder?
Answer: Allows the user to execute or reschedule a workflow within that folder.

QUESTIONS
what is disadvantage of using shortcuts instead of copies?
Answer: change in shortcuts may invalidate mappings.

QUESTIONS
Which Informatica servers are visible in the workflow monitor after the user has connected to the repository?
Answer: All servers registered with repository

QUESTIONS
A workflow manager can be used to?
Answer: Create workflows that contains existing session tasks

QUESTIONS
Which types of objects are visible in the workflow monitor task view?
Answer: Workflows, Tasks and worker servers.

PowerCenter 7 Mapping Design

2010-06-10T07:17:00.000-07:00

PowerCenter 7 Mapping Design

QUESTIONS
Specify which designer option cannot be set locally (differently for each client machine)

A. Whether to display column names or business names for all SQ
B. whether to import primary keys when importing a target definition
C. whether to import data type lengths and precisions for SQ
D. whether to display targets grouped by database in the navigator window
Answer: B

QUESTIONS
What types of transformation ports are supported?

A. Input,output,variable
B. input,output,input/output,variable
C. input,output,variable,aggregate
D. input,output,input/output,variable,parameter
Answer: B

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

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
Anserw: A

QUESTIONS
What types of targets are supported in a mapping?

A. Relational
B. Relational and XML
C. Relationsal,XML and flat file
D. Relational,XML, flat file and COBOL
Answer: C

QUESTIONS
Under what circumtances a target definition be edited fromt he mapping desinger, within
the mapping where the target definition is being used?
Answer: Never

QUESTIONS
Whether or not a session task can be considered to have heterogenous targets is determined by

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
Answer: B
QUESITONS
A transformation has the following ports:

Port_1:decimal,input port
Port_2:decimal,inpur 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

A. iif(Port_1>5,Port_2*10,Port*15)
B. iif(Port_1>5,Port*10)
C. iif(Port_5>7,Port_5*10,Port_5*15)
D. iif(Port_3>8,Port_3*10,Port_3*15)
Answer: C

QUESTIONS
A transformation has the following ports:

Port_1:String,Input port
Port_2:String,Input port
Port_3:String,Output port

Which of the following expressions shown below would be valid?
Assume that it is associated that Port_3 and that the string
ENDSTRING is not a parameter or mapping variable

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"))
Answer: D

QUESITONS
A developer wishes to connect two ports that have different data types, is it allowed?

A. Yes, always
B. Yes, provided tht the data types are compatible. The informatica server
will automatically convert 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
Answer: B

QUESIONS
Which situation would always cause a mapping to be invalid?

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 transaformation
D. A sQ with port names that do not match a field name in the corresponding cource definition
Answer: C

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

A. Dmato
B. Dmto
C. amato
D. Damato
Answer: D

QUESTIONS
The following are valid Informatica test funstions:

A. isnull,is_date,is_number,is_spaces
B. isnotnull,isdate,ishere
C. is null,isanumber,isadte
D. no_doubt,is_live
Answer: A
QUESTIONS
Can a port in an Expression transformation be given the name DISTINCT?

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 Iformatica
expression reserved word
Answer: A

QUESTIONS
A user finds that she is unable to generate default query in SQ. What is the
possible reason for that?
Answer: The SQ does not have one ro more ports connected to another transformation
or target in the mapping

QUESTIONS
Under what circumstances should a user override the default source qualifier join?

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
C. The Source qualifier is being used to join two or more flat files
D. The order of source qualifier ports has been changed since the mapping was first changed
Answer: A

QUESTIONS
What type od data source(s) can be read with the source qualifier transfromation?

A. Relational databases
B. Relational databases and flat files
C. Relational database, flat files and cobol
D. Relational databases, flat files, cobol and certified ERP sources such a peoplesoft and SAP/R3
Answer: B

QUESTIONS
Can a source qualifier be used to perform an outer join when joining 2 database tables?

A. Yes, but it requires that the user modify the join SQL by hand
B. Yes, but it requires that the user select left ot right outer join as the source qualifier join type property
C. No, because this may produce null values in the ports and the cource 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
Answer: A

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

A. Aggregate and non aggregate expressions, provided thay are not used togethe rin a single 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
Answer: C
QUESTIONS
What type of expressions can be used in an Aggregator transformation variable port?

A. Aggregate or non Aggregate expressions, provided they are not used together in a single variable port
B. Aggregate and no aggregate expressions together in the same variable port
C. Aggregate, non aggragate 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
Answer: D

QUESTIONS
The aggreagte cache always consists of

A. As 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 dile on the local disk if the file names and directory 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 prperty has been set to true when the session runs
Answer: A

QUESTIONS
An aggregator transformation will process 50 million rows per session, and the width of each row is 3 KB. There are
2 group by ports, Month and Customer. Assuming that the data set contains two yars worth of data and there are 500 customers whi are represented for each month on the data set, estimate the required data cache size.

A. 6 MB
B. 12 MB
C. 18 MB
D. 36 MB
Answer: D

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

A. The session would fail when the aggreagtor begins processing data
B. The session would fail when the data RAM cache exceeds 2 MB
C. The informatica server would automatically expand the data RAM cache to accomodate the additional data
D. The informatica server would automatically page the data to a file on the local disk
Answer: D

QUESTIONS
Select the statement below that is true.

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 suppoe\rts 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
Answer: C

QUESTIONS
When a sorter transformation is included in a mapping that is run in a workflow, when does the data sort occur?

A.befor ethe workflow runs
B. After the workflow runs
C. Either before or after the workflow runs
D. During the time the Workflow runs (session task run rime)
Answer: D
QUESTIONS
When using the sorter transformation to sort a very large data set additional RAM resources may be required to accomodate the data. Which machine would provide this RAM

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
Answer: A

QUESTIONS
Why the sorted transformation considered an active transfromation?

A. When the case sensitive property is set to true, rows that do not meet the case criteria in the sort may be discarded
B. When more than 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 distinc tOutput rows property is set to true duplicate rows are discarded
D. When the sort direction properties are not the same for all sort keys , a single input row may be outputted more than one time
Answer: C

QUESTIONS
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.

A. The informatica server generates a sequence of numbers for the NEXTVAL and Currval is set to the Nextval plus one.
B. The informatica server generates a sequence of numbers for Nextval and Currval is set to Nextval plus one
C. The informatica server generates a sequence of numbers for Curval and Nextval is set to Currval plus one
D. The informatica server generates a sequence of numbers for Currval and Nextval is set to Currval plus the integer value
set dor the Increment By sequence generator property
Answer: A

QUESTIONS
Can the NEXTVAL port of a sequence generator transfromation be connected to the PRIMARY KEY column of two different target table definitions in a mapping?
Assume that the two definitions represent diferent physical tables on the database server.

A. Yes, always
B. Yes, but only if two tables share a primary - foreign key relationship
C. No, because the sequence generator produces unique values and this would result in a unique constraint error
D.No, this is not allowd in the Mapping Desinger
Answer: A
~evaAm

QUESTIONS
What is one disadvantage of using an unconnected lookup transformation?

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
Answer: C

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

A. Database
B. Database and flat file
C. Database, flat file and COBOL file
D. Database, flat file, COBOL file, and XML file
Answer: B
QUESTIONS
Select the statement below that most accurately describes how a dynamic lookup cache is designed to function

A. The informatica server compares the target data to the cache data as each row is paased 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, updated or deletes rows oin the cache as it passes rows to target
Answer: B

QUESTIONS
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

A. Static cache condition: sales_amt > sales_amt1 unconnected
B. Static cache condition: sales_amt=sales_amt1 unconnected
C. Dynamic cache condition: sales_amt > sales_amt1 connected
D. Dynamic cache condition: sales_amt = sales_amt1 connected
Answer: C

QUESTIONS
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.

A. Cache the lookup
B. Include an Order by clause in the sql the sorts the look up fields in descending order
C. Generate a default query after override SQL has been entered
D. Validate the override SQL on teh actual database table.
Answer: A

QUESTIONS
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?

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.
Answer: A

QUESTIONS
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 required to join these sources?

A. One
B. Two
C. Three
D. One if the flat file and the XML file are located in the same directiory
Answer: B

QUESTIONS
A developer is using a Joiner transformation to join two data streams and a full outer join is desired. Select the statement that is true?

A. A joiner transformation may perform a full outer join under all conditions.
B. A joiner transformation may perform full outer join only when one or more sides of the join are from relational database.
C. A joiner transformation may perform full outer join only when all sides of the join originate from relational database
Answer: A
QUESTIONS
Can the Joiner transformation perform a case sensitive string comparison as part of the join condition?

A. Yes, always
B. No
C. Yes when the informatica server is running on the Unix OS, no when it is running on the Windows OS
D. Yes, when the database that is being used for the join supports case sensitive string comparison.
Answer: A

QUESTIONS
A user sets the Treat source rows as session property to Update. What is the effest of making this selection?

A. All records processed by the session will be treated as update statements on the target table
B. All records processed by the session will be treated as update statements on the target table.
provided that a primary key constraint exists on the corresponding target table definition.
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
Answer: B
~evaAm

QUESTIONS
What type of expressions can b used in an Update strategy transformation?

A. Immediate If (IIF)
B. Immediate If (IIF) or DECODE
C. Ummediate If (IIF), DECODE, or IF Then Else
D. Any valid non aggregate expression that is supported by transformational language
Answer: D

QUESTIONS
Specify which statement below describes the behavior of a router transformation that has three groups defined

A. The condition for each group will be evaluted for each row, and the first group that evalutes to a true results will pass that row out
of the transformation through that groups output ports. If no group evalutes 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 true result will pass
out of the transformation through that group's ports. If no group evalutes 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 evaluted for each row, and the first group that evalutes to true result will pass the row out of the
transformation through that group's output ports . If no group evalutes to 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 evaluted for each row, and each group that evalutes to true result will pass the row out of the transformation through
that group's output ports. If no group evalutes to 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.
Answer: D

QUESTIONS
A filter transformation contains the follwoing condition:
ITEM_COST>100.00. The value for ITEM_COST for a specific row is 35. What will happen to that row?

A. It is dropped from the mapping flow
B. It is dropped from the mapping flow and written to the reject file
C. It is dropped from the mapping flow and written to the reject file if the filter property
Forward filtered row is set to true.
D. if the filter property Forward filtered row is set to true, the row is sent to the downstream transformation, if any,
but not written to the target table.
Answer: A

QUESTIONS
A major difference between the router and filetr transformations is that filtered wors can be captured in the Router

A. True
B. False
Answer: A
QUESTIONS
How many homogenous source definitions can be placed in a mapplet?

A. One
B. One for each source qualifier transformation
C. Zero to One if mapplet does not contain an input or ERP source qualifier transformation
D. Unlimited
Answer: D

QUESTIONS
How many output transformations can be placed in a mapplet?

A. One
B. One or more
C. One or more unless there is one target definition in the mapplet, in which case zero output transformation
are permitted
D. Any number
Answer: B
~arvKh

QUESTIONS
Select the statement below that is true.

A. A mapplet that does not contain an input transaformation is always as active mapplet.
B. A mapplet that does bot contain an input transforamation 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 active mapplet by adding one or more lookup transformation to the mapplet.
Answer: A

QUESTIONS
will the update transformation used before the union transformamtion
Answer: no

Atoz Consulting Services--atozcs.com

2010-01-03T03:24:00.000-08:00

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

2009-12-09T02:22:00.000-08:00

• 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

2009-11-26T03:37:00.000-08:00

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

2009-11-21T08:05:00.001-08:00

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

Informatica Migration

2009-11-20T07:00:00.000-08:00

1 Introduction
In every application deployment, a migration strategy must be formulated to ensure a clean migration of objects between development, test, quality assurance and production. The migration strategy is influenced largely by the technologies deployed to maintain the development and production environments. The means to migrate work from development to production depends largely on the repository environment in case of Informatica, which is either:
· A shared environment, or
· Separate, dedicated environments (Informatica repositories) for development, test/UAT and
Production
2 Using Configuration tool
The migration strategy is influenced largely by the technologies deployed to maintain the development and production environments.

2.1 Migration Process

The mappings are created/modified only in the Development environment. These are then migrated into the Test/UAT environment, and then eventually into the Production environment. There are three main techniques to migrate from Development to Production, each involving some advantages and disadvantages:
· Export and Import the entire repository

· Export and Import the required mappings

Any of the approach for migration can be selected depending upon the requirement. Here we will be discussing the first approach i.e. export and import of the entire repository. The two migrations involved (Dev to Test/UAT and Test/UAT to Prod) will follow the following procedures:

2.2 Development to Test/UAT

Once the unit testing for all the mappings related to the project has been successfully completed, the mappings would be marked ready for migration to the Test/UAT environment. The following steps (in the specified order) would be followed for migrating the objects from the development environment to the Test/UAT environment:
· Take backups of both the Development and the Test/UAT repository and name them as pre-migration backups.
· Import all the required objects from the development environment to the Test/UAT environment.
· Take backup of the Test/QA repository and named it as a post-migration backup.
· Import these files into Test/UAT Informatica repository.

2.3 Test/UAT to Production

Once the testing has been successfully completed, the Test/UAT repository would be marked ready for migration to the Production environment. The following steps (in the specified order) would be followed for migrating the mapping/session/program from the Test/UAT environment to the Production environment:
· Take backups of both the Test/UAT and the Production repository and name them as pre-migration backups.
· Copy the concerned components from the Test/UAT to the Production repository (Make sure that the existing folder in the Production repository is replaced).
· Take backup of the Production repository and name it as a post-migration backup.

3 Using Informatica Version Control
3.1 Principle

If we follow the three main principles of Version Control, you will soon adopt a structured and methodological approach to managing the Informatica repository. These principles are:

3.1.1 Principle One
Use the three-stage approach. Utilize at least three different environments for your project development; one for development, one for testing, and one for production.

3.1.2 Principle Two
Make all coding changes in the development environment, and never in the test or production environments.

3.1.3 Principle Three
Set all components in the test and production environments to read-only. This enforces principle two.

Although these are simple principles, they are closely linked and are all of equal importance. They will provide you with a starting point for successful version control management.

3.2 The Three-stage Approach

Most Informatica job developers adopt a three-stage approach to developing their Informatica mappings, which has become the de facto standard.

These stages are:

· Development stage
· Test stage
· Production stage.

SQL Override

2009-11-20T06:50:00.000-08:00

Target update override:(done in designer in target instance and also view in session's target option)
Update Override it is an option available in TARGET instance
===============================================
Update override with non Primary Key field in the where condition:
Ex: Target table Target1 has A1,B1 as Primary key defined (local to informatica in designer)
UPDATE OVERRIDE SQL ON TARGET TABLE:

UPDATE Target1 SET C=:TU.C,D=:TU.D
WHERE A1=:TU.A1 AND B1=:TU.B1

--// already generated by informatica
================================================================
SQLOverride and Lookup SQL Override
==============================================================
Coming to SQL override it is an option available in Source Qualifier and Lookup SQL Override available in Lookup transafornmation where u can include joins ,filters,Group by and order byObviously we have some differences between those 2 Overrides...
1. SQL Override is to limit the number of incoming rows entering the mapping pipelineLookup SQL Override is to limit the number of lookup rows to avoid the whole table scan by saving the lookup time & cache it uses...
2. Lookup SQL Override uses "Order By" clause by defaultSQL Override doesn't uses it & should provide in our query if we require it
3. SQL Override can provide any kind of join by writing the queryLookup SQL Override provides only Non-Equi joins
4. Lookup SQL Override gives only one record even if it finds multiple records for a single condition SQL Override doesn't do that...
=========================================================
What is the difference between ODBC , DSN , NATIVE and TCP/IP Connections
===========================================================
ODBC is the database drivers which is used to connect from clinet to the relational database.
DSN is the data service name which is the name of the odbc name give by the customer in order to access from the client
NATIE Drivers is the drivers which allows the client to acess the source and target after loading them from database to the workspace
TCP/IP it is the protocal used for connecting the remote computers.here the repository contacts with informatica repository server through TCP/IP

Friends-

2009-11-08T06:58:00.000-08:00

SQL,INFORMATICA sites

2009-11-05T03:59:00.000-08:00

http://deviinformatica.blogspot.com/

Datastage tutorla
http://datastage-tutorials.blogspot.com/

Informatica 8.x

2009-11-04T07:49:00.000-08:00

PowerCenter 8.x components are
1. PowerCenter Domain 2. PowerCenter Repository3. Administration Console
4. PowerCenter Client 5. Repository Service 6. Integration Service
PowerCenter Domain
A domain is the primary unit for management and administration of services in PowerCenter.
Components of a domain are Node, Service Manager and Application Services.
Node
Node is the logical representation of a machine in a domain. The machine in which the PowerCenter
is installed acts as a Domain and also as a primary node. We can add other machines as nodes in the
domain and configure the nodes to run application services such as the Integration Service or
Repository Service. All service requests from other nodes in the domain go through the primary
node also called as ‘master gateway’.
The Service Manager
The Service Manager runs on each node within a domain and is responsible for starting and running
the application services. The Service Manager performs the following functions,
>Alerts. Provides notifications of events like shutdowns, restart
>Authentication. Authenticates user requests from the Administration Console, PowerCenter Client,
Metadata Manager, and Data Analyzer
>Domain configuration. Manages configuration details of the domain like machine name, port
>Node configuration. Manages configuration details of a node metadata like machine name, port
>Licensing. When an application service connects to the domain for the first time the licensing
registration is performed and for subsequent connections the licensing information is verified
>Logging. Manages the event logs from each service, the messages could be ‘Fatal’, ‘Error’, ‘Warning’, ‘Info’
>User management. Manages users, groups, roles, and privileges
Application services
The services that essentially perform data movement, connect to different data sources and manage
data are called Application services, they are namely Repository Service, Integration Service, Web
Services Hub, SAPBW Service, Reporting Service and Metadata Manager Service. The application services
run on each node based on the way we configure the node and the application service
Domain Configuration
Some of the configurations for a domain involves assigning host name, port numbers to the nodes,
setting up Resilience Timeout values, providing connection information of metadata Database, SMTP
details etc. All the Configuration information for a domain is stored in a set of relational database
tables within the repository. Some of the global properties that are applicable for Application
Services like ‘Maximum Restart Attempts’, ‘Dispatch Mode’ as ‘Round Robin’/’Metric Based’/’Adaptive’
etc are configured under Domain Configuration
2. PowerCenter Repository
The PowerCenter Repository is one of best metadata storage among all ETL products. The repository is
sufficiently normalized to store metadata at a very detail level; which in turn means the Updates to
therepository are very quick and the overall Team-based Development is smooth. The repository data
structure is also useful for the users to do analysis and reporting.
Accessibility to the repository through MX views and SDK kit extends the repositories capability from
a simple storage of technical data to a database for analysis of the ETL metadata.
PowerCenter Repository is a collection of 355 tables which can be created on any major relational database.
The kinds of information that are stored in the repository are,
1. Repository configuration details
2. Mappings
3. Workflows
4. User Security
5. Process Data of session runs
For a quick understanding,
When a user creates a folder, corresponding entries are made into table OPB_SUBJECT; attributes like
folder name, owner id, type of the folder like shared or not are all stored.When we create\import sources
and define field names, datatypes etc in source analyzer entries are made into opb_src and OPB_SRC_FLD.When
target and related fields are created/imported from any database entries are made into tables like OPB_TARG
and OPB_TARG_FLD.Table OPB_MAPPING stores mapping attributes like Mapping Name, Folder Id, Valid status and
mapping comments.Table OPB_WIDGET stores attributes like widget type, widget name, comments etc. Widgets are
nothing but the Transformations which Informatica internally calls them as Widgets.Table OPB_SESSION stores
configurations related to a session task and table OPB_CNX_ATTR stores information related to connection objects.
Table OPB_WFLOW_RUN stores process details like workflow name, workflow started time, workflow completed time,
server node it ran etc.REP_ALL_SOURCES, REP_ALL_TARGETS and REP_ALL_MAPPINGS are few of the many views created
over these tables.
PowerCenter applications access the PowerCenter repository through the Repository Service. The Repository Service
protects metadata in the repository by managing repository connections and using object-locking to ensure object
consistency.
We can create a repository as global or local. We can go for‘global’ to store common objects that multiple
developers can use through shortcuts and go for local repository to perform of development mappings and workflows.
From a local repository, we can create shortcuts to objects in shared folders in the global repository. PowerCenter
supports versioning. A versioned repository can store multiple versions of an object.
3. Administration Console
The Administration Console is a web application that we use to administer the PowerCenter domain and PowerCenter security.
There are two pages in the console, Domain Page & Security Page.We can do the following In Domain Page:
o Create & manage application services like Integration Service and Repository Service
o Create and manage nodes, licenses and folders
o Restart and shutdown nodes
o View log events
o Other domain management tasks like applying licenses and managing grids and resources
We can do the following in Security Page:
o Create, edit and delete native users and groups
o Configure a connection to an LDAP directory service. Import users and groups from the LDAP directory service
o Create, edit and delete Roles (Roles are collections of privileges)
o Assign roles and privileges to users and groups
o Create, edit, and delete operating system profiles. An operating system profile is a level of security that the
Integration Services uses to run workflows
4. PowerCenter Client
Designer, Workflow Manager, Workflow Monitor, Repository Manager & Data Stencil are five client tools that are used
to design mappings, Mapplets, create sessions to load data and manage repository.
Mapping is an ETL code pictorially depicting logical data flow from source to target involving transformations of
the data. Designer is the tool to create mappings
Designer has five window panes, Source Analyzer, Warehouse Designer, Transformation Developer, Mapping Designer
and Mapplet Designer.
Source Analyzer:
Allows us to import Source table metadata from Relational databases, flat files, XML and COBOL files. We can only
import the source definition in the source Analyzer and not the source data itself is to be understood. Source Analyzer also allows us to define our own Source data definition.
Warehouse Designer:
Allows us to import target table definitions which could be Relational databases, flat files, XML and COBOL files.
We can also create target definitions manually and can group them into folders. There is an option to create the
tables physically in the database that we do not have in source analyzer. Warehouse designer doesn’t allow creating
two tables with same name even if the columns names under them vary or they are from different databases/schemas.
Transformation Developer:
Transformations like Filters, Lookups, Expressions etc that have scope to be re-used are developed in this pane. Alternatively Transformations developed in Mapping Designer can also be reused by checking the option‘re-use’ and by that it would be displayed under Transformation Developer folders.
Mapping Designer:
This is the place where we actually depict our ETL process; we bring in source definitions, target definitions, transformations like filter, lookup, aggregate and develop a logical ETL program. In this place it is only a logical program because the actual data load can be done only by creating a session and workflow.
Mapplet Designer:
We create a set of transformations to be used and re-used across mappings.
5. Repository Service
As we already discussed about metadata repository, now we discuss a separate,multi-threaded process that retrieves, inserts and updates metadata in the repository database tables, it is Repository Service. Repository service manages connections to the PowerCenter repository from PowerCenter client applications like Desinger, Workflow Manager, Monitor, Repository manager, console and integration service. Repository service is responsible for ensuring the consistency of metdata in the repository.
Creation & Properties:
Use the PowerCenter Administration Console Navigator window to create a Repository Service. The properties needed to create are,
Service Name – name of the service like rep_SalesPerformanceDev
Location – Domain and folder where the service is created
License – license service name
Node, Primary Node & Backup Nodes – Node on which the service process runs
CodePage – The Repository Service uses the character set encoded in the repository code page when writing data to the repository
Database type & details – Type of database, username, pwd, connect string and tablespacename
The above properties are sufficient to create a repository service, however we can take a look at following features which are important for better performance and maintenance.
General Properties
> OperatingMode: Values are Normal and Exclusive. Use Exclusive mode to perform administrative tasks like enabling version control or promoting local to global repository
> EnableVersionControl: Creates a versioned repository
Node Assignments: “High availability option” is licensed feature which allows us to choose Primary & Backup nodes for continuous running of the repository service. Under normal licenses would see only only Node to select from
Database Properties
> DatabaseArrayOperationSize: Number of rows to fetch each time an array database operation is issued, such as insert or fetch. Default is 100
> DatabasePoolSize:Maximum number of connections to the repository database that the Repository Service can establish. If the Repository Service tries to establish more connections than specified for DatabasePoolSize, it times out the connection attempt after the number of seconds specified for DatabaseConnectionTimeout
Advanced Properties
> CommentsRequiredFor Checkin: Requires users to add comments when checking in repository objects.
> Error Severity Level: Level of error messages written to the Repository Service log. Specify one of the following message levels: Fatal, Error, Warning, Info, Trace & Debug
> EnableRepAgentCaching:Enables repository agent caching. Repository agent caching provides optimal performance of the repository when you run workflows. When you enable repository agent caching, the Repository Service process caches metadata requested by the Integration Service. Default is Yes.
> RACacheCapacity:Number of objects that the cache can contain when repository agent caching is enabled. You can increase the number of objects if there is available memory on the machine running the Repository Service process. The value must be between 100 and 10,000,000,000. Default is 10,000
> AllowWritesWithRACaching: Allows you to modify metadata in the repository when repository agent caching is enabled. When you allow writes, the Repository Service process flushes the cache each time you save metadata through the PowerCenter Client tools. You might want to disable writes to improve performance in a production environment where the Integration Service makes all changes to repository metadata. Default is Yes.
Environment Variables
The database client code page on a node is usually controlled by an environment variable. For example, Oracle uses NLS_LANG, and IBM DB2 uses DB2CODEPAGE. All Integration Services and Repository Services that run on this node use the same environment variable. You can configure a Repository Service process to use a different value for the database client code page environment variable than the value set for the node.
You might want to configure the code page environment variable for a Repository Service process when the Repository Service process requires a different database client code page than the Integration Service process running on the same node.
For example, the Integration Service reads from and writes to databases using the UTF-8 code page. The Integration Service requires that the code page environment variable be set to UTF-8. However, you have a Shift-JIS repository that requires that the code page environment variable be set to Shift-JIS. Set the environment variable on the node to UTF-8. Then add the environment variable to the Repository Service process properties and set the value to Shift-JIS.
6. Integration Service (IS)
The key functions of IS are
§ Interpretation of the workflow and mapping metadata from the repository.
§ Execution of the instructions in the metadata
§ Manages the data from source system to target system within the memory and disk
The main three components of Integration Service which enable data movement are,
§ Integration Service Process
§ Load Balancer
§ Data Transformation Manager
6.1 Integration Service Process (ISP)
The Integration Service starts one or more Integration Service processes to run and monitor workflows. When we run a workflow, the ISP starts and locks the workflow, runs the workflow tasks, and starts the process to run sessions. The functions of the Integration Service Process are,
§ Locks and reads the workflow
§ Manages workflow scheduling, ie, maintains session dependency
§ Reads the workflow parameter file
§ Creates the workflow log
§ Runs workflow tasks and evaluates the conditional links
§ Starts the DTM process to run the session
§ Writes historical run information to the repository
§ Sends post-session emails
6.2 Load Balancer
The Load Balancer dispatches tasks to achieve optimal performance. It dispatches tasks to a single node or across the nodes in a grid after performing a sequence of steps. Before understanding these steps we have to know about Resources, Resource Provision Thresholds, Dispatch mode and Service levels
§ Resources – we can configure the Integration Service to check the resources available on each node and match them with the resources required to run the task. For example, if a session uses an SAP source, the Load Balancer dispatches the session only to nodes where the SAP client is installed
§ Three Resource Provision Thresholds, The maximum number of runnable threads waiting for CPU resources on the node called Maximum CPU Run Queue Length. The maximum percentage of virtual memory allocated on the node relative to the total physical memory size called Maximum Memory %. The maximum number of running Session and Command tasks allowed for each Integration Service process running on the node called Maximum Processes
§ Three Dispatch mode’s – Round-Robin: The Load Balancer dispatches tasks to available nodes in a round-robin fashion after checking the “Maximum Process” threshold. Metric-based: Checks all the three resource provision thresholds and dispatches tasks in round robin fashion. Adaptive: Checks all the three resource provision thresholds and also ranks nodes according to current CPU availability
§ Service Levels establishes priority among tasks that are waiting to be dispatched, the three components of service levels are Name, Dispatch Priority and Maximum dispatch wait time. “Maximum dispatch wait time” is the amount of time a task can wait in queue and this ensures no task waits forever
A .Dispatching Tasks on a node
1. The Load Balancer checks different resource provision thresholds on the node depending on the Dispatch mode set. If dispatching the task causes any threshold to be exceeded, the Load Balancer places the task in the dispatch queue, and it dispatches the task later
2. The Load Balancer dispatches all tasks to the node that runs the master Integration Service process
B. Dispatching Tasks on a grid,
1. The Load Balancer verifies which nodes are currently running and enabled
2. The Load Balancer identifies nodes that have the PowerCenter resources required by the tasks in the workflow
3. The Load Balancer verifies that the resource provision thresholds on each candidate node are not exceeded. If dispatching the task causes a threshold to be exceeded, the Load Balancer places the task in the dispatch queue, and it dispatches the task later
4. The Load Balancer selects a node based on the dispatch mode
6.3 Data Transformation Manager (DTM) Process
When the workflow reaches a session, the Integration Service Process starts the DTM process. The DTM is the process associated with the session task. The DTM process performs the following tasks:
§ Retrieves and validates session information from the repository.
§ Validates source and target code pages.
§ Verifies connection object permissions.
§ Performs pushdown optimization when the session is configured for pushdown optimization.
§ Adds partitions to the session when the session is configured for dynamic partitioning.
§ Expands the service process variables, session parameters, and mapping variables and parameters.
§ Creates the session log.
§ Runs pre-session shell commands, stored procedures, and SQL.
§ Sends a request to start worker DTM processes on other nodes when the session is configured to run on a grid.
§ Creates and runs mapping, reader, writer, and transformation threads to extract, transform, and load data
§ Runs post-session stored procedures, SQL, and shell commands and sends post-session email
§ After the session is complete, reports execution result to ISP
Pictorial Representation of Workflow execution:
1. A PowerCenter Client request IS to start workflow
2. IS starts ISP
3. ISP consults LB to select node
4. ISP starts DTM in node selected by LB

Learn sql online with different database http://sqlzoo.net/
Learn java,c#,c++,Perl,php,vb online and comparatively. http://progzoo.net/

2009-11-04T05:10:00.000-08:00

Informatica Architecture -

Informatica 7.x provides following components
PowerCenter Repository
PowerCenter Repository Server
PowerCenter Client
PowerCenter Server
Informatica 8.x provides following components

Domain
Nodes
Repository Service
Integration Service

In Informatica 7.x Informatica server,load manager LM, data transfer manager DTM ,reader,temp server and writer are the components of informatica server. First load manager sends a request to the reader thread if the reader thread is ready to read the data from source and dump into the temp server and data transfer manager manages the load and it send the request to writer thread as per first in first out process and writer thread takes the data from temp server and loads it itno the target.

The differences between 7.x and 8.x is on architecture and the administration of the product. PowerCenter 8.x is having Service Oriented Architecture (SOA).The SOA architecture has replaced several servers that were used within the PowerCenter 7.x . A Tomcat web server is used to run an Informatica 'domain' comprising of several repositories or integration services. An integration service is responsible for data movement.SOA allows a single console tool to be used to administer the PowerCenter installer. This tool is web based and can be accessed from anywhere in your network. SOA supports several integration or repository services to run concurrently. This allows high availability to be implemented (if a server hosting an integration service fails these tasks can be 'failed over' onto another node in the domain).Additionally PowerCenter 8.x supports the use of Java in mappings, the ability to 'push down' processing into the database and other similar improvements. There is nosignificant change in the client interface.Additional Information: Several terms have acquired a new meaning with the change in product architecture.PowerCenter 7.x PowerCenter 8.x R epository server and repository R epository service PowerCenter Server Integration service Some other enchancements like push down processing, more flexible java transformations, SQL transformations, string parsing functions are newly added to 8.x. Only change we can see in it is a single place where we do all administration stuff. Repository server is renamed as repository service and informatica pc server renamed as integration service. Not much change in client interface as we still have Repository Manager,Designer,Workflow Manager and Workflow Monitor.Also on 8.5 and 8.6, security, user and group administration has been moved to the administration console.When comparing to the architecture 7.1 n 8.1,Domain,Node,Integration Services added to the 8.1 architectutre.Domain is a web based admin console.Node is like INF server or power centre server.Integration Servicesis like repository server.Domain,Node,Integration Services are at one place that is Domain Database .In 8.1 we don't have admin console and repository agent also you can install or have multiple Nodes in a single machine,we can intiate multiple processes simultaneously.In INF 8.1 has more security you can not create repository without previliges.you no need to start the services in control panel every time also some of the transformations added in 8.1.They are SQL,JAVA transformations.It supports unstructured data like emails,word,pdfs.concept of flatfile updation is introduced.Effective in PowerCenter 8.0, you might notice an increase in memory and CPU resource usage on machines running PowerCenter Services. The following factors can increase memory and CPU resource usage:Updates to Integration Service and Repository Service processes. The pmserver and pmdtm process for the Integration Service and the pmrepagent process for the Repository Service include updated functionality that requires more memory and CPU resource usage.High availability. You configure a domain for high availability, service restart, failover, and recovery operations.Java Runtime Environment (JRE). The PowerCenter service-oriented architecture uses JRE to run the PowerCenter domains and nodes. Domains and nodes require more memory and CPU resources than when you run a PowerCenter Server or Repository Server in PowerCenter 7.x.Log Manager. The Log Manager requires additional memory and CPU resources when it collects and processes a large number of log events.