How is refresh of nickname definition done?

[Prasad]

Hi,

We have DB2 7 fixpak 14 on AIX 5.1. There are federated links
(nicknames) established for tables in Informix database.

One of the table was modified in Informix. The table has now got 3 new
columns. These columns are not seen in DB2 nickname definition. When we
run the queries, we do not see these columns in the result. When we
describe the nickname, these columns are not present.

Now, How can we refresh the nickname defininton without dropping and
recreating it? There is a stored procedure nnstat in Version 8. But we
are at version 7.

Please can someone help.

Thanks,
Prasad

[Knut Stolze]

Prasad wrote:

Hi,

We have DB2 7 fixpak 14 on AIX 5.1. There are federated links
(nicknames) established for tables in Informix database.

One of the table was modified in Informix. The table has now got 3 new
columns. These columns are not seen in DB2 nickname definition. When we
run the queries, we do not see these columns in the result. When we
describe the nickname, these columns are not present.

Now, How can we refresh the nickname defininton without dropping and
recreating it? There is a stored procedure nnstat in Version 8. But we
are at version 7.

DROP and CREATE is the only way in V7.

Given that V7 is out of service for 1 year now, you should consider moving
to V8 or even V9.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Prasad]

Thank you for your response. We are in the process of upgrade. In the
meantime, this requirement came up.

Is there any way to capture the definitions of all the nicknames,
indexes & views etc created on the nicknames? db2look does not give
this information.

[Knut Stolze]

Prasad wrote:

Thank you for your response. We are in the process of upgrade. In the
meantime, this requirement came up.

Is there any way to capture the definitions of all the nicknames,
indexes & views etc created on the nicknames? db2look does not give
this information.

There are no indexes on nicknames. db2look (V8) does collect this
information. So again, the my answer would be to upgrade to V8 or V9.

Have a look at your V7 manual (or "db2look -h" to see if it covers any
federated objects).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Prasad]

We have definition only indexes on the nicknames. db2look does not have
the option to capture definitions of nicknames and other objects
depending on nicknames.

We are upgrading to V9 next year. This issue has come up now.

[Knut Stolze]

Prasad wrote:

We have definition only indexes on the nicknames. db2look does not have
the option to capture definitions of nicknames and other objects
depending on nicknames.

We are upgrading to V9 next year. This issue has come up now.

Well, db2look does extract wrapper, server, user mapping, nickname, and
index specification definitions:

$ db2look -d test -e | less

-- ---------------------------
-- DDL Statements for WRAPPER
-- ---------------------------
CREATE WRAPPER "DRDA"
LIBRARY 'libdb2drda.so'
OPTIONS (DB2_FENCED 'Y'
);
-- ---------------------------
-- DDL Statements for SERVER
-- ---------------------------
CREATE SERVER "SRV1"
TYPE DB2/UDB
VERSION '8'
WRAPPER "DRDA"
OPTIONS
(DBNAME 'remote'
);
-- --------------------------------
-- DDL Statements for USER MAPPING
-- --------------------------------
CREATE USER MAPPING FOR STOLZE
SERVER "SRV1"
OPTIONS
(REMOTE_AUTHID 'stolze'
,REMOTE_PASSWORD ''
);
-- --------------------------------
-- DDL Statements for NICKNAME
-- --------------------------------

-- DEFINER = STOLZE
CREATE NICKNAME "STOLZE "."N1"
FOR "SRV1"."STOLZE"."T1";
-- --------------------------------
-- DDL Statements for CREATE INDEX...SPECIFICATION ONLY
-- --------------------------------
CREATE INDEX "STOLZE"."IDX"
ON "STOLZE"."N1" (A ASC)
SPECIFICATION ONLY;

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Prasad]

I executed the same command (with our DB name) you provided on our
system. It does not provide the nickname definitions. Did you execute
the command on v7 system?