> > DB2 Programming MCQs > Basic DB2 Programming MCQs

Basic DB2 Programming MCQ

1. Is DECLARE TABLE in DCLGEN necessary?

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

2. What is the purpose of the WHENEVER statement?

It is used to control program actions depending on the SQL-CODE

It is used to control program Flow

It is used to Control errors in the program

None of the above

Answer

Correct Answer: It is used to control program actions depending on the SQL-CODE

Note: This Question is unanswered, help us to find answer for this one

3. Consider the employee table with column MGR nullable. How can you get a list of employees who are not assigned to any mgr?

SELECT EMPNO FROM EMP

WHERE MGR IS NULL;

SELECT EMPNO FROM EMP

WHERE MGR=NULL;

SELECT EMPNO FROM EMP

WHERE MGR=0;

None of the above

Answer

Correct Answer:

SELECT EMPNO FROM EMP

WHERE MGR IS NULL;

Note: This Question is unanswered, help us to find answer for this one

4. What are the advantages of using a PACKAGE?

Cost of a large bind can be avoided

The entire transaction can be avoided if it is unavailable during bind and automatic rebind of a plan

Fallback complexities can be minimzed if changes result in an error

None of the above

Answer

Correct Answer: Cost of a large bind can be avoided

Note: This Question is unanswered, help us to find answer for this one

5. What does DBRM Stand for?

DataBase Resource Manager

DataBase Request Module

DataBase Request Management

Data Base Release Module

Answer

Correct Answer: DataBase Resource Manager

Note: This Question is unanswered, help us to find answer for this one

6. What is returned by the VALUE function?

It returns the first argument that is null.

It returns the first argument that is not null.

It returns the last argument that is null.

It returns the last argument that is not null.

Answer

Correct Answer: It returns the first argument that is not null.

Note: This Question is unanswered, help us to find answer for this one

7. When would you choose to run RUNSTATS?

After a load

After mass updates, inserts, deletes

After REORG

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

8. What is a subselect?

A subQuery

A subselect where the inner select passes to the where criteria for the outer select

A select which works in conjunction with another select

None of the Above

Answer

Correct Answer: A select which works in conjunction with another select

Note: This Question is unanswered, help us to find answer for this one

9. What are the disadvantages of PAGE level lock?

High resource utilization if large updates are to be done

Low resource utilization if large updates are to be done

Low resource utilization for any transaction

None of above

Answer

Correct Answer: High resource utilization if large updates are to be done

Note: This Question is unanswered, help us to find answer for this one

10. When you COMMIT, is the cursor closed?

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

11. What are the various locks available?

EXCLUSIVE

UPDATE

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

12. What should be done before you do EXPLAIN?

EXPLAIN should be installed

Optimizer Should be activated

PLAN_TABLE should be created under the AUTHID

None of the above

Answer

Correct Answer: PLAN_TABLE should be created under the AUTHID

Note: This Question is unanswered, help us to find answer for this one

13. If the null indicator has -1, what does it mean?

The field value is truncated

The field is not null

the field is null

None of above

Answer

Correct Answer: the field is null

Note: This Question is unanswered, help us to find answer for this one

14. Which technique is used to retrieve data from more than one table in a single SQL statement?

Join

Union all

Minus

None of the above

Answer

Correct Answer: Join

Note: This Question is unanswered, help us to find answer for this one

15. How do you concatenate the FIRSTNAME and LASTNAME from EMPLOYEE table to give a complete name?

SELECT concat(FIRSTNAME LASTNAME) FROM EMPLOYEE;

SELECT FIRSTNAME || ' ' || LASTNAME FROM EMPLOYEE;

SELECT "FIRSTNAME" ||"LASTNAME" FROM EMPLOYEE;

SELECT 'FIRSTNAME ||LASTNAME' FROM EMPLOYEE;<br>

Answer

Correct Answer: SELECT FIRSTNAME || ' ' || LASTNAME FROM EMPLOYEE;

Note: This Question is unanswered, help us to find answer for this one

16. How would you retrieve the first 5 characters of FIRSTNAME column of DB2 table EMP?

SELECT SUBSTR(1,5,FIRSTNAME) FROM EMP;

SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

SELECT SUBSTR(1,FIRSTNAME,5) FROM EMP;

SELECT INSTR(FIRSTNAME,1,5) FROM EMP;

Answer

Correct Answer: SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

Note: This Question is unanswered, help us to find answer for this one

17. When do you specify the isolation level?

During the RUN process

During the BIND process

During the LOCK Process

None of the above

Answer

Correct Answer: During the BIND process

Note: This Question is unanswered, help us to find answer for this one

18. How do you leave the cursor open after issuing a COMMIT?

By using WITH HOLD option

By using Declare cursor option

It is not possible

None of above

Answer

Correct Answer: By using WITH HOLD option

Note: This Question is unanswered, help us to find answer for this one

19. Can you have more than one cursor open at any one time in a program?

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

20. Suppose there is a cobol-db2 program. If you have made changes only in the cobol program, is there a need to recomile the DBRM?

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

21. What is the meaning of underscore ( '_' ) in the LIKE statement?

It is a match for specified character

It is a match for all character

It is a match for any single character

None of the above

Answer

Correct Answer: It is a match for any single character

Note: This Question is unanswered, help us to find answer for this one

22. What are correlated subqueries?

It is a subquery in which the inner ( nested ) query refers back to the table in the outer query

It is a nested SubQuery

It is a subquery

None of the above

Answer

Correct Answer: It is a subquery in which the inner ( nested ) query refers back to the table in the outer query

Note: This Question is unanswered, help us to find answer for this one

23. What is the use of FREE command?

It is used to free space

It is used to delete Rows

It is used to delete Plan

None of the above

Answer

Correct Answer: It is used to delete Plan

Note: This Question is unanswered, help us to find answer for this one

24. How would you find out the total number of rows in a DB2 table?

Using SELECT COUNT(*)

Using SELECT ALL

Using SELECT *

You cannot find the total number of rows

Answer

Correct Answer: Using SELECT COUNT(*)

Note: This Question is unanswered, help us to find answer for this one

25. State whether true or false.
When you open a cursor, DB2 will always get all rows that meet the selection criteria and create a result set.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

26. Why is SELECT * not preferred in embedded SQL programs?

If the table structure is changed, the program will have to be modified

Program might retrieve the columns which it might not use

The chance of an index only scan is lost

None of the above

Answer

Correct Answer: If the table structure is changed, the program will have to be modified

Note: This Question is unanswered, help us to find answer for this one

27. What is the restriction on using UNION in embedded SQL?

It has to be in loop

It has to be in a CURSOR

Union will not work with embedded SQL

None of the above

Answer

Correct Answer: It has to be in a CURSOR

Note: This Question is unanswered, help us to find answer for this one

28. What is the difference between QMF & SPUFI?

Both are the same

You can know sqlcode after excuting the code in QMF. In SPUFI, it is not possible

SPUFI executes mutliple queries at one time while QMF executes only one query at a time.

SPUFI is mainly used to generate nicely crafted Reports with Headers, Footers for business users, whereas QMF can't used to generate reports.

Answer

Correct Answer: You can know sqlcode after excuting the code in QMF. In SPUFI, it is not possible

Note: This Question is unanswered, help us to find answer for this one

29. What is a thread?

It is a module

It is a set of code

It is a connection between DB2 and some other subsystem

None of above

Answer

Correct Answer: It is a connection between DB2 and some other subsystem

Note: This Question is unanswered, help us to find answer for this one

30. When will you use curser with hold option?

When you are using cursor

When you are using cursor Loop

When you are using commit with in the cursor

Cursor with hold option cannot be used

Answer

Correct Answer: When you are using commit with in the cursor

Note: This Question is unanswered, help us to find answer for this one

31. What is the statistics collected during RUNSTATS?

Number of distinct values of indexed column

Number of rows moved to a nearby/farway page due to row length increase

Number of rows on which statistics have been performed

None of the above

Answer

Correct Answer: Number of distinct values of indexed column

Note: This Question is unanswered, help us to find answer for this one

32. Under which circumstance would you create an index on a table?

When the table is small

When the table is updated frequently

When Columns values are static and contain a narrow range of values

When two columns are consistently used in the WHERE clause join condition of SELECT statements

Answer

Correct Answer: When two columns are consistently used in the WHERE clause join condition of SELECT statements

Note: This Question is unanswered, help us to find answer for this one

33. How can you quickly find out the number of rows updated after an update statement?

Check the value stored in SQLERRD(3)

Check the value stored in SQLERRM(3)

Check the value stored in SQLCODE

Check the value stored in SQLERRC

Answer

Correct Answer: Check the value stored in SQLERRD(3)

Note: This Question is unanswered, help us to find answer for this one

34. What are the disadvantages of using VARCHAR?

It can lead to high space utilization if most of the values are close to maximum

Positioning of VARCHAR column has to be done carefully as it has performance implications

Relocation of rows to different pages can not lead to more I/Os on retrieval

All of the above

Answer

Correct Answer: It can lead to high space utilization if most of the values are close to maximum

Note: This Question is unanswered, help us to find answer for this one

35. How would you display the table definitions (columns, data types of columns, constraints or default values set etc) of a table in QMF?

By Desc comand

SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME=table-name AND TBCREATOR=' creator-name';

SELECT * FROM SYSIBM.SYSCOLS WHERE TBNAME=table-name AND TBCREATOR='table-name';

None of above

Answer

Correct Answer: SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME=table-name AND TBCREATOR=' creator-name';

Note: This Question is unanswered, help us to find answer for this one

36. Can GROUP BY and ORDER BY used in a single query?

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

37. What happens when you say OPEN CURSOR?

Rows are fetched

The cursor is placed on the first row

Rows are sorted and then fetched

None of above

Answer

Correct Answer: The cursor is placed on the first row

Note: This Question is unanswered, help us to find answer for this one

38. What are the various locking levels available?

PAGE Level

TABLE Level

TABLESPACE Level

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

39. How does DB2 determine which lock-size to use?

DB2 determines it based on the given lock-size while creating the tablespace

The programmer can direct the DB2 which lock-size to use

If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE

None of the above

Answer

Correct Answer: DB2 determines it based on the given lock-size while creating the tablespace

Note: This Question is unanswered, help us to find answer for this one

40. Can you use MAX on a CHAR column?

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

41. When can you not drop a database

If there is a referential constraint pointing to a table of this database from a table of another database.

When a DB2 utility has control of any part of the database.

If there is still a user connected to the database.

Before all indexes connected to tables of this database are dropped.

Answer

Correct Answer: When a DB2 utility has control of any part of the database.

Note: This Question is unanswered, help us to find answer for this one

42. In what form is data physically stored in DB2

VSAM ESDS

Flat Files

Tables

VSAM LDS

Answer

Correct Answer: VSAM LDS

Note: This Question is unanswered, help us to find answer for this one

43. Consider the following embedded SQL statement: SELECT CLASSNAME INTO :CLASSNAME:IND FROM CLASS WHERE STUDNAME = :STUDNAME In which order should the following program variables be evaluated by the part of the program just after this SQL statement?

IND, STUDNAME, CLASSNAME

SQLCODE, STUDNAME, CLASSNAME

SQLCODE, IND, CLASSNAME

SQLCODE, CLASSNAME, IND

Answer

Correct Answer: SQLCODE, IND, CLASSNAME

Note: This Question is unanswered, help us to find answer for this one

44. Which of the following is not a type of dynamic SQL?

Interactive SQL.

Enhanced PL/SQL

Deferred embedded SQL.

Embedded dynamic SQL.

Answer

Correct Answer: Enhanced PL/SQL

Note: This Question is unanswered, help us to find answer for this one

45. When using JBDC, do you have to recode your Java application if you want to change between drivers?

No, however the new drivers need wrappers.

Yes, however there is a tool that must be used to change to a new driver.

No, however a special code must be written to use a new driver.

No.

Answer

Correct Answer: No.

Note: This Question is unanswered, help us to find answer for this one

46. Which of the following represents a function that is performed for each row in a DB2 table

Scalar function

Group by

Aggregate Function

Group by Having

Answer

Correct Answer: Scalar function

Note: This Question is unanswered, help us to find answer for this one

47. Which is an example of a column that would contain statistics necessary for programming?

CRDX

CARDY

CRDF

CARDF

Answer

Correct Answer: CARDF

Note: This Question is unanswered, help us to find answer for this one

48. Why must the DB2 precompiler be used to execute SQL statements?

The compiler does not recognize SQL statements.

The DB2 precompiler wraps the SQL statements in Java for easier use.

It isn't necessary to use the DB2 precompiler.

The DB2 precompiler executes a HIVE database for SQL operation.

Answer

Correct Answer: The compiler does not recognize SQL statements.

Note: This Question is unanswered, help us to find answer for this one

49. What four languages can host variable arrays be specified in?

C, C++, COBOL, or PL/I.

Javascript, C++, COBOL, or PL/I.

C, C++, Fortran, or PL/I.

C, C++, Perl, or PL/I.

Answer

Correct Answer: C, C++, COBOL, or PL/I.

Note: This Question is unanswered, help us to find answer for this one

50. Can the SQL procedural language be used for advanced functions?

Yes, but they must be precompiled first.

No.

Yes, but with some supplementary coding.

Yes, but with a supplementary set of Java wrappers.

Answer

Correct Answer: No.

Note: This Question is unanswered, help us to find answer for this one

51. What is the Java data access platform used in DB2?

jQuery.

pureQuery.

dQuery.

aQuery.

Answer

Correct Answer: pureQuery.

Note: This Question is unanswered, help us to find answer for this one

52. What happens when the execution of a utility is terminated by the TERM command?

The execution ends normally, the corresponding row in the SYSUTIL table is not removed, all resources are freed.

The execution ends normally, the corresponding row in the SYSUTIL table is removed, all resources are freed.

The execution ends abnormally, the corresponding row in the SYSUTIL table is not removed, all resources are freed.

The execution ends abnormally, the corresponding row in the SYSUTIL table is removed, all resources are freed.

Answer

Correct Answer: The execution ends normally, the corresponding row in the SYSUTIL table is removed, all resources are freed.

Note: This Question is unanswered, help us to find answer for this one

53. Which of the following is not a DB2 object

Table

Synonym

Storage Group

Column

Answer

Correct Answer: Column

Note: This Question is unanswered, help us to find answer for this one

54. In what catalog table must stored procedures be defined?

CONFPROC

STOPROCEDURES

SYSPROCEDURES

CONFPROCEDURES

Answer

Correct Answer: SYSPROCEDURES

Note: This Question is unanswered, help us to find answer for this one

55. Which of the following is a correct syntactical example of written SQL code?

EXEC: SELECT INTO :book_type FROM BOOKTYPES WHERE

TYPE INTO //:book_type FROM BOOK_TYPES WHERE//

SELECT TYPE INTO :book_type FROM BOOK_TYPES WHERE

EXEC SQL SELECT TYPE INTO :book_type FROM BOOK_TYPES WHERE

Answer

Correct Answer: EXEC SQL SELECT TYPE INTO :book_type FROM BOOK_TYPES WHERE

Note: This Question is unanswered, help us to find answer for this one

56. What is an example of an underlying cause for regressions caused by changes in DB2?

Regression analysis.

Redundant statistics.

Accurate statistics.

Inadequate statistics.

Answer

Correct Answer: Inadequate statistics.

Note: This Question is unanswered, help us to find answer for this one

57. When should we execute a REBIND rather than a BIND

When the embedded SQL of the application is changed.

C & E

After the execution of a RUNSTATS.

When an index is added to one of the tables used by the embedded SQL.

Answer

Correct Answer: C & E

Note: This Question is unanswered, help us to find answer for this one

58. SQLSTATE is a standard set of error messages and warnings in which the first two characters defines the class and the last three defines the subclass of the error. Which of the following SQLSTATE codes is interpreted as "No data returned"?

00xxx

01xxx

22xxx

02xxx

Answer

Correct Answer: 02xxx

Note: This Question is unanswered, help us to find answer for this one

59. Given the following DDL statements: CREATE TABLE Smart1 (a INT, b INT, c INT) CREATE VIEW View1 AS SELECT a,b,c FROM Smart1 WHERE a > 250 WITH CHECK OPTION Which of the following INSERT is correct

INSERT INTO Smart1 VALUES (200, 2, 3)

INSERT INTO Smart1 VALUES (350, 2, 3)

INSERT INTO View1 VALUES (250, 2, 3)

INSERT INTO View1 VALUES (300, 2, 3)

Answer

Correct Answer: INSERT INTO View1 VALUES (300, 2, 3)

Note: This Question is unanswered, help us to find answer for this one

60. Which of the following is correct SQLJ syntax?

#//sql [myConnCtXt] { update EMP

///#sql [myConnCtxt]}}{{||\\ { UPDATE EMP///

#sql [myConnCtxt] { UPDATE EMP

#sql [myConnCtxt] :: UPDATE//EMP

Answer

Correct Answer: #sql [myConnCtxt] { UPDATE EMP

Note: This Question is unanswered, help us to find answer for this one

61. What happens to SQL statements if there are no stored procedures?

They are embedded.

They are disregarded.

They are corrupted.

They are locked.

Answer

Correct Answer: They are embedded.

Note: This Question is unanswered, help us to find answer for this one

62. What code is used to start the CICS attachment facility?

CICSgo

CICSstart

EXEC CICS LINK PROGRAM('DSNyCOM ')

CICS

Answer

Correct Answer: EXEC CICS LINK PROGRAM('DSNyCOM ')

Note: This Question is unanswered, help us to find answer for this one

63. What must happen before using the EXPLAIN statement?

A stored procedure must be made to process it.

A Java wrapper must be written to use it.

A planned procedure must be made to execute it.

A plan table must be created to hold the results of EXPLAIN.

Answer

Correct Answer: A plan table must be created to hold the results of EXPLAIN.

Note: This Question is unanswered, help us to find answer for this one

64. Which of the following is a DB2 mechanism that ensures data integrity between tables related by Primary & Foreign Keys

B & C

Referential integrity

Data Integrity

Pre-compilation

Answer

Correct Answer: Referential integrity

Note: This Question is unanswered, help us to find answer for this one

65. When are dynamic SQL applications prepared?

When the program is sandboxed.

When the program is not running.

When the system is turned off.

While the program is running.

Answer

Correct Answer: While the program is running.

Note: This Question is unanswered, help us to find answer for this one

66. What would the “SQL CONNECT” statement be used for when coding stored procedures?

It connects DB2 with Active Directory.

It connects DB2 with the database.

It allows an application to communicate with DB2.

It connects DB2 with Java.

Answer

Correct Answer: It allows an application to communicate with DB2.

Note: This Question is unanswered, help us to find answer for this one

67. Given the following DDL statement: CREATE TABLE newsmart1 LIKE smart1 Which of the following would occur as a result of the statement execution?

newsmart1 has same triggers as smart1

newsmart1 is populated with smart1 data

newsmart1 columns have same attributes as smart1

newsmart1 has the same primary key as smart1

Answer

Correct Answer: newsmart1 columns have same attributes as smart1

Note: This Question is unanswered, help us to find answer for this one

68. Which of the following is an example of a programming method not used in DB2 programming?

Hadoop

OBDC

Dynamic SQL.

Static SQL

Answer

Correct Answer: Hadoop

Note: This Question is unanswered, help us to find answer for this one

69. Which of the following occurs if a DB2 procedure or application ends abnormally during an active unit of work?

The unit of wok moves to pending state

The unit of work moves to CHECK_PENDING

The unit of work remains active

The unit of work is rolled back

Answer

Correct Answer: The unit of work is rolled back

Note: This Question is unanswered, help us to find answer for this one

70. What type of SQL operator would you use to combine the results of 2 Select statements while retaining the duplicates

Inner Join

Intersect

Union All

Union

Answer

Correct Answer: Union All

Note: This Question is unanswered, help us to find answer for this one

71. A declared temporary table is used for which of the following purposes?

To store intermediate results

To share result sets between applications

To create a backup copy of a database or table space

To provide an area for database manager sorts

Answer

Correct Answer: To store intermediate results

Note: This Question is unanswered, help us to find answer for this one

72. A role is a database entity that groups together one or more privileges. Which of the following is true for a "role"

All of the above

A role cannot be a primary authorization ID.

A role cannot be set by using a SET CURRENT SQLID statement.

A role can be the schema qualifier of an object. However, when it is used as a schema qualifier, a role is considered to be a character string and does not add any implicit schema privileges (ALTERIN, CREATEIN, or DROPIN) to this role.

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

73. Which of the following statements eliminates all but one of each set of repeated rows inthe final result table?

SELECT DISTINCT * FROM t1

SELECT UNIQUE * FROM t1

SELECT * FROM DISTINCT T1

SELECT UNIQUE (*) FROM t1

Answer

Correct Answer: SELECT DISTINCT * FROM t1

Note: This Question is unanswered, help us to find answer for this one

74. What is a stored procedure?

An SQL statement to execute a program.

A program to execute SQL statements.

A method for using wrappers to encode SQL.

An SQL statement.

Answer

Correct Answer: A program to execute SQL statements.

Note: This Question is unanswered, help us to find answer for this one

75. What is a host structure?

A group of host variables that an SQL statement can refer to by using a single name.

A single host variable that an SQL statement can refer to by using a single name.

A variable group of hosts that an SQL statement can refer to by using any name.

A group of host variables that an SQL statement can process.

Answer

Correct Answer: A group of host variables that an SQL statement can refer to by using a single name.

Note: This Question is unanswered, help us to find answer for this one

76. What is SQLCA used for?

It parses stored procedures.

It checks the code of SQL statements.

It checks the execution of SQL statements.

It checks the locking of SQL statements.

Answer

Correct Answer: It checks the execution of SQL statements.

Note: This Question is unanswered, help us to find answer for this one

77. What are three examples of languages you can use to program in DB2?

HTML, MSDOS, Python.

PHP, Ruby on Rails, and Haskell.

Perl, Python, and PL/SQL.

COBOL, Fortran, and Perl.

Answer

Correct Answer: COBOL, Fortran, and Perl.

Note: This Question is unanswered, help us to find answer for this one

78. Which of the following is the correct syntax for an input variable of a PARMLIST string?

DCL SINTAR BIN FAXED(15);

(15)DCL SINTVAR BIN FIXED;

DCL SINvTAR BIN FIXED(15);

DCL SINTVAR BIN FIXED(15);

Answer

Correct Answer: DCL SINTVAR BIN FIXED(15);

Note: This Question is unanswered, help us to find answer for this one

79. What technique can DB2 use to more effectively interpret data from EXPLAIN tables?

Networked processing.

Parallel processing.

Unilateral processing.

Quadratic processing.

Answer

Correct Answer: Parallel processing.

Note: This Question is unanswered, help us to find answer for this one

80. What are the three primary development environments for DB2?

WebSphere Studio, Adobe Acrobat Studio, and IBM Optim Development Studio.

PhotoData Studio, Microsoft Visual Studio, and IBM Optim Development Studio.

Silverlight Studio, Microsoft Visual Studio, and IBM Optim Development Studio.

WebSphere Studio, Microsoft Visual Studio, and IBM Optim Development Studio.

Answer

Correct Answer: WebSphere Studio, Microsoft Visual Studio, and IBM Optim Development Studio.

Note: This Question is unanswered, help us to find answer for this one

81. Which of the following is not one of the types of authorizations associated with a DB2 user?

Current SQLID

Primary Authorization ID

SQL Authorization ID

Secondary Authorization ID

Answer

Correct Answer: SQL Authorization ID

Note: This Question is unanswered, help us to find answer for this one

82. How does one prepare a Java program that contains JDBC methods?

Use the “javago” command.

Use the “javac” command.

Use the “DB2java” command.

Use the “JBDC” command.

Answer

Correct Answer: Use the “javac” command.

Note: This Question is unanswered, help us to find answer for this one

83. What must a SELECT statement be coded within?

A STATIC CURSOR

A DECLARE CURSOR.

A SELECT CURSOR

A DEFINE CURSOR

Answer

Correct Answer: A DECLARE CURSOR.

Note: This Question is unanswered, help us to find answer for this one

84. What's the output of a DB2 Bind

DB2 Application plan

DB2 Catalog

Database request module

Load Module

Answer

Correct Answer: DB2 Application plan

Note: This Question is unanswered, help us to find answer for this one

85. What are two examples of steps that must be taken before coding an application with embedded static or dynamic SQL statements?

Transcribe, and bind.

Compile, and bind.

Pretranscribe, and bind.

Precompile, and bind.

Answer

Correct Answer: Precompile, and bind.

Note: This Question is unanswered, help us to find answer for this one

86. Before executing SQL statements, you want to know whether the CICS attachment facility is available. How do you check?

You check in the CICS configuration file.

Use the EXTRACT EXIT command in your application, or INQUIRE EXITPROGRAM in version 4.0.

You shut down DB2 and check in the CICS kernal.

You check in the CICS configuration menu.

Answer

Correct Answer: Use the EXTRACT EXIT command in your application, or INQUIRE EXITPROGRAM in version 4.0.

Note: This Question is unanswered, help us to find answer for this one

87. Why would you receive a command response of NORMAL when the attachment facility is not available?

The database was disabled.

The exit was disabled.

The exit was not ENABLE STARTED.

The exit was not enabled.

Answer

Correct Answer: The exit was not ENABLE STARTED.

Note: This Question is unanswered, help us to find answer for this one

88. Which statement about an index is NOT true?

An index has its own INDEX SPACE, automatically created

A clustering index influences the physical sequence of data in the tablespace.

An index always has a balanced tree structure.

The name of an index can be mentioned in a Select statement, to improve the performance of the query.

Answer

Correct Answer: The name of an index can be mentioned in a Select statement, to improve the performance of the query.

Note: This Question is unanswered, help us to find answer for this one

89. What is a lightweight web application created from multiple sources?

A crackup.

A mashup.

A mixup.

A smashup.

Answer

Correct Answer: A mashup.

Note: This Question is unanswered, help us to find answer for this one

90. Which of the following can be accomplished with a single UPDATE statement?

Updating multiple tables

Updating a view consisting of joined tables

Updating multiple tables based on a WHERE clause

Updating a table based on a sub-select using joined tables

Answer

Correct Answer: Updating a table based on a sub-select using joined tables

Note: This Question is unanswered, help us to find answer for this one

91. When coding in a language that requires a host variable declaration, what must precede it?

;varchar

;hostvar

;iostream.h

;hostchar

Answer

Correct Answer: ;hostvar

Note: This Question is unanswered, help us to find answer for this one

92. What is the name of the effect when a system continues to receive work, but is down?

Auger effect.

Faraday effect.

Stormdrain effect.

Hutchins effect.

Answer

Correct Answer: Stormdrain effect.

Note: This Question is unanswered, help us to find answer for this one

93. Consider the following: DECLARE MYCURS CURSOR FOR SELECT * FROM MYTABLE WHERE COL1 > :NUM Which of the following embedded SQL statements will NOT generate an error?

SELECT MYCURS INTO :HOSTMYTABLE:IND

FETCH MYCURS INTO :HOSTMYTABLE:IND

SELECT * INTO :HOSTMYTABLE:IND FROM MYCURS

FETCH * INTO :HOSTMYTABLE:IND FROM MYTABLE

Answer

Correct Answer: FETCH MYCURS INTO :HOSTMYTABLE:IND

Note: This Question is unanswered, help us to find answer for this one

94. What is a benefit of concurrency in SQL application programming?

Increase of data conflicts.

Minimization of data access conflicts.

Increasing data access points.

Multi-threaded compiling.

Answer

Correct Answer: Minimization of data access conflicts.

Note: This Question is unanswered, help us to find answer for this one

95. What is an example of a benefit of using the Java programming language?

Once you write the program, it can be used with any other program or software.

There is no benefit to programming with Java.

Once you write the program, it acts like a stored procedure.

Once you develop an application, it can be run anywhere.

Answer

Correct Answer: Once you develop an application, it can be run anywhere.

Note: This Question is unanswered, help us to find answer for this one

96. What must be done to a DB2 application before it can run, and why?

It has to be binded first so it can recognize SQL statements.

It has to be compressed first so it can recognize SQL statements.

It has to be encrypted first so it can recognize SQL statements.

It has to be compiled first so it can recognize SQL statements.

Answer

Correct Answer: It has to be binded first so it can recognize SQL statements.

Note: This Question is unanswered, help us to find answer for this one

97. How are ODBC calls binded?

They are not binded because they use standard functions to execute SQL.

They are binded at compile time.

They are pre-process binded.

They are binded using standard functions in SQL.

Answer

Correct Answer: They are not binded because they use standard functions to execute SQL.

Note: This Question is unanswered, help us to find answer for this one

98. SQL statements embedded into an application is called what?

Dynamic SQL.

PL/SQL

T-SQL.

Static SQL.

Answer

Correct Answer: Static SQL.

Note: This Question is unanswered, help us to find answer for this one

99. Can static SQL statements be changed without altering the program itself?

Yes, but the program has to be altered.

Yes. DB2 is a fully dynamic program, accepting all forms of SQL.

No.

Yes, but the statements have to be changed as well.

Answer

Correct Answer: No.

Note: This Question is unanswered, help us to find answer for this one

100. When is it necessary to precompile DB2 REXX procedures before running them?

Only sometimes, because they vary from static to dynamic SQL.

Never because they use dynamic SQL.

Every time because they use static SQL.

Only when you're using a Java wrapper.

Answer

Correct Answer: Never because they use dynamic SQL.

Note: This Question is unanswered, help us to find answer for this one

101. Given the statement: CREATE TABLE t1 (c1 INTEGER NOT NULL,c2 INTEGER,PRIMARY KEY(c1),FOREIGN KEY(c2) REFERENCES t2) How many non-unique indexes are defined for table t1?

Answer

Correct Answer: 0

Note: This Question is unanswered, help us to find answer for this one

102. When a user has a SELECT authorization on a certain base table, and he creates a view on that table alone, then which of the following is true?

He/She only has a SELECT authorization on that view.

He/She will face an authorization error when trying to create the view

He/She can only execute an UPDATE as long as the definition of the view is not exceeded.

He/She also has DELETE authorization on that view because he/she is the creator of the view.

Answer

Correct Answer: He/She only has a SELECT authorization on that view.

Note: This Question is unanswered, help us to find answer for this one

103. When declaring a foreign key on a table, referencing an existing primary key with complete definition, what might differ between foreign key and primary key?

The data type of the included columns.

The order of the included columns.

The nullability of one of the included columns.

B & E

Answer

Correct Answer: The nullability of one of the included columns.

Note: This Question is unanswered, help us to find answer for this one

104. Which of the following is true about the EXPLAIN command

It can be used in BIND step(for embedded SQL).

EXPLAIN is used to display the access path as determined by the optimizer for a SQL statement

It can be used in SPUFI(for single SQL statement)

C,D & E

Answer

Correct Answer: C,D & E

Note: This Question is unanswered, help us to find answer for this one

105. Consider the following SQL statement, executed by user S001: CREATE VIEW BOSTON_TEAMS AS SELECT * FROM TEAMS WHERE STATE = 'MA' User S002 has INSERT authority on this view, what would happen if he tries to insert a row into this view, where the STATE field of that line contains a value of ‘IN’?

The row will be inserted only if the user has an authority to insert on table S001.TEAMS, but it will never show up in a SELECT on this view.

The row won’t be inserted because the field STATE doesn’t have the value ‘MA’.

The row will be inserted in the table S001.TEAMS, but it will never show up in a SELECT on this view.

You can’t insert into a view, because views don’t contain actual data; they’re just virtual windows on base tables.

Answer

Correct Answer: The row will be inserted in the table S001.TEAMS, but it will never show up in a SELECT on this view.

Note: This Question is unanswered, help us to find answer for this one

106. Given the following cursor declaration: DECLARE CLASSUPDATE CURSOR FOR SELECT CSTITLE FROM T001.CLASSS FOR UPDATE OF CDUR Which of the following embedded SQL statements will use this cursor correctly?

UPDATE T001.CLASSS SET CDUR = 5

UPDATE T001.CLASSS SET CDUR = 5 WHERE CURRENT OF CLASSUPDATE

UPDATE SET CDUR = 5 WHERE CURRENT OF CLASSUPDATE

UPDATE T001.CLASSS C SET C.CSTITLE = 'New Title' WHERE CURRENT OF CLASSUPDATE

Answer

Correct Answer: UPDATE T001.CLASSS SET CDUR = 5 WHERE CURRENT OF CLASSUPDATE

Note: This Question is unanswered, help us to find answer for this one

107. When a column has an extension of WITH DEFAULT NULL and a unique index is created on this column, what will be the effects on the possible null values in that column?

C & F

There is still a single null allowed, since it is unique as such.

Nulls are no longer allowed, since nulls aren’t unique.

It has no influence on the possible null values; DB2 doesn’t consider nulls when it comes to an index.

Answer

Correct Answer: There is still a single null allowed, since it is unique as such.

Note: This Question is unanswered, help us to find answer for this one

108. Which of the following is not a DB2 datatype

Vargraphic

Long Vargraphic

Graphic

Long Graphic

Answer

Correct Answer: Long Graphic

Note: This Question is unanswered, help us to find answer for this one

109. Which of the following statements concerning locking on TABLESPACE level is correct?

When a TABLESPACE is S-locked by another user, a U-lock can be placed. However, an X-lock is not compatible and will have to wait until the S-lock is released.

When a TABLESPACE is S-locked by another user, neither a U lock or an X lock is com- patible on this level. In both cases an I lock (IU lock, IX lock) will be placed to indicate that a U lock or X lock is waiting to be placed.

When a TABLESPACE is S-locked by another user, a U lock can be placed. However, an X lock is not compatible. A IX lock (Intent to eXclusively use) will be placed to indicate that a X lock is waiting to be placed.

When a TABLESPACE is S-locked (Shared use) by another user, a U lock (Update use) can be placed. However, an X lock (eXclusive use) on this level will force the S lock to be dropped, its transaction to be rolled-back to be able to place the X lock.

Answer

Correct Answer: When a TABLESPACE is S-locked by another user, a U-lock can be placed. However, an X-lock is not compatible and will have to wait until the S-lock is released.

Note: This Question is unanswered, help us to find answer for this one

110. SELECT empname , paygrade , salary FROM emp, salgrade WHERE salary BETWEEN lowsal AND highsal ORDER BY paygrade The above is an example of a

Outer join

None of the above

Non equi join

Inner join

Answer

Correct Answer: Non equi join

Note: This Question is unanswered, help us to find answer for this one

111. Which subquery operator compares a single value to every member of set of value.

Any

All

Between

Answer

Correct Answer: Any

Note: This Question is unanswered, help us to find answer for this one

112. Which statement about tablespaces is true?

Dropping a tablespace will not only remove all tables of the tablespace itself, but also all indexes created on these tables, even tho ugh they are stored separately in their own indexspace.

A tablespace is divided in units called pages , which hold one or more rows of a table. If a row of 8000 bytes is placed in a 4K page, th e row will automatically be split over two pages.

A tablespace is divided in units called pages , which hold one or more rows of a table. If a row of 8000 bytes is placed in a 4K page, the page will automatically be resized to 8K.

When creating a tablespace, one must indicate either the storage group or the bufferpool to be used by this tablespace. If neither is defined, the creation of the tablespace will fail.

Answer

Correct Answer: Dropping a tablespace will not only remove all tables of the tablespace itself, but also all indexes created on these tables, even tho ugh they are stored separately in their own indexspace.

Note: This Question is unanswered, help us to find answer for this one

113. When you don't know the format of an SQL statement within a program you're writing, what is a good option?

Dynamic SQL.

T-SQL.

Static SQL.

NoSQL.

Answer

Correct Answer: Dynamic SQL.

Note: This Question is unanswered, help us to find answer for this one

114. Which of the following is true about locking

The data (row) is locked until a commit is executed to release the updated data.

All of the above

Locking prevents concurrent users from accessing inconsistent data.

Locking is a process that is used to ensure data integrity.

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

DB2 Programming MCQs | Topic-wise

MCQ on Basic DB2 Programming

General DB2 Programming MCQs

DB2 Programming Subjects

More Resources

DB2 Programming MCQ

DB2 Programming Skill Assessment

DB2 Programming PDF

Related MCQs

Delphi MCQs

DHTML MCQs

Django MCQs

Docker MCQs

DOS MCQs

Basic DB2 Programming MCQ

1. Is DECLARE TABLE in DCLGEN necessary?

2. What is the purpose of the WHENEVER statement?

3. Consider the employee table with column MGR nullable. How can you get a list of employees who are not assigned to any mgr?

4. What are the advantages of using a PACKAGE?

5. What does DBRM Stand for?

6. What is returned by the VALUE function?

7. When would you choose to run RUNSTATS?

8. What is a subselect?

9. What are the disadvantages of PAGE level lock?

10. When you COMMIT, is the cursor closed?

11. What are the various locks available?

12. What should be done before you do EXPLAIN?

13. If the null indicator has -1, what does it mean?

14. Which technique is used to retrieve data from more than one table in a single SQL statement?

15. How do you concatenate the FIRSTNAME and LASTNAME from EMPLOYEE table to give a complete name?

16. How would you retrieve the first 5 characters of FIRSTNAME column of DB2 table EMP?

17. When do you specify the isolation level?

18. How do you leave the cursor open after issuing a COMMIT?

19. Can you have more than one cursor open at any one time in a program?

20. Suppose there is a cobol-db2 program. If you have made changes only in the cobol program, is there a need to recomile the DBRM?

21. What is the meaning of underscore ( '_' ) in the LIKE statement?

22. What are correlated subqueries?

23. What is the use of FREE command?

24. How would you find out the total number of rows in a DB2 table?

25. State whether true or false. When you open a cursor, DB2 will always get all rows that meet the selection criteria and create a result set.

26. Why is SELECT * not preferred in embedded SQL programs?

27. What is the restriction on using UNION in embedded SQL?

28. What is the difference between QMF & SPUFI?

29. What is a thread?

30. When will you use curser with hold option?

31. What is the statistics collected during RUNSTATS?

32. Under which circumstance would you create an index on a table?

33. How can you quickly find out the number of rows updated after an update statement?

34. What are the disadvantages of using VARCHAR?

35. How would you display the table definitions (columns, data types of columns, constraints or default values set etc) of a table in QMF?

36. Can GROUP BY and ORDER BY used in a single query?

37. What happens when you say OPEN CURSOR?

38. What are the various locking levels available?

39. How does DB2 determine which lock-size to use?

40. Can you use MAX on a CHAR column?

41. When can you not drop a database

42. In what form is data physically stored in DB2

43. Consider the following embedded SQL statement: SELECT CLASSNAME INTO :CLASSNAME:IND FROM CLASS WHERE STUDNAME = :STUDNAME In which order should the following program variables be evaluated by the part of the program just after this SQL statement?

44. Which of the following is not a type of dynamic SQL?

45. When using JBDC, do you have to recode your Java application if you want to change between drivers?

46. Which of the following represents a function that is performed for each row in a DB2 table

47. Which is an example of a column that would contain statistics necessary for programming?

48. Why must the DB2 precompiler be used to execute SQL statements?

49. What four languages can host variable arrays be specified in?

50. Can the SQL procedural language be used for advanced functions?

51. What is the Java data access platform used in DB2?

52. What happens when the execution of a utility is terminated by the TERM command?

53. Which of the following is not a DB2 object

54. In what catalog table must stored procedures be defined?

55. Which of the following is a correct syntactical example of written SQL code?

56. What is an example of an underlying cause for regressions caused by changes in DB2?

57. When should we execute a REBIND rather than a BIND

58. SQLSTATE is a standard set of error messages and warnings in which the first two characters defines the class and the last three defines the subclass of the error. Which of the following SQLSTATE codes is interpreted as "No data returned"?

59. Given the following DDL statements: CREATE TABLE Smart1 (a INT, b INT, c INT) CREATE VIEW View1 AS SELECT a,b,c FROM Smart1 WHERE a > 250 WITH CHECK OPTION Which of the following INSERT is correct

60. Which of the following is correct SQLJ syntax?

61. What happens to SQL statements if there are no stored procedures?

62. What code is used to start the CICS attachment facility?

63. What must happen before using the EXPLAIN statement?

64. Which of the following is a DB2 mechanism that ensures data integrity between tables related by Primary & Foreign Keys

65. When are dynamic SQL applications prepared?

66. What would the “SQL CONNECT” statement be used for when coding stored procedures?

67. Given the following DDL statement: CREATE TABLE newsmart1 LIKE smart1 Which of the following would occur as a result of the statement execution?

68. Which of the following is an example of a programming method not used in DB2 programming?

69. Which of the following occurs if a DB2 procedure or application ends abnormally during an active unit of work?

70. What type of SQL operator would you use to combine the results of 2 Select statements while retaining the duplicates

71. A declared temporary table is used for which of the following purposes?

72. A role is a database entity that groups together one or more privileges. Which of the following is true for a "role"

73. Which of the following statements eliminates all but one of each set of repeated rows inthe final result table?

74. What is a stored procedure?

75. What is a host structure?

76. What is SQLCA used for?

77. What are three examples of languages you can use to program in DB2?

78. Which of the following is the correct syntax for an input variable of a PARMLIST string?

79. What technique can DB2 use to more effectively interpret data from EXPLAIN tables?

25. State whether true or false.
When you open a cursor, DB2 will always get all rows that meet the selection criteria and create a result set.