Source to target loading using oracle and datastage

rprasanna28 · Post by **rprasanna28** » Tue Jun 26, 2007 1:28 am

I am currently into a critical assignment.

Where i need to load data from source to target using datastage 7.X

Source and target being Oracle.

My Table Structure is a below for Source,

EMP_SRC

EMPNO_SRC
ENAME_SRC

My Table Structure is a below for Target,

EMP_TGT

EMPNO_TGT
ENAME_TGT

I Used Transformer operator to map the relation between source to target.

The Target option i used is 'LOAD'

Below is the error which i am getting from the log.

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

Please suggest me.

Regards,
PR

ArndW · Post by **ArndW** » Tue Jun 26, 2007 2:08 am

What type of load are you doing to Oracle? Automatic or user defined?

ray.wurlod · Post by **ray.wurlod** » Tue Jun 26, 2007 2:22 am

Please post the generated OSH so we can see what you actually designed. The message suggests that at least one mapping is missing from the Transformer stage.

Do you have Runtime Column Propagation enabled?

mspanda · Post by **mspanda** » Tue Jun 26, 2007 3:32 am

Arnd,

I have selected automatic load option.

Rgds,

mspanda · Post by **mspanda** » Tue Jun 26, 2007 4:03 am

Ray,

I was able to resolve the error by importing the metadata for the Source/Target tables. I was able to compile and execute.

But still the data is not loading to the target tables even it shows in the Job as it selected 14 records from the source and inserted 14 records in the target.

Below is the report,

<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style>
SPAN.special {
font:12pt black;
}
TABLE.properties {
width:95%;
}
TD.propname {
width:30%;
font:bold;
}
TD.propval {
width:70%;
}
TD.dependtype {
width:20%;
}
TD.dependloc {
width:60%;
}
TABLE.cols {
width:90%;
}
TD.constraint {
width:20%;
font:bold;
}
H3.hand {
cursor:hand;
}
</style><script language="JavaScript">
function ExpandDisplay(id)
{
// Expand or contract by changing display property to hide or show text
document.getElementById(id).style.display = ((document.getElementById(id).style.display == "none") ? "block" : "none");
return;
}
function splitLongName(name)
{
var charsLeft = name.length;
var newName = "";
var pos = 0;
while (charsLeft > 30)
{
newName = newName + name.substr(pos, 30) + "<br>";
charsLeft = charsLeft - 30;
pos = pos + 30;
}
if (charsLeft > 0)
{
newName = newName + name.substr(pos, charsLeft);
}
document.write(newName);
return;
}
</script></head>
<body><img src="file:///C:\Program Files\Ascential\DataStage7.5.2/ascentialjobreport_1600.bmp" alt="Ascential DataStage"><HR>
<table border="0" cellspacing="0" width="100%">
<tr valign="top">
<td align="center">
<H3>Report For Parallel Job</H3>
<H1><a name="EMPJOB">EMPJOB</a></H1><i>Report generated on 2007-06-26,
at 15.23.16<br>From project 3DPL on server 10.201.51.142<br>DataStage server version 7.5.2</i></td>
</tr>
</table><br><br><img src="./J445.bmp" usemap="#jobmap" alt="No image available"><map name="jobmap"><area shape="rect" coords="96,240,154,288" href="#V0S0" ALT="Click here for Stage Properties">
<area shape="rect" coords="456,264,504,312" href="#V0S1" ALT="Click here for Stage Properties">
<area shape="rect" coords="312,264,360,312" href="#V0S2" ALT="Click here for Stage Properties"></map><H2>Table of Contents</H2>
<DIV><B><A href="#_JobProperties_">Job Properties</A></B></DIV>
<DIV><B><A href="#_SourceStages_">Source Stages</A></B></DIV>
<LI><A href="#V0S0">emp_src</A></LI>
<DIV><B><A href="#_ProcessingStages_">Processing Stages</A></B></DIV>
<LI><A href="#V0S2">Transformer_2</A></LI>
<DIV><B><A href="#_TargetStages_">Target Stages</A></B></DIV>
<LI><A href="#V0S1">emp_tgt</A></LI><BR><BR><HR>
<H2><A name="#_JobProperties_">Job Properties</A></H2>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Job version number:</td>
<td class="propval">50.0.0</td>
</tr>
<tr valign="top">
<td class="propname">Runtime column propagation:</td>
<td class="propval">Enabled</td>
</tr>
</table>
<P><A onclick="ExpandDisplay('OrchestrateCode');"><H3 class="hand">Parallel Job Script<span class="special"> <I>(click to expand or hide)</I></span></H3></A></P>
<div id="OrchestrateCode" style="display:none;"><B>Warning: if you have changed your job and not re-compiled, this script may be out of date</B><PRE>#################################################################
#### STAGE: emp_src
## Operator
oraread
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=scott,password=[&__V0S0P1_password]}'
-table 'emp_src'
-server 'wistg'

## General options
[ident('emp_src'); jobmon_ident('emp_src')]
## Outputs
0> [modify (
EMPNO_SRC:nullable int32=EMPNO_SRC;
ENAME_SRC:nullable ustring[max=10]=ENAME_SRC;
)] 'emp_src:DSLink3.v'
;

#################################################################
#### STAGE: emp_tgt
## Operator
orawrite
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=scott,password=[&__V0S1P1_password]}'
-table 'emp_tgt'
-mode append
-server 'wistg'

## General options
[ident('emp_tgt'); jobmon_ident('emp_tgt')]
## Inputs
0< [] 'Transformer_2:DSLink4.v'
;

#################################################################
#### STAGE: Transformer_2
## Operator
transform
## Operator options
-flag run
-name 'V0S2_EMPJOB_Transformer_2'

## General options
[ident('Transformer_2'); jobmon_ident('Transformer_2')]
## Inputs
0< [] 'emp_src:DSLink3.v'
## Outputs
0> [] 'Transformer_2:DSLink4.v'
;

</PRE>
</div>
</P>
<HR>
<H2><A name="#_SourceStages_">Source Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Oracle Enterprise :
<A name="V0S0">emp_src</A></B></TR>
</TABLE>
<P>
<table class="properties" border="0">
</table>
</P>
<P>Output:
<B>DSLink3</B></P>
<dl>
<P>
<dd>Outputs to Transformer stage <A href="#V0S2">Transformer_2</A></dd>
</P>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Read Method:</td>
<td class="propval">Table</td>
</tr>
<tr valign="top">
<td class="propname">DB Options Mode:</td>
<td class="propval">Auto-generate</td>
</tr>
<tr valign="top">
<td class="propname">User:</td>
<td class="propval">scott</td>
</tr>
<tr valign="top">
<td class="propname">Password:</td>
<td class="propval">********</td>
</tr>
<tr valign="top">
<td class="propname">DB Options:</td>
<td class="propval">{user=scott,password=*******}</td>
</tr>
<tr valign="top">
<td class="propname">Table:</td>
<td class="propval">emp_src</td>
</tr>
<tr valign="top">
<td class="propname">Remote Server:</td>
<td class="propval">wistg</td>
</tr>
</table>
</P>
<H3>Output Columns</H3>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
<tr valign="top">
<td class="propname"><B><script>splitLongName('EMPNO_SRC')</script></B></td>
<td class="propval">Integer nullable</td>
</tr>
<tr valign="top">
<td class="propname"><B><script>splitLongName('ENAME_SRC')</script></B></td>
<td class="propval">VarChar/Unicode(10) nullable</td>
</tr>
</table>
</dl>
<HR>
<H2><A name="#_ProcessingStages_">Processing Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Transformer :
<A name="V0S2">Transformer_2</A></B></TR>
</TABLE>
<P>
</P>
<P>Input:
<B>DSLink3</B></P>
<dl>
<P>
<dd>Input from Oracle Enterprise stage <A href="#V0S0">emp_src</A></dd>
</P>
<P>
</P>
</dl>
<P>Output:
<B>DSLink4</B></P>
<dl>
<P>
<dd>Outputs to Oracle Enterprise stage <A href="#V0S1">emp_tgt</A></dd>
</P>
<P>
<dd>
<table class="properties" border="0">
<tr valign="top">
<td><b>Constraint:</b></td>
<td></td>
</tr>
</table>
</dd>
</P>
<P>
</P>
<P>
<dd><H3>Derived Columns</H3></dd>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
<tr valign="top">
<td class="propname"><script>splitLongName('EMPNO_TGT')</script></td>
<td class="propval">DSLink3.EMPNO_SRC</td>
</tr>
<tr valign="top">
<td class="propname"><script>splitLongName('ENAME_TGT')</script></td>
<td class="propval">DSLink3.ENAME_SRC</td>
</tr>
</table>
</P>
<P>
<dd><H3>Pass-through Columns</H3></dd>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
</table>
</P>
</dl>
<HR>
<H2><A name="#_TargetStages_">Target Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Oracle Enterprise :
<A name="V0S1">emp_tgt</A></B></TR>
</TABLE>
<P>
<table class="properties" border="0">
</table>
</P>
<P>Input:
<B>DSLink4</B></P>
<dl>
<P>
<dd>Input from Transformer stage <A href="#V0S2">Transformer_2</A></dd>
</P>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Write Method:</td>
<td class="propval">Load</td>
</tr>
<tr valign="top">
<td class="propname">DB Options Mode:</td>
<td class="propval">Auto-generate</td>
</tr>
<tr valign="top">
<td class="propname">User:</td>
<td class="propval">scott</td>
</tr>
<tr valign="top">
<td class="propname">Password:</td>
<td class="propval">********</td>
</tr>
<tr valign="top">
<td class="propname">DB Options:</td>
<td class="propval">{user=scott,password=*******}</td>
</tr>
<tr valign="top">
<td class="propname">Table:</td>
<td class="propval">emp_tgt</td>
</tr>
<tr valign="top">
<td class="propname">Silently Drop Columns Not In Table:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Write Mode:</td>
<td class="propval">Append</td>
</tr>
<tr valign="top">
<td class="propname">Truncate Column Names:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Disable Constraints:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Remote Server:</td>
<td class="propval">wistg</td>
</tr>
<tr valign="top">
<td class="propname">Runtime column propagation:</td>
<td class="propval">On</td>
</tr>
</table>
</P>
</dl>
</body>
</html>
[/img]

sanjay · Post by **sanjay** » Tue Jun 26, 2007 4:17 am

Hi

i clearly say runtime column propogation is on can you uncheck it.

Thanks

Sanjay

mspanda wrote:Ray,

I was able to resolve the error by importing the metadata for the Source/Target tables. I was able to compile and execute.

But still the data is not loading to the target tables even it shows in the Job as it selected 14 records from the source and inserted 14 records in the target.

Below is the report,

<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style>
SPAN.special {
font:12pt black;
}
TABLE.properties {
width:95%;
}
TD.propname {
width:30%;
font:bold;
}
TD.propval {
width:70%;
}
TD.dependtype {
width:20%;
}
TD.dependloc {
width:60%;
}
TABLE.cols {
width:90%;
}
TD.constraint {
width:20%;
font:bold;
}
H3.hand {
cursor:hand;
}
</style><script language="JavaScript">
function ExpandDisplay(id)
{
// Expand or contract by changing display property to hide or show text
document.getElementById(id).style.display = ((document.getElementById(id).style.display == "none") ? "block" : "none");
return;
}
function splitLongName(name)
{
var charsLeft = name.length;
var newName = "";
var pos = 0;
while (charsLeft > 30)
{
newName = newName + name.substr(pos, 30) + "<br>";
charsLeft = charsLeft - 30;
pos = pos + 30;
}
if (charsLeft > 0)
{
newName = newName + name.substr(pos, charsLeft);
}
document.write(newName);
return;
}
</script></head>
<body><img src="file:///C:\Program Files\Ascential\DataStage7.5.2/ascentialjobreport_1600.bmp" alt="Ascential DataStage"><HR>
<table border="0" cellspacing="0" width="100%">
<tr valign="top">
<td align="center">
<H3>Report For Parallel Job</H3>
<H1><a name="EMPJOB">EMPJOB</a></H1><i>Report generated on 2007-06-26,
at 15.23.16<br>From project 3DPL on server 10.201.51.142<br>DataStage server version 7.5.2</i></td>
</tr>
</table><br><br><img src="./J445.bmp" usemap="#jobmap" alt="No image available"><map name="jobmap"><area shape="rect" coords="96,240,154,288" href="#V0S0" ALT="Click here for Stage Properties">
<area shape="rect" coords="456,264,504,312" href="#V0S1" ALT="Click here for Stage Properties">
<area shape="rect" coords="312,264,360,312" href="#V0S2" ALT="Click here for Stage Properties"></map><H2>Table of Contents</H2>
<DIV><B><A href="#_JobProperties_">Job Properties</A></B></DIV>
<DIV><B><A href="#_SourceStages_">Source Stages</A></B></DIV>
<LI><A href="#V0S0">emp_src</A></LI>
<DIV><B><A href="#_ProcessingStages_">Processing Stages</A></B></DIV>
<LI><A href="#V0S2">Transformer_2</A></LI>
<DIV><B><A href="#_TargetStages_">Target Stages</A></B></DIV>
<LI><A href="#V0S1">emp_tgt</A></LI><BR><BR><HR>
<H2><A name="#_JobProperties_">Job Properties</A></H2>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Job version number:</td>
<td class="propval">50.0.0</td>
</tr>
<tr valign="top">
<td class="propname">Runtime column propagation:</td>
<td class="propval">Enabled</td>
</tr>
</table>
<P><A onclick="ExpandDisplay('OrchestrateCode');"><H3 class="hand">Parallel Job Script<span class="special"> <I>(click to expand or hide)</I></span></H3></A></P>
<div id="OrchestrateCode" style="display:none;"><B>Warning: if you have changed your job and not re-compiled, this script may be out of date</B><PRE>#################################################################
#### STAGE: emp_src
## Operator
oraread
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=scott,password=[&__V0S0P1_password]}'
-table 'emp_src'
-server 'wistg'

## General options
[ident('emp_src'); jobmon_ident('emp_src')]
## Outputs
0> [modify (
EMPNO_SRC:nullable int32=EMPNO_SRC;
ENAME_SRC:nullable ustring[max=10]=ENAME_SRC;
)] 'emp_src:DSLink3.v'
;

#################################################################
#### STAGE: emp_tgt
## Operator
orawrite
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=scott,password=[&__V0S1P1_password]}'
-table 'emp_tgt'
-mode append
-server 'wistg'

## General options
[ident('emp_tgt'); jobmon_ident('emp_tgt')]
## Inputs
0< [] 'Transformer_2:DSLink4.v'
;

#################################################################
#### STAGE: Transformer_2
## Operator
transform
## Operator options
-flag run
-name 'V0S2_EMPJOB_Transformer_2'

## General options
[ident('Transformer_2'); jobmon_ident('Transformer_2')]
## Inputs
0< [] 'emp_src:DSLink3.v'
## Outputs
0> [] 'Transformer_2:DSLink4.v'
;

</PRE>
</div>
</P>
<HR>
<H2><A name="#_SourceStages_">Source Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Oracle Enterprise :
<A name="V0S0">emp_src</A></B></TR>
</TABLE>
<P>
<table class="properties" border="0">
</table>
</P>
<P>Output:
<B>DSLink3</B></P>
<dl>
<P>
<dd>Outputs to Transformer stage <A href="#V0S2">Transformer_2</A></dd>
</P>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Read Method:</td>
<td class="propval">Table</td>
</tr>
<tr valign="top">
<td class="propname">DB Options Mode:</td>
<td class="propval">Auto-generate</td>
</tr>
<tr valign="top">
<td class="propname">User:</td>
<td class="propval">scott</td>
</tr>
<tr valign="top">
<td class="propname">Password:</td>
<td class="propval">********</td>
</tr>
<tr valign="top">
<td class="propname">DB Options:</td>
<td class="propval">{user=scott,password=*******}</td>
</tr>
<tr valign="top">
<td class="propname">Table:</td>
<td class="propval">emp_src</td>
</tr>
<tr valign="top">
<td class="propname">Remote Server:</td>
<td class="propval">wistg</td>
</tr>
</table>
</P>
<H3>Output Columns</H3>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
<tr valign="top">
<td class="propname"><B><script>splitLongName('EMPNO_SRC')</script></B></td>
<td class="propval">Integer nullable</td>
</tr>
<tr valign="top">
<td class="propname"><B><script>splitLongName('ENAME_SRC')</script></B></td>
<td class="propval">VarChar/Unicode(10) nullable</td>
</tr>
</table>
</dl>
<HR>
<H2><A name="#_ProcessingStages_">Processing Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Transformer :
<A name="V0S2">Transformer_2</A></B></TR>
</TABLE>
<P>
</P>
<P>Input:
<B>DSLink3</B></P>
<dl>
<P>
<dd>Input from Oracle Enterprise stage <A href="#V0S0">emp_src</A></dd>
</P>
<P>
</P>
</dl>
<P>Output:
<B>DSLink4</B></P>
<dl>
<P>
<dd>Outputs to Oracle Enterprise stage <A href="#V0S1">emp_tgt</A></dd>
</P>
<P>
<dd>
<table class="properties" border="0">
<tr valign="top">
<td><b>Constraint:</b></td>
<td></td>
</tr>
</table>
</dd>
</P>
<P>
</P>
<P>
<dd><H3>Derived Columns</H3></dd>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
<tr valign="top">
<td class="propname"><script>splitLongName('EMPNO_TGT')</script></td>
<td class="propval">DSLink3.EMPNO_SRC</td>
</tr>
<tr valign="top">
<td class="propname"><script>splitLongName('ENAME_TGT')</script></td>
<td class="propval">DSLink3.ENAME_SRC</td>
</tr>
</table>
</P>
<P>
<dd><H3>Pass-through Columns</H3></dd>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
</table>
</P>
</dl>
<HR>
<H2><A name="#_TargetStages_">Target Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Oracle Enterprise :
<A name="V0S1">emp_tgt</A></B></TR>
</TABLE>
<P>
<table class="properties" border="0">
</table>
</P>
<P>Input:
<B>DSLink4</B></P>
<dl>
<P>
<dd>Input from Transformer stage <A href="#V0S2">Transformer_2</A></dd>
</P>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Write Method:</td>
<td class="propval">Load</td>
</tr>
<tr valign="top">
<td class="propname">DB Options Mode:</td>
<td class="propval">Auto-generate</td>
</tr>
<tr valign="top">
<td class="propname">User:</td>
<td class="propval">scott</td>
</tr>
<tr valign="top">
<td class="propname">Password:</td>
<td class="propval">********</td>
</tr>
<tr valign="top">
<td class="propname">DB Options:</td>
<td class="propval">{user=scott,password=*******}</td>
</tr>
<tr valign="top">
<td class="propname">Table:</td>
<td class="propval">emp_tgt</td>
</tr>
<tr valign="top">
<td class="propname">Silently Drop Columns Not In Table:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Write Mode:</td>
<td class="propval">Append</td>
</tr>
<tr valign="top">
<td class="propname">Truncate Column Names:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Disable Constraints:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Remote Server:</td>
<td class="propval">wistg</td>
</tr>
<tr valign="top">
<td class="propname">Runtime column propagation:</td>
<td class="propval">On</td>
</tr>
</table>
</P>
</dl>
</body>
</html>
[/img]

sanjay · Post by **sanjay** » Tue Jun 26, 2007 4:18 am

Hi

it clearly say runtime column propogation is on can you uncheck it.

Thanks

Sanjay

mspanda wrote:Ray,

I was able to resolve the error by importing the metadata for the Source/Target tables. I was able to compile and execute.

But still the data is not loading to the target tables even it shows in the Job as it selected 14 records from the source and inserted 14 records in the target.

Below is the report,

<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style>
SPAN.special {
font:12pt black;
}
TABLE.properties {
width:95%;
}
TD.propname {
width:30%;
font:bold;
}
TD.propval {
width:70%;
}
TD.dependtype {
width:20%;
}
TD.dependloc {
width:60%;
}
TABLE.cols {
width:90%;
}
TD.constraint {
width:20%;
font:bold;
}
H3.hand {
cursor:hand;
}
</style><script language="JavaScript">
function ExpandDisplay(id)
{
// Expand or contract by changing display property to hide or show text
document.getElementById(id).style.display = ((document.getElementById(id).style.display == "none") ? "block" : "none");
return;
}
function splitLongName(name)
{
var charsLeft = name.length;
var newName = "";
var pos = 0;
while (charsLeft > 30)
{
newName = newName + name.substr(pos, 30) + "<br>";
charsLeft = charsLeft - 30;
pos = pos + 30;
}
if (charsLeft > 0)
{
newName = newName + name.substr(pos, charsLeft);
}
document.write(newName);
return;
}
</script></head>
<body><img src="file:///C:\Program Files\Ascential\DataStage7.5.2/ascentialjobreport_1600.bmp" alt="Ascential DataStage"><HR>
<table border="0" cellspacing="0" width="100%">
<tr valign="top">
<td align="center">
<H3>Report For Parallel Job</H3>
<H1><a name="EMPJOB">EMPJOB</a></H1><i>Report generated on 2007-06-26,
at 15.23.16<br>From project 3DPL on server 10.201.51.142<br>DataStage server version 7.5.2</i></td>
</tr>
</table><br><br><img src="./J445.bmp" usemap="#jobmap" alt="No image available"><map name="jobmap"><area shape="rect" coords="96,240,154,288" href="#V0S0" ALT="Click here for Stage Properties">
<area shape="rect" coords="456,264,504,312" href="#V0S1" ALT="Click here for Stage Properties">
<area shape="rect" coords="312,264,360,312" href="#V0S2" ALT="Click here for Stage Properties"></map><H2>Table of Contents</H2>
<DIV><B><A href="#_JobProperties_">Job Properties</A></B></DIV>
<DIV><B><A href="#_SourceStages_">Source Stages</A></B></DIV>
<LI><A href="#V0S0">emp_src</A></LI>
<DIV><B><A href="#_ProcessingStages_">Processing Stages</A></B></DIV>
<LI><A href="#V0S2">Transformer_2</A></LI>
<DIV><B><A href="#_TargetStages_">Target Stages</A></B></DIV>
<LI><A href="#V0S1">emp_tgt</A></LI><BR><BR><HR>
<H2><A name="#_JobProperties_">Job Properties</A></H2>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Job version number:</td>
<td class="propval">50.0.0</td>
</tr>
<tr valign="top">
<td class="propname">Runtime column propagation:</td>
<td class="propval">Enabled</td>
</tr>
</table>
<P><A onclick="ExpandDisplay('OrchestrateCode');"><H3 class="hand">Parallel Job Script<span class="special"> <I>(click to expand or hide)</I></span></H3></A></P>
<div id="OrchestrateCode" style="display:none;"><B>Warning: if you have changed your job and not re-compiled, this script may be out of date</B><PRE>#################################################################
#### STAGE: emp_src
## Operator
oraread
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=scott,password=[&__V0S0P1_password]}'
-table 'emp_src'
-server 'wistg'

## General options
[ident('emp_src'); jobmon_ident('emp_src')]
## Outputs
0> [modify (
EMPNO_SRC:nullable int32=EMPNO_SRC;
ENAME_SRC:nullable ustring[max=10]=ENAME_SRC;
)] 'emp_src:DSLink3.v'
;

#################################################################
#### STAGE: emp_tgt
## Operator
orawrite
## Operator options
-nchar_cs [&DSProjectMapName]
-db_cs [&DSProjectMapName]
-dboptions '{user=scott,password=[&__V0S1P1_password]}'
-table 'emp_tgt'
-mode append
-server 'wistg'

## General options
[ident('emp_tgt'); jobmon_ident('emp_tgt')]
## Inputs
0< [] 'Transformer_2:DSLink4.v'
;

#################################################################
#### STAGE: Transformer_2
## Operator
transform
## Operator options
-flag run
-name 'V0S2_EMPJOB_Transformer_2'

## General options
[ident('Transformer_2'); jobmon_ident('Transformer_2')]
## Inputs
0< [] 'emp_src:DSLink3.v'
## Outputs
0> [] 'Transformer_2:DSLink4.v'
;

</PRE>
</div>
</P>
<HR>
<H2><A name="#_SourceStages_">Source Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Oracle Enterprise :
<A name="V0S0">emp_src</A></B></TR>
</TABLE>
<P>
<table class="properties" border="0">
</table>
</P>
<P>Output:
<B>DSLink3</B></P>
<dl>
<P>
<dd>Outputs to Transformer stage <A href="#V0S2">Transformer_2</A></dd>
</P>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Read Method:</td>
<td class="propval">Table</td>
</tr>
<tr valign="top">
<td class="propname">DB Options Mode:</td>
<td class="propval">Auto-generate</td>
</tr>
<tr valign="top">
<td class="propname">User:</td>
<td class="propval">scott</td>
</tr>
<tr valign="top">
<td class="propname">Password:</td>
<td class="propval">********</td>
</tr>
<tr valign="top">
<td class="propname">DB Options:</td>
<td class="propval">{user=scott,password=*******}</td>
</tr>
<tr valign="top">
<td class="propname">Table:</td>
<td class="propval">emp_src</td>
</tr>
<tr valign="top">
<td class="propname">Remote Server:</td>
<td class="propval">wistg</td>
</tr>
</table>
</P>
<H3>Output Columns</H3>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
<tr valign="top">
<td class="propname"><B><script>splitLongName('EMPNO_SRC')</script></B></td>
<td class="propval">Integer nullable</td>
</tr>
<tr valign="top">
<td class="propname"><B><script>splitLongName('ENAME_SRC')</script></B></td>
<td class="propval">VarChar/Unicode(10) nullable</td>
</tr>
</table>
</dl>
<HR>
<H2><A name="#_ProcessingStages_">Processing Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Transformer :
<A name="V0S2">Transformer_2</A></B></TR>
</TABLE>
<P>
</P>
<P>Input:
<B>DSLink3</B></P>
<dl>
<P>
<dd>Input from Oracle Enterprise stage <A href="#V0S0">emp_src</A></dd>
</P>
<P>
</P>
</dl>
<P>Output:
<B>DSLink4</B></P>
<dl>
<P>
<dd>Outputs to Oracle Enterprise stage <A href="#V0S1">emp_tgt</A></dd>
</P>
<P>
<dd>
<table class="properties" border="0">
<tr valign="top">
<td><b>Constraint:</b></td>
<td></td>
</tr>
</table>
</dd>
</P>
<P>
</P>
<P>
<dd><H3>Derived Columns</H3></dd>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
<tr valign="top">
<td class="propname"><script>splitLongName('EMPNO_TGT')</script></td>
<td class="propval">DSLink3.EMPNO_SRC</td>
</tr>
<tr valign="top">
<td class="propname"><script>splitLongName('ENAME_TGT')</script></td>
<td class="propval">DSLink3.ENAME_SRC</td>
</tr>
</table>
</P>
<P>
<dd><H3>Pass-through Columns</H3></dd>
<table class="cols" border="2" cellspacing="0" cellpadding="4" frame="box">
</table>
</P>
</dl>
<HR>
<H2><A name="#_TargetStages_">Target Stages</A></H2>
<TABLE BORDER="0" CELLSPACING="0" WIDTH="100%">
<TR BGCOLOR="#C0C0C0"><B>Oracle Enterprise :
<A name="V0S1">emp_tgt</A></B></TR>
</TABLE>
<P>
<table class="properties" border="0">
</table>
</P>
<P>Input:
<B>DSLink4</B></P>
<dl>
<P>
<dd>Input from Transformer stage <A href="#V0S2">Transformer_2</A></dd>
</P>
<P>
<table class="properties" border="0">
<tr valign="top">
<td class="propname">Write Method:</td>
<td class="propval">Load</td>
</tr>
<tr valign="top">
<td class="propname">DB Options Mode:</td>
<td class="propval">Auto-generate</td>
</tr>
<tr valign="top">
<td class="propname">User:</td>
<td class="propval">scott</td>
</tr>
<tr valign="top">
<td class="propname">Password:</td>
<td class="propval">********</td>
</tr>
<tr valign="top">
<td class="propname">DB Options:</td>
<td class="propval">{user=scott,password=*******}</td>
</tr>
<tr valign="top">
<td class="propname">Table:</td>
<td class="propval">emp_tgt</td>
</tr>
<tr valign="top">
<td class="propname">Silently Drop Columns Not In Table:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Write Mode:</td>
<td class="propval">Append</td>
</tr>
<tr valign="top">
<td class="propname">Truncate Column Names:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Disable Constraints:</td>
<td class="propval">
</td>
</tr>
<tr valign="top">
<td class="propname">Remote Server:</td>
<td class="propval">wistg</td>
</tr>
<tr valign="top">
<td class="propname">Runtime column propagation:</td>
<td class="propval">On</td>
</tr>
</table>
</P>
</dl>
</body>
</html>
[/img]

mspanda · Post by **mspanda** » Tue Jun 26, 2007 5:40 am

Sanjay,

I had unchecked the same, but still the data is not getting loaded to target.

Rgds,

mspanda · Post by **mspanda** » Tue Jun 26, 2007 6:46 am

I have set the APT_ORA_WRITE_FILES the log is as shown below.

my observation is after the sqlldr completes its job it was suppose to remove the INFILE's

ora.164096.852937.fifo.0.out
ora.164096.852937.fifo.1.out
ora.164096.852937.fifo.2.out

which is not happening and PARFILE is not getting created.

I tried to load by removing the PARFILE syntax and it worked manually on the command prompt.

But how to rectify this issue and make it load in to database.

LOG :
rsh ETL_SERVER ' sqlldr scott@^E/tiger CONTROL=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.0.ctl LOG=/dsetlsoft/Asce
ntial/DataStage/Scratch/ora.164096.852937.0.log BAD=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.0.log.bad SILENT=header PARFILE=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.0.par ; rm /dsetlsoft/Ascential/DataStage/Scratch/ora.164096
.852937.fifo.0.out '
rsh ETL_SERVER ' sqlldr scott@^E/tiger CONTROL=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.1.ctl LOG=/dsetlsoft/Asce
ntial/DataStage/Scratch/ora.164096.852937.1.log BAD=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.1.log.bad SILENT=hea
der PARFILE=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.1.par ; rm /dsetlsoft/Ascential/DataStage/Scratch/ora.164096
.852937.fifo.1.out '
rsh ETL_SERVER ' sqlldr scott@^E/tiger CONTROL=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.2.ctl LOG=/dsetlsoft/Asce
ntial/DataStage/Scratch/ora.164096.852937.2.log BAD=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.2.log.bad SILENT=hea
der PARFILE=/dsetlsoft/Ascential/DataStage/Scratch/ora.164096.852937.2.par ; rm /dsetlsoft/Ascential/DataStage/Scratch/ora.164096
.852937.fifo.2.out '

mspanda · Post by **mspanda** » Tue Jun 26, 2007 7:17 am

The data is getting loaded after removing the APT_ORA_WRITE_FILES
which is set as /dsetlsoft/Ascential/DataStage/Scratch/250607/sample.log

Now i tried using the trim option in the columns while loading the data from EMP_SRC to EMP_TGT.

EMP_SRC

trim(EMPNO_SRC)
trim(ENAME_SRC)

It shows the below error

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

DSguru2B · Post by **DSguru2B** » Tue Jun 26, 2007 7:23 am

Check your target table again. Make sure all columns are present.

mspanda · Post by **mspanda** » Tue Jun 26, 2007 7:29 am

My Table Structure is a below for Source,

EMP_SRC

EMPNO_SRC
ENAME_SRC

My Table Structure is a below for Target,

EMP_TGT

EMPNO_TGT
ENAME_TGT

I done a simple tranformation using the trim option in the columns while loading the data from EMP_SRC to EMP_TGT.

EMP_SRC

trim(EMPNO_SRC)
trim(ENAME_SRC)

It shows the below error

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

DSguru2B · Post by **DSguru2B** » Tue Jun 26, 2007 7:32 am

Yes. I read that before. My point is, go to the database (not from datastage) and verify if all the columns do exist.

mspanda · Post by **mspanda** » Tue Jun 26, 2007 7:34 am

Yes the columns are available in DB.

Below is the log from DB

SQL> desc emp_tgt
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO_TGT NUMBER(10)
ENAME_TGT VARCHAR2(10)

SQL> desc emp_src
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO_SRC NUMBER(4)
ENAME_SRC VARCHAR2(10)

mspanda · Post by **mspanda** » Tue Jun 26, 2007 8:28 am

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.