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

Repository Query

Query to find list of sessions for which the target is set to Truncate & Reload

It will display both reusable sessions and non-reusable sessions inside the workflow
Run the following query in the Repository Database

SELECT S.SUBJ_NAME, TI.INSTANCE_ID, TI.INSTANCE_NAME, W.TASK_NAME WORKFLOW_NAME
FROM OPB_EXTN_ATTR EA, OPB_TASK_INST TI , OPB_TASK W, OPB_SUBJECT S
WHERE SESSION_ID IN (31583, 31582, 31589)
AND ATTR_ID = 9 AND ATTR_VALUE = 1
AND TI.INSTANCE_ID = SESSION_INST_ID
AND EA.WORKFLOW_ID = W.TASK_ID
AND W.SUBJECT_ID = S.SUBJ_ID AND SESSION_INST_ID != 0
UNION
SELECT DISTINCT S.SUBJ_NAME, TI.TASK_ID, TI.TASK_NAME, NULL WFLOW_NAME
FROM OPB_EXTN_ATTR EA , OPB_TASK TI, OPB_SUBJECT S
WHERE SESSION_INST_ID = 0 AND SESSION_ID IN (31583, 31582, 31589)
AND ATTR_ID = 9 AND ATTR_VALUE = 1
AND EA.SESSION_ID = TI.TASK_ID
AND TI.SUBJECT_ID = S.SUBJ_ID


Informatica : SQLOverride in the Session
select wad.attr_value override
from opb_session ses,opb_mapping m
,OPB_SWIDGET_INST SW,OPB_WIDGET_ATTR WA, OPB_WIDGET_ATTR WAd
where m.mapping_id=ses.mapping_id
AND SES.SESSION_ID=SW.SESSION_ID
AND SW.WIDGET_ID = WA.WIDGET_ID(+)
AND SW.instance_id= WA.INSTANCE_ID(+)
and wa.session_task_id (+) =sw.session_id
AND SW.WIDGET_ID = WAd.WIDGET_ID
AND 0!= WAd.INSTANCE_ID
and wa.widget_type=3
and wad.attr_id=1
AND SES.SESSION_ID=(
SELECT ti.task_id
FROM
OPB_TASK T,
OPB_SUBJECT S,
OPB_WFLOW_DEP DEP,
OPB_TASK_INST TI
WHERE
T.SUBJECT_ID = S.SUBJ_ID
AND S.SUBJ_NAME = 'JIM' -- folder name
AND T.TASK_NAME = 'wks_DELETE' -- workflow name
AND TI.INSTANCE_NAME='s_mD_MRPDESIGNATOR_DIM' -- session name
AND T.TASK_ID = DEP.WORKFLOW_ID
AND DE

Repository SQL

SQL to check if the Verbose Data is specified for any mapping or session

--- Reusable Sessions which have Verbose data enabled.
SELECT S.SUBJ_NAME, T.TASK_ID, T.TASK_NAME, SI.INSTANCE_NAME, SWA.ATTR_VALUE actual_value, T.*
-- SI.WIDGET_ID, SI.WIDGET_TYPE, SI.SESS_WIDG_INST_ID, A.ATTR_NAME, A.ATTR_VALUE default_value,
FROM OPB_SUBJECT S, OPB_TASK T, OPB_SWIDGET_INST SI, OPB_SWIDGET_ATTR SWA, OPB_ATTR A
WHERE S.SUBJ_ID = T.SUBJECT_ID
AND T.IS_REUSABLE=1 AND T.TASK_ID =SI.SESSION_ID
AND SI.SESS_WIDG_INST_ID = SWA.SESS_WIDG_INST_ID
AND SI.SESSION_ID = SWA.SESSION_ID
AND SI.WIDGET_TYPE = A.OBJECT_TYPE_ID AND SWA.ATTR_ID = A.ATTR_ID AND A.ATTR_NAME ='Tracing Level'
AND SUBJ_NAME LIKE '%FIX%' --Specify the informatica folder name


-- Reusable Sessions in Workflow / worklet which have Verbose data enabled.
SELECT S.SUBJ_NAME,W.TASK_NAME WORKFLOW_NAME, T.TASK_ID, T.INSTANCE_NAME, SI.INSTANCE_NAME, SWA.ATTR_VALUE actual_value,
-- SI.WIDGET_ID, SI.WIDGET_TYPE, SI.SESS_WIDG_INST_ID, A.ATTR_NAME, A.ATTR_VALUE default_value,
FROM OPB_SUBJECT S,OPB_TASK W, OPB_TASK_INST T, OPB_SWIDGET_INST SI, OPB_SWIDGET_ATTR SWA, OPB_ATTR A
WHERE S.SUBJ_ID = W.SUBJECT_ID
AND W.TASK_ID = T.WORKFLOW_ID
AND T.TASK_ID =SI.SESSION_ID
AND SI.SESS_WIDG_INST_ID = SWA.SESS_WIDG_INST_ID
AND SI.SESSION_ID = SWA.SESSION_ID
AND SI.WIDGET_TYPE = A.OBJECT_TYPE_ID AND SWA.ATTR_ID = A.ATTR_ID AND A.ATTR_NAME ='Tracing Level'
AND SUBJ_NAME LIKE '%FIX%' --Specify the informatica folder name


--- Mappings which have Verbose data enabled.
SELECT S.SUBJ_NAME, M.MAPPING_NAME, WI.INSTANCE_NAME, WI.WIDGET_TYPE, A.ATTR_NAME, WA.ATTR_VALUE actual_value
FROM OPB_SUBJECT S,OPB_MAPPING M, OPB_WIDGET_INST WI, OPB_WIDGET_ATTR WA, OPB_ATTR A
WHERE S.SUBJ_ID = M.SUBJECT_ID
AND M.MAPPING_ID = WI.MAPPING_ID
AND WI.WIDGET_ID = WA.WIDGET_ID
AND wi.WIDGET_TYPE = A.OBJECT_TYPE_ID
AND wa.ATTR_ID = A.ATTR_ID
AND A.ATTR_NAME ='Tracing Level' AND WA.ATTR_VALUE != '2'
AND SUBJ_NAME LIKE '%FIX%' --Specify the informatica folder name


SQL to get the Recovery Strategy Attribute of a session

SELECT s.subj_name, t.task_name, ta.attr_id, ta.attr_value
FROM opb_subject s, opb_task t, opb_Task_attr ta
WHERE s.subj_id = t.subject_id
AND t.task_id = ta.task_id AND ta.attr_id = 18
AND t.task_name LIKE 's_mFIX507%'
ORDER BY 3

SELECT
s.subj_name,w.task_name worfklow, t.instance_id , ta.instance_id ,t.instance_name, ta.attr_id, ta.attr_value
FROM opb_subject s, opb_Task w, opb_task_inst t, opb_Task_attr ta
WHERE s.subj_id = w.subject_id AND w.task_id = t.workflow_id
AND t.task_id = ta.task_id AND (ta.instance_id = t.instance_id OR ta.instance_id = 0)
AND ta.attr_id = 18 AND ta.attr_value != '2'
--AND t.instance_name LIKE 's_mFIX507%'
ORDER BY 3

Version 8 Flat file Enhancements

Merge Type
Not at all
Sequentially
Concurrently
File list

Append if Exists
Header Options

Header Command
Footer Command

Input Type
- File
- Command
- Command Type

Target Command
- Infa writs data to command instead of target flat file


FTP :-
- Source can be File List
- Merge files concurrently on the FTP