Question:* What is a federated database?
Answer: • A federated database is a system including a DB2 DBMS (federated database) and one or more data sources.
Question:* What type of performance impact does the configuration parameter, “blk_log_dsk_ful” have on the system?
Answer: • None.
Question:* Under what context is pushdown analysis utilized?
Answer: • Use with a federated database.
Question:* Is the autorestart parameter configurable on-line?
Answer: • Yes.
Question:* When would phantom read phenomenon occur?
Answer: • When another application inserts new data or updates existing data that would satisfy your application's query.
Question:* 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?
Answer: • (All of these)
Question:* Which of the following is a parameter that would specify the maximum size of a memory segment?
Answer: • dbheap
Question:* If you wish to update your statistics after a change in your database, what utility can be executed?
Answer: • RUNSTATS
Question:* 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?
Answer: • Lock escalation.
Question:* On the client side, what is linked with the DB2 Universal Database client library?
Answer: • Local or remote applications, or both.
Question:* What is an example of an environmental consideration in DB2 administration?
Answer: • Federated databases being affected by server options.
Question:* Which of the following are fields from SQLCA
Answer: • (All of these)
Question:* Which of the following is required to use the IMPORT utility to import data into a table?
Answer: • INSERT privilege on the table
Question:* What is the primary focus of performance tuning, and how should performance tuning should take place?
Answer: • Efficiency, and incrementally.
Question:* Why would you want to specify an optimization class when compiling an SQL query?
Answer: • So the optimizer chooses the most efficient access plan for that query.
Question:* After entering a query, what is the first step the SQL compiler takes?
Answer: • Parsing the query.
Question:* What is a DB2 IMAGECOPY
Answer: • It's a full backup of a table which can be used in recovery
Question:* What is an example of an instance when the database manager would allocate memory?
Answer: • When an application connects to the database.
Question:* What is the benefit of prefetching data?
Answer: • It holds frequently accessed data in memory.
Question:* 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?
Answer: • When a COMMIT statement is issued
Question:* When is it best to tune your system?
Answer: • When you have identified the constraints needed to be relieved.
Question:* 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?
Answer: • Once
Question:* At which of the following times is the access control authorization routine (DSNX@XAC) invoked?
Answer: • At DB2 startup
Question:* Which two of the following types of storage management methods are supported by DB2 OLAP Server?
Answer: • Both Hierarchical and Network
Question:* Why is a primary key needed on a table?
Answer: • To ensure referential integrity between tables
Question:* 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?
Answer: • View
Question:* Why is the deadlock detector an important part of DB2 architecture?
Answer: • When a computational stalemate occurs, an external application is necessary to break the deadlock.
Question:* What are EDUs responsible for?
Answer: • EDUs process most of the SQL processing for applications.
Question:* 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?
Answer: • - -START TRACE AUDIT CLASS (4,5) and ALTER TABLE OWNER.Smartemp AUDIT ALL
Question:* Which of the following must be set to restrict clients from being able to discover any DB2 instances on a server?
Answer: • DB2 Administration Server configuration parameter DISCOVER to DISABLE
Question:* Which two of the following identify which users have SYSCTRL authority?
Answer: • D&E
Question:* 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?
Answer: • The role
Question:* What calculation can you use to estimate your overhead cost?
Answer: • Average seek time in miliseconds + (0.5 * rotational latency)
Question:* When writing a query with a primary key, how would the DISTINCT clause be applied?
Answer: • It would not be applied. The DISTINCT clause is redundant.
Question:* Why would you need to execute RUNSTATS regularly when using the SQL compiler?
Answer: • Executing RUNSTATS provides the most current data which is used by the optimizer to create an effective access plan.
Question:* In a UNIX-based environment, where would the database manager configuration file be found?
Answer: • The sqllib subdirectory.
Question:* Is the parameter, “database_memory” configured automatically?
Answer: • Yes.
Question:* What does it mean if the the null indicator = -2
Answer: • The field value is truncated
Question:* Which of the following can be changed with an ALTER statement
Answer: • Tablespace
Question:* What condition would lead to distribution statistics not being collected?
Answer: • Unique data values.
Question:* What is one way that a query might be rewritten?
Answer: • Operation merging.
Question:* Which of the following tools can be used to edit related backup tasks created in the Task Center
Answer: • Command Center
Question:* What is the parameter to configure the default database path?
Answer: • dftdbpath
Question:* To prepare an embedded SQL program for use with a host-language compiler, which of the following database components is required?
Answer: • Precompiler
Question:* What should be considered when specifying an optimization level?
Answer: • A query's use of static or dynamic SQL.
Question:* 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?
Answer: • DELETE FROM staff
Question:* Which of the following kinds of table spaces allows LOBs to use the filesystem cache?
Answer: • An SMS table space
Question:* What is intra-partition parallelism?
Answer: • A technique that utilizes multiple subagents to scan an index, or table.
Question:* What type of parameter is “dbheap”?
Answer: • Database.
Question:* Which of the following is an important step in developing a performance-improvement process?
Answer: • Make one adjustment at a time.
Question:* If your columns are indexed, what clause would you run for the RUNSTATS command to collect statistics?
Answer: • ONLY ON KEY COLUMNS
Question:* Which of the following explicit system privileges allows the user to create new plans and packages without being able to also execute them?
Answer: • BINDAGENT
Question:* Which of the following can be done by a user who is granted the CONTROL privilege on an INDEX?
Answer: • Drop the index
Question:* 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?
Answer: • DCS
Question:* What two types of configuration files does DB2 use?
Answer: • Database manager configuration files, and database configuration files for the database itself.
Question:* Which of the following actions will occur when issuing the command FORCE APPLICATION ALL?
Answer: • Uncommitted units of work are rolled back
Question:* Which of the following is not a statistic collected during RUNSTATS
Answer: • None of the above
Question:* Which of the following is correct about the EXPLAIN statement
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)
Question:* 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?
Answer: • DB2 Runtime Client
Question:* Where would the optimizer gather information to estimate the amount of prefetching for a tablespace?
Answer: • The PREFETCHSIZE and EXTENTSIZE columns in SYSCAT.TABLESPACES.
Question:* 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?
Answer: • Free up disk space on path1
Question:* What parameter and option must be set before using a DB2 DBMS?
Answer: • Federated, YES.
Question:* 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: • 3
Question:* 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?
Answer: • Each container in table space Smart1 will be reduced in size by 100 MB.
Question:* What does it mean if the the null indicator = -1
Answer: • The field is null
Question:* 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?
Answer: • FREE Package (<collid>.<name of package>.<version id>)
Question:* What object types is the lock mode S (Share) applicable to?
Answer: • Rows, blocks, and tables.
Question:* What table space characteristic can affect how your compiler operates?
Answer: • Container characteristics.
Question:* Given the following statement: CREATE TABLESPACE smt1 MANAGED BY DATABASE USING (FILE 'smt01' 1024K) How many pages will be created for the table space?
Answer: • 256
Question:* 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?
Answer: • Change TSOID1 ‘s access to DSNR resource class DSN1.BATCH to NONE
Question:* Which of the following RACF profiles would be used to allow IMS to access DB2 DSN1?
Answer: • DSN1.MASS
Question:* What does it mean if the the null indicator = 0
Answer: • The field is not null
Question:* Which of the following DSNZPARMs is necessary to influence access path selection for certain queries?
Answer: • OPTHINTS
Question:* What technique can be used to reduce the number of calculations in a query?
Answer: • Shared aggregation.
Question:* 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:
Answer: • LOB columns cannot be used in an index.
Question:* What is a scenario in which you would be using a single buffer pool?
Answer: • You are working on a test system.
Question:* 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?
Answer: • Dropping the table hierarchy called PERSON
Question:* What is a technique to access non-contiguous data pages more efficiently?
Answer: • List sequential prefetch.
Question:* Which of the following allows index data to be stored on separate devices from table data?
Answer: • DMS table spaces
Question:* If you have a poorly clustered index with random synchronous I/Ss, what clause would you use to prevent an impending delay?
Answer: • OPTIMIZE FOR
No comments:
Post a Comment