DB2 Administration 2015

1. What is a federated database?

Answers:

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

• A federated database is a database that is totally isolated.

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

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

2. What type of performance impact does the configuration parameter, “blk_log_dsk_ful” have on the system?

Answers:

• It shuts the system down completely because it can't document errors.

• It speeds up the system because it no longer logs data.

• None.

• It slows down the system because the log file is full.

3. Under what context is pushdown analysis utilized?

Answers:

• Use with a federated database.

• Use with the standard database.

• Use with an encrypted database.

• Use with a large database.

4. Is the autorestart parameter configurable on-line?

Answers:

• Yes.

• Yes, but it must be turned off first.

• Yes, but it must be off-line first.

5. When would phantom read phenomenon occur?

Answers:

• When another application is running concurrently with DB2.

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

• When another application reads data that is entered.

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

Answers:

• After deletes

• (All of these)

• After heavy updates and inserts

• After segments of a tablespaces have become fragmented

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

Answers:

• dbheapdb

• heapdb

• dbheap

• bdheap

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

Answers:

• RUNSTATS

• NEWSTATS

• UPSTATS

• GOSTATS

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

Answers:

• Table escalation.

• Lock reduction.

• Lock escalation.

• Row escalation.

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

Answers:

• Local applications only.

• The network.

• Remote applications only.

• Local or remote applications, or both.

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

Answers:

• Federated databases being shut down.

• Federated databases being affected by server options.

• Federated databases being affected by the user.

• Federated databases being detected.

12. Which of the following are fields from SQLCA

Answers:

• SQLERRM

• SQLCODE

• (All of these)

• SQLERRD

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

Answers:

• ALTER privilege on the table

• INSERT privilege on the table

• SYSCTRL authority

• LOAD authority on the table

14. What is the primary focus of performance tuning, and how should performance tuning should take place?

Answers:

• Security, and hourly.

• Speed, and daily

• Performance, and yearly.

• Efficiency, and incrementally.

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

Answers:

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

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

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

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

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

Answers:

• Generate remote SQL.

• Parsing the query.

• Check its semantics.

• Rewrite the query.

17. What is a DB2 IMAGECOPY

Answers:

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

• It's a partial backup of a table (depending on certain criteria) which cannot 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

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

Answers:

• When hardware is being installed.

• When the database is idle.

• When the database is being compiled.

• When an application connects to the database.

19. What is the benefit of prefetching data?

Answers:

• It automatically compiles SQL statements for you.

• It holds all the data in the memory.

• It holds frequently accessed data in memory.

• It automatically writes stored procedures for you.

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

Answers:

• When there are no rows in the result set

• When a CLOSE statement is issued

• When an OPEN Statement is issued

• When a COMMIT statement is issued

21. When is it best to tune your system?

Answers:

• When you have identified the constraints needed to be relieved.

• Quarterly.

• When the system is down.

• Every day.

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

Answers:

• 4 times

• 3 times

• Once

• 5 times

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

Answers:

• At DB2 startup

• When executing a DB2 GRANT statement

• When DB2 has cached authorization information

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

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

Answers:

• Multi Dimensional

• Hierarchical

• Relational

• Both Hierarchical and Network

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

Answers:

• To provide free space on each index page for new data

• To support duplicate values

• To support table check constraints

• To ensure referential integrity between tables

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

Answers:

• Dimension Table

• View

• Table Constraint

• Summary Table

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

Answers:

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

• It tells the users how safe the system is.

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

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

28. What are EDUs responsible for?

Answers:

• EDUs process the security functions to use SQL.

• EDUs process most of the SQL processing for applications.

• EDUs act as a compiler to use SQL.

• EDUs process SQL.

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

Answers:

• - -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)

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

Answers:

• DB2 Administration Server configuration parameter SEARCH to DISABLE

• DISCOVER_DB parameter to DISABLE

• DISCOVER_INST parameter to DISABLE on a DB2 instance

• DB2 Administration Server configuration parameter DISCOVER to DISABLE

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

Answers:

• The database configuration

• The database manager configuration

• D&E

• The operating system security

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

Answers:

• The owner keyword

• The schema name

• The current SQLID (If set)

• The role

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

Answers:

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

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

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

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

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

Answers:

• It would be applied to locked threads.

• It would be applied to primary keys.

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

• It would be applied to redundant SQL statements.

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

Answers:

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

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

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

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

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

Answers:

• The sqllib subdirectory.

• The sqldb2 subdirectory.

• The sqletc subdirectory.

• The t-sqllib subdirectory.

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

Answers:

• Yes, however the settings need to be configured manually.

• Yes.

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

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

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

Answers:

• The field is null

• The field has spaces

• The field value is truncated

• The field has blanks

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

Answers:

• Tablespace

• Index

• Constraint

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

Answers:

• Consecutive data values.

• Equal data values.

• Excessive data values.

• Unique data values.

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

Answers:

• Operation merging.

• Operation churning.

• Operation compiling.

• Operation cloning.

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

Answers:

• Operations Center

• Journal

• Command Center

• Development Center

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

Answers:

• dftdbpdb

• dftdbpath

• dftdb

• dbpath

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

Answers:

• Precompiler

• Binder

• Stored Procedure Builder

• Application Development Center

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

Answers:

• A query's use of logical or dynamic 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 active or passive SQL.

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

Answers:

• DELETE * FROM staff

• DROP TABLE staff

• DROP * FROM staff

• DELETE FROM staff

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

Answers:

• A user temporary table space

• A system temporary table space

• A DMS table space created with raw devices

• An SMS table space 

48. What is intra-partition parallelism?

Answers:

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

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

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

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

49. What type of parameter is “dbheap”?

Answers:

• Network.

• Database.

• Configuration.

• System.

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

Answers:

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

• Make one adjustment at a time.

• Don't adjust the system's performance.

• Make all adjustments in step.

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

Answers:

• ONLY ON KEY STATS

• ONLY FROM STAT COLUMNS

• ONLY ON KEY COLUMNS

• ONLY ON STAT COLUMNS

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

Answers:

• CREATEDBA

• BINDADD

• BINDAGENT

• CREATEIN

53. Which of the following can be done by a user who is granted the CONTROL privilege on an INDEX?

Answers:

• Alter the index

• Drop the index

• Create an index extension on the index

• Add columns to the index

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

Answers:

• HOST

• DCS

• DRDA

• CLIENT

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

Answers:

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

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

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

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

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

Answers:

• Disconnect warning messages are sent to connected users

• Uncommitted units of work are rolled back

• Uncommitted units of work are committed

• No new database connections are allowed

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

Answers:

• None of the above

• No of rows in the table

• Percent of rows in clustering sequence

• No of distinct values of indexed column

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

Answers:

• 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 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) but not in the BIND step

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

Answers:

• DB2 Application Development Client

• DB2 Runtime Client

• DB2 Administration Client

• DB2 Personal Edition

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

Answers:

• The PREFETCHSIZE and EXTENTSIZE columns in SYSCAT.TABLESPACES.

• The CATSIZE and EXTENTSIZE columns in CAT.TABLESPACES.

• All of the columns in SYSCAT.TABLESPACES.

• The PRECAT and EXTENTIZER columns in SYSCAT.TABLESPACES.

61. Given the following command:  CREATE TABLESPACE Smt1  MANAGED BY SYSTEM USING ( 'path1' )  EXTENTSIZE 16 PREFETCHSIZE 32 In a single partition database, how can the amount of storage available to the table space Smt1 be increased?

Answers:

• Extend the container

• Increase the extent size of the table space

• Free up disk space on path1

• Add a container to Smt1 

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

Answers:

• Federated, NO.

• Federated, YES.

• DB2, YES.

• DBMS, YES.

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

Answers:

• 1

• 0

• 3

• 2

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

Answers:

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

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

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

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

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

Answers:

• The field is null

• The field has spaces

• The field is truncated

• The field has blanks

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

Answers:

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

• SPUFI or QMF with the DROP statement

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

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

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

Answers:

• Rows, blocks, and tables.

• Rows, blocks, and tuples.

• Rows, blocks, and units.

• Rows, blocks, and blips.

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

Answers:

• Container characteristics.

• Operator characteristics.

• DB2 characteristics.

• Compiler characteristics.

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

Answers:

• 1024

• 256

• 128

• 512

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

Answers:

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

• REVOKE DBADM FROM TSOID1

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

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

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

Answers:

• DSN1.SASS

• DSN1.MASS

• DSN1.IMS

• DSNR.IMS

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

Answers:

• The field is truncated

• The field is not null

• The field is null

• The field has blanks

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

Answers:

• PARTKEYU

• STDSQL

• DESCSTAT

• OPTHINTS

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

Answers:

• Paired aggregation.

• Compiled pairing.

• Compiled aggregation.

• Shared aggregation.

75. 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:

Answers:

• LOB columns cannot be used in an index.

• Reverse scans are not supported on multi-column indexes.

• A unique index cannot include an existing primary key.

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

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

Answers:

• You are working on a test system.

• You working on a broken system.

• Your system is locked.

• The system has errors.

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

Answers:

• Dropping the table hierarchy called PERSON

• Dropping the PERSON table

• Deleting all rows from the person hierarchy

• Dropping all columns from the subtables STUDENT and EMP

78. What is a technique to access non-contiguous data pages more efficiently?

Answers:

• List sequential prefetch.

• List all prefetch.

• List sequential parameters.

• Lock sequential prefetch.

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

Answers:

• SMS table spaces

• TMS table spaces

• The table space containers defined on different devices

• DMS table spaces

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

Answers:

• OPTIMIZE NOW

• OPTIMIZE

• OPTIMIZE FOR

• OPTIMIZE ON