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

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


okonita1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mustaccioConnect With a Mentor Commented:
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
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
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
 
gmarinoConnect With a Mentor Commented:
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
 
okonita1Author Commented:
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
 
mustaccioCommented:
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
All Courses

From novice to tech pro — start learning today.