Solved

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

Posted on 2014-10-23
5
1,680 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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