Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi

Read-Only Read-Only Topic
Go
Search
Notify
Tools
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, 2007Report This Post
Gold member
posted Hide Post
forgot to mention we are using Data Migrator 5.x version.
 
Posts: 80 | Registered: March 21, 2007Report 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, 2007Report 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, 2007Report 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, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders