Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / March 2006

Tip: Looking for answers? Try searching our database.

How to get my current connection id in SQL

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ian Boyd - 02 Mar 2006 20:42 GMT
i need to access some connection unique identifier from SQL.

In "Control Center" i stumbled into "Application" where i connection id's of
the form:
GA00000B.FC12.00BAC2202852
GA000010.P10C.00E582202330
GA00000B.I212.00D402201110
*LOCAL.DB2.060302193817
*LOCAL.DB2.060302193816
GA00012.K806.012A02162441

i've tried random SQL constructs such as

SELECT connection
SELECT current connection
SELECT current_connection
SELECT connection;
SELECT current connection;
SELECT current_connection;
SELECT connection$
SELECT current connection$
SELECT current_connection$
SELECT connection FROM MyTable
SELECT connection FROM MyTable;
SELECT spid
SELECT spid;
SELECT current spid
SELECT sp_id
SELECT CLIENT ACCTNG
SELECT CLIENT ACCTNG;
SELECT CLIENT ACCTNG$
SELECT CLIENT ACCTNG
SELECT CURRENT CLIENT ACCTNG
SELECT CLIENT_ACCTNG
SELECT CURRENT CLIENT_ACCTNG

None work.

What i am really looking for is a place to store per-connection data (a
software user id), but i cannot see any programatic access to any such
"session variable" / "context info". i need this information, because i am
trying to implement trigger based audit logging, and i need to know the user
of the software who is making the changes.

In the absence of a system provided "session information" store, i will have
to maintain my own table of "Connection ID <--> User ID" mappings.

Any ideas on any of the above?
Serge Rielau - 03 Mar 2006 01:22 GMT
> i need to access some connection unique identifier from SQL.
>
[quoted text clipped - 44 lines]
>
> Any ideas on any of the above?

*lol*A bit like trying to look up the spelling of psycho when you don't
know it starts with a "p", eh?
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad
min/r0011856.htm

Third function in the list :-)
Just in case you are not blessed with DB2 V8.2
http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302sto
lze.html


Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Eric.Jones - 03 Mar 2006 06:45 GMT
>> i need to access some connection unique identifier from SQL.

<snipped>

> http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad
min/r0011856.htm

>
> Third function in the list :-) Just in case you are not blessed with DB2 V8.2
> http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302sto
lze.html

May I jump in with a question..?

What are the rules for the valid lifetime of this identifier?  ie. when would the same ID be
"reused" for a different client?  I'm having a hard time grokking the following statement from the
infocenter:

"The value returned by this function is only unique for the period of time during which the client
can use the same value again."

Eric
Knut Stolze - 03 Mar 2006 14:18 GMT
> What are the rules for the valid lifetime of this identifier?  ie. when
> would the same ID be
[quoted text clipped - 3 lines]
> "The value returned by this function is only unique for the period of time
> during which the client can use the same value again."

Now that is a truly strange sentence. ;-)

The application ID contains the IP address of the client (or "*LOCAL" for
local connections), the instance name and the timestamp when the connection
was established.  The timestamp uses only 2-digit years and no subseconds.
But if two connections from the same client come to the same instance, the
ID is made unique (I don't know exactly how).

So this will give you the amount of "uniqueness" that you get: you might
have recycled IDs after 100 years.  Question is if you have to worry about
this or not.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Michel Esber - 03 Mar 2006 13:25 GMT
If you want to do audit logging, why don´t you use the USER variable
inside your triggers? This will give you the name of the user connected
to the DB ..
Ian Boyd - 03 Mar 2006 14:21 GMT
Because users don't connect to the database. Users are four layers separated
from the database. The user is sitting at home running Internet Explorer.

If you want to do audit logging, why don´t you use the USER variable
inside your triggers? This will give you the name of the user connected
to the DB ..
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.