How to get my current connection id in SQL

Ian Boyd

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?

Mar 2 '06 #1

Serge Rielau

Ian Boyd wrote:

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?

*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/infoce...n/r0011856.htm
Third function in the list :-)
Just in case you are not blessed with DB2 V8.2
http://www-128.ibm.com/developerwork...302stolze.html

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 3 '06 #2

Eric.Jones

Serge Rielau wrote:

Ian Boyd wrote:

i need to access some connection unique identifier from SQL.

<snipped>
http://publib.boulder.ibm.com/infoce...n/r0011856.htm
Third function in the list :-) Just in case you are not blessed with DB2 V8.2
http://www-128.ibm.com/developerwork...302stolze.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

Mar 3 '06 #3

Michel Esber

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 ..

Mar 3 '06 #4

Knut Stolze

Eric.Jones wrote:

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."

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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 3 '06 #5

Ian Boyd

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.

"Michel Esber" <mi****@us.automatos.com> wrote in message
news:11**********************@p10g2000cwp.googlegr oups.com...
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 ..

Mar 3 '06 #6