DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
Answer:
It is not possible to store all the session log information in some table. Along with error related in-formation we may get some other session related inin-formation from metadata repository tables like REP_SESS_LOG.
To capture error data, we can configure the session as below:
Go to Session->Config Object-> Error Handling Section Give the setting-
Error Log Type: Relational Database.
Error Log Type: Give the Database Connection, where we want to store the error tables.
Error Log Table Name Prefix: Prefix for the error tables. By default, Informatica creates 4 different error ta-bles. If we provide a prefix here the error tables will be created with the same prefix in the database.
Log Row Data: This option is used to log the data at the point where the error happened.
Log Source Row Data: Capture the source date for the error record.
Log Source Row Data: Error data will be stored into a single column of the database table. We can specify the delimiter for the source data here.
List of Error tables created by Informatica:
PMERR_DATA. Stores data and metadata about a transformation row error and its corresponding source row.
PMERR_MSG. Stores metadata about an error and the error message.
PMERR_SESS. Stores metadata about the session.
PMERR_TRANS. Stores metadata about the source and transformation ports, such as name and data type, when a transformation error occurs.
The above tables are specifically used to store the information about exception (error) records - e.g. records in the reject file.
We can use this as a base for error handling strategy. But this does not contain all the information that are present in session log - like performance details (thread busy percentage), details of the transformation in-voked in the session etc. We can also check the contents of REP_SESS_LOG view under Informatica reposito-ry schema; however, that too does not contain all the information.
5. Can we call a shell script from session properties?
Answer:
The Integration Service can execute shell commands at the beginning or at the end of the session. The Work-flow Manager provides the following types of shell commands for each Session task:
Pre-session command
Post-session success command
Post-session failure command
Use any valid UNIX command or shell script for UNIX nodes, or any valid DOS or batch file for Windows nodes. Configure the session to run the pre- or post-session shell commands.
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
6. Can we change the Source and Target table names in Session level?
Answer:
Yes, we can change the source and target table names in the session level. Go to the session and navigate to the mapping tab. Select the source/target to be changed- for target mention new table name in
“Target Table Name” & for source choose “Source Table Name”.
One more suitable method would be to parameterize the source and target table name. We can run the same mapping concurrently using different parameter files. We have to enable concurrent run mode in the Workflow level. Also find more information regarding parameterization.
7. How to write flat file column names in target?
Answer:
There are two options available in session properties to take care of this requirement. For this, Go to Map-ping Tab Target Properties and Choose the header option as Output Field names OR Use Header Command output File.
Option 1, will create your output file with a header record and the column heading names will be same as your Target transformation port names.
Option 2, we can create our command to generate the header record text. We can use an 'echo' command here to get this created. Here is an example
echo '"Employee ID"|"Department ID"'
It is recommended using the second option as it gives more flexibility for writing the column names.
8. What are the ERROR tables present in Informatica?
Answer:
PMERR_DATA- Stores data and metadata about a transformation row error and its corresponding source row.
PMERR_MSG- Stores metadata about an error and the error message.
PMERR_SESS- Stores metadata about the session.
PMERR_TRANS- Stores metadata about the source and transformation ports, such as name and data type, when a transformation error occurs.
9. What are the alternate ways to stop a session without using “STOP ON ERRORS” option set to 1 in session properties?
Answer:
We can also use the functions STOP () or ERROR () in an expression transformation to stop the execution of a
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
10. Suppose a session fails after loading of 10,000 records in the target. How can we load the records from 10,001 when we run the session next time?
Answer:
If we configure the Session for Normal load rather than Bulk load & by using Recovery Strategy in the Session Properties & selecting the Option “Resume from last Check point”, then we can run the Session from the last Commit Interval.
In this case if we specify the Commit Interval as 10,000 & the Integration Service issues a commit after load-ing 10,000 records then you can load the records from 10,001.
If 9999 rows were loaded and the session fails and Integration Service did not issue any commit as the Com-mit Interval in this case is 10,000 then we cannot perform Recovery. In this case truncate the Target Table &
Restart the session.
11. Define the types of Commit intervals apart from user defined?
Answer:
The different commit intervals are:
Target-based commit. The Informatica Server commits data based on the number of target rows and the key constraints on the target table. The commit point also depends on the buffer block size and the commit in-terval.
Source-based commit. The Informatica Server commits data based on the number of source rows. The commit point is the commit interval you configure in the session properties.
12. 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?
Answer:
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
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
13. How to capture performance statistics of individual transformation in the mapping and explain some important statistics that can be captured?
Answer:
Use tracing level Verbose data.
14. How can we parameterize success or failure email list?
Answer:
We can parameterize the email user list and modify the values in parameter file.
Use $PMSuccessEmailUser, $PMFailureEmailUser.
Also we can use pmrep command to update the email task:
updateemailaddr -d <folder_name>
-s <session_name>
-u <success_email_address>
-f <failure_email_address>
15. Is it possible that a session failed but still the workflow status is showing success?
Answer:
If the workflow completes successfully it will show the execution status of success irrespective of whether any session within the workflow failed or not. The workflow success status has nothing to do with session failure. If and only if we set the session general option in the workflow designer Fail Parent if this task fails, then only the workflow status will display as failed on session failure.
16. What is Busy Percentage?
Answer:
Duration of time the thread was occupied compared to total run time of the mapping.
So let’s say, we have one writer thread - this thread is internally responsible for writing data to the target ta-ble/ file. Now if our mapping runs for 100 seconds but the time taken by the mapping to write the data to the target is only 20 seconds (because other time it was busy in reading/ transforming the data), then busy percentage of the writer thread is 20%
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
17. Can we write a PL/SQL block in pre and post session or in target query override?
Answer:
Yes we can. Remember always to put a backslash (\) before any semi-colon ( ; ) we use in the PL-SQL block.
18. Whenever a session runs does the data gets overwritten in a flat file target? Is it possible to keep the existing data and add the new data to the target file?
Answer:
Normally with every session run target file data will be overwritten, except if we select “Append if Exist” (8x onwards) option for the Target session Property which will append the new data to the existing data in the flat file target.
19. Can we use the same session to load a target table in different databases having same target definition?
Answer:
Yes we can use the same session to load same target definition in different databases with the help of the Parameterization; i.e. using different parameter files with different values for the parameterized Target Con-nection object $DBConCon-nection_TGT and Owner/Schema name Table Name Prefix with
$Param_Tgt_Tablename. To run the single workflow with the session, to load two different database target tables we can consider using Concurrent workflow Instances with different parameter files.
Even we can load two instance of the same target connected in the same pipeline. At the session level use different relational connection object created for different Databases.
20. How do you remove the cache files after the transformation?
Answer:
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.
21. Why doesn't a running session QUIT when Oracle or Sybase return fatal errors?
Answer:
The session will only QUIT when its threshold: "Stop on errors" is set to 1. Otherwise the session will
contin-DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
22. If we have written a source override query in source qualifier in mapping level but have modified the query in session level SQL override then how integration service behaves.
Answer:
Informatica Integration Service treats the Session Level Query as final during the session run. If both the que-ries are different Integration Service will consider the Session level query for execution and will ignore the Mapping level query.
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
20. Workflow
1. What is the difference between STOP and ABORT options in Workflow?
Answer:
When we issue the STOP command on the executing session task, the Integration Service stops reading data from source. It continues processing, writing and committing the data to targets. If the Integration Service cannot finish processing and committing data, we can issue the abort command.
In contrast ABORT command has a timeout period of 60 seconds. If the Integration Service cannot finish pro-cessing and committing data within the timeout period, it kills the DTM process and terminates the session.
We can stop or abort tasks, worklets within a workflow from the Workflow Monitor or Control task in the workflow or from command task by using pmcmd stop or abort command. We can also call the ABORT function from mapping level.
When we stop or abort a task, the Integration Service stops processing the task and any other tasks in the path of the stopped or aborted task. The Integration Service however continues processing concurrent tasks in the workflow. If the Integration Service cannot stop the task, we can abort the task.
The Integration Service aborts any workflow if the Repository Service process shuts down.
2. Running Informatica Workflow continuously – How to run a workflow continuously until a certain condition is met?
Answer:
We can schedule a workflow to run continuously. A continuous workflow starts as soon as the In-tegration Service initializes. If we schedule a real-time session to run as a continuous workflow, the Integration Service starts the next run of the workflow as soon as it finishes the first. When the workflow stops, it restarts immediately.
Alternatively for normal batch scenario we can create conditional-continuous workflow as below.
Suppose wf_Bus contains the business session that we want to run continuously until a certain conditions is meet before it stops, may be presence of file or particular value of workflow variable etc.
So modify the workflow as Start-Task followed by Decision Task which evaluates a condition to be TRUE or FALSE. Based on this condition the workflow will run or stop.
Next use the Link Task to link the business session for $Decision.Condition=TRUE.
For the other part use a Command Task for $Decision.Condition=FALSE.
In the command task create a command to call a dummy workflow using pmcmd functionality. e.g.
"C:\Informatica\PowerCenter8.6.0\server\bin\pmcmd.exe" startworkflow -sv IS_info_repo8x -d Domain_hp -u info_repo8x -p info_repo8x -f WorkFolder wf_dummy
DWBIConcepts DWBIConcepts DWBIConcepts DWBIConcepts
Within the command task put the pmcmd command as
"C:\Informatica\PowerCenter8.6.0\server\bin\pmcmd.exe" startworkflow -sv IS_info_repo8x -d Domain_sauravhp -u info_repo8x -p info_repo8x -f
WorkFolder wf_Bus
In this way we can manage to run a workflow continuously. So the basic concept is to use two workflows and make them call each other.