C2090-541 - DB2 9.7 DBA for Linux UNIX and Windows Exam
Go back to IBM
Which command will reorg all indexes for a table named TABA?
REORG INDEXES ALL FOR TABLE taba
You are asked to create a new database called PRODDB where no privileges will be automatically granted to PUBLIC Which CREATE statement will accomplish this?
CREATE DATABASE proddb RESTRICTIVE
Which feature introduced in DB2 9.7 allows faster roll-in and roll-out of data partitions?
individual indexes for each partition in a partitioned table
Which method may be used to collect deadlock information in DB2 9.7?
LOCKING event monitor
A DBA wants to use Label Based Access Control (LBAC) to protect Individual rows in a table. Which data type must be used to create a column to hold the necessary LBAC security data?
What are three implementations for DB2 Workload Management? (Choose three.)
A database named FINANCE must be available to users 24x7x365. Which command should be used to create a backup image of the FINANCE database without having a significant impact on running applications?
BACKUP DATABASE finance ONLINE TO /mnt/backup UTIL_IMPACT_PRIORITY 15
Which statement is true about using the EXPORT utility with a table that contains XML data?
By default, XML documents are written to a single, separate file, which is stored in the same location as the file that contains the relational data exported.
A database named PAYROLL was backed up on Sunday. On Tuesday, a table space in the PAYROLL database was renamed from TBSP1 to EMPINFO_TBSP. On Thursday, the database was restored from the backup image created on Sunday. Which command must be issued to roll the renamed table space forward to the current date and time?
ROLLFORWARD DATABASE payroll TO END OF LOGS AND STOP TABLESPACE(empinfo_tbsp)
For which two events is it recommended to execute RUNSTATS? (Choose two.)
When row compression has been enabled.
When LOAD with STATISTICS has been run.
What is the purpose of a role?
to group privileges
If this command is executed: db2advis -d sample -g What is the Design Advisor using to perform its analysis?
A set of dynamic SQL statements that have been captured by the snapshot monitor for a database named SAMPLE.
Which two tools can create a compression dictionary? (Choose two.)
What are three system and database level authorizations available with DB2? (Choose three.)
Given the DDL shown below: CREATE TABLE tab1 (coll SMALLINT ); CREATE VIEW v1 AS SELECT coll FROMtabl WHERE coll > 25 ; CREATE VIEW V2 AS SELECT COI1 FROM v1 WITH CASCADED CHECK OPTION CREATE VIEW v3 AS SELECT coll FROM v2 WHERE coll < 100 ; Which statement will fail?
INSERT INTO v3 VALUES (25)
A file named db2advise.in contains the following text: -#SET FREQUENCY 100 SELECT COUNTO FROM employee; SELECT AVG(bonus), AVG(salary) FROM employee GROUP BY workdept ORDER BY workdept; Which command will cause the Design Advisor to make recommendations for a database named SAMPLE, using the information found in this file?
db2advis -d sample -i db2advise.in
A file named db2advise.in contains the text shown below: -#SET FREQUENCY 100 SELECT COUNTO FROM employee; SELECT * FROM employee WHERE lastname='HAAS'; What does the line -#SET FREQUENCY 100 convey when this file is used as input for the Design Advisor?
Executes both SQL statements in the workload file 100 times.
Which method may be used to collect deadlock information in DB2 97?
LOCKING event monitor
BOB and ALICE moved from the DEVELOPER group to the ANALYST group. As the security administrator, which two commands would remove Bob and Alice from the DEVELOPER group? (Choose two.)
REVOKE ROLE developer FROM bob, alice
REVOKE ROLE developer FROM USER bob, USER alice
The SELF_TUNING_MEM database configuration (db cfg) parameter is set to ON. Which three statements will cause the memory tuner to actively tune memory within the database? (Choose three.)
One db cfg parameter is set to AUTOMATIC or the size of at least one buffer pool is set to AUTOMATIC, and the DAT ABAS E_M E M O R Y db cfg parameter is set to a numeric value or to AUTOMATIC.
Any two of LOCKLIST, SHEAPTHRES_SHR, PCKCACHESZ are set to AUTOMATIC.
The SORTHEAP db cfg parameter is set to AUTOMATIC.
When using SQU and JDBC applications to connect to the host DB2 database, which software license must be registered?
When are packages automatically rebound?
When invalid packages are executed.
Which statement is true about unique constraints?
There can be multiple unique constraints per table.
A database named PAYROLL resides on an IBM System Storage DS8000 and a database administrator would like to use Flash Copy to back up the database. Which command can be used to accomplish this?
BACKUP DATABASE payroll USE SNAPSHOT
When using table partitioning, which three operations are valid? (Choose three.)
partition level REORG
For which two purposes would you run the db2look tool? (Choose two.)
To extract the definition of objects in a database and generate DDL statements to recreate them.
To extract the statistics information for a series of objects in a database and generateUPDATE statements to manually update the statistics
Which three authorizations allow you to execute db2pd? (Choose three.)
SYSADM authority level
SYSCTRL authority level
SYSMON authority level
Because of an application abend, a database named PAYROLL needs to be restored to approximately 9:26 am on June 1, 2009. After the database is restored from a backup image, the command shown below is executed: ROLLFORWARD DATABASE payroll TO 2009-06-01-09.26.44.609002 USING LOCAL TIME AND STOP When this command is executed, the error shown below is generated: SQL4970N Roll-forward recovery on database "PAYROLL" cannot reach the specified stop point (end-of-log or point-in-time) because of missing log file(s) on node(s) "0". Which two commands could be used to resolve this error? (Choose two.)
ROLLFORWARD DATABASE payroll TO END OF LOGS AND STOP
ROLLFORWARD DATABASE payroll TO 2009-06-01-09.26.44.609002 USING LOCAL TIMEAND STOP OVERFLOW LOG PATH /mnt/arclog
When using Optim Database Administrator to generate a change script, users are given which two EXPORT utility output formats to preserve their data when they drop a table then create a new table with the data? (Choose two.)
What correctly describes an MDC table rollout cleanup for qualifying DELETE statements after the statement shown below is run? SET CURRENT MDC ROLLOUT MODE IMMEDIATE
MDC rollout optimization is used. The RID indexes are updated immediately during the delete process. The deleted blocks are available for reuse after the transaction commits.
What will collect information about dynamic and static SQL statements?
MON_GET_PKG_CACHE_STMT table function
You are unable to configure a data source using DB2 Discovery. What are three reasons why this is happening? (Choose three.)
A network failure exists between the control center client and the database
The DISCOVER_DB parameter is disabled.
The database does not exist.
The event monitor for transactions has been replaced in DB2 9.7 with which tool?
MON_GET_WORKLOAD table function
A table named CUSTOMER has an XML column named CUSTINFO that contains several XML documents. Which SQL statement will retrieve XML documents from the CUSTOMER table and convert them to character strings?
SELECT XM LS E RIALIZE (c u sti nf o AS VARCHAR(350)) FROM customer
You want to federate communication between two databases. What are two key requirements? (Choose two.)
Enable the server for federation.
Define nicknames for the tables of a federated server.
Tables TABLE_A and TABLE_B in database TESTDB have the same data structure (same columns with the same definitions). You wish to unload the data from the two tables into a single file In PC/IXF format. Which command(s) can be used to do this?
EXPORT TO combined.ixf OF IXF SELECT * FROM table_a UNION ALL SELECT * FROM table_b
Which SQL statement will execute successfully?
CREATE TABLE customer(custid INTEGER NOT NULL PRIMARY KEY,custinfo XML)
Table TAB_A is already populated with a large number or rows, and you wish to insert more rows from a file named data.del. While these row are added, you want users to still have full access to the table in read/Write mode. Which command should you use to do this?
IMPORT FROM data.del OF DEL INSERT INTO tab_a
You are running a LOAD utility and have not provided an exception table. What will happen if duplicates for a unique key get Inserted during the LOAD phase due to duplicates in the input file?
LOAD will eliminate the duplicate rows from the table and give a warning message.
Given a table that is already compressed, if you create a new index on that table, when will the index be compressed?
immediately after running the CREATE INDEX command
Which list of automatic maintenance parameters can have a policy defined to specify their automatic behavior?
AUTO_DB_BACKUP, AUTO_RUNSTATS, AUTO_REORG
How does the DB2 Server identify the list of GSS-API plug-ins it supports?
by using the SRVCON_GSSPLUGIN_LIST database manager configuration parameter
What are two purposes of the db2mtrk command? (Choose two.)
to view application memory usage
to view the amount of database memory allocated to the buffer pools
Given the following CREATE TABLE statement: CREATE TABLE applicant (id INTEGER NOT NULL PRIMARY KEY, lastname VARCHAR(50)); Which SQL statement will execute successfully?
ALTER TABLE applicant ADD COLUMN resume XML CHECK (resume IS VALIDATED)
Which two DB2 commands are used to extract, format, and present diagnostic information? (Choose two.)
What are three default service classes used by Work Load Management? (Choose three.)
Which two SOL statements are explainable statements? (Choose two.)
How do you turn on Self-Tuning Memory Manager (STMM)?
UPDATE DB CFG FOR <database> USING SELF_TUNING_MEM ON
You are running an IMPORT utility and have specified the option COMMITCOUNT AUTOMATIC Which two reasons will cause the IMPORT utility to COMMIT? (Choose two.)
to avoid running out of active log space
to avoid lock escalation from row level to table level
You want to estimate the potential index compression savings for existing indexes on a table What do you use to obtain this information?
ADMIN_GET_INDEX_COMPRESS_INFO table function