You are asked to provide a recommendation for the appropriate DB2 edition for a new application. The
application is characterized by long, complex queries that select a subset of columns and often perform
grouping and aggregation. The initial database size is estimated at 5 TB, with growth of about 100 GB per
year. Keep data size on disk to a minimum is a priority?
Which of the following editions would be best meet the described needs?
- A . DB2 Express-C
- B . DB2 Enterprise Server Edition
- C . DB2 Workgroup Server Edition
- D . DB2 Advanced Enterprise Server Edition
D
Explanation:https://www.ibm.com/developerworks/data/library/techarticle/dm-1304whatsnewdb2105/
index.html
Which of the following editions of DB2 include support for Federation with Oracle database?
- A . DB2 Express-C
- B . DB2 Developer Edition
- C . DB2 Enterprise Server Edition
- D . DB2 Workgroup Server Edition
B
Explanation:https://www.ibm.com/developerworks/data/library/techarticle/dm-1311db2compare/index.html
Which three facilities are used to control authorization in DB2?
- A . privileges, db2ssh, LBAC credentials
- B . privileges, authority levels, certificates
- C . db2ssh, trusted contexts, authority levels
- D . privileges, authority levels, LBAC credentials
D
Explanation:https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_9.5.0/
com.ibm.db2.luw.admin.sec.doc/doc/c0006307.html
Which database object can be locked explicitly?
- A . Row
- B . View
- C . Index
- D . Table
D
Explanation:https://www.ibm.com/support/knowledgecenter/hr/SSEPGG_9.7.0/
com.ibm.db2.luw.admin.perf.doc/doc/c0005270.html
What happens when you run the following statement if MYUSER holds no privileges on table MYTABLE?
REVOKE SELECT ON TABLE mytable FROM USER myuser
- A . An error is returned
- B . An entry is mane in SYSCAT.TABAUTH
- C . The REVOKE statement succeeds with no effect
- D . All privileges are granted to user MYUSER on MYTABLE
A user executes the following statements:
СREATE TABLE t1 (c1 INT, c2 INT);
CREATE ALIAS a1 FOR t1;
What will be the result if the user executes the following statement?
DROP ALIAS a1;
- A . The alias A1 will be dropped
- B . The table T1 will be dropped
- C . Both T1 and A1 will be dropped
- D . Nothing, the statement will result in an error
Which constraint can be used to ensure that identical values are never stored in a set of columns in a
table?
- A . NOT NULL
- B . Foreign key
- C . Primary key
- D . Informational constraint
C
Explanation:https://www.ibm.com/developerworks/data/library/techarticle/dm-0401melnyk/index.html
Which of the following is TRUE about a COMMIT statement executed at the end of the T1 unit of work?
- A . A ROLLBACK command executed just after the COMMIT command cancels that COMMIT
- B . Save points defined during transaction T1 are released only if there is a RELEASE SAVEPOINT
command executed after the COMMIT command - C . It finishes the current T1 unit of work and a new one is initiated, however it is possible to ROLLBACK to
the latest SAVEPOINT before COMMIT - D . It finishes the current T1 unit of work and a new one is initiated. All changes made by UPDATE and
DELETE commands during unit of work T1 are committed
D
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/
db2z_sql_commit.html
Which of the following statements about stored procedures is TRUE?
- A . Stored procedures can be written in SQL PL, but not in the PL/SQL dialect
- B . The CREATE SOURCED PROCEDURE statement defines a procedure that is based on another
procedure in a federated database - C . There are three types of stored procedures which can be deployed: SQL procedures, external
procedures, and sourced procedures - D . Two identically-named procedures within a schema are permitted to have exactly the same number of
parameters, but the parameters must have different data types assigned
C
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/
com.ibm.db2.luw.sql.ref.doc/doc/r0023603.html
Which edition of DB2 LUW has been discontinued in v11.1?
- A . DB2 Developer Edition
- B . DB2 Express Server Edition
- C . DB2 Enterprise Server Edition
- D . DB2 Connect Enterprise Server Edition
B
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/
com.ibm.db2.luw.licensing.doc/doc/r0053238.html
Which of the following statements about triggers are TRUE? (Choose two.)
- A . There is a limit of 8 for the maximum number of cascading trigger activations
- B . Triggers can be activated as a result of changes due to referential constraint enforcement
- C . Multiple INSTEAD OF triggers are allowed for each kind of operation on a given subject view
- D . Constraints associated with a subject table are applied between execution of BEFORE and AFTER
triggers - E . The activation of the triggers for a particular table, event and activation time are conducted in
descending order of trigger creation time
CE
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/
com.ibm.db2.luw.admin.dbobj.doc/doc/c0004113.html
Which of the following is TRUE about indexes on column organized tables?
- A . No indexes are allowed
- B . Indexes are only allowed on numeric columns
- C . Indexes are automatically created to enforce unique constraints
- D . An index is only allowed on the synopsis table supporting a column organized table
How can you tell what privileges on a table or view have been granted to a user? (Choose two.)
- A . db2look with the Cx option
- B . db2pd with the Cauth option
- C . Query the SYSCAT.DBAUTH view
- D . Query the SYSCAT.TABLES view
- E . Query the SYSCAT.TABAUTH view
AE
Explanation:https://www.ibm.com/support/knowledgecenter/hr/SSEPGG_9.7.0/
com.ibm.db2.luw.admin.sec.doc/doc/t0005823.html
The following statements are executed:
GRANT CREATETAB, DATAACCESS, ON DATABASE TO ROLE role A;
GRANT CREATETAB, BINDADD ON DATABASE TO ROLE role B;
GRANT ROLE role A to USER db2inst3;
GRANT ROLE role B to USER db2inst3;
Which of the following is TRUE if this statement is executed later?
REVOKE ROLE roleB FROM USER db2inst3;
- A . This statement will succeed and DB2 INST3 will still be able to create in the database
- B . This statement will succeed and DB2 INST3 only retains DATAACCESS privileges on the database
- C . This statement will fail as CREATETAB is granted to two different roles and must be revoked at the
same time - D . This statement will succeed with a warning since CREATETAB cannot be totally revoked. DB2INST3
will retain both roles
Consider the following sequence of events:
Eddie grants SELECT WITH GRANT OPTION privilege to Mike.
Mike grants SELECT privilege to Jeff.
Eddie revokes SELECT privilege from Mike.
What will happen to Jeff’s SELECT privilege?
- A . Jeff’s SELECT privilege will be revoked
- B . Jeff’s SELECT privilege will be retained
- C . Jeff’s SELECT privilege will be retained only if he has ACCESSCTRL authority
- D . Jeff’s SELECT privilege will be retained only if Eddie has ACCESSCTRL authority
C
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/
ids_sqs_0125.htm
Which locking event occurs when two applications lock rows that are needed by the other, such that
neither application can continue executing?
- A . Deadlock
- B . Lock timeout
- C . Lock escalation
- D . Lock conversion
A
Explanation:https://www.ibm.com/developerworks/data/library/techarticle/dm-0406whitlark/index.html
BLU Acceleration is included in which of the following edition of DB2 LUW? (Choose two.)
- A . Direct Standard Edition
- B . Enterprise Server Edition
- C . Workgroup Server Edition
- D . DB2 Direct Advanced Edition
- E . Advanced Enterprise Server Edition
DE
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/
com.ibm.db2.luw.licensing.doc/doc/r0053238.html
Which of the following commands can undo the work done by the current unit of work executing UPDATE
statements on a table?
- A . COMMIT
- B . SAVEPOINT
- C . ROLLBACK WORK
- D . UPDATE TERMINATE
C
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/
db2z_sql_rollback.html
Which of the following authorities are sufficient for creating a database? (Choose two.)
- A . DBADM
- B . SYSADM
- C . SECADM
- D . SYSCTRL
- E . SYSMAINT
BD
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/
com.ibm.db2.luw.admin.cmd.doc/doc/r0001941.html
Which operation typically requires a Share (S) lock?
- A . INSERT
- B . SELECT
- C . DELETE
- D . UPDATE
B
Explanation:https://www.ibm.com/developerworks/data/library/techarticle/dm-0509schuetz/index.html
What type of function is created using the following command?
CREATE FUNCTION TAN (X DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SIN(X)/COS(X)
- A . Row
- B . Table
- C . Scalar
- D . External
C
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/
db2z_sql_createfuncinlinesqlscalar.html
What of the following is TRUE about triggers?
- A . Triggers are activated by INSERT, UPDATE, DELETE and SELECT statements
- B . Triggers can be used to perform data manipulation operations against complex views
- C . Triggers can only be used to execute logic before or after appropriate SQL statements
- D . Only one trigger for a particular event and activation time can be created for a particular table
B
Explanation:https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/
com.ibm.db2.luw.admin.dbobj.doc/doc/c0004113.html
What is the purpose of a role?
- A . To define exactly what a specific user can and cannot do within a particular database once they have
been authenticated - B . To group a collection of users together so that they can be simultaneously granted and revoked
specific authorities and privileges - C . To group a collection of privileges together so that they can be simultaneously granted to and revoked
from multiple users, groups, or other roles - D . To assign one or more users that need to run local DB2 applications and tools on a particular server to
the DB2ADMNS or the DB2USERS group
Which of the following authorities is sufficient for connecting to a database?
- A . SQLADM
- B . CONNECT
- C . DATAACESS
- D . ACCESSCTRL
B
Explanation:http://www-01.ibm.com/support/docview.wss?uid=swg21427232
Which of the following SQL statements will remove all rows from the table T1? (Choose two.)
- A . DELETE FROM t1
- B . DELETE* FROM t1
- C . DELETE* TABLE t1
- D . TRUNCATE TABLE t1 IMMEDIATE
- E . TRUNCATE TABLE t1