DB2 Session variables (or equivalent)

[Bob]

Hi there

While developing in DB2, I am trying to store information in session
variables for auditing purposes. I am currently working in v8.2 for
UNIX but we will be going to v9.1 before we deploy to production (so
any answers for either are appreciated).

By session variables, I mean a variable that will return the value set
to within that session even if there are 100 sessions setting their
own values.

Can anyone answer the following questions:

1. Is there a way to store session-specific information using a
methodology other than the DSN3@ATH and DSN3@SGN exit routines?

2. Where do the DSN3@ATH and DSN3@SGN routines reside? I have
searched the file system and cannot find them. Are they optionally
installed with DB2 and if so, where do they end up?

Thanks very much
Bob

[Knut Stolze]

Bob wrote:

Hi there

While developing in DB2, I am trying to store information in session
variables for auditing purposes. I am currently working in v8.2 for
UNIX but we will be going to v9.1 before we deploy to production (so
any answers for either are appreciated).

By session variables, I mean a variable that will return the value set
to within that session even if there are 100 sessions setting their
own values.

Can anyone answer the following questions:

1. Is there a way to store session-specific information using a
methodology other than the DSN3@ATH and DSN3@SGN exit routines?

2. Where do the DSN3@ATH and DSN3@SGN routines reside? I have
searched the file system and cannot find them. Are they optionally
installed with DB2 and if so, where do they end up?

How about storing such information in a temporary table?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

Bob wrote:

Hi there

While developing in DB2, I am trying to store information in session
variables for auditing purposes. I am currently working in v8.2 for
UNIX but we will be going to v9.1 before we deploy to production (so
any answers for either are appreciated).

By session variables, I mean a variable that will return the value set
to within that session even if there are 100 sessions setting their
own values.

Can anyone answer the following questions:

1. Is there a way to store session-specific information using a
methodology other than the DSN3@ATH and DSN3@SGN exit routines?

2. Where do the DSN3@ATH and DSN3@SGN routines reside? I have
searched the file system and cannot find them. Are they optionally
installed with DB2 and if so, where do they end up?

This sounds like DB2 for i5/OS... DB2 for LUW does not have these.

In DB2 9.5 for LUW we have exactly what you ask for: GLOBAL SESSION
VARIABLES

Prior to DB2 9.5 Declared global temporary tables (DGTT) are probably
your best bet. The problem is that there is no authorization needed to
muck with these, which may defeat your auditing requirement.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Bob]

Thanks everyone for the help. Looks like it may be temporary tables
until we upgrade.

Does anyone know the answers to the questions on DSN3@ATH and
DSN3@SGN?

Bob

[Ian]

Bob wrote:

Thanks everyone for the help. Looks like it may be temporary tables
until we upgrade.

Does anyone know the answers to the questions on DSN3@ATH and
DSN3@SGN?

Your post is a little confusing, because you don't specify what platform
you're running DB2 on. You specified versions 8.2 and 9.1, which would
imply DB2 for Linux/Unix/Windows (LUW) (since 8.2 was available ONLY on
LUW).

However, DSN3@SGN and DSN3@ATH are not part of DB2 LUW -- I believe they
are for DB2 on z/OS.

[Bob]

I did notice that they talked alot about z/OS in the posts about these
functions. I did say it was unix, though, in my original post. More
specifically, it is AIX. This place is all about the IBM.

I don't get this about DB2 - what is with the functionalities on one
platform and not another? Why haven't they just wrapped the core
functionality with platform specific wrappers? I hear about all this
great functionality on z/OS - better routine debugging, etc. What is
up with that?

Thanks for clarifying on the LUW

[Knut Stolze]

Bob wrote:

I did notice that they talked alot about z/OS in the posts about these
functions. I did say it was unix, though, in my original post. More
specifically, it is AIX. This place is all about the IBM.

DSN3xxx sounds very much like z/OS and not at all like DB2 LUW. Maybe you
are mixing some things here?

I don't get this about DB2 - what is with the functionalities on one
platform and not another? Why haven't they just wrapped the core
functionality with platform specific wrappers?

Because it is not that simple. Both DB2 products (actually 4 if you
consider DB2 Everyplace, DB2 for iSeries, DB2 z/OS, and DB2 LUW) take
advantage of the different features and restrictions on the respective
platform. For example, doing I/O operations on z/OS is completely
different than I/O on Unix. And because performance is the most critical
factor for any serious DBMS, it is not an option to introduce emulation
layers. Also, DB2 z/OS was initially (and still is) written PL/X and
Assembler. It also employs WLM for stored procedure/UDF execution - while
LUW always had a different process model for external routines simply
because WLM wasn't available in the beginning. This is not easily portable
to other platforms. DB2 for iSeries is integrated into the operating
system. This is yet another model, and DB2 Everyplace (still) has to work
on very limited hardware and you can't even install a full-blown Enterprise
Server Edition.

So you see that there are many good reasons why different architectures are
used. I guess that everyone why worked with the some of those different
platforms can understand those differences. What IBM tries to do is to
present a mostly consistent user/application interface while still taking
advantage of platform-specific features. But again, you have to consider a
different customer set on LUW and z/OS, for example. If certain features
are more important on LUW, they have a higher chance to get into the
development plans on LUW than on z/OS and maybe not at all in DB2
Everyplace.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Stefano P.]

>I don't get this about DB2 - what is with the functionalities on one

>platform and not another? Why haven't they just wrapped the core
functionality with platform specific wrappers?

Because it is not that simple. Both DB2 products (actually 4 if you
consider DB2 Everyplace, DB2 for iSeries, DB2 z/OS, and DB2 LUW) take
advantage of the different features and restrictions on the respective
platform. For example, doing I/O operations on z/OS is completely
different than I/O on Unix. And because performance is the most critical
factor for any serious DBMS, it is not an option to introduce emulation
layers. Also, DB2 z/OS was initially (and still is) written PL/X and
Assembler. It also employs WLM for stored procedure/UDF execution - while
LUW always had a different process model for external routines simply
because WLM wasn't available in the beginning. This is not easily portable
to other platforms.

DB2 for iSeries is integrated into the operating
system.

Search for more post about "DB2 for i5/OS" in *as400* newsgroups (from
whom I come), like "comp.sys.ibm.as400.misc" or "it.comp.as400" (in
italian, but you may find messages in english too).

This is yet another model, and DB2 Everyplace (still) has to work
on very limited hardware and you can't even install a full-blown Enterprise
Server Edition.

"DB2 is not DB2 is not DB2" ;-)
Regards
Stefano P.

--
"Niuna impresa, per minima che sia,
può avere cominciamento e fine senza queste tre cose:
e cioè senza sapere, senza potere, senza con amor volere"
[Anonimo fiorentino, XIV sec.]

(togliere le "pinzillacchere" dall'indirizzo email ;-)

[Serge Rielau]

Stefano P. wrote:

"DB2 is not DB2 is not DB2" ;-)

Hah, yes. We admit in shame that we can't squeeze the DB2 Warehouse
product onto a cell phone. But I do blame the cell phones for that... :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Stefano P.]

>"DB2 is not DB2 is not DB2" ;-)

May be - but I may fail - that Mike (Cain) told us this phrase...

Hah, yes. We admit in shame that we can't squeeze the DB2 Warehouse
product onto a cell phone. But I do blame the cell phones for that... :-)

ROTFL :-D
Even if at work I like DB2 (for i5/OS ;-) very much, I rather like to
use my cell phone for what it is: as a telephone ;-)
Regards
Stefano P.

--
"Niuna impresa, per minima che sia,
può avere cominciamento e fine senza queste tre cose:
e cioè senza sapere, senza potere, senza con amor volere"
[Anonimo fiorentino, XIV sec.]

(togliere le "pinzillacchere" dall'indirizzo email ;-)