Top Db2 Administrator Questions
1. What is a federated database?
A. A federated database is a system including a DB2 DBMS (federated database) and one or more data sources.
B. A federated database is a database that uses a Java wrapper.
C. A federated database is a database that is totally isolated.
D. A federated database is a database that exists on a cloud network.
Ans: A
2. 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?
A. DROP TABLE staff
B. DROP * FROM staff
C. DELETE FROM staff
D. DELETE * FROM staff
Ans: C
3. 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?
A. -START TRACE AUDIT CLASS (5)
B. -START TRACE AUDIT CLASS (4,5)
C. -START TRACE AUDIT CLASS (4,5) and ALTER TABLE OWNER.Smartemp AUDIT ALL
D. -START TRACE AUDIT CLASS (4,5) and ALTER TABLE OWNER.Smartemp DATA
Ans: C
4. Which two of the following types of storage management methods are supported by DB2 OLAP Server?
A. Multi Dimensional
B. Relational
C. Both Hierarchical and Network
D. Hierarchical
Ans: C
5. On the client side, what is linked with the DB2 Universal Database client library?
A. The network.
B. Local or remote applications, or both.
C. Remote applications only.
D. Local applications only.
Ans: B
6. What is an example of an environmental consideration in DB2 administration?
A. Federated databases being detected.
B. Federated databases being affected by server options.
C. Federated databases being shut down.
D. Federated databases being affected by the user.
Ans: B
7. Which of the following are fields from SQLCA
A. (All of these)
B. SQLCODE
C. SQLERRD
D. SQLERRM
Ans: A
8. When would phantom read phenomenon occur?
A. When another application reads data that is entered.
B. When the system starts producing data without user imput.
C. When another application inserts new data or updates existing data that would satisfy your application’s query.
D. When another application is running concurrently with DB2.
Ans: C
9. If you wish to update your statistics after a change in your database, what utility can be executed?
A. UPSTATS
B. NEWSTATS
C. RUNSTATS
D. GOSTATS
Ans: C
10. Which of the following is required to use the IMPORT utility to import data into a table?
A. ALTER privilege on the table
B. INSERT privilege on the table
C. LOAD authority on the table
D. SYSCTRL authority
Ans: B
What is the primary focus of performance tuning, and how should performance tuning should take place?
A. Speed, and daily
B. Efficiency, and incrementally.
C. Performance, and yearly.
D. Security, and hourly.
Ans: B
Which of the following is a parameter that would specify the maximum size of a memory segment?
A. dbheapdb
B. bdheap
C. heapdb
D. dbheap
Ans: D
What is an example of an instance when the database manager would allocate memory?
A. When the database is being compiled.
B. When the database is idle.
C. When hardware is being installed.
D. When an application connects to the database.
Ans: D
Why would you want to specify an optimization class when compiling an SQL query?
A. So the optimizer chooses the most efficient access plan for that query.
B. So the optimizer chooses the slowest access plan for that query.
C. So the organizer chooses the fastest access plan for that query.
D. So that Optimus Prime chooses the slowest access plan for that query.
Ans: A
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?
A. Row escalation.
B. Lock escalation.
C. Table escalation.
D. Lock reduction.
Ans: B
When is it best to tune your system?
A. Every day.
B. When the system is down.
C. Quarterly.
D. When you have identified the constraints needed to be relieved.
Ans: D
What is the benefit of prefetching data?
A. It holds all the data in the
memory.
B. It holds frequently accessed data in memory.
C. It automatically compiles SQL statements for you.
D. It automatically writes stored procedures for you.
Ans: B
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?
A. 3 times
B. 5 times
C. Once
D. 4 times
Ans: C
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?
A. After deletes
B. (All of these)
C. After segments of a tablespaces have become fragmented
D. After heavy updates and inserts
Ans: B
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?
A. When a COMMIT statement is issued
B. When there are no rows in the result set
C. When an OPEN Statement is issued
D. When a CLOSE statement is issued
Ans: A
Why is the deadlock detector an important part of DB2 architecture?
A. When a computational stalemate occurs, an external application is necessary to shut down the system.
B. When a computational stalemate occurs, an external application is necessary to maintain the deadlock.
C. When a computational stalemate occurs, an external application is necessary to break the deadlock.
D. It tells the users how safe the system is.
Ans: C
What is a DB2 IMAGECOPY
A. It’s a full backup of a table which can be used in recovery
B. It’s a full backup of a table which may or may not be able to be used in recovery
C. It’s a partial backup of a table (depending on certain criteria) which can be used in recovery
D. It’s a partial backup of a table (depending on certain criteria) which cannot be used in recovery
Ans: A
Is the autorestart parameter configurable on-line?
A. Yes, but it must be turned off first.
B. Yes.
C. Yes, but it must be off-line first.
Ans: B
Why is a primary key needed on a table?
A. To ensure referential integrity between tables
B. To support duplicate values
C. To support table check constraints
D. To provide free space on each index page for new data
Ans: A
After entering a query, what is the first step the SQL compiler takes?
A. Check its semantics.
B. Generate remote SQL.
C. Parsing the query.
D. Rewrite the query.
Ans: C
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?
A. View
B. Summary Table
C. Table Constraint
D. Dimension Table
Ans: A
Which of the following must be set to restrict clients from being able to discover any DB2 instances on a server?
A. DB2 Administration Server configuration parameter SEARCH to DISABLE
B. DISCOVER_DB parameter to DISABLE
C. DISCOVER_INST parameter to DISABLE on a DB2 instance
D. DB2 Administration Server configuration parameter DISCOVER to DISABLE
Ans: D
What are EDUs responsible for?
A. EDUs act as a compiler to use SQL.
B. EDUs process SQL.
C. EDUs process the security functions to use SQL.
D. EDUs process most of the SQL processing for applications.
Ans: D
What calculation can you use to estimate your overhead cost?
A. Average seek time in minutes + (0.5 * relational latency)
B. Average seek time in minutes + (0.5 * rotational latency)
C. Average seek time in miliseconds + (0.5 * rotational latency)
D. Total seek time in miliseconds + (0.5 * rotational latency)
Ans: C
Under what context is pushdown analysis utilized?
A. Use with the standard database.
B. Use with an encrypted database.
C. Use with a federated database.
D. Use with a large database.
Ans: C
When writing a query with a primary key, how would the DISTINCT clause be applied?
A. It would not be applied. The DISTINCT clause is redundant.
B. It would be applied to locked threads.
C. It would be applied to primary keys.
D. It would be applied to redundant SQL statements.
Ans: A
At which of the following times is the access control authorization routine (DSNX@XAC) invoked?
A. At DB2 startup
B. When executing a DB2 GRANT statement
C. When DB2 has cached authorization information
D. During any authorization check if NO was specified in the USE PROTECTION field of the DSNTIPP panel
Ans: A
In a UNIX-based environment, where would the database manager configuration file be found?
A. The sqldb2 subdirectory.
B. The sqllib subdirectory.
C. The sqletc subdirectory.
D. The t-sqllib subdirectory.
Ans: B
Why would you need to execute RUNSTATS regularly when using the SQL compiler?
A. Executing RUNSTATS provides the most current data which is used by the optimizer to create an effective access plan.
B. Executing RUNSTATS optimizes the data in the right order so the compiler can send it to the optimizer.
C. Executing RUNSTATS optimizes the compiler so the updater can encode SQL statements sequentially.
D. Executing RUNSTATS updates the optimizer so it can compile the data properly.
Ans: A
Which two of the following identify which users have SYSCTRL authority?
A. The database manager configuration
B. The database configuration
C. D&E
D. The operating system security
Ans: C
Is the parameter, “database_memory” configured automatically?
A. Yes.
B. Only partially. The rest of it has to be partitioned correctly.
C. Yes, however the settings need to be configured manually.
D. Yes, however part of it is left to manual configuration for security reasons.
Ans: A
What does it mean if the the null indicator = -2
A. The field has spaces
B. The field value is truncated
C. The field has blanks
D. The field is null
Ans: B
Which of the following can be changed with an ALTER statement
A. Constraint
B. Tablespace
C. Index
Ans: B
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?
A. The current SQLID (If set)
B. The schema name
C. The owner keyword
D. The role
Ans: D
What condition would lead to distribution statistics not being collected?
A. Unique data values.
B. Equal data values.
C. Consecutive data values.
D. Excessive data values.
Ans: A
To prepare an embedded SQL program for use with a host-language compiler, which of the following database components is required?
A. Binder
B. Stored Procedure Builder
C. Precompiler
D. Application Development Center
Ans: C
What is the parameter to configure the default database path?
A. dftdbpath
B. dftdb
C. dbpath
D. dftdbpdb
Ans: A
Which of the following tools can be used to edit related backup tasks created in the Task Center
A. Journal
B. Development Center
C. Command Center
D. Operations Center
Ans: C
Which of the following actions will occur when issuing the command FORCE APPLICATION ALL?
A. Uncommitted units of work are rolled back
B. No new database connections are allowed
C. Disconnect warning messages are sent to connected users
D. Uncommitted units of work are committed
Ans: A
What two types of configuration files does DB2 use?
A. Database manager compression files, and database compression files for the database itself.
B. Database log files, and database configuration files for the database itself.
C. Database cache configuration files, and database master files for the database itself.
D. Database manager configuration files, and database configuration files for the database itself.
Ans: D
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?
A. HOST
B. DRDA
C. DCS
D. CLIENT
Ans: C
What object types is the lock mode S (Share) applicable to?
A. Rows, blocks, and tuples.
B. Rows, blocks, and units.
C. Rows, blocks, and tables.
D. Rows, blocks, and blips.
Ans: C
What should be considered when specifying an optimization level?
A. A query’s use of logical or dynamic SQL.
B. A query’s use of active or passive SQL.
C. A query’s use of static or dynamic SQL.
D. A query’s use of PL/SQL or T-SQL.
Ans: C
What is one way that a query might be rewritten?
A. Operation cloning.
B. Operation compiling.
C. Operation churning.
D. Operation merging.
Ans: D
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?
A. FREE Package (..)
B. SPUFI or QMF with the DROP statement
C. DROP PLAN () PKLIST (..)
D. DROP Package (..)
Ans: A
What is intra-partition parallelism?
A. A technique that utilizes multiple databases to scan an index, or hard disk.
B. A technique that utilizes multiple subagents to scan an index, or table.
C. A technique that utilizes multiple subagents to scan an index, or table.
D. A technique that partitions multiple subagents to format an index, or table.
Ans: B
Which of the following is an important step in developing a performance-improvement process?
A. Make one adjustment at a time.
B. Make all adjustments in step.
C. Don’t adjust the system’s performance.
D. Plan all of your adjustments and do them in one step.
Ans: A
Which of the following is correct about the EXPLAIN statement
A. 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)
B. 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
C. 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
D. 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)
Ans: A
What is a scenario in which you would be using a single buffer pool?
A. Your system is locked.
B. You are working on a test system.
C. The system has errors.
D. You working on a broken system.
Ans: B
What type of parameter is “dbheap”?
A. System.
B. Configuration.
C. Database.
D. Network.
Ans: C
Which of the following kinds of table spaces allows LOBs to use the filesystem cache?
A. A DMS table space created with raw devices
B. A user temporary table space
C. A system temporary table space
D. An SMS table space
Ans: D
If your columns are indexed, what clause would you run for the RUNSTATS command to collect statistics?
A. ONLY ON KEY COLUMNS
B. ONLY ON KEY STATS
C. ONLY ON STAT COLUMNS
D. ONLY FROM STAT COLUMNS
Ans: A
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?
A. Free up disk space on path1
B. Add a container to Smt1
C. Increase the extent size of the table space
D. Extend the container
Ans: A
What type of performance impact does the configuration parameter, “blk_log_dsk_ful” have on the system?
A. None.
B. It shuts the system down completely because it can’t document errors.
C. It slows down the system because the log file is full.
D. It speeds up the system because it no longer logs data.
Ans: A
Where would the optimizer gather information to estimate the amount of prefetching for a tablespace?
A. All of the columns in SYSCAT.TABLESPACES.
B. The CATSIZE and EXTENTSIZE columns in CAT.TABLESPACES.
C. The PRECAT and EXTENTIZER columns in SYSCAT.TABLESPACES.
D. The PREFETCHSIZE and EXTENTSIZE columns in SYSCAT.TABLESPACES.
Ans: D
What table space characteristic can affect how your compiler operates?
A. Operator characteristics.
B. Compiler characteristics.
C. DB2 characteristics.
D. Container characteristics.
Ans: D
Which of the following can be done by a user who is granted the CONTROL privilege on an INDEX?
A. Drop the index
B. Alter the index
C. Create an index extension on the index
D. Add columns to the index
Ans: A
Which of the following RACF profiles would be used to allow IMS to access DB2 DSN1?
A. DSNR.IMS
B. DSN1.SASS
C. DSN1.MASS
D. DSN1.IMS
Ans: C
Which of the following explicit system privileges allows the user to create new plans and packages without being able to also execute them?
A. CREATEDBA
B. BINDAGENT
C. CREATEIN
D. BINDADD
Ans: B
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?
A. Each container in table space Smart1 that has no data will be reduced in size to 100 MB.
B. Each container in table space Smart1 will be reduced in size by 100 MB.
C. Each container in table space Smart1 will be reduced in size to 100 MB.
D. The total size of table space Smart1 will be reduced to 100 MB.
Ans: B
Which of the following is not a statistic collected during RUNSTATS
A. Percent of rows in clustering sequence
B. None of the above
C. No of rows in the table
D. No of distinct values of indexed column
Ans: B
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?
A. DB2 Administration Client
B. DB2 Application Development Client
C. DB2 Runtime Client
D. DB2 Personal Edition
Ans: C
What parameter and option must be set before using a DB2 DBMS?
A. DB2, YES.
B. Federated, YES.
C. Federated, NO.
D. DBMS, YES.
Ans: B