还是那只鱼-newcoin's blog

Q241) DB2 can implement a join in three ways using a merge join, a nested join or a hybrid join. Explain the differences?

A241) A merge join requires that the tables being joined be in a sequence; the rows are retrieved with a high cluster ratio index or are sorted by DB2. A nested join does not require a sequence and works best on joining a small number of rows. DB2 reads the outer table values and each time scans the inner table for matches. The hybrid join is a nested join that requires the outer table be in sequence.

Q242) Compare a subselect to a join? （文章来源：http://www.newcoin.info）

A242) Any subselect can be rewritten as a join, but not vice versa. Joins are usually more efficient as join rows can be returned immediately, subselects require a temporary work area for inner selects results while processing the outer select.

Q243) What is the difference between IN subselects and EXISTS subselect?

A243) If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN. (IN for index is the mnemonic).

Q244) What is a Cartesian product?

A244) A Cartesian product results from a faulty query. It is a row in the results for every combination in the join tables.

Q245) DB2 What is the difference between a package and a plan? How does one bind 2 versions of a CICS transaction with the same module name in two different CICS regions that share the same DB2 subsystem?

A245) Package and plan are usually used synonymously, as in this site. Both contain optimized code for SQL statements – a package for a single program, module or subroutine contained in the database request module (DBRM) library. A plan may contain multiple packages and pointers to packages. The one CICS module would then exist in a package that could be referenced in two different plans.

Q246) What is an asychronous write?

A246) It is a write to disk that may occur before or long after a commit. The write is controlled by the buffer manager.

Q247) What is a lock?

A247) A lock is the mechanism that controls access to data pages and tablespaces.

Q248) What is meant by isolation level?

A248) This is a key concept for any relational database. Isolation level is the manner in which locks are applied and released during a transaction. For DB@ a ‘repeatable read’ holds all locks untile the transaction completes or a syncpoint is issued. For transactions using ‘cursor stability’ the page lock releases are issued as the cursor ‘moves’, i.e. as the transaction releases addressability to the records.

Q249) What are leaf pages?

A249) They are the opposite of root pages. Leaf pages are the lowest level index pages – the pages that contain index entries and information to the corresponding table rows.

Q250) What is a precompiler?

A250) It is a DB2 facility for static SQL statements – it replaces these statements with calls to the DB2 language interface module.

Q251) What is a root page?

A251) The opposite of a leaf page; it is the highest level index page. An index can contain only the one root page; all other index pages are associated to the root.

Q252) What is a thread?

A252) A thread is the connection between DB2 and some other subsystem, such as CICS or IMS/DC.

Q253) Which transaction use a command thread ?

A253) Only the DSNC transaction uses a command thread.

Q254) What is the purpose of the DSNC transaction ?

A254) The DSNC transaction is used for controlling the CICS Call Attach Facility(CAF) and for Displaying CAF statistics.

DB2 Utilities

Q255) What does the CHECK Utility do ?

A255) The CHECK Utility checks the referential integrity of table relations and checks the integrity of the indexes by matching index column values to table column values.

Q256) what types of copies can be made with the COPY Utility ?

A256) The copy Utility can make a full image copy or an incremental image copy .

Q257) Why might full image copies be faster to implement than an incremental image copy?

A257) Because an incremental image copy has to search for changed data and cannot make use of sequential pre fetch. Conversely, a full image copy has no checking to do as it takes advantage of sequential pre fetch.

Q258) How could one combine a set of incremental image copies into a single copy?

A258) By using the MERGECOPY Utility.

Q259) What is the purpose of the QUIESE Utility?

A259) The QUIESE Utility prevents the start of any new table space activity while it gives active threads a chance to finish their tasks. Once all thread are inactive, it records information to establish a point of consistency for future recovery.

Q260) What does the REORG Utility do?

A260) The REORG Utility will sort the index space and table space to conform with the primary index or clustering index specified in the DDL. It will also reclaim the space from dropped simple table spaces.

Q261) What can the SET option of the Repair Utility accomplish?

A261) The set option of the Repair utility can reset a copy pending, check pending, and recover pending flags.（文章来源：http://www.newcoin.info）

Q262) What can the Locate option of the Repair Utility accomplish?

A262) The Locate option of the Repair Utility can delete a row from a table space, repair broken table space pages, and replace data as specific locations in a table space or index.

Q263) What does the RUNSTATS Utility do?

A263) The RUNSTATS Utility collects statistical information for DB2 table spaces, partitions, indexes, tables and columns and stores this data in the DB2 Catalog.

Q264) Why use RUNSTAT Utility?

A264) Because the DB2 Optimizer need accurate data in order to formulate the most efficient access path given the state of the environment and because the information will help the DBA to monitor the condition of the object in the DB2 subsystem.

Q265) What statistic will tell the DBA how must space can be reclaimed from dropped table spaces on the next reorg run?

A265) The DBA can see this in the PERCDROP column of the SYSIBM.SYSTABLEPART catalog table.

Q266) What DB2 Catalog column tell you when an index needs table reorganized ?

A266) The FAROFFPOS column of the SYSIBM.SYSINDEXPART table.

Q267) What is the STOSPACE Utility used for?

A267) The STOSPACE Utility updates the DB2 catalog with the DASD utilization of the table space and index space data sets.

Q268) What is a SELECT statement?

A268) A select statement is an SQL statement that retrieves data from a table or view.

Q269) What is the syntax of SELECT statement when embedded in a COBOL program?

A269) Exec SQL
SELECT col_name1,col_name2,col_name3

INTO hos:_var1,hos_var2,hos_var3

FROM owner.tablename

WHERE condition

END_EXEC.

Q270) What are column-name qualifiers used?

A270) Column-name qualifier are used as table designator to avoid ambiguity when the column names referenced exist in more than one table used in the SQL statement. Column-name qualifiers are used in correlated references.

Q271) What is correlation names?

A271) IT is a special type of column designator that connects specific column in the various levels of a multilevel SQL query.

Q272) How do you define a correlated name?

A272) A correlated name can be defined in the FROM clause of a query and in the first clause of an UPDATE or DELETE statement.

Q273) What is subquery ?（文章来源：http://www.newcoin.info）

A273) A subquery is a query that is written as part of another query’s WHERE clause. For example :

SELECT col_name1,col_name2

FROM table_A

WHERE col_name3 < ( SELECT Avg(col_name)

FROM table_A

WHERE col_name4 = ‘constant’ )

Q274) What is correlated subquery?

A274) A correlated subquery is one that has a correlation name as a table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery. For example:

SELECT col_name1,col_name2

FROM table_A x1

WHERE col_name3 <

( SELECT Avg(column_name3

FROM table_A

WHERE col_name4 = x1.col_name4 )

Q275) How does the processing of a correlated subquery differ from a non correlated subquery?

A275) The subquery in a correlated subquery is reevaluated for every row of the table or view named in the outer query, while the subquery of a non correlated subquery is evaluated only once.

Q276) What is a result table?

A276) A result table is the product of a query against one or more tables or views ( i.e. it is the place that holds the results of a query).

Q277) What is a cursor?

A277) A cursor is a named control structure used to make a set of rows available to a program.

Q278) What is the syntax required for the creation of a cursor?

A278) EXEC SQL

DECLARE cur_name CURSOR for

SELECT col1,col2

FROM table1

WHERE col1 = search_condition

END-EXEC.

Q279) When is the results table for the query in a DECLARE CURSOR statement created?

A279) The results table for a query specified in a DECLARE CURSOR statement of a cursor is created during the execution of the OPEN CURSOR statement.

Q280) What is read-only cursor?

A280) A read-only cursor is one in which the result table was created by a query containing one of the following :

·a DISTINCT keyword

·a UNION operator

·a column or scalar function

·a GROUP BY clause

·a ORDER BY clause

·a HAVING clause

·a read-only view in the FROM clause

·a FROM clause identifying more than one table or view