000-541 - DB2 9.7 DBA for Linux UNIX and Windows
Go back to IBM
You want to grant user BOB the ability to create tables in table space PLANS and to grant this privilege to others Which command would you use?
GRANT USE OF TABLESPACE plans TO bob WITH GRANT OPTION
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)
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
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.
Which three table features can be used concurrently in the same table? (Choose three.)
Which SQL statement will execute successfully?
CREATE TABLE customer (custid INTEGER NOT NULL PRIMARY KEY, custinfo XML)
What will collect information about dynamic and static SQL statements?
MON_GET_PKG_CACHE_STMT table function
The event monitor for transactions has been replaced in DB2 9.7 with which tool?
MON_GET_WORKLOAD table function
Which group of memory consumers can be adjusted by the Self-Tuning Memory Manager (STMM)?
buffer pools, lock memory, sort memory, package cache
Which DB2 tool is a Java-based, generic XML parser which produces readable text output from the data generated by an event monitor?
Which two plug-in types does the DB2 database system provide? (Choose two.)
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?
Scheduled tasks are executed by the DB2 autonomic computing daemon (db2acd). Every five minutes the daemon checks for new or updated tasks. If a task is scheduled to run every five minutes and for some reason, the task takes seven minutes to complete, the daemon will not execute another instance of the task at the next 5 minute interval. Which minute mark will allow the next task to run?
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
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.
When using table partitioning, which three operations are valid? (Choose three.)
partition level REORG
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 method may be used to collect deadlock information in DB2 9.7?
LOCKING event monitor
Index TAB 1X1 was created as shown below: CREATE INDEX tablxl ON tab1(col_1) Which modification to index TAB 1X1 can be performed with an ALTER INDEX statement?
ALTER INDEX tablxl COMPRESS YES
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.
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
You have a UTIL_IMPACT_LIM of 50 and a UTIL_IMPACT_PRIORITY of 60 for the backup utility What are the maximum amount of system resources that the backup utility can use?
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
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
You are asked to create a new database called PRODDB where no privileges will be dautomatically granted to PUBLIC Which CREATE statement will accomplish this?
CREATE DATABASE proddb RESTRICTIVE
When are packages automatically rebound?
When invalid packages are executed.
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 TIME AND STOP OVERFLOW LOG PATH /mnt/arclog
Which three authorizations allow you to execute db2pd? (Choose three.)
SYSADM authority level
SYSCTRL authority level
SYSMON authority level
Which method may be used to collect deadlock information in DB2 97?
LOCKING event monitor
A database administrator created a new database from an existing full database backup image. If the original database resided on a 64-bit AIX server and the new database was created on a different 64-bit AIX server, which two statements are correct? (Choose two.)
Existing SQL procedures must be dropped and re-created in the new database before they can be used
Incremental backups are not allowed until a non-incremental backup is taken.
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.
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.
Which statement is correct when describing the AUTOCONFIGURE command?
It calculates and displays initial values for buffer pool sizes, the database manager configuration parameters, and database configuration parameters for a single database, with the option of applying these values.
Which statement is correct about informational constraints?
An informational constraint can be used by the SQL compiler to improve the access to data and query performance.
Which three types of work can be defined for a Workload Management service class set? (Choose three.)
Which two tasks can DB2 Workload Management do? (Choose two.)
Limit the number of disruptive database activities that can run concurrently
Stop the execution of activities that exceed defined boundaries.
Which statement is true about unique constraints?
There can be multiple unique constraints per table.
Which administrative authority is needed to run the audit stored procedures and table functions such as the AUDIT_DELIM_EXTRACT stored procedure?
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
A database administrator takes a full backup of the PRODUCTS database every Sunday. During the week, two other backups are taken, one on Tuesday and one on Thursday. The PRODUCTS database is about 800 GB in size, and about 20 percent of the data is modified throughout a week. Assuming that backup times need to be kept to a minimum, which command should be used to produce the Tuesday and Thursday backup images?
BACKUP DATABASE products INCREMENTAL DELTA TO /mnt/backup
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 two SOL statements are explainable statements? (Choose two.)
Which two data types can be in-lined to enhance compression? ( Choose two.)
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
Which two tools can create a compression dictionary? (Choose two.)
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.
Which two can be replicated using HADR? (Choose two.)
Data Definition Language (DDL) statements
You have a job scheduled to run every Saturday; the scheduler is turned off on Friday and then restarted on Monday. The job scheduled for Saturday is now a job that is scheduled in the past. Which configuration parameters will allow your Saturday job to run when the scheduler is restarted?
EXEC_EXP_TASK=YES SCHED ENABLE=ON
What are three system and database level authorizations available with DB2? (Choose three.)
What is the purpose of the XMLPARSE() function?
To produce a well-formed XML document from a character string value.