Teradata TPT Scripts

SCRIPT1: (LOADING DATA FROM TABLE TO TABLE USING FASTLOAD)
===========================================================
DEFINE JOB VINAYAKA_fload_JOB  
(  
 
DEFINE SCHEMA SC_EOP  
(  
 TID INTEGER, 
 TNM VARCHAR(30) 
);  
  
DEFINE OPERATOR EOP
TYPE EXPORT  
SCHEMA SC_EOP
ATTRIBUTES  
(  
 VARCHAR UserName,  
 VARCHAR UserPassword,  
 VARCHAR SelectStmt,  
 VARCHAR TdpId  
);
DEFINE OPERATOR LOP
TYPE LOAD
SCHEMA *  
ATTRIBUTES  
(  
 VARCHAR UserName,  
 VARCHAR UserPassword,  
 VARCHAR LogTable,  
 VARCHAR TargetTable,  
 VARCHAR TdpId
);  
  
  
APPLY  
 ( 
  'INSERT INTO VINAY.TEST (TID,TNM) VALUES (:TID,:TNM);'
 ) 
TO OPERATOR  
(  
 LOP
  
 ATTRIBUTES 
 ( 
  UserName = @unm,
  UserPassword = @pwd,
  LogTable = 'VINAY.TEST_log_2',
  TargetTable = 'VINAY.TEST',
  TdpId = @tdpid
 ) 
)  
SELECT * FROM OPERATOR  
(  
 EOP
  
 ATTRIBUTES 
 ( 
  UserName = @unm,
  UserPassword = @pwd,
  SelectStmt = 'SELECT TID * 100,TNM FROM VINAYtech_db.TEST;',
  TdpId = @tdpid
 ) 
);  
);  
SCRIPT2: (LOADING DATA FROM TABLE TO TABLE USING MLOAD)
=========================================================
DEFINE JOB VINAYAKA_fload_JOB  
(  
 
DEFINE SCHEMA SC_EOP  
(  
 TID INTEGER, 
 TNM VARCHAR(30) 
);  
  
DEFINE OPERATOR EOP
TYPE EXPORT  
SCHEMA SC_EOP
ATTRIBUTES  
(  
 VARCHAR UserName,  
 VARCHAR UserPassword,  
 VARCHAR SelectStmt,  
 VARCHAR TdpId  
);
DEFINE OPERATOR LOP
TYPE UPDATE
SCHEMA *  
ATTRIBUTES  
(  
 VARCHAR UserName,  
 VARCHAR UserPassword,  
 VARCHAR LogTable,  
 VARCHAR TargetTable,  
 VARCHAR TdpId
);  
  
  
APPLY  
 ( 
  'INSERT INTO VINAY.TEST (TID,TNM) VALUES (:TID,:TNM);'
 ) 
TO OPERATOR  
(  
 LOP
  
 ATTRIBUTES 
 ( 
  UserName = @unm,
  UserPassword = @pwd,
  LogTable = 'VINAY.TEST_log_2',
  TargetTable = 'VINAY.TEST',
  TdpId = @tdpid
 ) 
)  
SELECT * FROM OPERATOR  
(  
 EOP
  
 ATTRIBUTES 
 ( 
  UserName = @unm,
  UserPassword = @pwd,
  SelectStmt = 'SELECT TID * 100,TNM FROM VINAYtech_db.TEST ;',
  TdpId = @tdpid
 ) 
);  
); 


CREATE A JOB FILE LIKE BELOW

JOB_VAR.TXT

   unm='dbc'
   pwd ='dbc'
   TdpId='127.0.0.1'

Run the above script using Job script like below
=====================================
tbuild -f <scriptpath> -v <jobfilepath>


SCRIPT1: (LOADING DATA FROM TABLE TO TABLE USING TPUMP)
=======================================================
DEFINE JOB VINAYAKA_fload_JOB  
(  
 
DEFINE SCHEMA SC_EOP  
(  
 TID INTEGER, 
 TNM VARCHAR(30) 
);  
  
DEFINE OPERATOR EOP
TYPE EXPORT  
SCHEMA SC_EOP
ATTRIBUTES  
(  
 VARCHAR UserName,  
 VARCHAR UserPassword,  
 VARCHAR SelectStmt,  
 VARCHAR TdpId  
);
DEFINE OPERATOR LOP
TYPE STREAM
SCHEMA *  
ATTRIBUTES  
(  
 VARCHAR UserName,  
 VARCHAR UserPassword,  
 VARCHAR LogTable,  
 VARCHAR TargetTable,  
 VARCHAR TdpId
);  
  
  
APPLY  
 ( 
  'INSERT INTO VINAY.TEST (TID,TNM) VALUES (:TID,:TNM);'
 ) 
TO OPERATOR  
(  
 LOP
  
 ATTRIBUTES 
 ( 
  UserName = @unm,
  UserPassword = @pwd,
  LogTable = 'VINAY.TEST_log_2',
  TargetTable = 'VINAY.TEST',
  TdpId = @tdpid
 ) 
)  
SELECT * FROM OPERATOR  
(  
 EOP
  
 ATTRIBUTES 
 ( 
  UserName = @unm,
  UserPassword = @pwd,
  SelectStmt = 'SELECT TID * 100,TNM FROM VINAYtech_db.TEST ;',
  TdpId = @tdpid
 ) 
);  
);  
SCRIPT1: (LOADING DATA FROM TABLE TO TABLE and passing user interaction values)
=======================================================================
DEFINE JOB VINAYAKA_fload_JOB  
(  
 
DEFINE SCHEMA SC_EOP  
(  
 TID INTEGER, 
 TNM VARCHAR(30) 
);  
  
DEFINE OPERATOR EOP
TYPE EXPORT  
SCHEMA SC_EOP
ATTRIBUTES  
(  
 VARCHAR UserName,  
 VARCHAR UserPassword,  
 VARCHAR SelectStmt,  
 VARCHAR TdpId  
);
DEFINE OPERATOR LOP
TYPE LOAD
SCHEMA *  
ATTRIBUTES  
(  
 VARCHAR UserName,  
 VARCHAR UserPassword,  
 VARCHAR LogTable,  
 VARCHAR TargetTable,  
 VARCHAR TdpId
);  
  
  
APPLY  
 ( 
  'INSERT INTO VINAY.TEST (TID,TNM) VALUES (:TID,:TNM);'
 ) 
TO OPERATOR  
(  
 LOP
  
 ATTRIBUTES 
 ( 
  UserName = @unm,
  UserPassword = @pwd,
  LogTable = 'VINAY.TEST_log_2',
  TargetTable = 'VINAY.TEST',
  TdpId = @tdpid
 ) 
)  
SELECT * FROM OPERATOR  
(  
 EOP
  
 ATTRIBUTES 
 ( 
  UserName = @unm,
  UserPassword = @pwd,
  SelectStmt = 'SELECT TID * 100,TNM FROM VINAYtech_db.TEST where tid='||@eid||';',
  TdpId = @tdpid
 ) 
);  
);  


Run the above script using the below navigation:
=====================================
tbuild -f <scriptpath> -u "eid='2'"  -v <jobfile path>


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.EMPS2(EID INTEGER,ENM VARCHAR(30));'),
('INSERT INTO VINAYTECH.EMPS2(1,''XXX'');'),
('INSERT INTO VINAYTECH.EMPS2(2,''YYYY'');')
TO OPERATOR (DDL_OPERATOR());

);

MLOAD DELETE  TASK IMPLEMENTATION 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] );
);

LOADING FILE DATA TO A TABLE USING FASTLOAD
===============================================
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.EMPS2'
 );
 APPLY
 ('INSERT INTO VINAYTECH.EMPS2(:EID,:ENM);')
 TO OPERATOR (LD[2])
 SELECT * FROM OPERATOR (DC[2]);

);


MLOAD ADDITIONAL EXAMPLE WITH FLAGS
========================================
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);')

WHEN ( IN_F1='D')

THEN ('INSERT INTO vinaytech.Claims (CLAIM_NO,CLAIM_STATUS,CLAIM_TYPE,CLAIMEE,LOCATION)VALUES (:IN_F2,:IN_F3,:IN_F4,:IN_F5,:IN_F6);')

WHEN ( IN_F1='T' )
      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 */



****************THANK YOU FOR WATCHING TILL END**************************







 

TERADATA RESUME CONTENT & TERADATA RESUME PREPARATION

 

====================================================================

RESUME PREPERATION PROCESS                                                                                     

====================================================================

1. READ AND USE THE ABOVE CONTENT FORMAT

2. OBSERVE THE BELOW PROFESSIONAL SUMMARY

AND INDIVIDUAL PROJECT RESPONSIBILITIES

3. REFER TO THE RESUMES GIVEN AS SOFT COPY

DOCUMENTS AT THE INSTITUTION

(HOW MUCH YOU OBSERVE THAT MUCH YOU WILL GAIN)

4. PLACE ALL THE IMPORTANT POINTS AND INTERESTING

POINTS  IN THE RESUME FORMAT GIVEN ABOVE.

5. GET THAT MONITORED BY THE MENTOR

 

TERADATA_SAMPLE_TABLES_FOR_PRACTICE

PARTY TABLE
==============
create table party(partyid integer,
partyname varchar(30),partycode integer,partyincome
integer,jdate date format 'YYYY-MM-DD',partyloc varchar(30));
insert into party(1,'vinay',10,40000,'2008-09-09','hyd');
insert into party(2,'vishnu',20,50000,'2008-08-09','mum');
insert into party(3,'LEELA',30,10000,'2008-07-08','che');
insert into party(4,'rakesh',40,40000,'2008-06-13','hyd');
insert into party(5,'rajesh',10,30000,'2008-09-18','mum');
insert into party(6,'  raman   ',20,80000,'2008-08-07','che');
insert into party(7,'hdfc',30,60000,'2008-10-10','hyd');
insert into party(8,'%icici',10,50000,'2008-11-11','mum');
insert into party(9,null,10,50000,'2008-11-11','che');

TEST TABLE
============
CREATE TABLE TEST(PARTYID INTEGER,
PARTYNAME VARCHAR(30),
TESTID VARCHAR(30))
--SEL * FROM PARTY;
INSERT INTO TEST(1,'VINAY','HYD');
INSERT INTO TEST(2,'VISHNU','BLORE');
INSERT INTO TEST(22,'VIKAS','BLORE');

EMP TABLE
===============
create table emp(eid integer,ename varchar(30),
mgrid integer,deptid integer);
insert into emp(1,'vinay',2,10);
insert into emp(2,'ramki',3,20);
insert into emp(3,'raju',1,30);
insert into emp(4,'krishna',2,20);
insert into emp(5,'mukesh',2,10);

EMP_NEW TABLE
===================
create table emp_NEW(eid integer,ename varchar(30),
mgrid integer,deptid integer);
insert into emp_NEW(1,'vinay',2,10);
insert into emp_NEW(2,'ramki',3,20);
create table dept(deptid integer,deptname varchar(30));
insert into dept(10,'IT');
INSERT INTO DEPT(30,'HR');

EMP_ADDRESS
===============
create table emp_ADDRESS(empid integer,empname varchar(30),
ADDRESS VARCHAR(30));
insert into emp_ADDRESS(1,'vinay','motinagar hyd');
insert into emp_address(2,'ramki','gachibowli hyd');
insert into emp_address(8,'murali','kukatpally hyd');