sysdbopen problem
The poster used sysdbopen/sysdbclose procedures to log all connections to his Informix instance, but connections from an OLE DB-based tool (FlySpeed SQL) produced no log entries. Art suggested it should work and advised opening a PMR; Eric pointed to possible causes (HDR/DBA defect IC91981, remote/distributed calls not firing sysdbopen) and recommended tracing via SET DEBUG FILE/TRACE ON. The PMR revealed the cause: sysdbopen must exist in every database, not just one. To get a single instance-wide log, Eric suggested each database's procedure write to one shared table via remote database calls, or consolidate with remote queries.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I doing some tests with one procedure table to register all access to my informix instance, even for informix user. I am using the code from planetids to use sysdbopen and sysdbclose: http://planetids.com/content/how-track-resources-used-database-users Every thing works fine until I have done a test with one software that connects over oledb, and I have no information about this connection. Is it necessary to specify some special parameters? What can I have done wrong? How can I track all access to my instance? Thanks for any help, SP sergio.peres@airc.pt
This should be working. The sysdbopen() procedure should execute for all connections to a database regardless of what the development protocol is. Unless you find some discussion of this previously that explains it, and I don't remember any, I would open a PMR. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Dec 18, 2016 at 2:45 PM, SERGIO PERES <sergio.peres@airc.pt> wrote: > Hi, > > I doing some tests with one procedure table to register all access to my > informix instance, even for informix user. I am using the code from > planetids > to use sysdbopen and sysdbclose: > http://planetids.com/content/how-track-resources-used-database-users > Every thing works fine until I have done a test with one software that > connects over oledb, and I have no information about this connection. > Is it necessary to specify some special parameters? What can I have done > wrong? > How can I track all access to my instance? > > Thanks for any help, > > SP > sergio.peres@airc.pt > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0117610faca97c0543f5b734
So without any info on your configuration I would agree with Art to opening a PMR. If usIng HDR and DBA with 11.70 check out: IC91981 What programming language is the app written in? What engine version? Is this using SQL Linked Server? "But when a user who is connected to the local database calls a remote UDR or performs a distributed DML operation that references a remote database object by using the database:object or database@server:object notation, no sysdbopenprocedure is invoked in the remote database.)". -Here's to hoping this isn't the case but this can be over come by adding this procedure to the originating database. https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_s qs_1799.htm If that's not the case: The above link states you can TRACE in the sysdbopen. Use it to see if something crazy is being done. I wouldn't put it past an app opening a transaction and rolling back (have seen this once because there was an error), the error might be trapped and the app moves on with its next statement. But even if there is a rollback after the SPL was excluded you should see the trace file, with no data in the table. SET DEBUG FILE TO set it to something with sessionid or a datetime year to second. So less likely to overwrite. https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_s qs_1112.htm TRACE ON https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlt.doc/ids_s qt_513.htm Hope this helps, Eric Rowell > On Dec 18, 2016, at 2:45 PM, SERGIO PERES <sergio.peres@airc.pt> wrote: > > Hi, > > I doing some tests with one procedure table to register all access to my > informix instance, even for informix user. I am using the code from planetids > to use sysdbopen and sysdbclose: > http://planetids.com/content/how-track-resources-used-database-users > Every thing works fine until I have done a test with one software that > connects over oledb, and I have no information about this connection. > Is it necessary to specify some special parameters? What can I have done > wrong? > How can I track all access to my instance? > > Thanks for any help, > > SP > sergio.peres@airc.pt > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --Apple-Mail-CC5C522F-993A-4B1E-810C-4B32FDB789A0
Thanks for your replies both for Art and Eric, I am using just SQL and the connection was made with FlySpeed SQL. I have opened one PMR and I received the information that I was wrong, I thought that it was enough to have one database with the procedure so it was valid for all the databases. But I was informed that is not valid, I have to replicate the procedure to all databases. So my question now is, if it is possible to have any form to register all the access to my databases, so I can have one log for all my databases access? Thanks again and regards, SP sergio.peres@airc.pt
You can point all of the procedures to a single table (remote DB call) or use remote queries to pull it all together. Sent from my iPhone Eric B Rowell > On Dec 20, 2016, at 4:34 AM, SERGIO PERES <sergio.peres@airc.pt> wrote: > > Thanks for your replies both for Art and Eric, > I am using just SQL and the connection was made with FlySpeed SQL. > I have opened one PMR and I received the information that I was wrong, I > thought that it was enough to have one database with the procedure so it was > valid for all the databases. > But I was informed that is not valid, I have to replicate the procedure to all > databases. > So my question now is, if it is possible to have any form to register all the > access to my databases, so I can have one log for all my databases access? > > Thanks again and regards, > > SP > sergio.peres@airc.pt > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >