By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

How to get my current connection id in SQL

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.