-1

I have a database (list db directory):

Database 4 entry:
Database alias                       = ABC
Database name                        = ABC
Local database directory             = /data
Database release level               = f.00
Comment                              =
Directory entry type                 = Indirect
Catalog database partition number    = 0
Alternate server hostname            =
Alternate server port number         =

I need to have an alias for that database because my application tries to connect to database DEF.

I can create an alias using

catalog db ABC as DEF

then the (list db directory) shows:

Database 4 entry:
Database alias                       = ABC
Database name                        = ABC
Local database directory             = /data
Database release level               = f.00
Comment                              =
Directory entry type                 = Indirect
Catalog database partition number    = 0
Alternate server hostname            =
Alternate server port number         =code here

Database 5 entry:
Database alias                       = DEF
Database name                        = ABC
Local database directory             = /data
Database release level               = f.00
Comment                              =
Directory entry type                 = Indirect
Catalog database partition number    = 0
Alternate server hostname            =
Alternate server port number         =

But after I connect to aliased db using:

db2 connect DEF

I can't access any schemas and tables from original database. Of course when I connect using ABC database name everything is visible and on place.

Am I misunderstanding aliases in DB2? Or maybe there is some option like "create an alias with data" or something like that?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
przemaz
  • 390
  • 5
  • 11
  • After connecting to database DEF and running the command "db2 list tables for all" - nothing is being returned or do you at least see the system tables? – DavidW Jul 09 '18 at 21:16
  • The question is incomplete. Did you run `db2 terminate` after the `db2 catalog db ABC as DEF` command? Are you running these commands locally on a Db2-server, or from a remote Db2-client? When you connect to DEF alias, are you connecting with instance-owner credentials? – mao Jul 10 '18 at 08:44
  • Define "can't access". – mustaccio Jul 10 '18 at 14:24
  • @DavidW, i see system tables but do not see tables that exists in ABC – przemaz Jul 11 '18 at 09:44
  • @mao - i ran db2 terminate. I can do `select count(*) from ABC.tab1` and see result, but when i try `select count(*) from DEF.tab1` ofcourse after connecting to that db - I get error message: "DEF.tab1 do not exists" – przemaz Jul 11 '18 at 09:45
  • @mustaccio - are you trying to get more reputation points? – przemaz Jul 11 '18 at 09:46
  • Database alias != schema name. – mustaccio Jul 11 '18 at 10:46

2 Answers2

0

You seem to misunderstand the purpose of a database-alias produced by db2 catalog database ABC as DEF.

For Db2 for Linux/Unix/Windows, a database ALIAS is not a SCHEMA.

You cannot use the new alias in SELECT or other SQL statements.

You can only reference the database-alias in the CONNECT step. After successful connection, use SQL as if you had connected to database ABC only.

The database-alias is only a pointer to a database.

The database being pointed to (in your case ABC) does not change, and the schemas inside it do not change, and you cannot change how you refer to objects like tables and views in those schemas.

In your SELECT (or other SQL statements) you must refer to the schemas that are present inside the physical database. So there really is not schema called DEF, becase DEF is an alias known only to the command-line-processor and db2-database-directory. If you wish to make new synonyms inside the database you are free to do that , but that is NOT the purpose of database-aliases.

As you seem to be running Db2 for Linux/Unix/Windows with local databases (Directory entry type = Indirect), you should connect to each database ABC and DEF and run both of the queries below and then compare their outputs from each database, and update your question with the outputs.

select char(os_name,20) as os_name
, char(os_version,5) as os_version
, char(os_release,20) as os_release
, char(host_name,30) as host_name 
from sysibmadm.env_sys_info;


select char(inst_name,15) as inst_name
,char(release_num,20) as release_num
,char(service_level,20) as service_level
,char(bld_level,20) as bld_level
,char(ptf,20) as ptf
from sysibmadm.env_inst_info;
mao
  • 11,321
  • 2
  • 13
  • 29
  • I mistyped the sample sql, it should look like this: `connect to ABC select * from SCHEMA_ORG.tabl1` works OK! `connect to DEF select * from SCHEMA_ORG.tabl1` File SCHEMA_ORG.tabl1 not found I understand that the alias is just additional name for an existing database. Problem is that when I connect using alias i cant use tables that exsits in original database. – przemaz Jul 11 '18 at 11:06
  • This means that you have not created the database-alias that you *think* you have. Most likely you are really connecting to a different database, or different Db2-instance. – mao Jul 11 '18 at 11:31
0

@mao - You are right! I've been connected using external software to one node, and using shell db2 to other node. That was the reason that i wasn't able to see databases.

przemaz
  • 390
  • 5
  • 11