C2090-616 Online Practice Questions and Answers

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

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

Which of the following compatibility features is always active, regardless of the settings for the DB2_COMPATIBILITY_VECTOR registry variable or the SQL_COMPAT global variable?

A. Implicit casting

B. Double-dot notation

C. Use of bitmap indexes

D. Selecting from the DUAL table

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

Application 1 executes the following statements:

INSERT INTO TAB1 (NAME,AMOUNT) VALUES (`John,100);

COMMIT;

UPDATE TAB1 SET AMOUNT=90 WHERE NAME=`John';

After a few seconds, application 2 executes the following query:

SELECT AMOUNT FROM TAB1 WHERE NAME=`John'

Assuming the Currently Committed (CUR_COMMIT) configuration parameter is enabled, which of the

following is TRUE?

A. Application 2 will read the amount of 90

B. Application 2 will read the amount of 100

C. Application 2 will receive a warning message

D. Application 2 will enter lock-wait state until application 1 commits or the locktimeout value expires

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

Which DB2 object is associated with PREVIOUS VALUE and NEXT VALUE expressions?

A. Sequence

B. Row Identifier

C. Identity Column

D. Insert time clustering table

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 of the following statements will successfully create a table that scores index data in a different table space than the table data?

A. CREATE TABLE t1 (c1 INT, c2 INT);

B. CREATE TABLE t1 (c1 INT, c2 INT) IN TS1 INDEX IN TS2;

C. CREATE TABLE t1 (c1 INT, c2 INT) IN TS1 INDEX IN TS1;

D. CREATE TABLE t1 (c1 INT, c2 INT) DATA IN TS1 INDEX IN TS2;

If the LOCKSIZE for a table is changed from TABLE to ROW, which of the following is TRUE?

A. Concurrency may be improved

B. Lock escalation will be prevented

C. Query performance will be reduced

D. The number of locks for the same workload will be reduced