Datastage Transformation Error

Kel · Post by **Kel** » Mon May 11, 2015 3:27 am

Hello, Im receiving the following errors on my datastage parallel job. the input came from a lookup stage.
My Transformer stage has 2 outputs, one which is null when the derivation is -3 , and the other is not null where table1.a=a and table1.b=b. can some1 help.

Output from transformer compilation follows:-Output from transformer compilation follows:

##I IIS-DSEE-TFCN-00001 04:04:12(000) <main_program>
IBM WebSphere DataStage Enterprise Edition 8.1.0.6157
Copyright (c) 2001, 2005-2008 IBM Corporation. All rights reserved

##I IIS-DSEE-TFCN-00006 04:04:12(001) <main_program> conductor uname: -s=AIX; -r=1; -v=6; -n=pbmdeaddst04; -m=00C719D44C00
##I IIS-DSEE-TOSH-00002 04:04:12(002) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 04:04:12(003) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 04:04:12(004) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 04:04:12(007) <main_program> Parameter specified but not used in flow: DSPXWorkingDir
##W IIS-DSEE-TFTM-00012 04:04:12(009) <transform> Error when checking composite operator: The number of reject datasets "0" is less than the number of input datasets "1".
##W IIS-DSEE-TFEV-00023 04:04:12(010) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00025 04:04:12(011) <transform> Error when checking composite operator: Converting decimal to ustring.
##W IIS-DSEE-TFEV-00023 04:04:12(012) <transform> Error when checking composite operator: Implicit conversion from source type "Decimal" to result type "UString".
##W IIS-DSEE-TFEV-00023 04:04:12(013) <transform> Error when checking composite operator: Implicit conversion from source type "String" to result type "UString".
##W IIS-DSEE-TFEV-00025 04:04:12(014) <transform> Error when checking composite operator: Converting decimal to ustring.
##W IIS-DSEE-TFEV-00023 04:04:12(015) <transform> Error when checking composite operator: Implicit conversion from source type "Decimal" to result type "UString".
##W IIS-DSEE-TFEV-00025 04:04:12(016) <transform> Error when checking composite operator: Converting number to string.
##W IIS-DSEE-TFEV-00023 04:04:12(017) <transform> Error when checking composite operator: Implicit conversion from source type "Int32" to result type "String".
##E IIS-DSEE-TFEV-00015 04:04:12(018) <transform> Error when checking composite operator: Only an input field can be "null" function argument: [line 51,character 9].
##E IIS-DSEE-TFEV-00015 04:04:12(019) <transform> Error when checking composite operator: Only an input field can be "null" function argument: [line 51,character 9].
##E IIS-DSEE-TFSR-00019 04:04:12(020) <main_program> Could not check all operators because of previous error(s)
##E IIS-DSEE-TCOS-00029 04:04:12(021) <main_program> Creation of a step finished with status = FAILED. (CopyOfJOB_RT01_AUTORIZACIONES.Trf_CL_VAL_ID)

*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V0S46_CopyOfJOB_RT01_AUTORIZACIONES_Trf_CL_VAL_ID transform operator.
0003: //
0004:
0005: // define our input/output link names
0006: inputname 0 Lnk_D_CL_VAL_ID_DMRFN;
0007: outputname 0 Lnk_D_CL_VAL_ID_NULL;
0008: outputname 1 Lnk_D_CL_VAL_ID_NOTNULL;
0009:
0010: global {
0011: // Job parameter declaration
0012: ustring DOLLAR_V_CST_REPCTOT;
0013: ustring DOLLAR_V_CST_ACSTMTDLV;
0014: }
0015:
0016: initialize {
0017: // define our row rejected variable
0018: int8 RowRejected0;
0019:
0020: // define our null set variable
0021: int8 NullSetVar0;
0022:
0023: // declare our intermediate variables for this section
0024: string InterVar0_4;
0025:
0026: // initialise constant values which require conversion
0027: InterVar0_4 = "-3";
0028: // Stage variable declaration and initialisation
0029: string StageVar0_StageVar;
0030: StageVar0_StageVar = "";
0031: }
0032:
0033: mainloop {
0034: // initialise our row rejected variable
0035: RowRejected0 = 1;
0036:
0037: // declare our intermediate variables for this section
0038: ustring InterVar0_0;
0039: ustring InterVar0_1;
0040: ustring InterVar0_2;
0041: ustring InterVar0_3;
0042:
0043: // evaluate the stage variables first
0044: InterVar0_0 = Lnk_D_CL_VAL_ID_DMRFN.COSTOS_TOTALES;
0045: InterVar0_1 = Lnk_D_CL_VAL_ID_DMRFN.UNQ_SRC_STM_ID;
0046: InterVar0_2 = Lnk_D_CL_VAL_ID_DMRFN.EXTRACTO_VIRTUAL;
0047: InterVar0_3 = Lnk_D_CL_VAL_ID_DMRFN.UNQ_SRC_STM_ID;
0048: StageVar0_StageVar = (((DOLLAR_V_CST_REPCTOT + InterVar0_0) == InterVar0_1) || ((DOLLAR_V_CST_ACSTMTDLV + InterVar0_2) == InterVar0_3));
0049:
0050: // evaluate constraint and columns for link: Lnk_D_CL_VAL_ID_NULL
0051: if (null(StageVar0_StageVar))
0052: {
0053: Lnk_D_CL_VAL_ID_NULL.TOTAL_COST_ID = InterVar0_4;
0054: Lnk_D_CL_VAL_ID_NULL.AC_STMT_DLV_TP_ID = InterVar0_4;
0055: writerecord 0;
0056: RowRejected0 = 0;
0057: }
0058: // evaluate constraint and columns for link: Lnk_D_CL_VAL_ID_NOTNULL
0059: if (notnull(StageVar0_StageVar))
0060: {
0061: Lnk_D_CL_VAL_ID_NOTNULL.CL_VAL_ID = Lnk_D_CL_VAL_ID_DMRFN.CL_VAL_ID;
0062: Lnk_D_CL_VAL_ID_NOTNULL.PARTNER = Lnk_D_CL_VAL_ID_DMRFN.PARTNER;
0063: writerecord 1;
0064: RowRejected0 = 0;
0065: }
0066: }
0067:
0068: finish {
0069: }
0070:
*** End of Internal Generated Transformer Code

chulett · Post by **chulett** » Mon May 11, 2015 7:34 am

So... you are unable to compile the job? And it's just a problem with this job, I assume, not with every job that includes a transformer.

The "##E" lines are your Error lines and it doesn't seem to like your function calls. Post your derivations (constraints or otherwise) that involve your NULL checks. You've also got a number of Warnings about implicit conversions you really should clean up as well.

ray.wurlod · Post by **ray.wurlod** » Mon May 11, 2015 2:09 pm

Check that your C++ compiler has a current licence.

Try using explicit, rather than implicit, data type transformations. And be careful about null handling.

Kel · Post by **Kel** » Mon May 11, 2015 7:27 pm

The output of my look up stage has 5 columns and was used as an input to the transformer stage. I used a stage variable with the following derivation. ($V_CST_REPCTOT:Lnk_D_CL_VAL_ID_DMRFN.COSTOS_TOTALES = Lnk_D_CL_VAL_ID_DMRFN.UNQ_SRC_STM_ID) OR
($V_CST_ACSTMTDLV :Lnk_D_CL_VAL_ID_DMRFN.EXTRACTO_VIRTUAL = Lnk_D_CL_VAL_ID_DMRFN.UNQ_SRC_STM_ID)

The 2 output links are:
IsNotNull(StageVar): 2 from the 5 inputs are connected to it. If true data shall pass through one output link.

IsNull(StageVar): none from the 5 inputs links are connecter. new columns are defined. If true change null to -3 and pass to another input link.

Hope this can help.
Dont flame me please im just new to Datastage.

Mike · Post by **Mike** » Tue May 12, 2015 5:40 am

My memory could be off a bit, but I don't think version 8.1 supported null in a stage variable. I think that might've been introduced in version 8.5.

Mike

chulett · Post by **chulett** » Tue May 12, 2015 6:33 am

That is my recollection as well.

ps. There's no flaming here. We were all new once, even Ray.

ray.wurlod · Post by **ray.wurlod** » Tue May 12, 2015 3:52 pm

Mike's memory is good. Handle the nulls in your stage variable expressions such that the final value of the stage variable is not null (for example using the NullToValue() function or one of its brethren).

Kel · Post by **Kel** » Thu May 14, 2015 7:45 pm

Thank you for all your inputs. Now I dont have compile errors. What we did was remove the stage variable, add an additional transformer stage, create two new columns where the derivation we used was the one on the stage variable. and put the isnull and is not null into the constraint into the next transformer stage.