LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (June 2004, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 30 Jun 2004 22:21:09 +0200
Reply-To:     Datametric <datametric@CLUB-INTERNET.FR>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Datametric <datametric@CLUB-INTERNET.FR>
Subject:      RE : load SAS table into DB2 temporary table
Comments: To: tonts <tonts@club-internet.fr>
In-Reply-To:  <40e306d4$0$313$7a628cd7@news.club-internet.fr>
Content-Type: text/plain; charset="iso-8859-1"

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 > >


Back to: Top of message | Previous page | Main SAS-L page