For BE/B.Tech/BCA/MCA/ME/M.Tech Major/Minor Project for CS/IT branch at minimum price Text Message @ 9424820157

DB2 Interview Questions

DB2 Interview Questions



1. How to find the number of rows in a DB2 table?

To find the number of rows in a DB2 table, the user has to use SELECT COUNT (*) on the DB2 query.


2. How can the duplicate values be eliminated from DB2 SELECT?

To eliminate the duplicate values from DB2 SELECT, the user has to use SELECT DISTINCT in the DB2 query.


3. What is Aggregate?

Aggregate functions are built-in mathematical tools that are used in the DB2 SELECT clause.


4. Is it possible using MAX on a CHAR column?

Yes! It is possible using MAX on a CHAR column.


5. Why SELECT is not preferred in Embedded SQL programs?

SELECT is not preferred in Embedded SQL programs for three reasons. First, if the table structure is changed by the 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 are also eliminated. Learn all about SQL Developers through this SQL Developer and SQL DBA Training Master’s Program!


6. When is the LIKE statement used?

The LIKE statement is used to conduct partial searches such as the search of employees by name. It is not necessary to indicate the complete name, partial string matches would do.


7. How is the value function used?

We can perform two tasks using the ‘value’ function:

First, to avoid −ve SQLCODEs by handling NULLs and 0s in the computation

Second, to substitute numeric values for NULLs used in the computation


8. What are UNION and UNION ALL?

Both UNION and UNION ALL are used to combine the results generated by multiple SELECT statements.

UNION is the function that eliminates duplicates in the table

UNION ALL retains the duplicates.


9. Is there any restriction on the use of UNION in Embedded SQL?

While using UNION in Embedded SQL, it has to be in a CURSOR.


10. What do you mean by BETWEEN and IN? Is BETWEEN inclusive of specified range values?

BETWEEN would supply a range of values, whereas IN would provide a list of values. BETWEEN is always inclusive of the range values specified.


11. How can the FIRSTNAME and the LASTNAME from the EMP table be concatenated to generate complete names?

Below is the statement that can be used to concatenate the first and last names to display full names:

FIRSTNAME II ** II LASTNAME FROM EMP


12. What could be the reason that the MySQL statement 'SELECT AVG (SALARY) FROM EMP' generates an inaccurate output?

The reason for the statement generating an inaccurate outcome is that SALARY has not been declared to have NULL values and, at the same time, the employees whose salary is not known are also counted.


13. What is a CURSOR and what is its use?

A CURSOR is a programming device that helps the SELECT statement find out a set of rows but displays them one at a time. This is because the host language can deal with only one row at a time.


14. How to retrieve rows from a DB2 table in Embedded SQL?

Rows from a DB2 table in Embedded SQL can be retrieved using the single-row SELECT statement. Another way is by using the CURSOR statement. However, the first process is the preferred one.


15. What is the result of the OPEN CURSOR statement?

In the simple OPEN CURSOR statement, the CURSOR is placed on the first row of a table. On the other hand, when the ORDER BY clause is added, rows would be fetched, sorted, and would be available for the FETCH statement.

Now that you are aware of the benefits of SQL, enroll in our SQL Course today and excel in your career!


16. Can there be more than one cursor open for any program?

Yes! The user can have more than one cursor open for any program.


17. How would the VARCHAR column REMARKS be defined?

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)

18. What is the physical storage length of each of the data types, DATE, TIME, and TIMESTAMP?

The physical storage length of DATE is 4 bytes, TIME is 3 bytes, and TIMESTAMP is 10 bytes.


19. What is meant by DCLGEN?

DCLGEN refers to Declaration Generator that is used to create the host language copy books for the table definitions. It also creates the DECLARE table.


20. What are the contents of DCLGEN?

DCLGEN has the EXEC SQL DECLARE TABLE statement that gives the layout of a table/view in terms of DB2 data types. It can also be defined as the host language copy book giving the host variable definitions for column names.


21. What are some of the main fields in SQLCA?

Major fields in SQLCA are SQLCODE, SQLERRM, and SQLERRD.


22. What is meant by EXPLAIN?

EXPLAIN is used for displaying the access path as determined by the optimizer for SQL statements. It can also be used in the case of SPUFI for single SQL statements or in the BIND step for Embedded SQL.


23. How to perform EXPLAIN for any Dynamic SQL statement?

Users can use SPUFI or QMF statements to perform EXPLAIN for Dynamic SQL statements. They can also include the EXPLAIN command in the Embedded Dynamic SQL statements.


24. What are the isolation levels possible?

Two isolation levels are possible: One is Cursor Stability and the other is Repeatable Read denoted as CS and RR, respectively.


25. What is the difference between CS and RR isolation levels?

CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of a transaction.


26. What is meant by Lock Escalation?

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 a single table space for escalations to take place.


27. What are the various types of Locks?

There are three different types of locks: SHARE, EXCLUSIVE, and UPDATE.


28. What is ALTER?

ALTER is the SQL command used to change the definition of DB2 objects.


29. What do you understand by DBRM and PLAN?

DBRM denotes Database 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.


30. What is meant by ACQUIRE/RELEASE in BIND?

ACQUIRE/RELEASE in BIND determines the point at which DB2 either acquires or releases locks against the table and the table spaces. This includes the intent locks.


31. What is meant by PACKAGES?

PACKAGES contain executable codes for SQL statements in respect of one DBRM.


32. What are the advantages of using PACKAGES?

When used, PACKAGES help avoid binding of large numbers of DBRM members in one plan. They also dispense with the cost of large BIND and avoid the entire transactions making them unavailable during BIND and automatic REBIND of the plan. Another advantage is that they minimize the fallback complexities when changes result in an error.


33. What is a collection?

A collection is a user-defined name that works as the anchor for packages but has no physical existence. It is used for the grouping of packages.


34. What is Dynamic SQL?

Dynamic SQL is the SQL statement created at the time of the execution of a program.


35. Explain about cursor stability?

Cursor stability is the property that tells the DB2 that the values of database that are read by making use of this application gets protected while the data is used.


36. Explain correlated sub-queries.

Correlated sub-queries are those queries wherein the nester query on the inner side refers directly back to outer query’s table. For each and every row that is qualified, the evaluation of correlated sub-query is a must.


37. Comment whether the cursor is closed during COMMIT or not.

Yes. The cursor is closed during COMMIT.


38. In an SQL table that is embedded, what is the procedure to retrieve rows that are part of a DB2 table?

Either you can use SELECT statements of single rows or an alternative way is to use CURSOR.


39. Mention the way of highlighting as well as putting a CURSOR to use in a COBOL program.

The best way of putting a CURSOR to use in a COBOL program is to make use of DECLARE CURSOR, which can be used either in procedure division operation or in working storage. This is being done basically to highlight the SELECT statement. Once DECLARE CURSOR is used, this is followed by OPEN, FETCH and finally CLOSE.


40. If the CURSOR is kept open followed the issuing of COMMIT, what is the procedure to leave the CURSOR that way?

Inside DECLARE CURSOR, there is a WITH HOLD option, which will come useful in this case. Although, one should note the point that WITH HOLD function has absolutely no effect if considered for CICS pseudo-conventional programs.


41. Explain PACKAGES.

PACKAGES are units, which consist of executable codes that are meant for SQL statements for one respective DBRM.

No comments:

Post a Comment



Please go through below tutorials:


Mule 4 Tutorials

DEPLOY TO CLOUDHUB C4E CLIENT ID ENFORCEMENT CUSTOM POLICY RABBIT MQ INTEGRATION
XML TO JSON WEBSERVICE CONSUMER VM CONNECTOR VALIDATION UNTIL SUCCESSFUL
SUB FLOW SET & REMOVE VARIABLE TRANSACTION ID SCATTER GATHER ROUND ROBIN
CONSUME REST WEBSERVICE CRUD OPERATIONS PARSE TEMPLATE OBJECT TO JSON LOAD STATIC RESOURCE
JSON TO XML INVOKE IDEMPOTENT FILTER FOR EACH FLAT TO JSON
FIXWIDTH TO JSON FIRST SUCCESSFUL FILE OPERATIONS EXECUTE ERROR HANDLING
EMAIL FUNCTIONALITY DYNAMIC EVALUATE CUSTOM BUSINESS EVENT CSV TO JSON COPYBOOK TO JSON
CHOICE ASYNC

Widely used Connectors in Mule 3

CMIS JETTY VM CONNECTOR SALESFORCE POP3
JMS TCP/IP WEBSERVICE CONSUMER QUARTZ MONGO DB
FILE CONNECTOR DATABASE CONNECTOR


Widely used Scopes in Mule 3

SUB FLOW REQUEST REPLY PROCESSOR CHAIN FOR EACH CACHE
ASYNC TCP/IP COMPOSITE SOURCE POLL UNTIL SUCCESSFUL
TRANSACTIONAL FLOW

Widely used Components in Mule 3

EXPRESSION CXF SCRIPT RUBY PYTHON
JAVASCRIPT JAVA INVOKE CUSTOM BUSINESS EVENT GROOVY
ECHO LOGGER


Widely used Transformers in Mule 3

MONGO DB XSLT TRANSFORMER REFERENCE SCRIPT RUBY
PYTHON MESSAGE PROPERTIES JAVA TRANSFORMER GZIP COMPRESS/UNCOMPRESS GROOVY
EXPRESSION DOM TO XML STRING VALIDATION COMBINE COLLECTIONS BYTE ARRAY TO STRING
ATTACHMENT TRANSFORMER FILE TO STRING XML TO DOM APPEND STRING JAVASCRIPT
JSON TO JAVA COPYBOOK TO JSON MAP TO JSON JSON TO XML FLATFILE TO JSON
FIXWIDTH TO JSON CSV TO JSON


Widely used Filters in Mule 3

WILDCARD SCHEMA VALIDATION REGEX PAYLOAD OR
NOT MESSAGE PROPERTY MESSAGE IDEMPOTENT FILTER REFERNCE
EXPRESSION EXCEPTION CUSTOM AND


Exception Strategy in Mule 3

REFERENCE EXCEPTION STRATEGY CUSTOM EXCEPTION STRATEGY CHOICE EXCEPTION STRATEGY CATCH EXCEPTION STRATEGY GLOBAL EXCEPTION STRATEGY


Flow Control in Mule 3

CHOICE COLLECTION AGGREGATOR COLLECTION SPLITTER CUSTOM AGGREGATOR FIRST SUCCESSFUL
MESSAGE CHUNK AGGREGATOR MESSAGE CHUNK SPLITTER RESEQUENCER ROUND ROBIN SOAP ROUTER