How many sql statements

Hello In order to activate SQLTrace, wanted to have an idea on how much sql statements are run during a day? is there a system table that record this stat ? or is there another way (Informix 14.1) thanks in advance ------------------------------ John Smith ------------------------------ #Informix

You can turn it on with default settings and at the end of the day you will see how many statements went through the buffer HTH Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

Hi John, To my knowledge, there is no system table storing this kind of information until you activate SQLTRACE. When you activate SQLTRACE: - the sysmaster:syssqltrace_info will contain this statistics in the sqlseen column corresponding to the number of SQL items traced since start or resizing of your SQLTrace buffer. - the onstat -g his will give another information, the duration of buffer in second, for example, if you specifiy 10 000 for your number of traces to SQLtrace, you can get an idea of how long the 10 000 SQL queries will be stored in the SQLTrace buffer with the duration of buffer. For example, imagine the duration of SQLTrace buffer is 600 seconds for 10 000 traces, then you can deduce that after one hour you will have traced about 60 000 queries (but you will store only 10 000 maximum traces at a time t in the sqltrace buffer and then you must think to save the old traces). Of course, this is just an estimate, because this duration of the SQLTrace buffer will vary during the day depending on user activity. Regards, -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr http://www.consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18 Le 27/09/2022 à 16:31, John Smith via IBM Community a écrit :

Just turn SQLTRACE on with the defaults of 1000 2K buffers trace level on MEDIUM in global mode: SQLTRACE level=med,ntraces=1000,size=2,mode=global Then you can select the row with the highest value for sql_id from sysmaster:syssqltrace to get a baseline query number (sql_id), then select again after the passage of time for which you want to capture all or most of the SQL in the buffers to get the current sql_id. The difference will be the number of SQL statements added to the trace buffers over that period of time. Then you can reconfigure SQLTRACE even on the fly with the SQL API, or just configure it in the ONCONFIG file for the next restart. So, let's say you want to poll SQLTRACE data every 10 minutes, so do this during a peak time: echo 'SELECT max(sql_id) FROM syssqltrace;' | dbaccess sysmaster - sleep 660 ## doing 11 minutes to allow for a busier period echo 'SELECT max(sql_id) FROM syssqltrace;' | dbaccess sysmaster - Then set up SQLTRACE with ntraces equal to the difference. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------