/******************************************************************************
 * Programme : SAS Viya : Comment accélérer le chargement PostgreSQL vers CAS avec le multi-nœuds
 * Reference : CHARGE58A4
 * Source    : https://www.wearecas.eu/en/sampleCode/CHARGE58A4
 ******************************************************************************/

/* --- BLOC 1 --- */
cas mySession sessopts=(metrics=true) ;

%let dclogpath=/viya/demo/DM/data/LOG ;
%let logprefix=&sysuserid._sasdcpg_ ;

%put My Userid is: &sysuserid ;

options msglevel=i ;

%deleteTraceFiles(dclogpath=&dclogpath,logprefix=&logprefix) ;
caslib caspg drop ;


/* --- BLOC 2 --- */
/* Set the numreadnodes parameter high so the log tells us CAS is attempting multi-node load */
/* Set some tracing options */
caslib caspg datasource=(srctype="postgres",username="myuser",password="mypw",server="myserver",
   database="dvdrental",schema="public",numreadnodes=10,numwritenodes=10,
   DRIVER_TRACE="SQL",
   DRIVER_TRACEFILE="&dclogpath/&logprefix.$SAS_CURRENT_HOST.log",
   DRIVER_TRACEOPTIONS="TIMESTAMP|APPEND") libref=caspg ;


/* --- BLOC 3 --- */
/* List source files and target tables */
proc casutil incaslib="caspg" ;
   list files ;
   list tables ;
quit ;


/* --- BLOC 4 --- */
/* Load the FILM PostgreSQL table in CAS in multi-node mode */
/* Drop the CAS table before in case it exists */
/* List target tables */
proc casutil incaslib="caspg" outcaslib="caspg" ;
   droptable casdata="&sysuserid._PG_film" quiet ;
   load casdata="film" casout="&sysuserid._PG_film" ;
   list tables ;
quit ;


/* --- BLOC 5 --- */
/* Display SAS Data Connector trace files */
%displayTrace(dclogpath=&dclogpath,logprefix=&logprefix) ;

/* Delete the SAS Data Connector trace files */
%deleteTraceFiles(dclogpath=&dclogpath,logprefix=&logprefix) ;


/* --- BLOC 6 --- */
/* Force the multi-node mode to use the LENGTH column */
proc casutil incaslib="caspg" outcaslib="caspg" ;
   load casdata="film" casout="&sysuserid._PG_film" options=(sliceColumn="length") replace ;
   list tables ;
quit ;


/* --- BLOC 7 --- */
/* Display SAS Data Connector trace files */
%displayTrace(dclogpath=&dclogpath,logprefix=&logprefix) ;

/* Delete the SAS Data Connector trace files */
%deleteTraceFiles(dclogpath=&dclogpath,logprefix=&logprefix) ;


/* --- BLOC 8 --- */
/* Force the multi-node mode to use slicing expressions on the LENGTH column */
proc casutil incaslib="caspg" outcaslib="caspg" ;
   load casdata="film" casout="&sysuserid._PG_film" options=(numreadnodes=2 sliceExpressions=("length < 100","length >= 100")) replace copies=0 ;
   list tables ;
quit ;


/* --- BLOC 9 --- */
/* Display SAS Data Connector trace files */
%displayTrace(dclogpath=&dclogpath,logprefix=&logprefix) ;

/* Delete the SAS Data Connector trace files */
%deleteTraceFiles(dclogpath=&dclogpath,logprefix=&logprefix) ;


/* --- BLOC 10 --- */
cas mysession terminate ;