SQL Overrides in Lookups, Source Qualifier etc

I have seen in many instances, you would want to know the SQL overrides written in a mapping Source Qualifier transformation or Lookup SQL override or Pre-SQL & Post-SQL etc. Below is the query that takes the workflow name & Folder name as input and gives you all the SQL overrides wherever they are in the workflow.

select folder, wf_name, 
       sess_name, mapping_name, 
       transformation_name, attr_name, 
       line_no, sql_value
from (select f.subj_name folder, 
             wf.task_name wf_name, 
             sess.instance_name sess_name, 
             m.mapping_name mapping_name, 
             w_inst.instance_name transformation_name, 
             attr.line_no, attr.attr_value sql_value, 
             attr_type.attr_name attr_name,
             row_number() over (partition by wf.task_name, 
                                             sess.instance_name, 
                                             m.mapping_name, 
                                             w_inst.instance_name, 
                                             attr.line_no, 
                                             attr.attr_value
                                order by attr.session_task_id desc
                               ) rn
      from opb_task_inst wf_inst
           ,opb_task_inst sess
           ,opb_session s
           ,opb_mapping m
           ,opb_subject f
           ,opb_widget_attr attr
           ,opb_widget_inst w_inst
           ,opb_task wf
           ,(select o.object_type_id object_type_id, 
                    o_attr.attr_id attr_id, 
                    o.object_type_name||': '||o_attr.attr_name attr_name
             from opb_attr o_attr, 
                  opb_object_type o
             where o.object_type_id = o_attr.object_type_id
             and o_attr.attr_datatype = 2
             and o_attr.attr_value is null
             and upper(o_attr.attr_name) like '%SQL%'
            ) attr_type
      where wf_inst.task_id = sess.task_id
      and sess.task_type = 68
      and sess.task_id = s.session_id
      and wf.subject_id = f.subj_id
      and s.mapping_id = m.mapping_id
      and attr.widget_id = w_inst.widget_id
      and w_inst.mapping_id = m.mapping_id
      and w_inst.widget_type = attr_type.object_type_id
      and wf_inst.workflow_id = wf.task_id
      and wf.task_type = 71
      and (attr.session_task_id = s.session_id
           or attr.session_task_id = 0)
      and attr.attr_id = attr_type.attr_id
      and attr.attr_value is not null
      and attr.attr_value <> '0'
      and wf.task_name = 'WORKFLOW_NAME'
      and f.subj_name = 'FOLDER_NAME'
      )
where rn = 1
order by 1, 2, 3, 4, 5, 6, 7

Shortcut Object and its parent folder

Have you always wondered how to get the shortcut object & it's parent folder. This requirement is imminent in folders where you have tens of shortcut transformations & mappings. Below query can get you what you want.

select shortcut_name, 
       case s.object_type
         when 21 then 'Mapping'
         when 23 then (select o.object_type_name
                       from opb_object_type o
                           ,opb_widget w
                       where w.widget_id = s.object_id
                       and w.widget_type = o.object_type
                      )
         when 24 then 'Target'
         when 25 then 'Source'
         when 44 then 'Mapplet'
         else 'Nothing'
       end Object_Type,
       decode(s.object_type, 21, (select f.subj_name
                                  from opb_subject f,
                                       opb_mapping m
                                  where m.mapping_id = s.object_id
                                  and m.subject_id = f.subj_id
                                  ),
                             23, (select f.subj_name
                                  from opb_subject f,
                                       opb_widget w
                                  where w.widget_id = s.object_id
                                  and w.subject_id = f.subj_id
                                  ),
                             25, (select f.subj_name
                                  from opb_subject f,
                                       opb_src src
                                  where src.src_id = s.object_id
                                  and src.subj_id = f.subj_id
                                  ),
                             24, (select f.subj_name
                                  from opb_subject f,
                                       opb_targ t
                                  where t.target_id = s.object_id
                                  and t.subj_id = f.subj_id
                                  ),
                             44, (select f.subj_name
                                  from opb_subject f,
                                       opb_widget w
                                  where w.widget_id = s.object_id
                                  and w.subject_id = f.subj_id
                                  )
              ) obj_original_folder
from opb_shortcut s, 
     opb_subject f
where s.subject_id = f.subj_id
and f.subj_name = 'FOLDER_NAME'

Repository tables Expression query

In a mapping with multiple expression transformations and multiple unconnected lookups, it is difficult for you to identify the expressions calling these unconnected lookups. This SQL will give you what you are looking for.

select w.instance_name,
       g.expression
from opb_widget_expr f,
     opb_expression g,
     opb_widget_inst w,
     opb_mapping m
where f.expr_id = g.expr_id
and f.widget_id = w.widget_id
and w.widget_type = 5
and w.mapping_id = m.mapping_id
and m.mapping_name = 'm_Open_Trades_Target_load'

Informatica 9 Launch

Though the webinar on Informatica 9 launch is studded with Key Note, Breakouts etc., I could only grab these points from the presentation about the new version.

• Logical Data objects - Something like a source, to which you can apply certain rules (set of mapplets or transformations) and you can deploy them as SQL services or web services so that you can view the data using a Browser. Note that till Informatica 8.x, you could only run transformations by creating a mapping, but in Informatica 9, you don't need a mapping, but a data object would do.
• Informatica Developer tool - The only client tool I could see in the webinar is something with "D" as a task-bar icon, doesn't necessarily mean "Designer" as in 8.x.
• Powerful Search tool to search the entire metadata for a port-name or even for a transformation name.
• With the new Analyst tools, there is extensive flexibility to the Business user to customize the Informatica objects for his needs. This would substantially reduce the Development effort, but considering the effort/cost in training Business Users to customize the developed Informatica objects, I am not sure if it is all a cake-walk.
• There are no two different objects as source or target. You can just import one object and can be used as a Source and also as a target, there by reducing the metadata storage.
  
  Please note that I have not seen the New version yet, all the above is only from the webinar that I have attended at http://www.informatica.com/9

Informatica PowerCenter Repository tables

I am sure every PowerCenter developer either has an intention or necessity to know about the Informatica metadata tables and where information is stored etc. For the starters, all the objects that we create in Informatica PowerCenter - let them be sources, targets, mappings, workflows, sessions, expressions, be it anything related to PowerCenter, will get stored in a set of database tables (call them as metadata tables or OPB tables or repository tables).

* I want to know all the sessions in my folder that are calling some shell script/command in the Post-Session command task.
* I want to know how many mappings have transformations that contain "STOCK_CODE" defined as a port.
* I want to know all unused ports in my repository of 100 folders.

In repositories where you have many number of sessions or workflows or mappings, it gets difficult to achieve this with the help of Informatica PowerCenter client tools. After all, whole of this data is stored in some form in the metadata tables. So if you know the data model of these repository tables, you will be in a better position to answer these questions.

Before we proceed further, let me clearly urge for something very important. Data in the repository/metadata/OPB tables is very sensitive and that the modifications like insert or updates are to be made using the PowerCenter tools ONLY. DO NOT DIRECTLY USE UPDATE OR INSERT COMMANDS AGAINST THESE TABLES.

Please also note that there is no official documentation from Informatica Corporation on how these tables act. It is purely based on my assumption, research and experience that I am providing these details. I will not be responsible to any of the damages caused if you use any statement other than the SELECT, knowing the details from this blog article. This is my disclaimer. Let us move on to the contents now.

There around a couple of hundred OPB tables in 7.x version of PowerCenter, but in 8.x, this number crosses 400. In this regard, I am going to talk about few important tables in this articles. As such, this is not a small topic to cover in one article. I shall write few more to cover other important tables like OPB_TDS, OPB_SESSLOG etc.

We shall start with OPB_SUBJECT now.

OPB_SUBJECT - PowerCenter folders table

This table stores the name of each PowerCenter repository folder.

Usage: Join any of the repository tables that have SUBJECT_ID as column with that of SUBJ_ID in this table to know the folder name.

OPB_MAPPING - Mappings table

This table stores the name and ID of each mapping and its corresponding folder.

Usage: Join any of the repository tables that have MAPPING_ID as column with that of MAPPING_ID in this table to know the mapping name.

OPB_TASK - Tasks table like sessions, workflow etc

This table stores the name and ID of each task like session, workflow and its corresponding folder.

Usage: Join any of the repository tables that have TASK_ID as column with that of TASK_ID/SESSION_ID in this table to know the task name. Observe that the session and also workflow are stored as tasks in the repository. TASK_TYPE for session is 68 and that of the workflow is 71.

OPB_SESSION - Session & Mapping linkage table

This table stores the linkage between the session and the corresponding mapping. As informed in the earlier paragraph, you can use the SESSION_ID in this table to join with TASK_ID of OPB_TASK table.

OPB_TASK_ATTR - Task attributes tables

This is the table that stores the attribute values (like Session log name etc) for tasks.

Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this table means. You can know more about OPB_ATTR table in the next paragraphs.

OPB_WIDGET - Transformations table

This table stores the names and IDs of all the transformations with their folder details.

Usage: Use WIDGET_ID from this table to that of the WIDGET_ID of any of the tables to know the transformation name and the folder details. Use this table in conjunction with OPB_WIDGET_ATTR or OPB_WIDGET_EXPR to know more about each transformation etc.

OPB_WIDGET_FIELD - Transformation ports table

This table stores the names and IDs of all the transformation fields for each of the transformations.

Usage: Take the FIELD_ID from this table and match it against the FIELD_ID of any of the tables like OPB_WIDGET_DEP and you can get the corresponding information.

OPB_WIDGET_ATTR - Transformation properties table

This table stores all the properties details about each of the transformations.

Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this transformation means.

OPB_EXPRESSION - Expressions table

This table stores the details of the expressions used anywhere in PowerCenter.

Usage: Use this table in conjunction with OPB_WIDGET/OPB_WIDGET_INST and OPB_WIDGET_EXPR to get the expressions in the Expression transformation for a particular, mapping or a set.

OPB_ATTR - Attributes

This table has a list of attributes and their default values if any. You can get the ATTR_ID from this table and look it up against any of the tables where you can get the attribute value. You should also make a note of the ATTR_TYPE, OBJECT_TYPE_ID before you pick up the ATTR_ID. You can find the same ATTR_ID in the table, but with different ATTR_TYPE or OBJECT_TYPE_ID.

OPB_COMPONENT - Session Component

This table stores the component details like Post-Session-Success-Email, commands in Post-Session/pre-Session etc.

Usage: Match the TASK_ID with that of the SESSION_ID in OPB_SESSION table to get the SESSION_NAME and to get the shell command or batch command that is there for the session, join this table with OPB_TASK_VAL_LIST table on TASK_ID.

OPB_CFG_ATTR - Session Configuration Attributes

This table stores the attribute values for Session Object configuration like "Save Session log by", Session log path etc.

Error Logging in PowerCenter

In order to capture any Informatica PowerCenter errors into a flat file or database during runtime, Informatica Corporation suggests row-level error logging. The major disadvantage with this is that the performance of the workflow is affected because of row-level processing as opposed to block processing.

In order to overcome this, a simple approach can be followed that can be a common approach for all the workflows. This approach is based on the fact that $Session_Name.ErrorMsg will store NULL value if the session runs fine, otherwise stores the latest error message from the Session run.

1) Create two workflow variables - one for Error message $$ERROR_MESSAGE and the other $$SESSION_NAME to store the failed session name.

2) Create an assignment task in the workflow and create links to it from each of the sessions. Please note that the flow should be TOWARDS the assignment task from the sessions.

3) Modify the link expression for all these links to $Session_Name.PrevTaskStatus = FAILED.

4) In the assignment task, assign $Session_Name.ErrorMsg to the workflow variable $$ERROR_MESSAGE and assign Session_Name to $$SESSION_NAME.

5) You need a bit of nested iifs to achieve this.

For variable $$ERROR_MESSAGE, the expression contains

:udf.if_null_or_blank($Session_Name_1.ErrorMsg,
    :udf.if_null_or_blank($Session_Name_2.ErrorMsg,
        :udf.if_null_or_blank($Session_Name_3.ErrorMsg,
            :udf.if_null_or_blank($Session_Name_4.ErrorMsg ,
                :udf.if_null_or_blank($Session_Name_5.ErrorMsg ,
                    :udf.if_null_or_blank($Session_Name_6.ErrorMsg ,
                        :udf.if_null_or_blank($Session_Name_7.ErrorMsg ,
                            :udf.if_null_or_blank($Session_Name_8.ErrorMsg ,
                                :udf.if_null_or_blank($Session_Name_9.ErrorMsg ,
                                    :udf.if_null_or_blank($Session_Name_10.ErrorMsg ,
                                        :udf.if_null_or_blank($Session_Name_11.ErrorMsg ,
                                            :udf.if_null_or_blank($Session_Name_12.ErrorMsg ,
                                                 'A Fatal Error occurred' 
                                 ,$Session_Name_12.ErrorMsg
                                 )
                              ,$Session_Name_11.ErrorMsg
                              )
                           ,$Session_Name_10.ErrorMsg
                           )
                        ,$Session_Name_9.ErrorMsg
                        )
                     ,$Session_Name_8.ErrorMsg
                     )
                  ,$Session_Name_7.ErrorMsg
                  )
               ,$Session_Name_6.ErrorMsg
               )
            ,$Session_Name_5.ErrorMsg
            )
         ,$Session_Name_4.ErrorMsg
         )
      ,$Session_Name_3.ErrorMsg
      )
   ,$Session_Name_2.ErrorMsg
   )
,$Session_Name_1.ErrorMsg
)

:udf.if_null_or_blank(input_String_Argument, output_if_NULL_Blank, output_if_not_NULL_Blank) is a user-defined function with expression contents

iif(isnull(input_String_Argument) or length(ltrim(rtrim(input_String_Argument)))
= 0, output_if_NULL_Blank, output_if_not_NULL_Blank)

In the same way, create the expression for the $$SESSION_NAME. It should be the same expression as for the $$ERROR_MESSAGE but in the else part of the iif(), the session names should be specified instead of ErrorMsg.

6) From this assignment task take a link to a session which stores the contents of these workflow variables into a database table or a flat file. let us call this session mapping as LOG mapping.

You may question the scope of these workflow variable inside the Log mapping. If you can use the workflow variable in the source qualifier SQL override, then you can get the data from the same. Like this:

select '$$ERROR_MESSAGE', '$$SESSION_NAME'
from dual

Take 2 ports out of the source qualifier onto a expression transformation and then continue loading into a relation table target or a flat file target.

7) It is IMPORTANT to make sure that the General tab property of the Assignment task --> Treat Input Links as "OR". This makes sure if at least one session fails, the assignment task is triggered and the error is logged.

If you implement the Error Logging this way, you will be able to catch all kinds of Informatica errors.

Dynamically generate parameter files

I've seen this in many places, but just want to tell you the way I have been doing it for few years now. I will take the context in a bit more detailed manner to make every one understand it.

1. PowerCenter objects – Introduction:

• A repository is the highest physical entity of a project in PowerCenter.


• A folder is a logical entity in a PowerCenter project. For example, Customer_Data is a folder.


• A workflow is synonymous to a set of programs in any other programming language.


• A mapping is a single program unit that holds the logical mapping between source and target with required transformations. A mapping will just say a source table by name EMP exists with some structure. A target flat file by name EMP_FF exists with some structure. The mapping doesn’t say in which schema this EMP table exists and in which physical location this EMP_FF table going to be stored.


• A session is the physical representation of the mapping. The session defines what a maping didn’t do. The session stores the information about where this EMP table comes from. Which schema, with what username and password can we access this table in that schema. It also tells about the target flat file. In which physical location the file is going to get created.


• A transformation is a sub-program that performs a specific task with the input it gets and returns some output. It can be assumed as a stored procedure in any database. Typical examples of transformations are Filter, Lookup, Aggregator, Sorter etc.


• A set of transformations, that are reusable can be built into something called mapplet. A mapplet is a set of transformations aligned in a specific order of execution.

As with any other tool or programing language, PowerCenter also allows parameters to be passed to have flexibility built into the flow. Parameters are always passed as data in flat files to PowerCenter and that file is called the parameter file.

2. Parameter file format for PowerCenter:

For a workflow parameter which can be used by any session in the workflow, below is the format in which the parameter file has to be created.

[Folder_name:WF.Workflow_Name]
$$parameter_name1=value
$$parameter_name2=value

For a session parameter which can be used by the particular session, below is the format in which the parameter file has to be created.

[Folder_name:WF.Workflow_Name:ST.Session_Name]
$$parameter_name1=value
$$parameter_name2=value


3. Parameter handling in a data model:

• To have flexibility in maintaining the parameter files.


• To reduce the overhead for the support to change the parameter file every time a value of a parameter changes


• To ease the deployment,
all the parameters have to be maintained in Oracle or any database tables and a PowerCenter session is created to generate the parameter file in the required format automatically.


For this, 4 tables are to be created in the database:

1. FOLDER table will have entries for each folder.


2. WORKFLOWS table will have the list of each workflow but with a reference to the FOLDERS table to say which folder this workflow is created in.


3. PARAMETERS table will hold all the parameter names irrespective of folder/workflow.

4. PARAMETER_VALUES table will hold the parameter of each session with references to PARMETERS table for parameter name and WORKFLOWS table for the workflow name. When the session name is NULL, that means the parameter is a workflow variable which can be used across all the sessions in the workflow.

To get the actual names because PARAMETER_VALUES table holds only ID columns of workflow and parameter, we create a view that gets all the names for us in the required format of the parameter file. Below is the DDL for the view.


a. Parameter file view:


CREATE OR REPLACE VIEW PARAMETER_FILE
(
HEADER,
DETAIL
)
AS
select '['fol.folder_name'.WF:' wfw.workflow_name']' header
,pmr.parameter_namenvl2(dtl.logical_name, '_'dtl.logical_name, NULL)'='
dtl.value detail
from folder fol
,parameters pmr
,WORKFLOWS wfw
,PARAMETER_VALUES dtl
where fol.id = wfw.folder_id
and dtl.pmr_id = pmr.id
and dtl.wfw_id = wfw.id
and dtl.session_name is null
UNION
select '['fol.folder_name'.WF:' wfw.workflow_name'.ST:' dtl.session_name']' header
,decode(dtl.mapplet_name, NULL, NULL, dtl.mapplet_name'.')
pmr.parameter_namenvl2(dtl.logical_name, '_'dtl.logical_name, NULL)'=' dtl.value detail
from folder fol
,parameters pmr
,WORKFLOWS wfw
,PARAMETER_VALUES dtl
where fol.id = wfw.folder_id
and dtl.pmr_id = pmr.id
and dtl.wfw_id = wfw.id
and dtl.session_name is not null

b. FOLDER table

ID (NUMBER)
FOLDER_NAME (varchar50)
DESCRIPTION (varchar50)


c. WORKFLOWS table

ID (NUMBER)
WORKFLOW_NAME (varchar50)
FOLDER_ID (NUMBER) Foreign Key to FOLDER.ID
DESCRIPTION (varchar50)


d. PARAMETERS table

ID (NUMBER)
PARAMETER_NAME (varchar50)
DESCRIPTION (varchar50)


e. PARAMETER_VALUES table

ID (NUMBER)
WF_ID (NUMBER)
PMR_ID (NUMBER)
LOGICAL_NAME (varchar50)
VALUE (varchar50)
SESSION_NAME (varchar50)

• LOGICAL_NAME is a normalization initiative in the above parameter logic. For example, in a mapping if we need to use $$SOURCE_FX as a parameter and also $$SOURCE_TRANS as another mapping parameter, instead of creating 2 different parameters in the PARAMETERS table, we create one parameter $$SOURCE. Then FX and TRANS will be two LOGICAL_NAME records of the PARAMETER_VALUES table.

• m_PARAMETER_FILE is the mapping that creates the parameter file in the desired format and the corresponding session name is s_m_PARAMETER_FILE.

Designing the mapping/workflow efficiently - part II

In few of my other blog articles, I've been mentioning about the push down optimization. In simple terms, try to push as much processing as possible into the database source or target based on the necessity. I will explain few instances where we can do so.

Using analytics

Instead of using aggregator transformation or a combination of sorter/expression/filter for occassions when they are demanded, we can make use of the anlytic functions of the database. For eg: While reading your source data from the table, you may need to identify the last record from the source. For this requirement, the first option that comes into mind is something like this:

One source pipeline picking up the data records and the second pipeline counting the number of source records and eventually you equate that number to the current record number. When they are equal, it implies that the current record is the last record from the source.

As I said earlier, people resort to a count(*) and GROUP BY from SQL or an aggregator/sorter/expression/filter combination for the same. Instead of this, the analytic functions provide you greater flexibility. The same task can be acheived with the below SQL.

select col1,
col2,
col2,
lead (col1) over (order by input_sort_criteria) next_record
from table_name
/

For the last record with the given "input_sort_criteria", the value will be NULL.


Avoid Stored Procedures

I have seen many mappings with unnecessary usage of Stored Procedures. Stored Procedures "are" performance killers. If run for every record in the source, stored procedures consume much of the workflow run-time. A database connection has to be established each time for a record and then the execution takes place in the database. As you can see, this beahviour leads to worst performing interfaces.

A general guideline is that when you can do it in PowerCenter using transformations, do not use store procedures.


Conclusion:

The two sections in this article seem to be conflicting with each other. In the first section, I've urged you to push as much processing as possible to the database and in the second section, I'm suggesting you to avoid database.

The difference here is when you connect to database using a Lookup or Source qualifier or pre/post-SQL procedures, you are really connecting to the database only ONCE for all the source records. When you connect to the database using a stored procedure transformation that is called for each record, then you are connecting to the database for as many times as the number of records in the source.

When you have a vehicle (a loading/unloading truck) at your disposal, would you take 100 bricks from one place to the other place at a time or each brick at a time in the vehicle?

Avoid sequence generator

I would always advice you to avoid sequence generator while populating an ID column in the relational target table. I suggest you rather create a sequence on the target database and enable the trigger on that table to fetch the value from the database sequence.

There are many advantages by doing this way.

• Less number of PowerCenter objects in the object which reduce development time and also less maintenance.
• Today you may be writing the records into that target table using PowerCenter flow. What if you get another application tomorrow that also writes records into that table? Your ID column values go bizarre in this scenario.
• During migration of objects from Dev to QA and also to Production further, there is no extra over-head of considering the persistent values of the sequence generator from the repository database.

In all of the above cases, a sequence created in the target database would make life lot easier for the table data maintenance and also for the PowerCenter development. In fact, databases will have specific mechanisms (focused) to deal with sequences and so you can implement manual Push-down optimization on your PowerCenter mapping design for yourself.

Performance of workflow - Most feared, but the easiest

With the newer and newer versions of PowerCenter, there is added flexibility for the developer to build better-performing workflows. Though performance tuning has been the most feared part of development, it is the easiest, if the intricacies are known. We shall discuss one major problems for performance - SQL query tuning.

SQL queries/actions occur in PowerCenter in one of the below ways.

• Relational Source Qualifier
• Lookup SQL Override
• Stored Procedures
• Relational Target

Accessing an execution plan for the query is the best way to tune it. Look for any table scans and remember "Full Table Scans are not evil", "Indexes are not always fast", and “Indexes can be slow too". Analyse the table data to see if picking up 20 records out of 20 million is best using index or using table scan. Fetching 10 records out of 15 using index is faster or using full table scan is easier.

Many times the relational target indexes create problem for the performance of the actual loading of records into the relational target. It may surprise you (some times?), the way PowerCenter has reduced the time of loading the target data if you disable the indexes and then enable them in post-SQL again.

Please note that the flow has to also consider the integrity constraints when you have disabled the indexes before loading.

FTP Connection object - Gain platform independence

If you have any files to be read as source from Windows server when your PowerCenter server is hosted on UNIX/LINUX, then makes use of FTP users on the Windows server and use File Reader with FTP Connection object. This connection object can be added as any other connection string. This gives the flexibility of platform independence. This will further reduce the over-head of having SAMBA mounts on to the Informatica UNIX boxes.

"This" and "That" of Pre/Post Session command tasks

It is a very good practice to email the success or failure status of a task, once it is done. In the same way, when a business requirement drives, make use of the Post Session Success and Failure email for proper communication. The built-in feature offers more flexibility with Session Logs as attachments and also provides other run-time data like Workflow run-instance ID
etc,.

Any archiving activities around the source and target flat files can be easily managed with-in the session using the post-session and pre-session commands. You can even build some resource-saving as well by issuing a gzip on the target file, to save space.

Never leave it a mystery - Multiple sources for one SQ

When you have different table joins in the source-qualifier, it is a bad practice to include the table in the SQL and not make it visible in the mapping, Though PowerCenter works fine without the source defined in Source Analyzer, it is a good practice to define the source then drag it to the mapping onto the common source qualifier.

Don't make your SQL override of the Source Qualifier orphan. The actual tables used in the query FROM clause have to be part of the mapping and should be visible in the mapping attached to the same source qualifier.

How to generate target file names dynamically from the mapping?

I've seen this question many times in the Devnet forums very recently. I thought I will just elaborate the PowerCenter documentation here.



In order to generate the target file names from the mapping, we should make use of the special "FileName" port in the target file. You can't create this special port from the usual New port button. There is a special button with label "F" on it to the right most corner of the target flat file when viewed in "Target Designer".



Below two screen-shots tell you how to create the special port in your target file.

Once this is done, the job is done. When you want to create the file name with a timestamp attached to it, just use a port from an Expression transformation before the target to pass a value of Output Port with expression $$FILE_NAMEto_char(sessstarttime, 'YYYYMMDDHH24:MISS')'.csv'.

Please note that $$FILE_NAME is a parameter to the mapping and I've used sessstarttime because it will be constant through out the session run.

If you use sysdate, it will change if you have 100s of millions of records and if the session may run for an hour, each second a new file will get created.

Please note that a new file gets created with the current value of the port when the port value which maps to the FileName changes.

We'll come to the mapping again. This mapping generates two files. One is a dummy file with zero bytes size and the file name is what is given in the Session properties under 'Mappings' tab for target file name. The other file is the actual file created with the desired file name and data.

When you have different sets of input data with different target files created, use the same instance, but with a Transaction Control transformation which defines the boundary for the source sets. A demonstration of this is available in the PowerCenter documentation.

Informatica PowerCenter performance - Lookups

Lookup performance

Lookup is an important and a useful transformation when used effectively.
What is a lookup transformation? It is just not another transformation which fetches you data to look against the source data. It is a transformation when used improperly, makes your flow run for ages.

I now try to explain different scenarios where you can face problems with Lookup and also how to tackle them.


Unwanted columns:

By default, when you create a lookup on a table, PowerCenter gives you all the columns in the table, but be sure to delete the unwanted columns from the lookup as they affect the lookup cache very much. You only need columns that are to be used in lookup condition and the ones that have to get returned from the lookup.

SQL query:

We will start from the database. Find the execution plan of the SQL override and see if you can add some indexes or hints to the query to make it fetch data faster. You may have to take the help of a database developer to accomplish this if you, yourself are not an SQLer.

Size of the source versus size of lookup:

Let us say, you have 10 rows in the source and one of the columns has to be checked against a big table (1 million rows). Then PowerCenter builds the cache for the lookup table and then checks the 10 source rows against the cache. It takes more time to build the cache of 1 million rows than going to the database 10 times and lookup against the table directly.

Use uncached lookup instead of building the static cache, as the number of source rows is quite less than that of the lookup.

Conditional call of lookup:

Instead of going for connected lookups with filters for a conditional lookup call, go for unconnected lookup. Is the single column return bothering for this? Go ahead and change the SQL override to concatenate the required columns into one big column. Break them at the calling side into individual columns again.

JOIN instead of Lookup:

In the same context as above, if the Lookup transformation is after the source qualifier and there is no active transformation in-between, you can as well go for the SQL over ride of source qualifier and join traditionally to the lookup table using database joins, if both the tables are in the same database and schema.

Increase cache:

If none of the above seems to be working, then the problem is certainly with the cache. The cache that you assigned for the lookup is not sufficient to hold the data or index of the lookup. Whatever data that doesn't fit into the cache is spilt into the cache files designated in $PMCacheDir. When the PowerCenter doesn't find the data you are lookingup in the cache, it swaps the data from the file to the cache and keeps doing this until it finds the data. This is quite expensive for obvious reasons being an I/O operation. Increase the cache so that the whole data resides in the memory.

What if your data is huge and your whole system cache is less than that? Don't promise PowerCenter the amount of cache that it can't be allotted during the runtime. If you promise 10 MB and during runtime, your system on which flow is running runs out of cache and can only assign 5MB. Then PowerCenter fails the session with an error.

Cachefile file-system:

In many cases, if you have cache directory in a different file-system than that of the hosting server, the cache file piling up may take time and result in latency. So with the help of your system administrator try to look into this aspect as well.

Useful cache utilities:

If the same lookup SQL is being used in someother lookup, then you have to go for shared cache or reuse the lookup. Also, if you have a table that doesn't get data updated or inserted quite often, then use the persistent cache because the consecutive runs of the flow don't have to build the cache and waste time.

After all these are tried out, sit and wait for the session to get completed - there is nothing else you can do about the lookup.