tag:blogger.com,1999:blog-65274467717255181682024-02-08T00:59:27.594+05:30SriniTeraSrinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.comBlogger54125tag:blogger.com,1999:blog-6527446771725518168.post-26604646155402280902017-05-07T13:29:00.003+05:302020-05-19T21:50:26.684+05:305 Top Teradata DATE functions to read now<font size="4">Top list of DATE functions. These functions frequently used in Teradata. The following statement requests the current system date:</font><span><a name='more'></a></span><div><br /><div style="text-align: center;"><a href="https://www.blogger.com/#"><img alt="teradata date" border="0" height="213" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-SHdcYXKGjQ_-Sx2lcBqrHeK5PTPSCCq6I-ahVhl4HlvxK0Co_sAzhQA5VuzglxV-KCVTHI7ejpJw7Xsu9DpbEI80wIOFSYBrextBBenaP_24LsNnLBVPsi0wvbeFSaouMlV6h3-fXRk/w320-h213/DATE+Functions.jpg" title="teradata date" width="320" /></a> </div><br /> <br /> You can find Teradata Date differences using Date functions <br /><br /><div><b> 1. Current DATE </b><br /><br /></div><div> SELECT CURRENT_DATE;<br /> <br /> The system responds with something like the following: <br /> Date -------- 01/12/28<br /> <br /> <br /><b> 2. Changing Current DATE to DATE as you expected </b></div><div><br /></div><div>To change the default output format of the CURRENT_DATE result, use Teradata explicit conversion syntax and specify the FORMAT phrase.<br /> <br /> For example, the following statement requests the current time and specifies a format that is different from the default:<br /> <br /> SELECT CURRENT_DATE (FORMAT 'MMMBDD,BYYYY');<br /> The result looks like this: Date ------------ May 31, 2007<br /> <br /><b>3. Current Time </b></div><div><br /></div><div>SELECT CURRENT_TIME;<br /> <br /> The system responds with something like the following: <br /> <br /> Current Time(0) --------------- 15:53:34+00:00<br /> <br /> The following statement requests the current system time and current session Time Zone displacement:<br /><br /><b> 4. Changing Current TIME </b></div><div><br /></div><div>To change the default output format of the CURRENT_TIME result, use Teradata explicit conversion syntax and specify the FORMAT phrase.<br /> <br /> SELECT CURRENT_TIME (FORMAT 'HH:MIBT');<br /> <br /> The result looks like this: Current Time(0) --------------- 02:29 PM<br /> <br /> For example, the following statement requests the current time and specifies a format that is different from the default:<br /> <br /> <br /><b> 5. Current TIMESTAMP </b></div><div><br /></div><div><br /></div><div>CURRENT_TIMESTAMP Fields The fields in CURRENT_TIMESTAMP are:<br /> <br /> YEAR <br /> MONTH <br /> DAY <br /> HOUR <br /> MINUTE <br /> SECOND <br /> TIMEZONE_HOUR <br /> TIMEZONE_MINUTE<br /> <br /><b> Example to Select Current TIMESTAMP</b><br /> <br /> The following statement requests the system timestamp and session Time Zone displacement:<br /> <br /> SELECT CURRENT_TIMESTAMP;<br /> <br /> The system responds with something like the following: <br /> <br /> Current TimeStamp(6) --------------- 2001-11-27 15:53:34.910000+00:00<br /> <br /> This statement returns the current date plus 13 years:<br /> <br /> SELECT ADD_MONTHS (CURRENT_DATE, 12*13);<br /> <br /><b> Example to Extract Year from DATE</b></div><div><br /></div><div>The following example returns the year, as an integer, from the current date:<br /> <br /> SELECT EXTRACT (YEAR FROM CURRENT_DATE);
</div></div>Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-32618693787982357332016-11-03T10:58:00.003+05:302020-05-19T20:15:11.858+05:30Teradata Quick refresher for most popular UtilitiesTeradata 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. <br /> <br /><div style="text-align: center;"><a href="https://www.blogger.com/#"><img alt="teradata utilities" border="0" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-ulpK2ksZaPzOT_RMRtFNayRsF2JH2vrNzaGIOCfzHvYN9q7WmY6HOwAQVwjk74IplMwAh4i0z_UFwmpXKu-S7E2LvnXXGjPSGxv4FfloxlDAsL31gnOXtymlP9rh03mH_90dCAzo9gA/w320-h203/Teradata.png" title="teradata utilities" width="320" /></a> </div><div><br /> <br /> Top Teradata Utilities <br /><br /></div><div> 1# MultiLoad </div><div><br /></div><div>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.<br /> <br /> You can provide user-defined SQL for complex operations. This stage is best used for bulk updates, deletes, upserts, and complex interface operations.<br /> <br /> <br /> 2# TPump </div><div><br /> 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. <br /> <br /> <br /> 3# FastExport </div><div><br /> 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.<br /> <br /> <br /> Also Read<br /><ul style="text-align: left;"><li><a href="https://www.blogger.com/#">Teradata datawarehousing and analytics</a></li></ul></div>Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-71455556945523292562016-09-25T13:17:00.003+05:302020-05-19T21:42:22.584+05:30All You Need NULL Value Functions<div style="text-align: center;"><a href="https://www.blogger.com/#"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-kqqEI1g7yojDagrGalkxb2c6aAUMqPh9DwXhH9UzXJ9z3llT_oabvP8zw65JH9zqjgq6yc947VJc9iO5qO53ubc25J6aVFn2aD-JnDQIZHlzhTRwRtvknsuAAi64UJGGWKZkZ58WYxM/w320-h180/NULL+FUNCTIONS.jpg" /></a> </div> The below are the Null value functions in Tearadata. One is "NVL" and another one is "NVL2". These two functions are part of <a href="https://www.blogger.com/#">Teradata SQL</a>.<br /> <br /> 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.<br /> <br /> <br /> The best SQL Null functions <div><br /> NVL function: </div><div><br /> SELECT NVL (NULL,'USA'), NVL('SPAIN','USA');<br /> -----------<br /> USA SPAIN<br /> <br /> Also Read: <a href="https://www.blogger.com/#">Top 70 Teradata Interview Questions</a><br /> <br />The NVL2 function :</div><div style="text-align: left;"><br /> 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.<br /> <br /> The rule is as follows: <br /><ol><li>If expression 1 is not null, expression 2 is returned</li><li>If expression 1 is null, expression 3 is returned </li></ol> <br /> The best NVL2 example</div><div style="text-align: left;"><br /> SELECT NVL2(NULL,'FRANCE','SPAIN'), NVL2('ENGLAND','FRANCE','SPAIN')<br /> -------------------<br /> SPAIN FRANCE
</div>Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-18536438672554004802016-09-16T10:13:00.001+05:302020-05-19T21:43:19.887+05:30How 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.<div><br /></div><span><a name='more'></a></span><div><br /></div><div><h2 style="text-align: left;">What does” Pseudo Table” Locks mean in EXPLAIN Plan in Teradata:</h2> <ol style="text-align: left;"><li>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.</li><li><br /></li><li> 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.
</li></ol> </div>Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-87453154688114656002016-09-08T22:52:00.001+05:302020-05-19T21:44:30.238+05:30How Teradata determines primary index <div style="text-align: center;"><a href="https://www.blogger.com/#"><img alt="teradata index" border="0" height="214" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9h6htYURO-PRd4rtKsgM7j_Ol7X4pqnynLY9NwJWV6EPCJekEEmcs1k3xHT022N4tpX8kQRq5yyXdXwQoLlDBbo42dr29p3kXIytrAvGWAral6ibGCS5DvAfAwLPkw0U3PJjYegMIBhc/w320-h214/Teradata+Index.png" title="teradata index" width="320" /></a> </div> 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.<br /> <br /> <br /> Primary Index Rules <div><br />Rule 1: One Primary Index per table. <br />Rule 2: A Primary Index value can be unique or non-unique. <br />Rule 3: The Primary Index value can be NULL. <br />Rule 4: The Primary Index value can be modified. <br />Rule 5: The Primary Index of a populated table cannot be modified. <br />Rule 6: A Primary Index has a limit of 64 columns. <br /><br /></div><div><br /></div><div> Two Types of Primary Indexes (UPI or NUPI) </div><div><br /></div><div><br /> 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<br /> <br /> 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.<br /> <br /> <br /> What happens when user not defined Primary index <br /> <br />Teradata assigns first column of table as Primary index, when not defined during creating a Table. <br />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.
</div>Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-3838071087175488762016-09-07T10:29:00.004+05:302020-05-19T21:45:33.613+05:3034 Complex Teradata Architecture Questions <div dir="ltr" style="text-align: left;" trbidi="on">
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNCekt8XJE69zFavW6GMtMhlsy5b2nTcuGIWH3gj3IWtqzGzU5A4gWSOu6eujYs6yNGotYzNTLleZ1kMoP-ngK255NkBo39tLozTnisiaqaVsqGNbOWj128ueN5lrQLsLYasw8ov2Mo04/s1600/A1.png" style="margin-left: auto; margin-right: auto;"><img alt="Teradata Architectural Questions" border="0" height="232" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNCekt8XJE69zFavW6GMtMhlsy5b2nTcuGIWH3gj3IWtqzGzU5A4gWSOu6eujYs6yNGotYzNTLleZ1kMoP-ngK255NkBo39tLozTnisiaqaVsqGNbOWj128ueN5lrQLsLYasw8ov2Mo04/w320-h232/A1.png" title="Teradata Architectural Questions" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Teradata Architectural Questions </td></tr>
</tbody></table>
<b>QUESTION No:1</b><br />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.) <br /><br />A. Index Scan Types Allowed <br />B. Throttle All-AMP Queries Only <br />C. No Aggregate Functions Allowed <br />D. Only limit queries with Step Time Threshold <br /><br />Answer: C,D <br /><br /><b>QUESTION NO: 2</b> <br />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? <br /><br />A. Implement a cluster backup of the table. <br />B. Implement an online archive of the table. <br />C. Implement an all-AMP backup of the table. <br />D. Implement partition backup for the previous day. <br /><br /><div>
Answer: C <br /><br /><b>QUESTION NO: 3 </b><br />Which two statements are true about FastLoad capabilities regarding error handling, return codes, and handling statistics about the load job? (Choose two.) <br /><br />A. The predefined system variables, prefixed with &SYS, are not implemented in FastLoad. <br />B. Notify Exit routines specify a predefined action to be performed whenever a failure occurs in the utility. The action only handles return codes. <br />C. Predefined system variables, prefixed with &SYS, are used to capture load statistics and can be used to determine if the process ran successfully. <br />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. <br /></div>
<div>
Answer: B,C <br /><b><br />QUESTION NO: 4 </b><br />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? <br /><br />A. Use TPump to continuously read from the queue and insert the data. <br />B. Pull the data from the queue into a file and use TPump to insert the data from the file. <br />C. Pull the data from the queue into a file and use MultiLoad to insert the data from the file. <br />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. <br /><br />Answer: B <br /><br /><b>QUESTION NO: 5 </b><br />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? <br /><br />A. ESCON Director <br />B. Block Multiplexer <br />C. Channel Extender <br />D. BYNET Optical Extension (BYOX) <br /><br />Answer: A <br /><br /><b>QUESTION NO: 6 </b><br />Which method can be used to address the privacy requirements of an application that accesses personal information? <br /><br />A. Use Access Logging to determine the requesting user, type of access, and the referenced object. <br />B. Extract the data, define a masking algorithm, and insert the data, with masking, into the response. <br />C. Use multi-factor authentication to provide strong access control and enhance the safety of data stored in the database. <br />D. Create a user defined function that can only be accessed from within the application to encrypt and decrypt the personal data. <br /><br />Answer: A <br /><br /><b>QUESTION NO: 7 </b><br />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.) <br /><br />A. priorities <br />B. roles and profiles <br />C. implicit access rights <br />D. access and DBQL logging <br />E. permanent space allocation <br />F. archive and restore operations <br /><br />Answer: A,D,F <br /><br /><b>QUESTION NO: 8 </b><br />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.) <br /><br />A. End logging on any user will flush the cache. <br />B. Reduce the setting of the dbscontrol parameter dbscachectrl. <br />C. Reduce the setting of the dbscontrol parameter dbqlflushrate. <br />D. End logging on systemfe is the only user that will allow the cache to be flushed manually. <br /><br />Answer: B,C <br /><br /><b>QUESTION NO: 9 </b><br />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? <br /><br />A. AMPUsage <br />B. DBQL.QryLog <br />C. ResUsageSpma <br />D. Metadata services <br />E. Dictionary access counts <br /><br />Answer: A <br /><br /><b>QUESTION NO: 10 </b><br />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. <br /><br />Which statement is true about using join indexes in the existing database architecture? <br /></div>
<div>
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. <br />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. <br />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. <br />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. <br /></div>
<div>
Answer: A <br /><br /><b>>> <a href="http://teradatablog.srinimf.com/p/page-two-teradata-architectural.html">Page two Questions</a></b></div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-45247988238713002312016-02-27T18:06:00.001+05:302019-06-20T11:20:23.411+05:3012 Frequently Used File Control Commands in BTEQ utility<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7UsCOhv07kfrGPdcdn3uJcaYpGfBwdL6Vz2wZAq_gmbUSkV0H0VxNP2TbpEJeZFddD8OemtUIWJis4olBxhLZFH_JZZpzvIMZFtqqrrx0_U5x-VKLoy_7OhlnEx1dQXaJDLlSj7udG_Y/s1600/file+control+commands.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="file control commands" border="0" data-original-height="400" data-original-width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7UsCOhv07kfrGPdcdn3uJcaYpGfBwdL6Vz2wZAq_gmbUSkV0H0VxNP2TbpEJeZFddD8OemtUIWJis4olBxhLZFH_JZZpzvIMZFtqqrrx0_U5x-VKLoy_7OhlnEx1dQXaJDLlSj7udG_Y/s1600/file+control+commands.jpg" title="file control commands" /></a></div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
The below list shows ultra frequently used commands in BTEQ utility.<br /><br /><b>DEFAULTS:</b> Reset all BTEQ format command options to their defaults. This will utilize the default configurations<br /><br /><b>ECHOREQ </b>- Enable the Echo required function in BTEQ returing a copy of each Teradata SQL request and BTEQ command to the standard output stream<br /><br /><b>EXPORT</b> - Open a file with a specific format to transfer information directly from the Teradata database.<br /><br /><b>IMPORT</b> - Open a file with a specific format to import information into Teradata<br /><br /><b>INDICDATA </b>- One of multiple data mode options for data selected from Teradata. The modes are INDICDATAFIELD or RECORD MODE<br /><br /><b>OS </b>- Execute an MS-DOS, PC-DOS or Unix command inside BTEQ<br /><br /><b>QUIET</b> -Limit BTEQ output displays to all error messages and request processing statistics<br /><br /><b>REPEAT </b>- Submit the next request a certain amount of times<br /><br /><b>RUN</b> - Execute Teradata SQL requests and BTEQ commnads directly from a specified run file<br /><br /><b>TSO</b> - Execute an MVS TSO command from inside the BTEQ environment</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-76562884104934379362016-02-24T11:55:00.003+05:302020-05-19T21:47:49.938+05:30How to run BTEQ export Script in Mainframe<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<div class="separator" style="clear: both; text-align: center;">
</div>
<div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTpubTshg6f5vNPVIK0vnvhbUHYK_JKAYlG7LkE66DTz3KL-d8DPPGkpYCiQ8I16yjKOU_Ip4J3dAdOkojaakkbwCwtQKyE2WvZfhfQNNE_PUB5KVuQspRyBKwxVzqtAEc-o_GEjXXrMk/s1600/JCL+for+BTEQ.jpg" style="margin-left: 1em; margin-right: 1em;"><img alt="JCL for BTEQ" border="0" data-original-height="400" data-original-width="712" height="180" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTpubTshg6f5vNPVIK0vnvhbUHYK_JKAYlG7LkE66DTz3KL-d8DPPGkpYCiQ8I16yjKOU_Ip4J3dAdOkojaakkbwCwtQKyE2WvZfhfQNNE_PUB5KVuQspRyBKwxVzqtAEc-o_GEjXXrMk/w320-h180/JCL+for+BTEQ.jpg" title="JCL for BTEQ" width="320" /></a></div>
<br /><b>
JCL to Run BTEQ Script in Mainframe a best example.</b><br />
<span style="font-family: "courier new", courier, monospace;"><br /></span>
<span style="font-family: "courier new", courier, monospace;">/*......................................................*/</span><br />
<span style="font-family: "courier new", courier, monospace;">/*..........................Program ................. */</span><br />
<span style="font-family: "courier new", courier, monospace;">/* SCRIPT = XYY9999....................................*/</span><br />
<span style="font-family: "courier new", courier, monospace;">/* SCRIPTTYPE=TERADATA BTEQ............................*/</span><br />
<span style="font-family: "courier new", courier, monospace;">/* LANGUAGE=UTILITY COMMANDS AND SQL................ */</span><br />
<span style="font-family: "courier new", courier, monospace;">/* RUN MODE=BATCH ..................................... */</span><br />
<span style="font-family: "courier new", courier, monospace;">/* ---------------------------------------------------*/</span><br />
<span style="font-family: "courier new", courier, monospace;">/* PROGRAM................................*/</span><br />
<span style="font-family: "courier new", courier, monospace;"><br /></span>
<span style="font-family: "courier new", courier, monospace;">.SESSIONS 1</span><br />
<span style="font-family: "courier new", courier, monospace;">.RUN FILE ILOGON; </span><br />
<span style="font-family: "courier new", courier, monospace;">/*JCL ILOGON -.LOGON CDW/SQL01,WHYNOT;*/</span><br />
<span style="font-family: "courier new", courier, monospace;">.RUN FILE=IDBENV; </span><br />
<span style="font-family: "courier new", courier, monospace;">/*JCL IDBENV-DATABASE SQL_CLASS;*/</span><br />
<span style="font-family: "courier new", courier, monospace;">.EXPORT DATA DDNAME=REPORTSELECT EMPLOYEE_NO, LAST_NAME, FIRST_NAME, SALARY, DEPT_NOFROM EMPLOYEE_TABLE</span><br />
<span style="font-family: "courier new", courier, monospace;">.IF ERROR CODE > 0 THEN </span><br />
<span style="font-family: "courier new", courier, monospace;">.GOTO DONE</span><br />
<span style="font-family: "courier new", courier, monospace;">.EXPORT RESET.LABEL DONE</span><br />
<span style="font-family: "courier new", courier, monospace;">.QUIT</span></div>
</div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-52920670459385225132015-11-24T08:44:00.001+05:302020-05-19T21:46:00.662+05:30BTEQ Mainframe Export JCL Example<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOOteogTkZ385ZB0ktAQmpwPKUaMtctGQIjGj5ZHruh1yLigtFdiclpheaEVa-8rObbsuP4GRejWfuattiONpGcvCtMranJ36NWKWUq9NU2OylKouxGCcnzwBLmfhdhG_Tl0rcIeWdeSs/s1600/BTEQ+export+jcl.jpg" style="margin-left: 1em; margin-right: 1em;"><img alt="bteq export jcl" border="0" data-original-height="400" data-original-width="712" height="180" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOOteogTkZ385ZB0ktAQmpwPKUaMtctGQIjGj5ZHruh1yLigtFdiclpheaEVa-8rObbsuP4GRejWfuattiONpGcvCtMranJ36NWKWUq9NU2OylKouxGCcnzwBLmfhdhG_Tl0rcIeWdeSs/w320-h180/BTEQ+export+jcl.jpg" title="bteq export jcl" width="320" /></a></div>
<b><span style="font-family: inherit;"><br /></span></b>
<b><span style="font-family: inherit;">BTEQ Mainframe export JCL real example.</span></b></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><span style="font-family: inherit;"><br /></span>
</span></div>
<span style="font-family: inherit;"><span style="font-family: "courier new", courier, monospace;">//B09XYZD2 JOB (T,AA,XYZ),'BTEQ' TEMPATE', CLASS=S, MSGCLASS-0,</span></span><br />
<span style="font-family: "courier new", courier, monospace;">// REGION=6M,NOTIFY=B09XYZ</span><br />
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//*</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//*+JBS BIND TDPO.UP</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//*</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//*----------------------------------------------------------------</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//* JOB INFORMATION AND COMMENTS</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//*----------------------------------------------------------------</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">// JOBLIB DD DSN=C309.BOSNCR.NM.R60.APPLOAD,DISP=SHR</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">// DD DSN=C309.BOSNCR.NM.R60.TRLOAD,DISP=SHR</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//*----------------------------------------------------------------</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//BTEQ1 EXEC <b>PGM=BTQMAIN</b></span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//LOGON DD DSN=B09XYZ.APPLUTIL.CLASS.JCL(ILOGN),D</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//IDBENV DD DSN=B09XYZ.APPLUTIL.CLASS.JCL(IDBENV),D</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//SYSIN DD DSN=B09XYZ.APPLUTIL.CLASS.JCL(BTEQSCPT)</span></div>
<div style="text-align: left;">
<span style="font-family: "courier new", courier, monospace;">//SYSPRINT DD SYSOUT=*</span></div>
<br />
<br /></div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-29397606583444547592015-11-23T08:06:00.003+05:302020-05-19T21:48:34.326+05:30Teradata Parsing Engine Details<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="text-align: left;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdIOGJKiSUx9-EG_68cMwVZVtHqOAFYqlWQvQXlBgarlcZF6cUNxKdx6JHbBcnI2dKnH03fnqCvJwX1Na_zMJ5K2VO8RlVuH5QTwi60wVU2CJGsi2Mgr09tLTlVUDAeLAweBojMDI1rio/s1600/Parsing.JPG" style="margin-left: 1em; margin-right: 1em;"><img alt="parsing engine" border="0" data-original-height="244" data-original-width="535" height="145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdIOGJKiSUx9-EG_68cMwVZVtHqOAFYqlWQvQXlBgarlcZF6cUNxKdx6JHbBcnI2dKnH03fnqCvJwX1Na_zMJ5K2VO8RlVuH5QTwi60wVU2CJGsi2Mgr09tLTlVUDAeLAweBojMDI1rio/w320-h145/Parsing.JPG" title="parsing engine" width="320" /></a></div>
<span style="font-family: inherit;">You may think that Teradata is super faster in handling data requests. The background is Parsing engine. It handles efficiently. I have given complete functions to your read now.<b> </b></span></div><div style="text-align: left;"><b><br /></b>
<span style="font-family: inherit;"><b>Basic functionalities of Teradata Parsing Engine</b></span><br />
<ul style="text-align: left;"></ul>
<ul>
<li>It handles session control </li>
<li>Logon/Logoff </li>
<li>Managing Session </li>
<li>BYNET </li>
<li>AMP</li>
</ul>
<ul style="text-align: left;"></ul>
</div>
<div style="text-align: left;">
<span style="font-family: inherit;"><b>Other functions</b></span></div>
<div style="text-align: left;">
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Acts like parser</span></li>
<li><span style="font-family: inherit;">Parse and Optimize SQL requests</span></li>
<li><span style="font-family: inherit;">Dispatching optimized plan to AMPs over BYNET</span></li>
<li><span style="font-family: inherit;">Send Query result back to requesting client</span></li>
<li><span style="font-family: inherit;">Responsible for Hashing data</span></li>
</ul>
</div>
<div style="text-align: left;">
<span style="font-family: inherit;"><b>Flow diagram:</b></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="color: #351c75; font-family: inherit;">Session control --> Parser --> Optimizer--> Dispatcher</span></div>
<div style="text-align: left;">
<ul style="text-align: left;">
<li><span style="font-family: inherit;">Locally couples SMP Nodes in multi node system</span></li>
<li><span style="font-family: inherit;">Carries message between AMPs and PEs</span></li>
<li><span style="font-family: inherit;">Communication between nodes -point to point broadcast</span></li>
<li><span style="font-family: inherit;">Merges answer sets back to PE</span></li>
</ul>
</div>
<div style="text-align: left;">
<span style="font-family: inherit;"><b>Performs DB and file management operations:</b></span></div>
<ul style="text-align: left;">
<li><span style="font-family: inherit;">- Finding requested rows</span></li>
<li><span style="font-family: inherit;">- LOCKS</span></li>
<li><span style="font-family: inherit;">- Sorting</span></li>
<li><span style="font-family: inherit;">- Joins</span></li>
<li><span style="font-family: inherit;">- Output data conversion</span></li>
<li><span style="font-family: inherit;">-Accounting/Journaling</span></li>
</ul>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-41033080434339149672015-08-29T19:06:00.000+05:302019-06-23T19:45:24.158+05:303 Types of Temporal Tables in Teradata<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXF1udYGr_rjDmHZRM_IQG_5sZ0eACfUrtXc8pljukIj8JWBfnzqN9KFUGJJq6EIia6yKpxQ8dC54LCSbjsPJ1JWuM85fWvKlUAnCnxEdI9_Xijgi3sut1KY1AxidRnUSerzS1obr9Nz4/s1600/Temporal+Tables.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="temporal tables" border="0" data-original-height="400" data-original-width="712" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXF1udYGr_rjDmHZRM_IQG_5sZ0eACfUrtXc8pljukIj8JWBfnzqN9KFUGJJq6EIia6yKpxQ8dC54LCSbjsPJ1JWuM85fWvKlUAnCnxEdI9_Xijgi3sut1KY1AxidRnUSerzS1obr9Nz4/s1600/Temporal+Tables.jpg" title="temporal tables" /></a></div>
<br />
What is a Temporal Database Definition - Temporal – the ability to store all states of a given set of data (a database row), as part of the query select a point in time to reference the data.<br />
<br />
Examples:<br />
<ul style="text-align: left;">
<li>What was this account balance (share price, inventory level, asset value, etc) on this data?</li>
<li>What data went into the calculation on 11/3/07, and what adjustments were made in 2008?</li>
<li>On this historic data, what was the service level (contract status, customer value, insurance policy coverage) for said customer?</li>
</ul>
<h3 style="text-align: left;">
Three Types of Temporal Tables</h3>
<h4 style="text-align: left;">
1. Valid Time tables</h4>
<div>
When a fact is true in the modeled reality. User specified times.</div>
<h4 style="text-align: left;">
2. Transaction time Tables</h4>
<div>
When a fact is stored in the database. System maintained time, no user control.<br />
<div>
<h4 style="text-align: left;">
3. Bitemporal Tables</h4>
</div>
<div>
Both Transaction Time and Valid Time <br />
<h4 style="text-align: left;">
User defined Time</h4>
</div>
<div>
User can add time period columns, and take advantage of the added temporal operators Database does not enforce any rules on user defined time columns.<br />
<h4 style="text-align: left;">
Temporal Query</h4>
</div>
<div>
Provides list of members who were reported as covered on JAN 15,2000 in the Feb 1 2000 NCQA report, with names as accurate as our best data shows today.<br />
<h4 style="text-align: left;">
Temporal Update - Bi Temporal Table</h4>
</div>
<div>
Current Valid time, Current transaction time Query jeans(125,102) are sold today (2005-08-30)<br />
<div class="MsoNormal">
<o:p></o:p></div>
</div>
</div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-90719887132676640752015-08-27T11:00:00.000+05:302019-06-20T10:55:37.102+05:30How to refresh Hadoop the best way in three minutes<div dir="ltr" style="text-align: left;" trbidi="on">
<iframe height="480" src="https://drive.google.com/file/d/0B0M3QnWxErrwejZjdGFpVExfV2s/preview" width="500"></iframe></div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-7423063804300563462015-08-26T14:00:00.000+05:302019-06-29T16:12:55.744+05:30How best You can use Teradata data mover<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.indeed.com/jobs?q=TERADATA&l=United+States&indpubnum=6634974704162507&chnl=LearnTeradata" style="clear: left; margin-bottom: 1em;" target="_blank"><img alt="How best You can use Teradata data mover" border="0" height="425" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjS5WFMPim7OuC8BQiE1anhAJguibXuh_Hq5cTqs52IFpClq3Q_n5QF3xuc7-MdFmx-3gsHCiIR9VzAhDcnoHFOMHWfTo7IBmgAciNtExTEuzLhjmedgRYqaJ1fiKIK7euLqm4P8_yO1o/s640/ID-100349666.jpg" title="How best You can use Teradata data mover" width="640" /></a></div>
Top advantages of Teradata data mover in data warehousing projects. An End-to-End Management Solution. Before going in detail you can refresh <a href="http://teradatablog.srinimf.com/2015/01/teradata-unique-architecture.html">All You Need to Know About Teradata Architecture</a>.<br />
<ul style="text-align: left;">
<li>You can also integrate data movement within your comprehensive ecosystem management and load jobs and automate. </li>
<li>Teradata Data Mover for your production execution needs. </li>
<li>That’s because the utility can be tightly integrated with Teradata Multi-System Manager for end-to-end management of changes to your environment. </li>
</ul>
<h3 style="text-align: left;">
The utility works with Teradata Multi System Manager to support these commands and status messages:</h3>
<ul style="text-align: left;">
<li>Send event for the start of a job. </li>
<li>Send event for end of a job. </li>
<li>Send event for a job in progress. </li>
<li>And Teradata Data Mover can be integrated with load process 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. </li>
<li>Teradata Data Mover also provides safeguards to force the direction of data movement and eliminate any possibility of copying data in the wrong direction. </li>
<li>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, Triggers, Join indexes, Hash indexes, Tables with UDT columns, and Users. </li>
</ul>
<h3 style="text-align: left;">
Best example for Teradata data mover</h3>
<ol style="text-align: left;">
<li>For example, you may have a Teradata Active Enterprise Data Warehouse serving as an EDW, a Teradata Extreme Data Warehouse for deep history web click analysis, and Teradata Data Mart Appliance as a test and development system. </li>
<li>As part of your critical ongoing maintenance, you must be able to move or copy data from one system to another. </li>
<li>Teradata Data Mover simplifies data movement and enable process automation, control, and monitoring. It also provides a point-and-click user interface to enable powerful data movement commands. </li>
<li>Teradata Data Mover offers you a complete solution to improve your processes and reduce the cost involved in maintaining the analytical ecosystem. </li>
<li>Take advantage of its scope of data movement technologies and breath of control mechanisms. </li>
<li>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.</li>
</ol>
<ul style="text-align: left;">
</ul>
</div>
<div class="MsoNormal">
<span style="font-family: inherit;"><b></b><b></b></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<span style="font-family: inherit;"><o:p></o:p></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<o:p></o:p></div>
<div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<o:p></o:p></div>
</div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-83288217457021956722015-08-22T20:53:00.001+05:302019-06-22T13:14:16.216+05:30Top features of Teradata data mover for large data transfer<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih8sfstvwkYsCEaExGjhirQ27NAVXYLtJK9X8QQZ3INAE2rfitywepdn558TqF9dkfC-npdOBpTkDH_DhAipjCzMkxrYM1FowlzugP6EqsYmFu1aoV0pBQmMkDpd0uYdG3VosTzpW8JtU/s1600/data+mover.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="data mover" border="0" data-original-height="400" data-original-width="712" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih8sfstvwkYsCEaExGjhirQ27NAVXYLtJK9X8QQZ3INAE2rfitywepdn558TqF9dkfC-npdOBpTkDH_DhAipjCzMkxrYM1FowlzugP6EqsYmFu1aoV0pBQmMkDpd0uYdG3VosTzpW8JtU/s1600/data+mover.jpg" title="data mover" /></a></div>
<h3 style="text-align: left;">
Teradata Data Mover</h3>
Moving data from one system to another is a key part of your analytical ecosystem operation. Well-planed, monitored, and managed data movement is essential to effective operation of your overall ecosystem.<br />
<br />
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’s mission of serving your business.<br />
<br />
The decision about the best way to copy data between Teradata systems depends on many factors, including the data model and indexes present, other workloads in your system, and additional load jobs underway.<br />
<br />
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’s exactly what Teradata Data Movers delivers to you and your business; all the supporting capabilities you need to easily, effectively- and affordably- meet your data movement demands.<br />
<ul style="text-align: left;">
<li><a href="http://teradatablog.srinimf.com/2015/07/dw-top-teradata-interview-questions-and.html">Tearadata +Interview+ Questions+ Part-1</a></li>
</ul>
<h3 style="text-align: left;">
Intelligent Selection</h3>
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 advantages 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 product, and the JDBC driver.<br />
<br />
<h3 style="text-align: left;">
Leverage Existing Technologies</h3>
<ul style="text-align: left;">
<li>Teradata Data Movers leverages all of the established data loading protocols and technologies to meet your needs and environment best. </li>
<li>You can use the utility for; Regular periodic tasks including populating a dependent datamart from an EDW. </li>
<li>Special one-time tasks such as moving data to a test system. </li>
<li>The utility also works with every member of our Teradata Purpose-Built Platform Family. In addition, it supports Teradata Database VZR6.1 and higher and runs. </li>
<li>On a Teradata Managed Server for high availability and comprehensive monitoring.</li>
</ul>
<div class="MsoNormal">
<ul style="text-align: left;">
</ul>
</div>
<div class="MsoNormal">
<o:p></o:p></div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-36768653693351296142015-08-17T10:30:00.000+05:302019-06-20T11:01:22.319+05:30New features in Teradata 14 (1 of 2)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
</div>
<b>Teradata 14.0 new features.</b><br />
<ul style="text-align: left;">
<li>It is columnar database</li>
<li>New security features added</li>
<li>Enhanced capability in workload and system management</li>
<li>Compression</li>
<li>Temporal tables</li>
</ul>
<div>
<b>TTU14.0</b></div>
<ul style="text-align: left;">
<li>TTU 14 (Teradata tools and utilities) compatible with previous relaeses</li>
<li>TASM(Teradata workload manager only supports with version 12</li>
<li>Teradata query director is not available in TU 14.0. It is replaced by Teradata unity</li>
<li>TTU 14.0 now supports, Redhat 6X, Zo/s 1.12, Zos 1.13, AIX 7.1 and Solaris 11</li>
<li>Support for the following Os are dropped- HP-UX11.11, Redhat Linux Advaned server 3.0, Windows 2000, Linux SUSE9, Solaris 8</li>
</ul>
<div>
<b>New Application program interfaces</b></div>
<div>
<ul style="text-align: left;">
<li>Teradata CLIV2-ODBC driver for Teradata- Teradata JDBC driver</li>
<li>OLEDB provider for Teradata-Teradata pre processor 2, .Net provider for Teradata</li>
<li>Support libraraies-ICU-Tera GSS</li>
<li>End user utilities- Teradata SQL assist - BTEQ</li>
<li>Load and Unload utilities- Teradata parallel transporter</li>
<li>Stand alone utilities- Fast load, Fast export, MultiLoad and Tpump</li>
<li>Developer Tools- Supports IDE Eclipse, Visual Studio</li>
</ul>
</div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-69260279872470504092015-08-03T11:00:00.000+05:302017-01-05T12:17:38.288+05:30Top Teradata Interview Questions and Answers (2 of 7)<div dir="ltr" style="text-align: left;" trbidi="on">
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirf0kTdK3HkCLqQhbgGWazaBQh-irn4uYNbj_J0Qr_IAl5b-J650cMsnlqKgg-nyJCoSG2XAdjpCdu2KmLYmc2yUk02vZo6VX4tJqhHpvt7_cgqhy_EPTyEq8RSzHbGh_TOJWbcM81PGk/s1600/ID-100184792.jpg" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img alt="Top Teradata Interview Questions and Answers" border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirf0kTdK3HkCLqQhbgGWazaBQh-irn4uYNbj_J0Qr_IAl5b-J650cMsnlqKgg-nyJCoSG2XAdjpCdu2KmLYmc2yUk02vZo6VX4tJqhHpvt7_cgqhy_EPTyEq8RSzHbGh_TOJWbcM81PGk/s200/ID-100184792.jpg" title="Top Teradata Interview Questions and Answers" width="132" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">#Top Teradata Interview Questions and Answers:</td></tr>
</tbody></table>
The teradata interview questions of second set.<br />
<div>
<ul style="text-align: left;">
<li>What is the usage of Fallback?</li>
</ul>
</div>
A) When a table is fallback protected the rows are duplicated on other AMPs. Fallback is similar to mirroring, but different. The similarities is that both provide a duplicate copy, but the difference is that Fallback places copies of its rows on multiple AMPs so if a failure occurs Teradata can use the parallelism to help the failed AMP.<br />
<br />
<a href="http://teradatablog.srinimf.com/2015/07/dw-top-teradata-interview-questions-and.html" target="_blank">Teradata+Interview +Questions-Part-1</a><br />
<ul style="text-align: left;">
<li>How many primary keys we will apply on a table?</li>
</ul>
A) Only one<br />
<ul style="text-align: left;">
<li>How does rows store in Teradata?</li>
</ul>
A) It spreads the rows across all AMPs<br />
<ul style="text-align: left;">
<li>Tell me the types of Indexes?</li>
</ul>
A) PI - Primary index<br />
NoPI-No primary index<br />
UPI-Unique primary index<br />
NUPI-Non unique primary index<br />
USI-Unique secondary index<br />
NUSI-Non unique secondary index<br />
<ul style="text-align: left;">
<li>Difference between UPI and NUPI and the situation where exactly? We will use these?</li>
</ul>
<a href="http://teradatablog.srinimf.com/2015/05/highlights-of-teradata-indexes.html" target="_blank">Teradata indexes</a><br />
<ul style="text-align: left;">
<li>The role of SI in teradata and explain the types?</li>
</ul>
<a href="http://teradatablog.srinimf.com/2015/03/teradata-secondary-index.html" target="_blank">Teradata Secondary indexes</a><br />
<ul style="text-align: left;">
<li>How do we create a join and hash indexes and explain the scenerios? When we will use exactly?</li>
</ul>
<br />
A) There are four basic types of joins that Teradata can perform depending on the characteristics of the table definition. When the join domain is the primary index (PI) column, with a unique secondary index (USI) the join is referred to as a nested join and involves, at most, three AMPs. The second type of join is a merge join, with three different forms of a merge join, based on the request.<br />
<br />
<ul style="text-align: left;">
<li>The newest type of join in Teradata is the Row Hash join using the pre-sorted Row Hash value instead of a sorted data value match. This is beneficial since the data row is stored based on the row hash value and not the data value. The last type is the product join.</li>
<li>In Teradata, each AMP performs all join processing in parallel locally. This means that matching values in the join columns must be on the same AMP to be matched. When the rows are not distributed and stored on the same AMP, they must be temporarily moved to the same AMP, in spool. Remember, rows are distributed on the value in the PI column(s). If joins are performed on the PI of both tables, no row movement is necessary. This is because the rows with the same PI value are on the same AMP - easy, but not always practical. Most joins use a primary key, which might be the UPI and a foreign key, which is probably not the PI.</li>
<li>Regardless of the join type, in a parallel environment, the movement of at least one row is normally required. This movement puts all matching rows together on the same AMP. The movement is usually required due to the user's choice of a PI. Remember, it is the PI data value that is used for hashing and row distribution to an AMP. Therefore, since the joined columns are mostly columns other than the PI, rows need to be redistributed to another AMP. The redistributed rows will be temporarily stored in spool space and used from there for the join processing.</li>
</ul>
<br />
<ul style="text-align: left;">
<li>What is the meaning of Transient journal overhead?</li>
</ul>
A) Transient Journal – Each AMP has a distinct Transient Journal for database integrity. If an AMP has a row that is about to change (Insert, Update, Delete), it will take a before picture and store it in its Transient Journal. If the transaction fails, then the AMP can Rollback the before image. If, however, the transaction is successful, the Transient Journal throws away the image! The Transient Journal lives for the life of a transaction.<br />
<br />
Permanent Journal – All AMPs copy any changes to the database and store them in the Permanent Journal permanently. The DBA or Table Creator must request a Permanent Journal. Each day, the DBA usually does a "Check Point with Save", and that days Permanent Journal is stored permanently on tape or disk off of the Teradata system<br />
<ul style="text-align: left;">
<li>What is skewness in teradata?</li>
</ul>
A) Skewness is the statistical term, which refers to the row distribution on AMPs. If the data is highly skewed, it means some AMPs are having more rows and some very less i.e. data is not properly/evenly distributed. This affects the performance/Teradata's parallelism. The data distribution or skewness can be controlled by choosing indexes.<br />
<ul style="text-align: left;">
<li>What is a subtable and when it requires?</li>
</ul>
A) As soon as the DBA uses the SQL to create a secondary index Teradata immediately gets to work. Teradata must build the secondary index Subtable immediately before it can become an alternate path to the data. Each AMP Hashes the secondary index value for each row they own with the Hash Formula. The result is a 32-bit Row Hash which points to a bucket in the Hash Map, which tells the secondary index row which AMPs Subtable it will be on. All UNIQUE Secondary Indexes are hashed and the value plus the real Row-ID of the base table are sent to the proper AMP over the BYNET. </div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-16572654656484970102015-07-24T20:00:00.000+05:302015-07-24T20:00:00.985+05:30Teradata Architecture: What are Similarities between User and Database <div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.indeed.com/jobs?q=TERADATA&indpubnum=6634974704162507" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;" target="_blank"><img alt="Teradata Jobs + Apply now" border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhNJ3vv_95Iu8usXLGnRuJL8b_CJca6WxOp2WxSW04GMHqFz8KHD6azbIht777Wfy8SkXRW9ykorcEXTudI9uvz-doK3lb1SpO7HYz5d7FULH0wp3OKK7oQbmsSm32zjAqqIIuGZnlqLs/s320/ID-100290507.jpg" title="Teradata Jobs + Apply now" width="212" /></a></div>
<b><u>Differences between Database and USER:</u></b><br />
<br />
<ul style="text-align: left;">
<li>A Database or a User can be assigned PERM Space</li>
<li>If the Database Store is assigned 10 GB of PERM, that means it can hold up to 10 GB of Permanent Tables.</li>
<li> If the User Nancy is assigned 10 GB of PERM, that means she can hold up to 10 GB of Permanent Tables.</li>
<li>A Database or a User can be assigned Spool Space</li>
<li>If the Database Store is assigned 10 GB of Spool, that means all users under Store can each run 10 GB queries.</li>
<li>If the User Nancy is assigned 10 GB of Spool, that means she can run up to 10 GB queries, and any user created under Nancy will default to 10 GB queries.</li>
<li>In Teradata, the only difference between a database and a<b> user is that a user can login and run queries.</b></li>
</ul>
<br />
<b><u>Roles and Teradata Access rights:</u></b><br />
<br />
Roles simplify database administration by assigning access rights to tables and other objects, and then groups of people with similar job functions (or roles) can access these objects.<br />
<br />
It is as simple as creating different roles for different job functions and responsibilities, and then granting specific privileges (access rights) on database objects to these roles, then granting a role or roles to users who share the same privileges.<br />
<br />
Imagine a thousand users accessing certain databases, tables, views, macros, or stored procedures and having to grant each individual user access rights to these objects. Why not, instead, create a role that provides access to these objects, and then assign everyone needing this access that role.<br />
<br />
Roles simplify this very important security assignment. This is the way that Teradata prefers to implement Access Rights. <br />
<br />
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-14995178203515297332015-07-22T21:00:00.000+05:302017-01-05T12:22:02.196+05:30Two most popular kinds of Teradata Utilities that must read<div dir="ltr" style="text-align: left;" trbidi="on">
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://www.indeed.com/jobs?q=Data+Warehouse&indpubnum=6634974704162507" imageanchor="1" style="margin-left: auto; margin-right: auto;" target="_blank"><img alt="Two most popular kinds of Teradata Utilities that must read" border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2fOk2ONS91nQuKwEr3TZSYPkVCkDLxXIhLFf-vYiJ8JN-DnCG8Tij8kjk19qqNNBbcd9z8I9hFZTNU2xgKK2Ln3IGm9xDPy2MOsiih6GDmajykKYBA7v571D54l8T2zec2z9ijXkAkq4/s200/ID-100248926.jpg" title="Two most popular kinds of Teradata Utilities that must read" width="200" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">#Two most popular kinds of Teradata Utilities that must read:</td></tr>
</tbody></table>
<b>Block level utilities:</b><br />
<br />
<ul style="text-align: left;">
<li>FastLoad performs Inserts into empty Teradata tables in 64K blocks.</li>
<li>MultiLoad loads to populated Teradata tables in 64K blocks.</li>
<li>FastExport – Exports data off of Teradata at the 64K block level.</li>
</ul>
<br />
<b>Row level utilities:</b><br />
<div>
<br /></div>
<div>
<div>
<b>BTEQ –</b> The first Teradata query tool. It imports and exports data and should be used when < 500,000 rows. BTEQ is an excellent report writer.</div>
<div>
</div>
<div>
<b>TPump</b> loads into Empty or Populated Tables and is a lot like MultiLoad except TPump does all of the work at the row level and NOT the block level. It is called TPump because you can turn up or down the pump to increase or decrease the load quantities.</div>
<div>
</div>
</div>
<div>
<b>How data is stored in Teradata:</b></div>
<div>
<ul style="text-align: left;">
<li>Teradata stores data in tables much like an Excel spreadsheet. </li>
<li>Each row has many columns. The difference is how Teradata processes the data. </li>
<li>The rows are not stored together like an Excel spreadsheet. Each row is physically separated from its neighbor and sent to a particular AMP. </li>
<li>The AMPs then store and retrieve the rows they have been assigned when the Parsing Engine orders them to do so. </li>
<li>The Parsing Engine puts the data rows together, and the user sees their spreadsheet once again.</li>
</ul>
</div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-74041283093189997772015-07-20T19:30:00.000+05:302019-06-20T11:00:53.141+05:30Top Teradata Interview Questions and Answers (1 of 7)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<div class="separator" style="clear: both; text-align: center;">
</div>
</div>
<b>1. Difference between Teradata and Other RDBMS?</b><br />
<br />
The following advantages <a href="http://www.indeed.com/jobs?q=TERADATA&indpubnum=6634974704162507" target="_blank">Teradata </a>is having over other RDBMS:<br />
<br />
<ul style="text-align: left;">
<li>- Parallel processing</li>
<li>- Shared nothing architecture</li>
<li>- Super fast data retrieval</li>
<li>- TASM-Teradata active control system controls traffic efficiently</li>
<li>- Teradata VIEWPOINT gives DBA and users their own view of data</li>
<li>- Excellent data processing options-JOINS, Secondary Indexes, Hash index, Portioned Tables for range queries,</li>
</ul>
<br />
<b>2. Draw the picture of Teradata warehouse?</b><br />
<b><br /></b>
<a href="http://teradatablog.srinimf.com/2015/07/dw-teradata-data-warehousing-pictorial.html" target="_blank">Refer the diagram</a><br />
<br />
<b>3. What are the differences between Star schema and Snow flake schema?</b><br />
<br />
<u>Star Schema:</u><br />
<ul style="text-align: left;">
<li>In a star schema every dimension will have a primary key.</li>
<li>In a star schema, a dimension table will not have any parent table.</li>
<li>Whereas in a snowflake schema, a dimension table will have one or more parent tables.</li>
<li>Hierarchies for the dimensions are stored in the dimensional table itself in star schema.</li>
<li>Whereas hierarchies are broken into separate tables in snowflake schema. These hierarchies help to drill down the data from topmost hierarchies to the lowermost hierarchies.</li>
</ul>
<u>Snow flake Schema:</u><br />
<div>
<br /></div>
<div>
<div>
Snowflake Schema is similar to the star schema. "A schema is called a snow flake if one or more dimension tables do not join directly to the fact table but must join through other dimension tables." </div>
<div>
<br /></div>
</div>
<b>4. Differentiate Dimension table and Fact table?</b><br />
<u><br /></u>
<u>Fact <span class="b24-hit">tables</span></u> are large
and usually distributed by hash. Each star schema contains a <span class="b24-hit">fact</span> <span class="b24-hit">table</span> that is home to measurements
describing a particular process. The measurements, or <span class="b24-hit">facts</span>, are given context by their related
dimensions. The grain of the <span class="b24-hit">fact</span> <span class="b24-hit">table</span> describes the level of detail at
which the <span class="b24-hit">facts</span> are
recorded.<br />
<span class="b24-hit"><br /></span>
<u><span class="b24-hit">Dimension</span> <span class="b24-hit">tables</span></u> are usually small and often
distributed by replication, but <span class="b24-hit">dimension</span> <span class="b24-hit">tables</span> can be distributed by hash. The dimensions provide contextual information, without which reports would be meaningless. Successful dimension design hinges on the proper use of keys, the development of a richly detailed set of dimension columns, and a rejection of the urge to save space.<br />
<div>
<br /></div>
<b>5. What is Factless fact table?</b><br />
<ul style="text-align: left;">
<li>A fact table that contains no facts is called a factless fact table. This oxymoron aptly describes the design technique discussed in this chapter. Although no facts are explicitly recorded in a factless fact table, it does support measurement. A factless fact table is useful in two kinds of situations:</li>
<li>Factless fact tables for events record the occurrence of activities. Although no facts are stored explicitly, these events can be counted, producing meaningful process measurements. Examples include the number of documents processed or approved, the number of calls to a customer support center, or the number of impressions of an advertisement.</li>
<li>Factless fact tables for conditions are used to capture significant information that is not part of a business activity. Conditions associate various dimensions at a point in time. When compared with activities, they provide valuable insight. Examples of conditions include eligibility of people for programs, the assignment of salesreps to customers, active marketing programs for a product, or special weather conditions in effect.</li>
</ul>
<b>6. Describe the different types of dimension tables? And explain with</b><br />
<b>Examples?</b><br />
<br />
<ul style="text-align: left;">
<li>- Start schema</li>
<li>- Galaxy Schema</li>
<li>- Fact constellation Schema</li>
<li>- Snow flake schema</li>
</ul>
<br />
<b>7. Explain Teradata important components with architecture?</b><br />
<b><br /></b>
<a href="http://teradatablog.srinimf.com/2015/01/teradata-unique-architecture.html" target="_blank">Refer Teradata Architecture</a><br />
<b><br /></b>
<b>8. Difference between SMP and MPP?</b><br />
<b><br /></b>
<b>MPP</b><br />
<ul style="text-align: left;">
<li>Stands for Massively parallel processing</li>
<li>A computing that uses many CPUs in parallel to execute a single program</li>
<li>CPU has its own memory which prevents hold up</li>
<li>Does not suffer from bottleneck when all CPUs attempt to access memory once</li>
<li>Difficult to program as applications must be divided to communicate with each other</li>
</ul>
<b>SMP</b><br />
<ul style="text-align: left;">
<li>Stands for symmetric processing</li>
<li>A computing where many CPUs are available for individual processes simultaneously</li>
<li>CPU attempts to access the memory at once, so there is hold up</li>
<li>Does suffer from bottleneck when all CPUs attempt to access memory once</li>
<li>Easier to program</li>
</ul>
<b>9. Explain the Shared nothing architecture?</b><br />
<br />
<b></b>
If the user request asks for all of the rows in a table, every AMP should participate along with all the other AMPs to complete the retrieval of all rows. This type of processing is called an all AMP operation and an all rows scan.<br />
<br />
However, each AMP is only responsible for its rows, not the rows that belong to a different AMP. As far as the AMPs are concerned, it owns all of the rows.<br />
<br />
Within Teradata, the AMP environment is a "shared nothing" configuration. The AMPs cannot access each other's data rows, and there is no need for them to do so.<br />
<div>
<br /></div>
<b>10.What is a set table?When we will use multiset table?</b><br />
<b><br /></b>
<b>Set table: </b>SET TABLE means that Duplicate ROWS are rejected. If your system is in Teradata mode, then SET tables will be the default. You can be in Teradata mode and explicitly define a Multiset table.<br />
<br />
<b>Multiset table:</b> A MULTISET Table means the table will ALLOW duplicate rows. If your system is in ANSI mode, then MULTISET tables will be the default. In either Teradata mode or ANSI mode, you can specifically state (SET or MULTISET) for the table type desired. The problem with Multiset tables is if you have a Non-Unique Primary Index, and accidentally load the table twice, you have duplicate rows.</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-58690993404220904252015-07-20T14:14:00.001+05:302016-06-25T12:20:20.214+05:30DW: Teradata Data warehousing Pictorial Diagram<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisqmG60yz7Iia5s-aXMtSrY8MLO8c2FfsSSqBEiRPk79SNl1bCB2s_srZdZhJm1l5WQXIAg298zDbo90fgn9qWMXgRNaDGD_NT1pfWiFBQA_q2nN5gCL_w4FpE4P3jvoNR2oxtzaHFOgY/s1600/Teradata+Data+Warehousing.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisqmG60yz7Iia5s-aXMtSrY8MLO8c2FfsSSqBEiRPk79SNl1bCB2s_srZdZhJm1l5WQXIAg298zDbo90fgn9qWMXgRNaDGD_NT1pfWiFBQA_q2nN5gCL_w4FpE4P3jvoNR2oxtzaHFOgY/s320/Teradata+Data+Warehousing.png" width="320" /></a></div>
<b>ODS :</b> Operational data source<br />
<br />
<b>Teradata Load Server:</b> Where data received from ODS is stored in Teradata load server.<br />
<br />
<b>RDBMS:</b> This is the database, which interacts with Tearadata Load server.</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-42803687951301061102015-07-19T19:00:00.000+05:302015-07-19T19:00:00.809+05:303 Uses of SAMPLE function in Teradata<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.indeed.com/jobs?q=TERADATA&indpubnum=6634974704162507" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;" target="_blank"><img alt="Teradata+Career+Jobs+Articles+Apply Today" border="0" height="212" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUXZjpwHqnEVyNQ72IB3izqYDNGCpb-sp0H4pRfRTWcfRdoVQOC3ZwQL3KjSh55a1xmE9sYMF7W9kRcJzbfCEdbAXbpv8InBtwKClGaX45lcm5BkKuB3eZ4LWh5Tj5jzq7ydJmDOWXBhg/s320/ID-10065235.jpg" title="Teradata+Career+Jobs+Articles+Apply Today" width="320" /></a></div>
<b>Usage-1</b><br />
<br />
It returns a rows randomly from Teradata database.<br />
<br />
<b>Syntax for SAMPLE function:</b><br />
<br />
SAMPLE [WITH REPLACEMENT]<br />
[RANDOMIZED ALLOCATION]<br />
[WHEN <condition> THEN]<br />
{<number-of-rows> | <percentage>}<br />
[…,<number-of-rows> | <percentage>]<br />
[ELSE {<number-of-rows> |<br />
<percentage } END]<br />
<div>
<br /></div>
<div>
<b>Usage 2</b></div>
<div>
<br /></div>
<div>
It allows the user to get absolute number of rows or percentage of rows.</div>
<div>
<b><br /></b></div>
<div>
<b>Usage 3</b></div>
<div>
<br /></div>
<div>
It also allows the rows to get from multiple samples</div>
<div>
<br /></div>
<div>
<b>How to get random rows from a table:</b></div>
<div>
<br /></div>
<div>
SELECT * from sample_table</div>
<div>
SAMPLE 5;</div>
<div>
<br /></div>
<div>
<b>How to get certain percentage of rows:</b></div>
<div>
<br /></div>
<div>
SELECT * from sample_table</div>
<div>
SAMPLE .30;</div>
<div>
<br /></div>
<div>
<b>How to get multiple SAMPLE data from a single tables with out duplicates:</b></div>
<div>
<br /></div>
<div>
SELECT * from sample_table</div>
<div>
<br /></div>
<div>
SAMPLE .20, SAMPLE .30</div>
<div>
<br /></div>
<div>
ORDER BY 1,2;</div>
<div>
<br /></div>
<div>
<b>SQL query to get 3 samples with duplicates: </b>So 'WITH REPLACEMENT" you will get duplicate rows:</div>
<div>
<br /></div>
<div>
<div>
SELECT ST_ID</div>
<div>
,CC_ID</div>
<div>
,SSID</div>
<div>
FROM sample_table</div>
<div>
SAMPLE <b>WITH REPLACEMENT</b> 8, 8, 8</div>
<div>
ORDER BY 3, 1, 2 ;</div>
</div>
<div>
<b><br /></b></div>
<div>
<b>What is percentage calculation in SAMPLE:</b></div>
<div>
<br /></div>
<div>
Total rows present in a tables/Percentage given in SQL query</div>
<div>
<br /></div>
<div>
Ex:</div>
<div>
<br /></div>
<div>
Total rows: 100</div>
<div>
Percentage: 0.5</div>
<div>
<br /></div>
<div>
Therefore" 100/.5= 100/(5/10x100): 2 rows</div>
<div>
<br /></div>
<div>
<br /></div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-59133833685979758732015-07-05T11:46:00.002+05:302015-07-05T15:14:51.924+05:30SQL Query to Create a View in Teradata<div dir="ltr" style="text-align: left;" trbidi="on">
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left; margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="http://www.indeed.com/jobs?q=TERADATA&indpubnum=6634974704162507" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;" target="_blank"><span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><img alt="Teradata+Jobs" border="0" height="212" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKorWw27yJ54ZhsAUg4aW1-pJBEA2MBGEFchso9ffpsvs0G9QuRoEV65UUMlMHCibRMBIvENp4mWWCoH_HO9gZajE2IufH7P54k7GVkMJ7vtpT_Vqnidlm7KCtABwk8JmnZ_PHmsnym9M/s320/ID-100109908.jpg" title="Teradata+Jobs" width="320" /></span></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></td></tr>
</tbody></table>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">Simple SQL query to create a view in Teradata:</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">CREATE View Employee_V AS</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">SELECT Employee_No</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ,First_Name</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ,Last_Name</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ,Dept_No</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">FROM Employee_Table ;</span><br />
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">- A view we create to restrict access to certain columns</span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">-To restrict access to certain derived columns</span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">-To restrict access to join tables</span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">-To restrict access to certain rows</span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">View is basically in a semantic layer. It supports to Presentation layer. Some time a view can change column names, a view can derive new columns , also aggregate columns.</span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<b><span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">Simple rules for views:</span></b></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">- A view should not contain Order By</span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">-All aggregate columns must have ALIAS</span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">-All derived column must have ALIAS</span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<br /></div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-10971815169892226402015-05-20T08:37:00.001+05:302019-06-22T13:09:20.991+05:30Real Rules to Qualify as Teradata Index<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9Izd92XvdMZ9S-8X1WPm3DAAk9Zw7gq6INm0gQz0vJUTFmK4BRPVAd-tKoray5aGwFWq9rCglc2J238H-JHRe2SaNrqgXdQLcB-t8-gE84gZemywAJzqPNJgh_BMdpBYMLDxzHzApIsI/s1600/Teradata+indexes.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Indexes in teradata" border="0" data-original-height="400" data-original-width="712" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9Izd92XvdMZ9S-8X1WPm3DAAk9Zw7gq6INm0gQz0vJUTFmK4BRPVAd-tKoray5aGwFWq9rCglc2J238H-JHRe2SaNrqgXdQLcB-t8-gE84gZemywAJzqPNJgh_BMdpBYMLDxzHzApIsI/s1600/Teradata+indexes.jpg" title="Indexes in teradata" /></a></div>
<div>
<br /></div>
Indexing is one of the most important features of the Teradata RDBMS. In the Teradata RDBMS, an index is used to define row uniqueness and retrieve data rows, it also can be used to enforce the primary key and unique constraints for a table.<br />
<div>
<br />
<h3>
The Teradata RDBMS support five types of indexes</h3>
<ul style="text-align: left;">
<li>Unique Primary Index (UPI) </li>
<li>Unique Secondary Index (USI) </li>
<li>Non-Unique Primary Index (NUPI) </li>
<li>Non-Unique Secondary Index (NUPI) </li>
<li>Join Index </li>
</ul>
The typical index contains two fields: a value and a pointer to instances of that value in a data table. Because the Teradata RDBMS uses hashing to distribute rows across the AMPs, the value is condensed into an entity called a row hash, which is used as the pointer.<br />
<br />
The row hash is not the value, but a mathematically transformed address. The Teradata RDBMS uses this transformed address as a retrieval index.</div>
<div>
<br />
<h3 style="text-align: left;">
The following rules apply to the indexes used in the Teradata Relation database:</h3>
<ul style="text-align: left;">
<li>An index is a scheme used to distribute and retrieve rows of a data table. It can be based on the values in one or more columns of the table. </li>
<li>A table can have a number of indexes, including one primary index, and up to 32 secondary indexes. </li>
<li>An index for a relational table may be primary or secondary, and may be unique or non-unique. Each kind of index affects system performance, and can be important to data integrity. </li>
<li>An index is usually defined on a table column whose values are frequently used in specifying WHERE constraints or join conditions. </li>
<li>An index is used to enforce PRIMARY KEY and UNIQUE constraints. </li>
</ul>
<br />
<b>Read more</b><br />
<ul style="text-align: left;">
<li><a href="http://teradata.uark.edu/research/wang/indexes.html">Teradata+Indexes</a></li>
</ul>
</div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-37059383914245649762015-05-01T14:36:00.001+05:302019-06-22T12:57:44.338+05:305 Steps Transition Your Career To Analytics<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyt33YGgmNz_JcNDsyxwMP4cxgLp5RGtworjmFRDyF1c-2XaTtFWcm45kidPjTggdZF2Q1YPcXu7lyZQ858lOiEgw5wL_2z215mjaSueMv1RWV1wuJu9lqeTwy6YHBLBH6gxq2Wyj34sM/s1600/career+in+analytics.jpg" imageanchor="1"><img alt="career in analytics" border="0" data-original-height="400" data-original-width="712" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyt33YGgmNz_JcNDsyxwMP4cxgLp5RGtworjmFRDyF1c-2XaTtFWcm45kidPjTggdZF2Q1YPcXu7lyZQ858lOiEgw5wL_2z215mjaSueMv1RWV1wuJu9lqeTwy6YHBLBH6gxq2Wyj34sM/s1600/career+in+analytics.jpg" title="career in analytics" /></a></div>
<br />
If you devour all things analytics, even to the point of setting up Google alerts to help you begin or progress in your analytics career, then you’ll find this five-lesson blog series helpful.<br />
<div>
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2IOMeZgm7wmXyvSEelMKs7S1xWMoeFvo-flvP4tC9XheCjwg2oSo36PCAhyphenhyphenMVtk4k0OOaij3cyFi6lfkzdsIaiqp-n_-IsDIRgl8EpH0gqK1zeC-nOM13o6QA0-1Yv4FNUw2-Ox-m-xE/s1600/Srinimf.png"></a>These lessons are part of Aryng’s Analytics series for individuals looking to transition to a career in analytics or who are new to an analytics role.</div>
<div>
<br />
I hope to answer all the questions I have received from readers of my blog on “Three Steps to Identify the Analytics Training You Need”. Before we go further, understand your fit to an analytics role by assessing your own analytics aptitude. If you don’t have high analytics aptitude, you won’t have fun being an analyst.<br />
<br />
<h3 style="text-align: left;">
Lesson 1 – Understand the analytics landscape and identify your ideal analytics job</h3>
So, what constitutes an analytics job? Is it the same as big data job?<br />
<br />
The analytics landscape is fraught with over-hyped and over-used terms, so before we go further, let me briefly clarify some of the terminology. (This subject is discussed in-depth in my book, “Behind Every Good Decision”, so feel free to start there as well. You can also download Chapter 7 of the book FREE here, which discusses analytics talent requirements in detail as part of the leadership toolkit.)<br />
<br />
Believe it or not, “analytics” is not synonymous with “Big Data” even though these days it is often mentioned in the same breath. Let’s discuss that in a moment.<br />
<br />
First let’s define “analytics” vs. “business intelligence” (BI). Business intelligence and analytics are actually two distinct processes that involve different tools and serve different purposes.<br />
<br />
When a user interacts with a system (such as when you checkout groceries from your local supermarket), data is produced, collected, cleaned and stored using data solutions including Teradata, Hadoop and Oracle. Data is then accessed via reports and, increasingly, via graphical dashboards. BI includes all components of the operation, from when data is collected to when it is accessed.</div>
<div>
<br /></div>
<h4 style="text-align: left;">
References</h4>
<div>
<ul style="text-align: left;">
<li><a href="http://www.forbes.com/sites/piyankajain/2015/01/05/5-steps-to-transition-your-career-to-analytics-step-1-identify-your-ideal-job/"></a><a href="http://www.forbes.com/sites/piyankajain/2015/01/05/5-steps-to-transition-your-career-to-analytics-step-1-identify-your-ideal-job/">Read more details</a></li>
</ul>
</div>
</div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0tag:blogger.com,1999:blog-6527446771725518168.post-75874559523730425792015-04-07T19:48:00.002+05:302019-06-29T16:13:30.469+05:30Teradata Utilities And Functionalities a Quick Tutorial<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; display: inline !important; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyDbyqD3SWaN4yFBycfSPYhfB1So7uYmA6xNGdniEf4h4m2eKJMOAOFP8Ve1lHCuoK1qL5iPIEODRLGOY8UpjQ7AtDSUwDScz8RdPW7xewQg8Rs2Ty3E0nUrWLOlweH89CBHdYh2nIe1M/s1600/Teradata+utilities.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="400" data-original-width="712" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyDbyqD3SWaN4yFBycfSPYhfB1So7uYmA6xNGdniEf4h4m2eKJMOAOFP8Ve1lHCuoK1qL5iPIEODRLGOY8UpjQ7AtDSUwDScz8RdPW7xewQg8Rs2Ty3E0nUrWLOlweH89CBHdYh2nIe1M/s1600/Teradata+utilities.jpg" /></a></div>
<br />
<br />
<ul style="text-align: left;">
<li><b>BTEQ</b> – The first Teradata query tool. It imports and exports data and should be used when < 500,000 rows. BTEQ is an excellent report writer.</li>
<li><b>FastLoad</b> - performs Inserts into empty Teradata tables in 64K blocks. There can be NO Secondary Indexes, Triggers, Referential Integrity, or Join Indexes on the table.</li>
<li><b>MultiLoad -</b> loads to populated Teradata tables in 64K blocks. MultiLoad supports Inserts, Updates, Deletes, and Upserts. There can be NO Unique Secondary Indexes, Triggers, Referential Integrity, or Join Indexes on the table.</li>
<li><b>TPump </b>- loads into Empty or Populated Tables and is a lot like MultiLoad except TPump does all of the work at the row level and NOT the block level. It is called TPump because you can turn up or down the pump to increase or decrease the load quantities, and it allows Secondary Indexes, Referential Integrity, Triggers, or Join Indexes.</li>
<li><b>FastExport </b>– Exports data off of Teradata at the block level. It is much faster than a BTEQ Export and is used for exports when there are more than 500,000 rows exported.</li>
<li><b>TPT</b> – The Teradata Parallel Transport is one scripting language that combines all of the above utilities into one common utility.</li>
</ul>
<br />
<br /></div>
Srinihttp://www.blogger.com/profile/07397528550702315947noreply@blogger.com0