2015.06.19 버전
<? String vSess = (odiRef.getSession("SESS_NO").length()<7?odiRef.getSession("SESS_NAME").substring(odiRef.getSession("SESS_NAME").length()-5):odiRef.getSession("SESS_NO").substring(odiRef.getSession("SESS_NO").length()-7,4)) ?>
-----------------------------------------------------
문제점 : interface를 parallel 로 실행할 경우 work table 이름 중복 때문에 에러가 발생
해결방법 : work table이름을 다르게 한다.
접근방법 : java beanshell을 사용하여 세션번호를 템프테이블에 달아 식별가능하도록 만든다.
1. 사용하는 지식모듈의 첫 번째 명령에 다음 명령을 추가한다.
Step Name: Parallel Setup
Technology: Java Bean Shell
Code: <? String vSess = odiRef.getSession(“SESS_NO”)?>
2. Topology 의 해당 물리적 스키마의 템프테이블 부분을 수정.
E$_<?=vSess?>
C$_<?=vSess?>
I$_<?=vSess?>
완료 및 테스트 !
Two days ago Devendra talk to me about parallel executions in ODI and we are discussing the solution from Oracle Metalink that works but, at true, I never was stopped to analyse.
In face of that, I decided try something in a new way and it worked fine!
Let me share my 2 minutes approach.
Problem Description:
Oracle Data Integrator uses the target table name to build the work tables (I$, E$, C$) and, because of that if more then one interface that loads the same target table is executed at same time the work tables creation, insert and drop process got mixed and nothing works.
Solution:
The solution is pretty obvious… It is necessary to make the work table name variable and linked to each execution. The ODI path for that is the Session Number.
My approach:
All solutions that I saw until today uses an Oracle Data Integrator variable but got problems if the table name is near to the maximum name length allowed for the technology.
In face of that, I decide to use the ODI natural behavior to manage the lenght.
All that is necessary to do is:
1. Edit all your KM (IKM and LKM if some) and add a new step (move it to be the first step)
Step Name: Parallel Setup
Technology: Java Bean Shell
Code: <? String vSess = odiRef.getSession(“SESS_NO”)?>
Importante note: this change can be done to any KM that will not interfier with process not parallel
2. Go to Topology Module, in the Physical Schema that will have parallel execution (or in all if you wish) and change the Work Table Prefix to:
E$_<?=vSess?>
C$_<?=vSess?>
I$_<?=vSess?>
Well my friends… that is all! Any Parallel or Single execution can be executed with no conflicts between work table name or length name problem will be raise.
The best about it is be very simple!!!
See you all in next post!
Regards,
Cezar Santos
