ALTER COMMAND SYNTXES AND EXAMPLES

ALTER COMMAND syntaxes:----------------------
ADDING COLUMN:
SYNTAX: ALTER TABLE <TABLENAME> ADD <COLUMNNAME> <DATATYPE>
 EG: ALTER TABLE PARTY ADD JDATE DATE

DROPING COLUMN:
SYNTAX: ALTER TABLE <TABLENAME> DROP <COLUMNNAME>
 EG: ALTER TABLE PARTY DROP JDATE

INCREASING DATATYPE LENGH:
SYNTAX:ALTER TABLE <TABLENAME> ADD COLUMNNAME DATATYPE<NEWLENGTH>
EG: ALTER TABLE PARTY ADD PARTYNAME VARCHAR(50)

ADDING CONSTRAINT:
SYNTAX: ALTER TABLE <TABLENAME> ADD CONSTRAINT <CONSTRAINTAME> PRIMARY/UNIQUE/CHECK
/REFERENCES(COLUMNS OR EXPRESSION)
 EG:ALTER TABLE PARTY ADD CONSTRAINT CHK_SAL CHECK (PARTYSAL>100000)
Note: to add primary key or unique the column should have not null defined on it.
      EG:   ALTER TABLE PARTY ADD CONSTRAINT uk  UNIQUE(PARTYNAME)

MODIFYING CONSTRAINT:
SYNTAX: ALTER TABLE <TABLENAME> MODIFY CONSTRAINT <CONSTRAINTAME> PRIMARY/UNIQUE
/CHECK/REFERENCES
 EG:ALTER TABLE PARTY MODIFY CONSTRAINT CHK_SAL
 CHECK (PARTYSAL>100000 AND PARTYSAL<200000)

 DROPING CONSTRAINT:
 SYNTAX:ALTER TABLE <TABLENAME> DROP CONSTRAINT <CONSTRAINTNAME>
 EG: ALTER TABLE PARTY DROP CONSTRAINT CHK_SAL

NO FALLBACK TO FALLBACK:
 SYNTAX: ALTER TABLE <TABLENAME>,FALLBACK
 EG: ALTER TABLE PARTY,FALLBACK

 CHANGING DATA BLOCKSIZE:
 SYNTAX:ALTER TABLE <TABLENAME>, DATABLOCKSIZE=<MEMORY>BYTES IMMEDIATE
 EG: ALTER TABLE <TABLENAME>, DATABLOCKSIZE=1200 BYTES IMMEDIATE

 RENAMING THE COLUMN:
 SYNTAX: ALTER TABLE <TABLENAME> RENAME <OLDNAME>TO <NEWNAME>
  EG: ALTER TABLE PARTY RENAME PARTYCD TO PARTYCODE

RENAMING A TABLE:
SYN:  RENAME TABLE <OLDTABLE> TO <NEW TABLE>
EG: RENAME TABLE PARTY TO PARTY_NEW

ADDING COMPRESS:
ALTER TABLE PARTY ADD PARTYCODE COMPRESS NULL;
ALTER TABLE PARTY ADD ACCOUNTTYPE COMPRESS 'sAVINGS';
ALTER TABLE PARTY ADD ACCOUNTTYPE COMPRESS 'sAVINGS','Current';
 

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

 

 

 

TERADATA JOIN STRATEGIES, JOIN & HASH INDEXES AND THEIR EXAMPLES

 

JOIN STRATEGIES,MERGE & PRODUCT JOIN SCREEN SHOTS(FIRST THREE)

 CONFIDENCE LEVELS ON SINGLE OR MULTIPLE TABLES

JOIN INDEX AND HASH INDEX

INDICES OBSERVATION IN A PARTICULAR DATABASE AND TABLE
-----------------------------------------------------------
SEL * FROM DBC.INDICES WHERE DATABASENAME='VINAYAKA' AND TABLENAME='DEPT'
BEFORE JOIN INDEXES
---------------------
EXPLAIN SEL EID,ENAME,DEPTID FROM EMP WHERE DEPTID=20--ALL AMP OPERATION
EXPLAIN
SELECT E.EID,E.ENAME,E.DEPTID FROM EMP E
INNER JOIN DEPT D
ON E.DEPTID=D.DEPTID --ROW DISTRIBUTION,SORTING  AND MERGE JOIN STRATEGY
AFTER JOIN INDEXES
==================
DROP JOIN INDEX  IDX;
SYN:
CREATE JOIN INDEX <INDEXNAME> AS <SEL QUERY> PRIMARY INDEX(COLS)
EX:
CREATE JOIN INDEX IDX  AS SEL EID,ENAME,DEPTID FROM EMP PRIMARY INDEX(DEPTID);
SHOW JOIN INDEX IDX;
NOTE: IF REQUIRED COLLECT STATS ON THE TABLES
   --COLLECT STATS ON EMP INDEX(EID)
   --COLLECT STATS ON DEPT INDEX(DEPTID)
EXPLAIN
SELECT E.EID,E.ENAME,E.DEPTID FROM EMP E
INNER JOIN DEPT D
ON E.DEPTID=D.DEPTID --NO ROW DISTRIBUTION,MERGE JOIN
EXPLAIN SEL EID,ENAME,DEPTID FROM EMP WHERE DEPTID=20 --SINGLE AMP OPERATION
 

TERADATA LOADERS(UTILITIES) USAGE IN INFORMATICA