Source to target loading using oracle and datastage
Moderators: chulett, rschirm, roy
We have created the Parallel job using Advanced Transformer and we were not able to use any of the Transformation functions in the job. Later we changed PX transformer with Basic Transformer and used functions and it worked out. But in Basic transformer we cannot use Type conversion functions which are available in PX transformer. We would appreciate if any one can suggest us any solutions.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Begin with a specification. There should be no reason not to use the parallel Transformer stage. If necessary you can create parallel routines that it can invoke.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
a naive workaround would be to try to use one basic and then a PX transformer or the other way around.
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Hi
Try one thing.
remove target oracle stage and use sequential file . see whether you are able to view data with sequential file . if you able to then use that sequential file to manually load in table using sql loader in command mode . so that you are sure probelm is not in transformer or sqlloader.
Thanks
Sanjay
Try one thing.
remove target oracle stage and use sequential file . see whether you are able to view data with sequential file . if you able to then use that sequential file to manually load in table using sql loader in command mode . so that you are sure probelm is not in transformer or sqlloader.
Thanks
Sanjay
mspanda wrote:We have created the Parallel job using Advanced Transformer and we were not able to use any of the Transformation functions in the job. Later we changed PX transformer with Basic Transformer and used functions and it worked out. But in Basic transformer we cannot use Type conversion functions which are available in PX transformer. We would appreciate if any one can suggest us any solutions.
Below are the cases which i tried,
My Table Structure is as below for Source,
EMP_SRC
EMPNO_SRC
ENAME_SRC
My Table Structure is as below for Target,
EMP_TGT
EMPNO_TGT
ENAME_TGT
Source/Target is Oracle
Case 1 :
Target Option is set to Load
Used PX tranformer
Created a PX job
This is one to one mapping with out any functions.
This works fine.
Case 2 :
Target Option is set to Load
Used PX tranformer
Created a PX job
We have used trim function for the Source Columns as below,
TRIM(EMPNO_SRC)
TRIM(ENAME_SRC)
This is throwing errors as below,
EMP_TGT:Describe failed because of a missing column for EMPNO_TGT
main_program: Could not check all operators because of previous error(s)
main_program: Creation of a step finished with status = FAILED.
Job EMPJOB aborted
Case 3 :
Target Option is set to Load
Used Basic tranformer
Created a PX job
We have used trim function for the Source Columns as below,
TRIM(EMPNO_SRC)
TRIM(ENAME_SRC)
This works fine.
Case 4 :
Target Option is set to Load
Used PX tranformer
Created a PX job
We have changed the source and target table column names to be similar.
We have used trim function for the Source Columns as below
TRIM(EMPNO_TGT)
TRIM(ENAME_TGT)
This works fine.
Since the case 2 is very crucial for me as all my existing jobs needs to use advanced functions which are available in PX tranformer.
Please suggest us any solutions.
Rgds,
My Table Structure is as below for Source,
EMP_SRC
EMPNO_SRC
ENAME_SRC
My Table Structure is as below for Target,
EMP_TGT
EMPNO_TGT
ENAME_TGT
Source/Target is Oracle
Case 1 :
Target Option is set to Load
Used PX tranformer
Created a PX job
This is one to one mapping with out any functions.
This works fine.
Case 2 :
Target Option is set to Load
Used PX tranformer
Created a PX job
We have used trim function for the Source Columns as below,
TRIM(EMPNO_SRC)
TRIM(ENAME_SRC)
This is throwing errors as below,
EMP_TGT:Describe failed because of a missing column for EMPNO_TGT
main_program: Could not check all operators because of previous error(s)
main_program: Creation of a step finished with status = FAILED.
Job EMPJOB aborted
Case 3 :
Target Option is set to Load
Used Basic tranformer
Created a PX job
We have used trim function for the Source Columns as below,
TRIM(EMPNO_SRC)
TRIM(ENAME_SRC)
This works fine.
Case 4 :
Target Option is set to Load
Used PX tranformer
Created a PX job
We have changed the source and target table column names to be similar.
We have used trim function for the Source Columns as below
TRIM(EMPNO_TGT)
TRIM(ENAME_TGT)
This works fine.
Since the case 2 is very crucial for me as all my existing jobs needs to use advanced functions which are available in PX tranformer.
Please suggest us any solutions.
Rgds,
Hi
can you uncheck runtime propogation from project level and then try .
Sanjay
can you uncheck runtime propogation from project level and then try .
Sanjay
mspanda wrote:Below are the cases which i tried,
My Table Structure is as below for Source,
EMP_SRC
EMPNO_SRC
ENAME_SRC
My Table Structure is as below for Target,
EMP_TGT
EMPNO_TGT
ENAME_TGT
Source/Target is Oracle
Case 1 :
Target Option is set to Load
Used PX tranformer
Created a PX job
This is one to one mapping with out any functions.
This works fine.
Case 2 :
Target Option is set to Load
Used PX tranformer
Created a PX job
We have used trim function for the Source Columns as below,
TRIM(EMPNO_SRC)
TRIM(ENAME_SRC)
This is throwing errors as below,
EMP_TGT:Describe failed because of a missing column for EMPNO_TGT
main_program: Could not check all operators because of previous error(s)
main_program: Creation of a step finished with status = FAILED.
Job EMPJOB aborted
Case 3 :
Target Option is set to Load
Used Basic tranformer
Created a PX job
We have used trim function for the Source Columns as below,
TRIM(EMPNO_SRC)
TRIM(ENAME_SRC)
This works fine.
Case 4 :
Target Option is set to Load
Used PX tranformer
Created a PX job
We have changed the source and target table column names to be similar.
We have used trim function for the Source Columns as below
TRIM(EMPNO_TGT)
TRIM(ENAME_TGT)
This works fine.
Since the case 2 is very crucial for me as all my existing jobs needs to use advanced functions which are available in PX tranformer.
Please suggest us any solutions.
Rgds,
</a>