From Database to MPP to Data Lake

Fexp : .ROUTE MESSAGES WITH ECHO TO FILE 'log_file' ;   .DATEFORM ANSIDATE;   .RUN FILE logon_file .LOGTABLE tabnam; .BEGIN EXPORT    TENACITY 4   SESSIONS 5   SLEEP 6;   .EXPORT OUTFILE 'outfile name and locations' MODE RECORD FORMAT TEXT; 'SQL Query .You can concatenate all fields and then cast them to particular length to avoid Junk Characters ' ) X ; .END EXPORT;      .LOGOFF; -- Fast load dateform ANSIDATE; errlimit 1000000; tenacity 4;        sessions 10; sleep 6; .LOGON ipaddress/logon,password; DROP TABLE .ET_tab1 ; DROP TABLE .UV_tab2; DELETE FROM STG_tab3; SET RECORD VARTEXT "|"; define  Field1 (VARCHAR(10))  Field2(VARCHAR(18))  Field3 (VARCHAR(20))  file=:CF.ImportFileName; show; begin loading STG_tab3 errorfiles ET_tab1, .UV_Tab2 checkpoint 25000000 ; insert into STG_tab3 ( Filed1, Field2, Field3 ) VALUES (  :Field1,  :Filed2,  :Filed3 ) ; end loading; logoff; -- MLOAD .ROUTE MESSA

clclbteq << eof > /odi47831/Scripts/Del_App_Work_Tables.txt.log .SET ECHOREQ ON .SET RETRY ON .SET TIMEMSG DEFAULT .SET ERRORLEVEL 3807 SEVERITY 0; .run file logon.txt .set width 200 .set titledashes off .set format off .set rtitle '' .export report file=del_app_work.run SEL 'DEL FROM ' ||Trim(Databasename)||'.'||Trim(Tablename)||';' (Title '') FROM DBC.TABLES WHERE CREATORNAME='xyz' AND TABLEKIND='T' ; .Export report file=del_app_work.sql .run file = del_app_work.run .logoff .quit

DROP MACRO MLOAD_EXPORT REPLACE MACRO MLOAD_EXPORT ( DB VARCHAR(100), TB VARCHAR(100), PATH VARCHAR(100), FS VARCHAR(100) ) AS ( SEL 1,CAST(1 AS BIGINT), CAST('.DATEFORM ANSIDATE; .LOGON server/username,password; .LOGTABLE USR_WORK.ML_UMS_' || :TB || '_1; DROP TABLE USR_WORK.WT_UMS_' || :TB || '_1; DROP TABLE USR_WORK.ET_UMS_' || :TB || '_1; DROP TABLE USR_WORK.UV_UMS_' || :TB || '_1; .ROUTE MESSAGES WITH ECHO TO FILE ''' || :PATH || '.ldrlog'' ; .BEGIN IMPORT MLOAD TABLES ' || :DB || '.' || :TB || ' WORKTABLES USR_WORK.WT_UMS_' || :TB || '_1 ERRORTABLES USR_WORK.ET_UMS_' || :TB || '_1 USR_WORK.UV_UMS_' || :TB || '_1 ERRLIMIT          0 CHECKPOINT     100000 TENACITY 10000 SESSIONS        10 SLEEP 6 ; .LAYOUT InputFileLayout; ' AS VARCHAR(6000)) FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=DATE UNION SEL 2,CAST(COLUMNID AS BIGINT), CAST('.FI

REPLACE MACRO .BTEQ_EXPORT ( DATABASENAME VARCHAR(100), TABLENAME VARCHAR(100), FILENAME VARCHAR(500), SERVER VARCHAR(100) default '' , USERNAME VARCHAR(300) default '', PASS VARCHAR(15) default 'password' ) AS ( SEL '                      .SESSIONS 5  .SET ECHOREQ ON .SET FORMAT OFF .SET QUIET ON .SET WIDTH 1000 .SET RETLIMIT 1000000 .SET RETCANCEL ON .LOGON  '   || :SERVER || '/' || :USERNAME || ', ' || :PASS || '; '  ||     ' .EXPORT REPORT FILE =' || :FILENAME BTEQ_SCRIPT     FROM SYS_CALENDAR.CALENDAR WHERE DATE=CALENDAR_DATE       UNION   SEL '                   SEL TOP 1000000            '   FROM SYS_CALENDAR.CALENDAR WHERE DATE=CALENDAR_DATE     UNION     SEL   '                 TRIM(COALESCE (CAST(' || COLUMNNAME ||  'AS VARCHAR(300)),' ||'27'xc || '27'xc || ')) ||  ' ||'27'xc ||'|' || '27'xc || '||'