DB2 Questions

True or False :

1. The major components of DB2 are system services component, Locking services component, Database services component and Distributed data facility component.

2. The DDL for creating an index space is CREATE INDEXSPACE.

3. More than 2 tables cannot be joined.

4. DB2 batch programs are executed in the background under the control of the TSO Terminal Monitor Program, IKJEFT01.

5. The COPY utility is used to create an image copy back up data set for a complex tablespace or a single partition of a tablespace.

6. Using a database will reduce data redundancy and address normalisation issue.

7. The DB2 catalog consists of regular tables, and is accessible by means of SQL data retrieval statements.

8. If you want to read a row and depending upon the values in the row, you want to modify, delete or do nothing, you can do that with a cursor, this is

accomplished with a cursor and a special clause of UPDATE and DELETE statements

usable only by embedded SQLs, namely WHERE CURRENT OF.

9. Referential Integrity ensures that the related tables stay in sync.

10. COMMIT and COMMIT WORK does the same function.

11. The data manipulation SQL statements are SELECT, INSERT, UPDATE and DELETE.

12. There can be more than one primary key for a table.

13. If the WHERE clause in the UPDATE or DELETE includes a subquery, then the FROM clause of that subquery must not refer to the table that is being updated

or deleted.

14. The three different types of database management systems are relational, hierarchical and network models.

15. The expression DATE('5/31/1977') + 1 MONTH will yield the result ‘6/30/1997’.

16. If a given column has nulls allowed, a UNIQUE index on that column will permit at the most one null to appear at any given time.

17. A mechanism called CICS Attachment Facility connects CICS with DB2.

18. The key word DISTINCT is not allowed for COUNT(*) function.

19. The CHECK utility has two options CHECK DATA and CHECK INDEX.

20. The catalog manipulation utilities of DB2 are CATMAINT, MODIFY, RUNSTATS and STOSPACE.

21. You can create a base table which has the same structure as some existing table using the CREATE TABLE ......... LIKE........ command.

22. DBADM or Database Administration authority on a specific database allows the

holder to execute any operation that the system supports on that database.

23. The ON DELETE CASCADE option means that the delete operation is cascaded,

that is if a row in the parent table is deleted then all the matching rows in the

child table is automatically deleted.

24. A view defined on a non-updatable view is updatble.

25. QUIESEC utility ensures that all tablespaces in the scope of QUIESEC are

refrentially intact.

26. The COMMIT and ROLLBACK statements are instruction to the DBMS.

27. System Operator authority allows the holder to carry out console operator

functions on the system such as starting and stopping system trace activities.

28. A package is a single, bound DBRM with optimised access paths containing bind timestamps.

29. The function of the Bind is to convert the high-level DBRMs into an optimised internal form.

30. The primary key is the unique identifier of the rows in a table.

31. A view is a virtual table, a table which does not physically exist.

32. The REPORT TABLESPACESET option generates a report detailing all tables and

tablespaces in a referential tablespace set.

33. The DB2 log registers data changes and significant events as they occur

into a DASD dataset called the active log.

34. SELECT PNAME FROM P WHERE PNAME NOT LIKE '%C%' will get the name of all the

parts whose name has a character C in it.

35. Database Maintenance authority on a specific database allows the holder to

execute read-only maintenance functions such as 'IMAGE COPY' on that database.

36. Every column participating in the primary key must be explicitly declared

NOT NULL.

37. SYSADM or System Administration authority allows the holder to execute any

operation that the system supports.

38. If the definition of the view involves either a GROUP BY or a HAVING clause

at the outermost level, then the view is updatable.

39. Any ORDER BY clause in the query must appear as part of the final SELECT

only and must identify ordering columns by their ordinal position or number

and not by name.

40. In a hierarchical model, data is represented as a tree structure.


41. If the definition of the view involves DISTINCT at the outermost level, then

that view is not updatable.

42. The SELECT * FROM...... will fetch all the columns of the table meeting the

search criteria.

43. The SQL ' SELECT AVG(MIN(QTY)) AS AVERAGE' is legal.

Multiple Choice : Choose the best answer.

44. A key made of more than one column is called

1. Composite key ( )

2. Complex key ( )

3. Foreign key ( )

4. None of the above ( )

45. Which is the function used for extracting the hour portion of the time or

timestamp

1. TIME ( )

2. HOUR ( )

3. TIMESTAMP TO HOUR ( )

4. None of the above ( )

46. What is the command that is used to eliminate duplicates in a SELECT statement

1. WITH NO DUPLICATES ( )

2. NO DUPLICATES ( )

3. DISTINCT ( )

4. None of the above ( )

47. In relational model rows are called

1. Rows ( )

2. Relations ( )

3. Tuples ( )

4. None of the above ( )

48. What are the date/time data types that DB2 supports?

1. DATE ( )

2. TIME ( )

3. TIMESTAMP ( )

4. All of the above ( )

49. Fill in the blanks to make the SQL complete and to ensure that all the

inserts and updates made on the view can specify values 'M' or 'F' only:

CREATE VIEW emp (emp-no, name, sex,age, dept, salary)

AS SELECT emp-no, name,sex, age, dept, salary

FROM emp-table WHERE sex IN ('M', 'F')

WITH -------------- OPTION;

1. EXAMINE ( )

2. CHECK ( )

3. ISSUE ( )

4. None of the above ( )

50. What are the functions of the Database services component, which supports

the definition, retrieval and update of user and system data.

1. Data definition ( )

2. Data retrieval ( )

3. Data manipulation ( )

4. All of the above ( )

51. What are the DB2 objects that can be altered using the ALTER statement?

1. Views ( )

2. Tables ( )

3. Indexes ( )

4. Tables and Indexes ( )

52. What are the data organisation utilities of DB2?

1. LOAD and REORG ( )

2. LOAD and RECOVER ( )

3. REORG and REPAIR ( )

4. None of the above ( )

53. What are the main backup utilities of DB2?

1. COPY and MERGE COPY ( )

2. QUIESEC ( )

3. RECOVER ( )

4. All of the above ( )

54. Which are the situations in which column names must be specified explicitly

for all columns of the view?

1. Any column of the view is derived from a function ( )

2. Any column of the view is derived from an operational expression ( )

3. Two or more columns of the view would otherwise have the same name ( )

4. All of the above ( )

55. A given foreign key value must have matching primary key value in some tuple

of the referenced relation if that foreign key value is non null. This principle is

1. Entity Integrity ( )

2. Referential Inegrity ( )

3. Primary Integrity ( )

4. None of the above ( )

56. What are the numeric data types that DB2 supports?

1. INTEGER, SMALLINT, DECIMAL, FLOAT ( )

2. INTEGER, DECIMAL, FLOAT ( )

3. INTEGER, FLOAT ( )

4. None of the above ( )

57. If NOT NULL WITH DEFAULT is specified during an ALTER TABLE command what

will be the default values for the DATE, TIME and TIMESTAMP?

1. SPACES ( )

2. CURRENT DATE, CURRENT TIME and CURRENT TIMESTAMP ( )

3. 01-01-0001, 00:00:00, 0001-01-01-00.00.00.000000 (@)

4. None of the above ( )

58. If NOT NULL WITH DEFAULT is specified, what will be the default values for

the character fields?

1. SPACES ( )

2. NULLS ( )

3. ZEROES ( )

4. None of the above ( )

59. What is the situation in which two or more transactions are in simultaneous?

Wait state, each waiting for one of the others to release a lock before it can proceed

Called?

1. Time-out ( )

2. Deadlock ( )

3. Rollback ( )

4. None of the above ( )

60. What is the number of tuples or rows in a relation called?

1. Degree ( )

2. Cardinality ( )

3. Row count ( )

4. None of the above ( )

61. What are the various environments in which DB2 operates?

1. IMS/DB and IMS/DC ( )

2. CICS ( )

3. TSO batch and on-line ( )

4. All of the above ( )

62. What is the name of the first prototype of SEQUEL introduced by IBM in 1974?

1. SQL ( )

2. SEQUEL-XRM ( )

3. SEQUEL ( )

4. None of the above ( )

63. What is CAF?

1. Call According to function ( )

2. Call Attachment Facility ( )

3. Continuous Application Facility ( )

4. None of the above ( )

64. The equijoin must produce a result containing two identical columns. If one

Of these columns is eliminated then that join is called:

1. Equijoin ( )

2. Natural join ( )

3. Outer join ( )

4. None of the above ( )


65. When WHERE CURRENT OF clause is used the cursor should be declared with.

1. UPDATE clause ( )

2. FOR UPDATE OF clause ( )

3. MODIFY clause ( )

4. None of the above ( )

66. What are the two kinds of tables in DB2?

1. Tables and Files ( )

2. Tables and Indexes ( )

3. Base tables and Views ( )

4. All of the above ( )

67. What is the input to the REPORT RECOVERY utility?

1. A tables pace ( )

2. Single partition of a partitioned tables pace( )

3. All of the above ( )

68. What are the different types of table spaces?

1. Simple ( )

2. Partitioned ( )

3. Segmented ( )

4. All of the above ( )

69. What is the TSO parameter controls the number of concurrent DB2 batch connections

1. IDFORE ( )

2. IDNO ( )

3. IDBACK ( )

4 None of the above ( )

70. What is the output of the Bind operation?

1. Load module ( )

2. Application Plan ( )

3. DBRMs ( )

4. None of the above ( )

71. What is an attribute or attribute combination of one table whose values are

required to match those of the primary key of some other table called?

1. Primary key ( )

2. Foreign key ( )

3. Composite key ( )

4. Alternate key ( )

72. Primary keys

1. Are unique identifiers of a table ( )

2. Cannot contain nulls ( )

3. Not more than one for a table ( )

4. All of the above ( )

73. What is the expansion of SPUFI?

1. Sequential Programming Using Functional Input ( )

2. SQL Processor Using File Input ( )

3. SQL Program Using Fast Interaction ( )

4. None of the above ( )

74. What is the DB2 utility used to generate host variables ?

1. QMF ( )

2. SPUFI ( )

3. DCLGEN ( )

4. None of the above ( )

75. Which was the first successful prototype of a RDBMS?

1. XRM ( )

2. SYSTEM-R ( )

3. DB2/2 ( )

4. None of the above ( )

76. What are the delimiters for embedded SQLs in COBOL?

1. EXEC and END EXEC ( )

2. EXEC SQL and END-EXEC ( )

3. SQL and END-SQL ( )

4. None of the above ( )

77. What are the functions of the System services component?

1. System operation ( )

2. Operator communication ( )

3. Logging ( )

4. All of the above ( )

78. What is the page size of an index space?

1. 32KB ( )

2. 4KB ( )

3. 16KB ( )

4. 32KB and 4KB ( )

79. What is the error handling SQL statement?

1. SELECT ( )

2. INSERT ( )

3. UPDATE ( )

4. WHENEVER ( )

80. What is the significance of the following sqlcodes ;

1. -180 ==>

2. -181 ==>

3. -204 ==>

4. -206 ==>

5. -310 ==>

6. -805 ==>

7. -811 ==>

8. -818 ==>

9. -904 ==>

10. -911 ==>

11. 0 ==>

12. +100 ==>


DB2 Answers

Name : Emp# : Date :

Q.No

Solution

Q.No

Solution

Q.No

Solution

Q.No

Solution

1

21

41

61

2

22

42

62

3

23

43

63

4

24

44

64

5

25

45

65

6

26

46

66

7

27

47

67

8

28

48

68

9

29

49

69

10

30

50

70

11

31

51

71

12

32

52

72

13

33

53

73

14

34

54

74

15

35

55

75

16

36

56

76

17

37

57

77

18

38

58

78

19

39

59

79

20

40

60

80

Answer for Qn 80:

1. -180 ==>

2. -181 ==>

3. -204 ==>

4. -206 ==>

5. -310 ==>

6. -805 ==>

7. -811 ==>

8. -818 ==>

9. -904 ==>

10. -911 ==>

11. 0 ==>

12. +100 ==>

No comments: