db2 UDB count(*) returns 0 from the view, but select * returns valid data

Multi tool use
Multi tool use





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







0















I have encountered a strange situation in DB2 UDB V11.



When I run SELECT COUNT(*) FROM view_name it returns 0 rows.



However, when I run SELECT * FROM *view_name* the data is returned properly.



I have tried dropping and re-creating the view and ran REORG and RUNSTAT on the underlying table.



Have anyone seen this situation before?










share|improve this question

























  • This may happen due to non-enforced or/and violated constraints and different access plans. Show them both and DDL of participating objects.

    – Mark Barinstein
    Jan 4 at 8:25













  • I've seen this when at least one object referenced by the view was a nickname, the query used host-variables, and predicate-pushdown was not properly configured.

    – mao
    Jan 4 at 10:01











  • Hi Graham, the view has several case statements to mask the sensitive data and is based on 2 joined tables. The are only 2 predicates and WHERE condition is very simple so not entirely sure this is what causing the problem. Also, the problem only exists in TEST environment, QA works fine.

    – Dmitry
    Jan 6 at 5:25




















0















I have encountered a strange situation in DB2 UDB V11.



When I run SELECT COUNT(*) FROM view_name it returns 0 rows.



However, when I run SELECT * FROM *view_name* the data is returned properly.



I have tried dropping and re-creating the view and ran REORG and RUNSTAT on the underlying table.



Have anyone seen this situation before?










share|improve this question

























  • This may happen due to non-enforced or/and violated constraints and different access plans. Show them both and DDL of participating objects.

    – Mark Barinstein
    Jan 4 at 8:25













  • I've seen this when at least one object referenced by the view was a nickname, the query used host-variables, and predicate-pushdown was not properly configured.

    – mao
    Jan 4 at 10:01











  • Hi Graham, the view has several case statements to mask the sensitive data and is based on 2 joined tables. The are only 2 predicates and WHERE condition is very simple so not entirely sure this is what causing the problem. Also, the problem only exists in TEST environment, QA works fine.

    – Dmitry
    Jan 6 at 5:25
















0












0








0








I have encountered a strange situation in DB2 UDB V11.



When I run SELECT COUNT(*) FROM view_name it returns 0 rows.



However, when I run SELECT * FROM *view_name* the data is returned properly.



I have tried dropping and re-creating the view and ran REORG and RUNSTAT on the underlying table.



Have anyone seen this situation before?










share|improve this question
















I have encountered a strange situation in DB2 UDB V11.



When I run SELECT COUNT(*) FROM view_name it returns 0 rows.



However, when I run SELECT * FROM *view_name* the data is returned properly.



I have tried dropping and re-creating the view and ran REORG and RUNSTAT on the underlying table.



Have anyone seen this situation before?







count db2






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 3 at 22:22









Graham

3,868143860




3,868143860










asked Jan 3 at 21:34









DmitryDmitry

11




11













  • This may happen due to non-enforced or/and violated constraints and different access plans. Show them both and DDL of participating objects.

    – Mark Barinstein
    Jan 4 at 8:25













  • I've seen this when at least one object referenced by the view was a nickname, the query used host-variables, and predicate-pushdown was not properly configured.

    – mao
    Jan 4 at 10:01











  • Hi Graham, the view has several case statements to mask the sensitive data and is based on 2 joined tables. The are only 2 predicates and WHERE condition is very simple so not entirely sure this is what causing the problem. Also, the problem only exists in TEST environment, QA works fine.

    – Dmitry
    Jan 6 at 5:25





















  • This may happen due to non-enforced or/and violated constraints and different access plans. Show them both and DDL of participating objects.

    – Mark Barinstein
    Jan 4 at 8:25













  • I've seen this when at least one object referenced by the view was a nickname, the query used host-variables, and predicate-pushdown was not properly configured.

    – mao
    Jan 4 at 10:01











  • Hi Graham, the view has several case statements to mask the sensitive data and is based on 2 joined tables. The are only 2 predicates and WHERE condition is very simple so not entirely sure this is what causing the problem. Also, the problem only exists in TEST environment, QA works fine.

    – Dmitry
    Jan 6 at 5:25



















This may happen due to non-enforced or/and violated constraints and different access plans. Show them both and DDL of participating objects.

– Mark Barinstein
Jan 4 at 8:25







This may happen due to non-enforced or/and violated constraints and different access plans. Show them both and DDL of participating objects.

– Mark Barinstein
Jan 4 at 8:25















I've seen this when at least one object referenced by the view was a nickname, the query used host-variables, and predicate-pushdown was not properly configured.

– mao
Jan 4 at 10:01





I've seen this when at least one object referenced by the view was a nickname, the query used host-variables, and predicate-pushdown was not properly configured.

– mao
Jan 4 at 10:01













Hi Graham, the view has several case statements to mask the sensitive data and is based on 2 joined tables. The are only 2 predicates and WHERE condition is very simple so not entirely sure this is what causing the problem. Also, the problem only exists in TEST environment, QA works fine.

– Dmitry
Jan 6 at 5:25







Hi Graham, the view has several case statements to mask the sensitive data and is based on 2 joined tables. The are only 2 predicates and WHERE condition is very simple so not entirely sure this is what causing the problem. Also, the problem only exists in TEST environment, QA works fine.

– Dmitry
Jan 6 at 5:25














1 Answer
1






active

oldest

votes


















0














I have seen this before when a MQT was involved. Because the optimizer will use the best way to query and can rewrite it to use the MQT this situation could happen when the MQT has not been refreshed but the table itsef has been updated/deleted already.



So check if any MQTs are involved.






share|improve this answer
























  • Michael, thank you for your reply. You are absolutely correct, the underlying table this view is based on defined as MQT (ORGANIZE BY clause is present in the DDL) and it has been dropped and re-created and loaded with data recently. The underlying table contains data but view as I have mentioned earlier does not return anything but 0 when select count() is executed against it. However, SELECT * does return the data properly. Any suggestion how to refresh the MQT definition so the count() on the view would work properly?

    – Dmitry
    Jan 6 at 5:17













  • Check out the refresh table statement here ibm.com/support/knowledgecenter/SSEPGG_11.1.0/… - and do not forget to mark it as answer please

    – MichaelTiefenbacher
    Jan 6 at 11:20












Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54030112%2fdb2-udb-count-returns-0-from-the-view-but-select-returns-valid-data%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














I have seen this before when a MQT was involved. Because the optimizer will use the best way to query and can rewrite it to use the MQT this situation could happen when the MQT has not been refreshed but the table itsef has been updated/deleted already.



So check if any MQTs are involved.






share|improve this answer
























  • Michael, thank you for your reply. You are absolutely correct, the underlying table this view is based on defined as MQT (ORGANIZE BY clause is present in the DDL) and it has been dropped and re-created and loaded with data recently. The underlying table contains data but view as I have mentioned earlier does not return anything but 0 when select count() is executed against it. However, SELECT * does return the data properly. Any suggestion how to refresh the MQT definition so the count() on the view would work properly?

    – Dmitry
    Jan 6 at 5:17













  • Check out the refresh table statement here ibm.com/support/knowledgecenter/SSEPGG_11.1.0/… - and do not forget to mark it as answer please

    – MichaelTiefenbacher
    Jan 6 at 11:20
















0














I have seen this before when a MQT was involved. Because the optimizer will use the best way to query and can rewrite it to use the MQT this situation could happen when the MQT has not been refreshed but the table itsef has been updated/deleted already.



So check if any MQTs are involved.






share|improve this answer
























  • Michael, thank you for your reply. You are absolutely correct, the underlying table this view is based on defined as MQT (ORGANIZE BY clause is present in the DDL) and it has been dropped and re-created and loaded with data recently. The underlying table contains data but view as I have mentioned earlier does not return anything but 0 when select count() is executed against it. However, SELECT * does return the data properly. Any suggestion how to refresh the MQT definition so the count() on the view would work properly?

    – Dmitry
    Jan 6 at 5:17













  • Check out the refresh table statement here ibm.com/support/knowledgecenter/SSEPGG_11.1.0/… - and do not forget to mark it as answer please

    – MichaelTiefenbacher
    Jan 6 at 11:20














0












0








0







I have seen this before when a MQT was involved. Because the optimizer will use the best way to query and can rewrite it to use the MQT this situation could happen when the MQT has not been refreshed but the table itsef has been updated/deleted already.



So check if any MQTs are involved.






share|improve this answer













I have seen this before when a MQT was involved. Because the optimizer will use the best way to query and can rewrite it to use the MQT this situation could happen when the MQT has not been refreshed but the table itsef has been updated/deleted already.



So check if any MQTs are involved.







share|improve this answer












share|improve this answer



share|improve this answer










answered Jan 4 at 14:29









MichaelTiefenbacherMichaelTiefenbacher

2,3172715




2,3172715













  • Michael, thank you for your reply. You are absolutely correct, the underlying table this view is based on defined as MQT (ORGANIZE BY clause is present in the DDL) and it has been dropped and re-created and loaded with data recently. The underlying table contains data but view as I have mentioned earlier does not return anything but 0 when select count() is executed against it. However, SELECT * does return the data properly. Any suggestion how to refresh the MQT definition so the count() on the view would work properly?

    – Dmitry
    Jan 6 at 5:17













  • Check out the refresh table statement here ibm.com/support/knowledgecenter/SSEPGG_11.1.0/… - and do not forget to mark it as answer please

    – MichaelTiefenbacher
    Jan 6 at 11:20



















  • Michael, thank you for your reply. You are absolutely correct, the underlying table this view is based on defined as MQT (ORGANIZE BY clause is present in the DDL) and it has been dropped and re-created and loaded with data recently. The underlying table contains data but view as I have mentioned earlier does not return anything but 0 when select count() is executed against it. However, SELECT * does return the data properly. Any suggestion how to refresh the MQT definition so the count() on the view would work properly?

    – Dmitry
    Jan 6 at 5:17













  • Check out the refresh table statement here ibm.com/support/knowledgecenter/SSEPGG_11.1.0/… - and do not forget to mark it as answer please

    – MichaelTiefenbacher
    Jan 6 at 11:20

















Michael, thank you for your reply. You are absolutely correct, the underlying table this view is based on defined as MQT (ORGANIZE BY clause is present in the DDL) and it has been dropped and re-created and loaded with data recently. The underlying table contains data but view as I have mentioned earlier does not return anything but 0 when select count() is executed against it. However, SELECT * does return the data properly. Any suggestion how to refresh the MQT definition so the count() on the view would work properly?

– Dmitry
Jan 6 at 5:17







Michael, thank you for your reply. You are absolutely correct, the underlying table this view is based on defined as MQT (ORGANIZE BY clause is present in the DDL) and it has been dropped and re-created and loaded with data recently. The underlying table contains data but view as I have mentioned earlier does not return anything but 0 when select count() is executed against it. However, SELECT * does return the data properly. Any suggestion how to refresh the MQT definition so the count() on the view would work properly?

– Dmitry
Jan 6 at 5:17















Check out the refresh table statement here ibm.com/support/knowledgecenter/SSEPGG_11.1.0/… - and do not forget to mark it as answer please

– MichaelTiefenbacher
Jan 6 at 11:20





Check out the refresh table statement here ibm.com/support/knowledgecenter/SSEPGG_11.1.0/… - and do not forget to mark it as answer please

– MichaelTiefenbacher
Jan 6 at 11:20




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54030112%2fdb2-udb-count-returns-0-from-the-view-but-select-returns-valid-data%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







NRlrrFluknVFp5OrRJ sHJrqBqyuGxmPxx UeIf96y9Ppg4GC2PAd scQNOlz,RYulHsBP WXoYGBz,l0nWrtswV,kVJzhyw
FQjhx8IuUanPKYdMp 7q8r1F UmvpIWU3qz1QWI

Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas