Thursday, 3 November 2016

Teradata Quick refresher for most popular Utilities

Teradata utilities are very important while working with SQL and in data warehousing projects. The below short and crispy definitions help you can use while attending for interviews or in your day to day work to refresh your Teradata knowledge.

  • MultiLoad - Inserts, updates, deletes, or upserts rows in a Teradata table by using the MLOAD command-line utility. It can handle multiple SQL statements in a single operation. You can provide user-defined SQL for complex operations. This stage is best used for bulk updates, deletes, upserts, and complex interface operations.
  • TPump - Inserts, updates, deletes, or upserts rows in a Teradata table using the TPUMP command-line utility. It can do concurrent updates on the same table. It can handle multiple SQL statements in a single operation. You can provide user-defined SQL for complex operations.
  • FastExport - Exports data from a Teradata table by using the FEXP command-line utility. You can customize it to use user-defined SQL statements for extraction. This stage is best used for bulk extracts from Teradata.
More:

Sunday, 25 September 2016

How to use Null value functions in Teradata for Handling Nulls

Teradata SQL Null functions
NULL functions
The below are the Null value functions in Tearadata. One is "NVL" and another one is "NVL2". These two functions are part of Teradata SQL.

NVL -The NVL function replace null value with a string or numeric value. This is the functionality is same as Teradata function NULLIF. It requires two arguments. If the value is not null, it will not do anything.

The best SQL NVL function:

SELECT NVL (NULL,'USA'), NVL('SPAIN','USA');

-----------

USA  SPAIN

More: Top 70 Teradata Interview Questions

The NVL2 function: Returns one of two specified values (arguments#2 and Arguments#3) based on weather the first arguments in NULL or not. It requires three arguments. The rule is as follows:

  • If expression 1 is not null, expression 2 is returned
  • If expression 1 is null, expression 3 is returned
The best NVL2  example

SELECT NVL2(NULL,'FRANCE','SPAIN'), NVL2('ENGLAND','FRANCE','SPAIN')

-------------------
SPAIN   FRANCE

Friday, 16 September 2016

How to Know Teradata is in Pseudo table Lock

The pseudo table mechanism is a way to serially queue all table level locks through the use of an internal pseudo table. In this table the database locks the row hash of the table's table ID.

What does” Pseudo Table” Locks mean in EXPLAIN Plan in Teradata?


It is a false lock which is applied on the table to prevent two users from getting conflicting locks with all-AMP requests. PE will determine an particular AMP to manage all AMP LOCK requests for given table and Put Pseudo lock on the table.

To put in Simple terms: its like an University with 10 gates and at a given time you can enter the university through one gate ( or main gate) for security check.

The best Teradata Video Tutorials




Thursday, 8 September 2016

How to have Teradata determined primaryindex the right way

How to have Teradata determined primaryindex the right way
#How to have Teradata determined primaryindex the right way
Every table must have at least one column as the Primary Index. The Primary Index is defined when the table is created.There are two reasons you might pick a different Primary Index then your Primary Key. They are (1) for Performance reasons and (2) known access paths.

Primary Index Rules

  • Rule 1: One Primary Index per table.
  • Rule 2: A Primary Index value can be unique or non-unique.
  • Rule 3: The Primary Index value can be NULL.
  • Rule 4: The Primary Index value can be modified.
  • Rule 5: The Primary Index of a populated table cannot be modified.
  • Rule 6: A Primary Index has a limit of 64 columns.

Two Types of Primary Indexes (UPI or NUPI)

Unique Primary Index(UPI)
A Unique Primary Index (UPI) is unique and cannot have any duplicates. If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. An UPI enforces UNIQUENESS for a column

Non-Unique Primary Index (NUPI)
A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. Duplicate values can exist.

What happens when user not defined Primary index

Teradata assigns first column of table as Primary index, when not defined during creating a Table. But always the best practice is need to define primary index during Table creation time. Else, you will get UNIQUENESS violation errors, since the first column of table may not follow uniqueness rules.

Wednesday, 7 September 2016

34 Powerful Complex Teradata Architectural Questions and Answers

Teradata Architectural Questions
Teradata Architectural Questions 


QUESTION No:1


An administrator is trying to manage resources on the system because there is a business critical reporting requirement. The reporting workload starts at 9AM and is required to finish by 10AM. At the same time, ad hoc users are allowed to access the system. The administrator is building a Query Resource Throttle in TDWM to better manage the resources. Which two are available as options for building the throttle? (Choose two.)


A. Index Scan Types Allowed


B. Throttle All-AMP Queries Only


C. No Aggregate Functions Allowed


D. Only limit queries with Step Time Threshold


Answer: C,D


QUESTION NO: 2


An administrator is asked to keep a daily backup of a very large transaction table. The table is defined with a PPI with a daily partitioning expression on insert date and the table is insert only. There is not enough of a window to backup the entire table each night. In this situation, which backup strategy is the best approach?


A. Implement a cluster backup of the table.


B. Implement an online archive of the table.


C. Implement an all-AMP backup of the table.


D. Implement partition backup for the previous day.


Answer: C


QUESTION NO: 3


Which two statements are true about FastLoad capabilities regarding error handling, return codes, and handling statistics about the load job? (Choose two.)


A. The predefined system variables, prefixed with &SYS, are not implemented in FastLoad.


B. Notify Exit routines specify a predefined action to be performed whenever a failure occurs in the utility. The action only handles return codes.


C. Predefined system variables, prefixed with &SYS, are used to capture load statistics and can be used to determine if the process ran


successfully.


D. Notify Exit routines specify a predefined action to be performed whenever a failure occurs in the utility. The action can handle return codes and


load statistics.


Answer: B,C


QUESTION NO: 4


A customer has a requirement to load data from a single message queue into a Sales table that is part of an active data warehouse. The Sales table has no additional indexes. The data latency requirements are daily. What is the most effective approach to support this requirement?


A. Use TPump to continuously read from the queue and insert the data.


B. Pull the data from the queue into a file and use TPump to insert the data from the file.


C. Pull the data from the queue into a file and use MultiLoad to insert the data from the file.


D. Pull the data from the queue into a file and use FastLoad to load the data into a staging table and then use SQL INSERT SELECT.


Answer: B


QUESTION NO: 5


Which type of physical device, in addition to Enterprise System Connection (ESCON), is required to allow a mainframe in one city to connect to a Teradata Database in another city?


A. ESCON Director


B. Block Multiplexer


C. Channel Extender


D. BYNET Optical Extension (BYOX)


Answer: A


QUESTION NO: 6


Which method can be used to address the privacy requirements of an application that accesses personal information?


A. Use Access Logging to determine the requesting user, type of access, and the referenced object.


B. Extract the data, define a masking algorithm, and insert the data, with masking, into the response.


C. Use multi-factor authentication to provide strong access control and enhance the safety of data stored in the database.


D. Create a user defined function that can only be accessed from within the application to encrypt and decrypt the personal data.


Answer: A


QUESTION NO: 7


An administrator needs to establish a data warehouse architecture for a user community of 2000 end-users and a data integration group of 20 users. Which three items are important when creating the database hierarchy to support these two groups of users? (Choose three.)


A. priorities


B. roles and profiles


C. implicit access rights


D. access and DBQL logging


E. permanent space allocation


F. archive and restore operations


Answer: A,D,F


QUESTION NO: 8


A programmer has executed a query and wants to know performance statistics. Upon investigating DBQL the query is not found. The programmer wants to see the query. Which two administrative options are available for making DBQL available sooner? (Choose two.)


A. End logging on any user will flush the cache.


B. Reduce the setting of the dbscontrol parameter dbscachectrl.


C. Reduce the setting of the dbscontrol parameter dbqlflushrate.


D. End logging on systemfe is the only user that will allow the cache to be flushed manually.


Answer: B,C


QUESTION NO: 9


A new workload management practice is being established without the involvement of application or user communities. What information should the administration teams collect to establish the workload management policy?


A. AMPUsage


B. DBQL.QryLog


C. ResUsageSpma


D. Metadata services


E. Dictionary access counts


Answer: A


QUESTION NO: 10


A critical customer identification application is used by all end users. Several sets of tactical queries have been created and deployed which select and transform the application data to support day-to-day credit extension, account management, and offer delivery decisions. Over time, 15 single and multi-table join indexes have been created on the critical tables to attempt to cover these tactical queries. A new set of tactical queries is being developed to support a mandated fraud reporting initiative required by the government. The application development team recommends adding another two multi-table join indexes to the application tables. The ETL team is consistently missing the required load window for this data which has resulted in financial penalties to the company for unmet Service Level Agreements (SLAs). The database administrator has been asked to review the database architecture and recommend potential solutions.


Which statement is true about using join indexes in the existing database architecture?


A. The new set of tactical queries might use more than one of the existing join indexes and therefore not require the new join indexes.


B. There is a limit of 16 join indexes per table so the addition of the two new join indexes may necessitate the removal of at least one of the existing join indexes.


C. The number of times a query (for which a join index was designed) must be executed to offset the creation and maintenance time of the join index should be calculated.


D. As new rows are added to the base tables, multiple join indexes can be updated as part of the same transaction so the incremental cost of adding new join indexes is mitigated.


Answer: A


Read -Page two Questions

Saturday, 27 February 2016

12 Frequently Used File Control Commands in BTEQ utility

12 Frequently Used File Control Commands in BTEQ utility
12 Frequently Used File Control Commands in BTEQ utility:
The below list shows ultra frequently used commands in BTEQ utility.

DEFAULTS: Reset all BTEQ format command options to their defaults. This will utilize the default configurations

ECHOREQ- Enable the Echo required function in BTEQ returing a copy of each Teradata SQL request and BTEQ command to the standard output stream

EXPORT- Open a file with a specific format to transfer information directly from the Teradata database.

IMPORT -Open a file with a specific format to import information into Teradata

INDICDATA - One of multiple data mode options for data selected from Teradata. The modes are INDICDATAFIELD or RECORD MODE

OS - Execute an MS-DOS, PC-DOS or Unix command inside BTEQ

QUIET -Limit BTEQ output displays to all error messages and request processing statistics

REPEAT - Submit the next request a certain amount of times

RUN - Execute Teradata SQL requests and BTEQ commnads directly from a specified run file

TSO - Execute an MVS TSO command from inside the BTEQ environment

Wednesday, 24 February 2016

How to run BTEQ export Script in Mainframe a best example

/*......................................................*/
/*..........................Program ................. */
/* SCRIPT = XYY9999....................................*/
/* SCRIPTTYPE=TERADATA BTEQ............................*/
/* LANGUAGE=UTILITY COMMANDS AND SQL................ */
/* RUN MODE=BATCH ..................................... */
/* ---------------------------------------------------*/
/* PROGRAM................................*/

.SESSIONS 1
.RUN FILE ILOGON;  
/*JCL ILOGON -.LOGON CDW/SQL01,WHYNOT;*/
.RUN FILE=IDBENV;  
/*JCL IDBENV-DATABASE SQL_CLASS;*/
.EXPORT DATA DDNAME=REPORTSELECT    EMPLOYEE_NO,    LAST_NAME,   FIRST_NAME,   SALARY,   DEPT_NOFROM EMPLOYEE_TABLE
.IF ERROR CODE > 0 THEN 
.GOTO DONE
.EXPORT RESET.LABEL DONE
.QUIT