还是那只鱼-newcoin's blog

Q201) What is a cursor and what is its function?

A201) An embedded SQL statement may return a number of rows while the programming language can only access one row at a time. The programming device called a cursor controls the position of the row.

Q202) What is referential integrity?

A202) Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

Q203) Usually, which is more important for DB2 system performance – CPU processing or I/O access?

A203) I/O operations are usually most critical for DB2 performance (or any other database for that matter).

Q204) Is there any advantage to denormalizing DB2 tables?

A204) Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.

Q205) What is the database descriptor?

A205) The database descriptor, DBD is the DB2 component that limits access to the database whenever objects are created, altered or dropped.

Q206) What is lock contention?

A206) To maintain the integrity of DB2 objects the DBD permits access to only on object at a time. Lock contention happens if several objects are required by contending application processes simultaneously.

Q207) What is SPUFI?

A207) SPUFI stands for SQL processing using file input. It is the DB2 interactive menu-driven tool used by developers to create database objects.

Q208) What is the significance of DB2 free space and what parameters control it?

A208) The two parameters used in the CREATE statement are the PCTFREE which specifies the percentage of free space for each page and FREEPAGE which indicates the number of pages to be loaded with data between each free page. Free space allows room for the insertion of new rows.

Q209) What is a NULL value? What are the pros and cons of using NULLS?

A209) A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value. It’s the DB2 equivalent of TBD on an organizational chart and often correctly portrays a business situation. Unfortunately, it requires extra coding for an application program to handle this situation.

Q210) What is a synonym? How is it used?

A210) A synonym is used to reference a table or view by another name. The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated. The synonym is linked to the AUTHID that created it.

Q211) What is an alias and how does it differ from a synonym?

A211) An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view. The alias is not dropped when the table is dropped.

Q212) What is a LIKE table and how is it created?

A212) A LIKE table is created by using the LIKE parameter in a CREATE table statement. LIKE tables are typically created for a test environment from the production environment.

Q213) If the base table underlying a view is restructured, eg. attributes are added, does the application code accessing the view need to be redone?

A213) No. The table and its view are created anew, but the programs accessing the view do not need to be changed if the view and attributes accessed remain the same.

Q214) Under what circumstances will DB2 allow an SQL statement to update more than one primary key value at a time?

A214) Never. Such processing could produce duplicate values violating entity integrity. Primary keys must be updated one at a time.

Q215) What is the cascade rule and how does it relate to deletions made with a subselect?

A215) The cascade rule will not allow deletions based on a subselect that references the same table from which the deletions are being made.

Q216) What is the self-referencing constraint? （文章来源：http://www.newcoin.info）

A216) The self-referencing constraint limits in a single table the changes to a primary key that the related foreign key defines. The foreign key in a self referencing table must specify the DELETE CASCADE rule.

Q217) What are delete-connected tables?

A217) Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect the contents of the foreign key table.

Q218) When can an insert of a new primary key value threaten referential integrity?

A218) Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity.

Q219) In terms of DB2 indexing, what is the root page?

A219) The simplest DB2 index is the B-tree and the B-tree’s top page is called the root page. The root page entries represent the upper range limits of the index and are referenced first in a search.

Q220) How does DB2 use multiple table indexes?

A220) DB2 use the multiple indexes to satisfy multiple predicates in a SELECT statement that are joined by an AND or OR.

Q221) What are some characteristics of columns that benefit from indexes?

A221) Primary key and foreign key columns; columns that have unique values; columns that have aggregates computed frequently and columns used to test the existence of a value.

Q222) What is a composite index and how does it differ from a multiple index?

A222) A multiple index is not one index but two indexes for two different columns of a table. A composite index is one index made up of combined values from two columns in a table. If two columns in a table will often be accessed together a composite index will be efficient.

Q223) What is meant by index cardinality?

A223) The number of distinct values for a column is called index cardinality. DB2′s RUNSTATS utility analyzes column value redundancy to determine whether to use a tablespace or index scan to search for data.

Q224) What is a clustered index?

A224) For a clustered index DB2 maintains rows in the same sequence as the columns in the index for as long as there is free space. DB2 can then process that table in that order efficiently.

Q225) What keyword does an SQL SELECT statement use for a string search?

A225) The LIKE keyword allows for string searches. The % sign is used as a wildcard.

Q226) What are some SQL aggregates and other built-in functions?

A226) The common aggregate, built-in functions are AVG, SUM, MIN, MAX, COUNT and DISTINCT.

Q227) How is the SUBSTR keyword used in sql?

A227) SUBSTR is used for string manipulation with column name, first position and string length used as arguments. E.g. SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.

Q228) What are the three DB2 date and time data types and their associated functions?

A228) The three data types are DATE, TIME and TIMESTAMP. CHAR can be used to specify the format of each type. The DAYS function calculates the number of days between two dates. (It’s Y2K compliant). （文章来源：http://www.newcoin.info）

Q229) Explain transactions, commits and rollbacks in DB2.

A229) In DB2 a transaction typically requires a series of updates, insertions and deletions that represent a logical unit of work. A transaction puts an implicit lock on the DB2 data. Programmers can use the COMMIT WORK statement to terminate the transaction creating smaller units for recovery. If the transaction fails DB2 uses the log to roll back values to the start of the transaction or to the preceding commit point.

Q230) What is deadlock?

A230) Deadlock occurs when transactions executing at the same time lock each other out of data that they need to complete their logical units of work.

Q231) What are the four lockable units for DB2?

A231) DB2 imposes locks of four differing sizes: pages, tables, tablespace and for indexes subpage.

Q232) What are the three lock types?

A232) The three types are shared, update and exclusive. Shared locks allow two or more programs to read simultaneously but not change the locked space. An exclusive lock bars all other users from accessing the space. An update lock is less restrictive; it allows other transactions to read or acquire shared locks on the space.

Q233) What is isolation level?

A233) SQL statements may return any number of rows, but most host languages deal with one row at a time by declaring a cursor that presents each row at a unique isolation level.

Q234) What is an intent lock?

A234) An intent lock is at the table level for a segmented tablespace or at the tablespace level for a non-segmented tablespace. They indicate at the table or tablespace level the kinds of locks at lower levels.

Q235) What is the difference between static and dynamic SQL?

A235) Static SQL is hard-coded in a program when the programmer knows the statements to be executed. For dynamic sql the program must dynamically allocate memory to receive the query results.

Q236) What is cursor stability?

A236) Cursor stability means that DB2 takes a lock on the page the cursor is accessing and releases the lock when the cursor moves to another page.

Q237) What is the significance of the CURSOR WITH HOLD clause in a cursor declaration?

A237) The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened.

Q238) What is the SQL Communications Area and what are some of its key fields?

A238) It is a data structure that must be included in any host-language program using SQL. It is used to pass feedback about the SQL operations to the program. Fields are return codes, error messages, handling codes and warnings.

Q239) What is the purpose of the WHENEVER statement?

A239) The WHENEVER statement is coded once in the host program to control program actions depending on the SQL-CODE returned by each sql statement within the program.

Q240) What is the FREE command?

A240) The FREE command can be used to delete plans and/or packages no longer needed.