TPT_ADDITIONAL _EXAMPLES

DDL IMPLEMENTATION IN TPT

-------------------------------------------
/*
.LOGON 127.0.0.1/DBC,DBC;
DATABASE VINAYTECH;
CREATE TABLE EMP(EID INTEGER,ENM VARCHAR(30))PRIMARY INDEX (EID);
INSERT INTO EMP(1,'XXX');
INSERT INTO EMP(2,'YYY');
.QUIT;
.LOGOFF;
*/
DEFINE JOB CREATE_TABLE
(
 DEFINE OPERATOR DDL_OPERATOR
 DESCRIPTION 'TPT DDL'
 TYPE DDL
 ATTRIBUTES
 (
  VARCHAR TDpID='127.0.0.1',
  VARCHAR USERNAME='DBC',
  VARCHAR USERPASSWORD='DBC'
 );
 APPLY

('CREATE TABLE VINAYTECH.EMPS(EID INTEGER,ENM VARCHAR(30));'),
('INSERT INTO VINAYTECH.EMPS(1,''XXX'');'),
('INSERT INTO VINAYTECH.EMPS(2,''YYYY'');')
TO OPERATOR (DDL_OPERATOR());

);

MLOAD DELETE TASK OPERATION IN TPT

-----------------------------------------------------------------------------------------------
DEFINE JOB DELETE_TASK
DESCRIPTION 'Hard-coded DELETE FROM PARTY TABLE'
(
DEFINE OPERATOR UPDATE_OPERATOR
DESCRIPTION 'Teradata PT UPDATE OPERATOR'
TYPE UPDATE
SCHEMA *
ATTRIBUTES
(
VARCHAR TargetTable = 'vinaytech.PARTY121',
VARCHAR TdpId = @tdpid,
VARCHAR UserName = @unm,
VARCHAR UserPassword = @pwd,
VARCHAR AccountId,
VARCHAR LogTable = 'vinaytech.DeleteTask_log',
VARCHAR DeleteTask = 'Y'
);
APPLY
(
'DELETE FROM vinaytech.party121 WHERE tid LT 1000;'
)
TO OPERATOR( UPDATE_OPERATOR [1] );
);

MLOAD IMPLEMENTATION FOR Loading changes for table vinaytech.Claims

-------------------------------------------------------------------------------------------------------------------

DEFINE JOB Claims_TPT_JOB
DESCRIPTION 'Loading changes for table vinaytech.Claims'

(

DEFINE SCHEMA Claims_LOAD_SCHEMA

(

IN_F1   VARCHAR(1),

IN_F2   VARCHAR(100),

IN_F3   VARCHAR(100),

IN_F4   VARCHAR(100),

IN_F5   VARCHAR(100),

IN_F6   VARCHAR(100)

);

DEFINE OPERATOR CHANGE_DATA_FILE_READER

TYPE DATACONNECTOR PRODUCER
SCHEMA Claims_LOAD_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'Claims_reader_log',

VARCHAR FileName = @sourcefile,

VARCHAR Format = 'Delimited',

VARCHAR OpenMode = 'Read',

VARCHAR TextDelimiter = ',',

VARCHAR AcceptMissingColumns = 'Yes'

);

DEFINE OPERATOR TARGET_TABLE_UPDATER

TYPE UPDATE
 SCHEMA *
ATTRIBUTES

(

INTEGER MaxSessions = 4,

VARCHAR TargetTable = 'vinaytech.Claims',

VARCHAR TdpId = '127.0.0.1',

VARCHAR UserName = 'dbc',

VARCHAR UserPassword = 'dbc',

VARCHAR LogTable = 'vinaytech.Claims_LG',

VARCHAR PrivateLogName = 'update_log',

VARCHAR WorkTable = 'vinaytech.Claims_WK',

VARCHAR ErrorTable1 = 'vinaytech.Claims_ET',

VARCHAR ErrorTable2 = 'vinaytech.Claims_UV'

);

DEFINE OPERATOR DROP_ERROR_TABLES

TYPE DDL

ATTRIBUTES

(

VARCHAR PrivateLogName = 'Claims_drop_error_tables_log',

VARCHAR TdpId = '127.0.0.1',

VARCHAR UserName = 'dbc',

VARCHAR UserPassword = 'dbc',

VARCHAR ARRAY ErrorList = ['3807','2580']

);

STEP DROP_ERROR_TABLES

(

APPLY

(
'DROP TABLE vinaytech.Claims_ET;'
),

('DROP TABLE vinaytech.Claims_WK;'),

('DROP TABLE vinaytech.Claims_UV;'),

('DROP TABLE vinaytech.Claims_ET;'),

('DROP TABLE vinaytech.Claims_LG'),

('RELEASE MLOAD vinaytech.Claims;')

TO OPERATOR (DROP_ERROR_TABLES);

);

/* end of STEP DROP_ERROR_TABLES */

STEP UPDATE_TABLE

(

APPLY

CASE
   
WHEN ( IN_F1='I' OR IN_F1='A' or IN_F1='G')
THEN ('INSERT INTO vinaytech.Claims (CLAIM_NO,CLAIM_STATUS,CLAIM_TYPE,CLAIMEE,LOCATION)VALUES (:IN_F2,:IN_F3,:IN_F4,:IN_F5,:IN_F6);')

END

TO OPERATOR (TARGET_TABLE_UPDATER[1])
 

SELECT * FROM OPERATOR (CHANGE_DATA_FILE_READER[1]);
);

 

 );
/* end of STEP UPDATE_TABLE */

 

 

 

 

CREATE A JOBFILE LIKE BELOW

-----------------------------------------------

sourcefile='C:\TPT\TPT_NEW\CLAIMS_USA_CANADA_2.TXT'
unm='dbc'
pwd='dbc'
tdpid='127.0.0.1'

 

 

INPUT FILE

----------------

A,1001,ACTIVE,ACCIDENTAL,Mr.JOSEPH,USA
I,1002,ACTIVE,ILLNESS,Mr.DAVID,CANADA
D,1003,ACTIVE,ACCIDENTAL,Mrs.OASIS,USA
I,1005,ACTIVE,ILLNESS,Mr.RITCH,USA
D,1004,INNNNACTIVE,GENERAL,Mrs.PHILLIPINE,USA
D,1006,IN ACTIVE,ACCIDENTAL,Mr.JOSEPH,CANADA

 

 

RUN THE ABOVE TPT SCRIPT BY USING THE JOBFILE

 

--------------------------------------------------------------------------------------------------------------------------

USING FASTLOAD TO MOVE FILE DATA TO A TABLE

------------------------------------------------------------------------------------------------------------------------

DEFINE JOB EMP_LOAD
(
 DEFINE SCHEMA SC
 (
 EID VARCHAR(30),
 ENM VARCHAR(30)
 );
 DEFINE OPERATOR DC
 TYPE DATACONNECTOR PRODUCER
 SCHEMA SC
 ATTRIBUTES
 (
  VARCHAR FILENAME='C:\TPT\TPT_NEW\EMP_DATA.TXT',
  VARCHAR OPENMODE ='READ',
  VARCHAR FORMAT='DELIMITED',
  VARCHAR TEXTDELIMITER =','
 );
 DEFINE OPERATOR LD
 TYPE LOAD
 SCHEMA *
 ATTRIBUTES
 (
 VARCHAR LOGTABLE ='VINAYTECH.KLK',
 VARCHAR TDPID='127.0.0.1',
 VARCHAR USERNAME='DBC',
 VARCHAR USERPASSWORD='DBC',
 VARCHAR TARGETTABLE='VINAYTECH.EMPS'
 ); 

 APPLY
 ('INSERT INTO VINAYTECH.EMPS(:EID,:ENM);')
 TO OPERATOR (LD[2])
 SELECT * FROM OPERATOR (DC[2]);

);

 

 

 

EMP DATA

---------------

666,six
777,seven