Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett, rschirm, roy
-
agathaeleanor
- Participant
- Posts: 76
- Joined: Mon Mar 05, 2007 9:26 pm
- Location: Malaysia
Post
by agathaeleanor »
Hi All,
I have a stored proc running fine in sqlplus but not when called from datastage. Stored proc defined as 'Transform'.
I ran the stored proc in sqlplus which inserted approximately 700 rows. However, when the stored proc calls from datastage, it generates zero rows.
One thing i suspected is the definition of the SP comes with ampersand(&), part of the SP goes like this
....WHERE edu IN ('Pre-sch', 'Sec & Post Sec')
Need your advise on this.
Thanks a bunch.
-
chulett
- Charter Member
- Posts: 43085
- Joined: Tue Nov 12, 2002 4:34 pm
- Location: Denver, CO
Post
by chulett »
If it inserts rows, I suspect it should be setup as a "Target" rather than as "Transform". What is your job design?
-craig
"You can never have too many knives" -- Logan Nine Fingers
-
agathaeleanor
- Participant
- Posts: 76
- Joined: Mon Mar 05, 2007 9:26 pm
- Location: Malaysia
Post
by agathaeleanor »
Thanks Craig for your response. The job design with a single STP stage. While the stored procedure defined insert into <table>.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
-
Contact:
Post
by ray.wurlod »
... then there's no way the SP could SELECT rows, is there?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
agathaeleanor
- Participant
- Posts: 76
- Joined: Mon Mar 05, 2007 9:26 pm
- Location: Malaysia
Post
by agathaeleanor »
Just found that the commit command in sp is placing at the wrong line. Should have commit before 'END' of procedure.
A useful note to take