Docstoc

Informatica - DOC

Document Sample
Informatica - DOC Powered By Docstoc
					                                        INFORMATICS 7.1.1

1. What is a Data Warehouse?
        A Data Warehouse is a collection of data marts representing historical data from different
operational data source (OLTP). The data from these OLTP are structured and optimized for querying
and data analysis in a Data Warehouse.

2. What is a Data mart?
        A Data Mart is a subset of a data warehouse that can provide data for reporting and analysis on a
section, unit or a department like Sales Dept, HR Dept, etc. The Data Mart is sometimes also called as
HPQS (Higher Performance Query Structure).

3. What is OLAP?
         OLAP stands for Online Analytical Processing. It uses database tables (Fact and Dimension
tables) to enable multidimensional viewing, analysis and querying of large amount of data.

4. What is OLTP?
       OLTP stands for Online Transaction Processing Except data warehouse databases the other
databases are OLTPs. These OLTP uses normalized schema structure. These OLTP databases are
designed for recording the daily operations and transactions of a business.

5. What are Dimensions?
        Dimensions are categories by which summarized data can be viewed. For example a profit Fact
table can be viewed by a time dimension.

6. What are Confirmed Dimensions?
       The Dimensions which are reusable and fixed in nature Example customer, time, geography
dimensions.

7. What are Fact Tables?
         A Fact Table is a table that contains summarized numerical (facts) and historical data. This Fact
Table has a foreign key-primary key relation with a dimension table. The Fact Table maintains the
                rd
information in 3 normal form.
         A star schema is defined as a logical database design in which there will be a centrally located
fact table which is surrounded by at least one or more dimension tables. This design is best suited for
Data Warehouse or Data Mart.

8. What are the types of Facts?
      The types of Facts are as follows.
      1. Additive Facts: A Fact which can be summed up for any of the dimension available in the fact
           table.
      2. Semi-Additive Facts: A Fact which can be summed up to a few dimensions and not for all
           dimensions available in the fact table.
      3. Non-Additive Fact: A Fact which cannot be summed up for any of the dimensions available in
           the fact table.

9. What are the types of Fact Tables?
       The types of Fact Tables are:
               1. Cumulative Fact Table: This type of fact tables generally describes what was
                   happened over the period of time. They contain additive facts.
               2. Snapshot Fact Table: This type of fact table deals with the particular period of time.
                   They contain non-additive and semi-additive facts.

10. What is Grain of Fact?
         The Grain of Fact is defined as the level at which the fact information is stored in a fact table. This
is also called as Fact Granularity or Fact Event Level.
11. What is Fact less Fact table?
         The Fact Table which does not contain facts is called as Fact Table. Generally when we need to
combine two data marts, then one data mart will have a fact less fact table and other one with common
fact table.
12. What are Measures?
       Measures are numeric data based on columns in a fact table.

13. What are Cubes?
       Cubes are data processing units composed of fact tables and dimensions from the data
warehouse. They provided multidimensional analysis.

14. What are Virtual Cubes?
        These are combination of one or more real cubes and require no disk space to store them. They
store only definition and not the data.

15. What is a Star schema design?
         A Star schema is defined as a logical database design in which there will be a centrally located
fact table which is surrounded by at least one or more dimension tables. This design is best suited for
Data Warehouse or Data Mart.

16. What is Snow Flake schema Design?
        In a Snow Flake design the dimension table (de-normalized table) will be further divided into one
or more dimensions (normalized tables) to organize the information in a better structural format. To
design snow flake we should first design star schema design.

17. What is Operational Data Store [ODS]?
       It is a collection of integrated databases designed to support operational monitoring. Unlike the
OLTP databases, the data in the ODS are integrated, subject oriented and enterprise wide data.

18. What is Denormalization?
       Denormalization means a table with multi duplicate key. The dimension table follows
Denormalization method with the technique of surrogate key.

19. What is Surrogate Key?
       A Surrogate Key is a sequence generated key which is assigned to be a primary key in the
system (table).

20. What are the client components of Informatica 7.1.1?
       Informatica 7.1.1 Client Components:
               1. Informatica Designer
               2. Informatica Work Flow Manager
               3. Informatica Work Flow Monitor
               4. Informatica Repository Manager
               5. Informatica Repository Server Administration Console.

21. What are the server components of Informatica 7.1.1?
       Informatica 7.1.1 Server Components:
               1. Informatica Server
               2. Informatica Repository Server.


22. What is Metadata?
       Data about data is called as Metadata. The Metadata contains the definition of a data.

23. What is a Repository?
         Repository is a centrally stored container which stores the metadata, which is used by the
Informatica Power center server and Power Center client tools. The Informatica stores Repository in
relational database format.

        Informatica 8.1.1 Repository has 247 database objects
        Informatica 7.1.1 Repository has 247 database objects
        Informatica 6.1.1 Repository has 172 database objects
        Informatica 5.1.1 Repository has 145 database objects
        Informatica 4.1.1 Repository has 111 database objects
24. What is Data Acquisition Process?
         The process of extracting the data from different source (operational databases) systems,
integrating the data and transforming the data into a homogenous format and loading into the target
warehouse database. Simple called as ETL (Extraction, Transformation and Loading). The Data
Acquisition process designs are called in different manners by different ETL vendors.
         Informatica ----> Mapping
         Data Stage ----> Job
         Abinitio    ----> Graph

25. What is the GUI based ETL tools?
       The following are the GUI based ETL tools:
       1. Informatica
       2. DataStage
       3. Data Junction
       4. Oracle Warehouse Builder
       5. Abinitio
       6. Business Object Data Integrator
       7. Cogon’s Decision Stream.

26. What are programmatic based ETL tools?
       1. Pl/Sql
       2. SAS BASE
       3. SAS ACCESS
       4. Tera Data Utilities
               a. BTEQ
               b. Fast Load
               C. Multi Load
               d. Fast Export
               e. T (Trickle) Pump

27. What is a Transformation?
         A transformation is a repository object that generates, modifies, or passes data. Transformations
in a mapping represent the operations the PowerCenter Server performs on the data. Data passes into
and out of transformations through ports that you link in a mapping or mapplet. Transformations can be
active or passive. An active transformation can change the number of rows that pass through it. A passive
transformation does not change the number of rows that pass through it.

28. The following are details description of Transformations available in Informatica.
Transformation          Type                      Description

Aggregator               Active / Connected          Performs aggregate calculations

Application    Source    Active / Connected          Represents the rows that the Power
Qualifier                                            Center Server reads from an application,
                                                     such as an ERP source, when it runs a
                                                     session.
Custom                   Active or     Passive   /   Calls a procedure in a shared library or
                         Connected                   DLL.
Expression               Passive / Connected         Calculates a value

External Procedure       Active / Connected or       Calls a procedure in a shared library or
                         Unconnected                 in the COM layer of windows.
Filter                   Active / Connected          Filters data

Input                    Passive / Connected         Defines mapplet input rows. Available in
                                                     the Mapplet Designer
Joiner                    Active / Connected         Joins data from different databases of
                                                     flat file systems.
Lookup                    Passive / Connected or     Looks up values
                          Unconnected
Normalizer                Active / Connected         Source qualifier for COBOL sources.
                                                     Can also use in the pipeline to normalize
                                                     data from relational or flat file sources?
Output                    Passive / Connected        Defines mapplet output rows. Available
                                                     in the Mapplet Designer.
Rank                      Active / Connected         Limits records to a top or bottom range.

Router                    Active / Connected         Router data into multiple transformations
                                                     based on group conditions.
Sequence Generator        Passive / Connected        Generates primary keys.

Sorter                    Active / Connected         Sorts data base4d on a sort key.

Source Qualifier          Active / Connected         Represents        the     rows    that    the
                                                     PowerCenter Server reads from a
                                                     relational or flat file source when it runs a
                                                     session.
Stored Procedure          Passive / Connected or     Calls a stored procedure.
                          Unconnected
Transaction Control       Active / Connected         Defines      commit        and      rollback
                                                     transactions.
Union                     Active / Connected         Merges data from different databases or
                                                     flat file systems.
Update Strategy           Active / Connected         Determines whether to insert, delete,
                                                     update, or reject rows.
XML Generator             Active / Connected         Reads data from one or more input ports
                                                     and outputs XML through a single output
                                                     port.
XML Parser                Active / Connected         Reads XML from one input port and
                                                     outputs data to one or more output ports.
XML Source Qualifier      Active / Connected         Represents     the    rows     that the
                                                     PowerCenter Server reads from an XML
                                                     source when it runs a session.

29. What are features of Informatica Repository Server?
       Features of Informatica Repository Server.

          1. Informatica client application and Informatica server access the repository database tables
             through the Repository Server.
         2. Informatica client connects to the repository server through the host name/ IP address and its
             port number.
         3. The Repository Server can manage multiple repository on different machines on the network.
         4. For each repository database registered with the Repository Server it configures and
             manages a Repository Agent process.
         5. The Repository Agent is a multi-threaded process that performs the action needed to retrieve,
             insert and updated metadata in the repository database tables.

30. How many types of Repositories are there?
       There are three types of Repositories:
              1. Standalone Repository
              2. Global Repository
                3. Local Repository

31. What are the types of metadata stored in Repository?
       The following types of metadata are stored in Repository:
                1. Database connections
                2. Global objects
                3. Mappings
                4. Mapplets
                5. Multi-dimensional metadata
                6. Reusable Transformations
                7. Sessions and Batches
                8. Shortcuts
                9. Source Definitions
                10. Target definitions
                11. Transformations

32. What are the types of locks in Repository?
       There are two types of Locks in Repository:
               1. Read Lock
               2. Write Lock
               3. Execute Lock
               4. Fetch Lock
               5. Save Lock

33. What are Repository objects which we can export?
       We can export the following Repository objects:
               1. Sources
               2. Targets
               3. Transformations
               4. Mapplets
               5. Mappings
               6. Sessions

34. What is a Work Flow?
       A Work Flow is a set of instructions on how to execute tasks such as sessions, emails and shell
commands. A WorkFlow is created from Workflow Manager.

35. What are actions which can be performed by pmcmd command?
       We can perform the following actions with pmcmd:
               1. Check whether the Informatica server is running
               2. Start and stop sessions and batches
               3. Recover sessions.
               4. Stop the Informatica server.
pmcmd returns zero on success and non zero on failure

36. What is commit interval?
        A commit interval is the interval at which the Informatica Server commits data to relational targets
during a session.
37. What is the use of Stored Procedure Transformation?
        We use the Stored Procedure Transformation for populating and maintaining the database.

38. What is the use of partitioning the sessions?
        The partitioning of session increases the session performance by reducing the time period of
reading the source data and loading the data into the target.

39. What is the use of Lookup Transformation?
       The Lookup Transformation is useful for:
           1. Getting a related value form a table using a key column value
           2. Update slowly changing dimension table
           3. To check whether records already exists in the table.

40. What is polling?
        It displays the update information about the session in the monitor window. The monitor window
displays the status of each session when you poll the Informatica Server.

41. What is a Parameter File?
        The parameter File is used to define the values of the parameters and variables used in a
session. It is a file created in a notepad and saved with .prm extension.

42. What is Metadata Reporter?
       It is a web based application that enables you to run reports against the repository metadata.
With a metadata reporter you can access information about your repository without having knowledge of
SQL.

43. What is meant by Lookup Cache?
        The Informatica server built a cache in memory when it process the first row of a data in a cached
lookup transformation.

44. What is a Load Manager?
       The Load Manager is a primary Informatica Server process. It performs the following tasks:
     Manages sessions and batch scheduling
     Locks the session and read the session properties
     Read the parameter file
     Expand the server and session variables and parameters.
     Verify permissions and privileges

45. What are the tasks performed by Sequence Generator Transformation?
    1. Create keys
    2. Replace missing values
    3. Cycle through a sequential range of numbers.

46. What is the end value of the Sequence Generator?
       The end value of the Sequence Generator is 2147483647.

47. What are variables supplied by the Transaction Control Transformation?
    1. TC_COMMIT_BEFORE
    2. TC_COMMIT_AFTER
    3. TC_ ROLLBACK_BEFORE
    4. TC_ROLLBACK_AFTER
    5. TC_CONTINUE_TRANSACTION [Default]

48. How to implement Update Strategy?
        To implement Update Strategy Transformation the source and target table should have primary
keys to compare the records and to find out the latest changes happened.

49. What are constants of Update Strategy Transformation?
    The constants of Update Strategy Transformation are:

    1.   DD_INSERT      -       0
    2.   DD_UPDATE      -       1
    3.   DD_DELETE      -       2
    4.   DD_REJECT      -       3
                                DD Stands For Data Driven


50 What are the benefits of Star Schema Design?
    Fewer tables
    Designed for analysis across time
    Simplify joins
    Less database space
    Supports drilling on reports

51 What is Data Scrubbing?
        The Data Scrubbing is the process of cleaning up the junk in the legacy data and makes it
accurate and useful. Simply, making good data out of bad data.

52. What are Bad Rows (Rejected Rows)?
        The Informatica Server will dump the bad or rejected rows which are sent out by the
transformation into a text file with tablename.bad extension.

53. The Normalizer Transformation is mainly used to extract and format the COBOL files.

54. We can apply “Distinct” clause only in Source Qualifier and Sorter Transformations.

55. What are types of Dimensional Modeling?
    1. Conceptual Modeling
    2. Physical Modeling
    3. Logical Modeling

56. What is forward engineering?
        Using the Erwin tool the data modeler will convert the .SQL script (logical structure of tables) into
a physical structure tables at the database level, this is called as Forward Engineering.

57. What is common use of creating a Factless Fact Table?
       The most common use of creating a Factless fact table is to capture date transaction events.

58. What are the different sources of Source systems of Data Warehouse?
    1. RDBMS
    2. Flat Files
    3. XML Files
    4. SAP R/3
    5. PeopleSoft
    6. SAP BW
    7. Web Methods
    8. Web Services
    9. Seibel
    10. Cobol Files
    11. Legacy Systems.

59. You cannot use XML source qualifier in a mapplet and Joiner and Normalizer Transformations.

60. What are the Session Partitions types?
    1. Round-robin
    2. Hash keys
    3. Key range
    4. Pass-through
    5. Database partitioning


61. You cannot use Incremental Aggregation when the mapping includes an aggregator
transformation.

62. While importing source definition the metadata that will be imported are:
    1. Source Name
    2. Database Location
    3. Column Names
    4. Data Types
    5. Key Constraints

63. We can stop the Batch by two ways:
    1. Server Manager
    2. By pmcmd command

64. What is the Batch and types of Batches?
    Grouping of sessions is known as Batch. There are two types of batches:
    1. Sequential
    2. Concurrent

65. What is a tracing level and types of Tracing level?
    Tracing level represents the amount of information that Informatica server writes in a log file. Types of
Tracing levels are:

    1.   Normal
    2.   Verbose
    3.   Verbose lnit
    4.   Verbose Data

66. What is the default join that source qualifier provides?
       Inner Join

67. Types of Slowly Changing Dimensions:
    1. Type – 1 (Recent updates)
    2. Type – 11 (Full historical information)
    3. Type – 111 (Partial historical information)

68. What are Update Strategy’s target table options?
    1. Update as Update: Updates each row flagged for update if it exists in the table.
    2. Update as Insert: Inserts a new row for each update.
    3. Update else insert: Updates if row exists, else inserts.

69. What is a Data in a database this include the source of tables, the meaning of the keys and the
relationship between the tables.

70. In Conceptual Modeling and Logical modeling the tables are called as entities.

71. What does a Mapping document contains?
    The Mapping document contains the following information:
    1. Source Definition – from where the database has to be loaded
    2. Target Definition – to where the database has to be loaded
    3. Business Logic – what logic has to be implemented in staging area?

72. What does the Top down Approach says?
        The Top down Approach is coined by Bill Immon. According to his approach he says “First we
need to implement the Enterprise data warehouse by extracting the data from individual departments and
from the Enterprise data warehouse develop subject oriented databases called as “Data Marts”.

73. What does the Bottom up Approach or Ralph Kimball Approach says?
       The Bottom down Approach is coined by Ralph Kimball. According to his approach he says “First
we need to develop subject oriented database called as “Data Marts” then integrate all the Data Marts to
develop the Enterprise data warehouse.

74. Who is the first person in the organization to start the Data Warehouse project?
       The first person to start the Data Warehouse project in an organization is Business Analyst.

75. What is a Dimension Modeling?
        A Dimensional Modeling is a high level methodology used to implement the start schema
structure which is done by the Data Modeler.

76. What are the types of OLAPs?
    1. DOLAP: The OLAP tool which words with desktop databases is called as DOLAP. Example:
       Cognos EP 7 Series and Business Objects, Micro strategy.
    2. ROLAP: The OLAP which works with Relational databases are called as ROLAP. Example:
       Business Object, Micro strategy, Cognos Report Net and BRIO.
    3. MOLAP: The OLAP which is responsible for creating multidimensional structures called cubes are
       called as MOLAP. Example: Cognos Report Net.
    4. HOLAP: The OLAP which uses the combined features of ROLAP and MOLAP are called as
       HOLAP. Example Cognos Report Net.

77. What is the extension of Repository backup?
       The extension of the Repository backup is .rep

78. Which join is not supported by Joiner Transformation?
       The non-equi joins are not supported by joiner Transformation.

79. What is SQL Override?
       Applying the joining condition in the source qualifier is called as sql override.

80. What is Rank Index?
       When you create a Rank Transformation by default “Rank Index” port will be created, to store the
number of ranks specified.

81. What is Sort Key?
       The column on which the sorting takes place in the Sorter Transformation is called as “Sort Key”
Column.

82. What is default group in Router Transformation?
       In the Router Transformation the rejected rows are captured by default group and the data will be
passed to target table.

83. What is unconnected Transformation?
        The transformation which does not involve in mapping data flow is called as Unconnected
Transformation.

84. What is Connected Transformation?
         The Transformation which involve in mapping data flow is called as connected transformation. By
default all the transformations are connected transformation.

85. Which Transformation is responsible to maintain updates in warehouse database?
       Update Strategy Transformation.

86. What are the caches contained by the Look up Transformation?
       1. Static Lookup cache
       2. Dynamic Lookup Cache
       3. Persistent Lookup Cache
       4. Data cache
       5. Index cache

87. What are the Direct and Indirect methods in the Flat file extraction?
         In the direct method the extract the flat file by using its own meta data. In indirect method we
extract all the flat files by using one flat file’s meta data.

88. What is the Maplet?
        Mapplet is type of meta data object which contains set of reusable transformation logic which can
be reused in multiple mapping. A maplet contains one maplet input Transformation and one maplet output
Transformation.

89. What is the basic difference between reusable transformation and mapplet?
         Maplets are set of reusable transformation logic and reusable transformations are created by
single transformation logic.

90. What is Target Load Planer?
        The Target Load plan is the order in which we should load the target to implement the Data
Acquisition Process.

91. What is Constraint Based Load ordering?
        The Constraint Based Load ordering specified the loading of the dimensions tables based on the
constraints designed in the dimension table. The Constraint Based Load order is used for implementing
snow-flake schema data loading.

92. How may Loading criteria?
    There are three types of loading criteria.
    1. Parallel loading
    2. Sequential
    3. Control flow loading

93. What is File Watch Event?
          The Event Wait activity of a session has event called as File Watch which will watch wether the
file is copied or not.

94. What is worklet?
       The worklet is a group of sessions. To execute the worklet we have to create the workflow.

95. Why we use stored procedure transformation?
       For populating and maintaining databases.

96. Why we use partitioning the session in Informatica?
        Partitioning achieves the session performance by reducing the time period of reading the source
and loading the data into target.

97. Why we use lookup transformation?
       Look up Transformations can access data from relational tables that are not sources in mapping.
With Lookup transformation, we can accomplish the following tasks.

98. Which transformation should we use to normalize the COBOL and relational sources?
       When you drag the COBOL source into the Designer workspace, the normalized transformation
automatically appears, creating input and output ports for every column in the source.

99. Which tool you use to create and manage sessions and batches and to monitor and stop the
Informatica server?
       Informatica server manager.

100. What are the types of data that passes between Informatica server and stored procedure?
    There are three types of data
    1. Input/output parameter
    2. Return Values
    3. Status code

101. What are the groups available in Router Transformation?
    1. User defined group
    2. Default group


102. What are join types in Joiner Transformation?
    The joins supplied by the Joiner Transformation are:
    1. Normal Join
    2. Master Outer Join
    3. Detail Outer Join
    4. Full Outer Join

103. What are the designer tools available for creation of Transformations?
    1. Mapping Designer
    2. Transformation Developer
    3. Mapplet Designer

104. What are the basic needs to join two sources in Source Qualifier?
       The two source tables should have a primary key – foreign key relationship and the two source
       tables should have matching data types.
105. What is a Status code?
       Status code provides error handling facility during the session execution.

106. What is Data Driven?
       The Data Driven is the instruction which is fed to Informatica Server whether to
       insert/delete/update when using Update Strategy Transformation.

107. What are the tasks to be done to partition a session?
     Configure the session to partition the source data
     Install the Informatica on a machine with multiple CPU

108. In which circumstances the Informatica creates a reject file (bad file)?
        When it encounters the DD_REJECT in Update strategy Transformation
        Violets database constraints file in the rows was truncated or overflowed.

109. In a sequential batch can you run the session if previous session fails?
        Yes, by setting the option always runs the session.

110. How many ways your create ports?
    Two ways:
    1. Drag the port from another transformation
    2. Click the add button on the ports tab.

111. How can you stop the batch?
       By using server manager or pmcmd.
112. How can you improve session performance in aggregator transformation?
       Use sorted input.

113. Can you use the mapping parameters or variables created in one mapping into any other
reusable transformation?
       Yes, because reusable transformation is not contained with any mapplet or mapping.

114. Can you use the mapping parameters or variables created in one mapping into another
mapping?
       No.
       We can use mapping parameters or variables in any transformation of the same mapping or
mapplet in which you have created mapping parameters or variables.



115. Can you start a session inside a batch individually?
       We can start our required session only in case of sequential batch in case of concurrent batch.
We can do like this.

116. Can you start batches with in a batch?
       You cannot. If you want to start batch that resides in a batch, create a new independent batch
and copy the necessary sessions into the new batch.

117. Can you generate reports in Informatica?
       Yes. By using Metadata reporter we can generate reports in Informatica.

118. Can you copy the batches?
       No.

119. After dragging the ports of there sources (sql server, oracle, Informix) to a single source
qualifier, can you map these three ports directly to target?
        No, unless and until you join those three ports I source qualifier you cannot map them directly.

120. What are Target Types on the Server?
       Target Types are File, Relational and ERP.
121. What is the aggregate transformation?
       Aggregate transformation allows you to perform aggregate calculations, such as averages and
sums.

122. What are Target Options on the Servers?
       Target Options for File Target type are FTP File, Loader and MQ There are no target options for
ERP target type.

123. How do you identify existing rows of data in the target table using lookup transformation?
       Can identify existing rows of data using Unconnected transformation.

124. What is Code Page used for?
       Code page is used to identify characters that might be I different languages. If your are importing
Japanese data into mapping, you must select the Japanese code page of source data.

125. What is a source qualifier?
       It represents all data queried from the source.

126. Where should you place the flat file to import the flat file definition to the designer?
       Place it in Local folder.

127. What are the settings that you use to configure the joiner transformation?
    1. Master and detail source
    2. Type of join
    3. Condition of the join

128. What are the session parameters?
       Session parameters are like mapping parameters; represent values you might want to change
between Sessions such as database connections or source files.

129. What are the methods for creating reusable transformations?
    There are two methods:
    1. Design it in the transformation developer.
    2. Promote a standard transformation from mapping designer. After you ass a transformation to the
       mapping, you can promote it to the status of reusable transformation.

130. What are the joiner caches?
        When a Joiner transformation occurs in a session, the Informatica Server reads all the records
from the master source and builds index and data caches bases on the master rows. After building the
caches, the Joiner transformation reads records from the detail source and performs joins.

131. What are different options uses to configure the sequential batches?
    There are two options:
    1. Run the session only if previous session completes successfully.
    2. Always runs the session.

132. What are the data movement modes in Informatica?
    Data movement modes determine how Informatica server handles the character data. You choose
the data movement in the Informatica server configuration settings two types of data movement modes
available in Informatica.
    1. ASCII mode
    2. Uni code mode

133. What is difference between stored procedure transformation and external procedure
transformation?
        Inner equi join.

134. What is difference between stored procedures will be compiled and external procedure
transformation?
         In case of stored procedure transformation procedure will be compiled and executed in a
relational data source. You needs data base connection to import the stored procedure in to yours
mapping. Where as in external procedure transformation procedure or function will be executed out side
of data source. That is you need to make it as a DLL to access in your mapping. No need to have data
base connection in case of external procedure transformation.

135. To achieve the session partition what are the necessary tasks you have to do?
    1. Configure the session to partition source data.
    2. Install the Informatica server on a machine with multiple CPU’S

136. Performance tuning in Informatica?
        The goal of performance tuning is optimize session performance so sessions run during the
available load window for the Informatica server.

137. When can session fail?
    The session fails when,
     Server cannot allocate enough system resources
     Session exceeds the maximum no. of sessions
     Server cannot obtain an execute lock for the session
     Server encounters database errors
     Network related errors

138. How many ways you can update a relational source definition?
    There are ways you can update a relational source definition:
    1. Edit the definition
    2. reimport the definition


139. How many ways you can create a Reusable Transformation?
    There are two ways to create a Reusable Transformation
    1. By designing it in the transformation developer
    2. By promoting the already existing Transformation to reusable from its properties.

140. What is an aggregator cache in aggregate transformation?
       The aggregator transformation stores the data in the aggregator cache until it completes
aggregate calculations.

141. What is data cache and Index cache?
        When you use aggregator transformation in your mapping then Informatica server creates Data
and Index cache in memory to process the transformation.

142. What is a Mapping Variable?
        A Mapping Variable represents a value that can change throughout the session. The Informatica
server saves the value of mapping variable in repository at the end of the session and uses it for the next
session run.

143. In which scenario does the Update Strategy Transformation is best suited?
          Within a session: When you configure a session, you can instruct the Informatica server to either
treat all records in same way (treat all as insert/treat all as update/treat all as update) or use instructions
coded into the session to flag records for different database operations.

         Within a Mapping: Within a mapping, you use the update strategy transformation to flag records
for insert, update or reject.

144. What are the types of mappings in Getting Started Wizard?
    1. Simple pass through mapping: loads a slowly growing fact or dimension table be inserting new
       rows. Use this map to loading new data into it.
    2. Slowly growing target: Loads a slowly growing fact or dimension table be inserting new rows. Use
       this map to load new data without disturbing the existing data.

145. How can you recognize whether or not the data is added in the table in Type – II dimension?
    1. By version number
    2. By flag value
    3. By effective date range
146. Why you use Repository connectivity?
        When you edit or schedule the session each time, Informatica server directly communicates the
repository to check whether or not the session and users are valid.

147. What are the data movement modes in Informatica?
    The data movement mode determines how Informatica server handles the character data. There are
two types of data movement modes:
    1. ASCII mode
    2. Uni code mode

148. Can you copy the session to a different folder or Repository?
          Yes. By using the copy session wizard you can copy a session in a different folder or Repository.
But first you should copy the mapping of that session before you copy session.

149. What is the difference between partitioning of relational target and partitioning of file target?
        If you partition a session with a relational target Informatica server creates multiple connections to
the target database to write target data concurrently. If you partition a session with file target the
Informatica server create one target file for each partition.

150. What are the Transformations that restrict the partition of sessions?
    1. Advanced External Transformation
    2. External Procedure Transformation
    3. Aggregator Transformation
    4. Joiner Transformation
    5. Normalizer Transformation
    6. XML Targets

151. What is a Power Center Repository?
        The Power Center Repository allows you to share metadata across repositories to create a data
mart domain. In a data mart domain, you can create a single global repository to store metadata used
across an enterprise and a number of local repositories to share the global metadata as needed.


                                      Informatica



1. While importing the relational source definition from database, what are the metadata of source U
import?

   Source name
   Database location
   Column names
   Data types
   Key constraints

2. How many ways U can update a relational source definition and what r they?
    Two ways
      1. Edit the definition
      2. Re import the definition

3. Where should U place the flat file to import the flat file definition to the designer?

   Place it in local folder

4. To provide support for Mainframes source data, which files r used as a source definitions?
    COBOL files
5. Which transformation should u need while using the COBOL sources as source definitions?
Normalizer transformation which is used to normalize the data. Since COBOL sources r often consists of
Denormailzed data.

6. How can U create or import flat file definition in to the warehouse designer?

  U can not create or import flat file definition in to warehouse designer directly. Instead U must analyze
the file in source analyzer, and then drag it into the warehouse designer. When u drags the flat file source
definition into warehouse designer workspace, the warehouse designer creates a relational target
definition not a file definition. If u want to load to a file, configure the session to write to a flat file. When
the informatica server runs the session, it creates and loads the flat file.

    7. What is the mapplet?
       Mapplet is a set of transformations that you build in the maplet designer and U can use in
       multiple mappings.

8. What is a transformation? It is a repository object that generates, modifies or passes data.

9. What r the designer tools for creating transformations?
  Mapping designer
  Transformation developer
  Mapplet designer

10. What r the active and passive transformations?
     An active transformation can change the number of rows that pass through it. A passive
transformation does not change the number of rows that pass through it.

11. What r the connected or unconnected transformations?
   An unconnected transforamtion is not connected to other transformations in the mapping.Connected
transforamation is connected to other transformations in the mapping.

12. How many ways u create ports?
     Two ways
      1. Drag the port from another transforamtion
      2. Click the add button on the ports tab.

14. What r the reusable transformations?
     Reusable transformations can be used in multiple mappings. When u need to incorporate this
transformation into mapping add an instance of it to maping.Later if U change the definition of the
transformation, all instances of it inherit the changes. Since the instance of reusable transforamation is a
pointer to that transforamtion,U can change the transforamation in the transformation developer, its
instances automatically reflect these changes. This feature can save U great deal of work.

15. What r the methods for creating reusable transformations?
      Two methods
      1. Design it in the transformation developer.
      2. Promote a standard transformation from the mapping designer.After U add a transformation to the
mapping, U can promote it to the status of reusable transformation.
    Once U promotes a standard transformation to reusable status can demote it to a standard
transformation at any time.
   If u change the properties of a reusable transformation in mapping, U can revert it to the original
reusable transformation properties by clicking the revert button.

16. What r the unsupported repository objects for a mapplet?
    COBOL source definition
    Joiner transformations
    Normalizer transformations
   Non reusable sequence generator transformations.
   Pre or post session stored procedures
   Target defintions
   Power mart 3.5 style Look Up functions
   XML source definitions
   IBM MQ source defintions

17. What r the mapping paramaters and maping variables?
    Maping parameter represents a constant value that U can define before running a session. A mapping
parameter retains the same value throughout the entire session.
   When u use the maping parameter, U declare and use the parameter in a maping or maplet.Then define
the value of parameter in a parameter file for the session.
  Unlike a mapping parameter, a mapping variable represents a value that can change throughout the
session.The informatica server saves the value of maping variable to the repository at the end of session
run and uses that value next time U run the session.

18. Can U use the maping parameters or variables created in one maping into another maping? NO.
We can use mapping parameters or variables in any transformation of the same maping or mapplet in
which U have created maping parameters or variables.
19. Can u use the mapping parameters or variables created in one maping into any other reusable
transformation?
   Yes.Because reusable tranformation is not contained with any maplet or maping.

20. How can U improve session performance in aggregator transformation?
 A. Use sorted input.

21. What is aggregate cache in aggregator transforamtion?
    The aggregator stores data in the aggregate cache until it completes aggregate calculations.When u
run a session that uses an aggregator transformation, the informatica server creates index and data caches
in memory to process the transformation.If the informatica server requires more space,it stores overflow
values in cache files.

22. What r the difference between joiner transformation and source qualifier transformation?
    U can join hetrogenious data sources in joiner transformation which we can not achieve in source
qualifier transformation.
    U need matching keys to join two relational sources in source qualifier transformation.Where as u
doesn't need matching keys to join two sources.
   Two relational sources should come from same datasource in sourcequalifier.U can join relatinal
sources which r coming from diffrent sources also.

23. In which conditions we cannot use joiner transformation (Limitations of joiner transformation)?

Both pipelines begin with the same original data source.
Both input pipelines originate from the same Source Qualifier transformation.
Both input pipelines originate from the same Normalizer transformation.
Both input pipelines originate from the same Joiner transformation.
Either input pipelines contains an Update Strategy transformation.
Either input pipelines contains a connected or unconnected Sequence Generator transformation.

24. What r the settings that u use to configure the joiner transformation?
       Master and detail source
       Type of join
       Condition of the join

25. What r the join types in joiner transformation?
            Normal (Default)
            Master outer
            Detail outer
            Full outer
26. What r the joiner caches?
   When a Joiner transformation occurs in a session, the Informatica Server reads all the records from the
master source and builds index and data caches based on the master rows.
After building the caches, the Joiner transformations reads records from the detail source and perform
joins.

27. What is the look up transformation?
    Use lookup transformation in u'r mapping to lookup data in a relational table, view, synonym.
    Informatica server queries the look up table based on the lookup ports in the transformation. It
compares the lookup transformation port values to lookup table column values based on the look up
condition.
28.Why use the lookup transformation ?
    To perform the following tasks.
Get a related value. For example, if your source table includes employee ID, but you want to include the
employee name in your target table to make your summary data easier to read.
Perform a calculation. Many normalized tables include values used in a calculation, such as gross sales
per invoice or sales tax, but not the calculated value (such as net sales).
Update slowly changing dimension tables. You can use a Lookup transformation to determine whether
records already exist in the target.

29.What r the types of lookup?         Connected and unconnected

30. Differences between connected and unconnected lookup?

      Connected lookup                                   Unconnected lookup

  Receives input values diectly from               Receives input values from the result of a
  the pipe line.                                 lkp expression in a another transformation.

 U can use a dynamic or static cache             U can use a static cache.

  Cache includes all lookup columns             Cache includes all lookup out put ports in the
 used in the mapping                            lookup condition and the lookup/return port.

 Support user defined default values            Does not support user defiend default values

31.what is meant by lookup caches?

   The informatica server builds a cache in memory when it processes the first row af a data in a cached
look up transformation.It allocates memory for the cache based on the amount u configure in the
transformation or session properties.The informatica server stores condition values in the index cache and
output values in the data cache.

32. What r the types of lookup caches?

   Persistent cache: U can save the lookup cache files and reuse them the next time the informatica server
processes a lookup transformation configured to use the cache.

Recache from database: If the persistent cache is not synchronized with he lookup table,U can configure
the lookup transformation to rebuild the lookup cache.

Static cache: U can configure a static or read-only cache for only lookup table. By default informatica
server creates a static cache.It caches the lookup table and lookup values in the cache for each row that
comes into the transformation.when the lookup condition is true,the informatica server does not update
the cache while it prosesses the lookup transformation.

Dynamic cache: If u want to cache the target table and insert new rows into cache and the target,u can
create a look up transformation to use dynamic cache.The informatica server dynamically inerts data to
the target table.

shared cache: U can share the lookup cache between multiple transactions.U can share unnamed cache
between transformations inthe same maping.

33. Difference between static cache and dynamic cache

  Static cache                                           Dynamic cache

U can not inert or update the cache.             U can insert rows into the cache as u pass
                                              to the target
The informatic server returns a value from        The informatic server inserts rows into cache
the lookup table opr cache when the condition      when the condition is false.This indicates that
is true.When the condition is not true,the         the row is not in the cache or target table.
informatica server returns the default value      U can pass these rows to the target table.
 for connected transformations and null for
unconnected transformations.


34. Which transformation should we use to normalize the COBOL and relational sources?
   Normalizer Transformation.
    When U drag the COBOL source in to the mapping Designer workspace,the normalizer
transformation automatically appears,creating input and output ports for every column in the source.

35.How the informatica server sorts the string values in Ranktransformation?
  When the informatica server runs in the ASCII data movement mode it sorts session data using Binary sortorder.I
configure the seeion to use a binary sort order,the informatica server caluculates the binary value of each string and returns
specified number of rows with the higest binary values for the string.

36.What r the rank caches?

   During the session ,the informatica server compares an inout row with rows in the datacache.If the input row out-rank
stored row,the informatica server replaces the stored row with the input row.The informatica server stores group informa
in an index cache and row data in a data cache.

37.What is the Rankindex in Ranktransformation?

   The Designer automatically creates a RANKINDEX port for each Rank transformation. The
Informatica Server uses the Rank Index port to store the ranking position for each record in a group. For
example, if you create a Rank transformation that ranks the top 5 salespersons for each quarter, the rank
index numbers the salespeople from 1 to 5:

38.What is the Router transformation?

    A Router transformation is similar to a Filter transformation because both transformations allow you
to use a condition to test data. However, a Filter transformation tests data for one condition and drops the
rows of data that do not meet the condition. A Router transformation tests data for one or more conditions
and gives you the option to route rows of data that do not meet any of the conditions to a default output
group.
 If you need to test the same input data based on multiple conditions, use a Router Transformation in a
mapping instead of creating multiple Filter transformations to perform the same task.
39.What r the types of groups in Router transformation? Input group       Output group

  The designer copies property information from the input ports of the input group to create a set of output ports for e
output group.
  Two types of output groups
   User defined groups
   Default group
   U can not modify or delete default groups.

40.Why we use stored procedure transformation?        For populating and maintaining data bases.

42.What r the types of data that passes between informatica server and stored procedure?
    3 types of data
             Input/Out put parameters
             Return Values
             Status code.
43.What is the status code?

       Status code provides error handling for the informatica server during the session.The stored procedure issues a sta
code that notifies whether or not stored procedure completed sucessfully.This value can not seen by the user.It only used
the informatica server to determine whether to continue running the session or stop.

44. What is source qualifier transformation?

   When U add a relational or a flat file source definition to a maping,U need to connect it to
  a source qualifer transformation.The source qualifier transformation represnets the records
 that the informatica server reads when it runs a session.

45.What r the tasks that source qualifier performs?

   Join data originating from same source data base.
   Filter records when the informatica server reads source data.
   Specify an outer join rather than the default inner join
   specify sorted records.
   Select only distinct values from the source.
   Creating custom query to issue a special SELECT statement for the informatica server to read
    source data.

46. What is the target load order?

   U specify the target loadorder based on source qualifiers in a maping.If u have the multiple
   source qualifiers connected to the multiple targets,U can designatethe order in which informatica
   server loads data into the targets.

47.What is the default join that source qualifier provides?

   Inner equi join.

48. What r the basic needs to join two sources in a source qualifier?

    Two sources should have primary and Foreign key relation ships.
    Two sources should have matching data types.

49.what is update strategy transformation ?
     This transformation is used to maintain the history data or just most recent changes in to target
  table.

50.Describe two levels in which update strategy transformation sets?

  Within a session. When you configure a session, you can instruct the Informatica Server to either treat
all records in the same way (for example, treat all records as inserts), or use instructions coded into the
session mapping to flag records for different database operations.

Within a mapping. Within a mapping, you use the Update Strategy transformation to flag records for
insert, delete, update, or reject.

51.What is the default source option for update stratgey transformation?

     Data driven.

52. What is Datadriven?

       The informatica server follows instructions coded into update strategy transformations with
     in the session maping determine how to flag records for insert,update,,delete or reject
     If u do not choose data driven option setting,the informatica server ignores all update strategy
    transformations in the mapping.

53.What r the options in the target session of update strategy transsformatioin?

   Insert
   Delete
   Update
   Update as update
   Update as insert
   Update esle insert
   Truncate table

54. What r the types of maping wizards that r to be provided in Informatica?

    The Designer provides two mapping wizards to help you create mappings quickly and easily. Both
wizards are designed to create mappings for loading and maintaining star schemas, a series of dimensions
related to a central fact table.

 Getting Started Wizard. Creates mappings to load static fact and dimension tables, as well as slowly
growing dimension tables.
Slowly Changing Dimensions Wizard. Creates mappings to load slowly changing dimension tables based
on the amount of historical dimension data you want to keep and the method you choose to handle
historical dimension data.

55. What r the types of maping in Getting Started Wizard?

   Simple Pass through maping :

     Loads a static fact or dimension table by inserting all rows. Use this mapping when you want to drop
all existing data from your table before loading new data.

 Slowly Growing target :

  Loads a slowly growing fact or dimension table by inserting new rows. Use this           mapping to load
new data when existing data does not require updates.
56. What r the mapings that we use for slowly changing dimension table?

    Type1: Rows containing changes to existing dimensions are updated in the target by overwriting the
existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data.
  Use the Type 1 Dimension mapping to update a slowly changing dimension table when you do not need
to keep any previous versions of dimensions in the table.

 Type 2: The Type 2 Dimension Data mapping inserts both new and changed dimensions into the target.
Changes are tracked in the target table by versioning the primary key and creating a version number for
each dimension in the table.
 Use the Type 2 Dimension/Version Data mapping to update a slowly changing dimension table when
you want to keep a full history of dimension data in the table. Version numbers and versioned primary
keys track the order of changes to each dimension.

Type 3: The Type 3 Dimension mapping filters source rows based on user-defined comparisons and
inserts only those found to be new dimensions to the target. Rows containing changes to existing
dimensions are updated in the target. When updating an existing dimension, the Informatica Server saves
existing data in different columns of the same row and replaces the existing data with the updates

57.What r the different types of Type2 dimension maping?

     Type2 Dimension/Version Data Maping: In this maping the updated dimension in the
source will gets inserted in target along with a new version number.And newly added dimension
in source will inserted into target with a primary key.

 Type2 Dimension/Flag current Maping: This maping is also used for slowly changing dimensions.In
addition it creates a flag value for changed or new dimension.
  Flag indiactes the dimension is new or newlyupdated.Recent dimensions will gets saved with cuurent
flag value 1. And updated dimensions r saved with the value 0.

Type2 Dimension/Effective Date Range Maping: This is also one flavour of Type2 maping used for
slowly changing dimensions.This maping also inserts both new and changed dimensions in to the
target.And changes r tracked by the effective date range for each version of each dimension.

58.How can u recognise whether or not the newly added rows in the source r gets insert in the target ?

  In the Type2 maping we have three options to recognise the newly added rows
   Version number
   Flagvalue
   Effective date Range

59. What r two types of processes that informatica runs the session?

 Load manager Process: Starts the session, creates the DTM process, and sends post-session email when
the session completes.
 The DTM process. Creates threads to initialize the session, read, write, and transform data, and handle
pre- and post-session operations.

60. What r the new features of the server manager in the informatica 5.0?

      U can use command line arguments for a session or batch.This allows U to change the values of
session parameters,and mapping parameters and maping variables.

  Parallel data processig: This feature is available for powercenter only.If we use the informatica server
on a SMP system,U can use multiple CPU's to process a session concurently.
Process session data using threads: Informatica server runs the session in two processes.Explained in
previous question.

61. Can u generate reports in Informatcia?

   Yes. By using Metadata reporter we can generate reports in informatica.

62.What is metadata reporter?

     It is a web based application that enables you to run reports againist repository metadata.
  With a meta data reporter,u can access information about U'r repository with out having knowledge of
sql,transformation language or underlying tables in the repository.

63.Define maping and sessions?

    Maping: It is a set of source and target definitions linked by transformation objects that define the
rules for transformation.
 Session : It is a set of instructions that describe how and when to move data from source to targets.

64.Which tool U use to create and manage sessions and batches and to monitor and stop the informatica
server?

   Informatica server manager.

65.Why we use partitioning the session in informatica?

     Partitioning achieves the session performance by reducing the time period of reading the source and
loading the data into target.

66.To achieve the session partition what r the necessary tasks u have to do?

      Configure the session to partition source data.

      Install the informatica server on a machine with multiple CPU's.

67.How the informatica server increases the session performance through partitioning the source?

  For a relational sources informatica server creates multiple connections for each parttion of a single
source and extracts seperate range of data for each connection.Informatica server reads multiple partitions
of a single source concurently.Similarly for loading also informatica server creates multiple connections
to the target and loads partitions of data concurently.

  For XML and file sources,informatica server reads multiple files concurently.For loading the data
informatica server creates a seperate file for each partition(of a source file).U can choose to merge the
targets.

68. Why u use repository connectivity?

     When u edit,schedule the sesion each time,informatica server directly communicates the repository to
check whether or not the session and users r valid.All the metadata of sessions and mappings will be
stored in repository.

69.What r the tasks that Loadmanger process will do?

   Manages the session and batch scheduling: Whe u start the informatica server the load maneger
launches and queries the repository for a list of sessions configured to run on the informatica
server.When u configure the session the loadmanager maintains list of list of sessions and session start
times.When u sart a session loadmanger fetches the session information from the repository to perform
the validations and verifications prior to starting DTM process.

Locking and reading the session: When the informatica server starts a session lodamaager locks the
session from the repository.Locking prevents U starting the session again and again.

Reading the parameter file: If the session uses a parameter files,loadmanager reads the parameter file and
verifies that the session level parematers are declared in the file

Verifies permission and privelleges: When the sesson starts load manger checks whether or not the user
have privelleges to run the session.

Creating log files: Loadmanger creates logfile contains the status of session.

70. What is DTM process?

  After the loadmanger performs validations for session,it creates the DTM process.DTM is to create and
manage the threads that carry out the session tasks.I creates the master thread.Master thread creates and
manges all the other threads.

71. What r the different threads in DTM process?

  Master thread: Creates and manages all other threads

 Maping thread: One maping thread will be creates for each session.Fectchs session and maping
information.

Pre and post session threads: This will be created to perform pre and post session operations.

Reader thread: One thread will be created for each partition of a source.It reads data from source.

Writer thread: It will be created to load data to the target.

Transformation thread: It will be created to tranform data.

72.What r the data movement modes in informatcia?

    Datamovement modes determines how informatcia server handles the charector data.U choose the
datamovement in the informatica server configuration settings.Two types of datamovement modes
avialable in informatica.

  ASCII mode
  Uni code mode.

73. What r the out put files that the informatica server creates during the session running?

    Informatica server log: Informatica server(on unix) creates a log for all status and error
messages(default name: pm.server.log).It also creates an error log for error messages.These files will be
created in informatica home directory.

Session log file: Informatica server creates session log file for each session.It writes information about
session into log files such as initialization process,creation of sql commands for reader and writer
threads,errors encountered and load summary.The amount of detail in session log file depends on the
tracing level that u set.
Session detail file: This file contains load statistics for each targets in mapping.Session detail include
information such as table name,number of rows written or rejected.U can view this file by double clicking
on the session in monitor window

Performance detail file: This file contains information known as session performance details which helps
U where performance can be improved.To genarate this file select the performance detail option in the
session property sheet.

Reject file: This file contains the rows of data that the writer does notwrite to targets.

Control file: Informatica server creates control file and a target file when U run a session that uses the
external loader.The control file contains the information about the target flat file such as data format and
loading instructios for the external loader.

Post session email: Post session email allows U to automatically communicate information about a
session run to designated recipents.U can create two different messages.One if the session completed
sucessfully the other if the session fails.

Indicator file: If u use the flat file as a target,U can configure the informatica server to create indicator
file.For each target row,the indicator file contains a number to indicate whether the row was marked for
insert,update,delete or reject.

output file: If session writes to a target file,the informatica server creates the target file based on file
prpoerties entered in the session property sheet.

Cache files: When the informatica server creates memory cache it also creates cache files.For the
following circumstances informatica server creates index and datacache files.

 Aggreagtor transformation
 Joiner transformation
 Rank transformation
 Lookup transformation

74.In which circumstances that informatica server creates Reject files?

   When it encounters the DD_Reject in update strategy transformation.
   Violates database constraint
   Filed in the rows was truncated or overflowed.

75. What is polling?
It displays the updated information about the session in the monitor window. The monitor window
displays the status of each session when U poll the informatica server

76. Can u copy the session to a different folder or repository?

      Yes. By using copy session wizard u can copy a session in a different folder or repository.But that
target folder or repository should consists of mapping of that session.
If target folder or repository is not having the maping of copying session ,
u should have to copy that maping first before u copy the session
77. What is batch and describe about types of batches?

    Grouping of session is known as batch.Batches r two types
    Sequential: Runs sessions one after the other
    Concurrent: Runs session at same time.

     If u have sessions with source-target dependencies u have to go for sequential batch to start the
sessions one after another.If u have several independent sessions u can use concurrent batches.
Whch runs all the sessions at the same time.

78. Can u copy the batches?          NO

79.How many number of sessions that u can create in a batch? Any number of sessions.

80.When the informatica server marks that a batch is failed?

 If one of session is configured to "run if previous completes" and that previous session fails.

81. What is a command that used to run a batch? pmcmd is used to start a batch.

82. What r the different options used to configure the sequential batches?   Two options

     Run the session only if previous session completes sucessfully. Always runs the session.

83. In a sequential batch can u run the session if previous session fails?

  Yes.By setting the option always runs the session.

84. Can u start a batches with in a batch?
      U can not. If u want to start batch that resides in a batch,create a new independent batch and copy the necessary sessi
into the new batch.

85. Can u start a session inside a batch idividually?
    We can start our required session only in case of sequential batch.in case of concurrent batch
  we cant do like this.

86. How can u stop a batch?          By using server manager or pmcmd.

87. What r the session parameters?

   Session parameters r like maping parameters,represent values U might want to change between
   sessions such as database connections or source files.

    Server manager also allows U to create userdefined session parameters.Following r user defined
  session parameters.
             Database connections
             Source file names: use this parameter when u want to change the name or location of
                               session source file between session runs
            Target file name : Use this parameter when u want to change the name or location of
                                session target file between session runs.
             Reject file name : Use this parameter when u want to change the name or location of
                               session reject files between session runs.

88. What is parameter file?
    Parameter file is to define the values for parameters and variables used in a session.A parameter
  file is a file created by text editor such as word pad or notepad.
   U can define the following values in parameter file
      Maping parameters
      Maping variables
      session parameters

89. How can u access the remote source into U'r session?
   Relational source: To acess relational source which is situated in a remote place ,u need to
                     configure database connection to the datasource.

   FileSource : To access the remote source file U must configure the FTP connection to the
                  host machine before u create the session.

   Hetrogenous : When U'r maping contains more than one source type,the server manager creates
                 a hetrogenous session that displays source options for all types.


90. What is difference between partioning of relatonal target and partitioning of file targets?

   If u parttion a session with a relational target informatica server creates multiple connections
    to the target database to write target data concurently.If u partition a session with a file target
    the informatica server creates one target file for each partition.U can configure session properties
    to merge these target files.

91. what r the transformations that restricts the partitioning of sessions?

   Advanced External procedure tranformation and External procedure transformation: This
    transformation contains a check box on the properties tab to allow partitioning.

   Aggregator Transformation: If u use sorted ports u can not parttion the assosiated source

  Joiner Transformation : U can not partition the master source for a joiner transformation

 Normalizer Transformation

 XML targets.

92. Performance tuning in Informatica?

    The goal of performance tuning is optimize session performance so sessions run during the available
load window for the Informatica Server.Increase the session performance by following.

 The performance of the Informatica Server is related to network connections. Data generally moves
across a network at less than 1 MB per second, whereas a local disk moves data five to twenty times
faster. Thus network connections ofteny affect on session performance.So aviod netwrok connections.

   Flat files: If u'r flat files stored on a machine other than the informatca server, move those files to the
machine that consists of informatica server.
Relational datasources: Minimize the connections to sources ,targets and informatica server to
 improve session performance.Moving target database into server system may improve session
 performance.
Staging areas: If u use staging areas u force informatica server to perform multiple datapasses.
Removing of staging areas may improve session performance.

U can run the multiple informatica servers againist the same repository.Distibuting the session load to
multiple informatica servers may improve session performance.

Run the informatica server in ASCII datamovement mode improves the session performance.Because
ASCII datamovement mode stores a character value in one byte.Unicode mode takes 2 bytes to store a
character.
If a session joins multiple source tables in one Source Qualifier, optimizing the query may improve
performance. Also, single table select statements with an ORDER BY or GROUP BY clause may benefit
from optimization such as adding indexes.

We can improve the session performance by configuring the network packet size,which allows
data to cross the network at one time.To do this go to server manger ,choose server configure database
connections.

If u r target consists key constraints and indexes u slow the loading of data.To improve the session
performance in this case drop constraints and indexes before u run the session and rebuild them after
completion of session.

Running a parallel sessions by using concurrent batches will also reduce the time of loading the
data.So concurent batches may also increase the session performance.

Partittionig the session improves the session performance by creating multiple connections to sources and
targets and loads data in paralel pipe lines.

In some cases if a session contains a aggregator transformation ,u can use incremental aggregation to
improve session performance.

Aviod transformation errors to improve the session performance.

If the sessioin containd lookup transformation u can improve the session performance by enabling the
look up cache.

If U'r session contains filter transformation ,create that filter transformation nearer to the sources
or u can use filter condition in source qualifier.

Aggreagator,Rank and joiner transformation may oftenly decrease the session performance .Because they
must group data before processing it.To improve session performance in this case use sorted ports option.

92. What is difference between maplet and reusable transformation?

     Maplet consists of set of transformations that is reusable.A reusable transformation is a
  single transformation that can be reusable.

   If u create a variables or parameters in maplet that can not be used in another maping or maplet.Unlike
the variables that r created in a reusable transformation can be usefull in any other maping or maplet.

We can not include source definitions in reusable transformations.But we can add sources to a maplet.

Whole transformation logic will be hided in case of maplet.But it is transparent in case of reusable
transformation.

We cant use COBOL source qualifier,joiner,normalizer transformations in maplet.Where as we can make
them as a reusable transformations.

93. Define informatica repository?

     The Informatica repository is a relational database that stores information, or metadata, used by the
Informatica Server and Client tools. Metadata can include information such as mappings describing how
to transform source data, sessions indicating when you want the Informatica Server to perform the
transformations, and connect strings for sources and targets.
The repository also stores administrative information such as usernames and passwords, permissions and
privileges, and product version.

Use repository manager to create the repository.The Repository Manager connects to the repository
database and runs the code needed to create the repository tables.Thsea tables
stores metadata in specific format the informatica server,client tools use.

94. What r the types of metadata that stores in repository?

  Following r the types of metadata that stores in the repository

 Database connections
 Global objects
 Mappings
Mapplets
Multidimensional metadata
Reusable transformations
 Sessions and batches
 Short cuts
 Source definitions
Target defintions
Transformations

95. What is power center repository?

  The PowerCenter repository allows you to share metadata across repositories to create a data mart
domain. In a data mart domain, you can create a single global repository to store metadata used across an
enterprise, and a number of local repositories to share the global metadata as needed.

96. How can u work with remote database in informatica?did u work directly by using remote
    connections?

   To work with remote datasource u need to connect it with remote connections.But it is not
   preferable to work with that remote source directly by using remote connections .Instead u bring that
source into U r local machine where informatica server resides.If u work directly with remote source the
session performance will decreases by passing less amount of data across the network in a particular
time.

97. What r the new features in Informatica 5.0?

   U can Debug U'r maping in maping designer
   U can view the work space over the entire screen
   The designer displays a new icon for a invalid mapings in the navigator window
   U can use a dynamic lookup cache in a lokup transformation
   Create maping parameters or maping variables in a maping or maplet to make mapings more
   flexible
   U can export objects into repository and import objects from repository.when u export a repository
object,the designer or server manager creates an XML file to describe the repository metadata.
The designer allows u to use Router transformation to test data for multiple conditions.Router
transformation allows u route groups of data to transformation or target.
U can use XML data as a source or target.

Server Enahancements:
   U can use the command line program pmcmd to specify a parameter file to run sessions or
batches.This allows you to change the values of session parameters, and mapping parameters and
variables at runtime.

If you run the Informatica Server on a symmetric multi-processing system, you can use multiple CPUs to
process a session concurrently. You configure partitions in the session properties based on source
qualifiers. The Informatica Server reads, transforms, and writes partitions of data in parallel for a single
session. This is avialable for Power center only.

Informatica server creates two processes like loadmanager process,DTM process to run the sessions.

Metadata Reporter: It is a web based application which is used to run reports againist repository metadata.

U can copy the session across the folders and reposotories using the copy session wizard in the
informatica server manager

With new email variables, you can configure post-session email to include information, such as the
mapping used during the session

98. what is incremantal aggregation?

   When using incremental aggregation, you apply captured changes in the source to aggregate
calculations in a session. If the source changes only incrementally and you can capture changes, you can
configure the session to process only those changes. This allows the Informatica Server to update your
target incrementally, rather than forcing it to process the entire source and recalculate the same
calculations each time you run the session.

99. What r the scheduling options to run a sesion?

  U can shedule a session to run at a given time or intervel,or u can manually run the session.

   Different options of scheduling

    Run only on demand: server runs the session only when user starts session
explicitly
    Run once: Informatica server runs the session only once at a specified date and time.
    Run every: Informatica server runs the session at regular intervels as u configured.
    Customized repeat: Informatica server runs the session at the dats and times secified in the
repeat dialog box.

100 .What is tracing level and what r the types of tracing level?

     Tracing level represents the amount of information that informatcia server writes in a log file.
   Types of tracing level
      Normal
      Verbose
      Verbose init
      Verbose data

101. What is difference between stored procedure transformation and external procedure transformation?

     In case of storedprocedure transformation procedure will be compiled and executed in a relational
data source.U need data base connection to import the stored procedure in to u'r maping.Where as in
external procedure transformation procedure or function will be executed out side of data source.Ie u need
to make it as a DLL to access in u r maping.No need to have data base connection in case of external
procedure transformation.
102. Explain about Recovering sessions?

     If you stop a session or if an error causes a session to stop, refer to the session and error logs to
determine the cause of failure. Correct the errors, and then complete the session. The method you use to
complete the session depends on the properties of the mapping, session, and Informatica Server
configuration.
Use one of the following methods to complete the session:
         Run the session again if the Informatica Server has not issued a commit.
         Truncate the target tables and run the session again if the session is not recoverable.
         Consider performing recovery if the Informatica Server has issued at least one commit.

103. If a session fails after loading of 10,000 records in to the target.How can u load the records from
10001 th record when u run the session next time?

  As explained above informatcia server has 3 methods to recovering the sessions.Use performing
recovery to load the records from where the session fails.

104. Explain about perform recovery?

    When the Informatica Server starts a recovery session, it reads the OPB_SRVR_RECOVERY table
and notes the row ID of the last row committed to the target database. The Informatica Server then reads
all sources again and starts processing from the next row ID. For example, if the Informatica Server
commits 10,000 rows before the session fails, when you run recovery, the Informatica Server bypasses the
rows up to 10,000 and starts loading with row 10,001.
     By default, Perform Recovery is disabled in the Informatica Server setup. You must enable Recovery
in the Informatica Server setup before you run a session so the Informatica Server can create and/or write
entries in the OPB_SRVR_RECOVERY table.

105. How to recover the standalone session?

    A standalone session is a session that is not nested in a batch. If a standalone session fails, you can run
recovery using a menu command or pmcmd. These options are not available for batched sessions.

  To recover sessions using the menu:
   1. In the Server Manager, highlight the session you want to recover.
   2. Select Server Requests-Stop from the menu.
   3. With the failed session highlighted, select Server Requests-Start Session in Recovery Mode from
        the menu.

 To recover sessions using pmcmd:
   1.From the command line, stop the session.
   2. From the command line, start recovery.

106. How can u recover the session in sequential batches?

   If you configure a session in a sequential batch to stop on failure, you can run recovery starting with
the failed session. The Informatica Server completes the session and then runs the rest of the batch. Use
the Perform Recovery session property

To recover sessions in sequential batches configured to stop on failure:

    1.In the Server Manager, open the session property sheet.
    2.On the Log Files tab, select Perform Recovery, and click OK.
    3.Run the session.
    4.After the batch completes, open the session property sheet.
    5.Clear Perform Recovery, and click OK.

If you do not clear Perform Recovery, the next time you run the session, the Informatica Server attempts
to recover the previous session.
If you do not configure a session in a sequential batch to stop on failure, and the remaining sessions in the
batch complete, recover the failed session as a standalone session.

107. How to recover sessions in concurrent batches?

If multiple sessions in a concurrent batch fail, you might want to truncate all targets and run the batch
again. However, if a session in a concurrent batch fails and the rest of the sessions complete successfully,
you can recover the session as a standalone session.
To recover a session in a concurrent batch:
    1.Copy the failed session using Operations-Copy Session.
    2.Drag the copied session outside the batch to be a standalone session.
    3.Follow the steps to recover a standalone session.
    4.Delete the standalone copy.

108. How can u complete unrcoverable sessions?

   Under certain circumstances, when a session does not complete, you need to truncate the target tables
and run the session from the beginning. Run the session from the beginning when the Informatica Server
cannot run recovery or when running recovery might result in inconsistent data.

109. What r the circumstances that infromatica server results an unreciverable session?

     The source qualifier transformation does not use sorted ports.
     If u change the partition information after the initial session fails.
    Perform recovery is disabled in the informatica server configuration.
    If the sources or targets changes after initial session fails.
    If the maping consists of sequence generator or normalizer transformation.
    If a concuurent batche contains multiple failed sessions.

110. If i done any modifications for my table in back end does it reflect in informatca warehouse or
maping desginer or source analyzer?

  NO. Informatica is not at all concern with back end data base.It displays u all the information
  that is to be stored in repository.If want to reflect back end changes to informatica screens,
 again u have to import from back end to informatica by valid connection.And u have to replace the
existing files with imported files.

111. After draging the ports of three sources(sql server,oracle,informix) to a single source qualifier, can u
map these three ports directly to target?

NO. Unless and until u join those three ports in source qualifier u cannot map them directly.
DATA WAREHOUSE CYCLE PROCESS



 Data Warehosue Developmen Process : Requirements + Design + implementation

                                    End-User Requirements


                                    Software Requirement
                                         Specification
                              Analyse the End-User
                          Requirements and Identify Data
                                     marts


                            Select Resources (Hardware,
                                     Software)


                               Design Data Modeling


                           Data Warehouse Architecture


                              Implement ETL Process


                                    Deployment


                           Unit Testing /System Testing /
                                         UAT


                             Production & Maintenance




PROJECT FLOW




--procedure to load the fact table
In informatica, I know that we have to do mappings from the dimension table to
fact table by doing the lookup transformation. This is for the foreign keys. What abt
the additive facts? Some of my additive facts are not in the dimension tables and r
in the source flat files only. How do i load them in the fact table
You can create a lookup on flat files too and then do the calcuation.

--I hav a sales analysis fact table and have customers, winning bid, time & item as
the dimension tabes.I have winning price, percentage charge , start price, amount
as the additive facts in the fact table so I wanted to know how to map these
additive facts into the fact table. So as u said i will perfom lookups on the flat files..
do i need to do aggregate transformation also? Please elaborate the steps of lookup
& aggregator.. Thanks

facttable source is ur source flatfile/relational

A facttable always has key s of all the surrounded dimnension , measure values.
In general we design dimension table /fact table>
step1: Source(flat file) > stage (relation)
Step2:Stage to Warehouse (dimension/fact)
It mean that even though wen u design ur fact table -- its source is ur flatfile or
stage(relational table) .Dnt think tat we hve to extract data from dimension table.
Source (flatfile/facttable) --- transformation---- facttable
Lookup on master table (dimension table) --- y we have to do this ?
wen ever a value (dimension key) loads into fact table .The key loaded in fact must
present in Dimension table if not such values must loaded into exception table.And
it can be reused in the process.


    1.IN AGGREGATOR TRANSFORMATION I HAVE NOT SELECT THE GROUP BY PORT THEN
    WHAT WILL BE THE OUTPUT AND WHY?
    If u are not selected group by option in aggregator, the result is depending on the aggregator
    functions. If u are not using any aggregator functions u will get last record of source data.

    2. WHAT IS THE DIFFERENCE BETWEEN FILTER AND LOOKUP?
    The filter transformation filters the data based on the filter condition.The filter transformation
    won’t pass any default values where in lookup the lookup condition if false the informatica will
    pass default values.

    3. WHICH SCENERIO WE USE LOOKUP AND JOIN?
    lookup is advanced for join transformation. lookup is more flexible than join

    4. WHAT IS JUNK DIMENSION AND DEGENERATED DIMENSION?

    5. HOW MANY TYPE OF FACT ARE THERE?
    ADDITIVE , SEMI ADDITIVE, NON ADDITIVE

    6. WHAT IS PROC_ID AND WHERE IT IS USED?
    PROC_ID IS A SYSTEM VARIABLE, THIS IS WE ARE USING IN UN-CONNECTED STORED
    PROCEDURE

    7. HOW INDEX WORK?

    8. I HAVE THE FOLLOWING TABLE?

    NAME COMPANY SALARY YEAR
    HELAL WIPRO 10000 1993
    HELAL IBM 15000 1995
    HELAL IFLEX 20000 1998
    HELAL HP 30000 2000

    I WANT THE OUTPUT AS:

    NAME COMPANY HIGHEST_SALARY
    HELAL WIPRO,IBM,IFLEX,HP 30000

    FOR THIS LOGIC U CAN GO FOR STORED PROCEDURE

--but for 8th question
u can try with union all function in oracle from sql*plus itself
no real need for pl/sql
--For 8th question,

Of course you can do it using plsql,
if you want this to do in SQL then U can do it using Analytical functions using row_number, over
paritition functions.

--Answer to the 8 th Question

Use sorter, expression, and aggregator

- In the sorter transformation sort by name so that all records of person are sequential.
- In the expression use two mapping variables. one variable to hold the employee name of the
previous record and the other one for concatinating the company name.
The company name variable will be initialised to null and prev employee name will be dfaulted to some
arbitrary value.
- For every record that comes to the expression, If the employee name in the previous variable is
same as the employee name in the current record concatinate the the company name comming from
source and company name variable in an output port and pass it to the aggregator. If the name of the
employ changes, then make the comapany name variable to null and concatinate with the input
company name.

- In the aggregator transformation group by empname and max(sal)

9.i have a problem in informatica...I have requirement,there is a workflow in which there
are a numerous sessions in series....the requirement is that the workflow should not run
more than 2 hours
Can we set this condition in the workflow???

--U write a shell/windows script for it or u can schedule a batchfile/scriptfile to stop/abort the
workflow.
--suppose ur workflow is scheduled to run at 1:00 PM then schedule the script(which has -------
PMCMD command to stop the workflow) at 3:00PM.
if the workflow is done by 3:00PM that's fine else the second script will stop the workflow at 3:00PM


10.how to use the ftp connection in informatica............
i tried that but its giving invalid session.
that err will be
Value must be provided for (Remote Filename) which belongs to (Connection).
******** Session ftp_conn is INVALID ********

--Make sure the ftp connection properties are set properly....it should specify the corect server
information, port number and the remote server should allow ftp connections on the specified port....

11.what is the best approach to aggregate a 50 million row table?

some tips....go for session level partitioning....use other features like sorted input to improve
performance...make sure the DTM Buffer calculation is set to Auto and override the default DTM Buffer
percentages...



12.validating header and tailer in a flat file
--i have a flatfile source with a header and tailer now i need to pass the data from a file if
that have a valid header and tailer other that file should be rejected
for example
source flat file is having header as ABC and trailer XYZ then it should process if that file has
header and trailer other than that it should be reject.
could any body pls help me over it
--Just read each record of flat file as one single field. Check first and last records against header or
trailer format. If succeeds then allow file to be created else use abort function within the expression to
fail the mapping
13.TEXT synerio
i have 1 text file and naming convention is filename_25/11/2008. Text file is changing
everyday, so tommorrow name will be like filename_26/11/2008.what u will write in
workflow source for source path.
--try parameterising the filename as filename_$$date
--Keep this parameter in some other file $$date=25/11/2008
  and update this file using a script which loads the current date in this file

In ur case U have two options
1. U can pass the file name as parameter to the workflow and run using the PMCMD command.
2. U can use the Indirect option, i.e. U list the file name(U can have more than one source file) in a
listfile and mention this listfile name in the session->Mapping tab->source properties and
change the source file type option to indirect.

next U can generate the listfile using some shell script.


14.
Issue – 1:
I have 4 million records in my source as a flat file . i want to load the records to target.
Target has four table .
In the mapping , first record should go to firat table , second record will go to second table,
3rd record goes to 3rd table,
4th record goes to 4th table , Again 5th record goes to first table and then Continue the
same process . How can u do this ?

Issue -2
I have to get the 10-12 million records from client in every day. There is no relationship (No
primary keys). But I have to load the datas
target . How can u do this ? if anyone know this process, give the solutions.

--Issue-1:
Use Router transformation....n put 4 conditions....
Nextval % 2=0.5 or Nextval % 4=1
Nextval % 4=2
Nextval % 2=1.5 or Nextval % 4=3
Nextval % 4=4

--Issue-:2
Use SQ Tarns n send all d data to target




15.can i change a mapping into a mapplet
--Yes you can create Mapplet from the mapping, only thing is you have to ommit some elements while
copying like
Mapplet, Target transformation etc..
--Do not give the answers as you like...why that question arises as mapping to mapplet.... Haven't
you see the Mapplet desigenr which is to be used ....u thinking for mapping to mapplet

In the filter transformation , in the condition is -1. what vl the output? Please share your
ideas!!!!
other than 0, everything is TRUE.....so all records will pass



16.Denormalize tables in Informatica?
I know how to denormalize table using Aggregator transformation.
I am wondering, is there any other method we can denormalize without using the Agg Tran?

like using Decode function in Expr Tran??
--Yes you are correct, we have to aggregate the values in the expression.
--The logic is simple but the implementation is difficult using Expression transformation.

17.In update strategy target table or flat file which gives more performance ? why?
--Update strategy is used to flag rows for table only. We cant use it with flat files.


I need to implement an inline code for Audit purposes.

This inline code will be called in every Informatica mapping that any developer creates.This code will
have to be in a mapplet.

The target table will be different for each mapping. The traditional way to get the target count is to to
do a lookup on target table. However,

since I have to get the target count in the mapplet, I need an alternative way, to get the target count.
i.e. the number of records loaded to target.

Would anyone know how I can get this done?
may i know which version of informatica u r using.
In 8.5 and above version we have $PMinstanceName@numAffectedRows built-in paramter . so here
instance name will be your target name for eg $SupplierTgt@numAffectedRows so this parameter will
return you how many rows are loaded to target.


18.workflow variables in mapping
Does anyone knwo how we cand dump workflow variables into a parameter file so a
maaping can pick it up?
--The workflow variables I need are
1. SrcSuccessRows
2. TgtSuccessRows
3.TgtFailedRows
of one session. This has to be used in the mapping of the following session in workflow, within
mapping..
The blog speaks beautifully about parameter files.

--But my concern is different. I need to get access to workflow variables above, within a mapping so I
can use it for audit purposes.

i.e. Say flow is Start-SesA-SesAudit

Then in the mapping of SesAudit, I need the workflow variables of SesA. So I was wondering after
SesA is there some way I could dump the variables of SesA into a parameter file?

19.Fatal error in Informatica during sorting
-workflow is failing (in mapping 2-during start of input to sorting transformation)with
following error message, please advise if you faced this kind of issue and whats the solution
2 implement.
FATAL node01_ETLProd *********** FATAL ERROR : Unexpected Condition in file
[\ZeusbuilderProduction\view\powrmart\server\cmnutils\svecmatrix.cpp] line [256].
Contact Informatica Technical Support for assistance.                    Aborting this DTM
process due to an unexpected condition. ***********

--Surprisingly, code is working fine in dev & QA but not in prod. This may be environment issue.
--This could be because of memory issue, try changing Data movement mode of Integration service to
Ascii mode if it is UNICODE or UNICODE if it is in ASCII,
other wise try some variation in Sorter Cache Size/DTM buffer size.

20.1. Why dont we use normalizer, cobol, xml in mapplet
Why dont we use normalizer, cobol, xml source in the mapplet, some body asked me about
this…,
i said as per ETL Specification or already specified in power centre that we can not use it
inside mapplet.
2. If i want to perform scd I or SCD II operation without using Lookup. is this possible to do
this. how can i do this..
3. suppose i am doing scd I, in this new rows get insert in the target and old value get
update, but i want to delete that updated row from target and when ever new row comes
then if it is new then it insert in the target,
if it is not new means already exist in the target. it get deleted or overwritten by new row
or any thing. but i dont want that existing row in the target. how can i do this.
like deptno dname
10 delhi
20 indore

if 10, delhi comes again, it get deleted from the target.
--2. u can use joiner(Left Outer join) transformation.
--3. 1.take lookup tr...compare with record....
      if is it new ...then goes to target by update transformation(Insert)

--3.2. after that if is it old record ......then u can use update transformation(Delete option).

21.modify a mapping if the target table name is chged
I have a mapping and every thing is working fne with it.
Now for some reason I had to change the Target Table Name, now what is the best
approach to make changes to the existing mapping.

Is there any other method to change without re-importing the target table definitions?
--As I know the only way is rename the existing table to the name u r going to import. then import
the table from db, that will replace all occurrence of the target with the newly imported... you do this
for large changes in the target, for minor changes do it directly in the Target designer.
This is what the method I know... I will also wanted to know if another way of doing this with out
importing it again.
This approach will work fine only if there are no changes in table structure and only name has
changed. We will have to reimport the definition if any changes in structure happens.

22.SQL opreations on source file
I am getting the source file from 3rd party.
I want to apply all SQL opreations on this source file say IN,NOT IN ,< ,>,
=,!=,BETWEEN,OR etc. some high level conditions....
I do'nt have the permission to load this source file to relational Database.So that I can
load this file to database then extract the data using these SQL Operations.

Is there anyway to create some temp table and execute the sql or any idea ?
--Yes, use filter transformation. Also, you can do some calculations in an expression transformation if
needed.

23.Scenario..rows into columns..
Source:
PROD_ID Prod_desc
1 LCD monitor
1 computer peripherals
1 bdhsdfkj
2 LG Television
2 home entertainment
2 ghhgfjhj
.
.
.
.
target:
PROD_ID desc1 desc2 desc3 desc4 .........
1 LCD monitor computer peripherals ... ...
--if we have same products with different descriptions(assume 200 descs) in source table
we need to populate those descriptions in related products row(single row for each product)

Here U R trying to denormalize the table, right??
--U can achieve using the aggregator transformation.
by grouping accordingly.
--U have to create ports for no. of desc U want then U need to filter the rows using First function in
informatica.
--I think I have posted how to denormalize the table in one of informatica forums, U can search for it.

24.How to connect to MS Access
we have a requirement to load MS Access tables, to Teradata.
Infa is hosted on UNIX.
I have created the mapping by importing the source definition. but what should be provided
as a connection string in session properties, to run the workflow.

--Stated below is the approache,we thought of,
convert the .mdb file to excel through Perl scripts. As the .mdb tables will be as sheets (assumption).
and then get this excel as source and run the mappings.
not sure whether this is a correct approch.
Could any one brief me on the process to be followed, if the above stated approach makes some
sense. :-)
--I have heard that we cannot connect to Acess source, thru UNIX server.
we have to have Powerchannel for this purpose??

--The issue we cannot access .mdb using UNIX server.
The best approach is to write a script which would generate a flat file out of the .mdb file. You can
schedule that script to automate.then use the flat file as a source.

25.What is the logic will you implement to load data into a fact table from n dimension
tables?
26.Explain a mapping design to start aggregating the values before getting all the rows
from the source.
27.If you use sorted I/P option in Aggregator but gave it Unsorted I/P then what will
happen?


    28.Suggest an approach - one source flat file, how can i it it into two files using
    Informatica?
    U can import more than one flat file in a single source definition using the indirect option. what U
    have to do is list all the filenames in a file and use this file name and path in session properties
    and the use the indirect option in the same properties.
    29. If I have 100 rows given as I/P to aggregator & want 100 rows as O/P then how
    can u achieve that? (none of the columns are primary key)

    I am wondering how we can achieve 100 o/p for the 6th question.
    As I tried the only chance of getting 100 output is setting all the i/p columns to group by. By doing
    that I am not achieving the purpose of Aggregator.

    30.What is the use of Return Port & Output port in Lookup Transformation?

How to transform normalized data to denormalized form in
informatica? Is there any logic or any transformations to
achieve this?
--We cannot use normalizer transformation in this case.
-It used for normalizing the records.
Use decode in expression transformation and then aggregator
transformation.

How will you display "Mr" for male & "Mrs" for female in
target table?
--based on flag for male and female we can use decode logic at
expression level before loading records to the target tables.
--but if in the database is too old and they dont have column
flag then how you u define.

--Here the question is to just display value 'MR'for
males 'MR'for males , not to modify the data in the table
as 'MR'for males 'MRS'for males

we can do it by many ways, one among those ........

select decode(column_name, 'male','MR','female','MRS')

this query displays MR for all males and MRS for all females

What will happen when Mapping variable and Mapping parameter
is not defined or given?
Where do you use mapping variable and mapping parameter?
--to my knowledge when the values of mapping parameter or
variable is not defined session takes null instead n hence
the session fails. they can b used in mapping or mapplet.
--If Mapping Parameter or variable is not defined in
parameter file session will fail, but if default value is
defined at , then session will take those default values.

if we r   using aggregator we enabled sorted input but the
records   r not sorted what happen?
Session   throws warning. can see it in log file
In case   of Join , i guess session fails

how to join the two flatfiles using the joiner t/r if there
is no matching port?
yes you can join two flatfiles using the joiner t/r even if
you don't have any matching port for that you need to take
one dummy column in source files and based on the dummy
column you can join them

what is casual dimension?
One of the most interesting and valuable dimensions in a
data warehouse is one that explains why a fact table record
exists. In most data warehouses, you build a fact table
record when something happens. For example:

When the cash register rings in a retail store, a fact
table record is created for each line item on the sales
ticket. The obvious dimensions of this fact table record
are product, store, customer, sales ticket, and time.
At a bank ATM, a fact table record is created for every
customer transaction. The dimensions of this fact table
record are financial service, ATM location, customer,
transaction type, and time.
When the telephone rings, the phone company creates a fact
table record for each "hook event." A complete call-
tracking data warehouse in a telephone company records each
completed call, busy signal, wrong number, and partially
dialed call.
In all three of these cases, a physical event takes place,
and the data warehouse responds by storing a fact table
record. However, the physical events and the corresponding
fact table records are more interesting than simply storing
a small piece of rev enue. Each event represents a
conscious decision by the customer to use the product or
the service. A good marketing person is fascinated by these
events. Why did the customer choose to buy the product or
use the service at that exact moment? If we only had a
dimension called "Why Did The Customer Buy My Product Just
Now?" our data warehouses could answer almost any marketing
question. We call a dimension like this a "causal"
dimension, because it explains what caused the event.
How to merge First Name & Last Name?
--Find the space by using the Instr() function and then split
into two.. then merge.
--by using concatination operator u can get it.
firstname||lastname

How will you combine 3 different sources with a single source?
use union transformation or you can write sql override if
you have advance knowledge of table join conditions for the
current database.

Is it passive or active when check and uncheck the box of
DISTINCT in Sorter transformation? why?
It is Active transformation.
If you configure the Sorter transformation for distinct
output rows, the Mapping Designer configures all ports as
part of the sort key. When the PowerCenter Server runs the
session, it discards duplicate rows compared during the
sort operation.

why sequence generator should not directly connected to
joiner transformation ?
Mainly sequence generator is used to generate an unique id
dynamically.
We can not join this number against any column in other
tables...
So.. we can not connect sequence generator with joiner.

and also,

Main reason is joiner is a Active transformation.Means it
can alter the number of rows.
So, if u connect sequence generator with joiner the outcome
sequence will not be proper.

Is it possible to update a table without Lkp Transformation?
Yes, set treat source rows as update in session properties!
Could you please explain what is a shift sensitive flat file
go to help flat file sources go and read
suppose in lookup transformation, when it looks up multiple match, the error should be
passed
It has different settings for multiple match. See LOOKUP properties and multiple match options.
It will select first value or last value or any value or error as per the selected option!
HOW CAN I IMPORT THE HEADER AND FOOTER OF A FLAT FILE IN A MAPPING?
WITHOUT USING SHELL SCRIPT?
in the target properties in the mapping tab of the session , click next to header options. You can
choose to have field names in the target.
for footer....easiest way is to append the file with a shell script. Or else insert the footer text in the
largest field of the target after all the data is loaded.




This is i iave problem how to solve this problem
i got a data from flat files i got the one column with numbers and characters
my requriment is to convert the character into numbers
this is my data : pincode
72A5246
Z6258x5
8265L68 which function we use please immediately
In expn trans write like this
to_integer(to_char(pincode))
this expn coverts the string value to integer
like:
72A5246---->725246


Restrictions
You cannot change data for the following output ports:

       Normalizer transformation. Generated Keys and Generated Column ID ports.
       Rank transformation. RANKINDEX port.
       Router transformation. All output ports.
       Sequence Generator transformation. CURRVAL and NEXTVAL ports.
       Lookup transformation. NewLookupRow port for a Lookup transformation configured
        to use a dynamic cache.
       Custom transformation. Ports in output groups other than the current output group.

Additionally, you cannot change data associated with the following:

       Mapplets that are not selected for debugging
       Input or input/output ports
       Output ports when the Debugger pauses on an error breakpoint

Incremental Aggregation

If the source tables change significantly, you might want to run the session with the entire source
data. To do this, you can configure the session to reinitialize the aggregate cache.

For example, you can reinitialize the aggregate cache if the source for a session changes
incrementally every day and completely changes once a month. When you receive the new
monthly source, you might configure the session to reinitialize the aggregate cache, truncate the
existing target, and use the new source table during the session.

After you run a session that reinitializes the aggregate cache, edit the session properties to
disable the Reinitialize Aggregate Cache option. If you do not clear Reinitialize Aggregate
Cache, the PowerCenter Server overwrites the aggregate cache each time you run the session.

Note: When you move from Windows to UNIX, you must reinitialize the cache. Therefore, you
cannot change from a Latin1 code page to an MSLatin1 code page, even though these code pages
are compatible.

Once you run an incremental aggregation session, avoid moving or modifying the index and data
files that store historical aggregate information.


    Data warehousing concepts


    1. What is difference between view and materialized view?
    Views contains query whenever execute views it has read from base table
    Where as M views loading or replicated takes place only once which gives you better query performance
Refresh m views 1.on commit and 2. On demand
(Complete, never, fast, force)


2. What is bitmap index why it’s used for DWH?
A bitmap for each key value replaces a list of rowids. Bitmap index more efficient for data warehousing
because low cardinality, low updates, very efficient for where class


3. What is star schema? And what is snowflake schema?
The center of the star consists of a large fact table and the points of the star are the dimension tables.

Snowflake schemas normalized dimension tables to eliminate redundancy. That is, the
Dimension data has been grouped into multiple tables instead of one large table.

Star schema contains demoralized dimension tables and fact table, each primary key values in dimension table
associated with foreign key of fact tables.
Here a fact table contains all business measures (normally numeric data) and foreign key values, and dimension
tables has details about the subject area.

Snowflake schema basically a normalized dimension tables to reduce redundancy in the dimension tables


4. Why need staging area database for DWH?
Staging area needs to clean operational data before loading into data warehouse.
Cleaning in the sense your merging data which comes from different source


5. What are the steps to create a database in manually?
Create os service and create init file and start data base no mount stage then give create data base command.


6. Difference between OLTP and DWH?
OLTP system is basically application orientation (eg, purchase order it is functionality of an application)
Where as in DWH concern is subject orient (subject in the sense custorer, product, item, time)

OLTP
·  Application Oriented
·  Used to run business
·  Detailed data
·  Current up to date
·  Isolated Data
·  Repetitive access
·  Clerical User
·  Performance Sensitive
·  Few Records accessed at a time (tens)
·  Read/Update Access
·  No data redundancy
·  Database Size 100MB-100 GB

DWH
· Subject Oriented
· Used to analyze business
· Summarized and refined
· Snapshot data
· Integrated Data
· Ad-hoc access
· Knowledge User
· Performance relaxed
· Large volumes accessed at a time(millions)
· Mostly Read (Batch Update)
· Redundancy present
· Database Size 100 GB - few terabytes
7.Why need data warehouse?

A single, complete and consistent store of data obtained from a variety of different sources made available to
end users in a what they can understand and use in a business context.

A process of transforming data into information and making it available to users in a timely enough manner to
make a difference Information

Technique for assembling and managing data from various sources for the purpose of answering business
questions. Thus making decisions that were not previous possible



8.What is difference between data mart and data warehouse?

A data mart designed for a particular line of business, such as sales, marketing, or finance.

Where as data warehouse is enterprise-wide/organizational

The data flow of data warehouse depending on the approach


9. What is the significance of surrogate key?
Surrogate key used in slowly changing dimension table to track old and new values and it’s derived from
primary key.



10.What is slowly changing dimension. What kind of scd used in your project?
Dimension attribute values may change constantly over the time. (Say for example customer dimension has
customer_id,name, and address) customer address may change over time.
How will you handle this situation?
There are 3 types, one is we can overwrite the existing record, second one is create additional new record at the
time of change with the new attribute values.
Third one is create new field to keep new values in the original dimension table.


11. What is difference between primary key and unique key constraints?
Primary key maintains uniqueness and not null values
Where as unique constrains maintain unique values and null values


12. What are the types of index? And is the type of index used in your project?
Bitmap index, B-tree index, Function based index, reverse key and composite index.
We used Bitmap index in our project for better performance.

13. How is your DWH data modeling (Details about star schema)?

14.A table have 3 partitions but I want to update in 3rd partitions how will you do?

Specify partition name n the update statement. Say for example
Update employee partition (name) a, set a.empno=10 where ename=’Ashok’
15. When you give an update statement how memory flow will happen and how oracles allocate memory
for that?

Oracle first checks in Shared Sql area whether same Sql statement is available if it is there it uses. Otherwise
allocate memory in shared Sql area and then create run time memory in Private Sql area to create parse tree and
execution plan. Once it completed stored in the shared Sql area wherein previously allocated memory

16. Write a query to find out 5th max salary? In Oracle, DB2, SQL Server
Select (list the columns you want) from (select salary from employee order by salary)
Where rownum<5

17. When you give an update statement how undo/rollback segment will work/what are the steps?

Oracle keep old values in undo segment and new values in redo entries. When you say rollback it replace old
values from undo segment. When you say commit erase the undo segment values and keep new vales in
permanent.

Informatica Administration

18. What is DTM? How will you configure it?
DTM transform data received from reader buffer and its moves transformation to transformation on row by row
basis and it uses transformation caches when necessary.

19. You transfer 100000 rows to target but some rows get discard how will you trace them? And where its
get loaded?

Rejected records are loaded into bad files. It has record indicator and column indicator.

Record indicator identified by (0-insert,1-update,2-delete,3-reject) and column indicator identified by (D-valid-
overflow,N-null,T-truncated).

Normally data may get rejected in different reason due to transformation logic

20.What are the different uses of a repository manager?

Repository manager used to create repository which contains metadata the informatica uses to transform data
from source to target. And also it use to create informatica user’s and folders and copy, backup and restore the
repository

21.How do you take care of security using a repository manager?

Using repository privileges, folder permission and locking.

Repository privileges(Session operator, Use designer, Browse repository, Create session and batches,
Administer repository, administer server, super user)

Folder permission(owner, groups, users)

Locking(Read, Write, Execute, Fetch, Save)



22.What is a folder?

Folder contains repository objects such as sources, targets, mappings, transformation which are helps logically
organize our data warehouse.


23.Can you create a folder within designer?

Not possible

24.What are shortcuts? Where it can be used? What are the advantages?

There are 2 shortcuts(Local and global) Local used in local repository and global used in global repository. The
advantage is reuse an object without creating multiple objects. Say for example a source definition want to use
in 10 mappings in 10 different folder without creating 10 multiple source you create 10 shotcuts.
25.How do you increase the performance of mappings?

Use single pass read(use one source qualifier instead of multiple SQ for same table)
Minimize data type conversion (Integer to Decimal again back to Integer)
Optimize transformation(when you use Lookup, aggregator, filter, rank and joiner)
Use caches for lookup
Aggregator use presorted port, increase cache size, minimize input/out port as much as possible
Use Filter wherever possible to avoid unnecessary data flow

26.Explain Informatica Architecture?
Informatica consist of client and server. Client tools such as Repository manager, Designer, Server manager.
Repository data base contains metadata it read by informatica server used read data from source, transforming
and loading into target.

27.How will you do sessions partitions?
It’s not available in power part 4.7


Transformation

28.What are the constants used in update strategy?

DD_INSERT, DD_UPDATE, DD_DELETE, DD_REJECT

29.What is difference between connected and unconnected lookup transformation?

Connected lookup return multiple values to other transformation
Where as unconnected lookup return one values
If lookup condition matches Connected lookup return user defined default values
Where as unconnected lookup return null values
Connected supports dynamic caches where as unconnected supports static

30.What you will do in session level for update strategy transformation?

In session property sheet set Treat rows as “Data Driven”




31.What are the port available for update strategy , sequence generator, Lookup, stored procedure
transformation?

Transformations                 Port
Update strategy                          Input, Output
Sequence Generator              Output only
Lookup                                   Input, Output, Lookup, Return
Stored Procedure                Input, Output

32.Why did you used connected stored procedure why don’t use unconnected stored procedure?

33.What is active and passive transformations?

Active transformation change the no. of records when passing to targe(example filter)
where as passive transformation will not change the transformation(example expression)

34.What are the tracing level?
Normal – It contains only session initialization details and transformation details no. records rejected, applied
Terse - Only initialization details will be there
Verbose Initialization – Normal setting information plus detailed information about the transformation.
Verbose data – Verbose init. Settings and all information about the session
35.How will you make records in groups?

Using group by port in aggregator

36.Need to store value like 145 into target when you use aggregator, how will you do that?

Use Round() function

37.How will you move mappings from development to production database?

Copy all the mapping from development repository and paste production repository while paste it will promt
whether you want replace/rename. If say replace informatica replace all the source tables with repository
database.

38.What is difference between aggregator and expression?

Aggregator is active transformation and expression is passive transformation
Aggregator transformation used to perform aggregate calculation on group of records
Where as expression used perform calculation with single record

39.Can you use mapping without source qualifier?

Not possible, If source RDBMS/DBMS/Flat file use SQ or use normalizer if the source cobol feed

40.When do you use a normalizer?

Normalized can be used in Relational to denormilize data.

41.What are stored procedure transformations. Purpose of sp transformation. How did you go about
using your project?
Connected and unconnected stored procudure.
Unconnected stored procedure used for data base level activities such as pre and post load

Connected stored procedure used in informatica level for example passing one parameter as input and capturing
return value from the stored procedure.

Normal - row wise check
Pre-Load Source - (Capture source incremental data for incremental aggregation)
Post-Load Source - (Delete Temporary tables)
Pre-Load Target - (Check disk space available)
Post-Load Target – (Drop and recreate index)

42.What is lookup and difference between types of lookup. What exactly happens when a lookup is
cached. How does a dynamic lookup cache work.
Lookup transformation used for check values in the source and target tables(primary key values).
There are 2 type connected and unconnected transformation
Connected lookup returns multiple values if condition true
Where as unconnected return a single values through return port.
Connected lookup return default user value if the condition does not mach
Where as unconnected return null values
Lookup cache does:
Read the source/target table and stored in the lookup cache

43.What is a joiner transformation?

Used for heterogeneous sources(A relational source and a flat file)

Type of joins:

Assume 2 tables has values(Master - 1, 2, 3 and Detail - 1, 3, 4)
Normal(If the condition mach both master and detail tables then the records will be displaced. Result set 1, 3)
Master Outer(It takes all the rows from detail table and maching rows from master table. Result set 1, 3, 4)
Detail Outer(It takes all the values from master source and maching values from detail table. Result set 1, 2, 3)
Full Outer(It takes all values from both tables)

44.What is aggregator transformation how will you use in your project?

Used perform aggregate calculation on group of records and we can use conditional clause to filter data

45.Can you use one mapping to populate two tables in different schemas?

Yes we can use

46.Explain lookup cache, various caches?

Lookup transformation used for check values in the source and target tables(primary key values).

Various Caches:

Persistent cache (we can save the lookup cache files and reuse them the next time process the lookup
transformation)

Re-cache from database (If the persistent cache not synchronized with lookup table you can configure the
lookup transformation to rebuild the lookup cache)

Static cache (When the lookup condition is true, Informatica server return a value from lookup cache and it’s
does not update the cache while it processes the lookup transformation)

Dynamic cache (Informatica server dynamically inserts new rows or update existing rows in the cache and the
target. Suppose if we want lookup a target table we can use dynamic cache)

Shared cache (we can share lookup transformation between multiple transformations in a mapping. 2 lookup in
a mapping can share single lookup cache)


47.Which path will the cache be created?

User specified directory. If we say c:\ all the cache files created in this directory.

48.Where do you specify all the parameters for lookup caches?

Lookup property sheet/tab.

49.How do you remove the cache files after the transformation?

After session complete, DTM remove cache memory and deletes caches files.
In case using persistent cache and Incremental aggregation then caches files will be saved.

50.What is the use of aggregator transformation?

To perform Aggregate calculation

Use conditional clause to filter data in the expression Sum(commission, Commission >2000)

Use non-aggregate function iif (max(quantity) > 0, Max(quantitiy), 0))

51.What are the contents of index and cache files?

Index caches files hold unique group values as determined by group by port in the transformation.

Data caches files hold row data until it performs necessary calculation.
52.How do you call a store procedure within a transformation?

In the expression transformation create new out port in the expression write :sp.stored procedure
name(arguments)

53.Is there any performance issue in connected & unconnected lookup? If yes, How?

Yes

Unconnected lookup much more faster than connected lookup why because in unconnected not connected to
any other transformation we are calling it from other transformation so it minimize lookup cache value

Where as connected transformation connected to other transformation so it keeps values in the lookup cache.

54.What is dynamic lookup?

When we use target lookup table, Informatica server dynamically insert new values or it updates if the values
exist and passes to target table.

55.How Informatica read data if source have one relational and flat file?

Use joiner transformation after source qualifier before other transformation.

56.How you will load unique record into target flat file from source flat files has duplicate data?

There are 2 we can do this either we can use Rank transformation or oracle external table
In rank transformation using group by port (Group the records) and then set no. of rank 1. Rank transformation
return one value from the group. That the values will be a unique one.


57.Can you use flat file for repository?

No, We cant

58.Can you use flat file for lookup table?

No, We cant

59.Without Source Qualifier and joiner how will you join tables?

In session level we have option user defined join. Where we can write join condition.

60.Update strategy set DD_Update but in session level have insert. What will happens?

Insert take place. Because this option override the mapping level option



Sessions and batches

61.What are the commit intervals?

Source based commit (Based on the no. of active source records(Source qualifier) reads. Commit interval set
10000 rows and source qualifier reads 10000 but due to transformation logic 3000 rows get rejected when 7000
reach target commit will fire, so writer buffer does not rows held the buffer)

Target based commit (Based on the rows in the buffer and commit interval. Target based commit set 10000
but writer buffer fills every 7500, next time buffer fills 15000 now commit statement will fire then 22500 like
go on.)

62.When we use router transformation?
When we want perform multiple condition to filter out data then we go for router. (Say for example source
records 50 filter condition mach 10 records remaining 40 records get filter out but still we want perform few
more filter condition to filter remaining 40 records.)

63.How did you schedule sessions in your project?

Run once (set 2 parameter date and time when session should start)

Run Every (Informatica server run session at regular interval as we configured, parameter Days, hour, minutes,
end on, end after, forever)

Customized repeat (Repeat every 2 days, daily frequency hr, min, every week, every month)

Run only on demand(Manually run) this not session scheduling.

64.How do you use the pre-sessions and post-sessions in sessions wizard, what for they used?

Post-session used for email option when the session success/failure send email. For that we should configure
Step1. Should have a informatica startup account and create outlook profile for that user
Step2. Configure Microsoft exchange server in mail box applet(control panel)
Step3. Configure informatica server miscellaneous tab have one option called MS exchange profile where we
have specify the outlook profile name.

Pre-session used for even scheduling (Say for example we don’t know whether source file available or not in
particular directory. For that we write one DOS command to move file directory to destination and set event
based scheduling option in session property sheet Indicator file wait for).

65.What are different types of batches. What are the advantages and dis-advantages of a concurrent
batch?

Sequential(Run the sessions one by one)

Concurrent (Run the sessions simultaneously)

Advantage of concurrent batch:

It’s takes informatica server resource and reduce time it takes run session separately.
Use this feature when we have multiple sources that process large amount of data in one session. Split sessions
and put into one concurrent batches to complete quickly.


Disadvantage

Require more shared memory otherwise session may get failed

66.How do you handle a session if some of the records fail. How do you stop the session in case of errors.
Can it be achieved in mapping level or session level?

It can be achieved in session level only. In session property sheet, log files tab one option is the error handling
Stop on ------ errors. Based on the error we set informatica server stop the session.

67.How you do improve the performance of session.

If we use Aggregator transformation use sorted port, Increase aggregate cache size, Use filter before
aggregation so that it minimize unnecessary aggregation.

Lookup transformation use lookup caches

Increase DTM shared memory allocation
Eliminating transformation errors using lower tracing level(Say for example a mapping has 50 transformation
when transformation error occur informatica server has to write in session log file it affect session performance)

68.Explain incremental aggregation. Will that increase the performance? How?

Incremental aggregation capture whatever changes made in source used for aggregate calculation in a session,
rather than processing the entire source and recalculating the same calculation each time session run. Therefore
it improve session performance.

Only use incremental aggregation following situation:

Mapping have aggregate calculation
Source table changes incrementally
Filtering source incremental data by time stamp

Before Aggregation have to do following steps:

Use filter transformation to remove pre-existing records

Reinitialize aggregate cache when source table completely changes for example incremental changes happing
daily and complete changes happenings monthly once. So when the source table completely change we have
reinitialize the aggregate cache and truncate target table use new source table. Choose Reinitialize cache in the
aggregation behavior in transformation tab

69.Concurrent batches have 3 sessions and set each session run if previous complete but 2nd fail then
what will happen the batch?

Batch will fail

General Project

70.How many mapping, dimension tables, Fact tables and any complex mapping you did? And what is
your database size, how frequently loading to DWH?

I did 22 Mapping, 4 dimension table and one fact table. One complex mapping I did for slowly changing
dimension table. Database size is 9GB. Loading data every data.

71.What are the different transformations used in your project?

Aggregator, Expression, Filter, Sequence generator, Update Strategy, Lookup, Stored Procedure, Joiner, Rank,
Source Qualifier.

72.How did you populate the dimensions tables?

73.What are the sources you worked on?
Oracle

74.How many mappings have you developed on your whole dwh project?

45 mappings

75.What is OS used your project?

Windows NT

76.Explain your project (Fact table, dimensions, and database size)

Fact table contains all business measures(numeric values) and foreign key values, Dimension table contains
details about subject area like customer, product

77.What is difference between Informatica power mart and power center?
Using power center we can create global repository
Power mart used to create local repository
Global repository configure multiple server to balance session load
Local repository configure only single server

78.Have you done any complex mapping?

Developed one mapping to handle slowly changing dimension table.

79.Explain details about DTM?

Once we session start, load manager start DTM and it allocate session shared memory and contains reader and
writer. Reader will read source data from source qualifier using SQL statement and move data to DTM then
DTM transform data to transformation to transformation and row by row basis finally move data to writer then
writer write data into target using SQL statement.


I-Flex Interview (14th May 2003)


80.What are the key you used other than primary key and foreign key?



Used surrogate key to maintain uniqueness to overcome duplicate value in the primary key.


81.Data flow of your Data warehouse(Architecture)

DWH is a basic architecture (OLTP to Data warehouse from DWH OLAP analytical and report building.

82.Difference between Power part and power center?

Using power center we can create global repository
Power mart used to create local repository
Global repository configure multiple server to balance session load
Local repository configure only single server

83.What are the batches and it’s details?

Sequential(Run the sessions one by one)

Concurrent (Run the sessions simultaneously)

Advantage of concurrent batch:

It’s takes informatica server resource and reduce time it takes run session separately.
Use this feature when we have multiple sources that process large amount of data in one session. Split sessions
and put into one concurrent batches to complete quickly.


Disadvantage

Require more shared memory otherwise session may get failed

84.What is external table in oracle. How oracle read the flat file

Used for read flat file. Oracle internally write SQL loader script with control file.

85.What are the index you used? Bitmap join index?
Bitmap index used in data warehouse environment to increase query response time, since DWH has low
cardinality, low updates, very efficient for where clause.

Bitmap join index used to join dimension and fact table instead reading 2 different index.

86.What are the partitions in 8i/9i? Where you will use hash partition?

In oracle8i there are 3 partition (Range, Hash, Composite)
In Oracle9i List partition is additional one

Range (Used for Dates values for example in DWH ( Date values are Quarter 1, Quarter 2, Quarter 3, Quater4)

Hash (Used for unpredictable values say for example we cant able predict which value to allocate which
partition then we go for hash partition. If we set partition 5 for a column oracle allocate values into 5 partition
accordingly).

List (Used for literal values say for example a country have 24 states create 24 partition for 24 states each)

Composite (Combination of range and hash)

91.What is main difference mapplets and mapping?

Reuse the transformation in several mappings, where as mapping not like that.

If any changes made in mapplets it automatically inherited in all other instance mapplets.

92. What is difference between the source qualifier filter and filter transformation?
Source qualifier filter only used for relation source where as Filter used any kind of source.

Source qualifier filter data while reading where as filter before loading into target.

93. What is the maximum no. of return value when we use unconnected
transformation?

Only one.

94. What are the environments in which informatica server can run on?

Informatica client runs on Windows 95 / 98 / NT, Unix Solaris, Unix AIX(IBM)

Informatica Server runs on Windows NT / Unix

Minimum Hardware requirements

Informatica Client Hard disk 40MB, RAM 64MB

Informatica Server Hard Disk 60MB, RAM 64MB


95. Can unconnected lookup do everything a connected lookup transformation can do?

No, We cant call connected lookup in other transformation. Rest of things it’s possible

96. In 5.x can we copy part of mapping and paste it in other mapping?

I think its possible

97. What option do you select for a sessions in batch, so that the sessions run one
after the other?

We have select an option called “Run if previous completed”
98. How do you really know that paging to disk is happening while you are using a lookup
transformation? Assume you have access to server?

We have collect performance data first then see the counters parameter lookup_readtodisk if it’s greater than 0
then it’s read from disk

Step1. Choose the option “Collect Performance data” in the general tab session property
        sheet.
Step2. Monitor server then click server-request  session performance details
Step3. Locate the performance details file named called session_name.perf file in the session
       log file directory
Step4. Find out counter parameter lookup_readtodisk if it’s greater than 0 then informatica
       read lookup table values from the disk. Find out how many rows in the cache see
       Lookup_rowsincache



99. List three option available in informatica to tune aggregator transformation?

Use Sorted Input to sort data before aggregation
Use Filter transformation before aggregator

Increase Aggregator cache size


100.Assume there is text file as source having a binary field to, to source qualifier What native data type
informatica will convert this binary field to in source qualifier?

Binary data type for relational source for flat file ?
101.Variable v1 has values set as 5 in designer(default), 10 in parameter file, 15 in
    repository. While running session which value informatica will read?

e8l uInformatica read value 15 from repository

102. Joiner transformation is joining two tables s1 and s2. s1 has 10,000 rows and s2 has 1000 rows .
Which table you will set master for better performance of joiner
transformation? Why?

Set table S2 as Master table because informatica server has to keep master table in the cache so if it is 1000 in
cache will get performance instead of having 10000 rows in cache

103. Source table has 5 rows. Rank in rank transformation is set to 10. How many rows the rank
transformation will output?

5 Rank

104. How to capture performance statistics of individual transformation in the mapping and explain
some important statistics that can be captured?

Use tracing level Verbose data

105. Give a way in which you can implement a real time scenario where data in a table is changing and
you need to look up data from it. How will you configure the lookup transformation for this purpose?

In slowly changing dimension table use type 2 and model 1

106. What is DTM process? How many threads it creates to process data, explain each
thread in brief?

DTM receive data from reader and move data to transformation to transformation on row by row basis. It’s
create 2 thread one is reader and another one is writer
   107. Suppose session is configured with commit interval of 10,000 rows and source has 50,000 rows
   explain the commit points for source based commit & target based commit. Assume appropriate value
   wherever required?

   Target Based commit (First time Buffer size full 7500 next time 15000)

   Commit Every 15000, 22500, 30000, 40000, 50000

   Source Based commit(Does not affect rows held in buffer)

   Commit Every 10000, 20000, 30000, 40000, 50000

   108.What does first column of bad file (rejected rows) indicates?

   First Column - Row indicator (0, 1, 2, 3)

   Second Column – Column Indicator (D, O, N, T)

   109. What is the formula for calculation rank data caches? And also Aggregator, data, index caches?

   Index cache size = Total no. of rows * size of the column in the lookup condition (50 * 4)

   Aggregator/Rank transformation Data Cache size = (Total no. of rows * size of the column in the lookup
   condition) + (Total no. of rows * size of the connected output ports)


   110. Can unconnected lookup return more than 1 value?

   No


ORACLE QUESTIONS
Q: What is PL/SQL?

A: Procedural Language/Structured Query Language (PL/SQL) is a procedural language.
      It is the native database programming language within Oracle utilized by several
      Oracle development tools.

Q: What is the difference between SQL and PL/SQL?

A: SQL is a structured query language. It contains SELECT, INSERT, UPDATE, and DELETE
      statements. SQL is an ANSI standard tool and is widely used by relational
      databases such as Oracle, Informix, DB2, and so on. PL/SQL is a block-structured
      programming language, and the use of this tool is limited to the Oracle database
      and Oracle development tools.
   In SQL, statements are processed by the database one at a time, whereas in PL/SQL,
      multiple SQL statements can be included in a single block and processed together
      in the database at the same time. This reduces the frequency of database calls. By
      doing so, PL/SQL obviously improves its performance. PL/SQL also has additional
      features such as control statements, iterative statements, error handling,
      procedures, functions, and so on.

Q: What is DDL?

A: DDL stands for Data Definition Language. You can create or drop the objects using
      DDL. Examples include CREATE TABLE, DROP TABLE, RENAME TABLE, CREATE
      VIEW, and DROP VIEW.

Q: What is DML?

A: DML stands for Data Manipulation Language. You can manipulate the object's data.
     Examples include INSERT, SELECT, UPDATE, and DELETE.
Q: What is DCL?

A: DCL stands for Data Control Language. You can control access to the data or to the
      database. Examples include GRANT and REVOKE.

Q: What is ROWID?

A: ROWID, an Oracle-defined column, contains the data block address. It can be used to
     directly point to where the data physically resides on the disk. It can also improve
     the performance of SQL transactions when referenced in the SQL statement.

Q: How do you execute a host operating system command from SQL*PLUS?

A: From the SQL*PLUS command prompt, you can use the HOST command to perform
      the operating system level tasks. Use the following example to list all SQL
      extension files in Unix:
   SQL HOST ls *.sql

Q: Can PL/SQL be integrated with any other Oracle development tools?

A: Yes, PL/SQL is already integrated with Oracle forms, Oracle reports, Oracle graphs,
      SQL*PLUS, Pro*C, Pro*Cobol, Oracle Call Interface (OCI), and Enterprise
      Manager.

Q: Does PL/SQL code run at the server side or on the client machine?

A: Both. If you run PL/SQL code in SQL*PLUS, it usually runs at the server side. Front-
      end tools, such as Oracle forms and reports, run PL/SQL on the client machine.

Q: Is PL/SQL code case-sensitive?

A: No. For code readability, it is always recommended to type reserved words in upper
      case.

Q: What does a PL/SQL block contain?

A: A PL/SQL block contains three sections: the declaration section, the program code
      section, and the error-handling section. The following is a listing of the typical
      PL/SQL block structure:
   DECLARE
   Declaration Section
   BEGIN
    If you do not have a source, target, or mapping bottleneck, you may have a session
   bottleneck.
   You can identify a session bottleneck by using the performance details. The informatica
   server creates performance details when you enable Collect Performance Data on the
   General Tab of the session properties.
   Performance details display information about each Source Qualifier, target definitions,
   and individual transformation. All transformations have some basic counters that indicate
   the Number of input rows, output rows, and error rows. Any value other than zero in the

   DECLARE
   hours integer;
   rate number(10,2);
   amount number(10,2);
   BEGIN
   amount := hours * rate;
   EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('Error') ;
   END;    /

Q: What are the main data types available in PL/SQL?

A: Though so many data types are available in PL/SQL, the following are the commonly
      used data types: NUMBER, INTEGER, DATE, CHAR, VARCHAR2, and BOOLEAN.

Q: What is the difference between CHAR and VARCHAR2?

A: CHAR is a fixed-length character string, while VARCHAR2 is a variable length. In
      other words, the CHAR data type will add spaces to data to make sure the
      populated data is the exact length of the defined width. VARCHAR2 holds up to
      the maximum length of a string, rather than the defined length.

Q: What is the size limitation for VARCHAR2 in Oracle 8?

A: The VARCHAR2 data type can have up to a maximum of 4,000 characters (bytes).

Q: What is an anonymous block in PL/SQL?

A: An anonymous block is a PL/SQL block with no name assigned. The following
      listing is an example of an anonymous block:
   BEGIN
   DBMS_OUTPUT.PUT_LINE('Print this line.');
   303
   END;    /




Q: How many categories of operators does PL/SQL support?

A: PL/SQL supports four different categories of operators:
    Arithmetic operators: +, -, *, /
    Comparison operators: =, , !=, , =, , =, BETWEEN, IN, IS NULL, LIKE
    Logical operators: AND, OR, NOT
    String operators: concatenation operators such as ||

Q: What is NULL? How can you substitute the NULL values in PL/SQL?

A: NULL represents an unknown value. You can use an NVL function to take care of the
      NULL values.



Q: What are the main data conversion functions?

A: Three widely used conversion functions in PL/SQL convert information from one data
      type to another, as follows:
   1. TO_CHAR transforms a number or date to a character string, such as
   2. SELECT TO_CHAR(SYSDATE,'HH:MI:SS')
   3-YY').     FROM DUAL;
   4. TO_DATE transforms a character string to a date, such as
   5. SELECT TO_DATE('29-JUN-99', 'DD-MON
   6. FROM DUAL;
   7. TO_NUMBER transforms a character string to a number, such as
   SELECT TO_NUMBER('6.9')
   FROM DUAL;

Q: What is the control statement in PL/SQL?

A: The IF...THEN...ELSE statement can be used to evaluate more than one condition.
      Here's an example:
   IF (x 10) THEN
   Y := TRUE;
   ELSE
   Y := FALSE;
   END IF;

Q: Does SQL support any control statements?

A: Although we cannot directly use IF...THEN...ELSE statements in SQL, we can achieve
       the same results by using the DECODE function. Here's an example:
   DECODE(variable,if1,val1,if2,val2,.......,else)

Q: Can you use BETWEEN, LIKE, and IN operands in an assignment statement?

A: Yes, PL/SQL extends the operand capabilities. Here's an example:
   DECLARE
   found BOOLEAN;
   dept_id NUMBER;
   dept_name VARCHAR2(20);
   BEGIN
   found := dept_id BETWEEN 1 and 5;
   found := dept_name LIKE '%eng%';
   found := dept_name IN ('Engineering', 'Procurement', 'Sales');
   END;

Q: What is a loop? How many types of loops are available in PL/SQL?

A: A loop is an iterative statement and can be used to process the code within a block
       repeatedly until it satisfies the condition. A FOR loop repeats a specified number
       of times. Here's an example of a FOR loop syntax:
   FOR index IN low_num....high_num LOOP
   execute statements
   END LOOP;
   WHILE condition true LOOP
   execute statements
   END LOOP;
   LOOP
   execute statements
   EXIT WHEN condition true
   END LOOP;
   REPEAT
   execute statements
   UNTIL condition true
Q: What is a cursor?

A: A cursor is a temporary work area (a context area) in memory where a database
      holds the current SQL statement.



Q: What is the difference between implicit cursors and explicit cursors?

A: Implicit cursors are defined by the Oracle database, while users define the explicit
      cursors that are used to process queries that return multiple data records. By
      declaring explicit cursors, you obviously get an improved performance.

Q: How do you define an explicit cursor?

A: It must be defined in a four-step process: declare the cursor, open the cursor, fetch
       the cursor, and close the cursor. Here's an example:
   DECLARE
   CURSOR dept_cur IS
   SELECT dept_no, dept_name
   FROM department
   WHERE dept_no IS NOT NULL;
   dept_number INTEGER;
   dept_names VARCHAR2(30);
   BEGIN
   OPEN dept_cur;
   LOOP
   FETCH dept_cur INTO dept_number, dept_names;
   EXIT WHEN dept_cur%NOTFOUND;
   END LOOP;
   CLOSE dept_cur;
   END;    /

Q: Assuming that the cursor is defined, how would you retrieve multiple sets of data?

A: A FETCH statement must be placed inside the loop to retrieve more than one record
      of information.

Q: Can any alternate approach be used to define the explicit cursors?

A: Yes, it can be used with a CURSOR FOR LOOP construct. It reduces the coding efforts
      and minimizes the human errors. In the following example, you do not need to
      use OPEN, FETCH, and CLOSE statements.
   DECLARE
   CURSOR dept_cur IS
   SELECT dept_no, dept_name
   FROM department
   WHERE dept_no IS NOT NULL;
   dept_var dept_cur%ROWTYPE;
   BEGIN
   FOR dept_var IN dept_cur
   LOOP
   INSERT INTO dept VALUES(dept_var.dept_no,             dept_var.dept_name);
   END LOOP;
   END; /

Q: What is the advantage of using cursor parameters?

A: First, cursor parameters can be used to limit the local variables in a block. Next, the
       same cursor definition can be used for different parameter values, avoiding the
       code repetition.

Q: Can you reference the same parameters in more than one cursor definition?

A: No, the scope of the cursor parameters is limited to only one cursor definition.

Q: What is a cursor variable? What are the advantages?

A: A cursor variable is defined like a PL/SQL variable with a few exceptions. First, you
      have to define a cursor type declaration. Here is its syntax:
   TYPE cursor_type_name IS REF CURSOR RETURN return_type:
   Here's an actual example:
   TYPE dept_cur_type IS REF CURSOR RETURN dept_cur%ROWTYPE;
   Then you can declare the cursor variable. Its syntax is as follows:
   dept_cur_type_var dept_cur_type;
   This can be used to make the code more generic and reusable. It can also be used to
      pass variables from precompilers to a PL/SQL block and back.

Q: What tells SQL*PLUS to display the DBMS_OUTPUT?

A: Two steps are involved here. First, you need to type the following command at SQL
      prompt in SQL*PLUS.
   SQL SET SERVEROUTPUT ON
   Next, you need to use a DBMS_OUTPUT package within the PL/SQL script. Here's an
      example:
   DECLARE
   i char(1);
   BEGIN
   i := 'A';
   DBMS_OUTPUT.PUT_LINE('Value of I is: ' || i );
   END; /

Q: What is the difference between procedures and functions?

A: Functions can return a value, whereas procedures cannot. The function value is
      returned through the use of the RETURN command. Functions can be used as part
      of an expression.

Q: Explain stored procedures and stored functions.

A: The format of stored procedures or stored functions is similar to regular procedures
      and functions, except that it is stored in the database. Here's an example:
   CREATE OR REPLACE PROCEDURE dept_task_proc(dept_id number, task_id number)
      AS
   BEGIN
   IF (dept_id 5) AND (task_id 3) THEN
   UPDATE dept_task_tbl
   SET task_hours := task_hours + 8
   WHERE dept_no = dept_id
   AND task_no = task_id;
   END; /

Q: What are the advantages of stored procedures and functions?

A: It provides consistency, security, easy maintenance, and better performance. It is
       centrally located in the database, views the source code through the data
       dictionary, and reduces network traffic.

Q: How do you invoke a procedure?

A: You can call a procedure in SQL*PLUS by using the execute command. Its syntax is as
      follows:
   SQL execute procedure_name(parameter1,..., ...);
   Here's an example:
   SQL execute dept_task_proc(dept_id, task_id);

Q: How do you recompile a stored procedure?

A: You can use the CREATE OR REPLACE PROCEDURE command. You can also use the
      ALTER PROCEDURE procedure_name COMPILE command.

Q: How do you recompile a stored function?

A: You can use the CREATE OR REPLACE FUNCTION command. You can also use the
      ALTER FUNCTION function_name COMPILE command.

Q: If a compilation fails, how can you see the errors?

A: You can see the errors using the SHOW ERRORS command. You can also use the
      USER_ERRORS table to display the errors.

Q: How do you view the source code of the stored procedure or function?

A: You can use the USER_SOURCE table to view the source code of all stored objects.
      Here's an example:
   SELECT text
   FROM user_source
   WHERE name = object_name




Q: What are the argument qualifiers in procedures or functions?

A: Three argument qualifiers are used for procedures and functions.
       IN When calling a procedure, the procedure takes in the value of the IN
          argument.
       OUT It sends the value back to the caller through the OUT argument.
       IN OUT It does both.

Q: What is overloading?

A: Overloading is an object-oriented technique. You can have the same procedure name
      but with different arguments that have different data types:
   dept_task_proc(task_rate number);
   dept_task_proc(10.75);
   dept_task_proc(task_hours integer);
   dept_task_proc(8);
   In this example, you can see the same procedure names with different data types of
       different parameters.
Q: What is a package?

A: A package is a group of objects, such as procedures, functions, variables, cursors,
      and exceptions. A package usually consists of two components: a specification
      and a body. The specification component has the declaration of variables, cursors,
      procedures, functions, and exceptions. The body component has the definition of
      the declared elements and it implements the specification component. An
      example of an package specification is as follows:
   CREATE OR REPLACE PACKAGE dept_pkg_spec AS
   dept_min NUMBER;
   dept_max NUMBER;
   CURSOR dept_cur IS
   SELECT dept_no, dept_name
   FROM department
   WHERE dept_no IS NOT NULL;
   dept_var dept_cur%ROWTYPE;
   PROCEDURE dept_task_proc(dept_id number, task_id number);
   END dept_pkg_spec;
   Here's an example of a package body:
   CREATE OR REPLACE PACKAGE BODY dept_pkg IS
   PROCEDURE dept_task_proc(dept_id number, task_id number);
   BEGIN
   IF (dept_id 5) AND (task_id 3) THEN
   UPDATE dept_task_tbl
   SET task_hours := task_hours + 8
   WHERE dept_no = dept_id
   AND task_no = task_id;
   END;
   END dept_pkg;

Q: How do you recompile a package?

A: Packages must be recompiled after you make any changes. Package recompilation
      neither changes the package definition nor any of its objects. You can use the
      ALTER PACKAGE package_name COMPILE command. If you make an edit within
      the package body, you only need to recompile the package body and not the
      package header. If you make an edit within the package header, you need to
      recompile both the package header and body.
   If you want to compile the package body only, then you can type in the following
       command:
   ALTER PACKAGE package_name COMPILE BODY;
   If you want to compile both a package body and specification, you can type in the
       following command:
   ALTER PACKAGE package_name COMPILE PACKAGE;

Q: How do you invoke an object in a package?

A: You can invoke a procedure or function within a package. Here is its syntax:
   Package_name.object_name
   dept_pkg.dept_task_proc(dept_id, task_id);

Q: What does the %ROWCOUNT attribute do?
A: The %ROWCOUNT attribute can be used to find out the number of rows processed
      when performing a DML transaction load. It can be used for insert, update, and
      delete statements. Here's an example:
   DELETE FROM dept WHERE dept_id = 10;
   DBMS_OUTPUT.PUT_LINE('Number of rows deleted: || SQL%ROWCOUNT);

Q: What is the difference between %TYPE and %ROWTYPE?

A: The %TYPE attribute can be used for a variable to inherit the definition of a table
      column data type. Its syntax is as follows:
   variable_name table_name.column_name%TYPE;
   Here's an example:
   dept_number dept.dept_no%TYPE;
   %ROWTYPE can be used for a variable to inherit the definition of the entire table
     record:
   dept_rec dept%ROWTYPE;

Q: What is a PL/SQL record?

A: A PL/SQL record is a group of fields representing a logical structure (a table row) and
      of the type RECORD. Here's an example:
   TYPE dept_rec IS RECORD
   (dept_id INTEGER,
   dept_name VARCHAR2(20));

Q: What is a PL/SQL table? What is the difference between a database table and a PL/SQL
   table?

A: A PL/SQL table is a composite data type and an object of the type TABLE. PL/SQL
      table has at least one column and one binary integer key. It can have any number
      of records. It works like an array variable in third-generation languages. A
      PL/SQL table is not like a database table. The structure and data are all held in
      memory. Its syntax is as follows:
   TYPE type_name IS TABLE OF table_name.column_name%TYPE
   INDEX BY BINARY_INTEGER;

Q: How do you insert and update a row in a PL/SQL table?

A: It's simple. You have to use an array-like syntax to reference a row in a PL/SQL
       table. The following example shows how to insert or update a row numbered 45
       in PL/SQL:
   dept_name(45) := 'Procurement';

Q: How do you delete a row in a PL/SQL table?

A: The following example can be used to delete a row numbered 45 in PL/SQL:
   dept_name(45);

Q: What is a database trigger?

A: A database trigger, stored in a table, can be fired when some DML event occurs to
      that table. A trigger can be fired by the database when insert, update, and delete
      actions take place. Twelve types of triggers are available in PL/SQL:
   BEFORE INSERT ROW
   AFTER INSERT ROW
   BEFORE INSERT STATEMENT
   AFTER INSERT STATEMENT
   BEFORE UPDATE ROW
   AFTER UPDATE ROW
   BEFORE UPDATE STATEMENT
   AFTER UPDATE STATEMENT
   BEFORE DELETE ROW
   AFTER DELETE ROW
   BEFORE DELETE STATEMENT
   AFTER DELETE STATEMENT
   CREATE OR REPLACE TRIGGER trigger_name
   { BEFORE | AFTER | INSTEAD OF }
   { DELETE | UPDATE | INSERT [ of column list ]} ON table_name
   FOR EACH ROW [ WHEN condition true]
   PL/SQL block

Q: What is the difference between a row-level trigger and a statement-level trigger?

A: A row-level trigger executes once for each row in a transaction, whereas a
       statement-level trigger executes once for each transaction.



Q: What is a mutating table error?

A: A mutating table error occurs when a trigger modifies the contents of the same table
      where the trigger is defined on.

Q: How can you view the source code of database triggers?

A: No direct command in PL/SQL enables the viewing of the database triggers' source
      code, but you can view the information by querying the USER_TRIGGERS table.
      You can view the information from these three columns: DESCRIPTION,
      WHEN_CLAUSE, and TRIGGER__BODY, based on TRIGGER_NAME.

Q: Can you disable the trigger? If you can, when do you disable the trigger?

A: Yes, triggers can be enabled or disabled based on the context. You can use the
      following syntax to enable or disable a trigger.
   ALTER TRIGGER trigger_name {ENABLED | DISABLED}
   It can be useful to disable the triggers when loading the data.

Q: What is an exception?

A: An exception is an error that occurs during run time. When an error is encountered,
      the program control automatically goes to an error-handling section. This section
      always starts with an EXCEPTION reserved word and terminates with the END
      command.

Q: What are the Oracle predefined exception errors?

A: The commonly used predefined exception errors are as follows:
       TOO_MANY_ROWS returns more than one row, rather than a single row.
       NO_DATA_FOUND returns no data.
       ZERO_DIVIDE tries to divide by zero.
       INVALID_NUMBER is used when a number-to-string conversion fails.
       INVALID_CURSOR is an illegal cursor operation.
       VALUE_ERROR is when a truncation, conversion, or arithmetic error occurred.
       DUP_VAL_ON_INDEX tries to insert duplicate information on the unique
           columns.

Q: Which exception traps all the errors?

A: You can use an OTHERS clause to trap all the exception errors other than the defined
      ones.

Q: What is a pragma?

A: A pragma is a compiler directive and is used to handle the undefined oracle errors.
      Its syntax is as follows:
     PRAGMA exception_init (exception_name, error_no);
     You must define the pragmas in the declaration section of a PL/SQL block.

Q: What is a transaction?

A: A transaction is a logical unit of work. In Oracle, two types of transactions exist:
       commit and rollback. Commit submits the transaction to the database, while
       rollback works like an undo command.

Q: What are the main transaction statements?

A: As stated earlier, these consist of COMMIT, which submits a transaction, and
       ROLLBACK, which cancels a transaction.

Q: What is SAVEPOINT?

A: SAVEPOINT is an intermediate point within a transaction to which you can rollback.

Q: How do you perform row-level locking?
A: Row-level locking can be performed using the FOR UPDATE clause in a select statement.
   Here's an example:
SELECT dept_no, dept_name
FROM dept
WHERE dept_no = 10
FOR UPDATE;
Q: How do you perform table-level locking?

A: Table locks can be performed using the following five different modes: exclusive,
      share lock, row share, row exclusive, and share row exclusive.

Q:    What is the default order when an index is created?
A: The default order of an index is ascending order, as in A, B, C.
Q:    What is the purpose of the dual table?
A: The dual table comes with Oracle and is owned by the user SYS. The dual table can be
   used to run SQL queries, such as "Select SYSDATE from Dual"

Beginning Skills
Q:    Can you describe Oracle?
A: Oracle is an object-relational database management system that stores data in a
   database. An Oracle database consists of logical and physical structures. For example, a
   tablespace is used to logically define the physical storage of data in datafiles. Oracle
   data is stored in related tables and SQL is utilized to retrieve information from the
   tables.
Q:    In the command, "Connect Scott/Tiger@ORCL," the @ORCL is needed for what
     purpose?
A: @ORCL, a host string, determines what database instance the user wishes to access. The
   statement will connect the user, Scott, to the database associated with the ORCL
   instance.
Q:   W Will the database start if the control file is missing?
A: The database will not start without the control file. The control file contains ncecessary
   datafile information and transaction control numbers.
Q:   How is SQL*Plus started?
A: Sqlplus <username><password.
Q:   What is a sequence?
A: A sequence is used to provid n
e a sequential unique number. A sequence will provide a numeric value increased by a
    specified increment. An example of a sequence follows:
CREATE SEQUENCE Orderno_Seq
   START WITH 1
   INCREMENT BY 1
   NOMAX VALUE
   NOCYCLE
   CACHE 20;
The preceding statement will do the following:
A sequence is referenced with the NEXTVAL and CURRVAL pseudocolumns. The NEXTVAL
   generates a new sequence number. The next example illustrates how to reference a
   sequence.
INSERT INTO PURCHASES(Orderno, Customer)
   VALUES(Orderno_seq.NEXTVAL,7021);

START WITH 1:              Sets the starting point of the sequence

INCREMENT BY 1:            Specifies the value by which the next sequence value should
                           be increased

NOMAXVALUE:                Sets no maximum value for the sequence

NOCYCLE:                   Indicates that the sequence cannot generate additional values
                           after reaching a maximum value that has been set

CACHE 20:                  Pre-allocates 20 sequence numbers to memory for faster
                           retrieval

Q:   What is SQL*Plus?
A: SQL*Plus, an Oracle tool, is an extension of SQL. SQL* Plus is used to connect to an
   Oracle database. The user can also use the tool to process SQL queries.
Q:   What is Net8?
A: Net 8 is used to establish network connections and to transfer data based on networking
   protocols such as TCP/IP. Net 8 allows clients to connect to remote Oracle databases
   residing on multiple servers.
Q:   If I need to add data to a table, which SQL command should I use?
A: The SQL command used to add data to a table is "insert." Here's an example:
Insert into employees(first_name,last_name)
   VALUES "Donna", "Matthews";
Q:   What is the difference between an index created by ASC and an index created by DESC?
A: The difference is that ASC will create an index with ascending values, such as A, B, C.
   The DESC index will create an index with descending values, such as C, B, A.
Q:   What is a join?
A: A join is used to link Oracle tables together through a key field, usually handled by a
   where condition in the SQL statement. Here's an example:
Select * From Employee, Department where Employee.Deptno= Department Deptno.
   Additional joins also exist:
Inner Join: An inner join will only return the rows where matches were found
Outer Join: An outer join will return all rows, including rows where a match does not exist.
Union: A union is the opposite of an inner join and returns those rows where no match was
   found between the tables.


Q:   What is a trigger?
A: A trigger is a procedure that is executed when a specific event occurs, such as when a
   table is inserted, updated, or deleted.
Q:   What is a view?
A: A view is an overlay for tables. Views and tables are queried and accessed in the same
   way as a table. Views make it possible to hide the actual name of a table as well as
   fields that a user should not access.
Q:   What is a procedure?
A: A procedure is a block of PL/SQL statements that is called by applications. A procedure
   allows the user to store frequently used commands for easy access later.
Q:   What does the distinct clause in SQL statements do?
A: The distinct clause in an SQL statement will remove any duplicate values. For example, if
   two last_name values of Matthews exist in a table, only one will be displayed.
Q:   Which one must return a value, a procedure, or a function?
A: A function must return a value and a procedure may never return a value.
Q:   What is the purpose of the HAVING clause in SQL?
A: The HAVING clause restricts the group of rows returned. It is similar to the WHERE
   clause, except that it is employed when a GROUP_BY clause has been used in the SQL
   query. Here's an example:
SELECT snum, state, AVG(amount), MAX(amount)
   From salesrep
   GROUP BY snum
   HAVING state = 'IL';
Q:   In a relational database, which one of the following describes an attribute?
A. Field
B. Record
C. Database
D. None of the above
A: The correct answer is A. A record is a combination of fields or attributes. A database is a
   combination of records.
Q:   Which one of the programming languages below is used in relational databases to
     manipulate the data?
A. Visual Basic
B. C++
C. SQL
D. None of the above
A: The correct answer is C. SQL is used to manipulate the data in relational databases.
   Answer A is incorrect because Visual Basic is usually a front-end to the database, but
   SQL is used to manipulate the data. Answer B is also incorrect because C++ must use
   SQL to manipulate the data.
Q:   What is SQL?
A: SQL stands for structured query language, which is used to access Oracle databases.
Q:   Oracle8i does not support Java programming.
A. True
B. False
A: The correct answer is B. Oracle8i supports Java programming wherever PL/SQL is used.
Java programming can be used for stored procedures and functions, as well as for triggers.
Q:   _____ are queries based on tables.
A. Views
B. Sequences
C. Indexes
None of the above
A: The correct answer is A. B is incorrect because sequences generate unique numbers for
   numeric columns in a table. C is incorrect because indexes are used to speed up the
   access of data from a database.
Q:    Given the tables "Employee_Information" and "Employee_Resume," what type of
     relationship do you think exists?
A. One-to-one
B. Many-to-many
C. One-to-many
A: The correct answer is A. Each employee should have only one resume.
Q:   What does the SQL statement "Select * From Employees where state = 'IL';" do?
A: This statement will display all the fields in the employee table where an employee is
   located in the state of Illinois.
Q:   What is the purpose of an index?
A: An index is used to store data in a specific way in a table which will permit easy retrieval
   of data. An index to a database is similar to an index in a book; it allows the user to
   immediately access the information he or she is seeking without having to read every
   page. Indexes sort one or more fields in a database in ascending or descending order.
Q:   How can a user create a table?
A: The SQL statement "Create table" is used to create a table in Oracle. An example of the
   statement follows:
     CREATE TABLE students (
     Student_id NUMBER(5) NOT NULL;
     Department CHAR(3) NOT NULL);


Q:   How does a user create an index?
A: The SQL statement "Create index" is used to create an index in Oracle. The syntax is
   "Create Index "ORCL.employees (empid);" is an example of a create statement.
   <indexname> on <table_name (column_name(s)>.
Q:   What is data normalization?
A: The goal of data normalization is to eliminate redundant data in tables. For example, in a
   payroll table where the hourly rate of $60 per hour is stored in a new field for each and
   every supervisor, a table can be created that is used to retrieve the hourly rate by the
   use of a join. This configuration will allow changes to be made once rather than in
   multiple locations for all supervisors in the table.
Q:   Can you give an example of a one-to-one relationship?
A: The relationship between an employee table and an employee resume table, where each
   employee has only one resume, illustrates a one-to-one relationship.
Q:   Can you give an example of a one-to-many relationship?
A: An example of a one-to-many relationship can be illustrated by the relationship between
   sales_reps and sales offices. Sales_reps can report to only one office, but an office can
   have more than one sales rep.
Q:   What does the command "Drop Table Employees" do?
A: The drop table command is used to delete the employee table and to remove information
   about it from the data dictionary.
Q:   What is the purpose of an Entity Relationship Diagram (ERD)?
A: An ERD is used to graphically depict the relationship between tables in an Oracle
   database.
Q:   What is PL/SQL?
A: PL/SQL is a programming language used to access Oracle databases. PL/SQL enhances
   the SQL programming language with additional programming capabilities, such as if-
   then statements, functions, procedures, and so on.
Q:   In a SQL statement, what is the purpose of the where clause?
A: The where clause is used to restrict the data returned from a SQL query.
Q:    If the department name for Human Resources were changed to HR in a Departments
     table, which SQL command would be used to update this information?
A: The SQL command update would be used to change the department name. The syntax
   would be:
UPDATE Departments
   Set Dept_Name = "HR"
   Where Dept_Name = "Human Resources";
Q:   Based on the previous question, what problem would occur during the update if the
     Dept_Name Human Resources were not used consistently in the Departments table?
     How could this problem be avoided?
A: The problem would be that not all of the occurrences of Human Resources would be
   updated. This problem could be avoided by creating another table which consists of
   department_code and department_name. The department_code could be used in a
   where condition to retrieve the department name. With this configuration, an update
   would need to be done in only one place.
Q:   The SQL statement,
Select First_name, Last_Name from Employee
   Where Last_Name = "Matthews";
would accomplish what task?
A: This statement would retrieve data in the fields First_name and Last_name where the
   value in the field Last_name is "Matthews."
Q:    The SQL statement, Select First_name, Last_Name from Employee; would retrieve what
     from the employee table?
A: This SQL statement will retrieve first name and last name data from the employee table.
Q:   Can you describe a primary key?
A: A primary key is used to identify rows in a table. An example of a primary key is a social
   security number in an employee table.
Q:   Describe a foreign key in a database.
A: Foreign keys refer to a primary key in another table. For example, the social security
   number in the payroll table can be used to refer to the social security number in the
   employee tables.
Q:   What is the purpose of the order clause?
A: The order clause is used to sort data by a specific field in a table.
Q:   What are some of the advantages relational databases offer?
A: Some advantages are: improved data integrity, reduced data redundancy, easier data
   retrieval, and easier data updates.
Q:   What does the command "commit" do?
A: Commit makes changes permanent.
Q:   What does the command "rollback" do, and can it be issued after a commit?
A: The rollback command will return a table to the state it was in before an insert, update,
   or delete was issued. A rollback can be issued after a commit; however, the changes will
   not be removed as they will already have been saved in the database.
Q:   What does the command "group by" do in an SQL statement?
A: The group by statement will group the output over identical values. It is used with
   aggregate functions such as average. A group by statement can be used to retrieve an
   average for all 50 states from a table.
Q:   What will the command "Select LENGTH('Donna') from dual;" return?
A: The command will return the number five for length of 'Donna.'
Q:   What will the command "SELECT substr('DONNA',1,3) from dual;" return?
A: This SQL query will return 'DON.' The command retrieves the data starting at the first
   position and retrieves three characters.
Q:   What will the command "SELECT UPPER('donna') from dual;" return?
A: The command will return 'DONNA.' The function UPPER converts the text from lower
   case to uppercase.
Q:   "Select count(*) from students;" will retrieve what data from Oracle?
A: The select count statement will return the total number of records in the student table.
Q:   What will the command "v_numberseats Number:=45;" do?
A: This command will create a variable named "v_numberseats" with a datatype of Number
   and set the value of the variable to 45.
Q:   Is there a difference between the two commands that follow?:
DECLARE v_firstname, v_lastname varchar2(20); And
Declare V_firstname varchar2(20);
        V_lastname varchar2(20);
A: Both statements are the same. The only difference between the two is visual.




Intermediate Skills
Q:   The init.ora file is only used when an instance is stopped.
A. True
B. False
A: The correct answer is B. The init.ora file is used at instance startup to configure the
   parameters and settings for the instance.
Q:   Which of the following is used to configure an instance when it starts?
     A. redo log files
     B. control files
     C. tables
     D. init.ora files
A: The correct answer is D. The init.ora file is used at instance startup to configure the
   parameters and settings for the instance. Redo log files are used to store changes to
   data. A setting in the init.ora file configures the control file. Tables are accessed only
   after an instance is started and the database is accessed.
Q:    The DBWn background process is used to write data from the database buffer to the
     redo log files; true or false?
     A. True
     B. False
A: False. The DBWR (Database Writer) is used to write data from the database buffer to the
   database files. LGWR (Log Writer) is used to write data from the Log Buffer to the redo
   log files.
Q:   Which is not a background process?
     A. DBWR
     B. PMON
     C. LGWR
     D. SGA
A: D is the correct answer. A, B, and C are all components of the background process.
Q:   Increasing the size of the data block parameter in the init.ora file will have what impact
     on queries?
A: Increasing the size of the data block parameter will speed up the execution of queries.
Q:   If a match is found in the shared pool for a SQL statement, what will happen?
A: If this occurs, the SQL statement will not need to be executed. Oracle will use the
   execution path information from the SQL statement in the shared pool.
Q:    A new service name is created, but the Oracle DBA did not add the entry to the
     listener.ora file. Will the user be able to make a connection? If not, why?
A: The user will not be able to make a connection because the listener.ora file is used to
   establish the services on the server.
Q:    Will typing "connect scott/tiger&orcl" in server manager enable the user to connect to
     the server from a client machine?
A: The answer is no. The correct syntax for establishing a connection is connect
   "username/password@service" name.
Q:    If the listener service is not running on the server, what will happen when a user tries
     to connect to the service name?
A: The listener must be running in order for a user to connect to the service name. The
   listener is used to process a user's request and route the request to the proper
   database.
Q:   What is the effect if the background_dump_dest parameter in the init.ora file is
     changed from c:\oracle to c:\temp?
A: The log files will be written to the c:\temp directory instead of the c:\oracle directory.
Q:   Is the syntax "CREATE USER demo IDENTIFY by test;" correct for creating a user?
A: The syntax is incorrect. The word IDENTIFIED should be used in place of the word
   IDENTIFY.
Q:   What impact will it have if the user does not install the sample (starter) database?
A: It will have no impact at all. The installation of the starter database is used as a guide to
   setting up an Oracle database.
Q:   What is the purpose of the SQL*Loader?
A: The SQL*Loader is an Oracle tool that allows the user to load non-Oracle data into a
   database. It is commonly used when loading an ASCII flat file, which is delimited or
   non-delimited into Oracle.
Q:    In an Oracle database, should all of the database objects be maintained in the SYSTEM
     tablespace? Why or why not?
A: The system tablespace should be reserved for Oracle's use only. The data dictionary is
   maintained in the system tablespace. Additional tablespaces should be created for a
   new database.
Q:    Which of the following is used to ensure that the data meets a specific condition before
     it is inserted, deleted, or edited?
A. Primary Key
B. Not Null
C. Check
A: The correct answer is C. The primary key is used to set the key that the table
   information should use as a unique key (for example, a social security number in an
   employee table). A not null constraint is used to disallow null data in a field.
Q:   What is a package?
A: A package is used to store procedures and functions in one place.
Q:   What are the two components of the package?
A: A package consists of a package specification and a package body.
Q:   The command "sysdate" returns what?
A: This command returns the computer's system date.
Q:   What is the purpose of the redo logs?
A: Redo logs record all of the changes made to a database. This information is used to
   perform rollbacks, which are used if it is necessary to recover data, and to help maintain
   read-consistency of data.
Q:   What is the purpose of the control file?
A: The control files contain information about the database, such as datafiles, and other
   physical architecture information about the database.
Q:   How does import/export work in Oracle?
A: Export reads the database and writes the output to a binary file, which is called an
   export dump file. The import utility is used to import the exported file back into Oracle.
   Exports are available for full, incremental, and cumulative database export. The export
   can be done while the database is up and running.
Q:   What is a tablespace?
A: A tablespace is a logical division of a database. Each database must at least have a
   system tablespace.
Q:   What is a datafile?
A: Datafiles physically divide a database. They give, for example, the actual name and
   location of files used in an Oracle database.
Q:   Describe the relationship between the tablespace and datafiles.
A: The system tablespace, made of the datafiles c:\system.dtf, illustrates the relationship.
   The tablespace is the physical name and the datafiles are the actual physical files. The
   tablespace can consist of one or more datafiles. As long as the system tablespace name
   stays the same, an Oracle DBA can make changes to the datafiles associated with the
   tablespace.
Q:   What is the difference between the truncate table and delete statements?
A: Both truncate and delete remove data from a database table. However, truncate also
   resets the storage to the initial setting.
Q:   What is a cluster?
A: A cluster is used to store tables that are frequently accessed together. Clusters enable
   better query performance.
Q:   What is the purpose of the init*.ora file?
A: The init*.ora file contains the parameters for the Oracle database which that database
   should use. Some of the parameters in the init*.ora file are DB blocks.
Q:   What is the purpose of the Oracle Application Server?
A: The Oracle Application Server is a Web server that is available from Oracle. The Oracle
   Application Server also allows you to easily access Oracle packages.
Q:   What is the purpose of the LGWR?
A: The LGWR is a background process that handles the writing of the redo log buffer to the
   online redo log files.
Q:    How many tablespaces are required in Oracle and what is the name of any mandatory
     tablespace?
A: Only one tablespace is required in an Oracle database, the system tablespace.
Q:   Is it a good idea to store everything in the system tablespace?
A: No, the system tablespace should be reserved for Oracle's use to avoid corruption of the
   Oracle database.
Q:   What occurs during a CKPT in Oracle?
A: Data is written to the datafiles and redo log files during a CKPT.
Q:   What is contained in the alert log and how often should it be checked?
A: The alert log records the commands and command results of major events in the
   database, such as tablespace creation. The alert log should be checked daily to see if
   any problems are occurring with the database.
Q:   What is the purpose of the DBWn?
A: The DBWn is the background process that handles writing data from the database
   buffers to the datafiles.


Q:   What does the not null constraint do on a field?
A: The not null constraint ensures that the field is not left empty. Data must be entered in
   any field that has been assigned a not null constraint.
Q:   What is an instance?
A: An Oracle instance is used to access the data in the database. The parameters are set in
   the init*.ora file. An instance that could be used to connect to the database is "Connect
   Scott/Tiger@ORCL." ORCL is the name of the instance.
Q:   What does the describe command do?
 A: The describe command will provide information about an Oracle object. For example,
   "Describe employees" will provide information on the employee fields and their
   datatypes in the table.
Q:   What command is used to create a user?
A: The command is as follows:
     Create user Rochelle Identified by "Tasha"
     This command will create a user "Rochelle" with the assigned password "Tasha."
Q:   What is the purpose of the default constraint?
A: The default constraint sets a default value if no data is inserted in a column.
Q:   What occurs during shutdown abort?
A: The shutdown abort command immediately stops the Oracle database and disconnects
   all connected users without completing any current transactions being performed.
Q:   What occurs during startup nomount?
A: A database is created.
Q:   What is the purpose of the check constraint?
A: The check constraint's purpose is to ensure that values in a specified column meet a
   certain criteria. It might be used, for example, in a situation where the salary value
   must be less than $200,000.
Q:   What is a database link?
A: A database link allows the user to access remote data without providing the fully
   qualified name of the remote object. This is done by creating public database link
   cur_link and connecting to an employee identified by matthews using 'TEMP.'
Then, once the user needs to connect to the table the command "Select * from
   employee@cur_link would retrieve the data.
Q:   If you own the table Employees, what command grants user Rob the ability to select
     data?
A: The command "Grant Select on employees to Rob;" gives him access.
Q:   What is containe0d under a user's schema in Oracle?
A: The user's schema will contain a set of objects owned by that user, such as tables,
   procedures, and so on.
Q:   What is the purpose of Oracle Designer?
A: Oracle Designer allows a user to develop applications quickly. Oracle Designer provides
   a Rapid Application Development (RAD) environment to model and generate Data
   Definition Language (DDL), client-server, and Web-based applications.
Q:   What is Oracle WebDB?
A: Oracle WebDB is a software package that enables users to easily develop Web-based
   applications from Oracle databases.
Q:   What is a synonym?
A: A synonym is used to completely identify a database object, such as a table, in a
   distributed database. The command is "Create public synonym employee for HR.
   employee;"
Q:   What will the command "drop user ORCL cascade" do?
A: A user normally cannot be dropped if it owns objects; however, if cascade were added, it
   would drop the user and all the objects associated with the user.
Q:   The following statement, "Create user matthews identified by MATTHEWS,"
     accomplishes what task?
A: This command will create the user matthews with a password of MATTHEWS.
Q:   The command "Alter user matthews default tablespace test" will do what?
A: The command will set the default tablespace for the user matthews to "test." Therefore,
   whenever the user matthews executes a SQL command that requires storage but does
   not specify a tablespace, the tablespace "test" will be used.
Q:   The "Grant insert on employees to PUBLIC" command will achieve what end?
A: This command will give all users who have access to the database the ability to insert
   data into the employee table.



Advanced Skills
Q:   Which Oracle feature contains information about the database, including a time stamp
     of data creation?
     A. None of the following
     B. Datafiles
     C. Tablespaces
     D. Control Files
A: The correct answer is D. The datafiles contain the data contained in the database.
   Tablespaces are the logical name given to datafiles.
Q:   Execution plans for SQL statements are maintained in the:
     A. Database buffer
     B. Redo logs
     C. Shared pool
     D. None of the above
A: The correct answer is C, the shared pool. The database buffer contains only recently
   accessed data. The redo logs contain changes to the database.
Q:    The datafile users.dtf, which is part of the users tablespace, is accidentally deleted. Will
     the database still start? Explain.
A: The database will not start without the users.dtf. At instance startup, the controlfile is
   used to check for the database structure information. Because the users.dtf is missing,
   the users tablespace will be invalid.
Q:    The Oracle database contains a tablespace called users, which consists of the datafile
     c:\oracle\database\users.dtf. The DBA decides to add a new datafile called users2.dtf
     to the tablespace. Will this change cause the developer to recreate all of the objects that
     are currently configured to the user's tablespace?
A: This change will have no effect on either existing objects under the tablespace or on any
   other development tasks. Oracle utilizes a database independence environment. This
   means that the changes to the physical structure (datafiles) have no impact on the
   logical structure (tablespaces). The changes are transparent to both the user and
   developer.
Q:   A media failure has occurred while data entry is being done. Which of the following can
     be used to recover the data that has not been written yet?
     A. Redo log
     B. Control file
     C. Tablespaces


A: The correct answer is A. The Redo log file contains information on data changes. B is
   incorrect because the control file is used to provide information only on the database
   structure. C is incorrect because tablespaces give the logical names assigned to
   datafiles in Oracle.
Q:   If the background process DBWn does not start, will the instance start?
A: No, the instance will not start without the DBWn process. DBWn is used to write
   modified data from the database buffer cache in the shared global area to the datafiles.
Q:   Explain the purpose of the LGWR background process.
A: The purpose of the LGWR is to manage the contents of the redo log buffer, contained in
   the shared global area, and of the online redo log files. LGWR writes log entries to the
   redo log files.
Q:   What is the purpose of the DBWn background process?
A: The DBWn background process manages the contents of the data buffer cache. The
   DBWn performs writes of changed data to the datafiles.
Q:    The network administrator has decided to change the name of the server to ORATEST
     and has also changed the IP address without the user's knowledge. When the user tries
     to make a connection to the server, will it work?
A: No. The connection will not work unless the names files are also updated. For example,
   if the user gains a connection by use of a tnsnames.ora file, the entry will reference the
   old server name or IP address, which no longer exists and the connection will fail. This
   example shows the need for the Oracle DBA and network administrators to
   communicate with each other about any system changes.
Q:   In the tnsnames.ora file, if the SERVICE_NAME=orcl is changed to
     SERVICE_NAME=dept and a user then tries to connect to the orcl service name, what
     will happen?
A: The connection will not work because the tnsnames.ora file will not have an entry for
   the orcl instance. If the user wants to add the department service name, the entire
   section for the orcl must be duplicated and the occurences of orcl replaced with dept.
Q:   In the listener.ora file, which section contains information about the service names?
     A. Description
     B. Connect data
     C. Host
A: The correct answer is B, the connect data section. The description section contains
   information about the server. The host is a component of the description section, which
   provides information on the server where Oracle resides.
Q:    Sending multiple sessions to the server at one time is handled by which of the
     following:
     A. Connection manager
     B. Tnsnames.ora
     C. Oracle names
A: The correct answer is A. B is incorrect because the tnsnames.ora file exists on client
   machines to provide information on the services and server residing on the Oracle
   server. Answer C is incorrect because Oracle names is a method used to allow for client
   connections.
Q:   Which option would be used to shut down a database without waiting for users to
     disconnect and which does not roll back uncommitted transactions?
     A. Normal
     B. Immediate
     C. Abort
A: The correct answer is C, abort. A normal shutdown waits for all currently connected
   users to terminate their sessions. An immediate shutdown terminates currently
   executing SQL queries and rolls back uncommitted transactions.
Q:   Which backup can only be performed if the database is in archivelog mode?
     A. Offline backup
     B. Online backup
     C. Export
A: The correct answer is B, online backup. A and C are incorrect because these backups can
   be performed even if the database is not in archivelog mode.
Q:   Which one of the following is used to create the data dictionary?
     A. Catalog.sql
     B. Init.ora
     C. LGWR
     D. DBWn
A: The correct answer is A. The init.ora file is read at instance startup to set parameters.
   The LGWR is a background process that handles writing information to the redo log
   files. D is incorrect because the DBWn is a background process that writes changes to
   physical datafiles.
Q:   Describe a cold backup in Oracle?
A: A cold backup requires that the database be taken down. All files associated with the
   database are then copied.
Q:   What does maxinstances do in the create database statement?
A: Maxinstances, in the create database statement, sets the maximum number of instances
   that can be connected to an Oracle database at one time.
Q:   What exactly are extents?
A: Extents are continuous sets of Oracle blocks of space. If possible, all of one object
   should be maintained in one extent.
Q:   Describe a hot backup?
A: A hot backup occurs while the Oracle database is running. In order for a hot backup to
   be done, the Oracle database must have the ARCHIVELOG option set. The ARCHIVELOG
   option is set with the command "alter database archcivelog;." This process will make a
   copy of the redo log files before they are overwritten. Tablespaces are then taken into
   backup state, datafiles are backed, and then the tablespaces are returned to an online
   status.
Q:   What is the purpose of the plan table?
A: The purpose of the plan table is to provide the execution path for SQL statements.
Q:   What does the archive feature do and how is it set?
A: The ARCHIVELOG option is set with the command "alter database archivelog;." This
   process will make a copy of the redo log files before they are overwritten.
Q:   What is the purpose of the tnsnames.ora files?
A: The tnsnames.ora files contain information on such Oracle instances as names, Network
   protocols, and IP addresses. This file is used to provide information to connect to Oracle
   databases.
Q:   What is a role and how is it associated with a user?
A: A role is used to distribute Oracle database privileges. Dba and connect are examples of
   some of the roles.
Q:   What is a profile?
A: A profile is used to place limits on the system and database resources available to a
   user.
Q:   Can the database block size be changed after a database is created?
A: No, the only way the database blocks can be resized is if the database is recreated.
Q:   What does the statement "Analyze table employee computer statistics" do?
A: The command creates information in the form of indexes, such as on the employee
   computer statistics table in Oracle. This information can be used by Oracle to determine
   the best execution path for SQL queries.
Q:   What are rollback segments?
A: Rollback segments are used within a database to construct a before image for
   uncommitted transactions and are used to roll back data when a rollback command is
   issued.
Q:   Name a few of the parameters that can be set in the init.ora file?
A: Two parameters that can be set are OPTIMIZATION_MODE and DB_BLOCK_SIZE.
Q:   What is the difference between privileges and a role?
A: Privileges are the Oracle commands given to a user, such as "Create Table." Roles
   consist of privileges and give the DBA a way to easily assign the same privileges to all
   users with a specific job role.
Q:   What command is used to change a user's password?
A: The command "alter user" can be used to change a user's password.
Q:   When the DBA sets privileges for a user and adds the command "with grant option,"
     how are the user's privileges altered?
A: The with grant option clause of the grant command is used to give the grantee the
   ability to grant privileges to other users.
Q:   Can tablespaces be taken offline?
A: Yes, tablespaces can be taken offline; this is done when a hot backup is needed.
Q:   What does the command "Alter tablespace coalesce" do and why is this needed?
A: This command will develop free spaces in tablespaces into larger extents. This will allow
   larger objects to fit in one extent.
Q:   What does the initial command do in create tablespace commands?
A: The initial command sets the number of extents that are initially created for a
   tablespace.
Q:   What is the purpose of the Shared SQL Pool?
A: The Shared SQL Pool stores information on SQL commands that have been submitted
   and their execution paths. When a new SQL command is issued, the Shared SQL Pool is
   checked for a match and the execution path from the Shared SQL Pool is used.
Q:   What will the command "DROP Table" do?
A: This command will delete the table from the Oracle database and all associated indexes
   from the table.
Advanced for Advanced Questions
Q:   How do you tune the PL/SQL statements?
A: You can tune the SQL or PL/SQL statements with the following checklist:
     1   Examine the indexes.
     2   Examine the optimization rules. Cost-based optimization is best.
     3   Set the SQL TRACE session to display the statistics.
     4   Use the EXPLAIN PLAN mechanism.
     5   Use the SET AUTOTRACE function in SQL*Plus to automatically see the EXPLAIN
         PLANS.
     6   Use ROWID to speed up the query processing.
     7   Tune subqueries, if necessary.
     8   Use explicit cursors as much as you can.
     9   Specify the driving table.
     10 Use hints wherever needed.
     11 Use PL/SQL tables to improve performance.
     12 Monitor the shared pool usage.
Q:   Which PL/SQL packages support I/O processes?
A: The following are widely used packages for I/O processes:
     1   The DBMS_OUTPUT package
     2   The TEXT_IO package
     3   The UTL_FILE package
Q:   What is the difference between the DBMS_OUTPUT package and the UTL_FILE package?
A: DBMS_OUTPUT can be used for standard output, which means writing it to a screen,
   whereas the UTIL_FILE package can be used to handle the file I/O processing.
Q:   What are the steps for defining a process for file reading?
A: Reading the input from a file consists of four main steps. File I/O processing in PL/SQL
   is similar to the one in C language:
     1   Declare a file handler using the UTIL_FILE package.
     2   Open a file with FOPEN.
     3   Read a line from the file using the GET_LINE function.
     4   Close the file using FCLOSE.
     The syntax is as follows:
     DECLARE
     file_handler_variable UTIL_FILE.FILE_TYPE;
     BEGIN
     file_handler_variable := UTL_FILE.FOPEN(file_      location, file_name,
           access_mode);
     GET_LINE(file_handler_variable, buffer_text);
     FCLOSE(file_handler_variable);
     END;
     Access mode should be "r" for file input.
Q:   What are the steps for defining a process for file writing?
A: Writing the output to a file consists of four main steps. File I/O processing in PL/SQL is
   similar to the one in C:
     1     Declare a file handler using the UTIL_FILE package.
     2     Open a file with FOPEN.
     3     Read a line from the file using the PUT_LINE function.
     4     Close the file using FCLOSE.
     Its syntax is as follows:
     DECLARE
     file_handler_variable UTIL_FILE.FILE_TYPE;
     BEGIN
     file_handler_variable := UTL_FILE.FOPEN(file_       location, file_name,
           access_mode);
     PUT_LINE(file_handler_variable, buffer_text);
     FCLOSE(file_handler_variable);
     END;
     Access modes should be either "w" for write or "a" for append.
Q:   Which function do you use to clear the buffer and flush the data to the OS file?
A: A FFLUSH function can be used to clear the buffer text and write to the OS file.
Q:   What is the difference between PUT, PUTF, and PUTLINE?
A: The PUT function just writes the output to a file. The PUTLINE function adds the newline
   character at the end of the line. The PUTF function is used for formatted output.
Q:   What is the maximum buffer size for I/O operations using the UTL_FILE package?
A: For input, the maximum size is 1,022 bytes. For output, the maximum size is 1,023
   bytes.
Q:   What is an abstract data type?
A: An abstract data type supports object-based programming. It is a new technique
   introduced in Oracle 8. It is a type of object with a class definition at the database level.
   It is also referred to as an object type.
     Here's an example:
     CREATE TYPE cust_addr_type AS OBJECT
     (addr1      VARCHAR2(30),
     addr2      VARCHAR2(30),
     city     VARCHAR2(20),
     state     VARCHAR2(2),
     zip     VARCHAR2(5));
     Let's look at the abstract data type in the following object table's creation script:
     CREATE TABLE customer(
     Customer_no NUMBER;
     Customer_name VARCHAR2(30);
     Address cust_addr_type);
Q:   What is the difference between database tables and object tables?
A: A database table is a collection of common tables and is defined based on the relational
   management. Object tables are similar to database tables, except they store objects as
   table columns. Object types are defined based on the object-oriented techniques.
Q:   What is a method?
A: A method is a collection of operations that affect the objects.
Q:   How many methods does an object have in Oracle 8?
A: Three main methods are widely used in Oracle 8:
     1     The accessor method is used to get an object's attributes.
     2   The constructor method is used to create instance of that type.
     3   The mutator method is used to set the attribute values.


Q:   In object types, which method is defined automatically by Oracle 8?
A: Constructor Method.
Q:   Can you define object types within PL/SQL procedures or functions?
A: No, it can only be defined at the database level.
Q:   What are collectors?
A: Collectors are data element sets that are considered to be part of a single record. Two
   types of collectors are available in Oracle 8: varying arrays (VARRAYs) and nested
   tables.
Q:   What is a varying array (VARRAY)?
A: VARRAYs are data element sets referenced with the same data type. VARRAYs can be
   defined based on only one column.
     Let's take an example. One order can have multiple products, and one product can have
     multiple orders. In a conventional relational fashion, you can join these two tables to
     retrieve the relevant information. You can also store the same relevant information at
     one place using VARRAYS. It can improve the performance while avoiding the
     conventional approach. The following statement creates a VARRAY called products_va:
     CREATE OR REPLACE TYPE products_va AS VARRAY(10) OF NUMBER;
     Now you can reference a VARRAY for a data element in a table:
     Create table orders
     (order_no NUMBER,
     products products_va);
Q:   When you describe VARRAY tables, how are the VARRAY elements defined?
A: You can see VARRAY elements defined as a RAW data type. Here is an example:
     SQL DESC ORDERS
     Name       TYPE
     ————————————————————
     ORDER_NO NUMBER
     PRODUCTS     RAW




Q:   How would you identify the exact data type of a VARRAY?
A: You can use USER_TAB_COLUMNS.DATA_TYPE to identify it. You can further identify the
   exact data type using USER_TYPES.TYPECODE based on the TYPE_NAME.
Q:   What is a nested table?
A: A nested table, like a nested query, is a table within a table.
Q:   What is the difference between a VARRAY and a nested table?
A: Both VARRAYs and nested tables function in the same manner with few differences.
   VARRAYs have a limitation on the number of entries per record, whereas nested tables
   have no limitations. Nested tables support a greater flexibility when querying, whereas
   varying arrays do not.
Q:   What is a LOB?
A: A LOB, a large object, is used to store huge volumes of data such as audio, video, and so
   on. It can store data up to four GB. The available four LOB data types are BLOB, CLOB,
   NCLOB, and BFILE:
     BLOB The Binary Large Object holds binary data and is stored internally.
     CLOB The Character Large Object holds character data and is stored internally.
     NCLOB The National Character Large Object holds multi-byte character sets and is
     stored               internally.
     BFILE The Binary File is a pointer to external file. It is located externally on the
     operating                     system directories.
Q:   Can you write to external files using BFILE?
A: No, you can only read from the external files.
Q:   Which function do you use for ConText queries?
A: The CONTAINS function is used to retrieve ConText queries.
Q:   Which package do you use to send event-based notifications?
A: The DBMS_ALERT package.
Q:   Which package do you use for scheduling PL/SQL jobs?
A: The DBMS_JOB package.
Q:   Which package do you use to send messages between sessions?
A: The DBMS_PIPE package.
Q:   Which package do you use to create dynamic SQL or PL/SQL scripts?
A: The DBMS_SQL package.
Q:   Which package do you use for various utilities?
A: The DBMS_UTILITY package.
Q:   Which package do you use for system utilities?
A: The DBMS_SYSTEM package. ZZZZZZZZZ: The UTL_FILE package.
Q:   What is a PL/SQL cartridge?
A: A PL/SQL cartridge is a process that can be used to run in the Oracle application server
   and in the Oracle database. You can run PL/SQL code through the Web URL.
Q:   What is a PL/SQL Web toolkit?
A: The PL/SQL Web toolkit, a group of PL/SQL packages, is used to implement the PL/SQL
   cartridges in the application server. Three components are available in the Web toolkit:
     1   Hypertext Procedures (HTTP) sends HTML code to the Web browser.
     2   Hypertext Functions (HTF) returns HTML code as a function return value.
     3   The Oracle Web Agent (OWA) works as a common gateway interface.
Q:   Do you need to write HTML code withinss the PL/SQL code?
A: No, you cannot directly write the HTML code within the PL/SQL. This can be achieved in
   two ways:
     1. You can write HTML tags using the PL/SQL Web toolkit. Knowledge of HTML helps
        you understand the format of the Web toolkit easily.
     2. Use the HTP.PRINT procedure to embed your own HTML code.
Q:   How do you pass parameters from the Web browser to the PL/SQL cartridges?
A: You can use GET or POST methods to pass the parameters from the Web browser to the
   PL/SQL cartridges.
Q:   What are the main techniques used to connect Java programs to an Oracle 8i database?
A: Two techniques are available: Java Database Connectivity (JDBC) and SQLJ.




Q:   What is SQLJ?
A: SQLJ is an Oracle preprocessor like Pro*C that can be used to embed SQL statements
   within the Java code.
Q:   Which file extension is used for SQLJ source code?
A: You have to use the .sqlj file extension for SQLJ source code.
Q:   What is SQLJ Translator?
A: SQLJ Translator is a preprocessor that converts SQLJ source code into Java code.
Q:   Which command do you use to compile SQLJ code?
A: You can use the following example command to translate SQLJ code.
     Its syntax is as follows:
     Sqlj -user username/password filename.sqlj
Q:   How do I use autonomous PL/SQL blocks?
A: You must create a new procedure that is called from the original procedure. In the
   define section of this new procedure, you must place the keyword PRAGMA
   AUTONOMOUS_TRANSACTION. Thus, this new PL/SQL block runs in its own transaction
   without interfering with the transaction from the calling program.
Q:   How do I implement invoker right procedures?
A: In the CREATE PROCEDURE | FUNCTION | PACKAGE command, you include the keywords
   AUTHID CURRENT_USER. Here's an example:
     CREATE PROCEDURE my_proc ( param1 VARCHAR2, param2 VARCHAR2 )
     AUTHID CURRENT_USER
     AS
     BEGIN
     INSERT INTO log_table VALUES (param1,param2);
     COMMIT;
     END;


                                         -: END :-

				
DOCUMENT INFO
Categories:
Tags:
Stats:
views:1111
posted:4/26/2011
language:English
pages:81
Description: Informatica