Mapping Designer: Copy-Paste of Transformations does not seem to work (no copied object visible)

Usually, the past processes takes an enormous amount of time, but the process is successful even though the new transformation is not visible at the beginning. You have to look for it by moving the visible peace of the mapping around e. g. by the overview window. Usually the new object is near or over the original. But it can happen, that it is at the top left corner or in some cases anywhere else.
If this does not help you can re-arrange the layout by the context menu or close the folder or even disconnect from the repository. The you will find them as usual for unconnected transformations at the top left corner of the mapping.
I assume that it has something to do with memory. With me, it happened when my system memory load was heavy especially when I worked on a big mapping. I also assume the mapplet designer behaves the same.

ORA-08005: specified row does not exist / ORA-08103: object no longer exists?!?

I lately came suddenly across mentioned Oracle errors after bringing a newly develop workflow to integration and acceptance test. We first had deadlocks and removed parallel runs of sessions eventually getting mentioned errors. I noticed after a good deal of search that the connections have not been adapted to the new environment. After having fixed the connections, all runs smooth again. We did not (yet) re-parallelise however.

Second fractions: What is the code, characters, string what so ever I need to use to handle second fractions in timestamps?

It is 'US', guess it stands for microseconds (μs) so probably PowerCenter supports 'only' up to 6 digits after the decimal point. An appropriate ISO format string could read thus 'YYYY-MM-DD HH24:MI:SS.US'.

Chopped off characters

Setup

There is this Oracle table we read from, let's say:

create table TAB (TEXT varchar2(20 char));

It contains the text in the next line.
Der Text enthält äs.
If you count you will see its length is equal to precisely 20 characters. Our PowerCenter server runs Unicode UTF8. This also is the code page of the DB connection that is used by the source defined as varchar2 with length 20.

Problem

The end of the text gets chopped off.

Answer

Apparently, the source qualifier interprets the length as bytes such that the original text, containing multi-byte characters (ä) does not fit into the definition. If you increase the port length of the source qualifier, the text does not get chopped - even if the length of the port of the source definition is kept at 20!

Parameters/variables in Lookups

Hm, rather no good idea as far as my experience goes.

My setup

My setup is a mapplet that uses a lookup and in this lookup I use a Lookup Source Filter which in turn uses mapplet parameters with default to define values to match against. I wanted therewith reduce the cache size and efficiency of the processing.

Result

The parameters were not replaced by their respective value defined in the parameter dialog of the mapplet menu.

Explanation

It seems that the parameter replacement, for no apparent reason to me, omits Lookup Source Filters.

Workaround

Use Lookup Sql Override to get parameters into the where clause of the SQL for the lookup cache.

User defined session parameter scope

There is a number of session parameter name patterns that allow to use complying session parameters to get used however deep into a cascade of worklets without defining them as worklet parameters:

• $InputFile<user string>
• $OutputFile<user string>
• $BadFile<user string>
• $LookupFile<user string>
• $DBConnection<user string>
• $LoaderConnection<user string>
• $FTPConnection<user string>
• $QueueConnection<user string>
• $AppConnection<user string>
• $Param<user string>

This allows for instance to provide a mapping with the name of the source file that it is loading easily and robust:
$InputFile_MyFirstFile=my_first_file_at_this_date.csv
$$InputFile_MyFirstFile=$InputFile_MyFirstFile

Is it worth to put a sorter in front of an aggregator to use sorted input in the latter no matter what?

In short... yes, to my great surprise. My personal argumentation against it was (sadly wrong):

• Number of sort operations: On sort takes place in any case. Either in AGG or in SRT, for the case the sorted input option of the AGG is switched on. -> no advantage for the SRT-AGG-combination
• Data flow: An AGG can forward a data group one by one only, if it gets pre-sorted data and the according switch is active (SRT-AGG-Kombination), otherwise it forwards all data at the same time, after having processed the last incoming record (AGG only). Later behavior is the same for SRT, such that down stream processing waits until the last record has passed such element. -> no advantage for the SRT-AGG-combination
• The SRT is an additional element, and data needs to be transported to and from. -> disadvantage for SRT-AGG-combination if the order of the data does not get used down stream again

However, go for the Oracle sorting if you can. I do not know whether other databases also sort as efficiently.

Setup

Test case code

A) Pure aggregator

B) Sorter in front of aggregator

C) As A but the source qualifier delegates sorting to the database

Results

Interpretation

• Wow, Oracle sorts it ways faster that PowerCenter!
• The first couple of thounds of rows the pure aggregator was faster but butchered by the sorter solution even with only 20'000 records.
• I have not the faintest idea why Informatica does not use the sorting mechanism of the Sorter in the aggregator as well being that much faster. I guess if you have an application for an aggregator to crunch on only a couple of thousands of records you do not care about the aggregator speed anyway.