Sample Utility Scripts

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 MESSAGES WITH ECHO TO FILE 'LoaderLog' ;
.dateform ansidate ;
.RUN FILE logon_file ( .LOGON ipaddress/logon,password;)

.logtable log_Table ;
DROP TABLE work_table;
DROP TABLE error table ;
DROP TABLE uv_table;
DELETE FROM stg_mtg_solicithist_dmce;
.begin import mload tables stg_mtg_solicithist_dmce
worktables  work_table
errortables error table
     uv_table
errlimit 0
checkpoint 100000
tenacity 10000
sessions 10
sleep 6
;
.layout InputFileLayout;
.field field1               * varchar(100) ;
.field field2                * varchar(100) ;
.field field3                   * varchar(100) ;

.dml Label tagDML ;
insert into stg_mtg_solicithist_dmce
(
field1              
,field2               
,field3                  
 
)
values
(
:field1              
,:field2               
,:field3                  
         
) ;
.import infile 'input_file_location'
layout InputFileLayout
 Format vartext' '
 Apply tagDML
;
.end mload ;
.logoff ;

Comments

Post a Comment

Popular posts from this blog

The Secret behind A B I N I T I O.

Background on Abinitio : Abinitio is considered one of the major ETL players  in line with informatica ,datastage ,SSIS ( if it can be considered major ETL player ;-) ) .In fact, in most respects it is considered better than all of the above . It has user interface and debugging simpler than Informatica  ( one of the positive points about informatica ) ,It has parallelism which data stage may still take years to reach . Superiority : (HearSay ) It has the ability to convert a 6 people ,6 month ETL project to 2 month 4 people ETL project . (Sounds hyperbole ,But yet it has been established in many cases that it takes half the development time compared to INFA with much better quality of code ) . Other statistics suggest that if you call Abinitio ,there is 75% chance you will be talking to a PhD holder.In other words it is considered to be ingenously simple. Secrecy : In spite of all this there is very little you would know about it (If at all ) . Reason : It is so because Abi
Read more

Finding Skewed Tables in Teradata

       Skewed Tables in Teradata                                                                  Teradata distributes its data for a given table based on its Primary Index and Primary Index alone .If this Primary Index is not selected appropriately it can cause performance bottle necks. Following are general parameters we should consider while creating Primary Indexes       1:Access Path       2:Volatility       3: Data Distribution        Volatility is usually not an issue in a well designed database .That is to say we do not expect update clauses updating the primary index itself. This usually leaves us with data distribution and Access Path.       Access Path implies particular column ( set of columns ) are always used in join conditions .Advantage of using these columns as PI is that it will avoid redistribution of data during join .( One of the most expensive operation for teradata ) and therefore can reduce usage of Spool files.        Data distribution implies
Read more

Macro to generate Mload Script

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
Read more