DB2 ( IBM Database 2 ) Interview Questions

Are you a Database Administrator? Need to update DBA technical knowledge or need to prepare for a job interview? Check out this collection of DB2 ( IBM Database 2 ) Interview Questions...

What is DB2 ( IBM Database 2 )?
What is an access path?
What is an alias?
Explain what a plan?
What is a DB2 bind?
What information is used as input to the bind process?
What is meant by the attachment facility?
What is meant by AUTO COMMIT
What is a base table ?
What is a base table ?
What is the function of buffer manager ?
What is a buffer pool ?
How many buffer pools are there in DB2?
On the create tablespace, what does the CLOSE parameter do?
What is a clustering index?
What will the COMMIT accomplish?
What is meant by concurrency?
What is cursor stability?
What is the function of the Data Manager?
What is a Database Request Module (DBRM)?
What is a data page?
What is a data type?
What is Declaration Generator ( DCLGEN )?
What does DSNDB07 database do?
What will the FREE command do to a plan?
What is a host variable?
What will the DB2 optimizer do?
What is a page?
What is pagespace?
What is a predicate?
What is a recovery log?
What is a Resource Control Table (RCT)? Describe its characteristics.
Where are plans stored ?
What is meant by repeatable read?
Describe what a storage group (STOGROUP) is?
How would you move a tablespace to a different DASD volume allocated to that tablespace?
What is the format (internal layout) of "TIMESTAMP"?
What is meant by a unit of recovery?
Can DASD types assigned to storage groups be intermixed ?
What are the three tyes of page locks that can be held?
Can DB2 be accessed by TSO users? If yes, which command is used to invoke DB2
How are write I/Os from the buffer pool executed
What is a DB2 catalog ?
In which column of which DB2 catalog would you find the length of the rows for all tables?
What information is held in SYSIBM.SYSCOPY?
What information is contained in a SYSCOPY entry?
What information can you find in SYSIBM.SYSLINKS table?
Where could you look if you had a question about whether a column has been defined as an index?
Once you create a view, where would information about the view be stored?
What is the SQL Communications Area and what are some of its key fields?
What is DCLGEN?
How do you leave the cursor open after issuing a COMMIT?
Give the COBOL definition of a VARCHAR field.
What is the physical storage length of each of the following DB2 data type
What is the COBOL picture clause of the following DB2 data types?
What is the COBOL picture clause for a DB2 column defined
What is DCLGEN?
What are the contents of a DCLGEN?
Is it mandatory to use DCLGEN? If not, why would you use it at all ?
Is DECLARE TABLE in DCLGEN necessary? Why it used?
Will precompile of an DB2 - COBOL program bomb, if DB2 is down?
How is a typical DB2 batch program executed?
What is the easiest way to find out which programs are affected by change in a table's structure ??
Name some of field from SQLCA?
How can you quickly find out the number of rows updated after an update statement?
What is EXPLAIN?
What do you need to do before you do EXPLAIN?
Where is the output of EXPLAN stored?
EXPLAN has output with MATCHCOLS ...
How do you do the EXPLAIN of a dynamic SQL statement ?
How do you simulat the EXPLAIN of an embedded SQL statement in SPUFI/QMF ?
What are the isolation levals possible ?
What is the difference between CS and RR isolation levels ?
When do you specify the isolation level? How ?
I use CS and update a page. Will the lock be released after I am done with that page ?
What are the various locking levels available ?
How does DB2 determine what lock-size to use?
What are the disadvantages of PAGE level lock?
What is lock escalation?
What are the various locks available?
Can I use LOCK TABLE on a view?
What is ALTER?
What is a DBRM, PLAN?
What is ACQUIRE/RELEASE in BIND?
What else is there in the PLAN apart from the access path?
What happens to the PLAN if index used by it is dropped?
What are PACKAGES?
What are the advantages of using a PACKAGE?
What is a collection?
In SPUFI suppose you want to selet maximum of 1000 rows ...
How would you print the output of an SQL statement from SPUFI?
Lot of updates have been done on a table due to which indexes have gone haywire. What do you do?
What is dynamic SQL?
When is the access path determined for dynamic SQL?
Suppose I have a program which uses a dynamic SQL and it has been performing ...
How does DB2 store NULL physically?
How do you retrieve the data from a nullable column?
What is the picture clause of the null indicator variable?
What does it mean if the null indicator has -1, 0, -2??
How do you insert a record with a nullable colunm?
What is RUNSTATS?
When will you chose to run RUNSTATS?
Give some example of statistics collected during RUNSTATS?
What is REORG? When is it used
What is IMAGECOPY
When do you use the IMAGECOPY ?
What is COPY PENDING status ?
What is CHECK PENDING ?
What is QUIESCE ?
What is a clustering index ?
How many clustering indexes can be defined for a table?
What is the difference between primary key and unique index?
What is SQLcode - 922?
What is SQLcode - 811?
What does the SQLcode of -818 pertain to?
Are view updatable?
If I have a view which is a join of two or more tables, can this view be updatable?
What are the 4 environments which can access DB2?
What is an inner join, and an outer join?
What is FREEPAGE and PCTFREE in TABLESPACE createion?
What are simple, segmented and partitioned table spaces?
What is filter factor?
What is index cardinality?
What is a synonym?
What is the difference between SYNONYM and ALLAS?
What do you mean by NOT NULL WITH DEFUALT? When will you use it ?
What do you mean by NOT NULL ? When will you use it ?
When would you prefer to use VARCHAR ?
What are the disadvantages of using VARCHAR ?
How do I create a table MANAGER ...
When is the authorization check on DB2 objects done - at BIND time or run time
What is auditing
MAX number of columns in a DB2 table
I need to view the number of tables existing under one particular OWNER. Is it possible ? If so, please give the SQL query for this ?
I need to view the number of tables existing under one particular OWNER
I need to view the number of tables existing under one particular OWNER. ?
What is JOIN and different types of JOIN ?
an I alter a table when other user is selecting some columns ...
How many sub queries can you combine together?
What are the different methods of accessing DB2 from TSO?
How many buffer pools are available in DB2
B37 abend during SPUFI
How many Buffer pools are there in DB2 and what are they ?
What is the command used by TSO users to invoke DB2 ?
What is the error code - 803 ?
How do you filter out the rows retrieved from a DB2 table ?
What is a collection?
What is SKELETON cursor table (SKCT?
What is the equivalent COBOL DATA Type for Decimal(x,y) in DB2 ?
Can we declare DB2 HOST variable in COBOL COPY book ?
What should be specified along with a cursor in order to continue updating process after commit ?
What is the name of the default DB2 catalog database ?
When can you be sure that a quary will return onlyl one row ?
What is the difference between join and union ?
What is a correlated sub query?
What are the functions of Bind?
What is Maximum number of rows per page
The only place of VSAM KSDS in DB2 is
Can ALl Users Have The Privilege To Use The SQL Statement Select * (DML) ?
What is the size of a data page ?
What is the best lock size that you could use when you create a tablespace?
What is the error code for Unique Index Violation?
Can you define an Index if the table size less than 10 PAGES?
What is the Manimum Length SQLCA and what is the content of SQLCABC?
What is the Manimum number of volumes that can be added to a STOGROUP?
What is the Manimum number of characters that a tablename can have?
What is the meaning of -805 SQL return code ?
When does the SQL statement gets executed when you use cursor in the application programming ?
What does CURRENTDATA option in bind indicate ?
What is the difference between TYPE 1 index and TYPE 2 index?
What are the levels of isolation available with DB2V4?
How do you achieve record level locking in DB2 versions when record level locking is not allowed?
In a DB2-CICS program which is acts as co-ordinator and which is participant?
What does DML stand for and what are some examples of it?
How to define the data items to receive the fetch items for the SQL?
How will you delete duplicate records from a table ?
What is the difference between Where and Having Clause ?
How to see the structure of DB2 table?
How do you declare a host variable ...
What is the maximum number of tables that can be stored on a Partitioned Tables Space?
Name the different types of Table spaces?
What are the Maximum and minimum, number of partitions allowed in a partition tablesspace?
What is the Maximum normber of partitions that can be joined?
What technique is used to retrieve data from more than one table in a single SQL statement?
Explain the use of the WHERE clause?
What is a DB2 bind?
What is a DB2 access path?
What is a DB2 plan?
What is normalization and what are the five normal forms?
What are foreign keys?
Describe the elements of the SELECT Query syntax?
Explain the use of the WHERE clause?
What techniques are used to retrieve data from more than one table in a single SQL statement?
What do the initials DDL and DML stand for and what is their meaning?
What is a view ? Why use it
Explain an outer join ?
What is subselect? Is it different from a nested select ?
What is the difference between group by and order by ?
Explain the EXPLAIN statement ?
What is tablespace ?
What is a cursor and what is its function ?
What is referential integrity ?
Usually, which is more important for DB2 system performance
Is there any advantage to denormalizing DB2 tables ?
What is the database descriptor?
What is lock contention?
What is SPUFI?
What is the significance of DB2 free space and what parameters control it?
What is a NULL value? What are the pros and cons of using NULLS
What is a synonym ? How is it used ?
What is an alias and how does it differ from a synonym?
What is a LIKE table and how is it created?
If the base table underlying a view is restructured ...
Under what circumstances will DB2 allow an SQL satement ...
What is the cascade rule and how does it relate to deletions made with a subselect ?
What is the self-referencing constraint ?
What are delete-connected tables ?
When can an insert of a new primary key value threaten referential integrity ?
In terms of DB2 indexing, what is the root page ?
How does DDB2 use multiple table indexes ?
What are some characteristics of columns that benefit from indexes ?
What is a composite index and how does it differ from a multiple index ?
What is meant by index cardinality?
What is a clustered index?
What keyword does an SQL SELECT statement use for a string search?
What are some SQL aggregates and other built-in functions?
How is the SUBSTR keyword used in SQL?
What are the three DB2 date and time data types and their associated functions?
Explain transactions, commits and rollbacks in DB2?
What is deadlock?
What are the four lockable units for DB2?
What are the three lock types?
What is isolation level?
What is an intent lock?
What is the difference between static and dynamic SQL?
What is cursor stability?
What is the significance of the CURSOR WITH HOLD clause in a cursor declaration?
What is the SQL Communicaions Area and what are some of its key fields?
What is the purpose of the WHENEVER statement?
What is the FREE command?
DB2 can implement a join in three ways using a merge join...
Compare a subselect to a join?
What is the difference between IN subselects and EXISTS subselect?
What is a Cartesian product?
DB2 What is the difference between a package and a plan ?
What is an asychronous write?
What is a lock?
What is meant by isolation level?
What are leaf pages?
What are a precompiler?
What is a root page?
What is a thread?
Which transaction use a command thread?
What is the purpose of the DSNC transaction?
What does the CHECK Utility do?
What types of copies can be made with the COPY Utility?
Why might full image copies be faster to implement than an incremental image copy?
How could one combine a set of incremental image copies into a single copy?
What is the purpose of the QUIESE Utility?
What does the REORG Utility?
What can the SET option of the repair Utility accomplish?
What can the Locate option of the Repair Utility accomplish?
What does the RUNSTATS Utility do?
Why use RUNSTAT Utility?
What statistic will tell the DBA how must space can be reclaimed from dropped table spaces on the next reorg run ?
What DB2 Catalog column tell you when an index needs table reorganized?
What is the STOSPACE Utility used for ?
What is a SELECT statement ?
What is the syntax of SELECT statement when embedded in a COBOL program ?
What are column-name qualifier used ?
What is correlation names ?
How do you define a correlated name ?
What is subquery ?
What is correlated subquery ?
How does the processing of a correlated subquery differ from a non correlated subquery ?
What is a result table ?
What is a cursor ?
What is the syntax required for the creation of a cursor ?
When is the results table for the query in a DECLARE CURSOR statement created ?
What is read-only cursor?
Which DB2 catalog tables are used to produce a list of table column by table for all tables in a database?
Which catalog tables contain authorization information?
Which catalog tables stores referential constraints?
What Utility is used to migrate DB2 from one release to the next?
How would one remove old reorg information from the DB2 catalog?
What happens to a tablespace when its recovery information has been removed and a full recovery is no longer possible?
Where is the access path logic created by the DB2 Optimizer stored?
When is the skeleton cursor table created?
How does one remove entires from the SCT02 table?
When one binds a PACKAGE of a plan what package information is stored and where it is stored?
Where besides the DB2 catalog is database object information stored by DB2?
Can you access the DB2 Directory table using SQL?
Which DB2 command is used to retireve environmental information?
Which command is issued to establish the Boot Strap Data Set after an I/O failure?
How is the status of a utility reset after it has been stopped by DB2?
How can one determine the status of a tablespace?