Skip to main content

C2090-730 Real Exam Questions

DB2 9 Family Fundamentals

302 questions available · Page 1 of 31

Updated Exam DumpsVerified AnswersPass Guarantee

Get Complete Exam Dumps
Question 1 Single choice

Which of the following DB2 objects can be referenced by an INSERT statement to generate values for a column?

  1. A

    Sequence

  2. B

    Identity column

  3. C

    Trigger

  4. D

    Table function

Show answer and explanation

Correct answer: A

Question 2 Single choice

Which of the following is the best statement to use to create a user-defined data type that can be used to store currency values?

  1. A

    CREATE DISTINCT TYPE currency ASNUMERIC(7,2)

  2. B

    CREATE DISTINCT TYPE currency AS SMALLINT

  3. C

    CREATE DISTINCT TYPE currency AS BIGINT

  4. D

    CREATE DISTINCT TYPE currency AS DOUBLE

Show answer and explanation

Correct answer: A

Question 3 Single choice

Given the following table:

TAB1

COL1 COL2
----- -----
A 10
B 20
C 30
D 40
E 50

And the following SQL statements:

DECLARE c1 CURSOR WITH HOLD FOR
SELECT * FROM tab1 ORDER BY col_1;
OPEN c1;
FETCH c1;
FETCH c1;
FETCH c1;
COMMIT;
FETCH c1;
CLOSE c1;
FETCH c1;

Which of the following is the last value obtained for COL_2?

  1. A

    20

  2. B

    30

  3. C

    40

  4. D

    50

Show answer and explanation

Correct answer: C

Question 4 Single choice

A user wishing to invoke an SQL stored procedure that queries a table must have which of the following privileges?

  1. A

    CALL privilege on the procedure; SELECT privilege on the table

  2. B

    CALL privilege on the procedure; REFERENCES privilege on the table

  3. C

    EXECUTE privilege on the procedure; SELECT privilege on the table

  4. D

    EXECUTE privilege on the procedure; REFERENCES privilege on the table

Show answer and explanation

Correct answer: C

Question 5 Single choice

Given the following table definition:

SALES
---------------------------------------------
INVOICE_NO CHAR(20) NOT NULL
SALES_DATE DATE
SALES_PERSON CHAR(20)
REGION CHAR(20)
SALES INTEGER

If the following SELECT statement is executed, which of the following describes the order of the rows in the
result data set produced?
SELECT * FROM sales

  1. A

    The rows are sorted by INVOICE_NO in ascending order.

  2. B

    The rows are sorted by INVOICE_NO in descending order.

  3. C

    The rows are ordered based on when they were inserted into the table.

  4. D

    The rows are not sorted in any particular order.

Show answer and explanation

Correct answer: D

Question 6 Single choice

Given the following requirements:

Create a table to contain employee data, with a unique numeric identifier automatically assigned when a row is

added, has an EDLEVEL column that permits only the values 'C', 'H' and 'N', and permits inserts only when a corresponding value for the employee's department exists in the DEPARTMENT table.

Which of the following CREATE statements will successfully create this table?

  1. A

    CREATE TABLEemp (
    empno SMALLINT NEXTVAL GENERATED ALWAYS AS IDENTITY, firstname VARCHAR(30) NOT
    NULL,
    lastname VARCHAR(30) NOT NULL,
    workdept CHAR(3) NOT NULL,
    edlevel CHAR(1),
    PRIMARY KEY emp_pk (empno),
    FOREIGN KEY emp_workdept_fk ON (workdept) REFERENCES department (deptno), CHECK edlevel_ck VALUES (edlevel IN ('C','H','N')),
    );

  2. B

    CREATE TABLEemp (
    empno SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT
    NULL,
    lastname VARCHAR(30) NOT NULL,
    workdept CHAR(3),
    edlevel CHAR(1),
    CONSTRAINT emp_pk PRIMARY KEY (empno),
    CONSTRAINT emp_workdept_fk FOREIGN KEY (workdept) REFERENCES department (deptno),
    CONSTRAINT edlevel_ck CHECK edlevel VALUES ('C','H','N') );

  3. C

    CREATE TABLEemp (
    empno SMALLINT NEXTVAL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT
    NULL,
    lastname VARCHAR(30) NOT NULL,
    workdept CHAR(3) NOT NULL,
    edlevel CHAR(1) CHECK IN ('C','H','N')),
    CONSTRAINT emp_pk PRIMARY KEY (empno),
    CONSTRAINT emp_workdept_fk FOREIGN KEY department (deptno) REFERENCES (workdept)
    );

  4. D

    CREATE TABLEemp (
    empno SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(30) NOT
    NULL,
    lastname VARCHAR(30) NOT NULL,
    workdept CHAR(3),
    edlevel CHAR(1),
    CONSTRAINT emp_pk PRIMARY KEY (empno),
    CONSTRAINT emp_workdept_fk FOREIGN KEY (workdept) REFERENCES department (deptno),
    CONSTRAINT edlevel_ck CHECK (edlevel IN ('C','H','N')) );

Show answer and explanation

Correct answer: D

Question 7 Single choice

What is the SQL Performance Monitor used for?

  1. A

    To examine the health of a DB2 Database Manager instance

  2. B

    To visually construct complex DML statements and examine the results of their execution

  3. C

    To schedule tasks, run tasks, and send notifications about completed tasks to other users

  4. D

    To analyze database operations performed against a DB2 for i5/OS database

Show answer and explanation

Correct answer: D

Question 8 Single choice

A DBA has been asked to create a table which will contain a substantial amount of detailed sales information for each calendar month and maintain it to contain only the last 12 months.

Which of the following methods will facilitate the online removal of the oldest month's data?

  1. A

    Create an MQT that selects the oldest month of data with the REFRESH IMMEDIATE option.

  2. B

    Create 12 separate tables, create a view based on all 12, drop the table with the oldest month's data then drop and re-create the view.

  3. C

    Create a range partitioned table, partitioned by month, and use the ALTER TABLE statement to detach the oldest month and attach storage for new data.

  4. D

    Create a single table, extract the data to be retained using UNLOAD with a SELECT statement, drop and re-create the table then load only the data to be retained.

Show answer and explanation

Correct answer: C

Question 9 Single choice

Which of the following tasks can NOT be performed using the Developer Workbench?

  1. A

    Develop and debug an SQL stored procedure

  2. B

    Develop and debug a user-defined data type

  3. C

    Develop and debug a user-defined function

  4. D

    Develop and run XML queries

Show answer and explanation

Correct answer: B

Question 10 Single choice

Given the following information:

Protocol: TCP/IP
Port Number: 5000
Host Name: DB_SERVER
Database Name: TEST_DB
Database Server Platform: Linux

  1. A

    CATALOG DATABASEtest_db AS test_db REMOTE TCPIP SERVER db_server PORT 5000 OSTYPE
    LINUX;

  2. B

    CATALOG TCPIP NODE 5000 REMOTE SERVERdb_server OSTYPE LINUX; CATALOG DATABASE
    test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

  3. C

    CATALOG TCPIP NODEdb_server REMOTE db_server SERVER 5000 OSTYPE LINUX; CATALOG
    DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

  4. D

    CATALOG TCPIP NODEdb_server REMOTE db_server PORT 5000 OSTYPE LINUX; CATALOG
    DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

Show answer and explanation

Correct answer: C