Monday, 27 April 2015

Difference between Connected and Unconnected Lookups in Informatica


Connected Lookup

Unconnected Lookup

Receives input values directly from the pipeline.

Receives input values from the result of a :LKP expression in another transformation.

Use a dynamic or static cache.

Use a static cache.

Cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports.

Cache includes all lookup/output ports in the lookup condition and the lookup/return port.

Can return multiple columns from the same row or insert into the dynamic lookup cache.

Designate one return port (R). Returns one column from each row.

If there is no match for the lookup condition, the Integration Service returns the default value for all output ports. If you configure dynamic caching, the Integration Service inserts rows into the cache or leaves it unchanged.

If there is no match for the lookup condition, the Integration Service returns NULL.

If there is a match for the lookup condition, the Integration Service returns the result of the lookup condition for all lookup/output ports. If you configure dynamic caching, the Integration Service either updates the row the in the cache or leaves the row unchanged.

If there is a match for the lookup condition, the Integration Service returns the result of the lookup condition into the return port.

Pass multiple output values to another transformation. Link lookup/output ports to another transformation.

Pass one output value to another transformation. The lookup/output/return port passes the value to the transformation calling :LKP expression.

Supports user-defined default values.

Does not support user-defined default values.

 


Monday, 20 April 2015

Using an Oracle PL/SQL block in pre SQL

Using an Oracle PL/SQL block in pre SQL

Question :

Is it possible to use an Oracle anonymous block in pre SQL? I would prefer that over the overhead of having to create a stored procedure since the action being taken only takes place in one location.

 

Solution:

 

Should be possible - just put a backslash before each semi-colon. 
e.g.:
 

declare
 
cursor bitmaps is
 
select index_name
 
from user_indexes
 
where index_type = 'BITMAP'
 
and table_name in ('X', 'Y')\;
 
exec_immed varchar(100)\;
 
begin
 
FOR bitmaps_rec in bitmaps
 
LOOP
 
execute immediate 'alter index '||bitmaps_rec.index_name||' unusable'\;
 
END LOOP\;
 
end\;
 

Maximum number of Columns in Oracle

 maximum number of columns in a table or view is 1000
01792. 00000 -  "maximum number of columns in a table or view is 1000"
*Cause:    An attempt was made to create a table or view with more than 1000
           columns, or to add more columns to a table or view which pushes
           it over the maximum allowable limit of 1000. Note that unused
           columns in the table are counted toward the 1000 column limit.
*Action:   If the error is a result of a CREATE command, then reduce the
           number of columns in the command and resubmit. If the error is
           a result of an ALTER TABLE command, then there are two options:
           1) If the table contained unused columns, remove them by executing
           ALTER TABLE DROP UNUSED COLUMNS before adding new columns;
           2) Reduce the number of columns in the command and resubmit.

Thursday, 9 April 2015

Oracle Syntax for Update with Joins

UPDATE table1 SET table1.value = (SELECT table2.CODE
                                  FROM table2 
                                  WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
            FROM table2 
            WHERE table1.value = table2.DESC);

Tuesday, 7 April 2015

Target update override - Informatica

Target update override - Informatica

When you used an update strategy transformation in the mapping or specified the "Treat Source Rows As" option as update, informatica integration service updates the row in the target table whenever there is match of primary key in the target table found.

The update strategy works only 

  • when there is primary key defined in the target definition.
  • When you want update the target table based on the primary key.

What if you want to update the target table by a matching column other than the primary key? In this case the update strategy wont work. Informatica provides feature, "Target Update Override", to update even on the columns that are not primary key.

You can find the Target Update Override option in the target definition properties tab. The syntax of update statement to be specified in Target Update Override is

UDATE TARGET_TABLE_NAME  SET TARGET_COLUMN1 = :TU.TARGET_PORT1,      [Additional update columns]  WHERE TARGET_COLUMN = :TU.TARGET_PORT  AND   [Additional conditions]  

Here TU means target update and used to specify the target ports.

Example: Consider the employees table as an example. In the employees table, the primary key is employee_id. Let say we want to update the salary of the employees whose employee name is MARK. In this case we have to use the target update override. The update statement to be specified is

UPDATE EMPLOYEES  SET SALARY = :TU.SAL  WHERE EMPLOYEE_NAME = :TU.EMP_NAME

Monday, 6 April 2015

Handling Single Quotes in Column Content

dbms_xmlgen.convert( rtrim( extract( Xmlagg( xmlelement(e,Sq_Sql.Attr_Value) order by Sq_Sql.line_no
             ), '//text()').getclobval() , ','), 1) 

Sq_Sql.Attr_Value , Sq_Sql.line_no are column names

Thursday, 2 April 2015

Session on Parameters and Variables

Types of Parameters and Variables

There are many types of Parameters and Variables we can define. Please find below the comprehensive list:

  • Service Variables: To override the Integration Service variables such as email addresses, log file counts, and error thresholds. Examples of service variables are $PMSuccessEmailUser, $PMFailureEmailUser, $PMWorkflowLogCount, $PMSessionLogCount, and $PMSessionErrorThreshold.
  • Service Process Variables: To override the the directories for Integration Service files for each Integration Service process. Examples of service process variables are $PMRootDir, $PMSessionLogDir and $PMBadFileDir.
  • Workflow Variables: To use any variable values at workflow level. User-defined workflow variables like $$Rec_Cnt
  • Worklet Variables: To use any variable values at worklet level. User-defined worklet variables like $$Rec_Cnt. We can use predefined worklet variables like $TaskName.PrevTaskStatus in a parent workflow, but we cannot use workflow variables from the parent workflow in a worklet.
  • Session Parameters: Define values that may change from session to session, such as database connections, db owner, or file names. $PMSessionLogFile, $DynamicPartitionCount and $Param_Tgt_Tablename are user-defined session parameters. List of other built in Session Parameters:

    $PMFolderName, $PMIntegrationServiceName, $PMMappingName, $PMRepositoryServiceName, $PMRepositoryUserName, $PMSessionName, PMSessionRunMode [Normal/Recovery], $PM_SQ_EMP@numAffectedRows, $PM_SQ_EMP@numAppliedRows, $PM_SQ_EMP@numRejectedRows, $PM_SQ_EMP@TableName, $PM_TGT_EMP@numAffectedRows, $PM_TGT_EMP@numAppliedRows, $PM_TGT_EMP@numRejectedRows, $PM_TGT_EMP@TableName, $PMWorkflowName, $PMWorkflowRunId, $PMWorkflowRunInstanceName.

    Note: Here SQ_EMP is the Source Qualifier Name and TGT_EMP is the Target Definition.

  • Mapping Parameters: Define values that remain constant throughout a session run. Examples are $$LOAD_SRC, $$LOAD_DT. Predefined parameters examples are $$PushdownConfig.
  • Mapping Variables: Define values that changes during a session run. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session. Example $$MAX_LOAD_DT

Difference between Mapping Parameters and Variables

A mapping parameter represents a constant value that we can define before running a session. A mapping parameter retains the same value throughout the entire session. If we want to change the value of a mapping parameter between session runs we need to Update the parameter file.

A mapping variable represents a value that can change through the session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time when we run the session. Variable functions like SetMaxVariable, SetMinVariable, SetVariable, SetCountVariable are used in the mapping to change the value of the variable. At the beginning of a session, the Integration Service evaluates references to a variable to determine the start value. At the end of a successful session, the Integration Service saves the final value of the variable to the repository. The next time we run the session, the Integration Service evaluates references to the variable to the saved value. To override the saved value, define the start value of the variable in the parameter file.

Parameterize Connection Object

First of all the most common thing we usually Parameterise is the Relational Connection Objects. Since starting from Development to Production environment the connection information obviously gets changed. Hence we prefer to go with parameterisation rather than to set the connection objects for each and every source, target and lookup every time we migrate our code to new environment.E.g.

  • $DBConnection_SRC
  • $DBConnection_TGT

If we have one source and one target connection objects in your mapping, better we relate all the Sources, Targets, Lookups and Stored Procedures with $Source and $Target connection. Next we only parameterize $Source and $Target connection information as:

  • $Source connection value with the Parameterised Connection $DBConnection_SRC
  • $Target connection value with the Parameterised Connection $DBConnection_TGT

Lets have a look how the Parameter file looks like. Parameterization can be done at folder level, workflow level, worklet level and till session level.

[WorkFolder.WF:wf_Parameterize_Src.ST:s_m_Parameterize_Src]  $DBConnection_SRC=Info_Src_Conn  $DBConnection_TGT=Info_Tgt_Conn  

Connection Object Parameter

Here Info_Src_Conn, Info_Tgt_Conn are Informatica Relational Connection Objects.

Note: $DBConnection lets Informatica know that we are Parameterizing Relational Connection Objects.

For Application Connections use $AppConnection_Siebel, $LoaderConnection_Orcl when parameterizing Loader Connection Objects and $QueueConnection_portal for Queue Connection Objects.

In a precise manner we can use Mapping level Parameter and Variables as and when required. For example $$LOAD_SRC, $$LOAD_CTRY, $$COMISSION, $$DEFAULT_DATE, $$CDC_DT.

Parameterize Source Target Table and Owner Name

Situation may arrive when we need to use a single mapping from various different DB Schema and Table and load the data to different DB Schema and Table. Condition provided the table structure is the same.

A practical scenario may be we need to load employee information of IND, SGP and AUS and load into global datawarehouse. The source tables may be orcl_ind.emp, orcl_sgp.employee, orcl_aus.emp_aus.

So we can fully parameterize the Source and Target table name and owner name.

  • $Param_Src_Tablename
  • $Param_Src_Ownername
  • $Param_Tgt_Tablename
  • $Param_Tgt_Ownername

The Parameterfile:-

[WorkFolder.WF:wf_Parameterize_Src.ST:s_m_Parameterize_Src]  $DBConnection_SRC=Info_Src_Conn  $DBConnection_TGT=Info_Tgt_Conn  $Param_Src_Ownername=ODS  $Param_Src_Tablename=EMPLOYEE_IND  $Param_Tgt_Ownername=DWH  $Param_Tgt_Tablename=EMPLOYEE_GLOBAL  

Check the implementation image below:

Source Tablename and Ownername

Target Tablename and Ownername

Parameterize Source Qualifier Attributes

Next comes what are the other attributes we can parameterize in Source Qualifier.

  • Sql Query: $Param_SQL
  • Source Filter: $Param_Filter
  • Pre SQL: $Param_Src_Presql
  • Post SQL: $Param_Src_Postsql

If we have user-defined SQL statement having join as well as filter condition, its better to add a $$WHEREclause at the end of your SQL query. Here the $$WHERE is just a Mapping level Parameter you define in your parameter file.

In general $$WHERE will be blank. Suppose we want to run the mapping for todays date or some other filter criteria, what you need to do is just to change the value of $$WHERE in Parameter file.

$$WHERE=AND LAST_UPDATED_DATE > SYSDATE -1   [WHERE clause already in override query]  OR   $$WHERE=WHERE LAST_UPDATED_DATE > SYSDATE -1   [NO WHERE clause in override query]  

Parameterize Target Definition Attributes

Next what are the other attributes we can parameterize in Target Definition.

  • Update Override: $Param_UpdOverride
  • Pre SQL: $Param_Tgt_Presql
  • Post SQL: $Param_Tgt_Postsql
$Param_UpdOverride=UPDATE $$Target_Tablename.EMPLOYEE_G SET   ENAME = :TU.ENAME, JOB = :TU.JOB, MGR = :TU.MGR, HIREDATE = :TU.HIREDATE,   SAL = :TU.SAL, COMM = :TU.COMM, DEPTNO = :TU.DEPTNO  WHERE EMPNO = :TU.EMPNO  

Parameterize Flatfile Attributes

Now lets see what we can do when it comes to Source, Target or Lookup Flatfiles.

  • Source file directory: $PMSourceFileDir\ [Default location SrcFiles]
  • Source filename: $InputFile_EMP
  • Source Code Page: $Param_Src_CodePage
  • Target file directory: $$PMTargetFileDir\ [Default location TgtFiles]
  • Target filename: $OutputFile_EMP
  • Reject file directory: $PMBadFileDir\ [Default location BadFiles]
  • Reject file: $BadFile_EMP
  • Target Code Page: $Param_Tgt_CodePage
  • Header Command: $Param_headerCmd
  • Footer Command: $Param_footerCmd
  • Lookup Flatfile: $LookupFile_DEPT
  • Lookup Cache file Prefix: $Param_CacheName

Parameterize FTP Connection Object Attributes

Now for FTP connection objects following are the attributes we can parameterize:

  • FTP Connection Name: $FTPConnection_SGUX
  • Remote Filename: $Param_FTPConnection_SGUX_Remote_Filename [Use the directory path and filename if directory is differnt than default directory]
  • Is Staged: $Param_FTPConnection_SGUX_Is_Staged
  • Is Transfer Mode ASCII:$Param_FTPConnection_SGUX_Is_Transfer_Mode_ASCII

Parameterization of Username and password information of connection objects are possible with$Param_OrclUname.

When it comes to password its recommended to Encrypt the password in the parameter file using thepmpasswd command line program with the CRYPT_DATA encryption type.

Using Parameter File

We can specify the parameter file name and directory in the workflow or session properties or in the pmcmd command line.

We can use parameter files with the pmcmd startworkflow or starttask commands. These commands allows us to specify the parameter file to use when we start a workflow or session.

The pmcmd -paramfile option defines which parameter file to use when a session or workflow runs. The -localparamfile option defines a parameter file on a local machine that we can reference when we do not have access to parameter files on the Integration Service machine

The following command starts workflow using the parameter file, param.txt:

pmcmd startworkflow -u USERNAME -p PASSWORD   -sv INTEGRATIONSERVICENAME -d DOMAINNAME -f FOLDER  -paramfile 'infa_shared/BWParam/param.txt'   WORKFLOWNAME  

The following command starts taskA using the parameter file, param.txt:

pmcmd starttask -u USERNAME -p PASSWORD   -sv INTEGRATIONSERVICENAME -d DOMAINNAME -f FOLDER  -w WORKFLOWNAME -paramfile 'infa_shared/BWParam/param.txt'   SESSION_NAME  

Workflow and Session Level Parameter File

When we define a workflow parameter file and a session parameter file for a session within the workflow, the Integration Service uses the workflow parameter file, and ignores the session parameter file. What if we want to read some parameters from Parameter file at Workflow level and some defined at Session Level parameter file.

The solution is simple:

  • Define Workflow Parameter file. Say infa_shared/BWParam/param_global.txt
  • Define Workflow Variable and assign its value in param_global.txt with the session level param file name. Say $$var_param_file=/infa_shared/BWParam/param_runtime.txt
  • In the session properties for the session, set the parameter file name to this workflow variable.
  • Add $PMMergeSessParamFile=TRUE in the Workflow level Parameter file.

Content of infa_shared/BWParam/param_global.txt

[WorkFolder.WF:wf_runtime_param]  $DBConnection_SRC=Info_Src_Conn  $DBConnection_TGT=Info_Tgt_Conn  $PMMergeSessParamFile=TRUE  $$var_param_file=infa_shared/BWParam/param_runtime.txt  

Content of infa_shared/BWParam/param_runtime.txt

[WorkFolder.wf:wf_runtime_param.ST:s_m_emp_cdc]  $$start_date=2010-11-02  $$end_date=2010-12-08  

The $PMMergeSessParamFile property causes the Integration Service to read both the session and workflow parameter files.

Scope of Informatica Parameter File

Scope of Informatica Parameter File

Take a quick look on how we can restrict the scope of Parameters by changing the Parameter File Heading section.

  1. [Global] -> All Integration Services, Workflows, Worklets, Sessions.
  2. [Service:IntegrationService_Name] -> The Named Integration Service and Workflows, Worklets, Sessions that runs under this IS.
  3. [Service:IntegrationService_Name.ND:Node_Name]
  4. [Folder_Name.WF:Workflow_Name] -> The Named workflow and all sessions within the workflow.
  5. [Folder_Name.WF:Workflow_Name.WT:Worklet_Name] -> The Named worklet and all sessions within the worklet.
  6. [Folder_Name.WF:Workflow_Name.WT:Worklet_Name.WT:Nested_Worklet_Name] -> The Named nested worklet and all sessions within the nested worklet.
  7. [Folder_Name.WF:Workflow_Name.WT:Worklet_Name.ST:Session_Name] -> The Named Session.
  8. [Folder_Name.WF:Workflow_Name.ST:Session_Name] -> The Named Session.
  9. [Folder_Name.ST:Session_Name] -> The Named Session.
  10. [Session_Name] -> The Named Session.



Naming Conventions for User-Defined Session Parameters

Naming Conventions for User-Defined Session Parameters

Parameter Type

Naming Convention

Database Connection

$DBConnectionName

Source File

$InputFileName

Target File

$OutputFileName

Lookup File

$LookupFileName

Reject File

$BadFileName

Source/TableName

$ParamName