Solved

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

Posted on 2014-10-23
5
1,561 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
Comment Utility
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
Comment Utility
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
Comment Utility
Are they both DB2 instances?
0
 

Author Comment

by:data_bits
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 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

8 Experts available now in Live!

Get 1:1 Help Now