Where Am I ?

[WalterR]

We have several identically defined databases that support a common set
of programs. Any program can run against any database. Is there a
function or a SYSIBM table that can provide the database name or,
alternately, provide the PLAN or PACKAGE name? This would be useful for
testing and possibly for logic.

Thanks,

Walter Rue

[Serge Rielau]

WalterR wrote:

We have several identically defined databases that support a common set
of programs. Any program can run against any database. Is there a
function or a SYSIBM table that can provide the database name or,
alternately, provide the PLAN or PACKAGE name? This would be useful for
testing and possibly for logic.

Thanks,

Walter Rue

select db_name
from table(snapshot_database(cast(NULL as varchar(1)),
-1)
) as f;
DB_NAME
----------
TEST

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jan M. Nelken]

WalterR wrote:

We have several identically defined databases that support a common set
of programs. Any program can run against any database. Is there a
function or a SYSIBM table that can provide the database name or,
alternately, provide the PLAN or PACKAGE name? This would be useful for
testing and possibly for logic.

Thanks,

Walter Rue

Or perhaps simpler - VALUES CURRENT SERVER - which returns alias you are
connected to.

If you want to use in SELECT - then SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1
would do as well.

Jan M. Nelken

[juliane26]

Walter, you mean z/OS or LUW ?

database name is slightly different concept for both systems.

and is it correct: you don't know exactly which database your
application is connecting to and which package it is using ?

Well, for packages: there are the system tables
SYSIBM.SYSPACKAGES
SYSIBM.SYSPACKLIST
having a relation to SYSIBM.SYSPLAN

complete list:
http://publib.boulder.ibm.com/infoce...jnrmstr571.htm

Not sure this is what you are looking for.

[Mark A]

"juliane26" <ju*******@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Walter, you mean z/OS or LUW ?

Good question, since DB2 for z/OS has plans and packages, and DB2 for Linux,
UNIX, and Windows has only packages.

[WalterR]

Jan:

I had already found this -- and tried it. But, since all of the (four)
databases run on the same server, it is not distinctive.

So far, I'm going to try Serge's suggestion and also look into the
SYSPLAN join opportunity.

Thanks,

Walter Rue
Jan M. Nelken wrote:

WalterR wrote:

We have several identically defined databases that support a common
set of programs. Any program can run against any database. Is there
a function or a SYSIBM table that can provide the database name or,
alternately, provide the PLAN or PACKAGE name? This would be useful
for testing and possibly for logic.

Thanks,

Walter Rue

Or perhaps simpler - VALUES CURRENT SERVER - which returns alias you are
connected to.

If you want to use in SELECT - then SELECT CURRENT SERVER FROM
SYSIBM.SYSDUMMY1 would do as well.

Jan M. Nelken

[Jan M. Nelken]

WalterR wrote:

I had already found this -- and tried it. But, since all of the (four)
databases run on the same server, it is not distinctive.

You are incorrect - here is a simple example from my machine which conveniently
has also 4 databases:

D:\Working>db2 list db directory

System Database Directory

Number of entries in the directory = 4

Database 1 entry:

Database alias = TBC_MD
Database name = TBC_MD
Database drive = D:\DB2
....
Database 2 entry:

Database alias = DWCTBC
Database name = DWCTBC
Database drive = D:\DB2
....

Database 3 entry:

Database alias = DWCTRLDB
Database name = DWCTRLDB
Database drive = D:\DB2
....

Database 4 entry:

Database alias = SAMPLE
Database name = SAMPLE
Database drive = D:\DB2

D:\Working>db2 connect to sample

Database Connection Information

Database server = DB2/NT 8.2.3
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
D:\Working>db2 values current server

1
------------------
SAMPLE

1 record(s) selected.

D:\Working>db2 connect to dwctrldb

Database Connection Information

Database server = DB2/NT 8.2.3
SQL authorization ID = DB2ADMIN
Local database alias = DWCTRLDB
D:\Working>db2 values current server

1
------------------
DWCTRLDB

1 record(s) selected.

As you can see - it is quite distinctive. CURRENT SERVER special register
returns name of the application server (database) to which application is
connected.

See online documentation available at:

http://publib.boulder.ibm.com/infoce...help/index.jsp

Jan M. Nelken

[Knut Stolze]

WalterR wrote:

Jan:

I had already found this -- and tried it. But, since all of the (four)
databases run on the same server, it is not distinctive.

It is "distinctive" because CURRENT SERVER does not contain the name of the
(physical or virtual) machine on which things reside, but rather the name
of the database itself. Now, the issue is if you have multiple instances,
then each instance can have a database with a name that is the same as a
database in another instance. So you need the instance name as well to
uniquely identify a database. (That's all on LUW - I don't know about
iSeries or z/OS.)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[WalterR]

Knut:

I'm on z/OS, and our WIntel PCs use DB2-connect. But your explanation
of native LUW is interesting.

Thanks,

Walter Rue

Knut Stolze wrote:

WalterR wrote:

Jan:

I had already found this -- and tried it. But, since all of the (four)
databases run on the same server, it is not distinctive.

It is "distinctive" because CURRENT SERVER does not contain the name of the
(physical or virtual) machine on which things reside, but rather the name
of the database itself. Now, the issue is if you have multiple instances,
then each instance can have a database with a name that is the same as a
database in another instance. So you need the instance name as well to
uniquely identify a database. (That's all on LUW - I don't know about
iSeries or z/OS.)

[WalterR]

Serge:

I pasted this into z/OS QMF and got nothing, so I must be too new at
this. Is "table" meant to be the name of a real DB2 table (the result
can vary by table?)? What is "snapshot_database" -- something in SYSIBM?

Pardon my ignorance, but please explain.

Thanks,

Walter Rue

Serge Rielau wrote:

WalterR wrote:

We have several identically defined databases that support a common
set of programs. Any program can run against any database. Is there
a function or a SYSIBM table that can provide the database name or,
alternately, provide the PLAN or PACKAGE name? This would be useful
for testing and possibly for logic.

Thanks,

Walter Rue

select db_name
from table(snapshot_database(cast(NULL as varchar(1)),
-1)
) as f;
DB_NAME
----------
TEST

Cheers
Serge