Difference between Stop and Abort in Informatica

Abort Request:
1. User sends the abort request either through workflow monitor or PMCMD utility.

2. It immediately kills the DTM process and user been notified for session aborted.
3. Later, it fires a request to the source database, waits for the source database to roll-back.
4. It fires a request to the target database, waits for the target database to roll-back.
5. It fires a request to the Informatica repository database to release the locks on session object.


Note: Each session uses SHARED/LOCKED memory blocks, but the ABORT function kills just the CODE threads, leaving the memory LOCKED and SHARED and allocated.

Stop Request:
1. User sends the stop request either through workflow monitor or PMCMD utility.
2. It fires a request to the source database, waits for the source database to roll-back.
3. It then cleans up the buffers in memory by releasing the data.
4. It fires a request to the target database, waits for the target database to roll-back.
5. It fires a request to the Informatica repository database to release the locks on session object.
6. It immediately kills the process DTM process and user notified for session aborted.

Note: The bigger the data in the source query, the more time it takes to "roll-back" the source query, to maintain transaction consistency in the source database. So when ever a request is passed, it waits for the entire process to roll-back before notifying the user.

Challenges faced during 711 to 861 migration (Informatica)

Infrastructure:
1. Latency issue: We had a performance impact when our target database for the current setup was residing outside the application server. (20% increase in runtime)
2. Character conversion issue: Make sure your code page setting across the layers like application (Informatica) --> System runtime(OS) --> database (Oracle) are uniform.
We migrated from IBM AIX to Solaris server, because of change in system runtime library we found their were few difference in character conversion.
3. Memory consumption: We noticed a increase in memory consumption for any DTM process (5-10 % Increase) running in current setup(861), reason may be we migrated form 32 bit machine to 64 bit machine.
4. Increase in repository database space required for 861, when compared against similar number of Informatica objects in 711.
711 ---- Approx. 900 MB ( 1800 Workflows)
861 ---- Approx. 1.3 GB ( 1800 Workflows)
5. The growth of Informatica repository logging were higher in case of 861.
711 -- Approx. 650 MB (For 3 Months)
861 -- Approx. 1 GB (For 3 Months)

Database:
1. In some cases the query performance was very slow, so we were forced to change the query used within informatica session override(after the upgrade).

Informatica:
After up-gradation from Informatica 7.1 to Informatica 8.6 we may get the below issues, so we requested our developers to cross check the mapping or workflow has part of their testing.
1. Fixed width flat files are read incorrectly shifting the alignments of some of the columns.
2. We may get below error when session properties treat Source Rows as: Update enabled.
Reason would be updating column datatype of VARCHAR2 with a precision of 4000.
WRT_8118 ERROR: Target table [MCK_CHCK_ADDR_TB] does not allow UPDATE Row # [0] in bad file
3. Session with an XML target and the Validate Target option enabled writes invalid XML when the xs:choice element is included in the XSD file.
ERROR WRITER_1_*_1 XMLW_31205 Warning: Failed to validate element [EmpID], group [X_Root], row [0], error [Value is NULL but element
is not nillable.]. Discarding row.
So please revalidate the xsd again.
4. When using a pass through port in XML Generator transformation, it gives 0 or null in the output.
5. Cross check the Dynamic Lookup transformation, ensure that the ports are projected out of the Lookup transformation in the mappings.
This will ensure that they are part of data cache.
6. When starting a workflow from the Workflow Manager Navigator pane, the Workflow Manager first fetches each and every session before starting the workflow.
Workaround: In the Workflow Manager, start the workflow from Workflow Designer pane instead of starting from Navigator pane.
7. Large values in a fixed width flat file source BigInt column are skipped when read by a PowerCenter 8.6 session.
To resolve this, change the column in the file source definition to String(19) and convert it in the mapping to BigInt using the TO_BIGINT function.

Line sequential buffer length (Informatica)

I've configure Line sequential buffer length parameter to 4096 in my session.In my log session file I've this :FR_3071 Maximum Line sequential buffer length is 4098.
I don't understand what this line mean ?
Note: Query was taken from a Informatica related forum.
Reason:
Suppose you take below sample record as your input feed.
"7525 XX XXXXXX*0*10S*QQQQ*XX*YYY YYYY*US*USD*Common Stock*N.A.*XXXXX*JT*N.A.* *Machinery-Diversified"
Total length of the record is 104 characters (Bytes) and suppose if you have defined the "Line sequential buffer length" parameter to 100,then session would fail for
FR_3071 Maximum Line sequential buffer length is 100. (6.x/7.x version)
FR_3093 Record length [104] is longer than line sequential buffer length [100]. (From 8.x version)
How to calculate Line sequential buffer length:
Some how you need to find out the expected maximum record length and add 30% more, define that as a value for "Line sequential buffer length", just to neutralize a common solution irrespective of your code page setting. Because the way length of a character treated in ASCII and UNICODE are different.
Solution:
To avoid the above error change your default setting for "Line sequential buffer length".
Open the session properties.
Go to the General tab.
Select the Advanced Options button in the lower left corner.
Increase the Line Sequential Buffer Length setting so that it is larger than the largest row in the source file (including the header row).

What if you get a flat file that has no line feed (LF) or carriage return (CR) characters to mark the end of the record ?
1. Define a fixed width source definition.
2. Set Line Sequential buffer length to the length of the record.
3. In the flat file properties set the Number of Bytes to Skip Between Records to 0.

How to read zipfile on fly without unzipping(Informatica)

Reading source zipfile on fly without unzipping it to a physical file

When we have a source file in zip format we can read it on fly(Generating Data) and informatica reader reads the generated stream. We have few options in informatica powercenter 8.x wherein we define commands or run a script to generate or display data. Using this method we can pre-process file while reading.

Define the following under Session->Mapping Tab->Sources->File Reader

Input Type : Command

Command Type : Command Generating Data

Command : gzip -cd $PMSourceFileDir/filename.txt.zip

How to add a reference port within XML transformation (Informatica)

Adding reference port in XML transformation:

1. Add a port that needs to be referenced.

2. Go to xml editor and right click on the xml view.

3. Select “Add a Reference Port” , new window pops up their select the reference port.

Informatica 8.6 Repository queries

Repository query to fetch all the workflows which are not enabled for “Write Backward Compatible Session Log File”
Workflow Property:
select a.task_ID,TASK_NAME,c.SUBJ_NAME,ATTR_ID,ATTR_NAME,ATTR_VALUE
from REP_TASK_ATTR a,OPB_TASK b,OPB_SUBJECT c
where
a.TASK_ID=b.TASK_ID
and b.subject_id=c.SUBJ_ID
and a.task_type_name='Workflow'
and ATTR_ID='12'
and c.SUBJ_NAME ="folder name"
and ATTR_VALUE='0'
and TASK_NAME ="workflow name"
order by SUBJ_NAME,TASK_NAME
Session Property:
select TASK_NAME,c.SUBJ_NAME,ATTR_ID,ATTR_NAME,ATTR_VALUE
from REP_TASK_ATTR a,OPB_TASK b,OPB_SUBJECT c
where
a.TASK_ID=b.TASK_ID
and b.subject_id=c.SUBJ_ID
and a.task_type_name='Session'
and ATTR_ID='17'
and c.SUBJ_NAME ="folder name"
and ATTR_VALUE='0'
and b.TASK_TYPE='68'
and b.RU_PARENT_ID in (
select TASK_ID from OPB_TASK a,OPB_SUBJECT b
where
a.subject_id=b.SUBJ_ID
and TASK_NAME ="workflow name"
and b.SUBJ_NAME ="folder name")
order by SUBJ_NAME,TASK_NAME