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

Where Am I ?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

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

Nov 12 '05 #4

P: n/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.
Nov 12 '05 #5

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

Nov 12 '05 #6

P: n/a
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
Nov 12 '05 #7

P: n/a
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
Nov 12 '05 #8

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

Nov 12 '05 #9

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

Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.