hkprs
asked on
How to Know what tables get affected for a given process??
Hi ,
Is there any command or any setting that I have to do so that I know what tables are being accessed for a given process?
I have a set of processes that run against the Sybase database and Select/Update/Insert various tables. I want to know what all tables are being accessed for a particular process.
E.g:
Suppose I run the Payments process, this will select from a table called tbl_invoice etc,
and Insert/Update tbl_payment etc.
I want to get the list of all the tables that this process accesses, i.e what are the tables that it Selects, what tables does it Inserts into and what are thte tables that this will Update.
Is there any setting which I can set and then run the Payamnet process so that at the end of the process I have the above said list. Something similar to profiler.
Thanks in advance,
hkprs
Is there any command or any setting that I have to do so that I know what tables are being accessed for a given process?
I have a set of processes that run against the Sybase database and Select/Update/Insert various tables. I want to know what all tables are being accessed for a particular process.
E.g:
Suppose I run the Payments process, this will select from a table called tbl_invoice etc,
and Insert/Update tbl_payment etc.
I want to get the list of all the tables that this process accesses, i.e what are the tables that it Selects, what tables does it Inserts into and what are thte tables that this will Update.
Is there any setting which I can set and then run the Payamnet process so that at the end of the process I have the above said list. Something similar to profiler.
Thanks in advance,
hkprs
ASKER
Hi Sandhyiyaa,
Thanks for the suggestion. But I am not just executing one procedure or one query. I have a exe that I run using a Unix script file. The exe is generated using some CPP files. The CPPs have many Sql statements including Selects, Updates and Inserts. It even has some procedures.
Now , what I want is that after I run the Unix script file, I want to know all the tables that have been accessed by all the queries and procedures present in the CPPs.
In other words, starting from a particular instance, any hit on the databse should be recorded with details of the tables that have been accessed and if it is a select/Update/Insert.
Is this possible?
Thanks & Regards,
hkprs
Thanks for the suggestion. But I am not just executing one procedure or one query. I have a exe that I run using a Unix script file. The exe is generated using some CPP files. The CPPs have many Sql statements including Selects, Updates and Inserts. It even has some procedures.
Now , what I want is that after I run the Unix script file, I want to know all the tables that have been accessed by all the queries and procedures present in the CPPs.
In other words, starting from a particular instance, any hit on the databse should be recorded with details of the tables that have been accessed and if it is a select/Update/Insert.
Is this possible?
Thanks & Regards,
hkprs
ASKER
Can I use TRACEON to achive my objective? If yes, How??
do you have the ability to modify the CPP code ?
if so, you should be able to add some logging into the application's function for executing a SQL statement.
if so, you should be able to add some logging into the application's function for executing a SQL statement.
Yous still have 5 open questions from February to April
How about you pay some attention to those first !!!!!!
04/24/2003 100 table to file
04/04/2003 50 Use of GROUP BY Clause!
03/20/2003 50 Query Running for Long time!!
03/13/2003 125 Error While Using Optimizer hint in From...
02/17/2003 50 SPMIG_ERROR while Compling StoredProcs
How about you pay some attention to those first !!!!!!
04/24/2003 100 table to file
04/04/2003 50 Use of GROUP BY Clause!
03/20/2003 50 Query Running for Long time!!
03/13/2003 125 Error While Using Optimizer hint in From...
02/17/2003 50 SPMIG_ERROR while Compling StoredProcs
ASKER
I'd love to but I am not able to find a way how I can close a question without accepting any comment as an answer!!!
Now, reg the question, I didn't get what you suggested. The CPP might call a procedure and that might Select/Insert/Update into some table. How can I get this info from a CPP file???
I heard that in MS SQL profiler, we have the option of tracing a process! Is smoething similar present in Sybase too.
Regards,
hkprs
Now, reg the question, I didn't get what you suggested. The CPP might call a procedure and that might Select/Insert/Update into some table. How can I get this info from a CPP file???
I heard that in MS SQL profiler, we have the option of tracing a process! Is smoething similar present in Sybase too.
Regards,
hkprs
lets say the CPP calls a function for each SQL statement it is executing
eg. retval = SQLExecuteCommand( "SELECT blaa FROM some_table" );
then add to the "SQLExecuteCommand()" function a logging command and log every command to a log file on the file system.
This is NOT a sybase change, its a extention of the application
RE CLEANUP:
if you don't like the answers you are getting, tell us that we are on the wrong track.
if you don't give any feedback (and you didn't), we can't help !
If you feel that you still have a valid case, put a request into Community Support for a refund
https://www.experts-exchange.com/Community_Support/
eg. retval = SQLExecuteCommand( "SELECT blaa FROM some_table" );
then add to the "SQLExecuteCommand()" function a logging command and log every command to a log file on the file system.
This is NOT a sybase change, its a extention of the application
RE CLEANUP:
if you don't like the answers you are getting, tell us that we are on the wrong track.
if you don't give any feedback (and you didn't), we can't help !
If you feel that you still have a valid case, put a request into Community Support for a refund
https://www.experts-exchange.com/Community_Support/
ASKER
Thanks for your suggestion,
but if the following is the case:
retval = SQLExecuteProc( "proc_xyz" );
the proc 'proc_xyz' might contain any Inserts/Updates/Deletes on any tables and I can't log in the application's log.
Thanks,
hkprs
but if the following is the case:
retval = SQLExecuteProc( "proc_xyz" );
the proc 'proc_xyz' might contain any Inserts/Updates/Deletes on any tables and I can't log in the application's log.
Thanks,
hkprs
then combine the list of proc calls with sandhiyaa's comment and run a sp_depends on the proc's in the list
Hi hkprs,
the sp_depends wont of course be able to identify any dynamic sql that is issued.
Cheers!
the sp_depends wont of course be able to identify any dynamic sql that is issued.
Cheers!
hkprs
any updates ?
any updates ?
ASKER
Hi all,
Sorry for delay in updation. Client didn't approve of the idea that was put forth. There are about 150 cpps and a major code modification is required. So, client wants us to look into all the CPPS and find the indtances of tabels being used.
Now can anyone tell me how close this question???
Thanks a lot for all the suggestions!!!
Regards,
Hkprs
Sorry for delay in updation. Client didn't approve of the idea that was put forth. There are about 150 cpps and a major code modification is required. So, client wants us to look into all the CPPS and find the indtances of tabels being used.
Now can anyone tell me how close this question???
Thanks a lot for all the suggestions!!!
Regards,
Hkprs
just award points to with a "accept answer" / "assisted answer" buttons
or you can request a refund by posting in "community support" if you feel that you did not get an answer to your question
or you can request a refund by posting in "community support" if you feel that you did not get an answer to your question
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
u can do this either by putting sp_depends < procedure name>
or showplan on and see what are the tables it is accessing while eecuting the procedure.