What happens to the PLAN if index used by it is dropped?

DB2 INTERVIEW QUESTIONS

(Continued from previous question...)

What happens to the PLAN if index used by it is dropped?

Plan is marked as invalid. The next time the plan is accessed, it is rebound.

(Continued on next question...)

Other Interview Questions

• How would you find out the total number of rows in a DB2 table?
• How do you eliminate duplicate values in DB2 SELECT ?
• How do you select a row using indexes in DB2?
• How do you find the maximum value in a column in DB2?
• How do you retrieve the first 5 characters of FIRSTNAME column of DB2 table EMP ?
• What are aggregate functions?
• Can you use MAX on a CHAR column?
• My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results. Why?
• How do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a complete name?
• What is the use of VALUE function?
• What is UNION,UNION ALL? –
• What is the restriction on using UNION in embedded SQL?
• In the WHERE clause what is BETWEEN and IN? –
• Is BETWEEN inclusive of the range values specified? –
• What is 'LIKE' used for in WHERE clause? What are the wildcard characters? –
• When do you use a LIKE statement?–
• What is the meaning of underscore ( ‘_’ ) in the LIKE statement? –
• What do you accomplish by GROUP BY ... HAVING clause? ––
• Consider the employee table with column PROJECT nullable. How can you get a list of employees who are not assigned to any project?
• What is the result of this query if no rows are selected:
• Why SELECT * is not preferred in embedded SQL programs?
• What are correlated subqueries?
• What is a cursor? why should it be used? –
• How would you retrieve rows from a DB2 table in embedded SQL? –
• How do you specify and use a cursor in a COBOL program? –
• What happens when you say OPEN CURSOR?
• Is DECLARE CURSOR executable?
• Can you have more than one cursor open at any one time in a program ? –
• When you COMMIT, is the cursor closed? - drona questions–
• How do you leave the cursor open after issuing a COMMIT? ( for DB2 2.3 or above only )
• A VARCHAR column REMARKS would be defined as follows:
• What is the physical storage length of each of the following DB2 data types:
• What is the COBOL picture clause of the following DB2 data types:
• What is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)?
• 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 pgm executed ?
• Name some fields from SQLCA.
• How can you quickly find out the # 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 EXPLAIN stored? –
• EXPLAIN has output with MATCHCOLS = 0. What does it mean? –
• How do you do the EXPLAIN of a dynamic SQL statement?
• How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF? Give an example with a host variable in WHERE clause.)
• What are the isolation levels possible ?
• What is the difference between CS and RR isolation levels?
• Where do you specify them ?
• What are the various locking levels available?
• How does DB2 determine what lock-size to use?
• 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?
• What are the 2 sqlcodes that are returned? –
• 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 well till now. Off late, I find that the performance has deteriorated. What happened? ––
• 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 column?
• 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 views updateable ?
• If I have a view which is a join of two or more tables, can this view be updateable?
• 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 creation?
• 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 ALIAS?
• What do you mean by NOT NULL WITH DEFAULT? 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 ( EMP#, MANAGER) where MANAGER is a foreign key which references to EMP# in the same table? Give the exact DDL.
• What is auditing?
• What does WITH HOLD option do ?
• How do you read vsam file?
• What is a NULL value? What are the pros and cons of using NULLS?
• How to increase table space in DB2?
• What is a synonym? How is it used?
• What is a precompiler?
• At what stage DB2 must be up?
• What is check pending and copy pending status in D...
• What techniques are used to retrieve data from more than one table in a single SQL statement?
• How do we unload the DB2 tables using JCL?
• How to read last record in vsam file.,I don't know how many records are there?
• What is a clustered index?
• What is a thread?
• What is a DB2 bind?
• Explain difference between sys.cat and sys.ibm?
• What is the difference between IN subselects and EXISTS subselect?
• What is performance tuning in DB2?
• Is there any advantage to denormalizing DB2 tables?
• What is difference between 911 and 913 error code in DB2 ?
• How will you send data from a cobol file to DB2 table?
• What is a DB2 plan?
• How To perform Performance Tunning on COBOL-DB2 program ?
• What is stored in tables under creator SYSIBM?
• Can anyone tell me when do we need a COLLECTION? I know the concept of PLAN and PACKAGE, but unable to understand about COLLECTION.