Extract Nickname definition from system catalog tables.

[Asphalt Blazer]

OS: AIX 5L, DB2 UDB v8.2

the nicknames are created like this

create nickname <schema-name>.<tab-name for
<remote-db-name>.<remote-schema-name>.<remote-tabname>

The schema name is usually same as the remote db name while tabname is
the same as the remote tabname.

How can I extract these from syscat tables.

"<remote-db-name>.<remote-schema-name>.<remote-tabname>"

Any help appreciated. Thanks.

[Knut Stolze]

Asphalt Blazer wrote:

OS: AIX 5L, DB2 UDB v8.2

the nicknames are created like this

create nickname <schema-name>.<tab-name for
<remote-db-name>.<remote-schema-name>.<remote-tabname>

The schema name is usually same as the remote db name while tabname is
the same as the remote tabname.

How can I extract these from syscat tables.

"<remote-db-name>.<remote-schema-name>.<remote-tabname>"

You get the information from SYSCAT.NICKNAME S. The remote database name
is "hidden" as server option, however, so you have to join those in as
well:

SELECT ( SELECT o.setting
FROM syscat.serverop tions AS o
WHERE o.servername = n.server_name AND
o.option = 'DB_NAME' ) || '.' ||
n.remote_schema || '.' ||
n.remote_table
FROM syscat.nickname s AS n

Note that this will only work if there is actually a database at the remote
data source, which is not mandatory.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Asphalt Blazer]

I did a "select * from syscat.nickname s with UR"

It gives me this : SQL0204N "SYSCAT.NICKNAM ES" is an undefined name.
SQLSTATE=42704

Any idea?

I am working on LUW.
Thanks
Knut Stolze wrote:

Asphalt Blazer wrote:

OS: AIX 5L, DB2 UDB v8.2

the nicknames are created like this

create nickname <schema-name>.<tab-name for
<remote-db-name>.<remote-schema-name>.<remote-tabname>

The schema name is usually same as the remote db name while tabname is
the same as the remote tabname.

How can I extract these from syscat tables.

"<remote-db-name>.<remote-schema-name>.<remote-tabname>"

You get the information from SYSCAT.NICKNAME S. The remote database name
is "hidden" as server option, however, so you have to join those in as
well:

SELECT ( SELECT o.setting
FROM syscat.serverop tions AS o
WHERE o.servername = n.server_name AND
o.option = 'DB_NAME' ) || '.' ||
n.remote_schema || '.' ||
n.remote_table
FROM syscat.nickname s AS n

Note that this will only work if there is actually a database at the remote
data source, which is not mandatory.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[amalnair]

SYSCAT.NICKNAME S looks like a v9 feature.

Any idea how to accomplish this on v8?

Thanks a bunch for the help.
Knut Stolze wrote:

Asphalt Blazer wrote:

OS: AIX 5L, DB2 UDB v8.2

the nicknames are created like this

create nickname <schema-name>.<tab-name for
<remote-db-name>.<remote-schema-name>.<remote-tabname>

The schema name is usually same as the remote db name while tabname is
the same as the remote tabname.

How can I extract these from syscat tables.

"<remote-db-name>.<remote-schema-name>.<remote-tabname>"

You get the information from SYSCAT.NICKNAME S. The remote database name
is "hidden" as server option, however, so you have to join those in as
well:

SELECT ( SELECT o.setting
FROM syscat.serverop tions AS o
WHERE o.servername = n.server_name AND
o.option = 'DB_NAME' ) || '.' ||
n.remote_schema || '.' ||
n.remote_table
FROM syscat.nickname s AS n

Note that this will only work if there is actually a database at the remote
data source, which is not mandatory.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[george.zhang]

Please try:
select * from syscat.taboptio n where tabname =?
Asphalt Blazer wrote:

I did a "select * from syscat.nickname s with UR"

It gives me this : SQL0204N "SYSCAT.NICKNAM ES" is an undefined name.
SQLSTATE=42704

Any idea?

I am working on LUW.
Thanks
Knut Stolze wrote:

Asphalt Blazer wrote:

OS: AIX 5L, DB2 UDB v8.2
>
the nicknames are created like this
>
create nickname <schema-name>.<tab-name for
<remote-db-name>.<remote-schema-name>.<remote-tabname>
>
The schema name is usually same as the remote db name while tabname is
the same as the remote tabname.
>
How can I extract these from syscat tables.
>
"<remote-db-name>.<remote-schema-name>.<remote-tabname>"

You get the information from SYSCAT.NICKNAME S. The remote database name
is "hidden" as server option, however, so you have to join those in as
well:

SELECT ( SELECT o.setting
FROM syscat.serverop tions AS o
WHERE o.servername = n.server_name AND
o.option = 'DB_NAME' ) || '.' ||
n.remote_schema || '.' ||
n.remote_table
FROM syscat.nickname s AS n

Note that this will only work if there is actually a database at the remote
data source, which is not mandatory.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[george.zhang]

it should be:
select * from syscat.taboptio ns where tabname =?

george.zh...@lo mbard.ca wrote:

Please try:
select * from syscat.taboptio n where tabname =?
Asphalt Blazer wrote:

I did a "select * from syscat.nickname s with UR"

It gives me this : SQL0204N "SYSCAT.NICKNAM ES" is an undefined name.
SQLSTATE=42704

Any idea?

I am working on LUW.
Thanks
Knut Stolze wrote:

Asphalt Blazer wrote:
>
OS: AIX 5L, DB2 UDB v8.2

the nicknames are created like this

create nickname <schema-name>.<tab-name for
<remote-db-name>.<remote-schema-name>.<remote-tabname>

The schema name is usually same as the remote db name while tabname is
the same as the remote tabname.

How can I extract these from syscat tables.

"<remote-db-name>.<remote-schema-name>.<remote-tabname>"
>
You get the information from SYSCAT.NICKNAME S. The remote database name
is "hidden" as server option, however, so you have to join those in as
well:
>
SELECT ( SELECT o.setting
FROM syscat.serverop tions AS o
WHERE o.servername = n.server_name AND
o.option = 'DB_NAME' ) || '.' ||
n.remote_schema || '.' ||
n.remote_table
FROM syscat.nickname s AS n
>
Note that this will only work if there is actually a database at the remote
data source, which is not mandatory.
>
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany