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;
SELECT t1.C_NO t2.CD1
where T1.C_NO = T2.C_NO
AND T1.co_cd = T2.co_cd
TABLE ON TABLE HOLD AS HOLD_FILE FORMAT INTERNAL
-***APPEND FILEDEF once ****
-IF &LCTR NE 1 THEN GOTO NOFLDF ;
FILEDEF HOLD_FILE DISK &FILEAPPEND
-SET &LCTR = &LCTR + 1 ;
-SET &LASTEND = &END_VAL ;
Can anybody see what is wrong and why it is behaving like that ????
Any help would be very much appreciated.
Dev Studio 7.1.6,Report Caster 7.1.1,Servlet
Dev Studio 7.1.6,Report Caster 7.1.1,Apache 5.0.2,