How to track the sql script running in the database?

Are you asking how to determine what the currently executing query for a session is at run time?

If so then you need to be running 10.1C or better.  First you need to enable the "Client Database-Request Statement Cache" in PROMON.  Go to the R&D menu, "1. Status",  "18. Client Database-Request Statement Cache".

After enabling the cache you can either use PROMON or use the _Connect-Cache* fields in the _Connect VST to obtain this info.  For a 4GL session it shows the current procedure and line number, you then look at that procedure to determine the query.  For a SQL-92 session it shows the SQL.

You can also download ProTop for an easy to use GUI interface.

If you're asking how to determine this at compile time then the answer is COMPILE XREF.

Thanks for your answer, but my system is 9.1e. How to do it?

Upgrade! 

When you say "sql script" what do you mean?  Are you using an ODBC client and trying to determine what SQL is being generated?  Or are you using a 4GL client and wanting to know what the 4GL query is doing?

The difference is important.  There are two very different query engines and optimizers and the approach you would use is very different.

It might also help to know why you want to know what is running.  That could also influence the answer.

I got a 4GL program, but the running result had some  problem. I want to konw what tables are be effected.

Ok.  To clarify a bit -- 4GL sessions do not execute SQL scripts.  FIND, FOR EACH, OPEN QUERY and so forth get compiled into r-code and handled by a different engine.  Query optimization on the 4GL side of things is static and is handled at compile time.  If you have source code you can compile it with something like:

COMPILE xyzzy.p XREF xyzzy.xrf

in order to see what indexes are being choosen.  Index selection is shown in the XREF output as "SEARCH".

With version 9 you do not have many runtime diagnostic options.  Upgrading to 10.1C or better will greatly increase those options.

Without an upgrade the best that you can do at runtime would be to monitor table and index utilization.  To do that you need to add the -tablerangesize and -indexrangesize parameters to your db startup.  (The default value of 50 tables and indexes is alnmost certainly inadequate.)  This will allow you to see, on a per table basis, how many creates, reads, updates and deletes are occuring and what indexes are being used at runtime.  You can use the _TableStat and _IndexStat VSTs to view this data.  Or you can download ProTop which already has this feature.  You could do this on a test system with just one user active to be certain that the activity seen is related to what the user is doing.  Or, if the other users are sufficiently quiet (or the program is badly behaved) you could just assume that your target program is doing most of what you see.

BTW -- this question probably ought to have been asked in the PSDN -> OpenEdge -> RDBMS forum

Ok.  To clarify a bit -- 4GL sessions do not execute SQL scripts. 

Mine does in 10.2A:

SELECT COUNT (*) FROM Customer . 

or

SELECT CustNum FROM Customer . 

And I swear, it did in 9.x as well.

That is SQL-89 and it gets translated to r-code just like 4GL statements at compile time.  It even shows up in the XREF...  It's covered by the "and so forth" clause in my previous reply      SQL-92 is handled very differently and is never used by a 4GL session..

The point that I was making is that a 4GL session does not do things "the SQL way" (even if you use the embedded SQL-89 capabilities -- and nobody should, they exist merely to satisfy marketing needs, nobody should mistake them for first class features) and that looking for SQL oriented solutions in the 4GL world is not going to work.

That is SQL-89 and it gets translated to r-code just like 4GL statements at compile time.  It even shows up in the XREF...  

I know! And I know it should not show up in any production code. But the original poster mentioned SQL Scripts and ABL code. The SQL89 translated to FOR EACH is the closed match I guess. So the OP probably should clarify what he's doing.

By the way: I was glad to be able to use the source code formatting in the new forum software once on a supported language :-) It still can't highlight ABL code.

We will have to await further comment from our original poster -- he seems to be in a very different time zone from either of us.

My assumption is that he said "sql scripts" because he is used to working with sql oriented products and just expects Progress to work like them.  I could be wrong -- maybe he really is writing SQL-89 and running it in a 4GL session (I surely hope not!)

Either way my comments regarding how to analyze it, either at compile time or at runtime, still hold.

We will have to await further comment from our original poster -- he seems to be in a very different time zone from either of us.

My assumption is that he said "sql scripts" because he is used to working with sql oriented products and just expects Progress to work like them.  I could be wrong -- maybe he really is writing SQL-89 and running it in a 4GL session (I surely hope not!)

Either way my comments regarding how to analyze it, either at compile time or at runtime, still hold.

Thanks  two friends' help. First, I tried Mike's way, but not found 'Customer'  table. Next, I tired the protop program, be told 'error edition', and I tired to look for _TableStat and _IndexStat VSTs  in database, but not found the tables.

Mike was just showing that embedded SQL works inside a 4GL session.  His example runs against the "sports" database but it is just an example of SQL running -- not an example of tracking the query.

If you can provide more information about the error that you got with ProTop I may be able to help you get beyond that problem.

The _TableStat and _IndexStat VST should be in every v9 (or higher) database.  How did you go about looking for them?  (They do not show up in a standard dictionary report because they are system tables.)