Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-09-08
5
Medium Priority
?
1,630 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
[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
5 Comments
 
LVL 8

Accepted Solution

by:
mustaccio earned 336 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 46

Assisted Solution

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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

722 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