DB2 paged query

[fedemartino]

Hi! I am having some issues with importing data from DB2. For some reason, the query being executed when importing data, is being ordered by an unidexed column and then filtered by rowid:

Statement start timestamp = 06/20/2019 12:45:26.197566

Dynamic SQL statement text:

select "AA1", "DATE1", "AAID", "JID", "SID", "NUMB", "AID", "SAID", "AMOUNT", "EID" from (select "$Ordered"."AA1", "$Ordered"."DATE1", "$Ordered"."AAID", "$Ordered"."JID", "$Ordered"."SID", "$Ordered"."NUMB", "$Ordered"."AID", "$Ordered"."SAID", "$Ordered"."AMOUNT", "$Ordered"."EID", row_number() over (order by "$Ordered"."AA1") as "$Row" from ( select "_"."AA1", "_"."DATE1", "_"."AAID", "_"."JID", "_"."SID", "_"."NUMB", "_"."AID", "_"."SAID", "_"."AMOUNT", "_"."EID" from ( select "_"."AA1", "_"."DATE1", "_"."AAID", "_"."JID", "_"."SID", "_"."NUMB", "_"."AID", "_"."SAID", "_"."AMOUNT", "_"."EID" from "USRBANCA"."TABLE1" "_" where "_"."DATE1" >= timestamp('2019-03-01 00:00:00') and "_"."DATE1" <= timestamp('2019-03-31 00:00:00') ) "_" where "_"."EID" = 2 and "_"."NUMB" = 1 ) "$Ordered" order by "$Ordered"."AA1") "$Paged" where "$Paged"."$Row" <= 4096 order by "$Paged"."$Row"

Why is this happening? If I am importing all data, why is the query only requesting the first 4096 rows?

[v-piga-msft]

Hi @fedemartino ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[v-piga-msft]

Hi @fedemartino ,

Actually, if you enter the query under SQL Statement, you will import the data based on your query.

If I understand your scenario correctly that you think that you didn't load the right data based on the query when you get data in power bi?

If so, could you get the right data with the query in your DB2?

Normally, the data you get in your DB2 with the query should be the same as that in power bi.

---

Why is this happening? If I am importing all data, why is the query only requesting the first 4096 rows?

---

It seems that the first 4096 rows may be caused by this query "$Row" <= 4096.

Best Regards,

Cherry

[fedemartino]

Hi @v-piga-msft 

This is not really the problem. The issue is that the server is receiving that query when it should not be filtering by rowid. I don't want to change the source because that would not allow me to apply the filter by parameters and I have to re aply all the steps I've done. 

The server should receive the same select query, except it shouldn't filter by rowid, since its a full import.

Thanks!