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

forgot to mention we are using Data Migrator 5.x version.

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?

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.

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