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