Less row count with DSDB2PX Stage

dsisbank · Post by **dsisbank** » Fri Mar 12, 2010 6:31 am

Hi,

We have a different problem with DSDB2PX Stage.

When I queiried from DB2 database, rowcount is 35 M.

but when i transferred with using DSDB2PX stage, rowcount is 33 M.

also there is no warning or another information on job log.

My job is like this.

DSDB2PX------->Transformer-------->Oracle

On DB2 there are 35M data but when i transferred it with datastage, rowcount is 33M.( this count is not only oracle tablse count, i saw the same number in the log )

DB2 Stage retrieves data less then database !

do you have an idea, how can it be ?

is there a limit for dsdb2px stage ?

DB2 v 9.5 on AIX
Datastage Enterprise wit PX 8.0 on AIX
Oracle 10g on AIX (Not important)

ArndW · Post by **ArndW** » Fri Mar 12, 2010 7:27 am

There is no limit in that sense in DataStage. What is your SELECT clause? Could rows be dropping from the Transform stage?

dsisbank · Post by **dsisbank** » Fri Mar 12, 2010 8:42 am

ArndW wrote:There is no limit in that sense in DataStage. What is your SELECT clause? Could rows be dropping from the Transform stage? ...

My Select Query is:

SELECT
DATE('01.' || RIGHT(DIGITS(AY),2) || '.' || RIGHT(DIGITS(YIL),4)) + 1 MONTH - 1 DAY AS DONEM,
MUSTERI_NO AS MUSTERI_NO,
URUN_KD_OR AS URUN_KODU,
SUM(VASATI) AS VASATI,
SUM(KAR) AS KAR,
SUM(FAIZ_GELIRI) AS FAIZ_GELIRI,
SUM(FAIZ_GIDERI) AS FAIZ_GIDERI

FROM
SMODEL.ABM_KAROZET_ABMTA

WHERE
YIL = #YIL#
AND
AY = #AY#

GROUP BY
DATE('01.' || RIGHT(DIGITS(AY),2) || '.' || RIGHT(DIGITS(YIL),4)) + 1 MONTH - 1 DAY,
MUSTERI_NO,
URUN_KD_OR
;

This query returns 35M rows on Toad for DB2,
same query on datastage , DSDB2PX Stage retrieves 33M.
All links have 33M rows

There is not any reject or dropping row.

ArndW · Post by **ArndW** » Fri Mar 12, 2010 9:52 am

I would wager (but just a small amount) that either #YIL# or #AY# are in some way different between your DataStage job and your manual SELECT.
Also, if you open the monitor window do you see the same number of records going into your transform stage as are coming out?

John Smith · Post by **John Smith** » Sun Mar 14, 2010 4:44 pm

Why don't you substitute the job parameters with EXACTLY how you would run it in TOAD. Also check that you have nothing in your Transformer that is dropping records. Check in your job logs for dropped records.

dsisbank · Post by **dsisbank** » Mon Mar 15, 2010 3:31 am

My parameters are YIL( Year) and AY(Month).

I am giving same parameter with the query.

I am running the job with parameters "YIL=2010" and "AY=2"

also running the query same parameters

FROM
SMODEL.ABM_KAROZET_ABMTA

WHERE
YIL = 2010
AND
AY = 2

I am giving same parameters to job and query.

ArndW · Post by **ArndW** » Mon Mar 15, 2010 3:48 am

For one test run, replace your SQL in the DataStage job with hardcoded values for YIL and AY - are the results still incorrect? Again, in the monitor, check to see the row counts coming out of the DB2 stage and out of the transform stage - are they the same?

dsisbank · Post by **dsisbank** » Mon Mar 15, 2010 5:57 am

We solved our problem

Thanks to everyone.

Problem was;

At the source side (DB2 Stage) a Varchar field has defined with Unicode option, we changed it to Non Unicode and rerun the job. Rowcount has been fixed.

Thanks everyone again