Re: 4GL Problem

->>From: naomi@qip (Naomi Walker) ->Subject: 4GL Problem ->To: informix-list@rmy.emory.edu (Informix List) ->Date: Thu, 12 Aug 93 9:00:13 MST ->Reply-To: naomi@qip.anasazi.com -> ->I'm posting this on behalf of one of our 4gl guys. Anyone heard of ->this? -> ->Details: -> ->ISQL 4.10.UE1 ->ESQL 4.10.UC1 ->4GLID 4.10.UE1 ->I4GL 4.10.UE1 ->4GLRDS 4.10.UE1 ->ONLINE 4.10.UF3 -> ->Running on Pyramid MIS-S, running dcosx 1.1 c062 -> same symptoms seen on AST 486 running interactiv3 unix -> ->--------------------------------------------------------------------- -> -> The informix error that occurs, is shown in the following: -> -> > Tue Aug 3 04:28:41 MST 1993 -> > + fglgo /prod/fs/support/supexec/support/bin/Olabs -Dfsoff -SNAME -> > Program stopped at "labs2.4gl", line number 135. -> > SQL statement error number -270. -> > Could not position within a temporary file. -> > SYSTEM error number -101. -> > ISAM error: file is not open. -> -> Here is the code (labs2.4gl - with line numbers) at which the above -> error complains: -> -> > 120 -> > 121 DECLARE c2 CURSOR FOR -> > 122 SELECT request, sum(xnumber) -> > 123 FROM dir -> > 124 WHERE request MATCHES p_request -> > 125 AND print_date = 1 -> > 126 GROUP BY request -> > 127 ORDER BY request -> > 128 OPEN c2 -> > 129 PRINT COLUMN 1, "REQUEST", -> > 130 COLUMN 15, " NUMBER" -> > 131 PRINT COLUMN 2, " TYPE", -> > 132 COLUMN 15, "REQUESTED" -> > 133 PRINT COLUMN 1, "-------", -> > 134 COLUMN 15, "---------" -> > 135 FOREACH c2 INTO p_dir.request, p_dir.xnumber -> > 136 PRINT COLUMN 3, p_dir.request, -> > 137 COLUMN 18, p_dir.xnumber using "###" -> > 138 END FOREACH -> -> Looking at the code and the errors note: -> -> Informix complains about not being able to position within a temporary -> file. ISAM complains about the file not being open (the temporary -> file). The actual code is NOT directly creating or manipulating any -> files. -> ... stuff omitted ... -> -> Normally the type of error output by informix (Temporary File No Open, -> and Unable to position within temporay file), is due to running out -> of Unix resources (not enough file descriptors, not enough memory, -> not enough locks, not enough ....). There is no evidence that we have -> run out of file, inodes, memory, disk space, etc.. -> ->-- -> Naomi Walker (aka N7FSA) naomi@anasazi.com -> Phoenix, Arizona -> To get the full value of joy, -> you must have someone to divide it with. ---Mark Twain -> I notice several peculiarities in this code: At line 128 it OPENs cursor c2. At line 135 it FOREACHes the cursor. My understanding is that one uses OPEN (and CLOSE) with FETCH, but that FOREACH automatically opens and closes the cursor in use. This might be confusing Informix. I would suggest removing the 'OPEN c2'. A second oddity is that the code is doing a significant amount of DB I/O within a REPORT (implied by the PRINTs) but without using the REPORT parameters. (I don't know this, but am guessing that p_dir.* are not parameters of the REPORT.) If you do not use ORDER EXTERNAL in a report, under certain circumstances Informix will use a temp file to collect data and then sort it for the report. Using a cursor with an ORDER BY clause within the report may have triggered this action. Too bad 'request MATCHES p_request' instead of 'request = p_request'. In the latter case, you could eliminate the cursor completely, replacing it with a simple SELECT sum(xnumber) .... I don't see a way to do this if you want to retain the generality of the MATCHES syntax. I hope this helps, ___________________________ O Alan __________________| R. Alan Popiel | H _________________________| Internet: | Martin Marietta, Tech Ops | H \\ | alan@den.mmc.com | P.O. Box 179, M/S 5422 | H \\ Std disclaimers apply.| Voice: | Denver, CO 80201-0179 USA | H ) | 303-977-9998 |___________________________| H / (But you knew that!) |_____________________) H /___________________________) H