Solved

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

Posted on 2010-09-08
5
1,535 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

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

Title # Comments Views Activity
Update foreign key reference after insert 9 47
Very interesting Access query problem. 13 70
mySQL Syntax 7 34
find age for two dates 5 34
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

912 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

25 Experts available now in Live!

Get 1:1 Help Now