Solved

What am I doing wrong with this declare cursor and loading table remotely?

Posted on 2010-09-08
5
1,526 Views
Last Modified: 2012-05-10
Hello all, I am trying to load my AIX DB2 table from a Mainframe DB2 table but getting this error message:
Error:9/8/2010 5:32:34 PM 0:00:03.718: DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64]
SQL0104N  An unexpected token "FROM" was found following ".USR_SMS_CDM  LOAD".  Expected tokens may include:  "HAVING".  SQLSTATE=42601

3: DECLARE mycurs CURSOR FOR SELECT * FROM SCHEMAA.USR_SMS_CDM
LOAD FROM mycurs OF cursor INSERT INTO USRDDBA.USR_SMS_CDM


--------------------------------------


Error:9/8/2010 5:32:34 PM 0:00:03.718: DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64] SQL0104N  An unexpected token "FROM" was found following ".USR_SMS_CDM  LOAD".  Expected tokens may include:  "HAVING".  SQLSTATE=42601

3: DECLARE mycurs CURSOR FOR SELECT * FROM SCHEMAA.USR_SMS_CDM
LOAD FROM mycurs OF cursor INSERT INTO USRDDBA.USR_SMS_CDM

Parser Messages:9/8/2010 5:44:20 PM: line 3, col 23: Incorrect syntax near 'DATABASE'
Error:9/8/2010 5:44:20 PM 0:00:00.390: DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64]

SQL0104N  An unexpected token "DECLARE mycurs CURSOR DATABASE RCADB04T USE" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<space>".  SQLSTATE=42601

3: DECLARE mycurs CURSOR DATABASE RCADB04T USER M081856 USING mypassword FOR SELECT * FROM SCHEMAA.USR_SMS_CDM
LOAD FROM mycurs OF cursor INSERT INTO USRDDBA.USR_SMS_CDM

What do I need to have to run successfully? I am using TOAD for DB2 to test this SQL/Load command.
Any help will be highly appreciated.

Thanks


0
Comment
Question by:okonita1
5 Comments
 
LVL 8

Accepted Solution

by:
mustaccio earned 84 total points
ID: 33632375
I don't use TOAD but I doubt it supports the LOAD command - this is not an SQL statement but rather a DB2 utility.

There is no "DATABASE ... USER ..." clause in the DECLARE CURSOR, by the way.

The easiest way to do it I think is put both lines into a text file and run it from the command line:

$cat myfile
DECLARE mycurs CURSOR FOR SELECT * FROM SCHEMAA.USR_SMS_CDM
LOAD FROM mycurs OF cursor INSERT INTO USRDDBA.USR_SMS_CDM

$db2 +c -f myfile
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 83 total points
ID: 33632447
Hi okonita,

The LOAD command is really a subcommand of the DB2 command line interface.  Run the CLI by typing 'db2' and entering your commands at the prompt.


Kent
0
 
LVL 4

Assisted Solution

by:gmarino
gmarino earned 83 total points
ID: 33632536
The problem seems to be that db2 is reading the DECLARE and LOAD commands as one command.  Use a command delimeter (;)  to separate these 2 commands and run the command using db2 -t

db2 -t

connect to my_db;
DECLARE mycurs CURSOR FOR SELECT * FROM SCHEMAA.USR_SMS_CDM;
LOAD FROM mycurs OF cursor INSERT INTO USRDDBA.USR_SMS_CDM;
connect reset;
0
 

Author Comment

by:okonita1
ID: 33633421
Hell all,
Thank you for your comments and suggestions. The problem I am trying to resolve may be one table at this time. I wanted to find out what I am doing wrong with this one table. However I want to be able to run a script that will do A LOAD for up to 22 tables in one go.
So the suggestions here will serve me well. Let me get to the office thursday and try the suggestion and I will be back to divyup the points.

Again, thank to you all
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 33633588
Consider using the db2move utility - it will automate the process of moving multiple tables.

db2move dbsrc export -sn yourschema -tn table1,table2
db2move dbtarget load -lo replace

This will store data being moved in the file system, unlike loading from a cursor, so you will need sufficient space available.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

18 Experts available now in Live!

Get 1:1 Help Now