Informatica session fails with RR_4035 : SQL Error [ ORA-29275: partial multibyte character

Informatica session fails with RR_4035 : SQL Error [ ORA-29275: partial multibyte character

The source database or the target database has multibyte characters.
To resolve this you have to change the Code Page of the Relational Connection

Steps :-
1)Open Workflow Manager
2) Click on the Relational Connections and choose the relational connection
3) Change the Code Page to : UTF-8 encoding of Unicode.

If the error occurs in any other tool, then make sure the connection you make to the database is with the correct code page which supports Unicode.

Informatica v7 High precision data

Informatica v7 High precision data

SELECT TO_CHAR(avg_rate) chris
, LENGTH(TO_CHAR(avg_rate)) len , LENGTH(SUBSTR(TO_CHAR(avg_rate),INSTR(TO_CHAR(avg_rate),'.'))) DEC
,LENGTH(TO_CHAR(avg_rate)) - LENGTH(SUBSTR(TO_CHAR(avg_rate),INSTR(TO_CHAR(avg_rate),'.'))) b4
FROM GL_TRANS_RATE_TST r

0.024967355	40	40	0
12345.12346	21	16	5
123456789.1	25	16	9
122.1001221	14	11	3
1.350074254	12	11	1
123457890	9	9	0
1.23457E+14	25	10	15
1.23457E+17	28	10	18
48.34354082	39	37	2

Test results after running the session
(The datatypes specified are defined only in the mapping. In the target table it is defined as Oracle NUMBER datatype )

SELECT set_of_books_id,TO_CHAR(average_rate) ris
, LENGTH(TO_CHAR(average_rate)) len , LENGTH(SUBSTR(TO_CHAR(average_rate),INSTR(TO_CHAR(average_rate),'.'))) DEC
, LENGTH(SUBSTR(TO_CHAR(average_rate),1,INSTR(TO_CHAR(average_rate),'.'))) before1
FROM conv_ref_tst
ORDER BY 1

DECIMAL(28,12)

1	.0249673551830981	17	17	1
2	12345.123456789	15	10	6
3	123456789.123457	16	7	10
4	122.1001221001	14	11	4
5	1.3500742541	12	11	2
6	123457890	9	9
7	123456789012345	15	15
8	123456789012346000	18	18
9	48.3435408203774	16	14	3

DECIMAL(28,12) WITH high PRECISION

1	.024967355183	13	13	1
2	12345.123456789012	18	13	6
3	123456789.123456789012	22	13	10
4	122.1001221001	14	11	4
5	1.3500742541	12	11	2
6	123457890	9	9
7	123456789012345.123456789	25	10	16
9	48.343540820377	15	13	3
9	48.3435408203774	16	14	3

DECIMAL 28,28 WITH high PRECISION

1

.0249673551830980992352499107

29

29

1

DECIMAL 15,15 WITH high PRECISION

1

.024967355183098

16

16

1

DECIMAL 28,15 WITH high PRECISION

1	.024967355183098	16	16	1
5	1.3500742541	12	11	2
9	48.343540820377357	18	16	3

DECIMAL 26,16 WITH high PRECISION

1	.0249673551830981	17	17	1
5	1.3500742541	12	11	2
9	48.3435408203773566	19	17	3

DECIMAL 40,28 WITH high PRECISION
DECIMAL 28,28

1	.0249673551830981	17	17	1
2	12345.123456789	15	10	6
3	123456789.123457	16	7	10
4	122.1001221001	14	11	4
5	1.3500742541	12	11	2
6	123457890	9	9
7	123456789012345	15	15
8	123456789012346000	18	18
9	48.3435408203774	16	14	3

Powercenter server processes decimals as doubles or decimals.
If you dont enable High precision, informatica powercenter treates teh data as doubl e with precision of 15.
To enable High precision, Decimal datatype in the mapping should have a precision of 16 to 28. Then select Enable High Precision in the session properties

Session Properties

Session Properties

General Options

Write backward compatible Session log file
Session log file name
Session log file directory
Parameter file name

Enable test load
Number of rows to test
- will be enabled only if you Enable Test Load

$Source connection value
$Target connection value

Target source rows as
- Insert / Update / Delete / Data driven
- If the mapping has update transformation then by default Data driven.

Commit Type [Target]
- Source / Target / User defined
Commit Interval
- commit after x number of records
Commit on End of file
Rollback transaction on errors

Recovery Strategy
- Restart Task / Fail task continue workflow / Resume from last check point

Java classpath

Performance -

DTM Buffer size [Auto]
- You can specify Auto or value. By default Integ service allocated 12MB

Collect performance data
- to collect performance data. you can use workflow monitor to see it

Write performance data to repository
- writes the performance details to the repository. you can use workflow monitor to see it

Incremental Aggregation
Reinitialize Aggregate data
Enable High precision
- to process Decimal datatype to a precision of 28

Session retry on deadlock
- applicable only for Normal loads
- In the Integ service you can specify number of deadlock retries and deadlock sleep time period.

Session sort order
- If the integration service runs in Ascii then it is Binary
- If it runs on Unicode, then it displays sort order based on the Integration service code page

Pushdown Optimization
- None / To Source /To Source with view / To Target / Full / Full with view / $PushdownConfig
-

Session Configuration

Session Configuration

Advanced -
Constraint based load ordering [Unchecked]
- To load data into the targets based on primary key foreign key relationship
Cache Lookup() Function - [Checked]
Default buffer block size [Auto]
-
Line Sequential Buffer Length [1024]
- Informatica reads a row from the file and holds it in the buffer. If the row is more than 1024 bytes, then increase the value.
Max Memory Allowed for Auto Memory attributes [512Mb]
Max percentage of Total memory allowed for Auto Mem Attributes [5]
Additional Concurrent pipelines for Lookup Cache Creation [Auto]

Log Options -
Save Sessions Log by [Session runs]
- Session runs - saves the number of sessions logs specifed
- Session timestamp - appends the timestamp to the session log file name.
Save Sessions log for these runs
- will be enabled only for Session Log by Session runs.
- specify the number of log files to store .0 means it will store only the current log file
- you can use $PMSessionLogCount

Error Handling -
Stop on errors
- Specify number of non-fatal errors to allow (reader, writer, dtm errors)
- 0 means it wont stop. you can use $PMSessionErrorThreshold service variable

Override tracing
On Stored Procedures error [Stop] - Stop / Continue
On Pre-session command task error [Stop] - Stop / Continue
On Pre-Post SQL error [Stop] - Stop / Continue


Error Log Type
Errot Log DB Connection
Error Log Table Name prefix
Error Log File Directory
Error Log File Name
Log Row Data
Log Source Row Data
Data column delimiter

Partitioning Options -
Dynamic Partitions [Disabled]
- Other options are Based on number of partitions , Based on source Partitioning, Based on number of nodes in the grid.
Number of Partitions [1]
- Will be enabled if you choose "Based on number of partitions"
- You can also specify $DynamicPartitionCount session parameter


Sessions on Grid -
Is Enabled [Unchecked]
- To run the sessions in a grid

Constraint based Loading

Constraint based Loading :-

It is an option available in the session.
If enabled, for each row generated by the source, it will load the record in the primary key table first, then it will load any foreign key tables. It applies only to the inserts.

- All the targets should have the same Active source
- You should define the relationships for the Target in the Warehouse Designer
- Targets shoule be in the same Target connection group
- Treat source as "Insert". You cannot use updates with constraint based loading


FullCBLOSupport = Yes/No
- It is a custom property. If you set it in the Integration service, it is applicable for all workflows. You can also set it in the session config object tab in the Custom Properties attributes.
- Allows you to load the changed data (updates and deletes) to be loaded to the target in the same transaction control unit (TCU).
- Your mapping should not contain any active transformation which changes the row id generated by the reader.