User has to use SELECT COUNT (*) on DB2 query.
To eliminate duplicate values in DB2 SELECT, the user has to use SELECT DISTINCT in DB2 query.
‘Aggregate’ functions are built in mathematical tools that are used in DB2 SELECT clause.
Learn DB2 in 12 hrs. Download e-book now
Yes! It is possible using the MAX on a CHAR column.
The reason for the statement generating inaccurate outcome is because of the fact that SALARY has not been declared to have NULL and at the same time the employees whose salary is not known are also counted.
Statement to concatenate the first and last name to display the full name is FIRSTNAME II ** II LASTNAME FROM EMP.
Two tasks are accomplished by the ‘Value’ function. The first one is avoiding –veSQLCODEs through the handling of NULLs as well as Zeroes in the computation. The second one is substituting numeric values for nulls used in computation.
UNION is the function that eliminates duplicates in the table and UNION ALL retains the duplicates. Both of these are used to combine the results generated by multiple SELECT statements.
While using UNION in embedded SQL it has to be in a CURSOR.
Download DB2 Interview questions asked by top MNCs in 2017 ?
BETWEEN would supply a range of values whereas IN would provide list of values. BETWEEN is always inclusive of the range values specified.
LIKE statement is used to conduct partial searches like the search of employees by name. It is not necessary to indicate the complete name, the partial string matches would do.
SELECT is not preferred in embedded SQL programs for three reasons. First; if the table structure is changed by addition or deletion of a field and the program is modified then using SELECT might retrieve the columns that the user may not use. This would lead to Input Output overhead and the chances of index only scan is also eliminated.
A CURSOR is programming device that helps the SELECT to find out set of rows but displays them one at a time. Since the host language can deal with only one row at a time.
Rows form DB2 table embedded SQL can be retrieved using the single row SELECT statement. Another way is using the CURSOR. However, the first process is the preferred one.
In simple OPEN CURSOR statement the CURSOR is placed on the first row of the table. On the other hand, if the ORDER BY clause is added, rows would be fetched, sorted and would be available for the FETCH statement.
Yes! The user can have more than one cursor open for any program.
The VARCHAR column remarks would be defined as follows.10 REMARKS.49 REMARKS-LEN PIC S9(4) USAGE COMP.49 REMARKS-TEXT PIC X(1920).
The physical storage length of DATE is 4 bytes, TIME is 3 bytes, and TIMESTAMP is 10 bytes.
DCLGEN refers to DeClarations GENerator that is used to create the host language copy books for the table definitions. It also creates the DECLARE table.
The contents of DCLGEN are EXEC SQL DECLARE TABLE statement that gives the layout of the table/view in terms of the DB2 data types. It can also be defined as the host language copy book giving host variable definitions for the column names.
Major Fields in SQLCA is SQL CODE, SQLERRM, and SQLERRD.
Use of EXPLAIN is for displaying the access path as determined by the optimizer for SQL statement. It can also be used in case of SPUFI for single SQL statements or in the BIND step for embedded SQL.
User can use SPUFI or QMF statements to EXPLAIN the dynamic SQL statement. They can also include EXPLAIN command in the embedded dynamic SQL statements.
Two isolation levels are possible. One is CURSOR stability and the other one is Repeatable Read denoted as CS and RR respectively.
CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of the transaction.
Lock escalation is the process of promoting PAGE lock sizes to table or table space lock size when the transaction has acquired more locks than the ones specified in NUMLKTS. Locks have to be taken on objects in single table space for escalations to take place.
There are three different types of locks such as SHARE, EXCLUSIVE, and UPDATE.
ALTER is the SQL command to change the definition of DB2 objects.
DBRM denotes Data Base Request Module. It has the SQL statements that are extracted from the host language program obtained by the pre-compiler. PLAN is the result of the BIND process and has executable code for SQL statements in DBRM.
ACQUIRE/RELEASE in BIND determines the point at which the DB2 either acquires or releases locks against the table and the table spaces. This includes the intent locks.
PACKAGES contain executable codes for SQL statements in respect of one DBRM.
When used, PACKAGES help avoiding the binding of large numbers of DBRM members in one plan. It also dispenses with the cost of large BIND and avoids the entire transactions making them unavailable during BIND and automatic REBIND of the plan. Another advantage is that it minimizes the fallback complexities when changes result in an error.
A collection is the user defined name that works as the anchor for packages but has no physical existence. The use is for grouping of packages.
Dynamic SQL is the SQL statement created at the time of execution of a program.