Sample parameter file and using parameter file in Informatica

Sample parameter file and using parameter file in Informatica

Below is a sample of a simple parameter file. All the global variables used by all the sessions are defined under [Global] and the session specific variable overrides are defined under session names.

-----------------------------------------------------

[Global]
$DBConnection_VSTP=VSTPDW_HIST_US

[s_MYSQL_EXT_Load5]
$$BATCH_KEY=204
$$YEAR=2016
$InputFile_Name=$$PMRootDir\SrcFiles\SRC1.EXT.G2016.txt

[s_SRC2_EXT_Load]
$$BATCH_KEY=204
$$YEAR=2016
$InputFile_Name=$$PMRootDir\SrcFiles\SRC2.EXT.G2016.txt

[s_SRC3_EXT_Load]
$$BATCH_KEY=204
$$YEAR=2016
$InputFile_Name=$$PMRootDir\SrcFiles\SRC2.EXT.G2016.txt

[s_SRC4_EXT_Load]
$$BATCH_KEY=204
$$YEAR=2016
$InputFile_Name=$$PMRootDir\SrcFiles\SRC14.EXT.G2016.txt
-------------------------------------------------------------------------

The parameter file can be set at session level or at workflow level. The worklevel parameter applies to all the session unless it is over riden by another parameter file at session level.

Performance tuning of Informatica Big Data Edition Mapping

Performance tuning of Informatica Big Data Edition Mapping

Below are the list of performance tuning steps that can be done in Informatica Big Data Edition:


1)  When using a look up transformation only when the lookup table is small. Lookup data is copied to each node and hence it is slow.


2) Use Joiners instead of lookup for large data sets.


3) Join large data sets before small datasets. Reduce the number of times the large datasets are joined in Informatica BDE.

4) Since Hadoop does not allow updates, you will have to rebuild the target table whenever the record is updated in a target table. Instead of rebuilding  the whole table, consider rebuilding only the impacted partitions.


5) Hive slower with any non string data type. It needs to create temp tables to do the conversion to and from the non string data type to string data type. Use non string data type only when required.


6) Use the data type precision close the actual data. Using higher precision slows down the performance of Informatica BDE.


7) Map only the ports that are required in the mapping transformation or loaded to target. Less number of ports means better performance and less data reads.

How to run stored procedure in Informatica cloud

How to run stored procedure in Informatica cloud?

There are multiple ways to run stored procedure in Informatica cloud.

If you want to call a stored procedure by passing an input value and reading the output from the stored procedure and process it in a mapping then it is better to use SQL transformation in Informatica cloud mapping designer as shown in picture below:

If you want to read the output of a stored procedure as a source in a mapping then you can call the stored procedure a query in the source as shown in screenshot below. The exec sp_help stored procedure is called in the source query. The stored procedure should return some data for this method to work. You might have to define the output variables in the stored procedure.

If you want to just execute a stored procedure once before or after executing a mapping then you can call the stored procedure a pre or post processing command in the mapping configuration as shown in the screenshot below. SQLCMD of sql server is used to execute the stored procedure. The output of the stored procedure is written to an output file. -b flag is used to return error code and make the preprocessing command fail when the stored procedure fails. Use session property  Preprocessing command failure and set it to stop if you want failure to stop the processing of other jobs.