Why SELECT * is not preferred in embedded SQL programs?

DB2 INTERVIEW QUESTIONS

(Continued from previous question...)

Why SELECT * is not preferred in embedded SQL programs?

For three reasons:
If the table structure is changed ( a field is added ), the program will have to be modified
Program might retrieve the columns which it might not use, leading on I/O over head.
The chance of an index only scan is lost.

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