TeraData Interview Questions and Answers

teradata-interview-questions-answers

&nbsp;
<h2 style="text-align: justify;">Teradata DBA Interview Questions</h2>
<h3 style="text-align: justify;">Q1) What is the difference between FastLoad and MultiLoad?</h3>
Ans: FastLoad uses multiple sessions to quickly load large amounts of data on an empty table. MultiLoad is used for high-volume maintenance on tables and views. It works with non-empty tables also. A maximum of 5 tables can be used in MultiLoad.
<h3 style="text-align: justify;">Q2) What is the difference between inner join and outer join?</h3>
Ans: 
<ul>
<li style="text-align: justify;">An inner join gets data from both tables where the specified data exists in both tables.</li>
<li style="text-align: justify;">An outer join gets data from the source table at all times and returns data from the outer joined table ONLY if it matches the criteria.</li>
</ul>
<h3 style="text-align: justify;">Q3) What Type Of Indexing Mechanism Do We Need To Use For A Typical Data Warehouse?</h3>
Ans: The Primary Index mechanism is the ideal type of index for the data warehouse.
<h3 style="text-align: justify;">Q4) How Do You Determine The Number Of Sessions?</h3>
Ans: 
<ul>
<li style="text-align: justify;">Teradata performance and workload.</li>
<li style="text-align: justify;">Client platform type, performance, and workload.</li>
<li style="text-align: justify;">Channel performance for channel attached systems.</li>
<li style="text-align: justify;">Network topology and performance for network-attached systems</li>
<li style="text-align: justify;">The volume of data to be processed by the application.</li>
</ul>
<h3 style="text-align: justify;">Q5) What is OLAP?</h3>
Ans: Online Analytical Processing, a category of software tools that provides an analysis of data stored in a database. OLAP tools enable users to analyze different dimensions of multidimensional data. For example, it provides a time series and trend analysis views. The chief component of OLAP is the OLAP server, which sits between a client and a database management system (DBMS). The OLAP server understands how data is organized in the database and has special functions analyzing the data.
<blockquote>
Inclined to build a profession as Teradata DBA Developer? Then here is the blog post on, explore <a title="Teradata DBA Training" href="teradata-dba-training" target="_blank" rel="noopener">Teradata DBA Training</a>
</blockquote>
<h3 style="text-align: justify;">Q6) What Are The Things To Be Considered While Creating a Secondary Index?</h3>
Ans: Creating a secondary index causes Teradata to build a sub-table to contain its index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or updated. Secondary index sub-tables are also duplicated whenever a table is defined with FALLBACK, so the maintenance overhead is effectively doubled.
<h3 style="text-align: justify;">Q7) What are the roles and responsibilities of Teradata DBA?</h3>
Ans: 
<ul>
<li style="text-align: justify;">User Management &ndash; Creation and managing Users, Databases, Roles, Profiles, and Accounts.</li>
<li style="text-align: justify;">Space Allocation &ndash; Assigning Permanent Space, Spool Space, and Temporary Space.</li>
<li style="text-align: justify;">Access of Database Objects &ndash; Granting and Revoking Access Rights on different database objects.</li>
<li style="text-align: justify;">Security Control &ndash; Handling login and logoff rules for Users.</li>
<li style="text-align: justify;">System Maintenance &ndash; Specification of system defaults, restart, etc.</li>
<li style="text-align: justify;">System Performance &ndash; Use of Performance Monitor(PMON), Priority Scheduler, and Job Scheduling.</li>
<li style="text-align: justify;">Resource Monitoring &ndash; Database Query Log(DBQL) and Access Logging.</li>
<li style="text-align: justify;">Data Archives, Restores, and Recovery &ndash; ARC Utility and Permanent Journals.</li>
</ul>
<h3 style="text-align: justify;">Q8) What are Access Module Processors?</h3>
Ans: The AMP vproc manages Teradata Database interactions with the disk subsystem. Each AMP manages a share of the disk storage.
<h3 style="text-align: justify;">Q9) How do you transfer large amounts of data in Teradata?</h3>
Ans:
<ul>
<li style="text-align: justify;">Transferring large amounts of data can be done using various Application Teradata Utilities which resides on the host computer ( Mainframe or Workstation) i.e. BTEQ, FastLaod, MultiLoad, Tpump, and FastExport.</li>
<li style="text-align: justify;">BTEQ (Basic Teradata Query) supports all 4 DMLs: SELECT, INSERT, UPDATE, and DELETE.</li>
<li style="text-align: justify;">BTEQ also supports IMPORT/EXPORT protocols.</li>
<li style="text-align: justify;">Fastload, MultiLoad, and Tpump transfer the data from Host to Teradata.</li>
<li style="text-align: justify;">FastExport is used to export data from Teradata to the Host.</li>
</ul>
<h3 style="text-align: justify;">Q10) What are TPUMP Utility Limitations?</h3>
Ans: Following are the limitations of Teradata TPUMP Utility:
<ul>
<li style="text-align: justify;">The use of the SELECT statement is not allowed.</li>
<li style="text-align: justify;">Concatenation of Data Files is not supported.</li>
<li style="text-align: justify;">Exponential &amp; Aggregate Operators are not allowed.</li>
<li style="text-align: justify;">Arithmetic functions are not supported.</li>
</ul>
<h3 style="text-align: justify;">Q11) How do you create a table with an existing structure of another table with data and with no data?</h3>
Ans: Create table Customer dummy as Customer with data / with no data.
<h3 style="text-align: justify;">Q12) While creating a table my DBA has FALLBACK or NO FALLBACK in his DDL. What is that?</h3>
Ans: FALLBACK requests that a second copy of each row inserted into a table be stored on another AMP in the same cluster. This is done when AMP goes down or disk fails.
<h3 style="text-align: justify;">Q13) What Is The Purpose Of Indexes?</h3>
Ans: An index is a mechanism that can be used by the <a href="sql-tutorials" target="_blank" rel="noopener">SQL</a> query optimizer to make table access more performant. Indexes enhance data access by providing a more or less direct path to stored data and avoiding the necessity to perform full table scans to locate the small number of rows you typically want to retrieve or update.
<h3 style="text-align: justify;">Q14) What is the opening step in the Teradata Query script?</h3>
Ans: .Logon tdipid/username, password.
<h3 style="text-align: justify;">Q15) What&rsquo;s the syntax of substring?</h3>
Ans: SUBSTRING (string_expression, n1 [n2])
<h3 style="text-align: justify;">Q16) Did you use the CASE WHEN statement? Can you tell us a little about it?</h3>
Ans: Yes. When a case has to be selected depending upon the value of the expression.

TOP 15+ Teradata DBA Interview Questions - Updated 2022

Teradata DBA Interview Questions

interview-questions-on-teradata-dba

<h2 style="text-align: justify;">Interview Questions On Teradata</h2>
What is Teradata?
Teradata is one of the popular Relational Database Management System. It is mainly suitable for building large scale data warehousing applications. Teradata achieves this by the concept of parallelism. It is developed by the company called Teradata.
Define&nbsp;Teradata RDBMS?
Teradata RDBMS is a complete relational database management system. The system is based on off-the-shelf Symmetric Multiprocessing (SMP) technology combined with a communication network connecting the SMP systems to form a Massively Parallel Processing (MMP) system.
What are&nbsp;BTEQ COMMAND?
BTEQ Command Classified in the below 4 types:
a)Session control commands
ABORT, EXTI, Halt EXECUTION, LOGON, LOGOFF, QUIT, Session, charset, session transaction, show versions, etc.
b)File formatting commands
ERROR OUT, EXPORT, HALT EXECUTION, IMPORT, INDIA DATA, OS, QUIET, Repeat, run, etc.
c)Sequence control commands
Abort, error level, Exit, GO TO, quite, IF Then, Label, etc
d)Format control commands
Side titles, fold line, Footing, Heading, etc.
<pre style="text-align: center;">Aspired to become an Teradata? 
Explore the post to discover the know-hows on <a href="https://tekslate.com/teradata-training/">Teradata Trainin</a>g.</pre>
Why is Aggregation expensive in a parallel DB ?
In order to aggregate records, they have to be on the same processing unit
This requires a redistribution of the records according to the &ldquo;Group by&rdquo; clause
Aggregation is more expensive than a Join, because more records have to be processed, normally, and a mathematic operation (sum, avg, min, max) is performed additionally
Each VAMP aggregates his local records first
Then the intermediate results are redistributed using Hash for the final aggregation
Each AMP performs a local count, then executes a share of the global count
Which Is Faster Fastload Or Multiload?
FastLoad
How Do You See A Ddl For An Existing Table?
By using show table command.
What Is Data Mart?
A data mart is a special purpose subset of enterprise data used by a particular department, function or application. Data marts may have both summary and details data, however, usually the data has been pre aggregated or transformed in some way to better handle the particular type of requests of a specific user community. Data marts are categorized as independent, logical and dependant data marts.

Teradata Interview Questions And Answers

interview-questions-on-teradata

&nbsp;
<h2>The following are the critical Teradata Components </h2>
PE BYNET AMP VSS 5.V DISK &nbsp;
<h3>AMP </h3>
AMP is vproc. vproc is nothing But a collection of instructions, which performs a specific operation(or)Task
<h3>PE[Passing Engine] &nbsp;</h3>
<img class="alignnone wp-image-9579" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_826-460x685.png" alt="Passing Engine" width="689" height="1028" />
<h3>Passing Engine &nbsp;</h3>
It takes SQL Request and delivery SQL response &nbsp;
<h3>Session Control </h3>
<ul>
<li>The session is nothing but a logical connection between user and Application.</li>
<li>Passing Engine checks the authentication and authorization, if it&rsquo;s valid, it does log in otherwise log off</li>
</ul>
<h3>Parser</h3>
<ul>
<li>It checks syntactical Error</li>
<li>It checks symmetrical Error</li>
<li>It checks the Existence of an object</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>Query Rewrite Mechanism</h3>
It writes the Query such away optimizer can understand easily &nbsp;
Example: &nbsp;
IF The Query is Q7, it converts into Q2 &nbsp;
<h2>Optimizer</h2>
It is an important component in any database including Teradata It provides an execution plan for SQL Statement so that it is going to be executed by the database Generally it usages the below information.
<ul>
<li>System configuration</li>
<li>Available parallel unit</li>
<li>Data Demographics, etc</li>
<li>Access plan</li>
<li>Join plan</li>
</ul>
<h3>Generator</h3>
It generated steps for the plan provided
<h3>Dispatched</h3>
It performs and manager request and response, flow control(Taking Request, Response keeping in Queue &nbsp;in delivery)
Note: &nbsp;
Passing Engine Handle max 120 sessions at a time
<h3> BYNET[BANYAN NETWORK]:</h3>
It acts as a&rdquo; Message communication layer&rdquo; between various components. &nbsp;
<h3>SMP BYNET [PE-AMP]</h3>
POINT - POINT-One message from PE To one AMP
MULTI CAST-One message from PE to Many AMPs
BROAD CAST- One message from PE to All AMPs &nbsp;
<h3>MPP BYNET[NODE-NODE]:</h3>
POINT- POINT-One message from One NODE to other NODE
MULTI CAST-One message from One NODE to Many NODE
BROAD CAST- One message from One NODE to All NODE &nbsp;
<h3>AMP[Access Module Processor]</h3>
1. Each AMP is responsible for managing a portion of MAIN DISK SPACE (VIRTUAL DISK)
2. This space not sharable by any other AMPs so we call architecture as shared-nothing architecture.
3. Each AMP Operator independently resources
4. Each AMP contains a database management subsystem. If perform the below operations.
<ul>
<li>Performing DDL</li>
<li>Performing DML</li>
<li>Performing Joins</li>
<li>Performing Applying and Releasing locks</li>
<li>Performing Aggregate operation</li>
<li>Performing OLAP operation</li>
<li>Performing SORTING Operation etc.,</li>
</ul>
Note:
Max so task AMP TO perform at a time
Multiple AMP runs independently so high parallelism implement &nbsp;
<h3>VSS(Virtual Storage System):</h3>
<ul>
<li>It is available from Teradata 13 onwards, basically, design to manage a multi-tier warehouse</li>
<li>Teradata VSS Pools all of the cylinders within CLIQUE&rsquo;s DISK SPACE and allocates cylinders from the storage pool.</li>
<li>It migrates frequently used data (Hot data) to FASTER DEVICES and less frequently used data (cold data)to SLOWER DEVICES. &nbsp;</li>
</ul>
<h3>Before Teradata VSS</h3>
a) AMPS know the physical location of the cylinder which are address by DRIVE# (OR) CYLINDER#
b) Adjusting system AMPS integral number of drives per AMP
c) Adding storage requires an additional drive per AMP. &nbsp;
<h3>After TDV SS</h3>
a) AMPs don&rsquo;t know the physical location of the cylinder and it can be changed, because of the cylinder in CLIQUE&rsquo;s On effective in the pool, that are managed by Teradata virtual storage, virtual process.
b) Added drives are shared by all AMP&rsquo;s
c) You can add several drives, This new Drive may have different capacity are performance than those drives, which are already present in the system
<h3>Before Teradata Virtual storage</h3>
&nbsp; <img class="alignnone wp-image-9580" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_920-460x255.png" alt="Teradata" width="452" height="250" />
&nbsp;
<h3>After &nbsp;Teradata Virtual storage</h3>
&nbsp; <img class="alignnone wp-image-9581" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1023-460x271.png" alt="Teradata" width="498" height="292" />
&nbsp;
<h2>Virtual Disk (or)VDISK &nbsp;</h2>
&nbsp;
<img class="alignnone wp-image-9582" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1119-460x508.png" alt="Virtual Disk" width="449" height="497" />
Collection of physical DISK (OR) Group of cylinder Arranged in Array position is called VDISK(or) virtual DISK Traditionally this called as DISK Array (or) Array of DISK After AMP performance operation, it invokes the controller this store and manages the data across physical disk This controller active array controller, the number of cylinders in an Array is called a RANK in the above Diagram the Rank is 3.(N-1)=4-1=3
<ul>
<li>CLI-CALL LEVEL INTERFACE</li>
<li>TDP-TERA DATA DIRECTORY PROGRAM</li>
<li>MTDP-Micro Tera data Directory program</li>
<li>MOSI- Micro Operating system Independent</li>
<li>CA-Channel Application</li>
</ul>
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<img class="alignnone wp-image-9583" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1214-460x510.png" alt="Teradata components" width="549" height="610" /> &nbsp;
For connection to the Teradata database system, we need to connect through either CHANNEL ATTACHED SYSTEM OR NETWORK ATTACHED SYSTEM &nbsp;
<h3>TDP(Teradata Directory Program)</h3>
<ul>
<li>If check the authentication is successful, it creates a session and Logs on</li>
<li>It balances multiple session [Create multiple session] &nbsp;</li>
</ul>
<h3>MTDP(Micro Tera data Directory Program)</h3>
It just like TDP cannot balances to sessions &nbsp;
<h3>CLI (CALL LEVEL Interface) </h3>
It is a low-level interface for request, response Blocking, and un- Blocking. &nbsp;
<h3>MOSI (Micro operating system Independent)</h3>
It checks the platform Independence of the operating system.
<h2>For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
<li><a href="http://tekslate.com/multiple-cliques-system-high-level-teradata/" target="_blank" rel="noopener">Multiple Cliques In System (OR) High Level In TeraData</a></li>
</ul>
&nbsp;

What is Teradata | Teradata Components - Tekslate

Teradata Components

teradata-components

Types of OLAP’s in TeraData - Tekslate

Types of OLAP’s in TeraData

types-olaps-teradata

&nbsp;
<h2 style="text-align: justify;">Memory Management</h2>
<h3 style="text-align: justify;">a) Permanent Memory</h3>
All tables, procedures, generals, indexes, sub tables, etc Required permanent memory
Views, Macro, <a href="http://tekslate.com/15-sample-triggers-different-scenarios/" target="_blank" rel="noopener">Trigger</a> doesn&rsquo;t require any permanent memory
A database can be created without permanent memory]
<h3 style="text-align: justify;">b) Spool Memory</h3>
i) It stores intermediates result and calculation(Select statement result, join operation data, etc)
ii) View, macros, derived tables, etc, store under spool memory
<h3 style="text-align: justify;">C)Temporary Memory</h3>
i) It holds intermediate result and calculation[generally session-level information]
ii)global temporary tables store under temporary memory
Note-&nbsp;The un-used permanent can be taken as spool(or) temper are memory
There is a database with 100GB of memory, 60 GB Assign to&nbsp; spool, 40GB Assign to permanent, 20 GB OF Permanent data stored, How much spool memory available for business operations.
<img class="alignnone size-full wp-image-9643" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_309.png" alt="spools" width="295" height="159" />
Spools = Assigned spool+ un- used permanent
=60+20
=80GB
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3 style="text-align: justify;">Current memory</h3>
Currently, occupied memory is the current memory
<h3 style="text-align: justify;">Peak memory</h3>
The maximum occupied of memory earlier is peak memory
<h3 style="text-align: justify;">Max memory</h3>
Max memory assigned(earlier maximum memory) to the object.
<h2 style="text-align: justify;">Creating a database</h2>
1)A database is a passive repository where it stores all database objects
2) Until you create an object, it is empty
3)Database doesn&rsquo;t contain any passwords
Example:
&nbsp;create database&lt;database name&gt;FROM
&lt;Parent database&gt;
As
Permanent=&lt;Memory space&gt;,
Spool=&lt; Memory space &gt;,
Temporary=&lt; Memory space &gt;,
[No]FALL BACK
Before/After journal,
Default journal table=&lt;DATABASE.TABLE NAME&gt;;//FOR
Permanent journal.
<h3 style="text-align: justify;">Dropping database/user</h3>
Syn: DROP database&lt;database/USER&gt;;
EG:DROP database MEERA;
<h3 style="text-align: justify;">Modifying database/user</h3>
Modifying database/user&lt;database/USER;&gt;
PERMANENT/Spool/TEMPORARY=&lt;MEMORY SPACE&gt;
Example:
Create database db-DEVELOPMENT
From DBC
AS
PERMANENT=2000000,
Spool=2000000,
TEMPORARY=2000000,
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />SELECT*FROM DBC.databaseS
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />CREATE TABLE DB- DEVELOPMENT. TEST[PARTY ID INTEGER, PARTY NAME VARCHAR(30)]
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />INSERT INTO DB- DEVELOPMENT.TEST(1,&rsquo;Meera&rsquo;)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Select *FROM DB- DEVELOPMENT.TEST
<h3 style="text-align: justify;">Creating a user</h3>
A user inactivates the repository (data dictionary), where it stores all database objects.
Until you create an object, the user is an empty
User contains a password whereas the database doesn&rsquo;t contain any Password
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Create USER- DEVELOPMENT
FROM DBC
AS
PERMANENT=2000000,
Spool =2000000,
TEMPORARY=2000000,
PASSWORD= VINAY;
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Select * FROM DBC.USERS
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Create a table user- DEVELOPMENT. Test(Party ID Integer, party name varchar(30))
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Insert INTO USR &ndash; DEVELOPMENT.TEST(&lsquo;VINAY&rsquo;)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />SEL* FROM USE- DEVELOPMENT. Test
<h2 style="text-align: justify;">DIFFERENCE BETWEEN database AND USER</h2>
<table style="height: 294px;" width="664">
<tbody>
<tr>
<td width="246">database</td>
<td width="337">USER</td>
</tr>
<tr>
<td width="246">1.Passive repository[NO Change in request resource]</td>
<td width="337">1.Active repository[changes in the number of request and responses]</td>
</tr>
<tr>
<td width="246">2.Doesnot contain the password</td>
<td width="337">2.Contain password</td>
</tr>
<tr>
<td width="246">3.To work with database we require user support</td>
<td width="337">3.By using this we can do any tool in tera data.</td>
</tr>
</tbody>
</table>
Note:-
In real-time for individual number(or) group administrator create
user id and password.
Creating an <a href="https://en.wikipedia.org/wiki/Open_Database_Connectivity" target="_blank" rel="noopener">ODBC DRIVER</a> with the above user
Open data sources
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Questions" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<img class="alignnone wp-image-9644" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_3114-460x280.png" alt="Teradata - system DNS" width="432" height="262" />
<img class="alignnone wp-image-9645" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_3212-460x318.png" alt="SODBC Driver" width="445" height="307" />
<h3 style="text-align: justify;">Connecting to query man by using this driver </h3>
<img class="alignnone wp-image-9646" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_3310-460x170.png" alt="Teradata" width="434" height="159" />
Now we are in the above userspace
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Connecting other databases (or)use They are 2 ways
By prefixing database name(or)USER name before the object
Syn:-&lt;database/user name&gt;&lt;object name&gt;
Example:- &nbsp;SQL*FROM VINAYAKA. PARTY
database Table
By takes database name(or)User name as a current database name[Multiple queries we can execute without prefixing database(or)user name]
Syn:-&lt;database/ database /USER name&gt;
Example:- &nbsp;&nbsp;Database VINAYAKA;
Select *From party;
Select *From party;
Select *From VIEW-party;
<h2>For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>

Memory Management in TeraData - Tekslate

Memory Management in TeraData

memory-management-teradata

&nbsp;
<h2 style="text-align: justify;">Navigation</h2>
a) Start run
b) Start programs Teradata administrator Select TD Admin<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />click ok
i)Database creation:- click<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />yellow box
ii)Creating user click
iii)privileges
<img class="alignnone wp-image-9655" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_349-460x350.png" alt="Teradata Administrator" width="522" height="397" />
<h3 style="text-align: justify;">Teradata SQL</h3>
Sql<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> IBM
PL/Sql<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> <a href="tutorialsoracle-application-framework-oaf-tutorials" target="_blank" rel="noopener">ORACLE</a>
T-Sql<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /><a href="http://tekslate.com/sql-server-integration-in-ssis/" target="_blank" rel="noopener">SQL Server</a>
Tera data sql <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Tera data
<ul>
<li style="text-align: justify;">SQL is 4th Generation language</li>
<li style="text-align: justify;">Fully based on set theory</li>
<li style="text-align: justify;">If contains a set of other languages for its operations</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
a) DDL -&nbsp;Data definition language
USE-For creating, ALTERING and REMOVING Objects
Commands-Creating, ALTERING, DROP
Note- There is no TRUNCATE COMMAND[delete entire at a time in oracle]
b) DML - Date Manipulation language
Use-&nbsp;For Manipulation objects data[By selecting, inserting, deleting, etc]
COMMAND-&nbsp;SELECT, INSERT,UPDATE, DELETE,MERGE
c) DCL - Date Control language
Use-&nbsp;For providing and revoking privileges for objects
COMMAND-&nbsp;GRANT, REVOKE, GIVE
SPL- Stored procedure language
Use: Creating and calling a stored procedure as procedures require concept c (or) c++ compilers we call this language as SPL
<h3 style="text-align: justify;">Teradata Extensions to SQL</h3>
<ul>
<li style="text-align: justify;">Help</li>
<li style="text-align: justify;">Show</li>
<li style="text-align: justify;">Explain</li>
<li style="text-align: justify;">Collect statistics</li>
</ul>
<h2 style="text-align: justify;">Show and Help commands</h2>
Show commands- It shows the <a href="https://en.wikipedia.org/wiki/Data_definition_language" target="_blank" rel="noopener">DDL</a> of the object
Syntax- Show object&lt;object name&gt;
Example- Show table&lt;table name&gt;
Show view&lt;view name &gt;
Show macro&lt; macro name &gt; etc&hellip;
Help command- It describes the objects by getting from the data dictionary
Syntax:- Help object&lt;object name&gt;
Help table&lt; table name&gt;
Help view &lt; view name&gt;
Help macro &lt; macro name&gt;
Help Procedure &lt; Procedure name&gt;
Help session To see session information etc..,
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Questions" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h2 style="text-align: justify;">Teradata objects</h2>
a) Table
b)Views
c)Macros
d)<a href="http://tekslate.com/15-sample-triggers-different-scenarios/" target="_blank" rel="noopener">Triggers</a>
e)Cursory
f)Procedure[Normal procedure(or) embedded (or) extended procedure]
<h3 style="text-align: justify;">CREATING Table</h3>
Create [SET/MULTISET][GLOBABL TEMPORARY/VOLATILE]
TABLE&lt; TABLE&nbsp; NAME&gt;
&lt;DATA OPTIONS&gt;
&lt;COLUMN DEFINITIONS&gt;
&lt;CONSTRAINTS&gt;
&lt;INDEXES&gt;
SET TABLE- It does not allow complete row duplicate
FREE SPACE- The percentage of free memory Assigned from the cylindrical memory
MULTISET- It allows complete row duplicate
DATA BLOCK SIZE-The maximum data block size specified to the table at the time input, output of file system
DATA OPTIONS- FALL BACK, NO FALL BACK BEFORE JOURNAL, NO BEFORE JOURNAL AFTER JOURNAL, NO AFTER JOURNAL
<img class="alignnone size-full wp-image-9656" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_358.png" alt="Data block size" width="261" height="88" />
<h2 style="text-align: justify;">For In-depth knowledge on Teradata click on:</h2>
<ul style="text-align: justify;">
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>

Teradata Administrator Tool | User Guide - Tekslate

Teradata Administrator Tool

teradata-administrator-tool

teradata Data Recovery & Data Protection

Data Recovery And Protection in Teradata

data-recovery-and-protection-in-teradata

&nbsp;
<h3 style="text-align: justify;">What is a clique?</h3>
<ul>
<li style="text-align: justify;">A clique is a set of Teradata nodes that share a common set of disk arrays that are connected in a daisy chain network to each disk array controller.</li>
<li style="text-align: justify;">Clique is mainly used to prevent node failures. A group of nodes that share common disk drives is called CLIQUE.</li>
<li style="text-align: justify;">A Group of cylindrical or physical disks arranged in array fashion is called VDISKS.</li>
<li style="text-align: justify;">After AMP performing the operations, it invokes the controllers to store and manage the data across physical disks.</li>
<li style="text-align: justify;">Here the two controllers are active and they do the same operation so we call these controllers as DUAL REDUNDANT ACTIVE ARRAY CONTROLLERS.</li>
</ul>
<h3 style="text-align: justify;">What is meant by AMP in Teradata?</h3>
AMP, an acronym for "Access Module Processor," is the type of vproc (Virtual Processor) used to manage the database, handle file tasks and manipulate the disk subsystem in the multi-tasking and possibly parallel-processing environment of the Teradata Database. AMP contains Database Management Subsystem and it performs the below operations:
<ul>
<li style="text-align: justify;">DDL</li>
<li style="text-align: justify;">DML</li>
<li style="text-align: justify;">Applying and releasing the locks</li>
<li style="text-align: justify;">Joins, aggregations, etc</li>
</ul>
<div id="nmtbi8" class="_qhf _grf nmiw nmtbi nmt nmtbia " style="text-align: justify;" data-hveid="67" data-ved="0ahUKEwjDo_6bgPjQAhXELY8KHZSbDHkQuk4IQygA">
<div class="_eHi">
<h3 class="_rhf">What is a Vdisk?</h3>
</div>
</div>
<div id="nmtbi8cntw" class="nmcw nmbl nmca" style="text-align: justify;" data-hveid="68" data-ved="0ahUKEwjDo_6bgPjQAhXELY8KHZSbDHkQu04IRCgB">
<div id="nmtbi8cnt">
<div class="_Zjj">
<div class="mod" data-md="61">
<div class="_oDd" data-hveid="69">A logical disk, logical volume, or virtual disk (VD or vdisk for short) is a virtual device that provides an area of usable storage capacity on one or more physical disk drive(s) in a computer system.</div>
<div class="_oDd" data-hveid="69">&nbsp;</div>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<div class="_oDd" data-hveid="69">
<div id="nmtbi10" class="_qhf _grf nmiw nmtbi nmt nmtbia " data-hveid="75" data-ved="0ahUKEwjDo_6bgPjQAhXELY8KHZSbDHkQuk4ISygA">
<div class="_eHi">
<h3 class="_rhf">What is the Teradata database?</h3>
</div>
</div>
<div id="nmtbi10cntw" class="nmcw nmbl nmca" data-hveid="76" data-ved="0ahUKEwjDo_6bgPjQAhXELY8KHZSbDHkQu04ITCgB">
<div id="nmtbi10cnt">
<div class="_Zjj">
<div class="mod" data-md="61">
<div class="_oDd" data-hveid="77">Teradata is a relational database management system (<a href="http://tekslate.com/sql-rdbms-concepts/" target="_blank" rel="noopener">RDBMS</a>) that is: Teradata is&nbsp;an open system, running on a <a href="http://tekslate.com/tutorials/unixlinux/" target="_blank" rel="noopener">UNIX</a> MP-RAS or Windows server platform. Teradata is capable of supporting many concurrent users from various client platforms. Teradata is compatible with industry standards (ANSI compliant).</div>
<div class="_oDd" data-hveid="77">&nbsp;</div>
</div>
</div>
</div>
</div>
</div>
<div class="_oDd" data-hveid="69">&nbsp;</div>
<div class="_oDd" data-hveid="69">Cliques provide data accessibility if a node fails for any reason,&nbsp;&nbsp;Proms are distributed across all nodes in the system. Large multiple node systems will have clique mechanisms associated with them</div>
</div>
</div>
</div>
</div>
In the event of a node failure, cliques provide for data access through vproc migration. When a node resets, the following happens to the AMPs:
<ul style="text-align: justify;">
<li>When the node fails, the Teradata Database restarts across all remaining nodes in the system.</li>
<li>The vprocs (AMPs) from the failed node migrate to the operational nodes in its clique.</li>
<li>The PE vprocs will migrate as follows: LAN attached PEs will migrate to other nodes in the clique. Channel attached PEs will not migrate. While that node remains down, that channel connection is not available.</li>
<li>Disks managed by the AMP remain available and processing continues while the failed node is being repaired.</li>
</ul>
<ul style="text-align: justify;">
<li>Cliques in a System</li>
</ul>
Vprocs are distributed across all nodes in the system. Multiple cliques in the system should have the same number of nodes.
The diagram below shows three cliques. The nodes in each clique are cabled to the same disk arrays. The overall system is connected by the BYNET. If one node goes down in a clique the vprocs will migrate to the other nodes in the clique, so data remains available. However, system performance decreases due to the loss of a node. System performance degradation is proportional to the clique size.
<img class="aligncenter wp-image-27827 size-full" title="Teradata - 3 cliques" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/clique.png" alt="Teradata - 3 cliques" width="419" height="339" />
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h2 style="text-align: justify;">BYNET (Banyan Network)</h2>
It acts as the "Message Communication" layer between components.
<ol>
<li>SMP BYNET [PE-AMP]</li>
</ol>
POINT-POINT: One msg from PE - One Amp
MULTICAST: One msg from PE- Many Amps
BROADCAST: One msg from PE - All Amps
2. MPP BYNET [NODE-NODE]
POINT-POINT: One msg from One Node to Another Node
MULTICAST: One msg from One Node to Many Node
BROADCAST: One msg from One Node to All Nodes
<h2>MULTIPLE CLIQUES IN SYSTEM (OR) HIGH LEVEL</h2>
<img class="aligncenter wp-image-9698" title="Teradata - Multiple Cliques In System" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_467-460x324.png" alt="Teradata - Multiple Cliques In System" width="687" height="483" />
<h3 style="text-align: justify;">INSIDE CLIQUE PROCESS(OR)SINGLE CLIQUE:-</h3>
<img class="aligncenter wp-image-9699" title="Teradata - Inside Clique Process" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_477-460x355.png" alt="Teradata - Inside Clique Process" width="574" height="442" />
If Node, fails
<img class="aligncenter wp-image-9700" title="Teradata - Node fails" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_489-460x342.png" alt="Teradata - Node fails" width="586" height="435" />
<div style="text-align: justify;">Every multi-system environment will have multiple cliques, each clique contains an equal number of nodes. <a href="https://en.wikipedia.org/wiki/Clique" target="_blank" rel="noopener">Clique</a> is nothing but group of nodes sharing common disk drives here the advantage is if any node goes down the virtual process migrate to other nodes to perform the operation.in this way, it provides high availability of data even node goes down.</div>
<h3 style="text-align: justify;">HSN (HOT STAND by NODE)</h3>
<ul>
<li style="text-align: justify;">This node is not functional till any other node goes down, simple acts like an extra player. In a clique, multiple nodes are accessible by any node in the same clique. The facing page shows a situation where a node fails. When the node fails PDE resets and Teradata restarts and the amp vprocs migrate to the other nodes in the clique.</li>
<li style="text-align: justify;">A massively parallel processing system will continue to run if a node or disk fails in the clique, even if you do not select the fallback option in the situation shown the TDP will restart the lost amp vprocs on different nodes within the clique. When the node is brought back to its original node for 7x24 systems, the fallback option is recommended for minimizing the risks of the system down time.</li>
<li>Applications that recognize restarts and are coded to will continue when the system comes back up otherwise, applications will need to be restarted.</li>
</ul>
<div style="text-align: justify;">&nbsp;</div>
<h2 style="text-align: justify;">For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a title="Teradata Wallet" href="teradata-wallet" target="_blank" rel="noopener">Teradata Wallet</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
<li><a href="http://tekslate.com/tera-data-parallel-transportertpt/" target="_blank" rel="noopener">Teradata Parallel Transporter(TPT)</a></li>
<li><a title="TeraData Interview Questions and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">TeraData Interview Questions and Answers</a></li>
</ul>
&nbsp;

Multiple Cliques In System | High Level In TeraData

Multiple Cliques In System (OR) High Level In TeraData

multiple-cliques-system-high-level-teradata

&nbsp;
<h2 style="text-align: justify;">Constraints</h2>
Constraints are conditions prototype rules and applying on tables or columns in other database constraints are
(a)Domain-Based &ndash; Not null x
Check &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; These are attribute of Teradata
Default
b) Entity-based &ndash; primary key[pk]
- unique key[UK]
c) Reference based-foreign key [FK]
<h3 style="text-align: justify;">Teradata Constraints</h3>
Unique &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> unique values (allows one null)
Primary &nbsp;&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;unique values + Not null+ No duplicate values
Check &nbsp;&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> validates against a condition
References &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Works like foreign key
Constraints &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;(Condition or restriction on the column's data)
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3 style="text-align: justify;">Constraints applied at two levels</h3>
a) Column Level&nbsp; On a single column we take constraint
b)Table level&nbsp;&nbsp;On multiple columns(Composite key) we take constraints.
Syntax: Column Name data type constraint &lt;Constraint name&gt;
Primary key/unique/check/references.
Example:
Create set table dept
(
D Code integer primary key Not null,
D name varchar(30)
D income integer constraint CHK
Check(DINcome&gt;30000)
)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Create set table emp
(
EID integer unique not null,
Ename varchar(30)
D Code integer constraint ref
References Dept(D code)
)
<h3 style="text-align: justify;">Table-level constraints</h3>
Create set table dept1
(
Party ID integer
Pname varchar(30) not null,
P DOB not null, primary key(Pname, PDOB)
)
Note: In the above situation p name, PDOB is used for unique identification of the record
<img class="alignnone wp-image-9705" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_497-460x309.png" alt="Table level constraints" width="554" height="371" />
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h3 style="text-align: justify;">Multiple column levels(composite)</h3>
<table>
<tbody>
<tr>
<td width="117">
PID
</td>
<td width="117">
Pname
</td>
</tr>
<tr>
<td width="117">
1
</td>
<td width="117">
X
</td>
</tr>
<tr>
<td width="117">
2
</td>
<td width="117">
X
</td>
</tr>
<tr>
<td width="117">
1
</td>
<td width="117">
Y
</td>
</tr>
<tr>
<td width="117">
2
</td>
<td width="117">
X
</td>
</tr>
<tr>
<td width="117">
3
</td>
<td width="117">
X
</td>
</tr>
</tbody>
</table>
<h3 style="text-align: justify;"> Soft referential integrity</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />We can remove the data in the parent even the value existed in the child
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Implemented with no check option
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Referential integrity
<img class="alignnone size-full wp-image-9706" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_506.png" alt="Referential Integrity" width="239" height="153" />
Example
Create set table emp
(
EID Integer unique, not null,
Ename varchar(30),
DCode integer constraint ref
References with no check option dept(Dcode)
)
Note
P, K UK required not null at the column level because they are associated with the index concept.
<h2 style="text-align: justify;">For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>
<div class="tags">&nbsp;</div>

Constraints in Teradata

Constraints in TeraData

constraints-in-teradata

&nbsp;
<h2>Indexes</h2>
<img class="alignnone wp-image-9712" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_5211-460x768.png" alt="Indexes" width="523" height="877" />
<h2>Primary Index</h2>
<ul>
<li>Mandatory index</li>
<li>Only one PI for table</li>
<li>We can take a P.I till 64 columns[API is composite till 64 columns]</li>
<li>It is a physical mechanism to assign a row to amp</li>
<li>It affects a data distribution</li>
<li>OPI takes only unique values</li>
<li>NUPI takes duplicate and null values</li>
<li>PPI for range base query</li>
<li>If don&rsquo;t specify any query system creates PI by default</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>Real-Time Advantages</h3>
<ul>
<li>For better distribution (or) uniform distribution</li>
<li>For better access plan</li>
<li>For better retrieval</li>
<li>For better join plan</li>
<li>As it is a single Amp operation performs it is operating faster etc. &nbsp;</li>
</ul>
<h3>Creating Alternating Droping</h3>
<ul>
<li>We cannot drop P. I [it we want to drop PI. The table first and then change the PI]</li>
<li>The table is empty we can alter the PI</li>
<li>The table is having data we cannot alter PI &nbsp;</li>
</ul>
<h3>PI Usage </h3>
<ul>
<li>Generally, we take in the below situation</li>
<li>Frequently used column</li>
<li>Frequently used no- indexed column in the where clause&nbsp;</li>
</ul>
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Questions" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h3>No primary index in where clause</h3>
i) If you are not able to decide and which columns
ii) We go for PI than create No PI, So that no distribution happens
iii) Whenever we fell comfortable then we convert NOPI
IV) Available NOPI from tera data 13 onwards. &nbsp;
<h3>No PI real time usage</h3>
a) Loading data into stage tables
b)Using FL and T pump Array insert at the time FL and T pump array insert etc. &nbsp;
<h2>Hash Map</h2>
It is created at the time of tera data soft installation It&rsquo;s provides the relationship b/w Amp&rsquo;s address [basic number] And row host values it the no. of the same in the two system hash map it also be same
<h2>For Indepth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>
<div class="tags">&nbsp;</div>

Indexes in Teradata

indexes-in-teradata

&nbsp;
<h2>Hash Maps</h2>
<img class="wp-image-9715 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_539-460x372.png" alt="Hash Algorithm" width="486" height="392" /> &nbsp;
<h2>DSW (Distribution Selection Word)</h2>
&nbsp; <img class="wp-image-9716 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_547-460x202.png" alt="DSW" width="563" height="246" />
&nbsp;
&nbsp; <img class="wp-image-9717 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_554-460x382.png" alt="DSW" width="424" height="352" />
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
Hash Map provides a relationship between row hash value[DSW] and Amp address Primary index Physical Mechanism [Storage &amp; retrieval Party id mechanism]&nbsp; &nbsp; &nbsp;<img class="size-full wp-image-9718 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_566.png" alt="Hash Table" width="242" height="241" />
<img class="wp-image-9719 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_576-460x560.png" alt="Hash Map" width="754" height="919" />&nbsp;&nbsp;
The&nbsp; dates distribution in the Amp&rsquo;s and amp contains the data in rowed logical Sequence &nbsp;
<h3> Inside the Amp </h3>
Sel * From party where Pid=2
&nbsp; <img class="wp-image-9720 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_586-460x280.png" alt="Hashmap" width="557" height="338" />
<img class="wp-image-9721 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_596-460x182.png" alt="Master Index" width="531" height="209" />
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h2>Hash synonym(or) Hash collisions</h2>
<ul>
<li>Getting the same row hash values for the same (or) to PI value is called hash synonym(or) Hash collision.</li>
<li>In this situation to identify every row system takes a concept called rowed.</li>
<li>It is useful to identify every record unique in the amp.</li>
<li>It is taken by the amp only.</li>
<li>It contains 64 bits first 32 bits are row hash value, next 32 bits are u values.</li>
</ul>
&nbsp; <img class="size-full wp-image-9722 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_606.png" alt="Hash Synonym" width="448" height="541" />
<h2> For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>
<div class="tags">&nbsp;</div>
&nbsp;

Hash Maps in TeraData | TekSlate TeraData Tutorials

Hash Maps in TeraData

hash-map-teradata

&nbsp;
<h2 style="text-align: justify;">UPI[Unique primary index]</h2>
<ul>
<li style="text-align: justify;">The unique primary index takes only unique values and one null value</li>
<li style="text-align: justify;">It distributes data across all Amp&rsquo;s uniformly(or) randomly(or) evenly, so this distribution is called uniform distribution(or) event distribution</li>
<li style="text-align: justify;">*One Amp operation operates only one row.</li>
</ul>
<h2 style="text-align: justify;">Nupi[Non- unique primary index]</h2>
<ul>
<li style="text-align: justify;">It takes duplicates and nulls</li>
<li style="text-align: justify;">It&rsquo;s distribution data across Amp&rsquo;s proportional to the of uniqueness that means, it distributes less even distribution (or) unable distribution[duplicate row going to the sam Amp]</li>
<li style="text-align: justify;">This distribution can also be called as Skewed distortion and the less uniform less is &ldquo;Skewness&rdquo;</li>
<li style="text-align: justify;">*One Amp operation and affects many rows</li>
</ul>
<h2 style="text-align: justify;">PPI[Portioned primary Index]</h2>
<img class="wp-image-9725 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_6112-460x297.png" alt="PPI" width="569" height="366" />
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h2 style="text-align: justify;">NPPI[Non- partitioned primary Index]</h2>
<img class="size-full wp-image-9726 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_6210.png" alt="NPPI" width="431" height="303" />
<h3 style="text-align: justify;">Partitioned Primary Index on P code[PPI]</h3>
<img class="size-full wp-image-9727 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_637.png" alt="PPI" width="237" height="338" />
<ul>
<li style="text-align: justify;">Data partitioned based on pcode first</li>
<li style="text-align: justify;">Within the partitioned data stored on row hash sequence.</li>
<li style="text-align: justify;">We go for PPI generally when we using other then PI Columns use in the where clause along with PI frequency</li>
<li style="text-align: justify;">If other column range based column query than also it&rsquo;s recommended</li>
<li style="text-align: justify;">In the above select query, the system goes to the corresponding partitioned and skips the other partition so improves the performance.</li>
<li style="text-align: justify;">Generally, it is single Amp for some time, multi Amp operations also</li>
</ul>
<h4 style="text-align: justify;">Case-N</h4>
Based on &rsquo;the condition&rsquo; we create partitions
<h4 style="text-align: justify;">Range-N</h4>
Based on Range it creates partitions
Note:
no case:- Condition Unmatched data. Move into the no case partition
<h2 style="text-align: justify;">Creation and dropping</h2>
PI Created only at table create
<h3 style="text-align: justify;">UPI</h3>
Create table&lt;table name&gt;(&lt;columns&gt;) Unique primary index(column names);
<h3 style="text-align: justify;">NUPI</h3>
Create table&lt;Table name&gt;(&lt; columns &gt;) <a href="http://tekslate.com/indexes-in-teradata/" target="_blank" rel="noopener">primary index</a>(column names);
<h3 style="text-align: justify;">PPI</h3>
Create table&lt;table name&gt;(&lt;columns&gt;)primary index(columns) partition by column name;
<h3 style="text-align: justify;">UPI</h3>
Create set table test1
(
Party Id integer;
Party name varchar(30))
Unique primary index(party id);
Create a set table test
(
Party id integer,
Party name varchar(30))
Primary index(Party id);
<h3 style="text-align: justify;">PPI</h3>
Create set table test1
(
Party id integer,
Party name varchar(30),
Party code integer)
Primary index(Party id) partition by case- N
Party code=10, party code=20, no case, unknown);
Note:
Works only with NUPI values
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h4 style="text-align: justify;">Range-N</h4>
Based on the range of data as part of partitions
We go for it
Create set table test1
(
Party id integer
Party name varchar(30),
Party code integer,
Jdate date) Unique primary index
(Party ID, J date)partition by range &ndash; N(J date between
&lsquo;2010-01-01&rsquo;And&rsquo;2010-10-01&rsquo; Each interval&rsquo;1&rsquo; month)
Note:
<ul>
<li style="text-align: justify;">Works only with UPI Values</li>
<li style="text-align: justify;">MPPI</li>
</ul>
Note:
<ul>
<li style="text-align: justify;">Each level must have 2 partitions</li>
<li style="text-align: justify;">Max 15 Levels supports</li>
<li style="text-align: justify;">Total partitions 65,535</li>
<li style="text-align: justify;">NOPI(only in Teradata 13)</li>
</ul>
<h3 style="text-align: justify;">Advantages</h3>
We can take as stage tables to load data from the fast load(or) <a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP</a> Array insert
Create multiset table let(party id integer, party name, varchar(30))
No Primary index:
<img class="wp-image-9728 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_646-460x303.png" alt="Primary Index" width="559" height="367" />
<h2 style="text-align: justify;">For In-depth knowledge on Teradata click on:</h2>
<ul style="text-align: justify;">
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>

UPI in Teradata

UPI [Unique primary index] in TeraData

upi-unique-primary-index-teradata

&nbsp;
<h2>Secondary Index</h2>
<h3>Real-time usage </h3>
Other than primary index columns if we are using in the where clause, To get fast retrieval data to take the second index on the columns. &nbsp;
<h3>Features </h3>
<ul>
<li>It prevents full table scans</li>
<li>Basically for getting fast Retrieval of data, this is record mended</li>
<li>We can create and drop SI at any time (Creation can be at table creation/after table creation)</li>
<li>We can create Max 32, SI, size in a single table.</li>
<li>USI takes the only unique value</li>
<li>NSUI takes duplicate and nulls</li>
<li>For range-based data VOSI is useful</li>
<li>It doesn&rsquo;t affect data distribution.</li>
<li>It uses a sub-table mechanism as part of its operations</li>
<li>Each SI can be composite till 64 columns</li>
<li>It is two or many Amp operation.&nbsp;</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>S.I Sub table Mechanism[Storage and retrieval Architecture]</h3>
<img class="wp-image-9734 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_656-460x468.png" alt="Storage and Retrieval Architecture" width="753" height="768" /> &nbsp;
Sel* from a party where pcode=20; Party
<table>
<tbody>
<tr>
<td width="213">PID</td>
<td width="213">PNM</td>
<td width="213">PCODE</td>
</tr>
<tr>
<td width="213">1</td>
<td width="213">X</td>
<td width="213">100</td>
</tr>
<tr>
<td width="213">2</td>
<td width="213">Y</td>
<td width="213">20</td>
</tr>
<tr>
<td width="213">3</td>
<td width="213">Z</td>
<td width="213">33</td>
</tr>
<tr>
<td width="213">4</td>
<td width="213">K</td>
<td width="213">20</td>
</tr>
</tbody>
</table>
&nbsp; &nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Sub table content added and removed according to the main table content, which is a burden to the system.
&nbsp; &nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Sub table may in the same Amp(or) it may be indifferent Amp. &nbsp;
<table width="681">
<tbody>
<tr>
<td width="227">S.I ROW ID</td>
<td width="227">Column value</td>
<td width="227">P.I row ID</td>
</tr>
<tr>
<td width="227">&nbsp;</td>
<td width="227">202033100</td>
<td width="227">&nbsp;</td>
</tr>
</tbody>
</table>
Sel * from the party where P-Code between 20 and 33 &nbsp;
Note:
<ul>
<li>In the case of USI Sub table will be in other Amp</li>
<li>In the case of the NUSI Sub table will be in the same Amp. &nbsp;</li>
</ul>
<h2>Creation and dropping </h2>
<h3>AT table creation </h3>
USI:&nbsp;Create table&lt;table name&gt;unique index(column name)
NUSI:- Create table&lt;table name&gt; index(column name) &nbsp;
<h3>After table creation </h3>
USI: Create unique index idx- party CD(party code) on the party;
NUSI:- Create index idx- party CD(party code) on party &nbsp;
<h3>Value order NUSI</h3>
Create index (party code) order by values on the party; &nbsp;
<h3>Dropping indexes </h3>
Named index dropping: Drop index&lt;index name&gt;on&lt;table name&gt;
 Unnamed index dropping:&nbsp;Drop index(Column name)on&lt;table name&gt; &nbsp; &nbsp;
<h3>Final Example </h3>
Create set table party test fall back (party id integer, party name varchar(30)upper case, party income integer check(party income&gt;5000), party code integer) unique primary index(party id)unique index(party code)
<img class="wp-image-9736 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_675-460x237.png" alt="Difference between P.I. and S.I." width="727" height="373" /> &nbsp;
<h2>Difference between PI and SI&nbsp; </h2>
<h3>FAQ&rsquo;S</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />There is a table with primary key and a unique key is available what types of index the system create by default?
Ans:- As the primary key give high priority, it takes UPI on the primary key and USI on unique
a) UPI:- UPI on the primary key column and unique key column as USI
b)USI:- USI on the unique key column
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h3>Naming conversion And rules</h3>
a) As per BIET Names are limited to 30th character
b) A name can be a combination of A-Z, a-Z,$,#,-
c) Object Name is unique in tera data
USI
<ol>
<li>Takes unique values, generally sub table stoves in other Amp&rsquo;s</li>
</ol>
<h3>[Two Amp operations and returns only one row] VOSI&nbsp;</h3>
In this situation instated of secondary index row ID/row hash sequence, actual values will be ordered so
that this index is useful for range-based query operations. <img class="size-full wp-image-9735 aligncenter" style="display: block; margin-left: auto; margin-right: auto;" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_666.png" alt="VOSI" width="300" height="306" /> &nbsp; &nbsp;
<h3>Characters literal and Numeric literal </h3>
General we used select, insert, etc statement
Select
Party id,
Party name,
&lsquo;IT&rsquo; as dept<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Character Literal
40 as code<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Numeric literal
From party;
<table>
<tbody>
<tr>
<td width="160">Party id</td>
<td width="160">Party name</td>
<td width="160">dept</td>
<td width="160">Code</td>
</tr>
<tr>
<td width="160">1</td>
<td width="160">Vinay</td>
<td width="160">IT</td>
<td width="160">40</td>
</tr>
<tr>
<td width="160">2.</td>
<td width="160">Madhu</td>
<td width="160">IT</td>
<td width="160">40</td>
</tr>
<tr>
<td width="160">3.</td>
<td width="160">Meera</td>
<td width="160">IT</td>
<td width="160">40</td>
</tr>
</tbody>
</table>
<h2>For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
<li><a href="http://tekslate.com/tera-data-parallel-transportertpt/" target="_blank" rel="noopener">Teradata Parallel Transporter</a></li>
</ul>
<div class="tags">&nbsp;</div>

Secondary Index in Teradata

Secondary Index in TeraData

secondary-index-teradata

&nbsp;
<h3>Creating a Table From Another table</h3>
<ul>
<li>Create table&lt;data base name&gt;&lt;new table name&gt;As&lt;DB Nam&gt;&lt;OLD Table name&gt; with Data ---With data</li>
<li>Create table&lt;data base name&gt;.&lt;New table name&gt;As&lt;DB Name&gt;.&lt;OLD table name&gt; with no data ---- With no data with only structure</li>
</ul>
<h3>Updating table from Another table</h3>
Update test2 set PNM=&rdquo;dum2&rdquo; where pid= Update principle set principle. Phone= reception. Phone where principal picl=reception pid &nbsp;
<h3> Creating a table with &nbsp;Statistics From another table</h3>
Create table new as old with data and statists Create table new as old with no data and statists; Create table new as(select*from old)with data and statists &nbsp;
Note: To load only data from one table to another table go to &rdquo;Insert select&rdquo; &nbsp;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h2>FAQ&rsquo;S &nbsp; </h2>
<h3>Q1) Difference between the drop and delete</h3>
Ans:
<table>
<tbody>
<tr>
<td width="319">
Drop
</td>
<td width="319">
Delete
</td>
</tr>
<tr>
<td width="319">1.Drops the objects(it removes the space no structure data available)</td>
<td width="319">1) Delete only data</td>
</tr>
<tr>
<td width="319">
<ol start="2">
<li>DDL Command</li>
</ol>
</td>
<td width="319">2.DML Command Delete are worked like truncate</td>
</tr>
<tr>
<td width="319">3.It&rsquo;s drops any objects</td>
<td width="319">3.It&rsquo;s only table and views</td>
</tr>
<tr>
<td width="319">4.No rollback</td>
<td width="319">4.Roll back is there</td>
</tr>
<tr>
<td width="319">
<ol start="5">
<li>No transient journal overhead</li>
</ol>
</td>
<td width="319">5.Transcient journal overhead</td>
</tr>
<tr>
<td width="319">
6.Doesn't support where the condition
</td>
<td width="319">6.Support where condition</td>
</tr>
</tbody>
</table>
<h3>Q2) What is the difference between delete and delete all </h3>
Ans:- Delete all runs faster than normal delete, because below the reason
<ul>
<li>No Transient journal overhead</li>
<li>Works like a truncate command</li>
<li>&nbsp;No rollback</li>
<li>It works directly on the master and cylinder index.</li>
</ul>
<h3>Q3) Why insert select runs faster than normal insertion</h3>
 Ans:-
<img class="alignnone size-full wp-image-9743" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_707.png" alt="Insert" width="441" height="536" /> &nbsp; &nbsp;
<ul>
<li>With the same amp, the rows will be moved it runs faster.</li>
<li>Because same Amp operation &nbsp;</li>
</ul>
<h2>Revoke privilege </h2>
A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; B
X,y,z
m1,m2 Grant Insert on X to B
Grant select on X to B
Revoke Insert on X to B
Revoke select on X to B &nbsp;
<h3>Give</h3>
If runs for one database access rights to others, that means if the database having 50 users with different privileges &agrave; we can trans for all the 50 users and privileges and other databases. A(DB) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B(DB)
100 Tables <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />100 Tables <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Give my DB to finance;
50 users------- 50 users
&nbsp; <img class="alignnone wp-image-9744" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_7113-460x93.png" alt="Give" width="330" height="66" />
If transactor database access Give A to B
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Working with the select statement?
To retrieve the data from the database in the required format, in the required column, we use select statement &nbsp;
<h3>Syntax:</h3>
Select Columns/* From&lt;Table name&gt;
Where&lt;Condition&gt;or&lt;Sub query&gt;&agrave; filters select stat
Group by&lt;columns&gt;or&lt;Expression&gt;or&lt;Numeric&rsquo;s&gt;
Having&lt;Condition&gt;or&lt;Sub query&gt;&agrave; filter group data
Order by&lt;columns&gt;Ascending/descending (or) Numeric&rsquo;s &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a></li>
<li><a title="BTEQ Features in Teradata" href="bteq-features-teradata" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a title="Secondary Index in TeraData" href="secondary-index-teradata" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
</ul>

Performing curd operations using another table

Performing Curd Operations by using another table in TeraData

performing-curd-operations-using-another-table-teradata

&nbsp;
<h2>In SQL Server -2008 only</h2>
Delete &nbsp;from TT Where T.T.%% PHYSLOC%%
Not in(Select min(%%PHYSLOC%%)
from T.T group by EID) &nbsp;
Note: PHYSLOC: Physical location of row[Just like row id] &nbsp;
<h2>Tera data</h2>
<ul>
<li>Here we must use in the intermediate table to delete the duplicate, Because row id, row number does not work in tera data</li>
<li>We cannot we order analytical function such as Rank and Qualify Row- number &amp; Qualify to delete the duplicate</li>
</ul>
<h3>Steps</h3>
<ul>
<li>Take duplicate into an intermediate table(intermediate table should have a similar Structure to the main table)</li>
<li>Delete the main table.</li>
<li>Rename the intermediate table to the main table.</li>
</ul>
So tera data The below 2 ways are recommended
<ul>
<li>Case of complete row duplicate</li>
<li>Take a new table with the set option</li>
<li>load the duplicate table data, so unique ready loaded</li>
<li>Drop duplicate table and rename set table name to old table name</li>
<li>In case of the same column are duplicate</li>
</ul>
<ol style="list-style-type: lower-alpha;">
<li>Take a new table</li>
<li>Load the distinct resource group by into it so unique rows loaded</li>
<li>Drop duplicate table and rename new table name to old table name</li>
</ol>
1.Create B like A
2.Load duplicate of &ldquo;A&rdquo; TO &ldquo;B&rdquo;
3.Drop &rdquo;A&rdquo;
4.Rename &ldquo;B&rdquo; TO &ldquo;A&rdquo;
Note: If we want to complete row duplications, then take a set table&nbsp;
<blockquote>
Inclined to build a profession as SQL Server Developer? Then here is the blog post on, explore <a title="SQL Server Training" href="sql-server-training" target="_blank" rel="noopener">SQL Server Training</a>
</blockquote>
<h2>Creating sequence Tera data</h2>
We use the identify column to generate the sequence in tera data and so the server create set table test (party ID Integer Generated always as identify Start with 1 Increment by1 Min value 2147483647 Max value 2147483647 No cycle), party name varchar (30)) Primary index(party id); &nbsp;
<h3>For testing:</h3>
Insert into test2(&lsquo;Vinay&rsquo;)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Generate always As identify, it will not in insert user given input value
<img src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" />Generated by default as identity, it takes user given input value also. Create set table test 11 (party id integer generated by default as identify(Max value3), Party name varchar(30)) Primary index(party id); &nbsp;
<h2>Conditional expressions</h2>
Generally, this conditional expression only where and having clauses (In, not in, BETWEEN AND, Not BETWEEN AND, IS NULL, IS NOT NULL, EXISTS, NOT EXISTS, LIKE, ALL, SAMS, ANY, etc---)
<img src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" />Among in and exists which gives good performance
Ans: Always use exists, because it gives a good performance (once finds the match it stores the process)
<img src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" />Display All the name where % is the 2nd character
Ans:&nbsp;Select * From party where party name like &ndash;z %% ESCAPE &lsquo;Z&rsquo;
<img src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" />Select * From party where party name like &ndash;z-%
<img src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" />Select * From party where party names like%
<img src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" />in Select * From party where party id in (1,2,3) (or) Sel * From party where party id = 1 or party id = 2 or party id =3
Exist not Exists: To the values existed it display the result &nbsp;
<h2>Working with multiple tables</h2>
Rows and columns
<h3>1. Sub Queries:</h3>
<ul>
<li>Its operation is Query wise based on one query result it Fetches the other Query</li>
</ul>
<h3>2. Set operations:</h3>
<ul>
<li>It performs operations on row-wise</li>
</ul>
<h3>3. Joint</h3>
<ul>
<li>&nbsp;It performs operations on column-wise</li>
</ul>
<h3>4. Views</h3>
<ul>
<li>It is a logical object for the physical collection of takes generally for retrieval perspective</li>
</ul>
<h3>5. Procedure</h3>
<ul>
<li>it is a pre-compile collection of SQL Statements, which performs as a particular process it can pass multiple values outside</li>
</ul>
<h3>6. Function</h3>
<ul>
<li>&nbsp;it is also for implementing a particular process, but it returns as a single value as a result</li>
</ul>
<h3>7.&nbsp; Trigger</h3>
<ul>
<li>&nbsp;it performs an action when there is an event that occurred</li>
</ul>
<h3>8.&nbsp; Cursers</h3>
<ul>
<li>&nbsp;it is a stored memory area where the navigation and operations of rows if possible intake area</li>
</ul>
<h3>9. Utilities</h3>
<ul>
<li>it performs Loading and unloading data</li>
</ul>
<h3>10. Macros</h3>
<ul>
<li>Collection of statements for implementing a simple process, Generally Recommended frequently used processes and queries.</li>
</ul>
<h2>Sub-Queries</h2>
Query inserts a query subquery, in this case, one query is outer Query, the other is inner Query
&nbsp; <img class="alignnone size-full wp-image-9751" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_756.png" alt="Sub-query" width="367" height="154" /> &nbsp;
Always we refer to inner Queries by using conditional expressions, like, in, Exists, not in, not Exists, etc&hellip;.. Operators. &nbsp;
<h3>Types of sub-Queries</h3>
<ul>
<li>Simple sub- Query</li>
<li>Nested sub- Query</li>
<li>&nbsp;Correlated sub- Query</li>
</ul>
<h4>1.Simple sub- Query</h4>
Query insert another Query is called simple sub &ndash; Query &nbsp;
Ex: Display employee details, if the dept id available in department&nbsp; table Select* From emp where exists (select dept no from dept): Select* From emp where dept id In (select dept id from dept): Select* From emp where exists (select dept no from dept where emp. dept no= dept.dept no): &nbsp;
<h4>2.Nested sub- Query</h4>
Sub- Query within another sub- Query is called Nested sub- Query
Ex: Display dept details where Dept ids available in employee table where empid available in the employee address table and those address are valid address in party address table Select* From dept where deptid in (Select dept id from emp where empid in) (Select dept id from emp _address where emp address &nbsp;in) (Select address from party _Address) ) )
<table style="height: 418px;" width="767">
<tbody>
<tr>
<td width="319">Simple sub Query</td>
<td width="319">Correlated sub Query</td>
</tr>
<tr>
<td width="319">1.Inner Query executed First</td>
<td width="319">1.Out Query executed First</td>
</tr>
<tr>
<td width="319">
2.Inner Query executed only one time
Ex: Finding 2nd Maximum &nbsp; Select max(Party income)From party where party income not in(Select mad(Party income)from the party)</td>
<td width="319">
2.Inner Query executed many times (According to Query)
[Based on outer Query (for loop)]
Ex: Finding 2nd maximum select a party income from the party a were 1st (Select count(Distinct(b: party income))from party b where a. party income(b. party income)
</td>
</tr>
</tbody>
</table>
Note: For with max salary need to place n-1 in place of 1 Collated
<table style="height: 117px; width: 52.9233%;">
<tbody>
<tr style="height: 75px;">
<td style="height: 75px; width: 22.9869%;" width="160">A30000</td>
<td style="height: 75px; width: 22.9869%;" width="160">
<ol>
<li>PI&lt;B.PI</li>
</ol>
&lt;(F) &lt;(F) &lt;(F)</td>
<td style="height: 75px; width: 22.9869%;" width="160">COUT0</td>
<td style="height: 75px; width: 22.9869%;" width="160">B30000 20000 25000</td>
</tr>
<tr style="height: 21px;">
<td style="height: 21px; width: 22.9869%;" width="160">20000</td>
<td style="height: 21px; width: 22.9869%;" width="160">&lt;T&lt;F &lt;T</td>
<td style="height: 21px; width: 22.9869%;" width="160">2</td>
<td style="height: 21px; width: 22.9869%;" width="160">3000020000 25000</td>
</tr>
<tr style="height: 21px;">
<td style="height: 21px; width: 22.9869%;" width="160">250000</td>
<td style="height: 21px; width: 22.9869%;" width="160">&lt;T&lt;F &lt;T</td>
<td style="height: 21px; width: 22.9869%;" width="160">1</td>
<td style="height: 21px; width: 22.9869%;" width="160">3000020000 25000</td>
</tr>
</tbody>
</table>
Note:
<ol>
<li>We need to avoid the correlate sub-Query sometime in Real-time Because it may decrease the performance of the Query by occupying more spool memory and operating many rows.</li>
<li>To identify with max salary Top 10 Salary we can use Rank Qualify, Row- Number Quality (or)Top N Functions</li>
</ol>
<ol start="3">
<li>Sub Query return always single table columns only (counter QUERY COLUMN)</li>
</ol>
<h3>Set operations</h3>
<ul>
<li>It performs operations Row-wise between multiple result sets</li>
<li>It is completely set theory based</li>
<li>It uses where a set operator for its process</li>
<li>Union</li>
<li>Union All(v)</li>
<li>Intersects(n)</li>
<li>Except(-)&nbsp;</li>
</ul>
Syntax: Select column/* From Table A union/union All/Intersect/except Select column/* From Table B &nbsp;
<h3>Limitations</h3>
The number of columns and order of the column data type should be the same in both the data sets it doesn&rsquo;t bother about the column name.
<h4>Union&nbsp;</h4>
it merges the rows from to data sets including duplicate
<h4>Union all&nbsp;</h4>
merges the rows from to data sets&nbsp; including duplicate
<h4>Intersects&nbsp;&nbsp;</h4>
Take common rows from the path the data set
<h4>Except&nbsp;</h4>
Take exclusive rows from the 1st dataset[Not including matching or other data set row]
Note: Union doesn&rsquo;t allow duplicate value union all allows duplicate value Sel EID, EName From emp Union ok union All or intersect or except Sel empid, emp name From emp-Address &nbsp; &nbsp;
Ex: Using Group by clause We have to take the group by clause individual Query wise rather than entire set operations wise Sel Eid, ename from emp group by eid, ename UNION Sel empid, exlame From emp- Address group by empid; emp name &nbsp;
<h3>Multiple set operation</h3>
Sel EID,E Name From emp UNION Sel empid, emp name From emp-Address Intersects Sel EID, Ename from emp10 Except Sal eid, e name from emp
<h2>Get In-Depth Knowledge on SQL Server Click on Here:</h2>
<ul>
<li><a href="transfer-sql-server-objects-task" target="_blank" rel="noopener">Transfer SQL server Objects Task</a></li>
<li><a href="performing-data-manipulations-using-disconnected-model" target="_blank" rel="noopener"><span style="font-size: 12pt;" data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Performing Data Manipulations using Disconnected Model&quot;}" data-sheets-userformat="{&quot;2&quot;:4224,&quot;10&quot;:2,&quot;15&quot;:&quot;Arial&quot;}">Performing Data Manipulations using Disconnected Model</a></li>
<li><a href="teradata-sql-server-2008" target="_blank" rel="noopener"><span style="font-size: 12pt;" data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Performing Data Manipulations using Disconnected Model&quot;}" data-sheets-userformat="{&quot;2&quot;:4224,&quot;10&quot;:2,&quot;15&quot;:&quot;Arial&quot;}">TeraData in sql server -2008</a></li>
<li><a href="sql-server-integration" target="_blank" rel="noopener"><span style="font-size: 12pt;" data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Performing Data Manipulations using Disconnected Model&quot;}" data-sheets-userformat="{&quot;2&quot;:4224,&quot;10&quot;:2,&quot;15&quot;:&quot;Arial&quot;}">SQL Server Integration</a></li>
</ul>

TeraData in sql server 

TeraData in sql server -2008

teradata-sql-server-2008

&nbsp;
<h2 style="text-align: justify;">Views</h2>
<ul>
<li style="text-align: justify;">There are windows to tables, A view nothing but a logical collection of an attribute from the table on tables, we use views mainly for</li>
<li style="text-align: justify;">Taking requires rows and columns from the tables</li>
<li style="text-align: justify;">Reduce net bond width</li>
<li style="text-align: justify;">Providing better binding between table and view data</li>
<li style="text-align: justify;">Providing security on the table etc.</li>
<li style="text-align: justify;">Based on the functionality, there are 3 types of views</li>
</ul>
1. Read-only view
2. Updatable view
3. Materialize view
<h2 style="text-align: justify;">Materialize View</h2>
<ul>
<li style="text-align: justify;">This concept not available in Teradata, but we can implements through aggregate, join index&nbsp;(or) Global temporary tables</li>
<li style="text-align: justify;">In this view, it will have storage for view and it stores the calculated data.</li>
<li style="text-align: justify;">This storage and calculate data are useful to retrieve the data FASTER</li>
<li style="text-align: justify;">Generally, we go for a materialized view at the time of frequent analytical and AGGREGATE operations.</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<img class="wp-image-9761 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_827-460x341.png" alt="Materialize View" width="458" height="339" />
<h2 style="text-align: justify;">Materialized View &amp; Updatable view</h2>
If you create a view based on a single table, then it will be an updatable view here we can perform INSERT UPDATE&nbsp; And DELETE operations on views so that table will be effected
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Create view VW party2 As(Select* From party)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Show view var _party2
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Select* From VW-Party2
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Replace view vw- Party2 As(Select party id, party name, party income from the party)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />update vw- party2 set party income=7777 where party id=1
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Read-only view(Join view and AGGREGATE View): Here we can perform any operations.
<h3 style="text-align: justify;">Join Views</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Create view vw- party3 As(Select P. party id, t. party name from party p
Left after joining test t On p. party id=party id);
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Show view vw- party3
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Select* from vw- party3
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Replacing view vw- party3 As(Select t. party name from party p Left after joining test t on p. party id= t. party id);
<h3 style="text-align: justify;">Aggregate Views</h3>
Aggregate Views are Views which contain Aggregate expressions or derived columns it is always necessary to assign a name to there columns so that they may be referenced within the view
<h3 style="text-align: justify;">An Optional Locking</h3>
Access clause may be added to the query which permits the table row to be read even in the event that another read or write lock is held on the table
Create view party sales as
Locking table party for access
Select party code as department
Sum(party income) As salary- total
Avg(Party income) As salary- Average
max(Party income) As salary- max
min (Party income) As salary- min
From party
Group by party code
Views with Check Option
The with check option prevents rows from being inserted (or) updated via the view if the resulting rows fall outside the view
Create view dept &ndash; budget as
Select party id party name from the party
Where party income&lt;=100000 WITH CHEQ option;
Select *from party
Insert into dept &ndash; budget(party id, party income)values(603,700000);
*Failure 3564 Range constraint CHECK Error in field party name Department party income, Inserted or updated view this view may not exceed $ 1,00,000.
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h3 style="text-align: justify;">Restrictions on Views</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />An INDEX Cannot be created on a view
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />A view cannot contain an ORDER BY CLAUSE
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Derived and aggregate column must be assigned as the name
A view cannot use for update operations if it contains:
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Data from more than one table(i.e. Join views)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />The same column specified twice
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Derived columns(i.e&nbsp; Salary- Account/12)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />A DISTINCT CLAUSE
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />A Group by CLAUSE
Note
The view contains only one select statement, view returns the result set
<h3 style="text-align: justify;">Difference&nbsp; MACROS And PROCEDURES</h3>
<table style="height: 1062px;" border="1" width="790" cellspacing="1" cellpadding="1">
<tbody>
<tr>
<td>Macros</td>
<td>Procedures</td>
</tr>
<tr>
<td>Collection of statements for implementing simple TASKS</td>
<td>Precompiled collection of statements for implementing compile TASKS Ex: a. Error Handling b. Working with multiple DML statements c. Knowing memory requirement etc..</td>
</tr>
<tr>
<td>In real-time for simple and frequent required we go for macro</td>
<td>For rare request and complex operations, we go for in real-time</td>
</tr>
<tr>
<td>Runs at client side</td>
<td>Runs at server side</td>
</tr>
<tr>
<td>Execution results are cached so it gives faster response</td>
<td>Always runs at server side</td>
</tr>
<tr>
<td>Takes only i/p parameter</td>
<td>It takes
<ol>
<li>IN: input value to the procedure</li>
<li>Out: out value from the procedure</li>
</ol>
Inout: taking&nbsp; input value and passing&nbsp;output value</td>
</tr>
<tr>
<td>All statements inside MARCO executed like a transaction</td>
<td>Not applicable</td>
</tr>
<tr>
<td>Security less</td>
<td>Security high</td>
</tr>
<tr>
<td>Network bandwidth less</td>
<td>More bandwidth</td>
</tr>
<tr>
<td>We can not execute procedure control statement [loops, If, while&hellip;]</td>
<td>Supports</td>
</tr>
<tr>
<td>It supports a maximum of one DDL statement</td>
<td>Procedure no limit</td>
</tr>
<tr>
<td>This is a reference by using &ldquo;exec&rdquo; (or) &ldquo;execute&rdquo; command</td>
<td>These are reference by using call common</td>
</tr>
</tbody>
</table>
<img class="wp-image-9762 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_833-460x370.png" alt="Restrictions in views" width="485" height="390" />
For In-depth knowledge on Teradata click on:
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
</ul>

Views in Teradata

views-in-teradata

&nbsp;
<h2>Procedures</h2>
<h3>Creating </h3>
Create procedure&lt;procedure name&gt;(&lt;PARAMS&gt;)As. BEGIN&lt;SQL Queries&gt;END; &nbsp;
<h3>Replacing </h3>
Replace procedure&lt;procedure name&gt;(&lt;PARAMS&gt;)As. BEGIN&lt;SQL Queries&gt;END; &nbsp;
<h3>Dropping</h3>
DROP procedure&lt;procedure name&gt; &nbsp;
<h3>View </h3>
SHOW Procedure&lt;procedure name&gt;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>Calling</h3>
Call procedure&lt;procedure name&gt;(&lt;PARAMS&gt;) &nbsp;
<h4>Example:</h4>
Create procedure (Dcone1 INTEGER,DName1 varchar(30))
BEGIN
DECLARE DEPTNAME VARCHAR(30);
SET DEPTNAME=DNAME1;
INSERT INTO DEPT(DCODE, DNAME)Values(:Dcode1,:DEPT NAME);
END;
Call p &ndash; sample(70,&lsquo;IT&rsquo;);
Select* From Dept;
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h4>Example:</h4>
Create procedure SP(PID INTEGER, out op INTEGER)
BEGIN
DECLARE VAR1 INTEGER;
SELECT Party income into:var1 from party where party =pid;
Select op=var1*10;
END;
Call SP(1, OP)
<h2>For Indepth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>
<div class="tags">&nbsp;</div>
&nbsp;

Procedures in Teradata | TekSlate Teradata Tutorials

Procedures in Teradata

procedures-teradata

&nbsp;
<h2 style="text-align: justify;">Cursors</h2>
It is a stored memory area where the result set store, and we can navigate the result set we can read one by one row from result set]
Based on the operations there are 2 types of cursors
<ul>
<li style="text-align: justify;">Position cursor</li>
<li style="text-align: justify;">Non- position cursor</li>
</ul>
<h3 style="text-align: justify;">Position cursor</h3>
Position cursor are updatable cursor
<h3 style="text-align: justify;">Non &ndash; position cursor</h3>
Non &ndash; position cursor is read-only
<h2 style="text-align: justify;">Cursor Declaration</h2>
We can declare a cursor
<ul style="text-align: justify;">
<li>INSIDE A Procedure[procedure cursor]</li>
<li>INSIDE A MACRO[MACRO cursor]</li>
<li>INSIDE A SELECT[SELECTMENT]</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h2 style="text-align: justify;">Implicitly cursor</h2>
<ol style="text-align: justify;">
<li>By using dynamic SQL</li>
<li>By using Embedded SQL etc</li>
</ol>
Cursor declaration is generally in two ways
<h4 style="text-align: justify;">For loop cursor</h4>
The scope of the cursor is within the for loop only
<h4 style="text-align: justify;">Declaration cursor</h4>
The scope of the cursor is between the begin and END statement only
<h2 style="text-align: justify;">Cursor process</h2>
It is 5 steps process
<ol style="text-align: justify;">
<li>Declaring the cursor</li>
<li>Opening the cursor</li>
<li>Fetching the data from the cursor</li>
<li>Working with rows of the cursor</li>
<li>Closing the cursor</li>
</ol>
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h3 style="text-align: justify;">Cursor error codes</h3>
Ex: If the cursor is not open, but we are trying to close the cursor at that time the SQL code is 7362
<h4>Example of procedure cursor</h4>
Create procedure SP3()
Begin
Declare var1 integer;
Declare var2 character(30);
Declare C1 &nbsp;cursor For Select party id, party name from party ORDER By party id;
Open C1;
While(SQL Code=o)DO Fetch C1 into var1, var2;
Insert into party1(Party id, party name) values(var1, var2);
End while Close cd;
End;
Call SP3()
Select* From party1;
<h2> For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>
<div class="tags">&nbsp;</div>

Cursors in Teradata

cursors-in-teradata

&nbsp;
<h2 style="text-align: justify;">Triggers</h2>
Perform actions based on the event, that event is inserting, delete, update operations on tables.
Based on the actions, there are 2 types of triggers.
<ul>
<li style="text-align: justify;">Before Trigger</li>
<li style="text-align: justify;">After Trigger</li>
</ul>
<h3 style="text-align: justify;">a) Before trigger</h3>
Here action first, event next.
<h3 style="text-align: justify;">b) After Trigger</h3>
The event first, action NEXT.
Trigger applied at&nbsp; 2 Levels.
<ol style="text-align: justify;">
<li>ROW Level</li>
<li>Statement Level</li>
</ol>
Note&nbsp;- In Tera data V2R5.1 onwards before trigger does not support
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h4 style="text-align: justify;">Example</h4>
Create trigger&lt; trigger name&gt;()
Replace trigger&lt; trigger name&gt;()
Drop trigger&lt; trigger name&gt;
Help trigger&lt; trigger name&gt;
SHOW trigger&lt; trigger name&gt;
Alter trigger&lt; trigger name&gt;DISABLED
ALTER trigger&lt; trigger name&gt;ENABLED
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Create trigger&lt; trigger name&gt; Alter update of (column name)
ON&lt;Table Name&gt;Referencing OLD TALE AS OLD TABLE NEW VABLE AS NEW TABLE.
For each statement/Row
[Where condition]
(Trigger Action)
<h4 style="text-align: justify;">Example</h4>
Create Trigger Vinay after update of (party income) on party for each statement
(Insert into test(11,&rsquo;Record update&rsquo;,&rsquo;00001&rsquo;););
SHOW Trigger Vinay
Update party set party income=99999 where party id=2
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h4 style="text-align: justify;">Example</h4>
Create table Translog
OLDVALU INTEGER,
NEW VALUE &nbsp;INTEGER,
Record date format&rsquo; yyyy-mm-dd&rsquo; USE-UPDATED
Varchar (50)Default user
Create trigger Tr log
After update of(party income) on party
Referencing OLD AS O New AS N For Each Row
When(N. Party name&gt;10000)
(Insert into translog (o. party income, N. party income, current-date);)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />update party set party income=40000 where party id =2
<h3 style="text-align: justify;">Limitations</h3>
We cannot use the below statement in the trigger body
Select statement to return as Result
Exception Handling
BEGIN And END Statements
COMMIT
DDL And DCL Statements
<h2 style="text-align: justify;">For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in Teradata</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in Teradata</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in Teradata</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In Teradata</a></li>
</ul>
<div class="tags">&nbsp;</div>

Triggers in TeraData | TekSlate TeraData Tutorials Online

Triggers in TeraData

triggers-in-teradata-with-examples

&nbsp;
<h2>Teradata Loops</h2>
If&lt;Condition&gt;Then&lt;Statement1&gt;;
Else&lt;Statment2&gt;;
End If &nbsp;
<h3>Description</h3>
If the condition is successes if executes statement1, other wise executes statement2 &nbsp;
<h2>While loop</h2>
While(&lt;Condition&gt;)Do
&lt;SQL Statements&gt;
End while; &nbsp;
<h3> Description</h3>
If repeats in till condition fails&nbsp; &nbsp;
<h2> Looping[For Loop] </h2>
Loop Label: Label- Name
&lt;Statements&gt;
End loop Label- Name &nbsp;
<h3> Description </h3>
Till the condition False the loop will be Repeated
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h2>Declaring variable </h2>
Syntax&nbsp;-&nbsp;&nbsp;Declare&lt;Variable name&gt;&lt;Data type&gt;;
Ex&nbsp;-&nbsp;Declare Dept name Varchar(30);
Assigning values to variables; &nbsp;
Syntax &nbsp;-&nbsp;Set &lt; Varchar &gt;=&lt;value&gt;or&lt;variable&gt;or&lt;columnname&gt;
Ex:
Set Dept name=D Name1;
Set Dept name= IT
<h2>Format command</h2>
9 &nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Digit
Z &nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Zero Suppressed Digit
$<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Under score etc
#&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp; Formatting string
X <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Single character
B <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;BLANK
<h2> Formatting Dates</h2>
DD <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;DAY
MMMM <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Full month
YY <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Year
YYYY <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Full year
B <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Blank etc&hellip;
85000 <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;$9999 <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> $85000
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
&nbsp; <img class="alignnone wp-image-9777" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_843-460x126.png" alt="Formatting Dates" width="512" height="140" /> &nbsp;
Note:&nbsp;&nbsp;
Formatting command we can execute only in BTEQ.
<h2>For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a title="Teradata Tutorials" href="tutorialsteradata" target="_blank" rel="noopener">Teradata Tutorials</a></li>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>
<div class="tags">&nbsp;</div>

Loops in Teradata | Tekslaate Teradata Online Tutorials

Loops in Teradata

loops-in-teradata

&nbsp;
<h2>Macros</h2>
<h3>Creating a Macro in Teradata</h3>
Create macro&lt; macro&nbsp; name&gt;(&lt;params&gt;)AS(SQL Queries); &nbsp;
<h3>Replacing a Macro in Teradata </h3>
Replace macro&lt; macro&nbsp; name&gt;(&lt;params&gt;)AS(SQL Queries); &nbsp;
<h3>Dropping &nbsp;a Macro in Teradata </h3>
Drop macro&lt; macro&nbsp; name&gt; &nbsp;
<h3>Viewing&nbsp;a Macro in Teradata </h3>
SHOW macro&lt; macro&nbsp; name&gt;&nbsp;&nbsp;
<h3> Executing a Macro in Teradata </h3>
Exec &lt; macro&nbsp; name&gt;(&lt;params&gt;) OR Execute&lt; macro&nbsp; name&gt;&gt;(&lt;params&gt;) CREATE macro mc &ndash; party2 (partyid1 integer)
As(Select* From party where party id =:party id1 or party name&gt; :party income1;)
Exe mc- party2 (2,20000)
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>Using Macros to preserve the integrity </h3>
MACROS may be used for either of the following purposes: <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />To ensure data integrity that the data conforms to certain or value restrictions <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />To ensure referential integrity that the data being input as foreign keys have legal primary key values else wherein the database Create as a macro which inserts a new party member row with the following restrictions <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Party must be over 21 years of age <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Party must be assigned a valid party code. <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Create macro new &ndash; party (pid integer, pname varchar(30),pcode integer, Hired data, birth date)
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
As(Rollback work &rsquo;Invalid Here&rsquo;)
Where(:Hired- birth)/365&lt;21;
Rollback work &rsquo;Invalid party code&rsquo;
Where: pcode not in (Select party code from a party where party code =: pacode);
Insert into the party(Party id, party name, party code, j date)
Values(:pid: p name, :p code: hired);); &nbsp;
<h3>Fail &nbsp;</h3>
exe new party (19,&rsquo; meer&rsquo;, 40,&rsquo;2010-08-06&rsquo;,&rsquo;2010-08-06&rsquo;)
<h3>Success</h3>
exe new&nbsp; party(19,&rsquo; meer&rsquo;,40,&rsquo;2010-08-06,&rsquo;&rsquo;1981-08-06&rsquo;)
<h2> For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>
<div class="tags">&nbsp;</div>
&nbsp;

Macros in TeraData | TekSlate TeraData Tutorials Online

Macros in TeraData

macros-in-teradata-with-examples

&nbsp;
<h2>Functions</h2>
a)To do some operations by loading or retrieving [Arithmetic, mathematics, etc operations]
b) To implement &ldquo;<a href="http://tekslate.com/etl-testing-training" target="_blank" rel="noopener">ETL</a>&rdquo; Functionality etc &nbsp;
<h2> Coalesce </h2>
<ul>
<li>It returns 1st, not null value</li>
<li>It can be also be used for returning user Defined values in case of null &nbsp;</li>
</ul>
<h4>Syntax:</h4>
Coalesce(Column or expression,&lt;user define values&gt;) &nbsp;
Ex: Select party name, coalesce (party name, &rsquo;unknown&rsquo;)From party &nbsp;
Ex: coalesce(permanent, temporary, email id, contracting &lsquo;Details not available&rsquo;) &nbsp;
<h3>Description</h3>
Getting the non- empty column value or control information. &nbsp;
<h2> Null LIF</h2>
It returns null faith Arguments are equal &nbsp;
<h4>Syntax</h4>
Null IF(Argument1,Argument2) &nbsp;
Ex:
<ol>
<li>Select party income, Null if(party income,50000)from party&rsquo;</li>
<li>Select 100/(null if(10-10-10,0))&mdash;it prevents from division by zero</li>
</ol>
<h2>TRIM &nbsp;</h2>
It removes both left and right side spaces &nbsp;
Syntax- TRIM (Column or expression)
Ex- &nbsp;Select party Name, TRIM (Party name) from the party;
TRIM ([Expression] Leading and training Blanks/Binary zeros
TRIM (Both From[Expression]Leading a training blank/brings zeros
Trim (Train LING From[Expression])Trailing blanks/binary zeros
Trim(Leading from[Expression]) Leading blanks/binary zeros&nbsp; &nbsp;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h2>Character or chars</h2>
Displays the No.of character in the string &nbsp;
Syntax-&nbsp;Chars (Column or expression) &nbsp;
Ex: Select party name, chars(party name) from party &nbsp;
<h2>Length </h2>
Display the length of the argument[Can be integer, string, date] &nbsp;
Syntax-&nbsp;Length(Argument)
Ex: Select Length(&lsquo;Vinay&rsquo;)
Select Length(&lsquo;100&rsquo;) &nbsp;
<h2>Sub str </h2>
Display substring from the specified to position to the length in the main string &nbsp;
Syntax- Sub str(Column or expression, startpos, Length)
Ex- Select party name, sub str(party name 3,2)From party; &nbsp;
<h2>Position </h2>
Display the first occurrence position of a character or string in columns expression
Syntax-&nbsp;Position(Character or string in column or expression)
Ex: Select party name, Position(&lsquo;a&rsquo; in party name)from the party; &nbsp;
<h2>Add Months</h2>
Adding a specified number of month to the date and column &nbsp;
Syntax- Add-months(Date column,&lt;No of Months&gt;)
Ex- Select Jdate, Add- months (jadate,5) from party &nbsp;
<h2>CAST </h2>
It converts one data type to another data type [Integer to string date to string etc]
Syntax-&nbsp;CAST(Column Or Exp As new data type);
Ex:
<ol>
<li>Select Jdate, CAST(J Date As varchar(7))From pary&mdash;Date&mdash;String</li>
<li>Select CAST(&lsquo;1234&rsquo; As char(3)--; Integer-String)</li>
</ol>
<h2>AS CLAUSE</h2>
It used for taking alias name and we can use alias anywhere in the query
Ex- Select party income as pi from a party where pi&lt;50000;&nbsp; &nbsp;
<h2>CASE Expression </h2>
It Evaluates Against multiple conditions[Instead of multiple id else conditions we go for this] &nbsp;
Syntax-&nbsp;CASE
When condition1 Then STMT1
When condition2 Then STMT2
When condition3 then STMT3
Else STMTN
End
Ex: &nbsp;
Select party id, party name, party income CASE When party income&lt;=30000 Then &rsquo;poor&rsquo; When party income&gt;30000 And party income&lt;50000 Then &rsquo;Avg&rsquo; Else &rsquo;Good&rsquo; End As Rating from the party;&nbsp; &nbsp;
<h2>Data types Attribute functions </h2>
Title -&nbsp;Display title of the column &nbsp;
Format -&nbsp;Display Format of the column &nbsp;
Named -&nbsp;Display Column name &nbsp;
Type - Display The data type of the column if there no title column name is the title
Select title(party name), Format(Party name), named(party name), type(party name )from the party;
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h2>Sample</h2>
<ul>
<li>Displays sample number of rows</li>
</ul>
Syntax: Select* From party sample N;
Ex: &nbsp;Select * from party smple10;
<ul>
<li>Displays sample present age number of rows</li>
</ul>
Syntax:&nbsp;Select*from party sample N;
Ex: Select * from party sample 20;
<h2>Rank&nbsp;</h2>
Displays the party Rank of the column
Select party income, Rank(Party income)ASR from party Quality R&lt;5; &nbsp;
<h2>RANDOM</h2>
Displays the RANDOM value between range specified
Syntax -&nbsp;Random(Low value, High value)
Ex - Select party id, party name, Random(20,30) from the party; &nbsp;
<h2>Merge</h2>
It updates, if the Record exists otherwise it inserts
Ex - Merge into a party using values(1,&rsquo;Meera&rsquo;,30)As D(pid,pnm)
On D. Pid- party id
When matched then
Update set party=D.PNM
When Not varchar then
Insert values(D.pid, D. PNM, D. ped) &nbsp;
<h2>Format commend</h2>
It is used to change the format of integer, strings, and dates
Ex: Select party income, party income(Format&rsquo;$99,999,99&rsquo;)
From party;
Select(1000/party income)*100(Format &lsquo;z9%&rsquo;)As
&ldquo;Increase percentage&rdquo; From party;-- it the output is 003% then it displays3%
Select party income(Format&rsquo; XC3&rsquo;)From party;
Select JDate, Jdate(Format &rsquo;yyyyBmmm/DD&rsquo;)From party;
<h2>For In-depth knowledge on Teradata click on:</h2>
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>
<div class="tags">&nbsp;</div>

Functions in TeraData | | TekSlate TeraData Tutorials

Functions in TeraData

functions-in-teradata-with-examples

&nbsp;
<h2>UTILITIES </h2>
A utility is nothing but an application (or) a script (or) collection of statements As a block, it is a tool, which LOADS And UNLOADS The Data There many utilities available our of them the very important are:
BTEQ- Load/Unload
FAST LOAD-LOAD
MULTI LOAD-LOAD
TPUMP-LOAD
TPT[Tera data-parallel Transporter]- Load/Unload
TPT API- Load/unload
OLE LOAD- LOAD/UNLOAD
Etc. &nbsp;
<h2> Utility Modes </h2>
Every Utility RUNS in 2ways
<ul>
<li>Interactive mode</li>
<li>Batch mode</li>
</ul>
<h3>a) Interactive mode </h3>
Here one by one statement executed after opening the utility window &nbsp;
<h3>b) Batch mode</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Collection of statements as a script executed here
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />live open an editor like a note pad, word pad, etc. and writes the collection of statement
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />We can save this script any extension[Tat.ddt, out, etc]But tera data Recommending the below extension to differentiate one script from another script &nbsp;
<h2>Utility Execution </h2>
There are 3 ways:
<ol>
<li>Open the utility and run one by one statement</li>
<li>Go to the corresponding utility and execute the utility script Run command Run File= &lt;File name&gt;;</li>
<li>This is the standard way</li>
</ol>
<h2>IN WINDOWS</h2>
 BTEQ -&nbsp;&nbsp;Start &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Run &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> cmd&agrave;BTEQ&lt;[Script path]&gt;[Log file path]
 FLOAD -&nbsp;&nbsp;&nbsp;Start &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Run &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> cmd <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> FLOAD LOAD&lt;[Script path]&gt;[Log file path]
TPUMP -&nbsp;&nbsp;&nbsp;Start &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Run <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;cmd <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> TPUMP &lt;[Script path]&gt;[Log file path]
 FEXP -&nbsp;&nbsp;&nbsp;Start &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Run&nbsp;&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;cmd&nbsp;&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />FEXP &lt;[Script path]&gt;[Log file path]
Mload -&nbsp;Start &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Run &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> cmd &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Mload &lt;[Script path]&gt;[Log file path] &nbsp;
<h2>IN UNIX</h2>
 BTEQ -&nbsp;&nbsp;&nbsp; $Sh&gt; BTEQ&lt;[ Script path]/TEE&lt; LOGFILE PATH &gt;(OR)
BTEQ -&nbsp;&nbsp;&nbsp;&nbsp;$Sh&gt; BTEQ&lt;[ Script path &gt;[LOG FILE PATH]
FLOAD -&nbsp;&nbsp;&nbsp; Sh&gt; FASTLOAD&lt;[ Script path]/TEE&lt; LOGFILE PATH &gt;(OR)
FLOAD -&nbsp;&nbsp;&nbsp;&nbsp;$Sh&gt; FASTLOAD &lt;[ Script path &gt;[LOG FILE PATH]
MLOAD -&nbsp;&nbsp;&nbsp; $Sh&gt; Mload &lt;[ Script path]/TEE&lt; LOGFILE PATH &gt;(OR)
MLOAD -&nbsp;&nbsp;&nbsp;&nbsp;$Sh&gt; Mload &lt;[ Script path &gt;[LOG FILE PATH]
TPUMP -&nbsp;&nbsp;&nbsp;$Sh&gt; TPUMP &lt;[ Script path]/TEE&lt; LOGFILE PATH &gt;(OR)
TPUMP -&nbsp;&nbsp;&nbsp;&nbsp;$Sh&gt; TPUMP &lt;[ Script path &gt;[LOG FILE PATH]
FEXP -&nbsp;&nbsp;&nbsp; $Sh&gt; FEXP &lt;[ Script path]/TEE&lt; LOGFILE PATH &gt;(OR)
FEXP -&nbsp;&nbsp;&nbsp;&nbsp;$Sh&gt; FEXP &lt;[ Script path &gt;[LOG FILE PATH] &nbsp;
Note:
TEE Display the LOG Information on the console&nbsp; as well as Re- Directs it to the specified file &nbsp;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>Uses of Log file</h3>
<ul>
<li>Ensuring Script success or fail.&nbsp;</li>
</ul>
GO TO LOGFILE LOG File And Read the Last Statement which contains the Return code, According to the return code we have to take appropriate action &nbsp;
<h3> Return codes and symbols </h3>
0 <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;Job complete normally
2 <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;User alert to Log on to the Tera data DBS
4 <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;Working
8 <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;User error
12 <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;Fatal error[Connection Related Issues possible here]
16 <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> &nbsp;If no MSG destination is the available or typical error &nbsp;
Note:
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> &nbsp;Each Return code belongs to a set of error codes
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;In Unix to know the script ran success or not without [opening log ile] &nbsp;
<h3>Open log file</h3>
<ul>
<li>If[$?]=0 means script success</li>
<li>If[$?]=other than o means script fail &nbsp;</li>
</ul>
<h3> Utility commands and keywords</h3>
<ul>
<li>Generally, utility commands storks with&rsquo;.&rsquo;[DOT]</li>
<li>Generally SQL Statements End with;</li>
<li>comment notation/*&nbsp; */BTET Mode</li>
<li>ANSI Mode</li>
</ul>
<h3>Some Important commands and key works</h3>
<ul>
<li>.ERRORCODE</li>
<li>.ACTIVITY COUNT</li>
<li>.ERROR LEVEL</li>
<li>.REMARK</li>
<li>.REPEAT</li>
</ul>
<h3>&nbsp; All utilities</h3>
<ul>
<li>.ACCEPT</li>
<li>.SET</li>
<li>.DISPLA</li>
<li>.ROUTE MESSAGES</li>
<li>.IMPORT</li>
<li>.EXIT</li>
<li>.QUIT</li>
<li>.OS CLS</li>
<li>.SHOW CONTROLS</li>
<li>.QUIET ON/OFF</li>
<li>.LOG ON</li>
<li>.LOG OFF</li>
</ul>
<h3>&nbsp; ADDITIONAL COMMANDS WITHOUT&rsquo;&rsquo;[DOT]</h3>
<ul>
<li>TENACITY</li>
<li>SLEEP</li>
<li>CHECKPOINT</li>
<li>SESSION</li>
<li>PACK</li>
<li>RECORD</li>
<li>USING</li>
<li>DEFINE</li>
<li>LAYOUT</li>
<li>ERRLIMIT</li>
<li>RECORD</li>
<li>.ERRQOUL</li>
<li>DECLARE</li>
<li>OS&lt;Command NAME&gt;-- PARTICULAR OS COMMAND</li>
</ul>
&nbsp; Note:
&nbsp; Refer to the material for more syntax, the example of this command in the tera data new and advanced material page
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>

Working Utilities in Teradata

Working with Utilities in TeraData

working-utilities-teradata

BTEQ Features in Teradata | TekSlate Teradata Tutorial

BTEQ Features in Teradata

bteq-features-teradata

&nbsp;
<h2>Fast load process and phases</h2>
<ol>
<li>It processes the data block by block</li>
<li>It Runs in 2 phases</li>
</ol>
&nbsp; <img class="alignnone wp-image-9800" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_951-460x153.png" alt="Fast load Process and Phases" width="561" height="185" /> &nbsp; &nbsp;
<h2>Phases</h2>
<h3>1.ACQVISITION PHASE[INSERT phase]</h3>
[Insert phase]{FileAmp}&rdquo; Access lock&rdquo; on target
&nbsp; <img class="alignnone wp-image-9802" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_962-460x326.png" alt="ACQVISITION PHASE" width="563" height="398" /> &nbsp; &nbsp;
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Row available in Amp and Disk but not in sequence
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Data available in the Amp and disk but not in stored order &nbsp; &nbsp;
&nbsp;
<h3>2) Application phase</h3>
[End loading phase]
&nbsp; <img class="alignnone wp-image-9858" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_971.png" alt="Application Phase" width="354" height="480" /> &nbsp;
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Rows in amp and Disk are in sequence
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Data in the Amp Disk are in stored order &nbsp;
Note
Refer to the material for more Description of each phase &nbsp; &nbsp;
<h2>Fast load Limitations</h2>
<ol>
<li>It loads only one table and the table should be empty</li>
<li>It does not support SI, RI, JI, HT, And Trigger</li>
<li>No Amp should Go Down when a Fast load is processing</li>
<li>The Table is a multiset, you try to store duplicate data, those records do not move into any error tables, but the number of duplicates mentioned in the Log File.</li>
</ol>
<h2>Fast load Locks</h2>
<ol>
<li>It applies &ldquo;Access Lock&rdquo; on the table in AQuision phase</li>
<li>It applies &ldquo;WRITE Lock&rdquo; And Application phase</li>
</ol>
<h3>&nbsp;Ex: Importing unformatted Data)(5th Script in the project)</h3>
.Set Record unformatted;
.Run File c:BTEQConn.Txt;
Database vinayaka;
Drop Table erparty3;
Drop table uvparty3;
Delete from party3;
Begin loading party3
Error files exparty3,Uvparty3
Checkpoint10;
Define
i-pid(char(1)),
i-PNUM(char(5)),
i-PCode(char(2)),
i-Pinc(char(5)),
CRLF(Char(2))
[CRLF=Enter character]
File=c:Fastloaddata-fixed.txt;
Insert INTO party3
(Party id, party name, email, party code, Jdate, party income user created)
Values(
: I-Pid,
: I-PNm,
&lsquo;Tera data <a href="mailto:vinay@yahoo.co.in">vinay@yahoo.co.in</a>&rsquo;,
: I-Pcode,
Current-Date(Formal&rsquo;yyyy-mm-dd&rsquo;),
: I-PINC,
);
END Loading;
.LOGOFF &nbsp; &nbsp;
&nbsp;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
&nbsp;
<h3>Importing variable Text Data [6th Script in the Project]</h3>
.Set Record Vartext&rdquo;,&rdquo;;
Session4;
.Run File c:BTEQConn.TxT;
Database vinayaka;
Drop table erparty4;
Drop table UV party4;
Delete from party4;
Begin Loading party4
Error files erparty4, UVParty4
Check point 10;
Record2;
/*errlimit10;*/
Define
i- pid (Varchar(30)),
i-PNM(Varchar(30)),
i-PCode(Varchar(30)),
i-pinc(Varchar(30)),
File=c:Fast loaddata-vartext.txt;
Insert into party4
(Party id, party name, email, party code, J date, party income, user created)
Values
(
:i-pid,
:i-PNM,
&lsquo;Tera data <a href="mailto:vinay@yahoo.co.in">vinay@yahoo.co.in</a>&rsquo;,
31300,
Current-Date(Format&rsquo;yyyy-mm-dd&rsquo;),
:i-pinc,
);
End Loading;
.LOGOFF &nbsp; &nbsp;
<h3>To see the data in error tables</h3>
<ol>
<li>Select*From Erparty4;//[select *From sys Admin. Fast Log]</li>
</ol>
[//select *From ET-Party41]
<ol start="2">
<li>F5 Press</li>
</ol>
&nbsp;
&nbsp; &nbsp; <img class="alignnone size-medium wp-image-9859" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_981-460x421.png" alt="Lob Information" width="300" height="274" />&nbsp; &nbsp; &nbsp;
<h3>2nd Error table data</h3>
Select* From UVParty4 &nbsp;
<h2>Generally problem in the Lab Rectification</h2>
<ol>
<li>Syntactical error in script</li>
<li>Record parcel length does not match</li>
<li>If the defining are using class structure length or input file, Data file not match, then we get the error</li>
<li>Input number of values not matching with column names</li>
</ol>
Ex: Insert into party(5Columns)Values(4Columns) &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="BTEQ Features in Teradata" href="bteq-features-teradata" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a title="Working with Utilities in TeraData" href="working-utilities-teradata" target="_blank" rel="noopener">Working with Utilities in TeraData</a></li>
</ul>

Fast load process and phases in TeraData

fast-load-process-phases-teradata

&nbsp;
<h2>ERROR HANDLING</h2>
To prevent from known and unknown error at rentine and doing our user-defined action is called error handling
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> we need to set an error level for handling the error &nbsp;
Syntax
.Set error level&lt;error code&gt;Severity &lt;Integer&gt; &nbsp;
Ex:
.Set error level 6784 severity 4
.Set error level 807severity 8
.Set error level 2121 severity 12
.Set error level 5678 (or)unknown severity 16 &nbsp;
&nbsp;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
Note:
HIGHEST Return code=0 means script success
/*Rum File c:BTEQconn. TxT;*/
.Logon 127.0.0.1/DBC,DBC;
.Set session 4;
Database Vinayaka;
.Set error level 3807 Severity 4
.Set error level 6066 Severity 8
Select*From party22;
Insert into party(1,&rsquo;vinay&rsquo;,34567865);
.IF error level =4 then insert into Translog1(&lsquo;vubat1&rsquo;&rsquo; Table is &rsquo;not created&rdquo;);
Insert into party 22(12345678912345678,&rsquo;Vinay&rsquo;);
.If error level -8 then. Remark &rsquo;Range invalid,&rsquo; don&rsquo;t load the table;
.Logoff&nbsp; &nbsp; &nbsp;
<h2>FAQs.</h2>
<h3>1. What is the main usage of BTEQ?</h3>
<h3>2. There is a file with 100 Records I want to load 60 Records without loading the 1st 20 Records</h3>
&nbsp; <img class="alignnone wp-image-9796" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_922.png" alt="file of records" width="223" height="176" />
Skip =20 , Repeat =60
By using skip=20 and repeating =60 there is a table with 10000 Records, but I Require Top 10 income person Details in the file
1) Select
Order by PI Desc:
Limit=10 &nbsp;
&nbsp;
<h3>3) How do you import multiple files And multiple tables</h3>
Ans:&nbsp;.LOGON By writing multiple import statement we perform the operation &nbsp; &nbsp;
<h3>4) How do you import multiple files and multiple tables</h3>
Ans:&nbsp;
.logon
Import infile file1
Using&lt;clause&gt;
Insert/update &lt;statement1&gt;
Import infile file2
using&lt;clause&gt;
insont /undate&lt;statey&gt; &nbsp;
<h3>5) General error in the Lab?</h3>
1.The file specified does not exist
Ans:- If the input file path/script path&nbsp; wrong we get these types of error &nbsp;
<h3>6) Record length not matched with structure (date parcel does not match)</h3>
Ans:- 
We get this at the time of&nbsp; in the below scenario
a)If multiple ends of the file marks are those
b)If the user class structures no. of column and data structure.
c)Not matching input record effect &nbsp;
7. the values specified &nbsp; &nbsp;
<h2>FAST LOAD</h2>
<ul>
<li>It is a bulk load facility which loads a large volume of data(High value of data)</li>
<li>Compare to all utilities it Runs FASTER</li>
<li>It runs in 2 phases</li>
<li>It captures the errors records into an error table</li>
<li>It is fully automatic Restartable and checkpoint configurable</li>
<li>It loads only one table at a time and in the table should be empty</li>
<li>It does not support duplicate records[NUPT OR multiset duplicate]</li>
<li>It processes the data block by block</li>
<li>It performs insert operation between begin loading and end loading[Before begin loading we can write DROP, Delete, create, etc commands]</li>
<li>It supports input module programming[INMOD]</li>
<li>Maximum 15 FEXP Script we can execute at a time</li>
</ul>
<h2>NAVIGATION</h2>
Start&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Run &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;FAST LOAD
Start &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;programs &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;Tera data Client &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Tera data fast load
&nbsp; FAST LOAD&nbsp;Table
It uses an error table and one log table &nbsp;
<h3>a)ERROR Table one</h3>
Generally, the below error move into the error Table one
<ul>
<li>Data conversion error</li>
<li>Constrains violation</li>
<li>Unavailable Amp error</li>
</ul>
Structure: Party
<img class="alignnone wp-image-9797" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_932-460x331.png" alt="Error Table" width="549" height="393" /> &nbsp; &nbsp;
&nbsp;
<h3>b)Error table Two</h3>
If the table is having UPI And we try to store duplicate Records and those Records move into error table2
Structure
Similar to the Target table &nbsp;
<a href="interview-questions-on-teradata-dba" target="_blank" rel="noopener"><span style="text-align: start; font-size: 10pt; font-family: Arial;" data-sheets-value="{&quot;1&quot;:2,&quot;2&quot;:&quot;Teradata DBA Interview Questions&quot;}" data-sheets-userformat="{&quot;2&quot;:4224,&quot;10&quot;:2,&quot;15&quot;:&quot;Arial&quot;}">Teradata DBA Interview Questions</a>
<h3>c) FAST LOG Table</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />FAST Load Internal take log table for implementing Restart ability
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Whenever Script fails the system stores the failed point in the fast log table
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />After rectifying the error, if we restart the FAST Load script it starts from the last point in the fast log table
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Once script executed succeed to content in the fast log table related to target table deleted
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />This table is available sysadmin Select* From SYSADMIN.FASTLOG; &nbsp;
<h2>FAST LOAD RECORDS FORMULA:</h2>
Number of records in Source file= Number of Records Loaded to target(2)
+Number of Records loaded error&nbsp; Table(1
+Number of Records Loaded error table(1)
+Number of duplicate records log file(1)
&nbsp;
&nbsp; <img class="alignnone wp-image-9798" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_941-460x252.png" alt="Fastload " width="521" height="285" /> &nbsp; &nbsp;
<h2>FAST LOAD STRUCTURE:</h2>
.Set Record&lt;Records Format&gt;
.LOGON&lt;&gt;
&lt;Create/DROP/Delete Commands&gt;
Begin loading&lt;Data base name. table name&gt;&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;phase1 starts
Error files &lt;Error table1&gt;,&lt; Error table2&gt;
Check point&lt;Integer&gt;
Define&lt;Structure&gt;
File=&lt;Filename&gt;
Insert&lt;STMT&gt;
End Loading&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> phases starts.
LOGOFF &nbsp; &nbsp;
<h3>Real-time usages of FASTLOAD</h3>
To load data into an empty table at the time of initial load
To load data into stage table[Truncate and load tables]
<h2>Checkpoint</h2>
Checkpoint is either Records based or Time based
We need to take a checkpoint was, it is happening for every 10 to15 minutes
It provides better Restorability
<h2>No checkpoint</h2>
{-1-1core}&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;reads 30 minutes
{1- core} <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;write 25 minutes
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />After Rectifying the error if you start, it starts from 1st Record &rdquo;Reading&rdquo;
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />While writing failed at 99,99,998th&nbsp; Record &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp;
<h3>Check points &ndash; Available</h3>
10 Lacks &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;check point
{1- 10L(Read)}&nbsp;&nbsp; {1- 10L(write)} <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;1 checkpoint
{10L +1-20L(Read)}{101 - 202} <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;2 check point
&hellip;&hellip;.
&hellip;&hellip;.
{90 L+1- 1 Crore (Read)} {90 C+ 1- 1 Crore(Fail)}<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> 9 check point
&nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />After Rectifying the error if you start, it start from last Record 90L+ Reading[Because of checkpoint in Log table] &nbsp;
<h3>Recommending</h3>
If records&lt;41c then check point 100000
If records&gt;41c then checkpoint 50000 &nbsp;

Working with ERROR HANDLING in Teradata - Tekslate

Working with ERROR HANDLING in TeraData

working-error-handling-teradata

&nbsp;
<h2>Multi Load</h2>
<ul>
<li>It is cusion of Fast load To load bulk data multiple tables</li>
<li>Mload Loads max 5 Tables at a Time</li>
<li>Multi load supports, INSERT, UPDATE, and UPSERT[update else insert]</li>
<li>It is fully Automatic restartable</li>
<li>It loads data into empty/populated tables</li>
<li>It loads duplicate record as well</li>
<li>It supports input module programming</li>
<li>Max15 FLML, FEXP Script we can execute at a time</li>
<li>It processes the data Block by block</li>
<li>It reduces the utilization of P.E</li>
<li>It Keeps BYNET Activity low by Running Amp local processing</li>
<li>There is no transient General overhead</li>
<li>It performs over multiple DML Operations, multiple tables by taking table data from a single file</li>
<li>Max 20 DML Operation it supports a script.</li>
<li>It Runs in 5 Phases and loads error data to error tables</li>
<li>Multi load support NUSI Data loading</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>Navigation</h3>
Start&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Run &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;M Load
Start &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;programs &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;<a href="indexes-in-teradata" target="_blank" rel="noopener">Tera data</a> client&nbsp;&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;&nbsp;Tera data m load &nbsp;
<h3>Limitation</h3>
<ul>
<li>If the table contains USI, RI, JI, HI, And Triggers, it cannot load the data</li>
<li>It does not support Aggregate exponential operations</li>
<li>Import task Requires the usage of P.I</li>
<li>Concatenation input data file not allowed</li>
</ul>
Note
Mload supports NUSI And soft Baronial Integrity &nbsp;
<h2>MLOAD Structure</h2>
.Logon&lt;&gt;
.Log Table&lt;Restart Log Table&gt;
.Begin Import Delete MLoad
Tables Table1, Table2,---Table5
Work Tables work table1, work table2,----Work table5
Error tables ET-Table1---ET-Table5
UV-Table1---UV-Table5
.Layout&lt;Layout Name&gt;
.Field
.Filler
.DML Label&lt;Label name&gt;
&lt;DML Operation&gt;
.Import In file&lt;File Name&gt;
From m&nbsp; For N thru K
Format&lt;Fast load/Binary/text/in Format/Var text&gt;
Layout&lt;Layout name&gt;
Apply&lt;Label Name&gt;[Where condition]
.End M Load
.Logo FF &nbsp;
<h3>.Field</h3>
.FIELD[Field Name][Start-pos Data Desc]
{NULLIF Null expr}
[Drop{Leading/trailing}{Blanks/Nulls}]
[[And]{Leading/training}{Blanks/Nulls}] &nbsp;
<h3>.FILLER</h3>
.FILLER[Field name][Start pos Desc] &nbsp;
Note
From M&agrave;Starting positions
For N&agrave;The number of Records To Read
Thru K&agrave;Till end Record &nbsp;
Ex: 6 To 8th Record
From 6 for 2 these 8 &nbsp;
<h3>M Load Conditional Logic</h3>
.IF&lt;Condition&gt;Then;
&lt;Statements&gt;
.Else;
&lt; Statements &gt;
.End if; &nbsp;
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata DBA Interview Question and Answers" href="interview-questions-on-teradata-dba" target="_blank" rel="noopener">Teradata DBA Interview Questions</a>
<h3>Support Environment system variables</h3>
$Sys Date&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;System Date in the Format yy/mm/dd $Sys
Date4&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;System Date in the Format yyyy/mm/dd $Sys
Date&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;System Day in the Format Day(3)
Ex: IF Friday Then &rsquo;Fri&rsquo;
$SySOS <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;HOST operating system
$Sysuser&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Unix/mvs user
$SYSRC&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Return code &nbsp; &nbsp;
<h3>M Load variables</h3>
$SYSDELCNT <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Delete COUNT
$SYSINSCNT&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;INSERT COUNT
$SYSUPDCNT&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;UPDATE COUNT
$SYSETCNT&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;ERROR Table COUNT
$SYS UVCOUNT&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;UV Table COUNT &nbsp;
<h2>Releasing LOCKS in m Load</h2>
<h3>IN ACQUISION</h3>
Release m load&lt;Table1&gt;&hellip;..&lt; Table 5&gt; &nbsp;
<h3>IN APPLY</h3>
Release m load&lt;Table1&gt;&hellip;..&lt; Table 5&gt;
IN APPLY &nbsp;
NOTE: 
WE Can use this release m load command only in &ldquo;BTEQ&rdquo;

Multi Load and its Structure in Teradata - Tekslate

Working with Multi Load and its Structure in TeraData

working-multi-load-structure-teradata

&nbsp;
<h2>Error operations</h2>
&nbsp; .DML Lable{Lable Name}
{Mark/IGNORE}duplicate{Insert/update}Rows
{Mark/IGNORE}MISSING{Insert/update}Rows
Do Insert For{missions update}Rows
&nbsp;
<img class="alignnone wp-image-9875" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1031-460x166.png" alt="Error Operations" width="542" height="195" /> &nbsp; &nbsp;
&nbsp;
<h3>Field</h3>
The value is loaded to the database.&nbsp;&nbsp;
<h3>Filler</h3>
The value which skipped before loaded to the database
&nbsp; <img class="alignnone wp-image-9876" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1041-460x213.png" alt="Delimiter" width="537" height="247" /> &nbsp;
<h3>DML Label</h3>
A name to the DML Operation to differentiate from one to other &nbsp;
&nbsp;
<h3>Multi load Tables</h3>
For every table it Requires ET, UV, WT Table for every Import task, it Requires a log table &nbsp;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>ET Table</h3>
<ol>
<li>It takes the errors that occur in AQuision phase</li>
<li>It contains primary index overflow error, that occurs in the application phase</li>
</ol>
<h3>Structure</h3>
The ET table contains the below fields
<h3>IMPORTSEQ</h3>
<ul>
<li style="list-style-type: none;">
<ul>
<li>Sequence Number that identifies the import command where</li>
</ul>
</li>
</ul>
<h3>DMLSEQ</h3>
<ul>
<li style="list-style-type: none;">
<ul>
<li>Sequence Number for the DML statement invalid the error</li>
</ul>
</li>
</ul>
<h3>SMTSEQ</h3>
<ul>
<li style="list-style-type: none;">
<ul>
<li>The sequence of the DML statement being carried out when the error was discovered.</li>
</ul>
</li>
</ul>
<h3>Applyseq:</h3>
<ul>
<li style="list-style-type: none;">
<ul>
<li>Sequence Number that tells which Apply clause was Running when the error occurred</li>
</ul>
</li>
</ul>
<h3>Sourceseq</h3>
<ul>
<li style="list-style-type: none;">
<ul>
<li>The Record number in the input file which has an error</li>
</ul>
</li>
</ul>
&nbsp;
&nbsp; <img class="alignnone size-medium wp-image-9877" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1051-460x151.png" alt="Structure" width="300" height="98" /> &nbsp;
&nbsp;
<h2>UV Table</h2>
it contains the error that occurs in the Application phase:
a)unique violation error
b) constrain error
c) overflow error on a column other than PI
&nbsp;
&nbsp; <img class="alignnone size-medium wp-image-9878" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1061-460x309.png" alt="Structure" width="300" height="201" /> &nbsp;
<ul>
<li>Import Seq</li>
<li>Dml Seq</li>
<li>Smt Seq</li>
<li>Apply Seq</li>
<li>Source Seq</li>
<li>Unique</li>
<li>DBC error cod</li>
<li>DBC errorcod</li>
<li>DBC error Field &nbsp;</li>
</ul>
<h2>Work Table</h2>
It contains DML Task Information
The input data that is Ready to Apply the amp1
Create multiset table w T c Parties
(
Field, Var Byte(64)Formal &rsquo;x(64)&rsquo;NOT Null
) primary Index(Field); &nbsp;
<h2>Log Table</h2>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />We need to specify this explicitly
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Mainly it is Import the time of Restorable the script &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="Loops in Teradata" href="loops-in-teradata" target="_blank" rel="noopener">Loops in Teradata</a></li>
<li><a title="Functions in Teradata" href="functions-in-teradata-with-examples" target="_blank" rel="noopener">Functions in Teradata</a></li>
</ul>

Error Operations in TeraData - Tekslate

Error Operations in TeraData

error-operations-teradata

&nbsp;
<h2>Multi load tasks </h2>
There are 2 types of Task &nbsp;
<h3>A) IMPORT TASK</h3>
<ol>
<li>It performs, inserts, update, Delete, And upsert operation</li>
<li>Max 5 Tables it operates, it Runs in 5 phase it uses</li>
<li>Apply command for its operations</li>
<li>Its operations are based on PI, But it cannot update the PI</li>
</ol>
<h3>B) Delete Task</h3>
<ol>
<li>It Deletes the data only one table</li>
<li>It is faster than import delete because it is global all Amp delete</li>
<li>Deletion Based on UPI Not allowed</li>
<li>Delete Task does not Require an AQuision phase, so it Runs in 4 phases</li>
<li>A Simple delete Tag is submitted to each Amp so that is Global all amp delete[Import delete does not satisfy this]</li>
</ol>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>IMPORT TASK</h3>
<img class="alignnone size-full wp-image-9881" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1071.png" alt="Import task" width="429" height="532" /> &nbsp; &nbsp; &nbsp;
<h3>DELETE TASK</h3>
<img class="alignnone size-full wp-image-9882" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1081.png" alt="Delete task" width="129" height="433" /> &nbsp; &nbsp;
<h3>MLOAD Delete</h3>
.RUN FILE C:BTEQConn.txt;
.LOGTABLE VINAYAKA.PLOG;
DATABASE VINAYAKA;
.IF&rsquo;$SYSDAY&rsquo; NE&rsquo;FRI&rsquo;THEN
.Display &rsquo;TODAY IS NOT FRIDAY, SO LOAD IS NOT HAPPENING&rsquo;
TO File c:Data&mdash;LogTROCKFILE.TXT;
.END IF;
.BEGIN DELETE MLOAD TABLES PARTY111;
DELETE FROM PARTY111;
.END MLOAD;
.LOGOFF; *Check TEMPORAL TABLE
TEM PORAL, DATA MOVER TABLE &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge click on below</h2>
<ul>
<li><a title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a></li>
<li><a title="Error Operations in TeraData" href="error-operations-teradata" target="_blank" rel="noopener">Error Operations in TeraData</a></li>
</ul>

Multi load Task in Teradata | Tekslate

Multi load tasks in TeraData

multi-load-tasks-teradata

&nbsp;
<h2>Difference Between NORMAL Delete and M LOAD DELETE </h2>
M LOAD Delete Runs Faster than Normal Delete Because of Below Reason:
<ul>
<li>No ROLLBACK so No TRANSIENT JOURNAL Overhead</li>
<li>Fully Automatic Restorable</li>
<li>RUNS in phases</li>
<li>Delete Based on UPI Not permitted</li>
</ul>
<h2>MLOAD PROCESSING And PHASES</h2>
It processes the data Block by Block
&nbsp; <img class="alignnone wp-image-9883" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1091-460x136.png" alt="MLoad Processing" width="559" height="164" /> &nbsp;
<h3>Preliminary phase[BASK SETUP]</h3>
Target Tables&agrave; A, B, C
3 ETS
3 UVS
3 WTS
LOG TABLE
&ldquo;INITIAL LEVEL LOCKS&rdquo;
(ACQUISIONLOCKS/APPLICATION LOCKS) &nbsp;
<h3>DML TRANSACTION PHASE[TO SENDING DML STATEMENTS TO RDBMS]</h3>
MLOAD Syntaxes
DML Syntaxes
DML Syntaxes MOVE TO WT TASK &nbsp;
<h3>ACQUISITION PTIASE[SENDING INPUT DATA TO AMPS] </h3>
Input data&agrave;&rdquo; WT&rdquo; Access work on Target
 Note: No AQuision phase for delete task &ldquo;ET&rdquo; Tabke &nbsp;
<h3>APPLICATION PHASE[SEND AMPS DATA TO TABLES] </h3>
WT&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Target tasks
&ldquo;Write lock&rdquo;
&ldquo;UV TABLES&rdquo; &nbsp;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>TASK CLEAN UP PHASE[CLEARING PROCESS]</h3>
DROP ETS
UVS
WTS
LOGTABLES
RELEASES&rdquo; LOCKS&rdquo; &nbsp;
Note: Refer to the material for detail description of phases &nbsp;
<h2>MULTI LOAD LOCKS </h2>
<h3>A)ACCESS LOCK </h3>
Applied in the acquisition phase &nbsp;
<h3> b)Write Lock </h3>
Applied in the application phase &nbsp;
<h3> C)AQuision Lock </h3>
Applied in the initial phase available till AQuision phase During this locks any DML operation including drip can be performed on the target table &nbsp;
<h3>D)APPLICATION LOCK </h3>
Applied in the initial phase available until the application phase during these locks, select operation including DR can be performed. &nbsp;
<h3>E)EXCLUSIVE LACK </h3>
This Lock is useful to release the locks on the target table by using the release Mload command &nbsp;
<h2>CHECKPOINT</h2>
Syntax -&nbsp;Checkpoint&nbsp; time/Records
&lt;60 times based
&gt;60 Records based &nbsp;
Ex- checkpoint 30;
For every 30 minutes it takes a checkpoint 50000;
For every 50000 Records, it takes a checkpoint &nbsp;
<h3>SESSIONS </h3>
Syntax: Sessions max-min Ex: Sessions&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2
It takes max 4, min2 sessions &nbsp;
<h3>Errlimit</h3>
<ol>
<li>Without percentage:</li>
</ol>
Ex -&nbsp;&nbsp;errlimit10; for 10errors script fails
<ol>
<li>with percentage</li>
</ol>
Ex: errlimit 1000 1;
For every 1000 Records, it checks for 1% fo errors, if it crosses script fails &nbsp;
<h3>AMPCHECK ALL/NONE/APPLY </h3>
APPLY -&nbsp; till the application phase continuous after that it stops the process
 NONE -&nbsp;it continuous of execution with Down AMP If fallback amp are available
All&nbsp;- it cannot continue its execution with down amp
EX -&nbsp;DELETING THE data by using delete TASK &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a></li>
<li><a title="Multi load tasks in Teradata" href="multi-load-tasks-teradata" target="_blank" rel="noopener">Multi load tasks in Teradata</a></li>
</ul>

Difference between Normal and M Load delete in Teradata

Difference Between NORMAL Delete and M LOAD DELETE in TeraData

difference-normal-delete-m-load-delete-teradata

&nbsp;
<h2 style="text-align: justify;">Teradata Parallel Transporter</h2>
<ul style="text-align: justify;">
<li>TPT stands for Teradata parallel transporter. As the name implies &ldquo;Parallel Transporting&rdquo;.</li>
<li>This is the new generation Load/unload utility provided by Teradata.</li>
<li>This act&rsquo;s as an integrated ETL suite that helps to EXTRACT&nbsp;data from multiple sources, apply the TRANSFORMATION logic, and&nbsp;LOAD the data in the target Teradata database.</li>
<li>TPT has all the features to run the stand-alone Teradata load/unload utilities i.e (mload, tpump, fastexport, fastload).</li>
<li>The TPT operator equivalent to standalone utilities are listed below</li>
</ul>
1. Mload=Update
2. Tpump=Stream
3. Fastload=Load
4. FastExport=Export
<ul style="text-align: justify;">
<li>As per the info the legacy utilities i.e (mload, tpump, fastexport, fastload). will continue to work as before and support the latest teradata version but any new features addition in load/unload utility will be done on TPT.</li>
<li>TPT is a utility tool of Teradata and has all the capabilities of <a title="SSIS ETL" href="http://tekslate.com/etl-in-ssis/" target="_blank" rel="noopener">ETL</a> along with features of another teradata load/unload utilities.</li>
<li>In simple terms, TPT can be described as a nutshell of all Teradata standalone utilities along with additional features.</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3 style="text-align: justify;">Teradata Parallel Transporter supports the following types of SQL statements</h3>
<ul style="text-align: justify;">
<li style="text-align: justify;">Data Manipulation Language (DML): Insert, Update, Delete, Upsert, Merge, and Select</li>
<li>Data Control Language (DCL): Give, Grant, and Revoke</li>
<li>Data Definition Language (DDL): Create, Drop, Alter, Modify, Delete Database, Delete User, and Rename</li>
</ul>
Unlike conventional utilities and products in which multiple data sources are usually processed serially, Teradata Parallel Transporter can access multiple data sources in parallel. This ability can lead to increased throughput. Teradata Parallel Transporter also allows different specifications for different data sources and, if their data is UNION-compatible, merges them.
<h2 style="text-align: justify;">Teradata PT Parallel Environment </h2>
Although the traditional Teradata standalone utilities offer load and extract functions, these utilities are limited to a serial environment.
The below figure illustrates the parallel environment of Teradata PT.
<img class="aligncenter wp-image-27821 size-full" title="Teradata Parallel Transporter Environment" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/12/TPT.png" alt="Teradata Parallel Transporter Environment" width="674" height="357" />
Teradata PT uses data streams that act as a pipeline between operators. With data streams, data basically flows from one operator to another. Teradata PT supports the following types of environments:
&bull; Pipeline Parallelism
&bull; Data Parallelism
Teradata Parallel Transporter was designed for increased functionality and customer ease of use for faster, easier, and deeper integration. The capabilities include:
<ul style="text-align: justify;">
<li>Simplified data transfer between one <a title="Teradata Component" href="teradata-components" target="_blank" rel="noopener">Teradata</a> Database and another; only one script is required to export from the production-and-load test system.</li>
<li>The ability to load dozens of files using a single script makes the development and maintenance of the data warehouse easier.</li>
<li>The distribution of workloads across CPUs on the load server eliminates bottlenecks in the data load process.</li>
<li>Data flows through multiple instances of UPDATE OPERATOR and in-memory data streams to update tables.</li>
<li>The option is available to export data to an in-memory data stream instead of landing data.</li>
<li>The open database connectivity (ODBC) operator reads from the ODBC driver, which could pull data from any database; for example, <a title="IBM DB2 Interview Questions" href="http://tekslate.com/ibm-db2-interview-questions/" target="_blank" rel="noopener">DB2 </a>or Oracle.</li>
<li>Accessibility to myriad data sources via open standards is possible.</li>
<li>Multiple operators can scan directories for files to load and can combine the data in the in-memory data stream with UNION ALL operation and stream operator loads.</li>
<li>A Script-building wizard is available to aid first-time users.</li>
</ul>
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h2 style="text-align: justify;">Important Features Of Teradata Parallel Transporter - TPT</h2>
The most important feature of TPT is its Scalability and Parallelism behaviour.
<h3 style="text-align: justify;">Scalability &amp; Performance Features Of Parallel Transporter</h3>
Below are some of the features of Parallel Transporter that can be used to increase load throughput:
<ul style="text-align: justify;">
<li>Parallel input file processing.</li>
<li>Sometimes splitting the I/O processing up over multiple input files will help overcome a performance bottleneck.</li>
<li>Directory scan feature &ndash; A single Parallel Transporter script can have multiple processes read multiple files in multiple directories.</li>
<li>Multiple processes reading the same input file &ndash; in some cases having multiple read processes reading the same file can increase data throughput.</li>
<li>Multiple processes sending data to the Teradata Database &ndash; performance can be improved by splitting the CPU time across multiple processes that are manipulating the data and then loading buffers to be sent to the Teradata Database.</li>
<li>Multiple processes exporting the same table.</li>
</ul>
<h2 style="text-align: justify;">For In-depth knowledge on Teradata click on:</h2>
<ul>
<li style="text-align: justify;"><a title="Teradata Wallet" href="teradata-wallet" target="_blank" rel="noopener">Teradata Wallet</a></li>
<li style="text-align: justify;"><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li style="text-align: justify;"><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
<li style="text-align: justify;"><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
</ul>
&nbsp;

Teradata Parallel Transporter - TPT - Updated 2022

Teradata Parallel Transporter(TPT)

tera-data-parallel-transportertpt

<h2 style="text-align: justify;">TPUMP</h2>
TPump is the shortened name for Teradata Parallel Data Pump. Fastload and Multiload are the loads that contain a huge volume of data. But TPump loads data one row at a time, using row hash locks. Because it locks at this level, and not at the table level like MultiLoad, TPump can make many simultaneous, or concurrent, updates on a table.
TPump performs Inserts, Updates, Deletes, and Upserts from Flat filed to populated Teradata tables at ROW LEVEL.
<table style="border-collapse: collapse; width: 100%; height: 124px;" border="1px" cellpadding="10">
<tbody>
<tr style="height: 124px;">
<td style="width: 98.3496%; height: 124px;">
<h3>Table of Contents</h3>
<ul>
<li><a style="color: #236fa1;" href="#tpump-supports" target="_blank">TPUMP supports</a></li>
<li><a style="color: #236fa1;" href="#tpump-structure-and-process">TPUMP Structure and Process</a></li>
<li><a style="color: #236fa1;" href="#tpump-commands">TPUMP COMMANDS</a></li>
<li><a style="color: #236fa1;" href="#difference-between-tpump-and-multi-load">Difference between TPUMP and Multi load</a></li>
<li><a style="color: #236fa1;" href="#tpump-limitations">Limitations of TPUMP</a></li>
<li><a style="color: #236fa1;" href="#tpump-error-handling">TPUMP Error Handling</a></li>
</ul>
</td>
</tr>
</tbody>
</table>
<h2 style="text-align: justify;"><a id="tpump-supports"></a>TPUMP supports:</h2>
<ul style="text-align: justify;">
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Indexes</a></li>
<li>Referential Integrity</li>
<li><a href="http://tekslate.com/15-sample-triggers-different-scenarios/" target="_blank" rel="noopener">Triggers</a></li>
<li>Join indexes</li>
<li>Pump data at varying rates.</li>
</ul>
<h2 style="text-align: justify;"><a id="tpump-structure-and-process"></a>TPUMP Structure and Process</h2>
It processes the data packet by packet
<img class="alignnone wp-image-9896" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_11110-460x127.png" alt="TPUMP Structure" width="596" height="163" />
<ul style="text-align: justify;">
<li>TPUMP Structure is similar to multi-load</li>
<li>TPUMP does not Run in phases But it creates macro for every DME Operation</li>
<li>This macro performs loading of data</li>
</ul>
Trump supports One Error Table. The error table does the following:
<ul style="text-align: justify;">
<li>Identifies errors</li>
<li>Provides some detail about the errors</li>
<li>Stores a portion of the actual offending row for debugging</li>
</ul>
<blockquote>
&nbsp;
<table style="border-collapse: collapse; width: 100%; height: 35px; background-color: #ecf0f1;" border="1px" cellpadding="10">
<tbody>
<tr style="height: 35px;">
<td style="width: 98.2442%; height: 35px; text-align: center;">Want to acquire industry skills and gain complete knowledge of Teradata? Enroll in Instructor-Led live <a style="color: #236fa1;" href="teradata-training" target="_blank">Teradata Training</a> to become Job Ready!</td>
</tr>
</tbody>
</table>
</blockquote>
<h2 style="text-align: justify;"><a id="tpump-commands"></a>TPUMP COMMANDS</h2>
<ul>
<li style="text-align: justify;">ACCEPT</li>
<li style="text-align: justify;">RUN FILE</li>
<li style="text-align: justify;">SET</li>
<li style="text-align: justify;">BEGIN LOAD</li>
<li style="text-align: justify;">FIELD</li>
<li style="text-align: justify;"><a href="http://tekslate.com/apex-data-manipulation-dml-operations/" target="_blank" rel="noopener">DML</a></li>
<li style="text-align: justify;">END LOAD</li>
<li style="text-align: justify;">IMPORT</li>
<li style="text-align: justify;">LAYOUT</li>
</ul>
<h3 style="text-align: justify;">TPUMP Example:</h3>
.Logtable myuser.tpump_yemp1;
.LOGON 127.0.0.1 myuser,myuser1;
&nbsp;DATABASE myuser;
.NAME test_tpump;
.BEGIN LOAD session 1 pack 2
&nbsp;errortable ytpump_ET;
.LAYOUT infile1;
.field f0 * varchar(10);
.field f1 * varchar(20);
.field f2 * varchar(10);
.dml label test_insert;
insert into test_tpump
values (:f0,:f1,:f2);
.IMPORT INFILE c:TESTtpump_infile.txt
&nbsp;format vartext '|'
&nbsp;apply test_tpump;
.END LOAD;
.LOGOFF;
<h2 style="text-align: justify;"><a id="difference-between-tpump-and-multi-load"></a>Difference between TPUMP and Multi load</h2>
<table style="border-collapse: collapse; width: 71.4179%; height: 492px;" border="1px" cellpadding="10">
<tbody>
<tr>
<td style="width: 50%;" width="319">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TPUMP</td>
<td style="width: 50%;" width="319">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Multi load</td>
</tr>
<tr>
<td style="width: 50%;" width="319">
<ul>
<li>Loads&nbsp; 60 or more table(max 64 Tables)</li>
</ul>
</td>
<td style="width: 50%;" width="319">
<ul>
<li>Loads max 5 Tables</li>
</ul>
</td>
</tr>
<tr>
<td style="width: 50%;" width="319">
<ul>
<li>Does not Runs in phases But create macros for DML Operations</li>
</ul>
</td>
<td style="width: 50%;" width="319">
<ul>
<li>Runs in 5 phases</li>
</ul>
</td>
</tr>
<tr>
<td style="width: 50%;" width="319">
<ul>
<li>No limitation in parallel Running of script</li>
</ul>
</td>
<td style="width: 50%;" width="319">
<ul>
<li>max 5 scripts of ML, FL exp at a time</li>
</ul>
</td>
</tr>
<tr>
<td style="width: 50%;" width="319">
<ul>
<li>Locks at the Row level</li>
</ul>
</td>
<td style="width: 50%;" width="319">
<ul>
<li>Locks at Tables level</li>
</ul>
</td>
</tr>
<tr>
<td style="width: 50%;" width="319">
<ul>
<li>Process packet by packet</li>
</ul>
</td>
<td style="width: 50%;" width="319">
<ul>
<li>Process block by block</li>
</ul>
</td>
</tr>
<tr>
<td style="width: 50%;" width="319">
<ul>
<li>Len volume of data loading</li>
</ul>
</td>
<td style="width: 50%;" width="319">
<ul>
<li>Huge volume of data loading</li>
</ul>
</td>
</tr>
<tr>
<td style="width: 50%;" width="319">
<ul>
<li>Supports USI, RI sand Triggers data loading</li>
</ul>
</td>
<td style="width: 50%;" width="319">
<ul>
<li>Does not support</li>
</ul>
</td>
</tr>
</tbody>
</table>
&nbsp;
<table style="border-collapse: collapse; width: 100%; height: 36px; background-color: #ecf0f1;" border="1px" cellpadding="10">
<tbody>
<tr style="height: 36px;">
<td style="width: 98.3496%; height: 36px; text-align: center;">Read these latest <a href="teradata-interview-questions-answers" target="_blank">Teradata Interview Questions</a> that help you grab high-paying jobs</td>
</tr>
</tbody>
</table>
<h2 style="text-align: justify;"><a id="tpump-limitations"></a>Limitations of TPUMP</h2>
<ul style="text-align: justify;">
<li>No concatenation of input data files is allowed.</li>
<li>TPump will not process aggregates, arithmetic functions, or exponentiation.</li>
<li>The use of the SELECT function is not allowed.</li>
<li>No more than four IMPORT commands may be used in a single load task.</li>
<li>Dates before 1900 or after 1999 must be represented by the yyyy format for the year portion of the date, not the default format of yy.</li>
<li>On some network-attached systems, the maximum file size when using TPump is 2GB.</li>
<li>TPump performance will be diminished if Access Logging is used.</li>
</ul>
<h2 style="text-align: justify;"><a id="tpump-error-handling"></a>TPUMP Error Handling</h2>
<h3 style="text-align: justify;">TPUMP Script is failed and error tables are available then how do we restart?</h3>
a) In case of the old file to restart:
<ul style="text-align: justify;">
<li>Do not Drop error, log tables &amp; macros</li>
<li>Rectify the error in the script or file and Run freshly</li>
</ul>
b)In case of the new file the restart:
<ol style="text-align: justify;">
<li>DROP error &amp; Log tables &amp; macros</li>
<li>Specify a new file in the script and Run freshly</li>
</ol>
Note:&nbsp; in case of the old file to restart if macros are available to execute macros directly by using exec or execute commands.
Exec&lt;Macro Name&gt;
For every DML commands, we require one macro
<h2 style="text-align: justify;">For In-depth knowledge on Teradata click on:</h2>
<ul>
<li style="text-align: justify;"><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li style="text-align: justify;"><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li style="text-align: justify;"><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/multiple-cliques-system-high-level-teradata/" target="_blank" rel="noopener">Multiple Cliques In System (OR) High Level In TeraData</a></li>
</ul>
&nbsp;

TPUMP Structure and Process In TeraData

tpump-structure-process-teradata

&nbsp;
<h2>Query man TD12/SQLA TD13 Importing and exporting</h2>
Tools&nbsp;&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;options &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />export/import
Use this delimiter between column &nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp; click ok &nbsp;
<h3>Exporting Step</h3>
<ul>
<li>Open query man</li>
<li>File menu<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> export results</li>
<li>Select the query press&rsquo;f5&rsquo;</li>
<li>File name 3the export&agrave;save</li>
</ul>
<h3>Importing Steps</h3>
<ul>
<li>Delete the Header for the input file</li>
<li>File&nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />INPUT DATA</li>
<li>Insert into emp(????)</li>
<li>Specified the file &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;Click open</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>Simple subquery</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Display employee details whose dept available in dept table
Ans: Sel*From emp;
Sel*From Dept;
Sel*From emp-Address; Select* emp where deptid in(Select deptid From Dept)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Display Dept&lsquo;s name which does not have an employee?
Ans: Sel*From Dept&nbsp; where deptid not in(Select deptid From Dept)
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Display Top 3 salary condate sub-Quer;
Ans: Select district (a. party income) From party a where3&gt;( salary count(district(b. party income) ) From party b where a. party income&lt; b. party income) &nbsp;
 Note: for nth max salary Top n salaries we have to go for either of the below wants
&nbsp; <img class="alignnone wp-image-9914" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_828-460x107.png" alt="OLAP function" width="409" height="94" /> &nbsp;
Note&nbsp;-&nbsp;Sub Query always return first Query column only(outer Query any) &nbsp; &nbsp;
<table>
<tbody>
<tr>
<td width="77">EID</td>
<td width="77">EN</td>
<td width="77">DID</td>
</tr>
<tr>
<td width="77">1</td>
<td width="77">A</td>
<td width="77">20</td>
</tr>
<tr>
<td width="77">2</td>
<td width="77">B</td>
<td width="77">30</td>
</tr>
<tr>
<td width="77">3</td>
<td width="77">C</td>
<td width="77">40</td>
</tr>
</tbody>
</table>
Emp &nbsp; Dept
<table>
<tbody>
<tr>
<td width="97">DidD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Did</td>
<td width="101">Dcat</td>
<td width="101">DLOC</td>
</tr>
<tr>
<td width="97">10</td>
<td width="101">IT</td>
<td width="101">&nbsp;</td>
</tr>
<tr>
<td width="97">20</td>
<td width="101">HR</td>
<td width="101">&nbsp;</td>
</tr>
<tr>
<td width="97">30</td>
<td width="101">Sales</td>
<td width="101">&nbsp;</td>
</tr>
</tbody>
</table>
&nbsp;Emp &ndash;address
<table>
<tbody>
<tr>
<td width="151">EDI</td>
<td width="151">Address</td>
</tr>
<tr>
<td width="151">1</td>
<td width="151">HYD</td>
</tr>
<tr>
<td width="151">2</td>
<td width="151">MUM</td>
</tr>
</tbody>
</table>
&nbsp; Sel * from emp.e, dept.d where did = d.did
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a></li>
<li><a title="Working with Project sessions in TeraData" href="woriking-project-sessions-teradata" target="_blank" rel="noopener">Working with Project sessions in TeraData</a></li>
</ul>

Query Importing and Exporting

Query Importing and Exporting in TeraData

query-importing-exporting-teradata

&nbsp;
<h2>Project sessions</h2>
Project &nbsp;- Collection of steps or processes or an approach to Implement task or operation or business.
(OR)
It is a process where a set of tasks define and implement this task.
With a standard model
Ex:&nbsp;IT Project
Here the model is SDLC And the business operation is a task
software development life cycle &ndash; SDLC
If we implement this task by using this model then it is &ldquo;IT&rdquo;
Project
&nbsp;
<img class="alignnone size-full wp-image-9917" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_923.png" alt="Project session" width="417" height="105" />
&nbsp;
<h2>NFI[National From insurance] organization &nbsp;</h2>
Day to day operations(Source system)
<h3>Q) What are General metting happen in a project?</h3>
Ans:
<ul>
<li>Project Tender meeting(SLA)Service Level agreement</li>
<li>KICK OFF Meeting[Project initial meeting/introduction meeting]</li>
<li>Status/schedule Meeting(Weakly, daily)</li>
<li>Delivery client Regular</li>
<li>Delivery Meeting&agrave; Project MANAGER Meeting</li>
<li>Team Meeting</li>
<li>Clarification defect resolution Meeting Developer&agrave;Lead&agrave;PM&agrave;Client</li>
<li>Discussion Meeting</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata DBA Developer? Then here is the blog post on, explore <a title="Teradata DBA Training" href="teradata-dba-training" target="_blank" rel="noopener">Teradata DBA Training</a>
</blockquote>
<h2>Development Responsibility</h2>
<h3>Documents in</h3>
<ul>
<li>LLD/Micro Design/Source map/target 2 sources/source to target mapping etc.</li>
<li>HLD</li>
<li>CR(Change Request)</li>
<li>Defect Tract sheet etc</li>
</ul>
<h3>Document out</h3>
<ul>
<li>Generated code</li>
<li>Unit-test code results</li>
<li>Defect Resolution steps</li>
<li>HLD LLD</li>
</ul>
<h3>Q) What is HLD And What it contains?</h3>
Ans:
<ul>
<li>HLD[High-Level Design]</li>
<li>It Displays the scope and various sub-components of corresponding tasks along with the relationship</li>
<li>It is platform-independent</li>
<li>Generally Represented with Diagrams[either data flow/e-R Diagrams etc]</li>
</ul>
<h3>Q) What is LLD And What it contains? </h3>
Ans: Low-Level Design[LLD]
It contains the below information
<ul>
<li>Source and its structure</li>
<li>Destination and its structure</li>
<li>Source to Destination business or mapping Rules</li>
<li>To Develop the coding this document Required[Generally Developer use these documents]</li>
</ul>
<h3>Q) Most of the organization there maintained LLD Document in excel sheet very few organization in word Doc Format</h3>
Ans: &nbsp;
What is mean by unit testing, who is Responsible for that How do you do that?
<ul>
<li>A testing individual unit of code is called unit testing</li>
<li>It is the responsibility of the Developer</li>
<li>Generally, we are keeping unit test cases and Result in the separate worksheet of excel sheet</li>
</ul>
&nbsp; &nbsp; <img class="alignnone wp-image-9918" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1024-460x175.png" alt="Project sessions" width="637" height="242" /> &nbsp;
<h3>Q) Explain various testing phases in our project?</h3>
Ans:
a)Unit test:
<ul>
<li>This can also be called white box testing</li>
<li>An individual unit of code tested here</li>
<li>It is the developer responsibility</li>
<li>b)System testing[Functional/block box test]</li>
<li>The functionality of the component tested here</li>
<li>It is the responsibility of the quality analyst(tester)</li>
<li>User accepting and testing(UAT) End-user Testing</li>
</ul>
Other testing
<ol>
<li>Silk Testing</li>
<li>Regression Testing</li>
<li>Integration testing</li>
</ol>
<h3>Q) What is the project life cycle model your using project describe me the steps?</h3>
Ans: We are using the waterfall model
other models
<ul>
<li>V Model</li>
<li><a href="agile-tutorials" target="_blank" rel="noopener">Agile </a>model (Frequently used in real-time)</li>
<li>Spiral model</li>
<li>Rapid model</li>
<li>Increment model Etc. &nbsp;</li>
</ul>
Steps
<ol>
<li>Requirement Gathering</li>
<li>Analysis</li>
<li>Design</li>
<li>Coding</li>
<li>Testing</li>
<li>Implementation and maintained</li>
<li>SLA Support</li>
</ol>
SLA=Service Level Agreement &nbsp;
<h3>Q) What is your data warehouse model and Approach?</h3>
Ans: Our model is &ldquo;3NF&rdquo;
[Other models STAR SCHEMA, Snow FLAKE SCHEMA, INTEGRATED SCHEMA, etc ]
&agrave;Data warehouse approach is Top-down Approach[WMON Approach]?
Ans: Top-down Approach
<h3>Q) How Do you Get the input file what is their size?</h3>
Ans:
<ol>
<li>Generally, we are Getting flat-file and the client keeps the file in a shared derive/location on the server</li>
<li>These file sizes are range from MBs To GB</li>
</ol>
<h3>Q) How do you Get Document HLD, LLD Business, etc interview project?</h3>
Ans: In my old project we use to get the document through the mail in my current project we use to getting from the share point server by using user id, and password &nbsp;
<h3>Q) What do you mean by source system and tell some of the source system names?</h3>
Ans: It is a name given to a particular process where the data source in the database, this system are input to the &nbsp;were house
Ex:
<ul>
<li>LA(Life ASIA)</li>
<li>AMS(AGENCY MANAGEMENT system)</li>
<li>PAY+</li>
<li>WEGA</li>
<li>Gold Mind</li>
<li>ETC</li>
</ul>
Note
<ul>
<li>Do not use the above names in interviews, instead use your corresponding project source system names</li>
<li>It do not the name create/add/put your own name &nbsp;</li>
</ul>
<h3>Q) What is the staging area and how it help us? where do you have the staging in your project?</h3>
Ans:
<ul>
<li>An intermediates storage area also be called a staging area.</li>
<li>Generally, in between OLATP System data warehousing, we have the staging area.</li>
<li>Here having&nbsp; 2 stagings in my project</li>
<li>At the operating system level Unix</li>
<li>At the data warehouse system level in tera data</li>
</ul>
<h3>Q) What are the scheduling tools you use in your project, how are doing scheduling?</h3>
Ans: As a Developer, I am not doing that, my onsite counterpart is doing the scheduling. I heard that they are using, CONTROL-M, for this scheduling &nbsp;
Other scheduling tools
<ul>
<li>Auto sys</li>
<li>$U.</li>
<li>TRILLIUM, CRONTab, AT Commands in union &nbsp; &nbsp;</li>
</ul>
<h3>Q) How are you maintaining versioning in your project?</h3>
Ans: We are using PVCS for maintaining Version PVCS &ndash; P Version control system
Other version Tools:
<ul>
<li>CVS;</li>
<li>VSS=Visual source safe</li>
<li>Clear case.</li>
<li>Quality center etc &nbsp;</li>
</ul>
&nbsp;
<img class="alignnone wp-image-9919" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1120-460x322.png" alt="Project Sessions" width="433" height="303" /> &nbsp;
&nbsp;
<table>
<tbody>
<tr>
<td width="160">
Version
</td>
<td width="160">
Doc name
</td>
<td width="160">
creation/conducting modify write date
</td>
<td width="160">
Description
&nbsp;</td>
</tr>
<tr>
<td width="160">1</td>
<td width="160">Abc.txt</td>
<td width="160">Vinay,15 Nov 2011</td>
<td width="160">Initial Release change date</td>
</tr>
<tr>
<td width="160">2</td>
<td width="160">Abc.txt</td>
<td width="160">Madhu,16 Nov 2011</td>
<td width="160">LLD322</td>
</tr>
</tbody>
</table>
&nbsp; &nbsp; <img class="alignnone wp-image-9920" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1215-460x315.png" alt="Teradata project Architecture" width="593" height="405" /> &nbsp;
&nbsp;
<h3>Steps</h3>
<ul>
<li>There is a scheduled process to get the day to day data from the source system to the <a href="unix-introduction" target="_blank" rel="noopener">Unix</a> stage server</li>
<li>From Unix stage to tera data stage actual operations are performed by using BTEQ, FL, ML Information, Data stage</li>
<li>Once the staging database is ready now there is a load from stage 2 actual destination.</li>
<li>During this load, if there any exception there moving into the error database</li>
<li>Once the target database is ready the reporting people there generate cubes and creating various Reports from inuring.</li>
</ul>
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="TPUMP Structure and Process In TeraData" href="tpump-structure-process-teradata" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
<li><a title="Working with Operators in TeraData" href="working-operators-teradata" target="_blank" rel="noopener">Working with Operators in TeraData</a></li>
</ul>

Woriking with Project sessions in TeraData

woriking-project-sessions-teradata

&nbsp;
<h2 style="text-align: justify;">General Developer Responsibilities</h2>
<ul style="text-align: justify;">
<li>Getting and understanding given documents, if there are any errors raising the request, once there are clarified working on them</li>
<li>After finishing every task, need to do reconciliation and unit testing</li>
<li>Identifying long-running queries, scripts, and tuning</li>
<li>Understanding various indexes and using appropriately in the queries</li>
<li>Identifying in the eliminating spool spaces, skewness issues</li>
<li>According to the business functionality, creating, manipulating various scripts of BTEQ, FL, ml, etc.</li>
<li>Having good knowledge of data and dimensional modeling</li>
<li>Working with various Unix commands day to day like LS, Grep, Wc, SCd, TEE, etc</li>
<li>As a Teradata developer, responsible for maintaining all DBA functions (development, test, production) in operation 24&times;7.</li>
<li>Performance tuning, including collecting statistics, analyzing explains &amp; determining which tables needed statistics. Increased performance by 35-40% in some situations.</li>
<li>Multiload, BTEQ, created &amp; modified databases, performed capacity planning, allocated space, granted rights for all objects within databases, etc</li>
<li>Moved databases between machines.</li>
<li>Installed patch sets and upgraded Teradata.</li>
<li>Automated data warehousing and datamart refreshes using the Transportable tablespace option.</li>
<li>Worked on creating and managing the partitions.</li>
<li>Performed database health checks and tuned the databases using Teradata Manager.</li>
<li>Deliver new and complex high-quality solutions to clients in response to varying business requirements and Creating and managing user accounts.</li>
<li>Installed Teradata drivers for the Teradata utilities.</li>
<li>Refreshed the data by using fast export and fastload utilities.</li>
<li>Used Teradata Administrator and Teradata Manager Tools for monitoring and control the system.</li>
<li>Creating and modifying MULTI LOADS for Informatica using UNIX and Loading data into IDW.</li>
<li>Loading data from various data sources and legacy systems into Teradata production and development warehouse using BTEQ, FAST EXPORT, MULTI LOAD, FASTLOAD, and Informatica.</li>
<li>With the in-depth expertise in the Teradata cost-based query optimizer, identified potential bottlenecks with queries from the aspects of query writing, skewed redistributions, join order, optimizer statistics, physical design considerations (PI and USI and NUSI and JI, etc), etc. In-depth knowledge of Teradata Explain and Visual Explain to analyze and improve query performance.</li>
<li>Working as an On-Site Teradata Lead DBA, doing DBA activities that are Implementation of data and table-level security, setting up access rights, and space rights for the Teradata environment. Performance tuning, Monitoring, and reporting Review system utilization by the user.</li>
<li>Acted as a liaison between the offshore and onsite teams.</li>
</ul>
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3 style="text-align: justify;"><img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Responsibility of production support people?</h3>
<ul style="text-align: justify;">
<li>Monitoring the given scheduling tool</li>
<li>For the fail jobs need to raise defects in defect training tools clear quest, drop a mail to the production manager/production head</li>
<li>Production vendor</li>
</ul>
Production manager
Development Lead/manager
Developer
Note: For seeing the data in flat file Generally we go for ULTER Real-time
<h3 style="text-align: justify;"><img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />How do you connect your server and how are you working with your corresponding tools?</h3>
<img class="alignnone size-medium wp-image-9921" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1315-460x212.png" alt="TCS" width="300" height="138" />
Start<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Run<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />MSTSC[Remote Desktop server]
<h3 style="text-align: justify;"><img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />What type of data loading in your project?</h3>
There are different load
A)Indirect load
i)Incremental(or)Delta lad
ii)Histronized
B)Direct load
<h3 style="text-align: justify;"><img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />What is the Granularity of your project?</h3>
We having Granularity, the lowest Granularity is day wist
The level of data loading
<h3 style="text-align: justify;"><img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Explain the semi-complex issue in your project?</h3>
Having duplicate in the tables(pay using queries we use to resolve it)
Working with error table and exception handling table[identifying the error in the tables reflecting file, table later loading it]
Multiple record opening issues
Multiple record Closing issues
[we had a separate process to resolve this issue]
<img class="alignnone wp-image-9923" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1413-460x163.png" alt="Multiple Record" width="642" height="227" />
Spool space issue and Resolution, identifying skewness and resolution identifying long Running query in tuning, etc.
<h3 style="text-align: justify;"><img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Tell some of your Dimension and fact tables in your project?</h3>
&nbsp;Refer to the material/Blog for dimension fact table structure and their Logic.
<h3 style="text-align: justify;"><img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />HR Question?</h3>
<ul>
<li style="text-align: justify;">Can you start some thinking your side</li>
<li style="text-align: justify;">Can you tell some thinking your self</li>
</ul>
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="Query Importing and Exporting in TeraData" href="query-importing-exporting-teradata" target="_blank" rel="noopener">Query Importing and Exporting in TeraData</a></li>
<li><a title="Working with Project sessions in TeraData" href="woriking-project-sessions-teradata" target="_blank" rel="noopener">Working with Project sessions in TeraData</a></li>
</ul>

General Developer responsibilities in Teradata

General Developer Responsibilities in TeraData

general-developer-responsibilities-teradata

&nbsp;
<h2>Resume preparation </h2>
Full name:
Email: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Mobile No: &nbsp; &nbsp;
Objective
Below 3 year
3+ years&gt;Desire, passion, excel &nbsp;
<h2>**Professional summary</h2>
<ul>
<li>7 to 8 lines (min)</li>
<li>Your overall experience, Roles and importance, Responsibilities</li>
<li>Don&rsquo;t add what you know more and more add what you have experience</li>
<li>Total IT Experience and relevant IT Experience</li>
</ul>
<h2>Certification:</h2>
<h3>Achievements </h3>
Best Team player from my manager &nbsp;
<h3>Professional organization</h3>
Each organization only one line we can write
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Working with Sathyam with Hyd as a senior developer from Nov-11-2014 &nbsp;
<h3>Education Background</h3>
<img class="alignnone wp-image-9935" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1512-460x121.png" alt="Education Background" width="462" height="120" /> &nbsp;
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3>Other Technical Skills </h3>
<ul>
<li>OS: WIN, UNIX</li>
<li>Script: Javascript, VB Script</li>
<li>ETL Tools</li>
<li>Reporting Tools</li>
<li>Scheduling tools; &nbsp;</li>
</ul>
<h3> Recent Accompligment&nbsp;</h3>
<ul>
<li>Project Handle</li>
<li>Project H</li>
<li>Project Name</li>
<li>Client Name</li>
<li>Duration</li>
<li>Team size</li>
<li>Environment</li>
<li>Role</li>
</ul>
<h3>Description </h3>
Min 6 to 7 lines we can write
Responsibility/contributions;
Min 7 lines, with respect to the project with responsive differential Responsibility of one project to another project. &nbsp;
<h2>Addition information/personal details</h2>
<ol>
<li>Full name</li>
<li>Language Know</li>
<li>Pan card/passport</li>
<li>Any valid visa</li>
<li>Material status</li>
</ol>
Note&nbsp;-&nbsp;Do not add gender, permanent address, contact address, habits, interest, etc. &nbsp;
<h2> Declaration </h2>
The above furnish down acknowledge (Sd.messaval)
<h3>Font Size </h3>
Temesacew&lt;12 &nbsp; &nbsp;
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="teradata-interview-questions-answers" target="_blank" rel="noopener">Teradata Interview Questions</a>

Resume Preparation for Teradata

Resume preparation in TeraData

resume-preparation-teradata

<h2>Tera data parallel Transpecter[TPT]</h2>
Step1: To work with TPT Java Run time should be install[JDK-6025 Java ER] There are 2 ways to work&nbsp; TPT
<ol>
<li>Through wizard</li>
<li>By writing script to execute like FL, ML Name: Script</li>
</ol>
Ex: 1st way &nbsp; Wizard approach To load the data from vinayaka database emp Table to vinay- USK emp empty table following the below steps Start
<h2>&nbsp;&darr;</h2>
Right click
<h2>&nbsp;&darr;</h2>
New Job Name: Vinayaka- vinay-emp-load Next Source type: Tera data Table Tera data source:127.0.0.1 User name: Vinayaka Pass work: vinay <img class="alignnone size-full wp-image-9941" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1611.png" alt="Screenshot_16" width="33" height="29" />&nbsp;Check include user ID in Generate Job <img class="alignnone size-full wp-image-9941" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1611.png" alt="Screenshot_16" width="33" height="29" />Check include password in Generate Job <img class="alignnone size-full wp-image-9942" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1710.png" alt="Screenshot_17" width="79" height="54" /> &nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Go to left hand side displayed data bases Vinayaka<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Select emp Select Right side emp columns<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Click select all Next Select Target type: Tera data table Tera data host:127.0.0.1 User name: Vinayaka Pass word: Vinay <img class="alignnone size-full wp-image-9941" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1611.png" alt="Screenshot_16" width="33" height="29" />Check include user ID in Generate Job <img class="alignnone size-full wp-image-9941" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1611.png" alt="Screenshot_16" width="33" height="29" />&nbsp;Check include password in Generate Job <img class="alignnone size-full wp-image-9942" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1710.png" alt="Screenshot_17" width="79" height="54" /> &nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Go to left hand side displayed data bases Vinay- use<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Select emp Select&nbsp; load operator &nbsp; Select Automatic mapping Next
<h2>&darr;</h2>
Finish &nbsp; Job name: Test- job Commit interval:10000 <img class="alignnone size-full wp-image-9942" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_1710.png" alt="Screenshot_17" width="79" height="54" /> &nbsp; New the Job runs, read the log message and observe the expected resultClick Note -&nbsp;To see the script Generated, GO To Job script and see &nbsp; 2nd way Coding Define Job vinayaka- vinaya-load-job ( Define operator w-1-0-vinayaka-vinay-load-job Type load Schema* Attributes ( Varchar User name, Varchar&nbsp; Password, Varchar Log table, Varchar Target Table Varchar Td pid ); Define schema w-o-s-Vinayaka &ndash;vinay&mdash;load- job ( eid int, Ename varchar(30), Mgrid int, Deptid int, ); Define operator w-o-c-Vinayaka &ndash;vinay&mdash;load- job Type export Schema w-o-s-Vinayaka &ndash;vinay&mdash;load- job Attributes ( Varchar User name, Varchar&nbsp; user Password, Varchar Select stmt, Varchar Td pid ); Apply ( Insert into vinay &ndash;usr.emp(eid, ename, mgrid, deptid) values(:eid,: ename, :mgrid, :deptid); To operator ( W-1-0- Vinayaka-vinay-load-Job[1] Attributes ( User name=&rsquo;vinay -usr&rsquo;, User password=&rsquo;vinay&rsquo;; Log table=&rsquo;vinay&rsquo;-usr. emp-Log&rsquo;, Target table=&rsquo;vinay&rsquo;-usr. Emp&rsquo;, Tdpid=&rsquo;127.0.01&rsquo; ) ) Select * from operator (w-o-o-Vinayaka &ndash;vinay&mdash;load- job[1] Attributes ( User name=&rsquo;vinayaka&rsquo;, User password=&rsquo;vinay&rsquo;, Sele CL Stmt=&rsquo;Select eid,ename, mgrid, deptid from vinayaka .emp;&rsquo;, Tdpix=&rsquo;127.0.0.1&rsquo; ); &nbsp; Execution&nbsp;-&nbsp;&nbsp;Log file navigation<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> c:\program files\Tera data\client\13.0\Tera data parallel transporter\logs Start<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Run<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />cmd TBUILD &ndash; F C:\TPT\TPT-Sample.txt &nbsp; TPT[Tera data parallel Transporter] &nbsp; <img class="alignnone size-full wp-image-9947" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_188.png" alt="Screenshot_18" width="356" height="140" /> &nbsp; Fast load: File &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Table Mload: File &nbsp; &nbsp; &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Table Tpump: File &nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp; Table Fast Export: Table <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;File BTEQ: File &nbsp;<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> Table Table<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />File &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Any operator in tera data Table <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />&nbsp;File &nbsp; TPT IS A Multi function product for loading, Retrieving, processing and transforming data with in the tera data base and between external data base and the tera data data base &nbsp; <img class="alignnone wp-image-9948" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_199-460x122.png" alt="Screenshot_19" width="494" height="130" /> <img class="alignnone wp-image-9949" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_209-460x409.png" alt="Screenshot_20" width="480" height="426" /> &nbsp;

Tera data parallel Transpecter [TPT]

Tera data parallel Transpecter[TPT]

tera-data-parallel-transpectertpt

&nbsp;
<h2>Operators: </h2>
TPT Performs operations that are extract, load, transform, etc to the below 4 operations
<ul>
<li>Producer operator</li>
<li>consumer operator</li>
<li>Filter operator</li>
<li>Standalone operator</li>
</ul>
<h3>a)Producer operator</h3>
Reads data source and writes to data streams the below operators are available in producer operator
<table>
<tbody>
<tr>
<td width="319">Operator</td>
<td width="319">Description</td>
</tr>
<tr>
<td width="319">EXPORT</td>
<td width="319">Fast export utility to export data to file form tables.</td>
</tr>
<tr>
<td width="319">SQL Selector</td>
<td width="319">Select data form tera data tables with SQL Sessions</td>
</tr>
<tr>
<td width="319">Data connector</td>
<td width="319">Reads data from external files.</td>
</tr>
<tr>
<td width="319">ODBC Operator</td>
<td width="319">Extract data from any ODBC Provider on windows, unix, MVS platforms.</td>
</tr>
</tbody>
</table>
&nbsp; <img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Fast load in mod adaptor
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Multi load in mod adaptor &nbsp;
<h3>b)Consumer operator</h3>
Reads data from data streams and writes to a tera database the below sub operators are available
<table>
<tbody>
<tr>
<td width="319">Operator</td>
<td width="319">Description</td>
</tr>
<tr>
<td width="319">Load Operator</td>
<td width="319">Implement Fast Load&nbsp; functionality, loading to empty tables</td>
</tr>
<tr>
<td width="319">Update Operator</td>
<td width="319">Implement multi-load functionality loading to 5 tables</td>
</tr>
<tr>
<td width="319">Stream Operator</td>
<td width="319">Implement TPUMP Protocol continuous loading data to tera data tables</td>
</tr>
<tr>
<td width="319">SQL Inserter</td>
<td width="319">Inserter data into tera data tables, with <a href="sql-tutorials" target="_blank" rel="noopener">SQL </a>session</td>
</tr>
<tr>
<td width="319">Data connector</td>
<td width="319">Writes data to an external file to access module</td>
</tr>
</tbody>
</table>
&nbsp;
<blockquote>
Inclined to build a profession as Teradata DBA Developer? Then here is the blog post on, explore <a title="Teradata DBA Training" href="teradata-dba-training" target="_blank" rel="noopener">Teradata DBA Training</a>
</blockquote>
&nbsp;
<table>
<tbody>
<tr>
<td width="319">PT Operator</td>
<td width="319">Max Tables</td>
</tr>
<tr>
<td width="319">Load</td>
<td width="319">1 &nbsp; &nbsp;</td>
</tr>
<tr>
<td width="319">Update</td>
<td width="319">5</td>
</tr>
<tr>
<td width="319">Stream</td>
<td width="319">127</td>
</tr>
<tr>
<td width="319">SQL Inserter</td>
<td width="319">1</td>
</tr>
</tbody>
</table>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Fast export out mod adaptor &nbsp;
<h3>c)Filter operator</h3>
Read and write data from and to stream and perform data transformation function such as selection, validation, conjoins
&nbsp; <img class="alignnone size-full wp-image-9950" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_2115.png" alt="Filter operator" width="429" height="44" /> &nbsp;
<h3>d)Standalone operator:</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Standalone processing that does not in value sending(or)Receiving to and from the data stream it does not operate on the data stream.
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />The below stand lone operators are available
<table>
<tbody>
<tr>
<td width="319">Operator</td>
<td width="319">Description</td>
</tr>
<tr>
<td width="319">DDL Operator</td>
<td width="319">Create indexes, tables, drop tables, delete table, replace, rename, macro, views, name, etc operations</td>
</tr>
<tr>
<td width="319">Update operator</td>
<td width="319">Use when performing delete the task and there is no data need for the delete SQL Request</td>
</tr>
<tr>
<td width="319">OS Command</td>
<td width="319">It executes os command on the client system</td>
</tr>
</tbody>
</table>
&nbsp;
<img class="alignnone wp-image-9952" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_2213-460x171.png" alt="Standalone Operator" width="668" height="247" />
&nbsp; <img class="alignnone wp-image-9953" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_2311-460x366.png" alt="Standalone Operator" width="426" height="338" /> &nbsp;
<h2>Object Definition</h2>
<ul>
<li>Define Job</li>
<li>Define Schema</li>
<li>Define operator</li>
<li>Define DBMS</li>
<li>Define Table</li>
<li>Define table ser&nbsp;</li>
</ul>
<h3>Define operator</h3>
Takes operator, operator attributes &nbsp;
<h3>Define schema</h3>
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Define data structure the data an operator will process
<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" />Unique structure for every object &nbsp;
<h2>Important command line structure</h2>
<h3>Tbulid&nbsp;&nbsp;</h3>
Runs tera data PT Job parallel transporter
<h3>Tlog view&nbsp;</h3>
View the content of log file produced for running a tera data-parallel transporter job &nbsp;
<h3>Operator syntax:</h3>
Type&lt;operator&gt;
Schema&lt;name&gt;(or)*
&nbsp;
&nbsp; <img class="alignnone wp-image-9954" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_2410.png" alt="Attributes" width="238" height="115" /> &nbsp;
&nbsp;
Example
Type load
Schema se- party
Attribute
(
&nbsp; ----
----
) &nbsp;
&nbsp;
<h2>Sessions and Instance</h2>
Sessions Take multiple connections to the script while processing
Generally fast load, multi-load, TPUMP, and fast export use session.
Instance is used by the operators to process the data gallery
Ex: min sessions=2;
Max sessions=8; &nbsp;
<h3>Instance</h3>
Syntax operator:[NUMBER]
EX: Load- party[4] &nbsp; &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="Teradata Data Mover" href="teradata-data-mover" target="_blank" rel="noopener">Teradata Data Mover</a></li>
<li><a title="Teradata Wallet" href="teradata-wallet" target="_blank" rel="noopener">Teradata Wallet</a></li>
</ul>

Operators in TeraData - Tekslate

Working with Operators in TeraData

working-operators-teradata

&nbsp;
<h2 style="text-align: justify;">Data Mover&nbsp;</h2>
Moving data from one system to another is a key part of your analytical ecosystem operations. Well-planned monitored, and managed data movement is essential to the effective operation of your overall ecosystem.
Whether your goal is to complete a onetime copy of data from your enterprise data warehouse (EDW) to your test system or to regularly and continuously synchronize a dual system environment, you need confidence that the data will be in the right place at the right time while causing the smallest possible impact on the data warehouse&lsquo;s mission of serving your business.
The decision about the best way to copy data between <a href="http://tekslate.com/tutorials/teradata/" target="_blank" rel="noopener">Teradata systems</a> depends on many factors, including the data model and indexes present, other workloads in your system, and additional load jobs underway
What you need is a solution that automates the data movement process, a solution that also accommodates the many situations found within your comprehensive analytical ecosystem. And that&lsquo;s exactly what Teradata&reg; Data Mover delivers to you and your business: all the supporting capabilities you need to easily, effectively &ndash; and affordably &ndash;meet your data movement demands
<h2 style="text-align: justify;">Intelligent Selection</h2>
Teradata Data Mover is a utility designed specifically to copy data and objects such as tables and statistics from one Teradata system to another. It takes advantage of the built-in underlying technologies already in your Teradata system. And it understands the available techniques you have for extracting data from or loading data into your Teradata Database, as well as our various load utilities, backup/restore products, and the <a class="zem_slink" title="JDBC driver" href="http://en.wikipedia.org/wiki/JDBC_driver" target="_blank" rel="noopener wikipedia">JDBC driver</a>.
<pre style="text-align: center;">Desired to gain proficiency on Teradata? Explore the blog post on <a href="http://tekslate.com/teradata-training/" target="_blank" rel="noopener">Teradata Training in Bangalore</a> to become a pro in Teradata.</pre>
<h2 style="text-align: justify;">Leverage Existing Technologies</h2>
Teradata Data Mover leverages all of the established data loading protocols and technologies to meet your needs and environment best.
You can use the utility for:
&amp;gt; Regular periodic tasks including populating a dependent data mart from an EDW.
&amp;gt; Special one-time tasks such as moving data to a test system.
The utility also works with every member of our <a href="teradata-components" target="_blank" rel="noopener">Teradata</a> Purpose-Built Platform Family.
In addition, it supports Teradata Database V2R6.1 and higher and runs on a Teradata Managed Server for high availability and comprehensive monitoring.
<h2 style="text-align: justify;">An End-to-End Management Solution</h2>
You can also integrate data movement within your comprehensive ecosystem management and load jobs and automate.
Teradata Data Mover for your production execution needs. That&lsquo;s because the utility can be tightly integrated with Teradata Multi-System Manager for end-to-end management of changes to your environment.
The utility works with Teradata Multi-System Manager to support these commands and status messages:
&amp;gt; Send event for the start of a job.
&amp;gt; Send event for the end of a job.
&amp;gt; Send event for a job in progress.
And Teradata Data Mover can be integrated with load processes to copy data to a second system. That means you can copy data from an EDW to a data mart as soon as the data are loaded into the EDW.
Teradata Data Mover also provides safeguards to force the direction of data movement and eliminate any possibility of copying data in the wrong direction. Teradata Data Mover not only copies data, but reduces operational cost and complexity and improves your overall data management capabilities by copying an array of objects, including Tables, PPI tables, Global Temp Tables, Tables with LOB columns, Statistics, <a href="http://tekslate.com/triggers-in-teradata-with-examples/" target="_blank" rel="noopener">Triggers</a>, Join Indexes, Hash Indexes, Tables with UDT columns, and Users.
<pre style="text-align: center;">Check out the top <a href="http://tekslate.com/teradata-interview-questions-and-answers" target="_blank" rel="noopener">Teradata&nbsp;Interview Questions</a> now!</pre>
<h2 style="text-align: justify;">Swift, Simple Data Movement</h2>
Your analytical ecosystem uses multiple Teradata systems, each with different roles.
For example, you may have a Teradata Active Enterprise <a class="zem_slink" title="Data warehouse" href="http://en.wikipedia.org/wiki/Data_warehouse" target="_blank" rel="noopener wikipedia">Data Warehouse</a> serving as an EDW, a Teradata Extreme Data Warehouse for deep history web click analysis, and a Teradata Data Mart Appliance as a test and development system.
As part of your critical ongoing maintenance, you must be able to move or copy data from one system to another. Teradata Data Mover simplifies data movement and enables process automation, control, and monitoring. It also provides a point-and-click user interface to enable powerful data movement commands.
<h2 style="text-align: justify;">Flexible, Powerful, Easy-to-Use</h2>
The value of any analytical system rests heavily on having the right data in the system. In fact, the very success of your enterprise can depend on the smooth operation of the data movement. That's why it's so vital that data be moved quickly and reliably between your Teradata systems. And you can add more value &ndash; and reduce operational costs &ndash; when you use production-quality tools to create standardized jobs, monitor status, and progress, and handle exceptions. Teradata Data Mover offers you a complete solution to improve your processes and reduce the cost involved in maintaining the analytical ecosystem. Take advantage of its scope of data movement technologies and breadth of control mechanisms. Whether it's event-driven for automatically moving newly loaded data or ad-hoc DBA-initiated copies for exception processing, Teradata Data Mover gives you the power and flexibility to strengthen your analytical environment and deliver even more value from your data.
For In-depth knowledge on Teradata click on:
<ul>
<li><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
<li><a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">TPUMP Structure and Process In TeraData</a></li>
</ul>

Teradata Data Mover - tekslate

Teradata Data Mover

teradata-data-mover

&nbsp;
Teradata Wallet is a facility for the storage of sensitive/secret information, such as Teradata Database user passwords. Users save and retrieve items by using the facility.
Wallet or tdwallet is the latest piece or pack of Teradata software, introduced in the latest Teradata Tools and Utilities packages those are version 14.00/15.00 and later, where the customers can store passwords/credentials (or other confidential information) securely and safely on client computers or application servers. This stored information is used while signing in to the Teradata Database using any utility or piece of software that uses Teradata.
<h2 style="text-align: justify;">Items</h2>
Teradata Wallet is a facility for the storage of sensitive/secret information, such as Teradata Database user passwords.&nbsp;Users save and retrieve items by using the facility.&nbsp;Each item has two parts:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(1) the name of the item, and
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(2) the value of the item.
Both &ldquo;Name&rdquo; and &ldquo;value&rdquo; of the item are sequences of <a href="https://en.wikipedia.org/wiki/Unicode" target="_blank" rel="noopener">Unicode </a>characters of arbitrary content.
&nbsp;An item name, (1), is used to uniquely select an item; for example, it is possible to retrieve the value of the item named "password_proddev".
An item value, (2), is the actual content of the item; for example, a Teradata Database user password, or a credit card number.
Items are stored in a wallet. Each user has exactly one wallet. Item names must be unique within a given wallet; for example, a specific user could only have a single item named "password_proddev", but two different users, say "User1" and "User2", could each have items named "password_proddev" with possibly different values.
<blockquote>
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore <a title="Teradata Training" href="teradata-training" target="_blank" rel="noopener">Teradata Training</a>
</blockquote>
<h3 style="text-align: justify;">CLIv2(Call Level Interface version2) to connect to the Teradata Database</h3>
Logging on to a Teradata Database requires the user to submit a password, which sometimes causes problems:
<ul>
<li style="text-align: justify;">Job scripts require the inclusion of a password, which is then exposed in plain text.</li>
<li style="text-align: justify;">Someone watches the user type in the password.</li>
<li style="text-align: justify;">Users with access to multiple database systems record their password son sticky notes in order to remember them.</li>
</ul>
Now, rather than placing passwords within job scripts or recording them on sticky notes, users can store passwords managed by Teradata Wallet.
<h2 style="text-align: justify;">Business Value</h2>
Teradata Wallet provides an easy method for making Teradata passwords, and therefore your Teradata data, more secure. It is especially beneficial for easy retrieval of passwords on application servers or other shared computers that host multiple users and connect to multiple databases.
<h2 style="text-align: justify;">Technical Overview</h2>
For this initial release, Teradata CLIv2 is the only connectivity interface that makes use of the Teradata Wallet. The following is a list of Teradata Client products that use Teradata CLIv2 to connect to the <a href="http://tekslate.com/tpump-structure-process-teradata/" target="_blank" rel="noopener">Teradata Database</a>:
<ul>
<li style="text-align: justify;">Basic Teradata Query Utility</li>
<li style="text-align: justify;">Teradata FastLoad</li>
<li style="text-align: justify;">Teradata MultiLoad</li>
<li style="text-align: justify;">Teradata Parallel Data Pump</li>
<li style="text-align: justify;">Teradata FastExport</li>
<li style="text-align: justify;">Teradata ARC</li>
<li style="text-align: justify;">Teradata Preprocessor 2</li>
<li style="text-align: justify;">Teradata Parallel Transporter</li>
</ul>
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" title="Teradata Interview Question and Answers" href="tutorialsteradata" target="_blank" rel="noopener">Teradata Interview Questions</a>
<h2 style="text-align: justify;">How it Works</h2>
Wallet data is segregated by client users and a given user can only access information from his/her own wallet.
<img class="size-full wp-image-20974 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.1253.png" alt="Teradata" width="835" height="535" />
The system will check David's wallet for the string that has the name―password_for_slugger‖ and would then access the encrypted value associated with it (g0t#L0st#).
<h2 style="text-align: justify;">Features</h2>
<h3 style="text-align: justify;">Use this feature when:</h3>
Users are running scripted applications: They can embed password retrieval syntax into scripts instead of compromising security by including a password.
Users are accessing multiple Teradata <a class="zem_slink" title="Database management system" href="http://en.wikipedia.org/wiki/Database_management_system" target="_blank" rel="noopener wikipedia">Database systems</a>: They can automatically retrieve the correct password for a system instead of having to remember the password or look it up.
<h3 style="text-align: justify;">Do NOT use this feature when:</h3>
All users log in to the same client system using the same login information (and as such are treated as the same user on the client system) AND each user has a separate Teradata Database user name and password. In this scenario, it would not be advisable to use the Teradata Wallet feature because the users would be able to access each other&lsquo;s Teradata Database password (since they would all be using the same wallet).
<h2 style="text-align: justify;">For In-depth knowledge on Teradata click on:</h2>
<ul>
<li style="text-align: justify;"><a href="http://tekslate.com/bteq-features-teradata/" target="_blank" rel="noopener">BTEQ Features in Teradata</a></li>
<li><a href="http://tekslate.com/secondary-index-teradata/" target="_blank" rel="noopener">Secondary Index in TeraData</a></li>
<li><a href="http://tekslate.com/memory-management-teradata/" target="_blank" rel="noopener">Memory Management in TeraData</a></li>
<li><a href="http://tekslate.com/types-olaps-teradata/" target="_blank" rel="noopener">Types of OLAP&rsquo;s in TeraData</a></li>
</ul>
&nbsp;