What is a lock?

June 25, 2012

A lock is the mechanism that controls access to data pages and table spaces.

DB2 Comments Off

What is a Cartesian product?

June 25, 2012

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

DB2 Comments Off

What is the difference between IN sub selects and EXISTS sub select?

June 25, 2012

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).

DB2 Comments Off

Compare a sub select to a join.

June 25, 2012

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

DB2 Comments Off

What is the FREE command?

June 25, 2012

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

DB2 Comments Off

What is DCLGEN?

June 25, 2012

A DCLGEN stands for declarations generator; it is a facility to generate DB2 SQL data structures in COBOL or PL/1 programs.

DB2 Comments Off

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

June 25, 2012

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.

DB2 Comments Off

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

June 25, 2012

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

DB2 Comments Off

What is the difference between Static and Dynamic SQL?

June 25, 2012

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.

DB2 Comments Off

What are the functions of Bind?

June 25, 2012

BIND mainly performs two things: Syntax checking and Authorization checking. It binds together all packages into an application plan hence the name BIND. Apart from this bind has optimizer as a subcomponent. Its function is to determine the optimum access strategy.

DB2 Comments Off