Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
instance owner sees db alias, other users don't
72 views
Skip to first unread message
Peter H. Coffin
Jan 23, 2012, 10:00:09 AM1/23/12
to
Here's a fun one.
Instance owner can see/use a local database name:
-------------------
db2 => connect
connect
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST2
Local database alias = CW
db2 => grant connect on database to public
grant connect on database to public
DB20000I The SQL command completed successfully.
db2 => list tables for schema src
list tables for schema src
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
BVD_GME_LINKS SRC T 2012-01-18-12.54.33.628198
BVD_LINKS SRC T 2012-01-18-12.36.03.750860
BVD_MCH_CONS_OLD SRC T 2012-01-20-13.33.51.044277
3 record(s) selected.
-------------------
However other users cannot even attempt to connect to the database. They
don't even see the alias, even though the alias is listed in the
directory.
-------------------
[08:47:16] $ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.0
You can issue database manager commands and SQL statements from the
command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => connect to cw
SQL1013N The database alias name or database name "CW" could not be
found.
SQLSTATE=42705
db2 => list db directory
System Database Directory
Number of entries in the directory = 13
Database 1 entry:
Database alias = CW
Database name = CW
Local database directory = /saswork/db2/cw
Database release level = d.00
Comment = Client Wallet Local Storage
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = XMETA
Database name = XMETA
...
-------------------
There's no duplicate CW alias, it's not saying it's forbidden to
connect to it, it's saying there's no such name or alias. It also
persists through a db2stop/db2start cycle in the same circumstance:
owner can access, user sees the entry in the directory but can't use
it.
Any suggestions for reconciling this? Obviously, I'd prefer to make
other users able to see the alias than break it for the instance
owner...
--
Either way, it'll remind the clued that there's only one letter
difference between 'turkey' and 'turnkey'.
-- Mike Andrews
Instance owner can see/use a local database name:
-------------------
db2 => connect
connect
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST2
Local database alias = CW
db2 => grant connect on database to public
grant connect on database to public
DB20000I The SQL command completed successfully.
db2 => list tables for schema src
list tables for schema src
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
BVD_GME_LINKS SRC T 2012-01-18-12.54.33.628198
BVD_LINKS SRC T 2012-01-18-12.36.03.750860
BVD_MCH_CONS_OLD SRC T 2012-01-20-13.33.51.044277
3 record(s) selected.
-------------------
However other users cannot even attempt to connect to the database. They
don't even see the alias, even though the alias is listed in the
directory.
-------------------
[08:47:16] $ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.0
You can issue database manager commands and SQL statements from the
command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => connect to cw
SQL1013N The database alias name or database name "CW" could not be
found.
SQLSTATE=42705
db2 => list db directory
System Database Directory
Number of entries in the directory = 13
Database 1 entry:
Database alias = CW
Database name = CW
Local database directory = /saswork/db2/cw
Database release level = d.00
Comment = Client Wallet Local Storage
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = XMETA
Database name = XMETA
...
-------------------
There's no duplicate CW alias, it's not saying it's forbidden to
connect to it, it's saying there's no such name or alias. It also
persists through a db2stop/db2start cycle in the same circumstance:
owner can access, user sees the entry in the directory but can't use
it.
Any suggestions for reconciling this? Obviously, I'd prefer to make
other users able to see the alias than break it for the instance
owner...
--
Either way, it'll remind the clued that there's only one letter
difference between 'turkey' and 'turnkey'.
-- Mike Andrews
TheBoss
Jan 23, 2012, 3:19:09 PM1/23/12
to
"Peter H. Coffin" <hel...@ninehells.com> wrote in
news:slrnjhqtfp....@nibelheim.ninehells.com:
Hi Peter,
could it be your other users are connecting to a different instance?
Compare environment variable DB2INSTANCE for the other users vs. the
instance owner.
HTH
Cheers!
--
Jeroen
news:slrnjhqtfp....@nibelheim.ninehells.com:
could it be your other users are connecting to a different instance?
Compare environment variable DB2INSTANCE for the other users vs. the
instance owner.
HTH
Cheers!
--
Jeroen
Ian
Jan 23, 2012, 6:21:37 PM1/23/12
to
On Jan 23, 8:00 am, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> There's no duplicate CW alias, it's not saying it's forbidden to
> connect to it, it's saying there's no such name or alias. It also
> persists through a db2stop/db2start cycle in the same circumstance:
> owner can access, user sees the entry in the directory but can't use
> it.
>
> Any suggestions for reconciling this? Obviously, I'd prefer to make
> other users able to see the alias than break it for the instance
> owner...
Chances are that the permissions somewhere in your instance
> There's no duplicate CW alias, it's not saying it's forbidden to
> connect to it, it's saying there's no such name or alias. It also
> persists through a db2stop/db2start cycle in the same circumstance:
> owner can access, user sees the entry in the directory but can't use
> it.
>
> Any suggestions for reconciling this? Obviously, I'd prefer to make
> other users able to see the alias than break it for the instance
> owner...
owner's home directory and/or in the local database path are not
correct.
Can the user connect to other databases in the same instance?
If not, you might try using db2iupdt (which should correct
permissions in the sqllib directory).
If so, there will probably be error messages in the db2diag.log
indicating what file is unreadable...
Peter H. Coffin
Jan 23, 2012, 6:54:35 PM1/23/12
to
directory showed? That is, it wouldn't show another instance's db
directory, right?
--
With a Dremel tool and a cut-off wheel, _everything_ takes a flat-blade
screwdriver.
-- Matt Roberds in the Monastery
Peter H. Coffin
Jan 23, 2012, 6:57:15 PM1/23/12
to
On Mon, 23 Jan 2012 15:21:37 -0800 (PST), Ian wrote:
> On Jan 23, 8:00?am, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
>> There's no duplicate CW alias, it's not saying it's forbidden to
>> connect to it, it's saying there's no such name or alias. It also
>> persists through a db2stop/db2start cycle in the same circumstance:
>> owner can access, user sees the entry in the directory but can't use
>> it.
>>
>> Any suggestions for reconciling this? Obviously, I'd prefer to make
>> other users able to see the alias than break it for the instance
>> owner...
>
> Chances are that the permissions somewhere in your instance
> owner's home directory and/or in the local database path are not
> correct.
>
> Can the user connect to other databases in the same instance?
Yes. Using the same instance, the user that cannot access the cataloged,
> On Jan 23, 8:00?am, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
>> There's no duplicate CW alias, it's not saying it's forbidden to
>> connect to it, it's saying there's no such name or alias. It also
>> persists through a db2stop/db2start cycle in the same circumstance:
>> owner can access, user sees the entry in the directory but can't use
>> it.
>>
>> Any suggestions for reconciling this? Obviously, I'd prefer to make
>> other users able to see the alias than break it for the instance
>> owner...
>
> Chances are that the permissions somewhere in your instance
> owner's home directory and/or in the local database path are not
> correct.
>
> Can the user connect to other databases in the same instance?
local CW databse CAN use the instance to connect to other (at least
remote) databases using that catalog.
> If not, you might try using db2iupdt (which should correct
> permissions in the sqllib directory).
>
> If so, there will probably be error messages in the db2diag.log
> indicating what file is unreadable...
Peter H. Coffin
Jan 23, 2012, 7:33:11 PM1/23/12
to
On Mon, 23 Jan 2012 15:21:37 -0800 (PST), Ian wrote:
>> There's no duplicate CW alias, it's not saying it's forbidden to
>> connect to it, it's saying there's no such name or alias. It also
>> persists through a db2stop/db2start cycle in the same circumstance:
>> owner can access, user sees the entry in the directory but can't use
>> it.
>>
>> Any suggestions for reconciling this? Obviously, I'd prefer to make
>> other users able to see the alias than break it for the instance
>> owner...
>
> Chances are that the permissions somewhere in your instance
> owner's home directory and/or in the local database path are not
> correct.
>
> Can the user connect to other databases in the same instance?
Further checking: remote databases, okay. other local databases, no, not
>> There's no duplicate CW alias, it's not saying it's forbidden to
>> connect to it, it's saying there's no such name or alias. It also
>> persists through a db2stop/db2start cycle in the same circumstance:
>> owner can access, user sees the entry in the directory but can't use
>> it.
>>
>> Any suggestions for reconciling this? Obviously, I'd prefer to make
>> other users able to see the alias than break it for the instance
>> owner...
>
> Chances are that the permissions somewhere in your instance
> owner's home directory and/or in the local database path are not
> correct.
>
> Can the user connect to other databases in the same instance?
those either.
> If not, you might try using db2iupdt (which should correct
> permissions in the sqllib directory).
with a fresh shell session and running the same db2profile now give
access, to both instance owner *and* reular users. Effusive thanks!
--
This was, apparently, beyond her ken. So far beyond her ken that she
was well into barbie territory.
-- J. D. Baldwin
TheBoss
Jan 25, 2012, 5:06:12 PM1/25/12
to
"Peter H. Coffin" <hel...@ninehells.com> wrote in
news:slrnjhrspr....@nibelheim.ninehells.com:
> On 23 Jan 2012 20:19:09 GMT, TheBoss wrote:
>>
>> Hi Peter,
>>
>> could it be your other users are connecting to a different instance?
>> Compare environment variable DB2INSTANCE for the other users vs. the
>> instance owner.
>
> Same instance for both users. And wouldn't that ALSO affect what the
> directory showed? That is, it wouldn't show another instance's db
> directory, right?
>
Yes, you're right.
>> Hi Peter,
>>
>> could it be your other users are connecting to a different instance?
>> Compare environment variable DB2INSTANCE for the other users vs. the
>> instance owner.
>
> Same instance for both users. And wouldn't that ALSO affect what the
> directory showed? That is, it wouldn't show another instance's db
> directory, right?
>
I misinterpreted the db directory you showed as coming from the instance
owner.
I'm glad Ian's solution solved the issue.
Cheers!
--
Jeroen
0 new messages