Mixing
How to find all objects that query a table in DB2
I want to find all objects (Stored Procedures, views, etc) that make queries to each table in DB2 hosted in IBM iSeries. Is there a way to track this over a time period say 1 month.
db2 db2-400
edited Jan 3 at 15:38
jmarkmurphy
7,9062041
asked Jan 1 at 11:58
Suren NanayakkaraSuren Nanayakkara
61
add a comment |
I want to find all objects (Stored Procedures, views, etc) that make queries to each table in DB2 hosted in IBM iSeries. Is there a way to track this over a time period say 1 month.
db2 db2-400
edited Jan 3 at 15:38
jmarkmurphy
7,9062041
asked Jan 1 at 11:58
Suren NanayakkaraSuren Nanayakkara
61
Possible duplicate of In db2 how to find all the Stored Procedures having a given text in it
– Clockwork-Muse
Jan 2 at 7:25
Although note that that can't find dynamic SQL queries. There's ways to set up performance monitoring for specific table that will yield queries run against them, but if you enable it globally it's going to make performance tank.
– Clockwork-Muse
Jan 2 at 7:37
You can journal a table and the journal will tell all the objects that have used the table over time. Turning journaling on for all tables probably isn't such a bad idea as it once seemed. Journaling adds two plus times IO and disk space cost.
– danny117
Jan 2 at 20:27
add a comment |
I want to find all objects (Stored Procedures, views, etc) that make queries to each table in DB2 hosted in IBM iSeries. Is there a way to track this over a time period say 1 month.
db2 db2-400
edited Jan 3 at 15:38
jmarkmurphy
7,9062041
asked Jan 1 at 11:58
Suren NanayakkaraSuren Nanayakkara
61
I want to find all objects (Stored Procedures, views, etc) that make queries to each table in DB2 hosted in IBM iSeries. Is there a way to track this over a time period say 1 month.
db2 db2-400
db2 db2-400
edited Jan 3 at 15:38
jmarkmurphy
7,9062041
asked Jan 1 at 11:58
Suren NanayakkaraSuren Nanayakkara
61
edited Jan 3 at 15:38
jmarkmurphy
7,9062041
asked Jan 1 at 11:58
Suren NanayakkaraSuren Nanayakkara
61
edited Jan 3 at 15:38
jmarkmurphy
7,9062041
edited Jan 3 at 15:38
jmarkmurphy
7,9062041
edited Jan 3 at 15:38
jmarkmurphy
7,9062041
7,9062041
asked Jan 1 at 11:58
Suren NanayakkaraSuren Nanayakkara
61
asked Jan 1 at 11:58
Suren NanayakkaraSuren Nanayakkara
61
asked Jan 1 at 11:58
Suren NanayakkaraSuren Nanayakkara
61
61
Possible duplicate of In db2 how to find all the Stored Procedures having a given text in it
– Clockwork-Muse
Jan 2 at 7:25
Although note that that can't find dynamic SQL queries. There's ways to set up performance monitoring for specific table that will yield queries run against them, but if you enable it globally it's going to make performance tank.
– Clockwork-Muse
Jan 2 at 7:37
You can journal a table and the journal will tell all the objects that have used the table over time. Turning journaling on for all tables probably isn't such a bad idea as it once seemed. Journaling adds two plus times IO and disk space cost.
– danny117
Jan 2 at 20:27
add a comment |
Possible duplicate of In db2 how to find all the Stored Procedures having a given text in it
– Clockwork-Muse
Jan 2 at 7:25
Although note that that can't find dynamic SQL queries. There's ways to set up performance monitoring for specific table that will yield queries run against them, but if you enable it globally it's going to make performance tank.
– Clockwork-Muse
Jan 2 at 7:37
You can journal a table and the journal will tell all the objects that have used the table over time. Turning journaling on for all tables probably isn't such a bad idea as it once seemed. Journaling adds two plus times IO and disk space cost.
– danny117
Jan 2 at 20:27
Possible duplicate of In db2 how to find all the Stored Procedures having a given text in it
– Clockwork-Muse
Jan 2 at 7:25
Possible duplicate of In db2 how to find all the Stored Procedures having a given text in it
– Clockwork-Muse
Jan 2 at 7:25
Although note that that can't find dynamic SQL queries. There's ways to set up performance monitoring for specific table that will yield queries run against them, but if you enable it globally it's going to make performance tank.
– Clockwork-Muse
Jan 2 at 7:37
Although note that that can't find dynamic SQL queries. There's ways to set up performance monitoring for specific table that will yield queries run against them, but if you enable it globally it's going to make performance tank.
– Clockwork-Muse
Jan 2 at 7:37
You can journal a table and the journal will tell all the objects that have used the table over time. Turning journaling on for all tables probably isn't such a bad idea as it once seemed. Journaling adds two plus times IO and disk space cost.
– danny117
Jan 2 at 20:27
You can journal a table and the journal will tell all the objects that have used the table over time. Turning journaling on for all tables probably isn't such a bad idea as it once seemed. Journaling adds two plus times IO and disk space cost.
– danny117
Jan 2 at 20:27
add a comment |
1 Answer
1
active
oldest
votes
You can view the plan cache. This is not exactly what you are asking for, but I believe it should be able to give you the information you need. The plan cache is a cache of all SQL statements that have been executed, along with performance in formation about them. You can filter the plan cache by a number of things, including the run date, and objects referenced.
This is an interactive tool that is available in System i Navigator, or more recently in iACS. In iACS, you can search the plan cache by:
- Navigate to Database -> SQL Performance Center.
- Under the
Plan Cachetab, click theShow Statementsbutton.
The resulting dialog has filters on the left, and statements on the right sorted by total (cumulative) processing time in descending order. As long as you don't need some automated task to handle your queries, and you are not looking for the programs involved, then this should give you the information you are looking for.
If you are looking for the program that references a given table, you can use the command DSPPGMREF. This command allows you to capture (in a file in your case) all the objects referenced by a given program. As long as you run the command over all libraries that contain programs you are interested in, then you can query the output table to find every program that references a given table (except dynamic queries).
answered Jan 2 at 14:13
jmarkmurphyjmarkmurphy
7,9062041
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%2f53995231%2fhow-to-find-all-objects-that-query-a-table-in-db2%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
You can view the plan cache. This is not exactly what you are asking for, but I believe it should be able to give you the information you need. The plan cache is a cache of all SQL statements that have been executed, along with performance in formation about them. You can filter the plan cache by a number of things, including the run date, and objects referenced.
This is an interactive tool that is available in System i Navigator, or more recently in iACS. In iACS, you can search the plan cache by:
- Navigate to Database -> SQL Performance Center.
- Under the
Plan Cachetab, click theShow Statementsbutton.
The resulting dialog has filters on the left, and statements on the right sorted by total (cumulative) processing time in descending order. As long as you don't need some automated task to handle your queries, and you are not looking for the programs involved, then this should give you the information you are looking for.
If you are looking for the program that references a given table, you can use the command DSPPGMREF. This command allows you to capture (in a file in your case) all the objects referenced by a given program. As long as you run the command over all libraries that contain programs you are interested in, then you can query the output table to find every program that references a given table (except dynamic queries).
answered Jan 2 at 14:13
jmarkmurphyjmarkmurphy
7,9062041
add a comment |
You can view the plan cache. This is not exactly what you are asking for, but I believe it should be able to give you the information you need. The plan cache is a cache of all SQL statements that have been executed, along with performance in formation about them. You can filter the plan cache by a number of things, including the run date, and objects referenced.
This is an interactive tool that is available in System i Navigator, or more recently in iACS. In iACS, you can search the plan cache by:
- Navigate to Database -> SQL Performance Center.
- Under the
Plan Cachetab, click theShow Statementsbutton.
The resulting dialog has filters on the left, and statements on the right sorted by total (cumulative) processing time in descending order. As long as you don't need some automated task to handle your queries, and you are not looking for the programs involved, then this should give you the information you are looking for.
If you are looking for the program that references a given table, you can use the command DSPPGMREF. This command allows you to capture (in a file in your case) all the objects referenced by a given program. As long as you run the command over all libraries that contain programs you are interested in, then you can query the output table to find every program that references a given table (except dynamic queries).
answered Jan 2 at 14:13
jmarkmurphyjmarkmurphy
7,9062041
add a comment |
You can view the plan cache. This is not exactly what you are asking for, but I believe it should be able to give you the information you need. The plan cache is a cache of all SQL statements that have been executed, along with performance in formation about them. You can filter the plan cache by a number of things, including the run date, and objects referenced.
This is an interactive tool that is available in System i Navigator, or more recently in iACS. In iACS, you can search the plan cache by:
- Navigate to Database -> SQL Performance Center.
- Under the
Plan Cachetab, click theShow Statementsbutton.
The resulting dialog has filters on the left, and statements on the right sorted by total (cumulative) processing time in descending order. As long as you don't need some automated task to handle your queries, and you are not looking for the programs involved, then this should give you the information you are looking for.
If you are looking for the program that references a given table, you can use the command DSPPGMREF. This command allows you to capture (in a file in your case) all the objects referenced by a given program. As long as you run the command over all libraries that contain programs you are interested in, then you can query the output table to find every program that references a given table (except dynamic queries).
answered Jan 2 at 14:13
jmarkmurphyjmarkmurphy
7,9062041
You can view the plan cache. This is not exactly what you are asking for, but I believe it should be able to give you the information you need. The plan cache is a cache of all SQL statements that have been executed, along with performance in formation about them. You can filter the plan cache by a number of things, including the run date, and objects referenced.
This is an interactive tool that is available in System i Navigator, or more recently in iACS. In iACS, you can search the plan cache by:
- Navigate to Database -> SQL Performance Center.
- Under the
Plan Cachetab, click theShow Statementsbutton.
The resulting dialog has filters on the left, and statements on the right sorted by total (cumulative) processing time in descending order. As long as you don't need some automated task to handle your queries, and you are not looking for the programs involved, then this should give you the information you are looking for.
If you are looking for the program that references a given table, you can use the command DSPPGMREF. This command allows you to capture (in a file in your case) all the objects referenced by a given program. As long as you run the command over all libraries that contain programs you are interested in, then you can query the output table to find every program that references a given table (except dynamic queries).
answered Jan 2 at 14:13
jmarkmurphyjmarkmurphy
7,9062041
answered Jan 2 at 14:13
jmarkmurphyjmarkmurphy
7,9062041
answered Jan 2 at 14:13
jmarkmurphyjmarkmurphy
7,9062041
answered Jan 2 at 14:13
jmarkmurphyjmarkmurphy
7,9062041
7,9062041
add a comment |
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%2f53995231%2fhow-to-find-all-objects-that-query-a-table-in-db2%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
Possible duplicate of In db2 how to find all the Stored Procedures having a given text in it
– Clockwork-Muse
Jan 2 at 7:25
Although note that that can't find dynamic SQL queries. There's ways to set up performance monitoring for specific table that will yield queries run against them, but if you enable it globally it's going to make performance tank.
– Clockwork-Muse
Jan 2 at 7:37
You can journal a table and the journal will tell all the objects that have used the table over time. Turning journaling on for all tables probably isn't such a bad idea as it once seemed. Journaling adds two plus times IO and disk space cost.
– danny117
Jan 2 at 20:27