Solved

SQL1013N  The database alias name or database name "MRIIN" could not be found.  SQLSTATE=42705

Posted on 2014-10-23
5
1,628 Views
Last Modified: 2014-10-23
I'm trying to set up an environment where I can use "LOAD from cursor" to move data from one remote database to another database.

INSDB is the target and MRIINS is the source.

I have the remote source databases cataloged as such and can connect to them.

 Database alias                       = MRIIN
 Database name                        = MRIIN
 Node name                            = MRDBA01
 Database release level               = d.00
 Comment                              = MRIIN on dbs01
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

 Database alias                       = INSB
 Database name                        = INSB
 Node name                            = QADBS01
 Database release level               = d.00
 Comment                              = INSDB on qadbs01
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =

However, when I run my sql script to load the data, I get the SQL1013N error.
---
connect to insdb user batchusr using

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = BATCHUSR
 Local database alias   = INSDB


DECLARE IIS_ACCAOR_CURSOR cursor DATABASE MRIIN user BATCHUSR USING          for select * from IIS.ACCAOR with ur
DB20000I  The SQL command completed successfully.

LOAD FROM IIS_ACCAOR_CURSOR of cursor REPLACE into IIS.ACCAOR nonrecoverable
SQL1013N  The database alias name or database name "MRIIN" could not be found.  SQLSTATE=42705

Why is the script now giving me this error?  What piece of the puzzle am I missing?

Thanks!
0
Comment
Question by:data_bits
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 40399776
Hi databits,

The LOAD statement has the same authentication requirements as other SQL statements.

  DECLARE IIS_ACCAOR_CURSOR cursor DATABASE MRIIN user BATCHUSR USING          for select * from IIS.ACCAOR with ur

Test that BATCHUSR is authorized to get to MRIIN

  CONNECT TO MRIIN user BATCHUSR using ******
0
 

Author Comment

by:data_bits
ID: 40399829
I am able to connect to MRIIN as batchusr outside of the script. And the batchusr id has connect and select privs on the table in  the MRIIN database. But I still get the SQL1031N.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 40400075
Are they both DB2 instances?
0
 

Author Comment

by:data_bits
ID: 40400151
Yes, both databases are in DB2 instances. But they are on separate servers.
Do in need to catalog the MRIIN database to the instance where INSDB database resides?
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 40400229
To "catalog the MRIIN database to the instance where INSDB database resides" you would essentially be setting up the Federated Environment.  If you're using DB2 Express-C, you won't be able to do that.

It looks like both databases are defined to your local client with CATALOG DATABASE or CATALOG TCPIP NODE.  But I believe that what you're trying to do requires that the databases be federated.

If you're not in a federated environment, just EXPORT the data from the source and IMPORT it into the target.


Kent
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now