Ensurepass.com : Ensure you pass the IT Exams
2018 Jan IBM Official New Released C2090-543
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/C2090-543.html

DB2 9.7 Application Development

Question No: 1 – (Topic 1)

Using the default lock timeout and isolation level, user USER1 drops table TABLE1 however, the transaction is not committed. Subsequently, user USER2 attempts to access table TABLE1 using the default isolation level. What will occur?

  1. User USER2 receives an error immediately.

  2. User USER2 will wait until user USER1 issues a COMMIT or ROLLBACK statement.

  3. User USER1 will receive an error.

  4. User USER2 will be able to access the table successfully.

Answer: B

Question No: 2 – (Topic 1)

Which statement is true of an application that only uses static SQL?

  1. The authorization ID of program creator is used to qualify explicitly qualified table references on a DROP statement.

  2. Static SQL uses SQLDA to determine the number of rows successfully fetched on a FETCH statement.

  3. The authorization ID of application creator is used to qualify implicitly qualified view references on an UPDATE statement.

  4. The authorization ID of application invoker used by DB2 to perform authorization checking of an embedded OPEN statement during execution.

Answer: C

Question No: 3 – (Topic 1)

The statement below was used to create a cursor named CSR1:

DECLARE csr1 CURSOR FOR SELECT * FROM employee FOR UPDATE OF job;

When the cursor is opened, the result set contains six rows. After fetching all of the rows in the result set associated with cursor CSR1, application TEST1 needs to fetch the third row

of the result set again.

Which steps will allow application TEST1 to fetch the third row again?

  1. FETCH ABSOLUTE 3

  2. CLOSE csr1; OPEN csr1; FETCH; FETCH; FETCH

  3. REOPEN csr1; FETCH;

    FETCH; FETCH;

  4. FETCH PRIOR; FETCH PRIOR; FETCH PRIOR; FETCH PRIOR;

Answer: B

Question No: 4 – (Topic 1)

A database contains two tables with the same definition and authorities: PRODUCTION.SALESDATA and TEST.SALESDATA. The PRODUCTION.SALESDATA

table contains business-critical information that is constantly changing; the TEST. SALESDATA table contains a snapshot of the PRODUCTION.SALESDATA table. Business rules require that applications under development access the TEST.SALESDATA table until ready for deployment. Once an application is deployed, it accesses the PRODUCTION.SALESDATA table. Which object can be used to simplify switching between the TEST.SALESDATA and PRODUCTION.SALESDATA tables without having to re-code the application?

  1. trigger

  2. procedure

  3. alias

  4. sequence

Answer: C

Question No: 5 – (Topic 1)

A table named INVENTORY was created by executing the SQL statement shown below:

CREATE TABLE inventory (part_no INTEGER, quantity INTEGER, price DECIMAL(7,2), status CHAR(2));

If items are indicated to be out of stock by setting STATUS to NULL and QUANTITY to zero, which SQL statement updates the INVENTORY table to indicate that all items with part numbers below 150 are out of stock?

  1. UPDATE inventory SET status = #39;NULL#39;, quantity = 0 WHERE part_no lt; 150

  2. UPDATE inventory SET status = NULL, SET quantity = 0 WHERE part_no lt; 150

  3. UPDATE inventory SET (status, quantity) = (#39;NULL#39;, 0) WHERE part_no lt; 150

  4. UPDATE inventory SET (status, quantity) = (NULL, 0) WHERE part_no lt; 150

Answer: D

Question No: 6 – (Topic 1)

The statement below was used to create a cursor named CSR1: DECLARE csr1 DYNAMIC SCROLL CURSOR FOR

SELECT * FROM employee;

After fetching all of the rows in the result set associated with cursor CSR1, which two statements can be used to fetch the data starting at the end of the result set? (Choose two.)

  1. FETCH LAST

  2. FETCH PRIOR

  3. FETCH RELATIVE

  4. FETCH OFFSET

Answer: B,C

Question No: 7 – (Topic 1)

Which combination of statements can be used to delete qualifying rows from a table named EMP?

  1. DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR

    UPDATE;

    DELETE FROM emp WHERE CURRENT OF cursor1;

  2. DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR DELETE;

    DELETE FROM emp WHERE CURRENT OF cursor1;

  3. DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR UPDATE;

    DELETE FROM emp;

  4. DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR DELETE;

DELETE FROM emp;

Answer: A

Question No: 8 – (Topic 1)

Click the Exhibit button.

CREATE FUNCTION deptemployees (idept VARCHAR(3)) RETURNS TABLE (empno CHAR(6),

firstname VARCHAR(12), lastname VARCHAR(15)) LANGUAGE SQL READS SQL DATA

NO EXTERNAL ACTION DETERMINISTIC RETURN

SELECT empno, firstnme, lastname FROM employee

WHERE workdept = deptemployees.idept;

Referring to the exhibit, what contains the proper way to invoke the DEPTEMPLOYEES() user-defined function to return all of the employees that work in department D11?

  1. SELECT * FROM TABLE(deptemployees(#39;D11#39;))

  2. SELECT * FROM deptemployees(#39;D11#39;)

  3. SELECT * FROM deptemployees(#39;D11#39;) AS D11dept

  4. SELECT * FROM TABLE(deptemployees(#39;D11#39;)) AS D11dept

Answer: D

Question No: 9 – (Topic 1)

The scope of a cursor is limited to .

  1. the source program in which it is defined

  2. the transaction in which the cursor is declared

  3. all remotely called routines

  4. applications local to the DB2 server

Answer: A

Question No: 10 – (Topic 1)

Click the Exhibit button. CONNECT TO test;

CREATE TABLE parent (empno CHAR(6), firstname VARCAHR(25), lastname VARCHAR(25));

CREATE TABLE child (childno CHAR(6), firstname VARCHAR(25), lastname VARCHAR(25), empno

CHAR(6));

CREATE VIEW employee AS SELECT * FROM parent; CREATE VIEW employee_children AS SELECT * FROM child; COMMIT;

CONNECT RESET;

A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.

Which technique can be used to produce a list of all employees and their children, including

employees who have no children?

  1. FULL OUTER JOIN

  2. LEFT or RIGHT OUTER JOIN

  3. MERGE JOIN

  4. INNER JOIN

Answer: B

100% Ensurepass Free Download!
Download Free Demo:C2090-543 Demo PDF
100% Ensurepass Free Guaranteed!
C2090-543 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.