Records Duplicating

ds2000 · 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 · 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.

ray.wurlod · 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?

nagarjuna · 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 ...

ds2000 · 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 · 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 · 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 · 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?

ds2000 · 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 · Post by **samsuf2002** » Mon Jun 01, 2009 10:19 pm

Try using a join or lookup stage for joining instead of SQL.

ds2000 · 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 · Post by **ds2000** » Tue Jun 02, 2009 9:05 am

Any thoughts on this issue ?

samsuf2002 · 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 ?