Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
SQL FETCH CURSOR ERROR
 Login/Join
 
Gold member
posted
Every month we are loading data into to final table using a DF. A stored procedure creats the HOLD file and this HOLD file is act as the source for the final table.The hold file is created from other tables using joins conditions and saves the output using FILEDEF to disk(.ftm).

Since we are dealing with millions of records, we splitted the primary key into different ranges using the NTILE(20) command (oracle) and loops the SQL to create HOLD file for 20 ranges. So every looping, the the SQL in the stored procedure gets the data for that range and appends the output to the saved HOLD file using FILEDEF.

ie,Everytime the DM reads the Begin and End values from the NTILE range and executes for that range and the output is appended to the file saved in the disk.

This logic was working fine but suddenly we are getting cursor error while runing the SQL for HOLD file.

"(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
(FOC1407) SQL FETCH CURSOR ERROR. : SQLOUT" and only less records are captured in the HOLD file.

But when we increase the NTILE range from 20 to a much higher value(20000), it worked fine.

This is the structure of the code we are using.

FILEDEF HOLD_FILE CLEAR
FILEDEF HOLD_FILE DISK HOLD_FILE.FTM
-SET &LCTR = 1;
-START_LP

SQL SQLORA
SELECT t1.C_NO t2.CD1
FROM T1,T2
where T1.C_NO = T2.C_NO
AND T1.co_cd = T2.co_cd
.
.
.
.
TABLE ON TABLE HOLD AS HOLD_FILE FORMAT INTERNAL
END
-RUN

-END_LP

-***APPEND FILEDEF once ****

-IF &LCTR NE 1 THEN GOTO NOFLDF ;
FILEDEF HOLD_FILE DISK &FILEAPPEND

-NOFLDF
-SET &LCTR = &LCTR + 1 ;

-SET &LASTEND = &END_VAL ;
-GOTO START_LP

Can anybody see what is wrong and why it is behaving like that ????

Any help would be very much appreciated.

Regards


Dev:
Dev Studio 7.1.6,Report Caster 7.1.1,Servlet
Local:
Dev Studio 7.1.6,Report Caster 7.1.1,Apache 5.0.2,
Win XP


 
Posts: 80 | Registered: March 21, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
forgot to mention we are using Data Migrator 5.x version.
 
Posts: 80 | Registered: March 21, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Prasanna, you mentioned increasing NTILE from 20 to 20000 and it worked, but the code you included does not contain any mention of NTILE. Could you please send all of the SQL?


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Also, what 'loop' is it in when it fails and what are the values of the variables at that time? You can get this information from the log if you have &ECHO turned on. If you don't have &ECHO turned on and you've not used it before, put this in the top of your stored procedure -SET &ECHO=ALL; Then run it again. Seeing what loop it's in when it fails and what the variables are at that time might give you an idea as to what is wrong.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Prasanna,

Put a -EXIT before the loop for starters (with the the 'echo' statement Jessica wisely recomended.) Does it run? If not - your loop is not the problem. Also here is a sample of some code written here by me. NOTICE THE ';' before the TABLE....I do not see this in your code.

SQL SQLORA
SELECT vvvvvv FROM wwwwwww
WHERE zzzzzzzz = 'OK'
;
TABLE
ON TABLE HOLD
END



Ira wf 536 aix 533


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.