Friday 26 February 2016

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';
 

Tuesday 23 February 2016

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
 


 
 

Sunday 14 February 2016

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