Showing less data in the database

[shivan]

I am running a job. The job suppose to load 1.7 million rows in the mainframe table. It runs successfully but in the actual mainframe table it only has 40000 rows. We are doings some grouping. But i dont think so grouping reduce the data to that extent.
Data is loading from sql server to mainframe.

thanks
shivan

[pnchowdary]

Hi Shivan,

1) Check how many rows you are extracting from source( SQL server)

2) At each stage where you have a constraint, store the reject records in a reject file

3) Then finally,

Code: Select all

No of rows you are trying to load to target = No of rows you extracted from source + Total No of rows eliminated because of constraints

That being said, if you have records in the source, having the same keys but other columns are different and assuming you are using the strategy of insert and update. Then multiple rows from the source might get converted into one row, depending upon the strategy you are trying to implement. This might also cause a difference in numbers.

I hope this will get you started in your analysis.

[shivan]

but i m just using grouping. How can it only process 40000 out of 1.7 million.

thanks
shivan

Hi Shivan,

1) Check how many rows you are extracting from source( SQL server)

2) At each stage where you have a constraint, store the reject records in a reject file

3) Then finally,

Code: Select all

No of rows you are trying to load to target = No of rows you extracted from source + Total No of rows eliminated because of constraints

That being said, if you have records in the source, having the same keys but other columns are different and assuming you are using the strategy of insert and update. Then multiple rows from the source might get converted into one row, depending upon the strategy you are trying to implement. This might also cause a difference in numbers.

I hope this will get you started in your analysis.

[shivan]

i am loading the data from sql server to mainframe.

shivan

but i m just using grouping. How can it only process 40000 out of 1.7 million.

thanks
shivan

Hi Shivan,

1) Check how many rows you are extracting from source( SQL server)

2) At each stage where you have a constraint, store the reject records in a reject file

3) Then finally,

Code: Select all

No of rows you are trying to load to target = No of rows you extracted from source + Total No of rows eliminated because of constraints

That being said, if you have records in the source, having the same keys but other columns are different and assuming you are using the strategy of insert and update. Then multiple rows from the source might get converted into one row, depending upon the strategy you are trying to implement. This might also cause a difference in numbers.

I hope this will get you started in your analysis.

[pnchowdary]

Hi Shivan,

What exactly do you mean by "but i m just using grouping". Could you please eloborate on what you are exactly doing by grouping?

[shivan]

what i doing is that.
i wrote a sql query to extract data from sql server. then i m loading in db2. The sql query extracts 1.7 million rows. But in db2 only 40,000. There is one to one mapping.

thanks
shivan

Hi Shivan,

What exactly do you mean by "but i m just using grouping". Could you please eloborate on what you are exactly doing by grouping?

[pnchowdary]

Hi Shivan,

After extracting data from SQL server and before loading into DB2, what tranformations are you applying to the data?

[shivan]

mapping is like:
some columns, if data is "y" , do this else do this.
others are one to one

thanks
shivan

Hi Shivan,

After extracting data from SQL server and before loading into DB2, what tranformations are you applying to the data?

[pnchowdary]

Hi Shivan,

Could you tell me how many rows you see on the link that goes into the transformer and how many rows you see on the link that goes out of the transformer?

[shivan]

they are same like 1.7 million

shivan

Hi Shivan,

Could you tell me how many rows you see on the link that goes into the transformer and how many rows you see on the link that goes out of the transformer?

[elavenil]

Hi Shivan,

Are there any warnings in the director? If there are, can you look at the warnings.

Regards
Saravanan

[ds_developer]

It might happen this way if the DB2 table is missing the designation of one or more fields as part of a compound key. In essence, updating the same row over and over.

John

Yeah - it would be the other way around wouldn't it. Too many keys, not too few.

[shivan]

The problem is fixed. What was happening. The sql server has different set of primary keys then db2. So when the data was loading from sql server in the db2, it was neglecting the rows with a same value , as the pk should be unique. Example,
sql server data:
1(pk) 2(pk) 3 4 5
1 2 3 4 7
1 2 3 4 8


and the structure in db2 was

1(pk) 2(pk) 3(pk) 4 5
1(pk) 2(pk) 3(pk) 4 7
1 2 3 4 8

i hope it helps u understand what was the problem

thanks

It might happen this way if the DB2 table is missing the designation of one or more fields as part of a compound key. In essence, updating the same row over and over.

John

[ray.wurlod]

Of course it's possible. If you grouped by sex ('M' or 'F') you would only get two output rows. Think about it. How many distinct values are there in the column by which you are grouping?