Determine queries run by database?
- Thread starter wstromer
- Start date
Rob Fitzpatrick
ProgressTalk.com Sponsor
First off, what are you trying to accomplish? And how are the queries being run?
If it is in the SQL engine you can look into Client Request Statement Caching. It can cache the recent queries that have been run (up to 31 lines, I believe). Gus did a presentation on it at the Revolution conference last year. You can download the presentation here.
You can use the same technique to get some information about certain ABL clients' call stacks. This has to be configured in advance, i.e. before you need it. The database doesn't maintain any master list of queries in the database or in a flat file. You can also get similar information about ABL clients' call stacks on an ad hoc basis, assuming you have access to the system where they are running, with the proGetStack command. This will get you module names and line numbers, so you will need source access for this information to be meaningful.
Either way, the techniques above are just point-in-time sampling, and are not comprehensive. OpenEdge auditing would not help you, as you can audit record create, update, and delete events, but not reads. And even if you could audit reads you really wouldn't want to unless this was a test database where you didn't care about performance. If you think about it, read auditing would turn every database read into a read and a write, and your performance would be abysmal. Also there would be some cost involved, in terms of development, testing, performance tuning, and additional administration overhead. It would also chew through a lot of disk space.
You could maybe do something by writing database trigger code, but is it whatever you're trying to accomplish worth all that effort?
If it is in the SQL engine you can look into Client Request Statement Caching. It can cache the recent queries that have been run (up to 31 lines, I believe). Gus did a presentation on it at the Revolution conference last year. You can download the presentation here.
You can use the same technique to get some information about certain ABL clients' call stacks. This has to be configured in advance, i.e. before you need it. The database doesn't maintain any master list of queries in the database or in a flat file. You can also get similar information about ABL clients' call stacks on an ad hoc basis, assuming you have access to the system where they are running, with the proGetStack command. This will get you module names and line numbers, so you will need source access for this information to be meaningful.
Either way, the techniques above are just point-in-time sampling, and are not comprehensive. OpenEdge auditing would not help you, as you can audit record create, update, and delete events, but not reads. And even if you could audit reads you really wouldn't want to unless this was a test database where you didn't care about performance. If you think about it, read auditing would turn every database read into a read and a write, and your performance would be abysmal. Also there would be some cost involved, in terms of development, testing, performance tuning, and additional administration overhead. It would also chew through a lot of disk space.
You could maybe do something by writing database trigger code, but is it whatever you're trying to accomplish worth all that effort?
We have a number of reports that are being run by our ERP system. We would like to automate the collection of data from these reports and pipe this data directly into a data warehouse from Progess. We can only do this if we know the query details. Some of the reports are available in cleartext *.p files which we can (more or less) interpret but others are in Crystal and we don't have access to the business view file so the exact query is hidden. We can turn on auditing for our test database and run the same reports so performance is not an issue.
Rob Fitzpatrick
ProgressTalk.com Sponsor
So you have a mix of ABL and SQL queries? Do you have source for all of the ABL side?
All queries, both ABL and SQL will come from QAD EE. I'm only interested in what kind of SQL hits the database.
Are ABL queries somehow translated to SQL? I don't really care to see any of the ABL, just want to know how this hits the database so I can recreate this in an ETL job and hit the database directly.
Are ABL queries somehow translated to SQL? I don't really care to see any of the ABL, just want to know how this hits the database so I can recreate this in an ETL job and hit the database directly.
Rob Fitzpatrick
ProgressTalk.com Sponsor
SQL-92 and ABL are handled separately in OpenEdge databases. ABL doesn't get translated into SQL, there are separate query engines for each.
If you're focused on SQL queries and can do some controlled testing then I think Client Request Statement Caching would be a good fit for you. You can enable it for all clients, for a single client, all clients of a given server, all future clients of a given server. It remains on, if memory serves, until disabled or until the next DB shutdown. Data is cached in the client's _Connect VST record. I think it may be written out to a file on disk if it grows past a certain size. Given that it is written in the VST, you can also write ABL code to query it if you like.
Check out Gus' presentation, linked above. It should give you a good overview of how to use the feature and what information it provides. Also, for SQL clients there is little to no performance hit in using it because the data is already server-side so doesn't have to be sent from a client as it does with ABL clients.
If you're focused on SQL queries and can do some controlled testing then I think Client Request Statement Caching would be a good fit for you. You can enable it for all clients, for a single client, all clients of a given server, all future clients of a given server. It remains on, if memory serves, until disabled or until the next DB shutdown. Data is cached in the client's _Connect VST record. I think it may be written out to a file on disk if it grows past a certain size. Given that it is written in the VST, you can also write ABL code to query it if you like.
Check out Gus' presentation, linked above. It should give you a good overview of how to use the feature and what information it provides. Also, for SQL clients there is little to no performance hit in using it because the data is already server-side so doesn't have to be sent from a client as it does with ABL clients.