Warning in transformer

chulett · Post by **chulett** » Thu Feb 14, 2008 12:50 am

I asked for the client version - it will be something like 9.2.0.5.

basu.ds · Post by **basu.ds** » Thu Feb 14, 2008 1:55 am

version is 9.2.0.1.0

basu.ds · Post by **basu.ds** » Thu Feb 14, 2008 1:56 am

version is 9.2.0.1.0

basu.ds · Post by **basu.ds** » Thu Feb 14, 2008 2:27 am

Hi ray this is code can you help me on this

* Tokens were replaced below as follows:
* JobParam%%1 <= SrcDB
* JobParam%%2 <= SrcDBUser
* JobParam%%3 <= SrcDBPwd
* JobParam%%4 <= StgDir
* Pin%%V0S17P1.Column%%1 <= DSLink3.SUBMISSION_ID
* Pin%%V0S17P1.Column%%2 <= DSLink3.REC_STATUS_R
* Pin%%V0S17P1.Column%%3 <= DSLink3.REC_STATUS_L
* Pin%%V0S17P1.Column%%4 <= DSLink3.REC_STATUS_G
* Pin%%V0S17P1.Column%%5 <= DSLink3.REC_STATUS_P
* Pin%%V0S17P1.Column%%6 <= DSLink3.REC_STATUS_N
* Pin%%V0S17P1.Column%%7 <= DSLink3.REC_STATUS_E
* Pin%%V0S17P1.Column%%8 <= DSLink3.REC_STATUS_H
* Pin%%V0S17P1.Column%%9 <= DSLink3.REC_STATUS_I
* Pin%%V0S17P1.Column%%10 <= DSLink3.REC_STATUS_J
* Pin%%V0S17P1.Column%%11 <= DSLink3.REC_STATUS_M
* Pin%%V0S17P1.Column%%12 <= DSLink3.REC_STATUS_T
* Pin%%V0S17P1.Column%%13 <= DSLink3.TOTAL_ERROR_WEB
* Pin%%V0S17P1.Column%%14 <= DSLink3.TOTAL_ERROR_LCL
* Pin%%V0S17P1.Column%%15 <= DSLink3.TOTAL_ERROR_GLB
* Pin%%V0S17P1.Column%%16 <= DSLink3.REC_OVERWRITTEN
* Pin%%V0S17P1.Column%%17 <= DSLink3.REC_DELETED
* GET.Pin%%V0S17P1 <= GET.DSLink3
* Pin%%V0S17P1.REJECTEDCODE <= DSLink3.REJECTEDCODE
* Pin%%V0S17P2.Column%%1 <= DSLink19.SUBMISSION_ID
* Pin%%V0S17P2.Column%%2 <= DSLink19.REC_STATUS_R
* Pin%%V0S17P2.Column%%3 <= DSLink19.REC_STATUS_L
* Pin%%V0S17P2.Column%%4 <= DSLink19.REC_STATUS_G
* Pin%%V0S17P2.Column%%5 <= DSLink19.REC_STATUS_P
* Pin%%V0S17P2.Column%%6 <= DSLink19.REC_STATUS_N
* Pin%%V0S17P2.Column%%7 <= DSLink19.REC_STATUS_E
* Pin%%V0S17P2.Column%%8 <= DSLink19.REC_STATUS_H
* Pin%%V0S17P2.Column%%9 <= DSLink19.REC_STATUS_I
* Pin%%V0S17P2.Column%%10 <= DSLink19.REC_STATUS_J
* Pin%%V0S17P2.Column%%11 <= DSLink19.REC_STATUS_M
* Pin%%V0S17P2.Column%%12 <= DSLink19.REC_STATUS_T
* Pin%%V0S17P2.Column%%13 <= DSLink19.TOTAL_ERROR_WEB
* Pin%%V0S17P2.Column%%14 <= DSLink19.TOTAL_ERROR_LCL
* Pin%%V0S17P2.Column%%15 <= DSLink19.TOTAL_ERROR_GLB
* Pin%%V0S17P2.Column%%16 <= DSLink19.REC_OVERWRITTEN
* Pin%%V0S17P2.Column%%17 <= DSLink19.REC_DELETED
* PUT.Pin%%V0S17P2 <= PUT.DSLink19
* Pin%%V0S17P2.REJECTED <= DSLink19.REJECTED
* Pin%%V0S17P2.REJECTEDCODE <= DSLink19.REJECTEDCODE
*
* Subroutine for active stage testsummary.Transformer_17 generated at 12:19:08 13 Feb 2008
*
SUBROUTINE DSTransformerStage(HANDLES,ERROR)

$INCLUDE DSINCLUDE DSD_RTCONFIG.H
$INCLUDE DSINCLUDE DSD_STAGE.H
$INCLUDE DSINCLUDE DSD_BCI.H

$DEFINE JobParam%%1 STAGECOM.JOB.STATUS<7,1>
$DEFINE JobParam%%2 STAGECOM.JOB.STATUS<7,2>
$DEFINE JobParam%%3 STAGECOM.JOB.STATUS<7,3>
$DEFINE JobParam%%4 STAGECOM.JOB.STATUS<7,4>

$INCLUDE DSINCLUDE JOBCONTROL.H
DEFFUN DSRLoadString(Num,Text,Args) CALLING '*DataStage*DSR_LOADSTRING'
$DEFINE Pin%%V0S17P1.Column%%1 STAGECOM.ARR(1)
$DEFINE Pin%%V0S17P1.Column%%2 STAGECOM.ARR(2)
$DEFINE Pin%%V0S17P1.Column%%3 STAGECOM.ARR(3)
$DEFINE Pin%%V0S17P1.Column%%4 STAGECOM.ARR(4)
$DEFINE Pin%%V0S17P1.Column%%5 STAGECOM.ARR(5)
$DEFINE Pin%%V0S17P1.Column%%6 STAGECOM.ARR(6)
$DEFINE Pin%%V0S17P1.Column%%7 STAGECOM.ARR(7)
$DEFINE Pin%%V0S17P1.Column%%8 STAGECOM.ARR(8)
$DEFINE Pin%%V0S17P1.Column%%9 STAGECOM.ARR(9)
$DEFINE Pin%%V0S17P1.Column%%10 STAGECOM.ARR(10)
$DEFINE Pin%%V0S17P1.Column%%11 STAGECOM.ARR(11)
$DEFINE Pin%%V0S17P1.Column%%12 STAGECOM.ARR(12)
$DEFINE Pin%%V0S17P1.Column%%13 STAGECOM.ARR(13)
$DEFINE Pin%%V0S17P1.Column%%14 STAGECOM.ARR(14)
$DEFINE Pin%%V0S17P1.Column%%15 STAGECOM.ARR(15)
$DEFINE Pin%%V0S17P1.Column%%16 STAGECOM.ARR(16)
$DEFINE Pin%%V0S17P1.Column%%17 STAGECOM.ARR(17)
$DEFINE GET.Pin%%V0S17P1 CALL $DSP.GetNext(1,Pin%%V0S17P1.REJECTEDCODE)
IF STAGECOM.TRACE.STATS THEN CALL $PERF.NAME(-2,'DSLink19.Derivation')
$DEFINE Pin%%V0S17P2.Column%%1 STAGECOM.ARR(1)
$DEFINE Pin%%V0S17P2.Column%%2 STAGECOM.ARR(2)
$DEFINE Pin%%V0S17P2.Column%%3 STAGECOM.ARR(3)
$DEFINE Pin%%V0S17P2.Column%%4 STAGECOM.ARR(4)
$DEFINE Pin%%V0S17P2.Column%%5 STAGECOM.ARR(5)
$DEFINE Pin%%V0S17P2.Column%%6 STAGECOM.ARR(6)
$DEFINE Pin%%V0S17P2.Column%%7 STAGECOM.ARR(7)
$DEFINE Pin%%V0S17P2.Column%%8 STAGECOM.ARR(8)
$DEFINE Pin%%V0S17P2.Column%%9 STAGECOM.ARR(9)
$DEFINE Pin%%V0S17P2.Column%%10 STAGECOM.ARR(10)
$DEFINE Pin%%V0S17P2.Column%%11 STAGECOM.ARR(11)
$DEFINE Pin%%V0S17P2.Column%%12 STAGECOM.ARR(12)
$DEFINE Pin%%V0S17P2.Column%%13 STAGECOM.ARR(13)
$DEFINE Pin%%V0S17P2.Column%%14 STAGECOM.ARR(14)
$DEFINE Pin%%V0S17P2.Column%%15 STAGECOM.ARR(15)
$DEFINE Pin%%V0S17P2.Column%%16 STAGECOM.ARR(16)
$DEFINE Pin%%V0S17P2.Column%%17 STAGECOM.ARR(17)
$DEFINE PUT.Pin%%V0S17P2 CALL $DS.SEQPUT(2, Pin%%V0S17P2.REJECTEDCODE)

UPDATE.COUNT = STAGECOM.RATE

LOOP
REJECTED = @TRUE
* Get next row from primary input pin DSLink3
STAGECOM.PINNO = 1
GET.Pin%%V0S17P1
ERROR = Pin%%V0S17P1.REJECTEDCODE
WHILE NOT(ERROR)

STAGECOM.PINNO = 2
IF STAGECOM.TRACE.STATS THEN CALL $PERF.BEGIN(-2)
IF @TRUE THEN
* Column derivation code for pin DSLink19
Pin%%V0S17P2.REJECTED = @FALSE
IF STAGECOM.TRACE.STATS THEN CALL $PERF.END(-2)

PUT.Pin%%V0S17P2
IF NOT(Pin%%V0S17P2.REJECTEDCODE) THEN
REJECTED = @FALSE
END ELSE
Pin%%V0S17P2.REJECTED = @TRUE
END
END
ELSE
Pin%%V0S17P2.REJECTED = @TRUE
Pin%%V0S17P2.REJECTEDCODE = 0
END

UPDATE.COUNT -= 1
IF UPDATE.COUNT LE 0 THEN CALL DSD.Update(HANDLES);UPDATE.COUNT = STAGECOM.RATE
REPEAT
RETURN
END

ray.wurlod · Post by **ray.wurlod** » Thu Feb 14, 2008 4:49 am

Care to put the line numbers on that? I don't have the time.

ArndW · Post by **ArndW** » Thu Feb 14, 2008 5:17 am

Hmmm... could be in the write to sequential. Please put in a constraint "1=2" in your transform stage, recompile and re-run to see if the error remains the same or goes away. Also, how much data / how many rows are you writing?

basu.ds · Post by **basu.ds** » Thu Feb 14, 2008 6:57 am

hi ray could you help now
1* Tokens were replaced below as follows:
2* JobParam%%1 <= SrcDB
3* JobParam%%2 <= SrcDBUser
4* JobParam%%3 <= SrcDBPwd
5* JobParam%%4 <= StgDir
6* Pin%%V0S17P1.Column%%1 <= DSLink3.SUBMISSION_ID
7* Pin%%V0S17P1.Column%%2 <= DSLink3.REC_STATUS_R
8* Pin%%V0S17P1.Column%%3 <= DSLink3.REC_STATUS_L
9* Pin%%V0S17P1.Column%%4 <= DSLink3.REC_STATUS_G
10* Pin%%V0S17P1.Column%%5 <= DSLink3.REC_STATUS_P
11* Pin%%V0S17P1.Column%%6 <= DSLink3.REC_STATUS_N
12* Pin%%V0S17P1.Column%%7 <= DSLink3.REC_STATUS_E
13* Pin%%V0S17P1.Column%%8 <= DSLink3.REC_STATUS_H
14* Pin%%V0S17P1.Column%%9 <= DSLink3.REC_STATUS_I
15* Pin%%V0S17P1.Column%%10 <= DSLink3.REC_STATUS_J
16* Pin%%V0S17P1.Column%%11 <= DSLink3.REC_STATUS_M
17* Pin%%V0S17P1.Column%%12 <= DSLink3.REC_STATUS_T
18* Pin%%V0S17P1.Column%%13 <= DSLink3.TOTAL_ERROR_WEB
19* Pin%%V0S17P1.Column%%14 <= DSLink3.TOTAL_ERROR_LCL
20* Pin%%V0S17P1.Column%%15 <= DSLink3.TOTAL_ERROR_GLB
21* Pin%%V0S17P1.Column%%16 <= DSLink3.REC_OVERWRITTEN
22* Pin%%V0S17P1.Column%%17 <= DSLink3.REC_DELETED
23* GET.Pin%%V0S17P1 <= GET.DSLink3
24* Pin%%V0S17P1.REJECTEDCODE <= DSLink3.REJECTEDCODE
25* Pin%%V0S17P2.Column%%1 <= DSLink19.SUBMISSION_ID
26* Pin%%V0S17P2.Column%%2 <= DSLink19.REC_STATUS_R
27* Pin%%V0S17P2.Column%%3 <= DSLink19.REC_STATUS_L
28* Pin%%V0S17P2.Column%%4 <= DSLink19.REC_STATUS_G
29* Pin%%V0S17P2.Column%%5 <= DSLink19.REC_STATUS_P
30* Pin%%V0S17P2.Column%%6 <= DSLink19.REC_STATUS_N
31* Pin%%V0S17P2.Column%%7 <= DSLink19.REC_STATUS_E
32* Pin%%V0S17P2.Column%%8 <= DSLink19.REC_STATUS_H
33* Pin%%V0S17P2.Column%%9 <= DSLink19.REC_STATUS_I
34* Pin%%V0S17P2.Column%%10 <= DSLink19.REC_STATUS_J
35* Pin%%V0S17P2.Column%%11 <= DSLink19.REC_STATUS_M
36* Pin%%V0S17P2.Column%%12 <= DSLink19.REC_STATUS_T
37* Pin%%V0S17P2.Column%%13 <= DSLink19.TOTAL_ERROR_WEB
38* Pin%%V0S17P2.Column%%14 <= DSLink19.TOTAL_ERROR_LCL
39* Pin%%V0S17P2.Column%%15 <= DSLink19.TOTAL_ERROR_GLB
40* Pin%%V0S17P2.Column%%16 <= DSLink19.REC_OVERWRITTEN
41* Pin%%V0S17P2.Column%%17 <= DSLink19.REC_DELETED
42* PUT.Pin%%V0S17P2 <= PUT.DSLink19
43* Pin%%V0S17P2.REJECTED <= DSLink19.REJECTED
44* Pin%%V0S17P2.REJECTEDCODE <= DSLink19.REJECTEDCODE
45*
46* Subroutine for active stage testsummary.Transformer_17 generated at 12:19:08 13 Feb 2008
47*
48SUBROUTINE DSTransformerStage(HANDLES,ERROR)
49
50$INCLUDE DSINCLUDE DSD_RTCONFIG.H
51$INCLUDE DSINCLUDE DSD_STAGE.H
52$INCLUDE DSINCLUDE DSD_BCI.H
53
54$DEFINE JobParam%%1 STAGECOM.JOB.STATUS<7,1>
55$DEFINE JobParam%%2 STAGECOM.JOB.STATUS<7,2>
56$DEFINE JobParam%%3 STAGECOM.JOB.STATUS<7,3>
57$DEFINE JobParam%%4 STAGECOM.JOB.STATUS<7,4>
58
59$INCLUDE DSINCLUDE JOBCONTROL.H
60DEFFUN DSRLoadString(Num,Text,Args) CALLING '*DataStage*DSR_LOADSTRING'
61$DEFINE Pin%%V0S17P1.Column%%1 STAGECOM.ARR(1)
62$DEFINE Pin%%V0S17P1.Column%%2 STAGECOM.ARR(2)
63$DEFINE Pin%%V0S17P1.Column%%3 STAGECOM.ARR(3)
64$DEFINE Pin%%V0S17P1.Column%%4 STAGECOM.ARR(4)
65$DEFINE Pin%%V0S17P1.Column%%5 STAGECOM.ARR(5)
66$DEFINE Pin%%V0S17P1.Column%%6 STAGECOM.ARR(6)
67$DEFINE Pin%%V0S17P1.Column%%7 STAGECOM.ARR(7)
68$DEFINE Pin%%V0S17P1.Column%%8 STAGECOM.ARR(8)
69$DEFINE Pin%%V0S17P1.Column%%9 STAGECOM.ARR(9)
70$DEFINE Pin%%V0S17P1.Column%%10 STAGECOM.ARR(10)
71$DEFINE Pin%%V0S17P1.Column%%11 STAGECOM.ARR(11)
72$DEFINE Pin%%V0S17P1.Column%%12 STAGECOM.ARR(12)
73$DEFINE Pin%%V0S17P1.Column%%13 STAGECOM.ARR(13)
74$DEFINE Pin%%V0S17P1.Column%%14 STAGECOM.ARR(14)
75$DEFINE Pin%%V0S17P1.Column%%15 STAGECOM.ARR(15)
76$DEFINE Pin%%V0S17P1.Column%%16 STAGECOM.ARR(16)
77$DEFINE Pin%%V0S17P1.Column%%17 STAGECOM.ARR(17)
78$DEFINE GET.Pin%%V0S17P1 CALL $DSP.GetNext(1,Pin%%V0S17P1.REJECTEDCODE)
79IF STAGECOM.TRACE.STATS THEN CALL $PERF.NAME(-2,'DSLink19.Derivation')
80$DEFINE Pin%%V0S17P2.Column%%1 STAGECOM.ARR(1)
81$DEFINE Pin%%V0S17P2.Column%%2 STAGECOM.ARR(2)
82$DEFINE Pin%%V0S17P2.Column%%3 STAGECOM.ARR(3)
83$DEFINE Pin%%V0S17P2.Column%%4 STAGECOM.ARR(4)
84$DEFINE Pin%%V0S17P2.Column%%5 STAGECOM.ARR(5)
85$DEFINE Pin%%V0S17P2.Column%%6 STAGECOM.ARR(6)
86$DEFINE Pin%%V0S17P2.Column%%7 STAGECOM.ARR(7)
87$DEFINE Pin%%V0S17P2.Column%%8 STAGECOM.ARR(8)
88$DEFINE Pin%%V0S17P2.Column%%9 STAGECOM.ARR(9)
89$DEFINE Pin%%V0S17P2.Column%%10 STAGECOM.ARR(10)
90$DEFINE Pin%%V0S17P2.Column%%11 STAGECOM.ARR(11)
91$DEFINE Pin%%V0S17P2.Column%%12 STAGECOM.ARR(12)
92$DEFINE Pin%%V0S17P2.Column%%13 STAGECOM.ARR(13)
93$DEFINE Pin%%V0S17P2.Column%%14 STAGECOM.ARR(14)
94$DEFINE Pin%%V0S17P2.Column%%15 STAGECOM.ARR(15)
95$DEFINE Pin%%V0S17P2.Column%%16 STAGECOM.ARR(16)
96$DEFINE Pin%%V0S17P2.Column%%17 STAGECOM.ARR(17)
97$DEFINE PUT.Pin%%V0S17P2 CALL $DS.SEQPUT(2, Pin%%V0S17P2.REJECTEDCODE)
98
99UPDATE.COUNT = STAGECOM.RATE
100
102
102
103LOOP
104 REJECTED = @TRUE
105 * Get next row from primary input pin DSLink3
106 STAGECOM.PINNO = 1
107 GET.Pin%%V0S17P1
108 ERROR = Pin%%V0S17P1.REJECTEDCODE
109WHILE NOT(ERROR)
110
111 STAGECOM.PINNO = 2
112 IF STAGECOM.TRACE.STATS THEN CALL $PERF.BEGIN(-2)
113 IF @TRUE THEN
114 * Column derivation code for pin DSLink19
115 Pin%%V0S17P2.REJECTED = @FALSE
116 IF STAGECOM.TRACE.STATS THEN CALL $PERF.END(-2)
117
118 PUT.Pin%%V0S17P2
119 IF NOT(Pin%%V0S17P2.REJECTEDCODE) THEN
120 REJECTED = @FALSE
121 END ELSE
122 Pin%%V0S17P2.REJECTED = @TRUE
123 END
124 END
125 ELSE
126 Pin%%V0S17P2.REJECTED = @TRUE
127 Pin%%V0S17P2.REJECTEDCODE = 0
128 END
129
130
131 UPDATE.COUNT -= 1
132 IF UPDATE.COUNT LE 0 THEN CALL DSD.Update(HANDLES);UPDATE.COUNT = STAGECOM.RATE
133REPEAT
134RETURN
135END

chulett · Post by **chulett** » Thu Feb 14, 2008 7:20 am

basu.ds wrote:version is 9.2.0.1.0

Old and unstable.

Is your 9i database itself that old? If not, suggest you have your DBA upgrade your client to a more 'recent' and stable version, like 9.2.05 or 9.2.0.6.

basu.ds · Post by **basu.ds** » Thu Feb 14, 2008 7:30 am

any other solution?

ArndW · Post by **ArndW** » Thu Feb 14, 2008 7:34 am

Since you seem to be overlooking what Craig and I posted, you will have to wait to see if Ray responds.

chulett · Post by **chulett** » Thu Feb 14, 2008 8:22 am

Whatever. I've been down this road and been bitten in the ass by this very client version. Everything works great except for one or two perfectly valid sqls. There's nothing wrong with the job or the query, but memory leaks and other problem in the client blow you up real good.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 14, 2008 3:33 pm

Line 107 contains Get.Pin%%V0S17P1 which maps back (via a $DEFINE declaration) to CALL $DSP.GetNext(1,Pin%%V0S17P1.REJECTEDCODE)
This is a GCI subroutine call to retrieve the next row from DSLink3 (see line 23 for the generic interpretation). Since you have your Oracle stage providing rows to DSLink3 we can narrow the focus of investigation to the Oracle client, to the SQL or to the Oracle database server.

Heed Craig's advice about client software version; he's had more experience with Oracle and DataStage than most of us. And been bitten by more things.

chulett · Post by **chulett** » Thu Feb 14, 2008 4:09 pm