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
ds2000
Premium Member
Posts: 109 Joined: Sun Apr 22, 2007 7:25 pm
Location: ny
Post
by ds2000 » Mon Jun 01, 2009 2:36 pm
im using db2 api stage to extract data from two tables in db2 z/os. When i run sql in db2 api stage i get some records duplicated even both tables have single record and to verify this i have this sql in db2 client and get only one record.
samsuf2002
Premium Member
Posts: 397 Joined: Wed Apr 12, 2006 2:28 pm
Location: Tennesse
Post
by samsuf2002 » Mon Jun 01, 2009 3:38 pm
Are you doing a join ?
Posting the sql you are using will help us to help you.
hi sam here
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Mon Jun 01, 2009 4:34 pm
Two nodes each executing the same query? Is your DB2 stage executing in parallel or sequential mode?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
nagarjuna
Premium Member
Posts: 533 Joined: Fri Jun 27, 2008 9:11 pm
Location: Chicago
Post
by nagarjuna » Mon Jun 01, 2009 5:55 pm
As mentioned by ray , you are running the DB2 stage on two nodes thats the reason why records being duplicated ...
Nag
ds2000
Premium Member
Posts: 109 Joined: Sun Apr 22, 2007 7:25 pm
Location: ny
Post
by ds2000 » Mon Jun 01, 2009 8:14 pm
Yes, it has defaul option of SEQUENTIAL. I changed the option to SEQUENTIAL which resolved the problem even when reversed the option back to defaul (Sequential) it produced correct result. Looks like default setting needed to be refereshed !!
ds2000
Premium Member
Posts: 109 Joined: Sun Apr 22, 2007 7:25 pm
Location: ny
Post
by ds2000 » Mon Jun 01, 2009 8:24 pm
Please disregard my last post because i was running sql with distinct in it. Now when i run actual sql with SEQUENTIAL mode records are being duplicated.
I have simple join condition on key and as i mentioned there is one record in each table with matching key.
Is there a partition on db tables making this scenario ?
ds2000
Premium Member
Posts: 109 Joined: Sun Apr 22, 2007 7:25 pm
Location: ny
Post
by ds2000 » Mon Jun 01, 2009 9:31 pm
adding more info here.
Not all records are being duplicated only certain records are being duplicated.
e.g. SQL query output gives 28 records and db2 stage is producing 44 records.
Any thoughts.
chulett
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Mon Jun 01, 2009 9:35 pm
As asked - how many nodes? If you run on a single node, is all ok?
-craig
"You can never have too many knives" -- Logan Nine Fingers
ds2000
Premium Member
Posts: 109 Joined: Sun Apr 22, 2007 7:25 pm
Location: ny
Post
by ds2000 » Mon Jun 01, 2009 9:56 pm
I tried running SEQUENTIAL and with 1 NODE both runs produced wrong number of rows.
samsuf2002
Premium Member
Posts: 397 Joined: Wed Apr 12, 2006 2:28 pm
Location: Tennesse
Post
by samsuf2002 » Mon Jun 01, 2009 10:19 pm
Try using a join or lookup stage for joining instead of SQL.
hi sam here
ds2000
Premium Member
Posts: 109 Joined: Sun Apr 22, 2007 7:25 pm
Location: ny
Post
by ds2000 » Mon Jun 01, 2009 10:30 pm
SQL join is requirement of this job otherwise db extract is too big if i want to use ds join.
ds2000
Premium Member
Posts: 109 Joined: Sun Apr 22, 2007 7:25 pm
Location: ny
Post
by ds2000 » Tue Jun 02, 2009 9:05 am
Any thoughts on this issue ?
samsuf2002
Premium Member
Posts: 397 Joined: Wed Apr 12, 2006 2:28 pm
Location: Tennesse
Post
by samsuf2002 » Tue Jun 02, 2009 9:52 am
I believe join will handle the big volume..... can you post the exact SQL you r running on data stage and db2 client ?
hi sam here