Wednesday, March 10, 2010

EBCDIC file handling

Sourcing the EBCDIC file from mainframes to Informatica can be a harder job! Verifying and managing the Packed decimals and the zoned decimals are as easy as the normal flat file handling. You should be in a position to know various flat file options to have it done!
In informatica to read an EBCDIC file VSAM source file strucure is used. Where we can prepare EBCDIC file from the flat file by the tool called 'Ultraedit'. To prepare from ASCII, first need to convert to EBCDIC format. Then to remove null spaces convert it to hex mode and serch and remove for "0D 25" with null. After that again return it to EBCDIC mode and this file with .TRX extension you can use for testing purpose.


With Regards, Sanket
Using a well known file enditor VEDIT ,the EBCDIC file can directly be edited without translating it and then can be transferred back to a mainframe computer. For transfering to a PC or a UNIX system particularly to a database such as SQL or MS Access, using VEDIT, the EBCDIC file can be translated to ASCII before transfer. The EBCDIC files that do not contain packed-decimal fields are particularly easy to translate to ASCII using the VEDIT.

Informatica Metadata Repository tables

While browsing through the Informatica rep and opb tables, we are usually stuck up as we do not understand what the widget_id and widget_types are for.
Below is the list of widget_types. This data would help us ease the handling of metadata.
Widget Ids and transformation types
widget_type
Type of transformation
1Source
2Target
3Source Qualifier
4Update Strategy
5expression
6Stored Procedures
7Sequence Generator
8External Procedures
9Aggregator
10Filter
11Lookup
12Joiner
14Normalizer
15Router
26Rank
44mapplet
46mapplet input
47mapplet output
55XML source Qualifier
80Sorter
97Custom Transformation

What are widget IDs and why do we require them?
Informatica maintains metedata regarding the mappings and its tranformations, sessions, workflows and their statistics. These details are maintained in a set of tables called OPB tables and REP tables.
The widget refers to the types of transformation details stored in these tables.

Port types in a transformation
As the above section details, widget is a transformation in metadata tables. To get the port type from repository table, below is the SQL snippet to use

select a.widget_id, decode(a.porttype, 1, 'INPUT',
3, 'IN-OUT',
2, 'OUT',
32, 'VARIABLE',
8, 'LOOKUP',
10, 'OUT-LOOKUP',
to_char(a.porttype)) Port_Type
from opb_widget_field a;

If you want to know the mapping name, then match the widget_id against the widget_id of opb_widget_inst and then pull the mapping_id which can be mapped against mapping_id in opb_mappings table. If you want to know the Folder name, then map the subject_id from opb_mappings to that of subj_id in OPB_SUBJECTS table to get the subject_name.

Expressions and SQL overrides in a transformation
OPB_EXPRESSION is the table that stores all the expressions in metadata.
To associate an expression to a field in a transformation, OPB_WIDG_EXPR is the table to be used.

select g.expression
from opb_widget_expr f,
opb_expression g
where f.expr_id = g.expr_id

SQL overrides can be in Source Qualifiers and Lookup transformations.

To get the SQL Override from metadata, check REP_WIDGET_ATTR.ATTR_VALUE column.

Sunday, March 7, 2010

Differences between Advanced External Procedure and External Procedure Transformations

Advanced External Procedure Transformation

Advanced External Procedure transformation is an Active and Connected transformation. It operates in conjunction with procedures, which are created outside of the Designer interface to extend PowerCenter/PowerMart functionality. It is useful in creating external transformation applications, such as sorting and aggregation, which require all input rows to be processed before emitting any output rows.

External Procedure Transformation

External Procedure transformation is an Active and Connected/UnConnected transformations. Sometimes, the standard transformations such as Expression transformation may not provide the functionality that you want. In such cases External procedure is useful to develop complex functions within a dynamic link library (DLL) or UNIX shared library, instead of creating the necessary Expression transformations in a mapping.

Differences between Advanced External Procedure and External Procedure Transformations:

External Procedure returns single value,
whereas Advanced External Procedure returns multiple values.

External Procedure supports COM and Informatica procedures
whereas AEP supports only Informatica Procedures.

Difference between Connected and UnConnected Lookup Transformation

Difference between Connected and UnConnected Lookup Transformation:

Connected lookup receives input values directly from mapping pipeline
whereas UnConnected lookup receives values from: LKP expression from another transformation.

Connected lookup returns multiple columns from the same row
whereas UnConnected lookup has one return port and returns one column from each row.

Connected lookup supports user-defined default values
whereas UnConnected lookup does not support user defined values.

Informatica 7 vs 8 (architecture level)

The architecture of Power Center 8 has changed alot;
PC8 isservice-oriented for modularity, scalability and flexibility.

The Repository Service and Integration Service (as replacementfor Rep Server and Informatica Server) can be run on different computers in a network (so called nodes), even redundantly.

Management is centralized, that means services can be startedand stopped on nodes via a central web interface.

Client Tools access the repository via that centralized machine,resources are distributed dynamically.

Running all services on one machine is still possible, ofcourse.

Differences between informatica 6,7,8 versions (application level)

6 to 7
union transformation
lookup on flat files
we will connect workflow through designer itself

7 to 8
java transformation
sql transformation
HTML transformation

8 version contains integration services
many source files are added like
webservices,
tibco,
webmethods
user defined functions ...