Writing temporary table problem

[Jabeena]

I have a problem writing to a temporary table. Here are some additional indications

- Z / OS
- COBOL program.
- DB2 V7

Here is my code

creating the temporary table

EXEC SQL                                                          
    CREATE GLOBAL TEMPORARY TABLE SESSION.TEMPCOCS                 
     ( DATA1                 CHAR(12) NOT NULL,               
       DATA2               CHAR(4) NOT NULL,                
       DATA3               CHAR(45) NOT NULL,               
        )                                                          
 END-EXEC.

I integrate the data into the table:

EXEC SQL                          
   INSERT INTO SESSION.TEMPCOCS   
          (DATA1                  
          ,DATA2                  
          ,DATA3                  
          )                       
   VALUES (:WS-DATA1              
          ,:WS-DATA2              
          ,:WS-DATA3              
          )                       
 END-EXEC.

the displays shows that we move 22 times in the INSERT table

at the end I expect the data in this table:

EXEC SQL                                                     
 SELECT COUNT(*) INTO :WS-COMPT FROM SESSION.TEMPCOCS           
 END-EXEC.

but the result is zero.

I tried to add like but nothing changes. I'm always in the same session when I did the select count.

[$tatic]

Recall the principle of global temporary tables: Data inserted in a xxl are automatically deleted without user intervention (no delete) in the first commit. So, if after each insertion, you do a commit, this has the effect of removing the line you just inserted (same with a rollback)!

Check between your insert and your select, there is no order to generate an automatic commit and normally you should be able to read your lines without worry.

The global temporary tables may go many services but keep in mind that you can not load lines in a xxl x in a program for use in a program there, unless these programs are actually ss pros-known even by the calling program.

The only exception I know to delete the rule in cases of automatic commit: if you declared a cursor with the option 'with hold' on the global temporary tables, whereas a commit does not delete rows from the xxl. This allows, inter alias, use a xxl in a program and you will be able to make intermediate commits to release locks and validate what is already made.

Other rules on xxl: no update, no delete selectively, just the SQL insert, select and delete without where clause.

[MindSpace]

The data should probably be written in blocks (or groups) of lines, ie the physical transfer between memory and BDD occurs only when a number of lines is placed in the output buffer. This number is usually determined by the compiler determining the blocking factor (number of rows per block), depending on factors such as length of each line (record), the volume of the database, the space available on discs, etc..

It is therefore possible, although data are not yet physically written to the DB and still reside in the write buffer until the block is completed by additional lines or that the table is closed. So, the select count (*) would give zero.

[opaper]

It is therefore possible, although data are not yet physically written to the DB and still reside in the write buffer until the block is completed by additional lines or that the table is closed. So, the select count (*) would give zero.

I think not. Even if the rows are physically stored offline (and yet here we are dealing with a temporary table), the SELECT statement always reads the "buffer" of data (we talk about BUFFER POOL in DB2 z/OS terminology ).

[CitricAcid]

Replace CREATE by DECLARE , for example:

In working 
EXEC SQL                                              
  DECLARE SESSION.TEMP_NOTEBOOK TABLE                   
  (IDCORIG CHAR(3) ,                                  
   BANK CHAR(5) )                                  
END-EXEC      
                                        
In procedure 
EXEC SQL                                                       
  DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_NOTEBOOK            
  (IDCORIG CHAR(3) ,                                           
   BANK CHAR(5) )                                           
END-EXEC 
 
EXEC SQL                                                       
  INSERT INTO SESSION.TEMP_NOTEBOOK            
  ( IDCORIG ,                                                  
    BANK )                                                  
  VALUES                                                       
  ( 'BET'   ,                                                  
    '12345' )                                                  
END-EXEC