IBM DB2 interview questions,Faqs

Question :What do you mean by NOT NULL WITH DEFAULT? When will you use it?
Answer :This column cannot have nulls and while insertion, if no value is supplied then it wil have zeroes, spaces or date/time depending on whether it is numeric, character or date/time. Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row
Question :What is the difference between SYNONYM and ALIAS?
Answer :SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator. ALIAS: is retained even if table or tablespace is dropped. ALIAS can be created even if the table does not exist. It is used mainly in distributed environment to hide the location info from programs. Alias is a global object & is available to all.
Question :What is index cardinality? ?
Answer :The number of distinct values a column or columns contain. 103) What is a synonym ? Synonym is an alternate name for a table or view used mainly to hide the leading qualifier of a table or view.. A synonym is accessible only by the creator.
Question :What is filter factor?
Answer :one divided by the number of distinct values of a column.
Question :What are simple, segmented and partitioned table spaces ?
Answer :Simple Tablespace: Can contain one or more tables Rows from multiple tables can be interleaved on a page under the DBAs control and maintenance Segmented Tablespace: Can contain one or more tables Tablespace is divided into segments of 4 to 64 pages in increments of 4 pages. Each segment is dedicated to single table. A table can occupy multiple segments Partitioned Tablespace: Can contain one table Tablespace is divided into parts and each part is put in a separate VSAM dataset.
Question :What is FREEPAGE and PCTFREE in TABLESPACE creation?
Answer :PCTFREE: percentage of each page to be left free FREEPAGE: Number of pages to be loaded with data between each free page
Question :What is an inner join, and an outer join ?
Answer :Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of on e table with all the clause in corresponding columns of the other table or tables. This kind of join which involve a match in both columns are called inner joins. Outer join is one in which you want both matching and non matching rows to be returned. DB2 has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION.

No comments: