-
What is the purpose of Before Journal and After journal?
yuvaevergreen
- Jun 7th, 2011
Before Journal keeps the images of the table before making the change to the table. Very useful, if the changes need to be reverted incase of a fault or rollback. After Journal keeps the images of the table after the changes has been made to the table.
SanthuTeradata
- Mar 30th, 2011
We use both before journal and after journal in Permenent journal to do selective or full recovery of data..........consider Today Monday**Yesterday--------->Before ImageTuesday------------>Afte...
-
Fact vs Dimension Table
Which table is large terms of size, fact table or dimension table? Explain
goeljessica
- Sep 7th, 2012
Fact tables: Have any measurable value in it and it contain foreign key of all the dimension table.
Dimension Tables: Do not have any numeric value, and it shows the characteristics that describe a transaction.santhosh
- May 28th, 2012
Dimension:It gives description about fact.
Fact:Is knows as Key Identifier(Measures)
EX:
Empno Empname Sal Deptno
1 A 20,000 201
2 B 15,000 203
All facts are numeric -
Low and High Confidentiality
Explain What are Low and High Confidentiality in Explain command.
yuvaevergreen
- Aug 13th, 2010
The explain generally displays the plan which would be prepared by Optimizer. We can say that(though not accurate), plan would be the steps followed by Teradata to solve a query. Confidence levels ind...
samyukta.mogadali
- Jul 28th, 2010
Low Confidence: Normally when stats are difficult to use preciselyHigh Confidence: Normally Optimizer is sure of the results based on the stats available
-
MultiSet Table and Duplication
Why fastload does not support multiset table and duplication?
saiteja
- Jun 16th, 2017
Fastload does not support duplicates because storing duplicates in fastload is difficult to the system decreases the performance of the fastload when the fastload script fails when you restart the fastload script and the same row hash value is loaded in to second time
senthang
- Jun 22nd, 2011
Fastload will support the MULTISET table but it will ignore the all row duplicates because of the restartability mechanism of fastload.
-
Additive Fact
Is time a semi-additive fact or a non-additive fact?Explain it with an example.
yuvaevergreen
- Sep 7th, 2010
Time is a semi additive measure. If you have a fact table containing information on the number of items in stock in a warehouse, then it would never make sense to aggregate these measures over t...
-
Terminated Job
What happens when we give disp=(new,catlg,catlg) and the job is terminated?
yuvaevergreen
- Apr 4th, 2010
Disp=(new,catlg,catlg) indicates that the dataset to be 1.created in the particular step.2.catalogue the dataset if the job runs successfully.3.catalogue the dataset even if the job abends.
-
Referential Integrity
Which one of the following does not help to maintain referential integrity?a. Unique constraintsb. Primary keysc. Cascading actions d. Foreign keys
-
Log Table
What is log table? What is the use of log table?
saiteja
- Jun 29th, 2017
Log table maintain records of all checkpoints log table is used for restartability
yuvaevergreen
- Oct 7th, 2010
Log table in MLOAD is used to store the results of each phase of the operation. This log table is used by MLOAD for restarting the mload process from the appropriate checkpoint i.e. after the successful phase.
-
Single Row Function
Display details of employees having same char at the star and end postition of their namelike abishikathis name have last character and first character is same
yuvaevergreen
- Jun 8th, 2011
In Teradata,sel * from employee where substr(name,1,1)=substr(name,length(trim(name)),1)
Miao Lin
- May 9th, 2011
SELECT * from employee where SUBSTR(EmployeeName, 1,1) = SUBSTR(EmployeeName, -1,1);
-
Group By
Can you select any column in a Group by query even if that column is not groupable?e.g. Select Dept, Empno, Ave(salary) From T1 Group by DeptCan we select empno in the above query?
-
Bind process
What happens during pre-compilation and finally bind process?
naga1126
- Jun 16th, 2010
Bind process is mainly for two processes:1. To syntax check the Query (To see the Columns and tables referred are actually present etc. )2. To find the efficient access path.Result of Bind is an Executable form of the DBRM.
yuvaevergreen
- Apr 11th, 2010
Precompilation:Separates out the SQL statements in the COBOL program and creates the DBRM.BIND:Creates the executable format of DBRMs.
-
DB2 Large Objects (LOB) Columns
In DB2, Large Objects (LOB) Columns are used to store large objects (Documents, pictures, voice, and mixed media) in database. Which of the following clauses can't be used on LOB columns?a) GROUP BYb) HAVINGc) ORDER BYd) NONE OF THE ABOVE
kris mohan
- Sep 13th, 2011
None of the above
-
what is the result of the following statement?
Given the following transaction in an embedded SQL application: CREATE TABLE dwaine.mytab (col1 INT, col2 INT) INSERT INTO dwaine.mytab VALUES (1,2) INSERT INTO dwaine.mytab VALUES (4,3) ROLLBACK What is the result of issuing the following statement? SELECT * FROM dwaine.mytab
-
Given a read-only application that requires consistent data for every query, which isolation levels should it use to provide the most concurrency with other applications doing updates?
Given a read-only application that requires consistent data for every query, which of the following isolation levels should it use to provide the most concurrency with other applications doing updates? A. Read Stability B. Repeatable Read C. Cursor Stability D. Uncommitted Read
-
If Sql return -805 , what will user want to do
Yaswanth
- Jul 12th, 2021
Dbrm or package not found in olan
Mandar Dindorkar
- Jul 27th, 2012
-805 will never occur if a DBRM is directly bound to a PLAN. -805 occurs when 1) Source Program is changed and per-compiled and new LOAD Module created as a result of change is used at Run time, but ...
-
Find second lowest salary from emp table
display the second lowest salary from emp table without using min & all
-
How do you print the last n rows or the first n rows of a table ?
Read Best Answer
Editorial / Best Answer
nirmal1in- Member Since Jan-2010 | Jan 17th, 2010
This can be accomplished in following way:
Example: table - emp
For First n rows:SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a < 5 --- say n is 5 display first 5 records
For last n rows:SELECT * FROM
FROM )WHERE .a<
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) x
WHERE x.a < 5This query can be used for finding nth row also say n = 5
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a = 5deepthi
- May 17th, 2012
Code- SELECT rownum,a.*FROM(SELECT rownum,b.*FROM emp b ORDER BY rownum DESC) a WHERE rownum <&n;
Vasavi Katakam
- Jul 18th, 2011
For First N Rows:
For Last N Rows:Code- SELECT top 5 * FROM table_name
Code- SELECT * FROM
- (SELECT top 5 * FROM
- table_name
- ORDER BY Columnname DESC)
- ORDER BY Columnname ASC
-
what is explain in teradata?
what is explain and how does it work?
Read Best Answer
Editorial / Best Answer
Madhulathamadduri- Member Since Jan-2010 | Apr 15th, 2011
The EXPLAIN modifier preceding an SQL statement generates an
English translation of the optimizer’s plan. It is fully parsed and
optimized, but not executed.
EXPLAIN returns:
Text showing how a statement will be processed (a plan).
An estimate of how many rows will be involved.
A relative cost of the request (in units of time).
This information is useful for:
• Predicting row counts.
• Predicting performance.
• Testing queries before production.
• Analyzing various approaches to a problem.
EXPLAIN may also be accessed using Teradata Visual Explain.senthang
- Jun 22nd, 2011
1. English version on optimizer plan2. to identify the objects used and kind of locks applied on those objects3. to identify the number of amp operation eg. single ot group amp4. to identify the data ...
yuvaevergreen
- Jun 6th, 2011
Explain statement generally gives the step by step analysis of the queries being executed in the database. It is the plan prepared by PE dispatched to the amps. It could also identify intermediate sp...
-
DB2 Cursor Stablity
i) Cursor Stablity Leads to Higher concurency ii) Repeatable Read Leads to Higher consistency1 Both are True2 i) Is Only True3 ii) is only True4 Neither is TrueBoth are True
-
DB2 Storage groups
Select the correct statement 1 Storage groups can be altered2 Database can be altered3 Tablespace can be altered4 All of the Choices All of the Choices
-
DB2 Indexes
i ) Unique Index can be created only on Primary Key. ii) Query will execute faster if predicates are applied on Indexed column. 1 Both are True2 i) is only True3 ii) is Only True4 Neither is True
-
DB2 Synonyms
i) Synonyms can refer to local and remote table ii) Alias can refer only to local tables Of the Statement 1 Both are TRUE2 Both Are False3 i) is Only True4 ii) is Only True Both Are False
-
DB2 IRLM module
IRLM is a module of DB2 which does1 Logging2 Thread creation3 Concurrency control using locking4 both logging and thread creation
sagar reddy
- Apr 1st, 2015
Concurrency control using locking
-
DB2 View Purpose
Which of the following is not a purpose of a View ?a.To simplify the appearance of data to different usersb.To control access to a tablec.To show more meaningful data, such as summaryd.To store data separately for user copye.To get data from multiple tables To store data separately for user copy
ambareeshtss
- Oct 19th, 2008
DB2 table may have many fields, All the users are not intended to see some fields. To customise this, Views are created for DB2 tables. A single DB2 tables can have any no.of views.
-
DB2 control structure
A control structure used by DB2 to communicate with the application program isa.Address spaceb.Authidc.Threadd.Viewe.String Thread
-
Which of the following is not true about Bind ?
a.Programs bound to packages must still be designated in a plan.b.SQL statements are validated during Bind.c.Authorization is verified during bind.d.SQL statements cannot be executed unless bind is performed.e.The program should determine the db2 access path before the bind is performed.The program should determine the db2 access path before the bind is performed.
yuvaevergreen
- Mar 26th, 2010
The program should determine the db2 access path before the bind is performed.
-
What happens when more than one row satisfies a Select statement that is executed outside of any cursor processing?
pawan_singh
- May 14th, 2010
If you using just a simple select SQL statement and it is giving you more than one row as output than you will get a SQL code retrun -811. To avaoid this depending your requirement you can modify your query or if you need more than one row than just use cursor.ThanksPawan
-
Explain about CHAR function and it's use in DB2
yuvaevergreen
- Mar 26th, 2010
Returns a fixed-length character string representation of Datetime Character Integer Decimal Floating-point number Row ID
kalone
- Feb 1st, 2010
If we want to concatenate date or time values to some text ,then we have to convert those values to string char first ,for this in DB2 we have CHAR() function.Eg. CHAR(CURRENT DATE)
-
How many null values does a column accept if it is declared as a unique key?
Pritesh Modi
- Jun 2nd, 2011
There is only one null values accepted by a column if you assign a column as unique key constraint.thanking youpritesh
-
What is the main use of FOREIGN KEY?
Read Best Answer
Editorial / Best Answer
Answered by: MANAS KUMAR ROUT ( USA ) (TCS) (Eli LILLY ) - PFIZ
- Jul 6th, 2007
Hey Ramesh,
I understand your interviewer's enthusiasm to know about the Foreign Key.? See foreign key definitions says its states the relationship of one attribute(Column) of one table to the corresponding column on the other table.
So one os a parent and other is child. PARENT is that table which holds the primary key and CHILD is table which holds the referential key. Hence every entry in the PRIMARY table is unique where as same entry might be repeating in the child table. BUT that same has to be present in the PARENT table (Primary KEY).
Now Going by definition Foreign key can be of two types :
========================================
1.? ON DELETE SET NULL
2.? ON DELETE SET CASCADE
DETAILS:
=======
On delete set null :
=======
When a foreign key is created by on delete set null definition then when you delete one row from the primary?column (of parent table ) , then the corresponding entry in the foreign key table (Child table ) have the value "NULL" for that particular column.
ON Delete Set Cascade:
================
When the foreign key is created by this definition then when you delete the primary column (any one row -unique data) , then the Child table forcefully deletes all the rows in the child table having same value for that particular column.
I hope u got the crux of the Foreign key
Thanks
Manas Kumar Rout
SAS Consultant
Oracle Certified Associate
Unix Consultant
shweta dhumal
- Sep 8th, 2011
foreign key is primary key of other table. for example: 'STUDENT' is one table which stores information about students and 'SID' is primary key in that table and 'MARKS' is another table which stor...
shweta dhumal
- Sep 8th, 2011
The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted. For example, say we have two tab...
-
How do you find the number of rows in a table?
-
-
How do we unload the DB2 tables using JCL?
yuvaevergreen
- Mar 26th, 2010
We can use UNLOAD utility for loading a table to file. Following is a sample JCL.//STEP1 EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DSN'//SYSREC DD DSN=USERID.SMPLUNLD.SYSREC,// DISP=...
Smitha Brindavan
- Mar 15th, 2007
If DBP1 is the subsystem name, then the below unload step can be used//STEP001 EXEC DBP1UNLD,USER='USERNAME',ULDTAB='FILE1',DATE='UNLOAD(+1)' //UNLOAD.SYSREC00 DD SPACE=(CYL,(6...
-
What is check pending and copy pending status in DB2, how do you resolve it?
Mandar Dindorkar
- Jul 27th, 2012
Check Pending is when you have data added to the table using either insert or LOAD utility which violate table check constraints. The IBM DB2 LOAD UTILITY has ENFORCE CONSTRAINTS and similar options w...
Mohit
- May 28th, 2012
SET INTEGRITY Option....please read IBM libraries before using this.
-
How the durability(one amongst the ACID model) of database is achieved while loading table with 'Log No' option?
manasa
- Nov 4th, 2011
When we load data with LOG NO then the table goes to copy pending status and is not available for updating..
It can be accessed for read only.
So,we need to take a image copy which will change the copy pending status to RW.
This IC will be used as a backup and thus durability can be achieved. -
-
How to find out the 10th highest salary in SQL query?
yuvaevergreen
- Jun 14th, 2011
In Teradata, even if the duplicate salaries are present, below query can be used.sel b.* from (sel a.*,rank() over (order by a.salary desc) as rnk from(sel salary from samples.empgroup by salary ) as a) bwhere b.rnk=10
rohit1505
- May 21st, 2011
SELECT *FROM emp aWHERE &n = (SELECT COUNT(DISTINCT sal))FROM empbWHERE a.emp<=b.emp);
-
-
What operator performs pattern matching?
saurabh
- Sep 5th, 2011
Underscore operator (_) for single character matching n wildcard (%) for any length string matching...
example a column having abhi,abhaaa, bhaaa,bmddd,cmmm;and i want all those name that start with a then use like 'a%'; -
SQL Query to return only duplicate rows and count
I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated?
yuvaevergreen
- Jun 14th, 2011
sel name,count(*) from retail.yuvagroup by 1 order by 2 having count(*) >1
-
TRUNCATE TABLE EMP;,DELETE FROM EMP;, Will the outputs of the above two commands?
Both will result in deleting all the rows in the table EMP.Answered by Scott on 2005-05-12 10:31:58: The difference is that the TRUNCATE call cannot be rolled back and all memory space for that table is released back to the server. TRUNCATE is much faster than DELETE and in both cases only the table data is removed, not the table structure.
yuvaevergreen
- Jun 14th, 2011
In Teradata,Drop table and delete table are the options. delete table is preferred.
vinod.km
- May 14th, 2009
CREATE TABLE ABC
(
Col1 INT
)INSERT INTO ABC VALUES (1)
BEGIN TRAN
TRUNCATE TABLE ABC
ROLLBACK TRAN
SELECT * FROM ABCIn this way, I am able to roll back truncate command. So in
SQL server we can Roll Back TRUNCATE Command. -
