January 15, 2008
Here are more questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press, to help you prepare for the certification test. The questions are reprinted with permission. Check back each week for more, and try past questions here.
QUESTION 11
If the following SQL statement is executed:
UPDATE DB CFG FOR sample USING BLK_LOG_DSK_FUL YES
What will happen?
A. The SAMPLE database will be configured to use infinite logging.
B. The SAMPLE database will not automatically allocate additional storage space when the active log directory becomes full.
C. Log files for the SAMPLE database will be backed up automatically whenever a full backup image of the SAMPLE database is made.
D. Transactions running against the SAMPLE database will not be rolled back if they receive a disk full error.
QUESTION 12
User USER1 has the privileges needed to invoke a stored procedure named GEN_RESUME. User USER2 needs to be able to call the procedure--USER1 and all members of the group PUBLIC should no longer be allowed to call the procedure. Which of the following statement(s) can be used to accomplish this?
A. GRANT EXECUTE ON ROUTINE gen_resume TO user2 EXCLUDE user1, PUBLIC
B. GRANT EXECUTE ON PROCEDURE gen_resume TO user2;
REVOKE EXECUTE ON PROCEDURE gen_resume FROM user1, PUBLIC;
C. GRANT CALL ON ROUTINE gen_resume TO user2 EXCLUDE user1, PUBLIC
D. GRANT CALL ON PROCEDURE gen_resume TO user2;
REVOKE CALL ON PROCEDURE gen_resume FROM user1, PUBLIC;
Answers
QUESTION 11
The correct answer is D. When archival logging is used and archived log files are not moved from the active log directory to another location, the disk where the active log directory resides can quickly become full. By default, when this happens, transactions will receive a disk full error and be rolled back. If the blk_log_dsk_ful database configuration parameter is set to YES, applications will hang (instead of rolling back the current transaction) if the DB2 Database Manager receives a disk full error when it attempts to create a new log file in the active log directory. (This gives you the opportunity to manually move or delete files to make more room available.) The DB2 Database Manager will then attempt to create the log file every five minutes until it succeeds – after each attempt, a message is written to the Ad-ministration Notification Log.
QUESTION 12
The correct answer is B. The syntax used to grant the only stored procedure privilege available is:
GRANT EXECUTE ON [RoutineName] |
[PROCEDURE
TO [Recipient, ...]
The syntax used to revoke the only stored procedure privilege available is:
REVOKE EXECUTE ON [RoutineName |
[PROCEDURE
FROM [Forfeiter, ...]
RESTRICT
where:
RoutineName identifies by name the routine (user-defined function, method, or stored procedure) that the EXECUTE privilege is to be associated with.
TypeName identifies by name the type in which the specified method is found.
SchemaName identifies by name the schema in which all functions, methods, or procedures—including those that may be created in the future—are to have the EXECUTE privilege granted on.
Recipient identifies the name of the user(s) and/or group(s) that are to re-ceive the EXECUTE privilege. The value specified for the Re-cipient parameter can be any combination of the following:
Forfeiter Identifies the name of the user(s) and/or group(s) that are to lose the package privileges specified. The value specified for the For-feiter parameter can be any combination of the following:
Thus, the proper way to grant and revoke stored procedure privileges is by executing the GRANT EXECUTE … and REVOKE EXECUTE … statements.
Trackback Pings
TrackBack URL for this entry:
http://www.ibmdatabasemag.com/blog/main/archives/2008/01/post_6.html
« Windows Vista: Where is db2diag.log location? | Main | SQL Injection and Stored Procedures »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.