Elance DB2 Administrator Test Answers 2015

·


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


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 TABLE staff
DROP * FROM staff
DELETE FROM staff
DELETE * FROM staff


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 (5)
- -START TRACE AUDIT CLASS (4,5)
- -START TRACE AUDIT CLASS (4,5) and ALTER TABLE OWNER.Smartemp AUDIT ALL
- -START TRACE AUDIT CLASS (4,5) and ALTER TABLE OWNER.Smartemp DATA


Which two of the following types of storage management methods are supported by DB2 OLAP Server?
Multi Dimensional
Relational
Both Hierarchical and Network
Hierarchical


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.


What is an example of an environmental consideration in DB2 administration?
Federated databases being detected.
Federated databases being affected by server options.
Federated databases being shut down.
Federated databases being affected by the user.


Which of the following are fields from SQLCA
(All of these)
SQLCODE
SQLERRD
SQLERRM


When would phantom read phenomenon occur?
When another application reads data that is entered.
When the system starts producing data without user imput.
When another application inserts new data or updates existing data that would satisfy your application's query.
When another application is running concurrently with DB2.


If you wish to update your statistics after a change in your database, what utility can be executed?
UPSTATS
NEWSTATS
RUNSTATS
GOSTATS


Which of the following is required to use the IMPORT utility to import data into a table?
ALTER privilege on the table
INSERT privilege on the table
LOAD authority on the table
SYSCTRL authority


What is the primary focus of performance tuning, and how should performance tuning should take place?
Speed, and daily
Efficiency, and incrementally.
Performance, and yearly.
Security, and hourly.


Which of the following is a parameter that would specify the maximum size of a memory segment?
dbheapdb
bdheap
heapdb
dbheap


What is an example of an instance when the database manager would allocate memory?
When the database is being compiled.
When the database is idle.
When hardware is being installed.
When an application connects to the database.


Why would you want to specify an optimization class when compiling an SQL 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 the organizer chooses the fastest access plan for that query.
So that Optimus Prime chooses the slowest access plan for that query.


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.
Table escalation.
Lock reduction.


When is it best to tune your system?
Every day.
When the system is down.
Quarterly.
When you have identified the constraints needed to be relieved.


What is the benefit of prefetching data?
It holds all the data in the memory.
It holds frequently accessed data in memory.
It automatically compiles SQL statements for you.
It automatically writes stored procedures for you.


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?
3 times
5 times
Once
4 times


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 deletes
(All of these)
After segments of a tablespaces have become fragmented
After heavy updates and inserts


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 a COMMIT statement is issued
When there are no rows in the result set
When an OPEN Statement is issued
When a CLOSE statement is issued


Why is the deadlock detector an important part of DB2 architecture?
When a computational stalemate occurs, an external application is necessary to shut down the system.
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.


What is a DB2 IMAGECOPY
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 can be used in recovery
It's a partial backup of a table (depending on certain criteria) which cannot be used in recovery


Is the autorestart parameter configurable on-line?
Yes, but it must be turned off first.
Yes.
Yes, but it must be off-line first.


Why is a primary key needed on a table?
To ensure referential integrity between tables
To support duplicate values
To support table check constraints
To provide free space on each index page for new data


After entering a query, what is the first step the SQL compiler takes?
Check its semantics.
Generate remote SQL.
Parsing the query.
Rewrite the query.


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?
View
Summary Table
Table Constraint
Dimension Table


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


What are EDUs responsible for?
EDUs act as a compiler to use SQL.
EDUs process SQL.
EDUs process the security functions to use SQL.
EDUs process most of the SQL processing for applications.


What calculation can you use to estimate your overhead cost?
Average seek time in minutes + (0.5 * relational latency)
Average seek time in minutes + (0.5 * rotational latency)
Average seek time in miliseconds + (0.5 * rotational latency)
Total seek time in miliseconds + (0.5 * rotational latency)


Under what context is pushdown analysis utilized?
Use with the standard database.
Use with an encrypted database.
Use with a federated database.
Use with a large database.


When writing a query with a primary key, how would the DISTINCT clause be applied?
It would not be applied. The DISTINCT clause is redundant.
It would be applied to locked threads.
It would be applied to primary keys.
It would be applied to redundant SQL statements.


At which of the following times is the access control authorization routine (DSNX@XAC) invoked?
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


In a UNIX-based environment, where would the database manager configuration file be found?
The sqldb2 subdirectory.
The sqllib subdirectory.
The sqletc subdirectory.
The t-sqllib subdirectory.


Why would you need to execute RUNSTATS regularly when using the SQL compiler?
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.


Which two of the following identify which users have SYSCTRL authority?
The database manager configuration
The database configuration
D&E
The operating system security


Is the parameter, “database_memory” configured automatically?
Yes.
Only partially. The rest of it has to be partitioned correctly.
Yes, however the settings need to be configured manually.
Yes, however part of it is left to manual configuration for security reasons.


What does it mean if the the null indicator = -2
The field has spaces
The field value is truncated
The field has blanks
The field is null


Which of the following can be changed with an ALTER statement
Constraint
Tablespace
Index


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 current SQLID (If set)
The schema name
The owner keyword
The role


What condition would lead to distribution statistics not being collected?
Unique data values.
Equal data values.
Consecutive data values.
Excessive data values.


To prepare an embedded SQL program for use with a host-language compiler, which of the following database components is required?
Binder
Stored Procedure Builder
Precompiler
Application Development Center


What is the parameter to configure the default database path?
dftdbpath
dftdb
dbpath
dftdbpdb


Which of the following tools can be used to edit related backup tasks created in the Task Center
Journal
Development Center
Command Center
Operations Center


Which of the following actions will occur when issuing the command FORCE APPLICATION ALL?
Uncommitted units of work are rolled back
No new database connections are allowed
Disconnect warning messages are sent to connected users
Uncommitted units of work are committed


What two types of configuration files does DB2 use?
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.
Database manager configuration files, and database configuration files for the database itself.


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?
HOST
DRDA
DCS
CLIENT


What object types is the lock mode S (Share) applicable to?
Rows, blocks, and tuples.
Rows, blocks, and units.
Rows, blocks, and tables.
Rows, blocks, and blips.


What should be considered when specifying an optimization level?
A query's use of logical or dynamic SQL.
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.


What is one way that a query might be rewritten?
Operation cloning.
Operation compiling.
Operation churning.
Operation merging.


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?
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>)
DROP Package (<collid>.<name of package>.<version id>)


What is intra-partition parallelism?
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.
A technique that utilizes multiple subagents to scan an index, or table.
A technique that partitions multiple subagents to format an index, or table.


Which of the following is an important step in developing a performance-improvement process?
Make one adjustment at a time.
Make all adjustments in step.
Don't adjust the system's performance.
Plan all of your adjustments and do them in one step.


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


What is a scenario in which you would be using a single buffer pool?
Your system is locked.
You are working on a test system.
The system has errors.
You working on a broken system.


What type of parameter is “dbheap”?
System.
Configuration.
Database.
Network.


Which of the following kinds of table spaces allows LOBs to use the filesystem cache?
A DMS table space created with raw devices
A user temporary table space
A system temporary table space
An SMS table space 


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 ON STAT COLUMNS
ONLY FROM STAT COLUMNS


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?
Free up disk space on path1
Add a container to Smt1 
Increase the extent size of the table space
Extend the container


What type of performance impact does the configuration parameter, “blk_log_dsk_ful” have on the system?
None.
It shuts the system down completely because it can't document errors.
It slows down the system because the log file is full.
It speeds up the system because it no longer logs data.


Where would the optimizer gather information to estimate the amount of prefetching for a tablespace?
All of the columns in SYSCAT.TABLESPACES.
The CATSIZE and EXTENTSIZE columns in CAT.TABLESPACES.
The PRECAT and EXTENTIZER columns in SYSCAT.TABLESPACES.
The PREFETCHSIZE and EXTENTSIZE columns in SYSCAT.TABLESPACES.


What table space characteristic can affect how your compiler operates?
Operator characteristics.
Compiler characteristics.
DB2 characteristics.
Container characteristics.


Which of the following can be done by a user who is granted the CONTROL privilege on an INDEX?
Drop the index
Alter the index
Create an index extension on the index
Add columns to the index


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


Which of the following explicit system privileges allows the user to create new plans and packages without being able to also execute them?
CREATEDBA
BINDAGENT
CREATEIN
BINDADD


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 that has no data will be reduced in size 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.
The total size of table space Smart1 will be reduced to 100 MB.


Which of the following is not a statistic collected during RUNSTATS
Percent of rows in clustering sequence
None of the above
No of rows in the table
No of distinct values of indexed column


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 Application Development Client
DB2 Runtime Client
DB2 Personal Edition


What parameter and option must be set before using a DB2 DBMS?
DB2, YES.
Federated, YES.
Federated, NO.
DBMS, YES.


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
3
1
2
0


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 NONE
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.TSO to NONE


What does it mean if the the null indicator = -1
The field is truncated
The field has spaces
The field is null
The field has blanks


What is a technique to access non-contiguous data pages more efficiently?
List sequential parameters.
List sequential prefetch.
List all prefetch.
Lock sequential prefetch.


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 NOW
OPTIMIZE
OPTIMIZE ON


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
1024
256
128


What does it mean if the the null indicator = 0
The field is null
The field is truncated
The field has blanks
The field is not null


What technique can be used to reduce the number of calculations in a query?
Compiled aggregation.
Shared aggregation.
Compiled pairing.
Paired aggregation.


Which of the following DSNZPARMs is necessary to influence access path selection for certain queries?
STDSQL
PARTKEYU
OPTHINTS
DESCSTAT


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 all columns from the subtables STUDENT and EMP
Deleting all rows from the person hierarchy
Dropping the PERSON table
Dropping the table hierarchy called PERSON


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:
A unique index cannot include an existing primary key.
Reverse scans are not supported on multi-column indexes.
LOB columns cannot be used in an index.
INCLUDE columns are not supported in indexes that support REVERSE SCANS.


Which of the following allows index data to be stored on separate devices from table data?
DMS table spaces
The table space containers defined on different devices
SMS table spaces
TMS table spaces

Subscribe to: Posts (Atom)