Sestavení clastru

/* Target table/view(s)  */
%let _OUTPUT = L2_spds.PREDPOJ_RIZ;
%let _OUTPUT1 = L2_spds.PREDPOJ_RIZ;

/* Options  */
%let etls_filter = %nrquote(PREDPOJ_RIZ);
%let etls_maxslots = %nrquote(2);

/* List of target columns to keep  */
%let keep = idsml drpr drzuc kodpp riziko castpr plod pldo datspl datvytv storno muc
        ruc upisovaci_rok idflot orgj inkr_pk inks_pk smlo_pk prod_pk idri_pk
        part_pk orgj_pk odve_pk;

%macro clusterCreateAdd;
/* Input template on PFD is included to allow inserting in a process flow.                   */
/* Use _output macro var to get the name of the library. (Assumes user removed SPDS loader.) */
%let etls_libref=%scan(&_output,1,.);
%let etls_outdsn=%scan(&_output,2,.);

proc sql;
   create table work.SPDSTMP as
      select memname length = 32
         from dictionary.tables
            where upcase(libname) = %upcase(“&etls_libref”) and
                  upcase(memname) ^= %upcase(“&etls_outdsn”); 
quit;

data _null_;
  /*  If the cluster already exists        */
  /*  then generate code for an add.       */
  /*  Otherwise generate code for a create.*/ 
  if exist(“&_output”) then
     call symput(‘etls_exist’,”YES”); 
  else
     call symput(‘etls_exist’,”NO”);
run;

data _null_;
   set work.SPDSTMP end=eof;  

   %if “&etls_filter” ne “” %then
   %do;
      where index(upcase(memname),%upcase(“&etls_filter”)) > 0;
   %end;
  
   if _n_=1 then
   do;
     call execute(“PROC SPDO library=&etls_libref;”);
     if “&etls_exist”=”YES” then
        call execute(“cluster add &etls_outdsn “);
     else
        call execute(“cluster create &etls_outdsn “);   
   end;
   call execute(” mem=”||memname);
   if eof then do;
      if “&etls_exist”=”YES” then
             call execute(” ; quit;”);
           else
      /* Only put the maxslots when creating a */
      /* new cluster.  If max slots from the   */
      /* options tab is 0 then the number of   */
      /* tables in the library will be used.   */             
           do;
              length etls_max 8;
         if &etls_maxslots > 0 then
                 etls_max = &etls_maxslots;
              else
                 etls_max = _n_;      
         call execute(” maxslot=”||trim(left(put(etls_max,8.)))||”; quit;”);    
           end;
   end;
run;
%mend clusterCreateAdd;
%clusterCreateAdd;

Rozbytí clusteru

/* Source table(s)/view(s)  */
%let _INPUT1 = L2_spds.PREDPOJ_RIZ;
%macro clusterUndoTables;
%let etls_libref=%scan(&_INPUT1,1,.);
%let etls_dsn=%scan(&_INPUT1,2,.);
/* Undo removes the cluster definition.         */
/* The clustered tables are returned to their   */
/* original unclustered state                   */
data _null_;
       call execute(‘Proc spdo library=&etls_libref;’);
       call execute(‘          cluster undo &etls_dsn;’);
       call execute(‘quit;’);
run;
%mend clusterUndoTables;
%clusterUndoTables;