Db2 import and user access

access-controldb2

When I import data from DB2 using db2move, the dump file comes with the data exported as "USERX.MYTABLE".

The import process seems to be fine, I see lots of:

USERX.MYTABLE n rows imported …

But USERX is not on my database and when I try to select data using MYUSER, I always get empty results.

Also, if I run db2move and export the data from my local database, it agains export all rows for USERX.

How can I fix the access to MYUSER?

Best Answer

If you don't want to specify the schema explicitly, you can choose one of several options, for example:

Modify the default schema in the session, e.g. by issuing the statement SET CURRENT SCHEMA USERX.
Create an alias for the table USERX.MYTABLE in the schema MYUSER.
Before importing data, edit the file db2move.lst and replace all occurrences of USERX with MYUSER.

Related Solutions

DB2 Defining remote database alias

First you need to catalog the remote instance.

db2 catalog tcpip node <my alias node name> remote <host or host alias name> server <port>

Issue db2 terminate to make sure the catalog entry remains.

Now you can actually catalog the remote database.

db2 catalog db <remote database name> as <local alias> at node <node alias supplied above>

And once again issue db2 terminate

Here is an example of what this could look like:

db2 catalog tcpip node devinst1 remote devservera server 50100;
db2 terminate;
db2 catalog db crmdb as remotedb at node devinst1;
db2 terminate;

Now you can connect to that local database using either the db name (crmdb) or the alias name (remotedb) depending on your needs.

Note: Sometimes you can run into issues where it will say things about a null package id. This would mean that particular packages need to be bound for the connection to work. Easiest way to solve this is to have your instance owner id (of the remote database) make the first connection and then disconnect or to have your id you are using have BINDADD authority. Then the correct packages will get bound. You may not run into this, especially if you are on the same version and fix pack level of DB. If you ever differ between "client" and "server" is when you for sure have to do this.

Db2 – SQL3550W: … target column has been defined as GENERATED ALWAYS

Using the same IXF files created by db2move export, you can manually load the data using db2 import with the MODIFIED BY IDENTITYOVERRIDE parm for those tables that require it.

Best Answer

Related Solutions

DB2 Defining remote database alias

Db2 – SQL3550W: … target column has been defined as GENERATED ALWAYS

Related Question