Solved

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

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

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…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

803 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