f



load SAS table into DB2 temporary table

Hello,

I have a problem with the load DB2.

I'm working on MVS system under Z/OS and with DB2.

I have to create a DB2 temporary table from a SAS table.

I've tried 2 solution, the first work good but it's slow to store the
records
The second should be very faster but it doesn't work.

The 2 solutions :

1.  put all the record read from the SAS table into macro-variable
     then create the DB2 temporary table
     then do a loop to insert all macro-variable in the DB2 table
     then do a SQL request to try if the table is loaded

==> this work very good

2. create a SAS table with the data to store into DB2
    create a sequentiel file (SAM) on the MVS system
    then create the DB2 temporary table
    then loas the MVS file in the DB2 table
    then do a SQL request to try if the table is loaded

==> this doesn't work, in the DB2 "load" order. He doesn't understand the DD
statment, and like to see that he did not make the link between the
allocation and the DD order in the load syntax.

I run this macro in the interaction mode, not batch mode.

May be it doesn't work because the "load DB2" order only work in batch mode
?
Is there a way to by pass this problem ?

==> I think that the load instraction is much faster then the insert
instruction, so if I have to insert thounsend of record, I hope to optimize
the load....

Can any body help me  ?

Thanks you very much
Pascal

--------------------------------------
Here are the 2 SAS programms :
---------------------------------------

SOLUTION 1 who works :
********************

%macro TESTDB2;

data _null_;
  set ICMFRPD.ICMFRCAR;
  N= compress(put(_N_, best.));
  call symput('mvNUCAMO'!!N, NUCAMO);
  call symput('mvNBCG', N);
run;

 proc sql ;

/* Connexion to DB2 */

  connect to db2 (ssid=DB0N schema=ICMC);

/* creation of the DB2 temporary table */

        execute (
           DECLARE GLOBAL TEMPORARY TABLE SESSION.CLIENT
           (
            IDFCLI               CHAR(19)  NOT NULL
            )
     on commit preserve rows
        ) by db2;


/* loop to insert rows from the macro-variables  */

  %do z=1 %to &mvNBCG;

        execute (
     INSERT INTO SESSION.CLIENT
     VALUES (%bquote('&&mvNUCAMO&z'))
     ) by db2;
         %end;

      execute (
     COMMIT
     ) by db2;

/* one request to see if the table is good loaded  */

         create TABLE TABSAS as
      select * from connection to db2
   (

  select
      IDFCLI
    from
    SESSION.CLIENT

      );

/* disconnect from DB2 */

  disconnect from db2;
 quit ;

%mend;

%testdb2;


SOLUTION 2 who doesn't works :
**************************

%macro TESTDB2;

/* creation of a table with only the field to insert into the DB2 table */

data TABNUM(keep =  NUCAMO);
  set ICMFRPD.ICMFRCAR;
run;

/* macro that create a sequentiel file on MVS system  */

%icmextab(mvNOMVUE= TABNUM
           ,mvFICSEQ= 'IMNT1.DEV.PJE.FICSEQ1'
   );

/* allocation the MVS sequentiel file */

x ALLOC file(SYSREC) da('IMNT1.DEV.PJE.FICSEQ1') shr;

 proc sql ;

/* Connexion to DB2 */

  connect to db2 (ssid=DB0N schema=ICMC);

/* creation of the DB2 temporary table */

        execute (
           DECLARE GLOBAL TEMPORARY TABLE SESSION.CLIENT
           (
            IDFCLI               CHAR(19)  NOT NULL
            )
     on commit preserve rows
        ) by db2;

/* load the sequentiel file in the DB2 table  */

        execute (
     LOAD DATA INDDN SYSREC INTO TABLE SESSION.CLIENT
     (NUCAMO position (1) char(19))
     ) by db2;

      execute (
     COMMIT
     ) by db2;

/* one request to see if the table is good loaded  */

      select * from connection to db2
   (
  select
      IDFCLI
    from
    SESSION.CLIENT
      );

/* disconnect from DB2 */

  disconnect from db2;
 quit ;

%mend;

%testdb2;


0
tonts (4)
6/28/2004 5:31:16 PM
comp.soft-sys.sas 142827 articles. 3 followers. Post Follow

2 Replies
1161 Views

Similar Articles

[PageSpeed] 32

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



"tonts" <tonts@club-internet.fr> wrote in message news:<40e055e8$0$313$7a628cd7@news.club-internet.fr>...
> Hello,
> 
> I have a problem with the load DB2.
> 
> I'm working on MVS system under Z/OS and with DB2.
> 
> I have to create a DB2 temporary table from a SAS table.
> 
> I've tried 2 solution, the first work good but it's slow to store the
> records
> The second should be very faster but it doesn't work.
> 
> The 2 solutions :
> 
> 1.  put all the record read from the SAS table into macro-variable
>      then create the DB2 temporary table
>      then do a loop to insert all macro-variable in the DB2 table
>      then do a SQL request to try if the table is loaded
> 
> ==> this work very good
> 
> 2. create a SAS table with the data to store into DB2
>     create a sequentiel file (SAM) on the MVS system
>     then create the DB2 temporary table
>     then loas the MVS file in the DB2 table
>     then do a SQL request to try if the table is loaded
> 
> ==> this doesn't work, in the DB2 "load" order. He doesn't understand the DD
> statment, and like to see that he did not make the link between the
> allocation and the DD order in the load syntax.
> 
> I run this macro in the interaction mode, not batch mode.
> 
> May be it doesn't work because the "load DB2" order only work in batch mode
> ?
> Is there a way to by pass this problem ?
> 
> ==> I think that the load instraction is much faster then the insert
> instruction, so if I have to insert thounsend of record, I hope to optimize
> the load....
> 
> Can any body help me  ?
> 
> Thanks you very much
> Pascal
> 
> --------------------------------------
> Here are the 2 SAS programms :
> ---------------------------------------
> 
> SOLUTION 1 who works :
> ********************
> 
> %macro TESTDB2;
> 
> data _null_;
>   set ICMFRPD.ICMFRCAR;
>   N= compress(put(_N_, best.));
>   call symput('mvNUCAMO'!!N, NUCAMO);
>   call symput('mvNBCG', N);
> run;
> 
>  proc sql ;
> 
> /* Connexion to DB2 */
> 
>   connect to db2 (ssid=DB0N schema=ICMC);
> 
> /* creation of the DB2 temporary table */
> 
>         execute (
>            DECLARE GLOBAL TEMPORARY TABLE SESSION.CLIENT
>            (
>             IDFCLI               CHAR(19)  NOT NULL
>             )
>      on commit preserve rows
>         ) by db2;
> 
> 
> /* loop to insert rows from the macro-variables  */
> 
>   %do z=1 %to &mvNBCG;
> 
>         execute (
>      INSERT INTO SESSION.CLIENT
>      VALUES (%bquote('&&mvNUCAMO&z'))
>      ) by db2;
>          %end;
> 
>       execute (
>      COMMIT
>      ) by db2;
> 
> /* one request to see if the table is good loaded  */
> 
>          create TABLE TABSAS as
>       select * from connection to db2
>    (
> 
>   select
>       IDFCLI
>     from
>     SESSION.CLIENT
> 
>       );
> 
> /* disconnect from DB2 */
> 
>   disconnect from db2;
>  quit ;
> 
> %mend;
> 
> %testdb2;
> 
> 
> SOLUTION 2 who doesn't works :
> **************************
> 
> %macro TESTDB2;
> 
> /* creation of a table with only the field to insert into the DB2 table */
> 
> data TABNUM(keep =  NUCAMO);
>   set ICMFRPD.ICMFRCAR;
> run;
> 
> /* macro that create a sequentiel file on MVS system  */
> 
> %icmextab(mvNOMVUE= TABNUM
>            ,mvFICSEQ= 'IMNT1.DEV.PJE.FICSEQ1'
>    );
> 
> /* allocation the MVS sequentiel file */
> 
> x ALLOC file(SYSREC) da('IMNT1.DEV.PJE.FICSEQ1') shr;
> 
>  proc sql ;
> 
> /* Connexion to DB2 */
> 
>   connect to db2 (ssid=DB0N schema=ICMC);
> 
> /* creation of the DB2 temporary table */
> 
>         execute (
>            DECLARE GLOBAL TEMPORARY TABLE SESSION.CLIENT
>            (
>             IDFCLI               CHAR(19)  NOT NULL
>             )
>      on commit preserve rows
>         ) by db2;
> 
> /* load the sequentiel file in the DB2 table  */
> 
>         execute (
>      LOAD DATA INDDN SYSREC INTO TABLE SESSION.CLIENT
>      (NUCAMO position (1) char(19))
>      ) by db2;
> 
>       execute (
>      COMMIT
>      ) by db2;
> 
> /* one request to see if the table is good loaded  */
> 
>       select * from connection to db2
>    (
>   select
>       IDFCLI
>     from
>     SESSION.CLIENT
>       );
> 
> /* disconnect from DB2 */
> 
>   disconnect from db2;
>  quit ;
> 
> %mend;
> 
> %testdb2;
0
lois831 (1)
6/30/2004 5:21:39 AM
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
>
>


0
tonts (4)
6/30/2004 6:30:35 PM
Reply: