Solved

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

Posted on 2014-10-23
5
1,776 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
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:Kent Olsen
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:
Kent Olsen 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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 tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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