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

Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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
add a comment |
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
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
add a comment |
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
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
count db2
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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
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.
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
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
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
add a comment |
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.
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
add a comment |
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.
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.
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
add a comment |
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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