As400 Db2 Interview Questions And Answers

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.

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

33) Highlight all the advantages that are attached to a PACKAGE.

Following are the advantages attached to a PACKAGE.

  • Avoid the cost of a large collection of bind. It is much more advisable to go for a small collection instead of a large one.
  • Ensure that you do not have to bring a large collection of members of DBRM together for a particular plan.
  • In case if you wish to make changes in the program and these changes eventually lead to errors, then you can decrease the fallback complexities with the help of PACKAGE.
  • During the process of automatic binding and rebinding of a particular plan, make sure that the total transaction attached to the process is unavailable during the course.
  • 24) Explain in detail about buffer manager and its functionalities?

    Buffer manager can be considered as the component inside DB2 that helps in transferring data between virtual as well as external medium. The buffer manager reduces the quantity of physical input as well as output operations that are actually performed by making use of buffering techniques that are highly sophisticated.

    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.

    38) Mention some fields that are a part of SQLCA.

    There are primarily two components of DCLGEN. These are: –

  • A copy book of the host language, which will give alternative definitions for all the column names.
  • In terms of the data-types of DB2, EXEC SQL DECLARE TABLE shows an aesthetic layout of the table.
  • 44) Before you give the EXPLAIN statement, what are the prerogatives?

    Before giving the EXPLAIN statement, we need to make sure that PLAN_TABLE has already been created under AUTHID.

    What is Aggregate?

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

    26) Mention a credible reason why SELECT* is never given preference in an SQL program that has been embedded.

    There are primarily three reasons why SELECT* is never given preference in an embedded SQL program. These are: –

  • In case if there is an alteration in the structure of the table, then the program has to undergo a modification process.
  • All columns will be retrieved by the program including those columns , which might not be used.
  • In case if the user wishes to scan the index, then that will not be possible.
  • 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.

    46) Outputs of EXPLAIN are with MATCHCOLS = 0. What does this signify?

    This signifies a non-matching scan of index provided that ACCESSTYPE = I

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

    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.

    DB2 Interview questions and answers

    Related Posts

    Leave a Reply

    Your email address will not be published. Required fields are marked *