Pascal,

It's your "disconnect DB2" which delete the table not the quit. This the disconnect which close the session.

When I use temporary table, I use this method : the temp table is created before by a process (not with sas I mean) and I load it with proc append. When the SAS job is terminated, a cobol program test/analyze and load the data into a 'true' table.

The second method of which I think is to create the table with proc sql as you could do with an inobs=0 for example, load into with proc append, and call the specific program which load the data into db2 permanent table. After you can disconnect from DB2.

Coud you try this ?

I tested the sequential file loading method and it works very well. We load the data into a file and with the "dbload utility" (I wonder if it's the name...maybe) a program load the data into the db2 table.

I'll answer tomorrow more details about the creation of my temp tables.

PS : why your dba want a sql statement for the temp table ? I'm not sure he can't see the difference with a data step.

Stéphane

-----Message d'origine----- De : SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] De la part de tonts Envoyé : mercredi 30 juin 2004 20:31 À : SAS-L@LISTSERV.UGA.EDU Objet : Re: load SAS table into DB2 temporary table

Lois,

Thanks for your answer. But the problem is a little bit different :

The DB2 table MUST BE a temporary table and can only be created into a PROC SQL statment (strict instruction from my Data Base Administrator). And, at the end of the Proc SQL statment (the "quit;" instruction), the DB2 table is automatically delete. Therefore, I have to do all my manipulation inside a PROC SQL statment...

Not very easy when I have to optimize the statments....

Thanks for everybody who will help me.

Pascal

<lois831@hotmail.com> a écrit dans le message de news:3b15b5e0.0406292121.347bca28@posting.google.com... > Pascal, > > Creating a DBMS table using SQL INSERT statements is a very slow > method and you don't need to write out your SAS dataset as a > sequential file. Try using the LIBNAME statement to define your DB2 > database and then just create the table as if it were a regular SAS > dataset. If you have a lot of data then you can use the BULKLOAD > option to load all of the records. > > Here is an example: > > libname session db2 user=aaa password=bbb ssid=DB0N schema=ICMC; data > session.client; > set ICMFRPD.ICMFRCAR; > run; > > or with BULKLOAD For a lot of data: > > data session.client(bulkload=yes); > set ICMFRPD.ICMFRCAR; > run; > > > For more details feel free to read my SUGI paper > > http://www2.sas.com/proceedings/sugi29/106-29.pdf > > It refers to Oracle tables but most of it applies to DB2 as well. I > hope you find it helpful. > > Lois Levin > >