Problem with cursors and temporary tables

[fjleon]

Hi. I have this stored procedure:

create procedure consulta_recibos_flota_cobertura_siniestro
(in @tipide char, in @cedreg char(9), in @numpla varchar(8),
 in @fecsin varchar(8))

    dynamic result sets 1
    language sql
    modifies sql data

    begin

        declare sqlStm varchar(5000);

        declare datos cursor with return to caller for
        select vig.ramo, vig.poliza, vig.numrec, vig.stsfin
        from qtemp/cerecvig vig
             inner join qtemp/cerecflo flo on flo.ramo   = vig.ramo   and
                                              flo.poliza = vig.poliza and
                                              flo.numrec = vig.numrec;

        set sqlStm= 'declare global temporary table cerecflo as
        (select distinct c08.ramo, c08.poliza, c08.numrec
         from clli0806 c08
              inner join clli0710 c07 on
                         c07.ramo = c08.ramo and
                         c07.poliza = c08.poliza and
                         c07.cerveh = c08.cerveh
         where c07.numpla = ''' || @numpla || ''' and
               c07.tipide = ''' || @tipide || ''' and
               c07.cedben = ''' || @cedreg || ''') with data with replace';

        prepare qry from sqlStm;
        execute qry;

        set sqlStm= 'declare global temporary table cerecvig as
        (select h02.ramo, h02.poliza, h02.numrec, h02.stsfin
         from hjli0202 h02
         where h02.ramo = 31 and h02.stssit = ''1'' and
               h02.cdoper in (1, 4, 20) and
               (
                ((h02.anodes*10000)+(h02.mesdes*100)+h02.diades) <=
                ' || @fecsin || '
                and
                ((h02.anohas*10000)+(h02.meshas*100)+h02.diahas) >=
                ' || @fecsin || '
               ) ) with data with replace';

        prepare qry from sqlStm;
        execute qry;

        open datos;

    end; 

I used two temporary tables and then a cursor that joins those tables instead
of only using a join with all the tables, because some of them are really big
(hjli0202 65 columns and over 2.5 millions of records, clli0806 28 columns and over 4.7 millions of records)
and the initial full select took too much executing time. Using temporary tables
partitioning the selects and then joining them in a cursor reduced a lot of time.

The problem i'm facing now is when i execute the procedure for a second time from a
C#.net application, i get a SQL0913 error (Row or object CERECFLO in QTEMP type *FILE in use).
I found out that while the cursor is in use the temporary tables won't be dropped from QTEMP
(if a close the cursor after openning it i get no error, but obviously no data either).
I tried to copy the data from cursor datos to another one allowing me returning the new cursor to caller and closing cursor datos)
but i couldn't find how to do that. Is there a way to do this? Could you give me another possible solution?
i'll appreciate a lot any useful help.

I'm using IBM.Data.DB2.iSeries driver to connect from C# to DB2, and a iDB2DataReader to work
with the result set returned from the stored procedure (i close the iDB2DataReader after using it in the C# code,
but it doesn't close the cursor).

[Mercury]

Re: Problem with cursors and temporary tables

Download the Red book Stored Procedures, Triggers and User Defined Functions on DB2 Universal Database for iSeries and read carefully chapter 5.6 Global Temporary Table: A result set alternative from page 106+ where storing a result set into a Global Temporary Table is clearly explained.

[B.Hauser]

Re: Problem with cursors and temporary tables

Hi,

close the cursor before declaring your temporary tables. As long as a cursor that accesses the (temporary) tables is opened, it is not possible to replace the the (temporary) tables.

If the cursor was opened, it will be closed and the temporary tables recreated. If the cursor was not opened, you'll get an error code that you'll ignore.

Birgitta

[fjleon]

Re: Problem with cursors and temporary tables

thanks guys i will investigate this further and report on my findings.

[fjleon]

Re: Problem with cursors and temporary tables

I tried closing the cursor before declaring the temporary tables, but i still get the same error. Here's how i tried to close the cursor:

declare continue handler for sqlstate '24501'
close datos; 

I have read that book from start to finish and there isn't an example that combines both a global temporary table and a cursor. I even tried the example on page 110 but with a cursor instead of the for loop because i need to return the values but it still doesn't work.

I am closing the iDB2DataReader after using it in the C# code,
but it doesn't close the cursor. Here's how i am trying to close it:

iDB2Command cmd = new iDB2Command("consulta_recibos_flota_cobertura_siniestro", CommandType.StoredProcedure, As400Conexion);
iDB2DataReader drRecibos = cmd.ExecuteReader();        
drRecibos.Close();
As400Conexion.Close(); 

Can someone help?

[Mercury]

Re: Problem with cursors and temporary tables

As shown in the examples you do NOT need a cursor because all the selected data are stored in a unique pass into a Global Temporary Table (GTT) at the end of the procedure.

SET s1 = 'DECLARE Global Temporary Table [B]freeemp_results[/B] AS
(SELECT distinct firstname as fname,lastname as lname,workdept as deptnum
FROM employee e
WHERE ' || deptsearch || '
(CURRENT DATE > ALL (SELECT EMP_ENDDATE FROM emp_activeprojects p
WHERE e.empno=p.empno AND emp_enddate IS NOT NULL)
AND empno NOT IN (SELECT empno FROM emp_activeprojects WHERE emp_enddate is null)) )
WITH DATA WITH REPLACE ON COMMIT DELETE ROWS' ;

PREPARE dclstmt FROM s1;
EXECUTE dclstmt;

On the client side you get the values in reading this GTT prefixing the name by "session." (see explanations in the quoted Red Book) :

SELECT ... FROM [B][COLOR=Red]session.[/COLOR]freeemp_results[/B]

HTH

[fjleon]

Re: Problem with cursors and temporary tables

The problem is that i wanted all sql queries on the iseries, so i wanted to use a second stored procedure to call the first and then using a cursor to iterate over the values returned by the first. But doing this causes the same problem about the file being in use. I guess i will break the rule of doing all sql code in the iseries and just do a regular select of the temporary table like you suggest from the application itself.

I could use just one big sql without the global temporary table but it is really too slow. It is so slow that even with the temporary table i am looking at 6 seconds response time. Thanks for your time!

[fjleon]

Re: Problem with cursors and temporary tables

I finally just used a stored procedure to just fill up the global temporary table and then from the application did the sql to read it, but when executing the stored procedure for the second time i got the same problem that the GTT was being used.

So what i did is to store the GTT with a global unique identifier (GUID) so each time i ran the stored procedure it would create the temporary table with a new name. Since it is temporary it should be deleted very soon so that's ok.

I even tried dropping the temporary table first but i still got the same error that the file was already being used.

It isn't the best solution but for now it works.

[Mercury]

Re: Problem with cursors and temporary tables

So what i did is to store the GTT with a global unique identifier (GUID) so each time i ran the stored procedure it would create the temporary table with a new name. Since it is temporary it should be deleted very soon so that's ok.

You'd be better off using a Declare Condition to delete all the data stored previously into the GTT instead as shown below :

Begin[INDENT]...
Declare TABLE_ALREADY_EXISTS Condition For '42710';  
Declare CONTINUE HANDLER For TABLE_ALREADY_EXISTS    
   Delete From SESSION.[I]YourGTTname[/I];  
Declare Global Temporary Table ...
[/INDENT]

So the GTT will be cleared each time you call the SP and the GUID is not needed anymore. FYI SQLSTATE 42701 (or SQL Code -601) is related to the SQL message "&1 in &2 type *&3 already exists."