Focal Point
SQL FETCH CURSOR ERROR

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/2731046262

December 21, 2007, 12:00 PM
Prasanna
SQL FETCH CURSOR ERROR
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


December 21, 2007, 12:02 PM
Prasanna
forgot to mention we are using Data Migrator 5.x version.
December 22, 2007, 09:11 AM
Jessica Bottone
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
December 22, 2007, 09:18 AM
Jessica Bottone
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
December 24, 2007, 09:57 AM
ira
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