DB2 Technical Interview Questions frequently asked in interviews.
What do you mean by DBRM and PLAN ?Answer : DBRM is Data Base Request Module, has the SQL statements extracted from the host language program by the pre-compiler PLAN is a result of the BIND process It has the executable code for the SQL statements in the DBRM
What do you mean by ACQUIRE/RELEASE in BIND?
Answer : Determine the point at which DB2 acquires or releases locks against table and Tables spaces, including intent locks.
What else is there in the PLAN apart from the access path?
Answer : PLAN has the executable code for the SQL statements in the host program
What happens to the PLAN if index used by it is dropped?
Answer : Plan is marked as invalid The next time the plan is accessed, it is rebound
What do you mean by PACKAGES ?
Answer : They contain executable code for SQL statements for one DBRM
Explain what are the advantages of using a PACKAGE?
Answer : The main advantages of using a PACKAGE are as cited below.
1. Avoid having to bind a large number of DBRM members into a plan
2. Avoid cost of a large bind
3. Avoid the entire transaction being unavailable during bind and automatic rebind of a plan.
4. Minimize fallback complexities if changes result in an error
What do you mean by collection?
Answer : A user defined name that is the anchor for packages It has not physical existence Main usage is to group packages
In SPUFI suppose you want to select max of 1000 rows , but the select returns only 200 rows What are the 2 Sql codes that are returned?
Answer : 100(For successful completion of the query ), 0(For successful COMMIT if AUTOCOMMIT is set to Yes).
Explain how would you print the output of an SQL statement from SPUFI?
Answer : Print the output Data Set
How can you pull up a query which was previously saved in MF ?
Answer : Looks like index page split has occured DO a REORG of the indexes
What is dynamic SQL?
Answer : Dynamic SQL is a SQL statement created at program execution time
Can you tell me when is the access path determined for dynamic SQL?
Answer : At run time, when the PREPARE statement is issued
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?
Answer : Probably RUN STATS is not done and the program is using a wrong index due to incorrect status Probably RUNSTATS is done and optimizer has chosen a wrong access path based on the latest statistics
Under what circumstances does DB2 store NULL physically?
Answer : As an extra-byte prefix to the column value physically, the null prefix is Hex '00' if the value is present and Hex 'FF' if it is not.
How do you retrieve the data from a null column?
Answer : Use null indicators Syntax INTO :HOSTVAR:NULLIND
What do you mean by picture clause of the null indicator variable?
Answer : S9(4) COMP
What does it mean if the null indicator has -1, 0, -2?
Answer : -1 : the field is null 0 : the field is not null -2 : the field value is truncated
Explain how can you insert a record with a nullable column?
Answer : To insert a NULL, move -1 to the null indicator To insert a valid value, move 0 to the null indicator
What do you mean by RUNSTATS?
Answer : A DB2 utility used to collect statistics about the data values in tables which can be used by the optimizer to decide the access path It also collects statistics used for space management These statistics are stored in DB2 catalog tables
Under what situations, will you chose to run RUNSTATS?
Answer : After a load, or after mass updates, inserts, deletes, or after REORG
Give some instances of statistics collected during RUNSTATS?
Answer : # of rows in the table Percent of rows in clustering sequence # of distinct values of indexed column # of rows moved to a nearby/far way page due to row length increase
What do you mean by REORG?When is it used?
Answer : REORG reorganizes data on physical storage to re cluster rows, positioning flowed rows in their proper sequence, to reclaim space, to restore free space It is used after heavy updates, inserts and delete activity and after segments of a segmented table space have become fragmented.
What do you mean by IMAGECOPY ?
Answer : It is full backup of a DB2 table which can be used in recovery
When do you use the IMAGECOPY?
Answer : To take routine backup of tables After a LOAD with LOG NO.
What do you mean by COPY PENDING status?
Answer : A state in which, an image copy on a table needs to be taken, In this status, the table is available only for queries You cannot update this table To remove the COPY PENDING status, you take an image copy or use REPAIR utility
What do you mean by CHECK PENDING ?
Answer : When a table is Loaded with ENFORCE NO option, then the table is left in CHECK PENDING status It means that the LOAD utility did not perform constraint checking
Define lock escalation?
Answer : Promoting a PAGE lock-size to table or table space lock-size when a transaction has acquired more locks than specified in NUMLKTS Locks should be taken on objects in single table space for escalation to occur
Can you tell me what are the various locks available?
Answer : SHARE, EXCLUSIVE, UPDATE
Can you use LOCK TABLE on a view?
Answer : No To lock a view, take lock on the underlying tables
What do you mean by ALTER ?
Answer : SQL command used to change the definition of DB2 objects.
Related Posts
No comments :