General DB2 Programming MCQs

1. Which of the following is true about locking

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

3. Which statement about tablespaces is true?

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

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

6. Which statement concerning locking on TABLESPACE level is correct?

7. If the SQLCA is included in the program, which of the following host-variables is NOT known and as such unavailable for the program?

8. Which of the following is not a DB2 datatype

9. 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?

10. 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?

11. 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’?

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

13. 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?

14. 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?

15. 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?

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

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

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

19. How are ODBC calls binded?

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

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

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

23. 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?

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

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

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

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

28. Which statement about an index is NOT true?

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

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

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

32. How many indexes will be created by the following statement? Create table Smarterer { Col1 int not null primary key, Col2 char (64), Col3 char (32), Col4 int not null, Constraint c4 unique (Col4,Col1) }

33. What's the output of a DB2 Bind

34. What must a SELECT statement be coded within?

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

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

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

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

39. Correct syntax for an input variable of a PARMLIST string?

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

41. What is SQLCA used for?

42. What is a host structure?

43. Which object allows multiple users to access data in a table with each only being able to access certain subsets of the data?

44. What is a stored procedure?

45. Which eliminates all but one of each set of repeated rows in the final result table?

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

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

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

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

50. Which is an example of a programming method not used in DB2 programming?

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

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

53. When are dynamic SQL applications prepared?

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

55. What must happen before using the EXPLAIN statement?

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

57. What are the maximum number of tables that can be joined in DB2

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

59. Which of the following is correct SQLJ syntax?

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

61. 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'?

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

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

64. Which is a correct syntactical example of written SQL code?

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

66. Which of the following is not a DB2 object

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

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

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

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

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

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

73. Which represents a function that is performed for each row in a DB2 table

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

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

76. Any database needs to go through a normalization process to make sure that data is represented only once. This will eliminate problems with creating or destroying data in the database. The normalization process is done usually in three steps which results in first, second and third normal forms. Which best describes the process to obtain the third normal form?

77. 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?

78. Besides on TABLE and TABLESPACE level, on what level can implicit locks be placed by DB2?

79. In what form is data physically stored in DB2

80. When can you not drop a database

81. Can you use MAX on a CHAR column?

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

83. What are the various locking levels available?

84. What happens when you say OPEN CURSOR?

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

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

87. What are the disadvantages of using VARCHAR?

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

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

90. What is the statistics collected during RUNSTATS?

91. When will you use curser with hold option?

92. What is a thread?

93. What is the difference between QMF & SPUFI?

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

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

96. When you open a cursor, DB2 will always get all rows that meet the selection criteria and create a result set.

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

98. What is the use of FREE command?

99. What are correlated subqueries?

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

101. 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?

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

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

104. When do you specify the isolation level?

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

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

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

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

109. What should be done before you do EXPLAIN?

110. What are the various locks available?

111. When you COMMIT, is the cursor closed?

112. What are the disadvantages of PAGE level lock?

113. What is a subselect?

114. When would you choose to run RUNSTATS?

115. What is returned by the VALUE function?

116. What does DBRM Stand for?

117. What are the advantages of using a PACKAGE?

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

119. What is the purpose of the WHENEVER statement?

120. Is DECLARE TABLE in DCLGEN necessary?