> > DB2 Administration MCQs > Basic DB2 Administration MCQs

Basic DB2 Administration MCQ

1. Which of the following can a DBA do to determine what an image copies, active logs and archive logs will be required for the recovery of a table space?

Read the SYSIBM.SYSCOPY table

Run the Report Recovery utility

Run the COPY TABLESPACE utility

Run the MERGECOPY TABLESPACE utility

Answer

Correct Answer: Run the Report Recovery utility

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

2. Which of the following table space characteristics for a system Managed (SMS) table space can be modified with the ALTER TABLESPACE Statements?

Extent Size

Prefetch Size

Number of containers

Size of an existing container

Answer

Correct Answer: Prefetch Size

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

3. what is the root page in DB2?

It is a simple DB2 index

It is a B-tree

It is a B-tree's top page

It is a clustered index

Answer

Correct Answer: It is a B-tree's top page

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

4. What is a deadlock?

A deadlock occurs when two or more programs are read simultaneously

When transaction fails it is called a deadlock

A deadlock occurs when transactions are executed at the same time

None of the above

Answer

Correct Answer: A deadlock occurs when transactions are executed at the same time

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

5. Which of the following COPY utility statements will backup table space DBTAB1.TS1 preparing 2 copies for local use and 2 copies for disater recovery use?

Copy Tablespace DBTAB1.TS1 LOCAL(2) OFFSITE(2)...

Copy Tablespace DBTAB1.TS1 COPYDDN(DD1,DD2,DD3,DD4)...

Copy Tablespace DBTAB1.TS1 LOCAL(DD1,DD2) OFFSITE(DD3,DD4)...

Copy Tablespace DBTAB1.TS1 COPYDDN(DD1,DD2)RECOVERYDDN(DD3,DD4)...

Answer

Correct Answer: Copy Tablespace DBTAB1.TS1 LOCAL(DD1,DD2) OFFSITE(DD3,DD4)...

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

6. Which of the following index type will cause the rows of the table to be arranged on the disk according to the ordering of their index keys?

Unique Index

Clustered Index

Non-unique Index

Index with include column

Answer

Correct Answer: Clustered Index

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

7. Which function does QUIESCE perform?

It flushes all DB2 buffers on to the disk

It gives a correct snapshot of the database

It is a LOAD utility

None of the above

Answer

Correct Answer: It flushes all DB2 buffers on to the disk

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

8. What are segmented table spaces?

Table spaces divided into segments of 1 to 10 pages

Table spaces divided into segments of 4 to 64 pages

Table spaces containing only one table

None of the above

Answer

Correct Answer: Table spaces divided into segments of 4 to 64 pages

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

9. Can you use LOCK TABLE on a view?

Yes

Answer

Correct Answer: No

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

10. How do you perform the EXPLAIN of a dynamic SQL statement?

EXPLAIN of a dynamic SQL cannot be done

By using SPUFI or QMF to EXPLAIN the dynamic SQL statement

By including EXPLAIN command in the embedded dynamic SQL statements

None of above

Answer

Correct Answer: By using SPUFI or QMF to EXPLAIN the dynamic SQL statement

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

11. Which of the following cannot be done with the ALTER TABLE statement?

Change the cache value for an identity column

Turn on auditing for the table

Add a primary key to the table

Add a column to the end of a table row

Answer

Correct Answer: Change the cache value for an identity column

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

12. Where is the output of EXPLAIN stored?

In SYSIBM schema

In userid.PLAN_TABLE

IN SPUFI

None of above

Answer

Correct Answer: In userid.PLAN_TABLE

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

13. A DBA issues the command:-DIS DB(DSNDB06) SPACE(*) LOCKS. In the LOCKINFO field the following data is reported: H-IS,S,C. When Will the lock first be released by DB2?

COMMIT

Deallocation

When the table space is stopped

When DB2 moves to the next page in the table

Answer

Correct Answer: COMMIT

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

14. What does sqlcode -922 denote?

Database failure

Network failure

Authorization failure

Instance failure

Answer

Correct Answer: Authorization failure

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

15. What does the term IMAGECOPY mean?

It is a full backup of a DB2 database

It is a full backup of a DB2 redolog

It is a full backup of a DB2 table

All of the above

Answer

Correct Answer: It is a full backup of a DB2 table

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

16. Which of the following is true when joining a table with DB2 Relational Connect function?

DB2's cost based optomizer is turned off for Federated database requests

The DB2 optimizer is not aware that all tables are not in the same DB2 instance

The DB2 optimizer is  aware that some tables are on remote machines, but will not perform any optimization on SQL sent to the remote database

The DB2 optimizer is aware that some tables are on remote machines, and where appropriate will rewrite SQL before sending it to the remote machine

Answer

Correct Answer: The DB2 optimizer is aware that some tables are on remote machines, and where appropriate will rewrite SQL before sending it to the remote machine

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

17. How many clustering indexes can be defined for a table?

Two

four

One

Six

Answer

Correct Answer: One

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

18. If a user accidentally drops a table and commits work, which of the following recovers the dropped table?

Once commited the dropped table cannot be recovered

If ALTER TABLESPACE..DROPPED TABLE RECOVERY on enabled,restore tablespace,role forward

If ALTER TABLESPACE..DROPPED TABLE RECOVERY on not enabled, first alter tablespace and then restore database and rollforward

If ALTER TABLESPACE..DROPPED TABLE RECOVERY on not enabled, first alter tablespace and then restore tablespace and rollforward

Answer

Correct Answer: If ALTER TABLESPACE..DROPPED TABLE RECOVERY on enabled,restore tablespace,role forward

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

19. When is the access path determined for dynamic SQL?

At run time

At compile time

At bind time

All of the above

Answer

Correct Answer: At run time

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

20. Which of the following is not true for a segmented table space?

It is more efficient for table spaces greater than 4 GB

It makes free pages available immediately after a commit

It generally skips empty pages when a table scan is being processed

It has more efficient space map processing than a simple tablespace

Answer

Correct Answer: It is more efficient for table spaces greater than 4 GB

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

21. What is a DB2 bind?

A process that builds an access path to DB2 tables

A process that accesses index

A process that defines table structure

None of the above

Answer

Correct Answer: A process that builds an access path to DB2 tables

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

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

It is based on the lock-size given while creating the tablespace

The programmer may direct the DB2 on which lock-size to use

It is not possible to dertermine the lock-size

None of above

Answer

Correct Answer: It is based on the lock-size given while creating the tablespace

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

23. What is a DB2 access path?

It is a method used to access data specified in DB2

It is an application

It is a package

It is a method user to show the execution plan

Answer

Correct Answer: It is a method used to access data specified in DB2

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

24. What is dynamic SQL?

An SQL statement written in procedure

An SQL statement written in cursor

An SQL statement created at program execution time

None of the above

Answer

Correct Answer: An SQL statement created at program execution time

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

25. Can you update a view which is based on more than one table?

Yes

Answer

Correct Answer: No

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

26. What happens during a DB2 Checkpoint?

Resource managers are invoked to write their status to the log

Data Manager writes DBE table log records

Both a and b

None of above

Answer

Correct Answer: Both a and b

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

27. The various locking levels available are:

User, Schema

Page, Table, Tablespace

Database, Segment

Segment, Extent

Answer

Correct Answer: Page, Table, Tablespace

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

28. Which of the following statements will gather only the index statistics for the table john.table1?

Runstats on table john.table1 indexes all

Runstats on table john.table1 for indexes all

Runstats on table john.table1 on indexes all

Runstats on table john.table1 and indexes all

Answer

Correct Answer: Runstats on table john.table1 for indexes all
Runstats on table john.table1 and indexes all

Note: This question has more than 1 correct answers

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

29. What do you need to do before you perform EXPLAIN?

Create PLAN_TABLE  under the AUTHID

Create EXPLAIN  under the AUTHID

Create SPUFI under the AUTHID

Create QMF under the AUTHID

Answer

Correct Answer: Create PLAN_TABLE  under the AUTHID

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

30. The various locks available are _____________.

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

31. When is the authorization check on DB2 objects done - at BIND time or run time?

At bind time

It is never done

At run time

At compile time

Answer

Correct Answer: At run time

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

32. Which of the following ALTER TABLESPACE statements causes DB2 to record data page changes in the space map pages of a segmented table space DSN8D71A.DSN8S71E?

ALTER TABLESPAcE dsn8d71a.dsn8s7ie log yes;

ALTER TABLESPAcE dsn8d71a.dsn8s7ie close yes;

ALTER TABLESPAcE dsn8d71a.dsn8s7ie Trackmod yes;

ALTER TABLESPAcE dsn8d71a.dsn8s7ie changemod yes;

Answer

Correct Answer: ALTER TABLESPAcE dsn8d71a.dsn8s7ie Trackmod yes;

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

33. What is RUNSTATS?

It is a package

It is a utility used to collect statistics

It is a Collection

It is a DB2 Procedure

Answer

Correct Answer: It is a utility used to collect statistics

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

34. What are partitioned table spaces?

Table spaces divided into segments

Table spaces divided into parts

Table spaces containing one or more tables

All of the above

Answer

Correct Answer: Table spaces divided into parts

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

35. Which of the following commands will give the minimum point in time recovery for table spaces of a recoverable database?

Get dbm cfg

List minimum point in time

List tablespace show detail

Get db cfg for <database-alias

Answer

Correct Answer: List tablespace show detail
Get db cfg for <database-alias

Note: This question has more than 1 correct answers

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

36. Which DB2 catalog table can be used to determine if a user has authorization to bind a new plan?

SYSDBAUTH

SYSTABAUTH

SYSUSERAUTH

SYSPLANAUTH

Answer

Correct Answer: SYSUSERAUTH

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

37. What is a clustering index?

An index that stores only unique values

A mandatory index defined on a partitioned table space

An index Which is on more than one key

None of the above

Answer

Correct Answer: A mandatory index defined on a partitioned table space

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

38. Index cardinality represents:

The number of distinct values a column Contains

The number of duplicate values a column Contains

The Index which is on more than one key

None of above

Answer

Correct Answer: The number of distinct values a column Contains

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

39. Which of the following is required to import data into a table?

SYSCTRL

LOAD authority on the table

ALTER privilege on the table

IMPORT authority on the table

INSERT privilege on the table

Answer

Correct Answer: LOAD authority on the table

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

40. What does the term EXPLAIN denote?

A precompiler

A utility to run a DB2 batch program

It displays the access path as determined by the optimizer

A BIND step for embedded SQL

Answer

Correct Answer: It displays the access path as determined by the optimizer

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

41. Is the parameter, “database_memory” configured automatically?

Yes, however part of it is left to manual configuration for security reasons.

Only partially. The rest of it has to be partitioned correctly.

Yes, however the settings need to be configured manually.

Yes.

Answer

Correct Answer: Yes.

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

42. Which of the following allows index data to be stored on separate devices from table data?

SMS table spaces

TMS table spaces

DMS table spaces

The table space containers defined on different devices

Answer

Correct Answer: DMS table spaces

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

43. Given the following statements: CREATE TABLE T1 (COL1 INT NOT NULL PRIMARY KEY, COL2 CHAR, COL3 CLOB (40K), COL4 VARCHAR(10)); CREATE UNIQUE INDEX IND_1 ON T1 (COL1, COL2) INCLUDE (COL3) ALLOW REVERSE SCANS; The CREATE UNIQUE INDEX statement will fail because:

Reverse scans are not supported on multi-column indexes.

A unique index cannot include an existing primary key.

LOB columns cannot be used in an index.

INCLUDE columns are not supported in indexes that support REVERSE SCANS.

Answer

Correct Answer: LOB columns cannot be used in an index.

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

44. What technique can be used to reduce the number of calculations in a query?

Compiled pairing.

Compiled aggregation.

Paired aggregation.

Shared aggregation.

Answer

Correct Answer: Shared aggregation.

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

45. What does it mean if the the null indicator = 0

The field is not null

The field is null

The field is truncated

The field has blanks

Answer

Correct Answer: The field is not null

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

46. Which of the following DSNZPARMs is necessary to influence access path selection for certain queries?

OPTHINTS

STDSQL

DESCSTAT

PARTKEYU

Answer

Correct Answer: OPTHINTS

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

47. Given the following statement: CREATE TABLESPACE smt1 MANAGED BY DATABASE USING (FILE 'smt01' 1024K) How many pages will be created for the table space?

512

256

1024

128

Answer

Correct Answer: 256

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

48. Given the following DDL statements: CREATE TABLE person OF person_t (REF IS OID USER GENERATED) CREATE TABLE emp OF emp_t UNDER person INHERIT SELECT PRIVILEGES CREATE TABLE student OF student_t UNDER person INHERIT SELECT PRIVILEGES Which of the following will drop all tables associated with the hierarchy?

Dropping the table hierarchy called PERSON

Dropping all columns from the subtables STUDENT and EMP

Deleting all rows from the person hierarchy

Dropping the PERSON table

Answer

Correct Answer: Dropping the table hierarchy called PERSON

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

49. What does it mean if the the null indicator = -1

The field is null

The field is truncated

The field has spaces

The field has blanks

Answer

Correct Answer: The field is null

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

50. To set up a client that can access DB2 UDB through DB2 Connect Enterprise Edition, which of the following is the minimum software client that must be installed?

DB2 Administration Client

DB2 Personal Edition

DB2 Application Development Client

DB2 Runtime Client

Answer

Correct Answer: DB2 Runtime Client

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

51. Which of the following RACF profiles would be used to allow IMS to access DB2 DSN1?

DSN1.SASS

DSN1.IMS

DSN1.MASS

DSNR.IMS

Answer

Correct Answer: DSN1.MASS

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

52. What parameter and option must be set before using a DB2 DBMS?

DBMS, YES.

DB2, YES.

Federated, NO.

Federated, YES.

Answer

Correct Answer: Federated, YES.

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

53. Which of the following explicit system privileges allows the user to create new plans and packages without being able to also execute them?

BINDADD

CREATEDBA

CREATEIN

BINDAGENT

Answer

Correct Answer: BINDAGENT

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

54. An audit trace shows that TSO user TSOID1 with DBADM authority, is continually attempting to update a table in DB2 subsystem DSN1 that is not supposed to be updated. Which of the following will prevent access to the DB2 subsystem?

Change TSOID1 ‘s access to DSNR resource class DSN1.BATCH to READ

REVOKE DBADM FROM TSOID1

Change TSOID1 ‘s access to DSNR resource class DSN1.BATCH to NONE

Change TSOID1 ‘s access to DSNR resource class DSN1.TSO to NONE

Answer

Correct Answer: Change TSOID1 ‘s access to DSNR resource class DSN1.BATCH to NONE

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

55. If you have a poorly clustered index with random synchronous I/Ss, what clause would you use to prevent an impending delay?

OPTIMIZE FOR

OPTIMIZE

OPTIMIZE ON

OPTIMIZE NOW

Answer

Correct Answer: OPTIMIZE FOR

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

56. Given that there are two containers in table space Smart1 and the following statement: ALTER TABLESPACE Smart1 REDUCE (ALL 100 M) Which of the following will happen?

Each container in table space Smart1 will be reduced in size by 100 MB.

Each container in table space Smart1 that has no data will be reduced in size to 100 MB.

Each container in table space Smart1 will be reduced in size to 100 MB.

The total size of table space Smart1 will be reduced to 100 MB.

Answer

Correct Answer: Each container in table space Smart1 will be reduced in size by 100 MB.

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

57. Given the following table definition: CREATE TABLE smarterer_list (empid INTEGER, points INTEGER CONSTRAINT chk CHECK (points <= 100) NOT ENFORCED ) and the statement: INSERT INTO smarterer_list VALUES (98, 100), (123,123), (101, 98) How many records will be retrieved by the following statement? SELECT * FROM smarterer_list

Answer

Correct Answer: 3

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

58. What table space characteristic can affect how your compiler operates?

Operator characteristics.

Container characteristics.

DB2 characteristics.

Compiler characteristics.

Answer

Correct Answer: Container characteristics.

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

59. Which of the following is not a statistic collected during RUNSTATS

No of rows in the table

No of distinct values of indexed column

None of the above

Percent of rows in clustering sequence

Answer

Correct Answer: None of the above

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

60. DB2 Enterprise Server Edition (ESE) is running on Linux and needs to validate the userids and passwords on the z/OS server for the DB2 clients connecting to DB2 for z/OS. Which of the following authentication levels satisfies this while providing authentication for other DB2 clients at the DB2 ESE server?

DCS

DRDA

HOST

CLIENT

Answer

Correct Answer: DCS

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

61. What is intra-partition parallelism?

A technique that utilizes multiple subagents to scan an index, or table.

A technique that partitions multiple subagents to format an index, or table.

A technique that utilizes multiple databases to scan an index, or hard disk.

A technique that utilizes multiple subagents to scan an index, or table.

Answer

Correct Answer: A technique that utilizes multiple subagents to scan an index, or table.

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

62. Which of the following kinds of table spaces allows LOBs to use the filesystem cache?

A system temporary table space

An SMS table space

A DMS table space created with raw devices

A user temporary table space

Answer

Correct Answer: An SMS table space

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

63. Which of the following is correct about the EXPLAIN statement

EXPLAIN is used to display the access path as determined by the optimizer for a SQL statement. It cannot be used in SPUFI (for single SQL statement) or 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) or in BIND step (for embedded SQL)

EXPLAIN is used to execute a SQL statement. It can be used in SPUFI (for single SQL statement) or 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) but not in the BIND step

Answer

Correct Answer: 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) or in BIND step (for embedded SQL)

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

64. A DBA needs to use the DSN command processor to delete DB2 packages that are no longer needed. Which of the following choices is correct for the DBA to use?

DROP Package (<collid>.<name of package>.<version id>)

FREE Package (<collid>.<name of package>.<version id>)

SPUFI or QMF with the DROP statement

DROP PLAN (<plan name>) PKLIST (<collid>.<name of a particular package>.<version id>)

Answer

Correct Answer: FREE Package (<collid>.<name of package>.<version id>)

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

65. What type of parameter is “dbheap”?

Configuration.

Database.

Network.

System.

Answer

Correct Answer: Database.

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

66. What is one way that a query might be rewritten?

Operation compiling.

Operation merging.

Operation churning.

Operation cloning.

Answer

Correct Answer: Operation merging.

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

67. To prepare an embedded SQL program for use with a host-language compiler, which of the following database components is required?

Application Development Center

Binder

Precompiler

Stored Procedure Builder

Answer

Correct Answer: Precompiler

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

68. Which of the following tools can be used to edit related backup tasks created in the Task Center

Command Center

Journal

Development Center

Operations Center

Answer

Correct Answer: Command Center

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

69. Which of the following actions will occur when issuing the command FORCE APPLICATION ALL?

No new database connections are allowed

Uncommitted units of work are rolled back

Uncommitted units of work are committed

Disconnect warning messages are sent to connected users

Answer

Correct Answer: Uncommitted units of work are rolled back

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

70. Given the following code: EXEC SQL EXECUTE IMMEDIATE: sqlstmt Which of the following values must sqlstmt contain so that all rows are deleted from the STAFF table?

DROP * FROM staff

DELETE * FROM staff

DROP TABLE staff

DELETE FROM staff

Answer

Correct Answer: DELETE FROM staff

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

71. If your columns are indexed, what clause would you run for the RUNSTATS command to collect statistics?

ONLY ON KEY COLUMNS

ONLY ON KEY STATS

ONLY FROM STAT COLUMNS

ONLY ON STAT COLUMNS

Answer

Correct Answer: ONLY ON KEY COLUMNS

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

72. What two types of configuration files does DB2 use?

Database manager configuration files, and database configuration files for the database itself.

Database manager compression files, and database compression files for the database itself.

Database log files, and database configuration files for the database itself.

Database cache configuration files, and database master files for the database itself.

Answer

Correct Answer: Database manager configuration files, and database configuration files for the database itself.

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

73. What should be considered when specifying an optimization level?

A query's use of active or passive SQL.

A query's use of static or dynamic SQL.

A query's use of PL/SQL or T-SQL.

A query's use of logical or dynamic SQL.

Answer

Correct Answer: A query's use of static or dynamic SQL.

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

74. What condition would lead to distribution statistics not being collected?

Consecutive data values.

Unique data values.

Excessive data values.

Equal data values.

Answer

Correct Answer: Unique data values.

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

75. Where would the optimizer gather information to estimate the amount of prefetching for a tablespace?

The CATSIZE and EXTENTSIZE columns in CAT.TABLESPACES.

The PRECAT and EXTENTIZER columns in SYSCAT.TABLESPACES.

All of the columns in SYSCAT.TABLESPACES.

The PREFETCHSIZE and EXTENTSIZE columns in SYSCAT.TABLESPACES.

Answer

Correct Answer: The PREFETCHSIZE and EXTENTSIZE columns in SYSCAT.TABLESPACES.

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

76. At which of the following times is the access control authorization routine (DSNX@XAC) invoked?

During any authorization check if NO was specified in the USE PROTECTION field of the DSNTIPP panel

At DB2 startup

When DB2 has cached authorization information

When executing a DB2 GRANT statement

Answer

Correct Answer: At DB2 startup

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

77. If an object is created statically by a role within a trusted context and the ROLE AS OBJECT OWNER clause is specified, who becomes the object owner when executing the package?

The role

The owner keyword

The current SQLID (If set)

The schema name

Answer

Correct Answer: The role

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

78. Which of the following can be changed with an ALTER statement

Constraint

Tablespace

Index

Answer

Correct Answer: Tablespace

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

79. What is the parameter to configure the default database path?

dftdbpath

dftdbpdb

dftdb

dbpath

Answer

Correct Answer: dftdbpath

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

80. What does it mean if the the null indicator = -2

The field is null

The field has spaces

The field value is truncated

The field has blanks

Answer

Correct Answer: The field value is truncated

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

81. Which of the following is an important step in developing a performance-improvement process?

Plan all of your adjustments and do them in one step.

Make all adjustments in step.

Make one adjustment at a time.

Don't adjust the system's performance.

Answer

Correct Answer: Make one adjustment at a time.

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

82. Which two of the following identify which users have SYSCTRL authority?

The database manager configuration

D&E

The operating system security

The database configuration

Answer

Correct Answer: D&E

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

83. What is a scenario in which you would be using a single buffer pool?

You are working on a test system.

The system has errors.

Your system is locked.

You working on a broken system.

Answer

Correct Answer: You are working on a test system.

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

84. What calculation can you use to estimate your overhead cost?

Average seek time in minutes + (0.5 * relational latency)

Average seek time in miliseconds + (0.5 * rotational latency)

Total seek time in miliseconds + (0.5 * rotational latency)

Average seek time in minutes + (0.5 * rotational latency)

Answer

Correct Answer: Average seek time in miliseconds + (0.5 * rotational latency)

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

85. What are EDUs responsible for?

EDUs process most of the SQL processing for applications.

EDUs process SQL.

EDUs process the security functions to use SQL.

EDUs act as a compiler to use SQL.

Answer

Correct Answer: EDUs process most of the SQL processing for applications.

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

86. Under what context is pushdown analysis utilized?

Use with a federated database.

Use with the standard database.

Use with a large database.

Use with an encrypted database.

Answer

Correct Answer: Use with a federated database.

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

87. After entering a query, what is the first step the SQL compiler takes?

Parsing the query.

Generate remote SQL.

Rewrite the query.

Check its semantics.

Answer

Correct Answer: Parsing the query.

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

88. Which of the following must be set to restrict clients from being able to discover any DB2 instances on a server?

DB2 Administration Server configuration parameter DISCOVER to DISABLE

DISCOVER_DB parameter to DISABLE

DISCOVER_INST parameter to DISABLE on a DB2 instance

DB2 Administration Server configuration parameter SEARCH to DISABLE

Answer

Correct Answer: DB2 Administration Server configuration parameter DISCOVER to DISABLE

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

89. In a UNIX-based environment, where would the database manager configuration file be found?

The sqletc subdirectory.

The t-sqllib subdirectory.

The sqldb2 subdirectory.

The sqllib subdirectory.

Answer

Correct Answer: The sqllib subdirectory.

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

90. Why would you need to execute RUNSTATS regularly when using the SQL compiler?

Executing RUNSTATS updates the optimizer so it can compile the data properly.

Executing RUNSTATS optimizes the compiler so the updater can encode SQL statements sequentially.

Executing RUNSTATS optimizes the data in the right order so the compiler can send it to the optimizer.

Executing RUNSTATS provides the most current data which is used by the optimizer to create an effective access plan.

Answer

Correct Answer: Executing RUNSTATS provides the most current data which is used by the optimizer to create an effective access plan.

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

91. I have 5 SQL Select statements connected by a Union/Union All. How many times should I have to specificy union to eliminate duplicate rows?

4 times

Once

3 times

5 times

Answer

Correct Answer: Once

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

92. What is the benefit of prefetching data?

It holds frequently accessed data in memory.

It holds all the data in the memory.

It automatically compiles SQL statements for you.

It automatically writes stored procedures for you.

Answer

Correct Answer: It holds frequently accessed data in memory.

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

93. What is a DB2 IMAGECOPY

It's a partial backup of a table (depending on certain criteria) which can be used in recovery

It's a full backup of a table which can be used in recovery

It's a full backup of a table which may or may not be able to be used in recovery

It's a partial backup of a table (depending on certain criteria) which cannot be used in recovery

Answer

Correct Answer: It's a full backup of a table which can be used in recovery

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

94. Which of the following DB2 objects allows multiple users to access data in a table with each user only being able to access certain portions of the data?

Table Constraint

Summary Table

View

Dimension Table

Answer

Correct Answer: View

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

95. When working with concurrency controls, what happens when the number of locks held on rows and tables is equal to the percentage of the locklist specified by maxlocks?

Row escalation.

Lock escalation.

Lock reduction.

Table escalation.

Answer

Correct Answer: Lock escalation.

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

96. Why is the deadlock detector an important part of DB2 architecture?

When a computational stalemate occurs, an external application is necessary to maintain the deadlock.

When a computational stalemate occurs, an external application is necessary to break the deadlock.

It tells the users how safe the system is.

When a computational stalemate occurs, an external application is necessary to shut down the system.

Answer

Correct Answer: When a computational stalemate occurs, an external application is necessary to break the deadlock.

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

97. Given the code: EXEC SQL DECLARE cursor1 CURSOR FOR SELECT name, age, b_date FROM person; EXEC SQL OPEN cursor1; Under which of the following situations will the above cursor be implicitly closed?

When there are no rows in the result set

When a COMMIT statement is issued

When a CLOSE statement is issued

When an OPEN Statement is issued

Answer

Correct Answer: When a COMMIT statement is issued

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

98. Why is a primary key needed on a table?

To support table check constraints

To provide free space on each index page for new data

To support duplicate values

To ensure referential integrity between tables

Answer

Correct Answer: To ensure referential integrity between tables

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

99. Is the autorestart parameter configurable on-line?

Yes, but it must be off-line first.

Yes.

Yes, but it must be turned off first.

Answer

Correct Answer: Yes.

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

100. Which of the following is a parameter that would specify the maximum size of a memory segment?

dbheapdb

bdheap

heapdb

dbheap

Answer

Correct Answer: dbheap

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

101. When is it best to tune your system?

Quarterly.

When the system is down.

When you have identified the constraints needed to be relieved.

Every day.

Answer

Correct Answer: When you have identified the constraints needed to be relieved.

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

102. REORG is a function that organizes data on physical storage to recluster rows, positioning overflowed rows to reclaim space and to free up space. When is it used?

After segments of a tablespaces have become fragmented

After heavy updates and inserts

(All of these)

After deletes

Answer

Correct Answer: (All of these)

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

103. What is an example of an instance when the database manager would allocate memory?

When the database is idle.

When an application connects to the database.

When the database is being compiled.

When hardware is being installed.

Answer

Correct Answer: When an application connects to the database.

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

104. If you wish to update your statistics after a change in your database, what utility can be executed?

GOSTATS

RUNSTATS

NEWSTATS

UPSTATS

Answer

Correct Answer: RUNSTATS

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

105. What is an example of an environmental consideration in DB2 administration?

Federated databases being affected by the user.

Federated databases being detected.

Federated databases being shut down.

Federated databases being affected by server options.

Answer

Correct Answer: Federated databases being affected by server options.

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

106. Which of the following are fields from SQLCA

(All of these)

SQLCODE

SQLERRD

SQLERRM

Answer

Correct Answer: (All of these)

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

107. When would phantom read phenomenon occur?

When another application is running concurrently with DB2.

When another application reads data that is entered.

When another application inserts new data or updates existing data that would satisfy your application's query.

When the system starts producing data without user imput.

Answer

Correct Answer: When another application inserts new data or updates existing data that would satisfy your application's query.

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

108. On the client side, what is linked with the DB2 Universal Database client library?

The network.

Local or remote applications, or both.

Remote applications only.

Local applications only.

Answer

Correct Answer: Local or remote applications, or both.

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

109. Which of the following is required to use the IMPORT utility to import data into a table?

LOAD authority on the table

ALTER privilege on the table

SYSCTRL authority

INSERT privilege on the table

Answer

Correct Answer: INSERT privilege on the table

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

110. Why would you want to specify an optimization class when compiling an SQL query?

So the organizer chooses the fastest access plan for that query.

So the optimizer chooses the most efficient access plan for that query.

So the optimizer chooses the slowest access plan for that query.

So that Optimus Prime chooses the slowest access plan for that query.

Answer

Correct Answer: So the optimizer chooses the most efficient access plan for that query.

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

111. When writing a query with a primary key, how would the DISTINCT clause be applied?

It would be applied to primary keys.

It would be applied to locked threads.

It would be applied to redundant SQL statements.

It would not be applied. The DISTINCT clause is redundant.

Answer

Correct Answer: It would not be applied. The DISTINCT clause is redundant.

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

112. Which two of the following types of storage management methods are supported by DB2 OLAP Server?

Relational

Both Hierarchical and Network

Hierarchical

Multi Dimensional

Answer

Correct Answer: Both Hierarchical and Network

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

113. A DBA wishes to audit all access to the non-audited table OWNER.Smartemp. Assuming no audit traces are started, which of the following steps are needed to audit access to this table?

- -START TRACE AUDIT CLASS (4,5) and ALTER TABLE OWNER.Smartemp DATA

- -START TRACE AUDIT CLASS (5)

- -START TRACE AUDIT CLASS (4,5) and ALTER TABLE OWNER.Smartemp AUDIT ALL

- -START TRACE AUDIT CLASS (4,5)

Answer

Correct Answer: - -START TRACE AUDIT CLASS (4,5) and ALTER TABLE OWNER.Smartemp AUDIT ALL

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

114. What object types is the lock mode S (Share) applicable to?

Rows, blocks, and tables.

Rows, blocks, and tuples.

Rows, blocks, and blips.

Rows, blocks, and units.

Answer

Correct Answer: Rows, blocks, and tables.

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

115. What is a federated database?

A federated database is a system including a DB2 DBMS (federated database) and one or more data sources.

A federated database is a database that exists on a cloud network.

A federated database is a database that uses a Java wrapper.

A federated database is a database that is totally isolated.

Answer

Correct Answer: A federated database is a system including a DB2 DBMS (federated database) and one or more data sources.

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

DB2 Administration MCQs | Topic-wise

MCQ on Basic DB2 Administration

General DB2 Administration MCQs

DB2 Administration Subjects

More Resources

DB2 Administration MCQ

DB2 Administration Skill Assessment

DB2 Administration PDF

Related MCQs

DB2 Programming MCQs

Delphi MCQs

DHTML MCQs

Django MCQs

Docker MCQs