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
Content-Type: text/plain; charset="iso-8859-1"
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.
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
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.
<firstname.lastname@example.org> a écrit dans le message de
> 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
> set ICMFRPD.ICMFRCAR;
> or with BULKLOAD For a lot of data:
> data session.client(bulkload=yes);
> set ICMFRPD.ICMFRCAR;
> For more details feel free to read my SUGI paper
> It refers to Oracle tables but most of it applies to DB2 as well. I
> hope you find it helpful.
> Lois Levin