DB2 Session variables (or equivalent)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 06 Nov 2007 00:19 GMT

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

Reply to this Message

Knut Stolze - 06 Nov 2007 08:52 GMT

> Hi there
>
[quoted text clipped - 15 lines]
> 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?

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Serge Rielau - 06 Nov 2007 14:11 GMT

> Hi there
>
[quoted text clipped - 15 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Bob - 06 Nov 2007 18:27 GMT

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

Reply to this Message

Ian - 06 Nov 2007 18:51 GMT

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

Reply to this Message

Bob - 09 Nov 2007 15:48 GMT

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

Reply to this Message

Knut Stolze - 09 Nov 2007 17:39 GMT

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

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Bob - 10 Nov 2007 02:33 GMT

Looking at your credentials, Knut, sounds like I am talking to the
right person.
By wrapper, I don't mean a complete wrapper. Where performance is
concerned, the communication channels should be quick and platform
specific. But for this specific situation - session variables - why
would that only be in z/OS? There is an obvious need for them and
Oracle has supported them for a while. They don't really seem like
they would be that hard to implement and surely are not harder on one
platform vs another. From the outside, it seems to me that IBMs
development teams each work in their own little vacuums. No offense
to you and I am sure you are good at what you do but the vacuum
philosophy - I don't see a defense for that. Another example I
referred to above is that last I read the IBM documentation, the 9.1
Developers Workbench supports stepping through routines during
debugging - but only on z/OS. I don't know maybe what you say does
apply in this situation and I use software, I don't develop at the OS
level, but it seems a little fishy to me.

Bob

Reply to this Message

Stefano P. - 30 Nov 2007 06:30 GMT

>> 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
[quoted text clipped - 7 lines]
> 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.

Signature

"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 ;-)

Reply to this Message

Serge Rielau - 30 Nov 2007 13:24 GMT

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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Stefano P. - 30 Nov 2007 22:02 GMT

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

Signature

Reply to this Message