How to get the current database name from SQL routine?

[waliamanish]

Hi all,
I need a help on VMS routines. I want to find out the alias name of the database i am currently connected to using SQL routines in Oracle RDb.
Will appreciate prompt response :)

-Manish

[Kris Clippeleyr]

Hi,

Could you be more specific? Do you connect by means of a self-written program? DCL procedure? At the DCL prompt (SQL)?

If you are using interactive SQL (image SYS$SYSTEM:SQL$.EXE), the database you're using by default is the one the logical SQL$DATABASE translates to.

Hope this helps,

Kris

[Wim Van den Wyngaert]

I don't know RDB but isn't there some system table in which you can find the name ? Of course this only gives the non-unique name. You could have database TOTO in 3 different environments but with the same name.

[Mobeen_1]

Manish,
Try to do

$sh log *SQL$DATABAS*

and you should be able to see the database name

regards
Mobeen

[Keith Cayemberg · Accepted solution]

SQL> show version
Current version of SQL is: Oracle Rdb SQL V7.1-241

SQL> show database !find alias
Default alias:
Oracle Rdb database in file SQL$DATABASE

SQL> $ show logical SQL$DATABASE !DCL Command
"SQL$DATABASE" = "DB_EX" (LNM$PROCESS_TABLE)
1 "DB_EX" = "$21$DKB600:[DB]EXAMPLE.RDB" (LNM$PROCESS_TABLE)

or...

SQL> select RDB$FILE_NAME from RDB$DATABASE;

RDB$FILE_NAME
$21$DKB600:[DB]EXAMPLE.RDB;2316
>>
>>
>>
1 row selected

or if the alias is thru CDD, then...

SQL> select RDB$CDD_PATH from RDB$DATABASE;

RDB$CDD_PATH

>>
>>
>>
1 row selected

But, in my case the attach wasn't thru CDD.

You might be interested in other DB parameters and table infos, so here is a tip. You can find the system tables with...

SQL> show system tables

Cheers!

Keith Cayemberg

[Jan van den Ende]

Keith,

to have some flexibility in the LOCATION of your database, I fanatically propose usage of CONCEALED devices as the database location.
In your example that would be NOT be
$21$DKB600:[DB]EXAMPLE.RDB
but (example, choose your own favorite naming scheme):
EXAMPLE_ROOT:[DB]EXAMPLE.RDB,
with EXAMPLE_ROOT being RDB_DEV:[DATABASES.] and
RDB_DEV being $21$DKB600:

EXAMPLE_ROOT & RDB_DEV need to be /EXECUTIVE/TRANSLAT=CONCEAL, and in LNM$SYSTEM_TABLE.

(and they need to be for the RDMMON process to be able to find them when needed).

The LNM$SYSTEM_TABLE actually is considered a BUG, LNM$SYSTEM would be MUCH better.
Current behavior still reflects the pre-VMS-V7.2 situation.
It guarantees that any process on the "SYSTEM" references the SAME location, and.. "it is the responsability of the system manager to make sure that it has THE SAME tranlation clusterwide...".
Well, since 7.2 we have clusterwide logical names, and guess what? IF clusterwide defined, they are NOT recognised. (by the way, same applies for DBMS).
Reported to Oracle in april 2000, acknowledged, "will be corrected in next release", but NOT corrected in the releases WE have since seen....

Still, if you store your database locations as Concealed Devices, you are NOT in trouble if you move from SCSI to SAN, or start using shadowing, or for whatever reason you need (some of) your databases OFF of $21$DKB600:


... and if this inspires only one system manager to start using concealed devices (and use them consistently, for everything!), than there is one more site that starts using a major piece of the fantastic flexibility that VMS can offer.


Keith, I am writing this with a good glass of Weizenbier under hand's reach, so, if ever your salute is returned rightfully, here you go now!

Prosit!
Cheers!

Jan