jhayne
Quiz by , created more than 1 year ago

c2090-543 Quiz on C2090-543, created by jhayne on 29/07/2014.

169
0
1
jhayne
Created by jhayne over 9 years ago
Close

C2090-543

C2090-543

Question 1 of 100

1

Which condition will prevent a developer from using the DB2 Call Level Interface in an application?

Select one of the following:

  • The developer must control the cursor names to comply with company naming standards.

  • An SQL precompiler is not available to the developer.

  • The application must create an external scalar function with the CREATE FUNCTION statement.

  • The DECLARE CURSOR statement needs to be used.

Explanation

Question 2 of 100

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.)

Select one or more of the following:

  • FETCH LAST

  • FETCH PRIOR

  • FETCH RELATIVE

  • FETCH OFFSET

Explanation

Question 3 of 100

1

CREATE TABLE store(sid INTEGER, info XML);
INSERT INTO store VALUES (1,
'<storeinfo sid="1">
<name>Grocery A</name>
<items>
<fruit><name>Mango</name><price>1.20</price></fruit>
<fruit><name>Apple</name><price>0.50</price></fruit>
<dessert><name>Ice Cream</name><price>6.00</price></dessert>
</items>
</storeinfo>');

Given the statements shown in the exhibit, a user executes the query shown below:
XQUERY for $store in db2-fn:xmlcolumn('STORE.INFO')/storeinfo
let $items := $store/items/fruit,
$count := fn:count($items)
return <itemcount>$count</itemcount>

What is the output?

Select one of the following:

  • 2

  • <itemcount>2</itemcount>

  • <itemcount>1</itemcount>

  • <itemcount>$count</itemcount>

Explanation

Question 4 of 100

1

You have created a stored procedure (MULTIRESULTS()) on the server which will return multiple result sets. While developing a PHP application using the IBM_DB2 extension, you want to call the stored procedure. The code shown below will execute the stored procedure:

$stmt = db2_exec($conn, 'CALL multiResults()');

How can you fetch all of the result sets from $stmt?

Select one of the following:

  • Call db2_fetch_object($stmt) for the first result set; call db2_next_result($stmt) for more result sets.

  • Call db2_next_result($stmt) for each result set.

  • Call db2_fetch_object($stmt) for each result set.

  • Call db2_next_result($stmt) for each result set; call db2_fetch_object($stmt) for each row in the result set.

Explanation

Question 5 of 100

1

An existing table has the definition shown below:

CREATE TABLE hr.employees (
empid INTEGER NOT NULL PRIMARY KEY,
deptno INTEGER,
authid VARCHAR(255),
salary DECIMAL (10,2),
commission DECIMAL (5,3) DEFAULT 0,
benefits XML )

Only members of the HR_ADMIN group have privilege to SELECT, INSERT, UPDATE, or DELETE from the HR.EMPLOYEES table. A Web-based application is under development that
connects to the database with a user's AUTHID and enables a user to see their record. No other records are visible.

Which type of database object can be created by a member of the HR_ADMIN that provides the
necessary information without changing the privileges on the HR.EMPLOYEES table?

Select one of the following:

  • trigger

  • view

  • alias

  • index

Explanation

Question 6 of 100

1

The table shown below contains a large number of financial transactions:
CREATE TABLE webstore.transactions (
transaction_id INTEGER NOT NULL PRIMARY KEY,
order_date TIMESTAMP NOT NULL,
shipped_date TIMESTAMP,
customer_id INTEGER NOT NULL,
shipping_info XML NOT NULL,
billing_info XML NOT NULL,
invoice XML NOT NULL )
Only members of the AUDIT_TEAM group have SELECT privilege on the WEBSTORE.TRANSACTIONS table.
For appropriate supply-chain management, members of the INVENTORY_CONTROL group need to see the INVOICE document for each transaction that has a NULL SHIPPED_DATE, but are restricted from seeing any shipping or billing information. Which database object can a member of the AUDIT_TEAM group create to enable the INVENTORY_CONTROL group to access the information needed from WEBSTORE.TRANSACTIONS?

Select one of the following:

  • ALIAS

  • SEQUENCE

  • TRIGGER

  • VIEW

Explanation

Question 7 of 100

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?

Select one or more of the following:

  • TRIGGER

  • PROCEDURE

  • ALIAS

  • SEQUENCE

Explanation

Question 8 of 100

1

For which two database objects can an ALIAS be created? (Choose two.)

Select one or more of the following:

  • SCHEMA

  • TRIGGER

  • SEQUENCE

  • MODULE

Explanation

Question 9 of 100

1

CREATE TABLE s1.mytab (
col1 INTEGER GENERATED ALWAYS AS IDENTITY,
col2 INTEGER,
col3 INTEGER, CHECK (col1+col3 < 500)
CREATE VARIABLE s1.var1 INTEGER DEFAULT (99)
CREATE FUNCTION s1.add100 (p1 INT, p2 INT) RETURNS INTEGER
LANGUAGE SQL DETERMINISTIC NOT FENCED BEGIN
SET p1= p2+100;
RETURN p1;
END

Given the table, variable, and function definitions shown in the exhibit, which two statements contain a valid invocation of the add100 function? (Choose two.)

Select one or more of the following:

  • SELECT col1,outcol=add100(col2,col3) FROM mytab

  • SET var1 = ABS(add100(1,99))

  • SELECT col1, add100(col2,col3) AS outcol FROM mytab

  • SELECT outcol.* FROM TABLE(add100(col2,col3)) AS outcol

Explanation

Question 10 of 100

1

Which SQL procedure will retrieve all the rows from table T1 and make those rows available to the invoker as a result set?

Select one of the following:

  • CREATE PROCEDURE proc1()
    DYNAMIC RESULTS SET 1
    LANGUAGE SQL
    SELECT * FROM t1

  • CREATE PROCEDURE proc1()
    DYNAMIC RESULTS SET 1
    LANGUAGE SQL
    BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM t1;
    OPEN c1;
    END

  • CREATE PROCEDURE proc1()
    DYNAMIC RESULTS SET 1
    LANGUAGE SQL
    RETURN (SELECT * FROM t1)

  • CREATE PROCEDURE proc1()
    DYNAMIC RESULTS SET 1
    LANGUAGE SQL
    BEGIN
    DECLARE c1 CURSOR FOR SELECT * FROM t1;
    RETURN c1;
    END

Explanation

Question 11 of 100

1

Which object can be invoked on a single invocation to return both output parameters and a result set back to the invoking application?

Select one of the following:

  • procedure

  • module

  • table function

  • method

Explanation

Question 12 of 100

1

Which object can be created to group together related procedures and user-defined functions that are used for tax calculations?

Select one of the following:

  • COLLECTION

  • MODULE

  • ROUTINE

  • ROUTINE SPACE

Explanation

Question 13 of 100

1

CREATE FUNCTION deptemployees (idept VARCHAR(3))
RETURNS TABLE (empno CHAR(6),
firstnameVARCHAR(12),
lastnameVARCHAR(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?

Select one of the following:

  • SELECT * FROM TABLE(deptemployees('D11'))

  • SELECT * FROMdeptemployees('D11')

  • SELECT * FROMdeptemployees('D11') AS D11dept

  • SELECT * FROM TABLE(deptemployees('D11')) AS D11dept

Explanation

Question 14 of 100

1

An application must insert values into a column that has a TIMESTAMP data type. What is a valid string representation of a TIMESTAMP?

Select one of the following:

  • 2009.08.07.12.22.22.0000

  • 2009-08-07 12:22:22.0000

  • 07/08/2009-12:22:22:0000

  • 07-08-2009 12.22.22.0000

Explanation

Question 15 of 100

1

In a query, which clause can reference a CLOB data type?

Select one of the following:

  • an ORDER BY clause

  • a GROUP BY clause

  • a WHERE clause

  • a DISTINCT clause

Explanation

Question 16 of 100

1

You have set your data types as CHAR(10), VARCHAR(20). Given the operation shown below:
CHAR(10) UNION VARCHAR(20)

What will be the final data type?

Select one of the following:

  • CHAR(200)

  • VARCHAR(30)

  • VARCHAR(20)

  • CHAR(30)

Explanation

Question 17 of 100

1

The table shown below exists in the database:
CREATE TABLE team.workitems (
priority INTEGER,
last_update TIMESTAMP,
details XML )

User USER1 has CREATEIN authority on the TEAM schema and can SELECT from the TEAM.WORKITEMS table. An SQL routine is created that performs a positioned update using a
cursor declared as shown below:

DECLARE c_workitem CURSOR FOR SELECT priority, last_update FROM team.workitems FOR UPDATE;

Which additional privilege is required?

Select one of the following:

  • EXECUTE privilege on TEAM schema

  • ALTER privilege on TEAM.WORKITEMS table

  • BINDADD privilege on TEAM schema

  • UPDATE privilege on TEAM.WORKITEMS table

Explanation

Question 18 of 100

1

User USER1 successfully creates a table USER1.TABLE1 and successfully creates a package USER1.PKG1 for an application containing the SQL shown below:

EXEC SQL UPDATE user1.table1 SET col1 =:hvar1 WHERE col1 IS NULL

Assuming user USER2 can connect to the database, but has no other relevant authorities, which command must user USER1 issue to permit user USER2 to run the application?

Select one of the following:

  • GRANT ALTER ON TABLE user1.table1 TO USER user2

  • GRANT EXECUTE ON PACKAGE user1.pkg1 TO USER user2

  • GRANT UPDATE ON TABLE user1.table1 TO USER user2

  • GRANT BIND ON PACKAGE user1.pkg1 TO USER user2

Explanation

Question 19 of 100

1

A database contains a table and a view declared as shown below:
CREATE TABLE s1.t1 ( c1 INTEGER, c2 CHAR(20) );
CREATE VIEW s1.v1 AS SELECT * FROM s1.t1;
A system administrator successfully executes the following code:
GRANT CONNECT, IMPLICIT_SCHEMA ON DATABASE TO user1;
GRANT INSERT ON TABLE s1.t1 TO user1;
GRANT CONTROL ON s1.v1 TO user1;
Which SQL statement does user USER1 have privileges to execute?

Select one of the following:

  • UPDATE s1.t1 SET c1 = 2 WHERE c1 IS NULL

  • CREATE VIEW user1.v2 AS SELECT * FROM s1.v1

  • CREATE PUBLIC ALIAS a1 FOR s1.v1

  • SELECT * FROM s1.t1 WHERE c2 < 100

Explanation

Question 20 of 100

1

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

Select one of the following:

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

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

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

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

Explanation

Question 21 of 100

1

Which set of SQL statements must be run prior to the EXECUTE statement shown below so that the INSERT statement can be executed successfully?
EXECUTE s1 USING :v1;

Select one of the following:

  • SET :stmt = 'INSERT INTO tab1 VALUES(?)';
    PREPARE s1 FROM :stmt

  • SET :stmt = 'INSERT INTO tab1 VALUES(:v1)';
    PREPARE s1 FROM :stmt;

  • SET :stmt = 'INSERT INTO tab1 VALUES(?v1)';
    PREPARE s1 FROM :stmt;

  • SET :stmt = 'INSERT INTO tab1 VALUES(?)';
    PREPARE s1 FROM :stmt DESCRIBE :v1;

Explanation

Question 22 of 100

1

VEMPLOYEE
-------------------
EMPNO CHAR(6)
LASTNAME VARCHAR(30)
SALARY DEC(9,2)
DEPTNO CHAR(3)

VDEPARTMENT
----------------------
DEPTNO CHAR(3)
DEPTNAME VARCHAR(20)
MGRNO CHAR(6)

VPROJECT
----------------
PROJNO CHAR(6)
PROJNAME VARCHAR(20)
DEPTNO CHAR(6)
RESPEMP CHAR(6)

A JOIN operation references three views in the exhibit; the SELECT statement used does not contain a WHERE clause. When the SELECT statement is executed, what will the result set contain?

Select one of the following:

  • one row from each table

  • no rows

  • unordered rows from one of three tables

  • the Cartesian product

Explanation

Question 23 of 100

1

CONNECT TO test;
CREATE TABLE project (projno CHAR(6), deptno CHAR(3), projname VARCHAR(20));
CREATE TABLE employee (empno CHAR(6), lastname VARCHAR(25), deptno CHAR(3));
COMMIT;
CONNECT RESET;

A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
Which query will produce a list of all projects and all employees assigned to projects and also include employees that have not been assigned a project?

Select one of the following:

  • SELECT empno, lastname, workdept, projname
    FROM employee RIGHT OUTER JOIN project
    ON employee.deptno = project.deptno

  • SELECT empno,lastname, employee.deptno, projname
    FROM employee FULL OUTER JOIN project
    ON employee.deptno = project.deptno

  • SELECT empno, lastname, employee.deptno, projname
    FROM employee LEFT OUTER JOIN project
    ON employee.deptno = project.deptno

  • SELECT empno, lastname, employee.deptno, projname
    FROM employee JOIN project ON employee.deptno = project.deptno

Explanation

Question 24 of 100

1

Which type of join condition specifies that the result table will contain a row for each row from the Table to the left, concatenated with each row from the table to the right?

Select one of the following:

  • FULL OUTER JOIN

  • EXCEPTION JOIN

  • CROSS JOIN

  • INNER JOIN

Explanation

Question 25 of 100

1

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?

Select one of the following:

  • FULL OUTER JOIN

  • LEFT or RIGHT OUTER JOIN

  • MERGE JOIN

  • INNER JOIN

Explanation

Question 26 of 100

1

Given the tables shown below:
TAB1 LETTER GRADE
A 80
B 70
C 60

TAB2 LETTER GPA
A 4
C 2
D 1

and the successful execution of the following query:
SELECT * FROM tab1 FULL OUTER JOIN tab2 ON tab1.letter = tab2.letter;
How many rows will be returned?

Select one of the following:

  • 2

  • 3

  • 4

  • 6

Explanation

Question 27 of 100

1

Given the tables shown below:

TAB1 LETTER GRADE
A 80
B 70
C 60

TAB2 LETTER GPA
A 4
C 2
D 1

and the successful execution of the following query:
SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON tab1.letter = tab2.letter;
How many rows will be returned?

Select one of the following:

  • 1

  • 2

  • 3

  • 4

Explanation

Question 28 of 100

1

Given the two tables shown below:
COUNTRIES
----------
COUNTRY CONTINENT_ID
Greece 1
Germany 1
Canada 2
CONTINENTS
-----------
ID CONTINENT
1 Europe
2 North America
3 Asia
A user wants the following result:
COUNTRY CONTINENT
Greece Europe
Which query should the user submit to achieve the result?

Select one of the following:

  • SELECT * FROM countries LEFT OUTER JOIN continents
    ON countries.continent_id =continents.id AND countries.country= 'Greece';

  • SELECT * FROM countries LEFT OUTER JOIN continents
    ON countries.continent_id =continents.id AND continents.id = 1
    FETCH FIRST 1 ROWS ONLY;

  • SELECT countries.country, continents.continent FROM countries RIGHT OUTER JOIN continents
    ON countries.continent_id= continents.id AND continents.id = 1;

  • SELECT countries.country, continents.continent FROM countries, continents
    WHERE countries.continent_id=continents.id AND country = 'Greece';

Explanation

Question 29 of 100

1

CONNECT TO test;
CREATE TABLE tab_a (col1 INT);
CREATE TABLE tab_b (col1 INT);
INSERT INTO tab_a VALUES (1), (2), (3), (4);
INSERT INTO tab_b VALUES (1), (1), (2), (2);
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
If the statement shown below is executed:
UPDATE tab_a SET col1 = 10 WHERE col1 IN (SELECT * FROM tab_b);
How many rows in table TAB_A will be modified?

Select one of the following:

  • 0

  • 1

  • 2

  • 3

Explanation

Question 30 of 100

1

Table T1 and view V1 were created by executing the statements shown below:

CREATE TABLE t1 (c1 INT CHECK (c1 < 275), c2 CHAR(3));
CREATE VIEW v1 AS SELECT c1, c2 FROM t1 WHERE c1 > 250
WITH LOCAL CHECK OPTION;

Which statement will execute successfully?

Select one of the following:

  • INSERT INTO t1 VALUES (275, 'abc')

  • INSERT INTO t1 VALUES (260, 'abc')

  • INSERT INTO v1 VALUES (250, 'abc')

  • INSERT INTO v1 VALUES (NULL,abc)

Explanation

Question 31 of 100

1

Table T1 and views V1 and V2 were created by executing the statements shown below:
CREATE TABLE t1 (c1 INT, c2 CHAR(3));
CREATE VIEW v1 AS SELECT c1, c2 FROM t1 WHERE c1 > 100;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE c2 IS NULL WITH
CASCADED CHECK OPTION;
Which statement will execute successfully?

Select one of the following:

  • INSERT INTO v2 VALUES (125, NULL)

  • INSERT INTO v2 VALUES (125, 'abc')

  • INSERT INTO v2 VALUES (100, NULL)

  • INSERT INTO v2 VALUES (100, 'abc')

Explanation

Question 32 of 100

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?

Select one of the following:

  • UPDATE inventory SET status = 'NULL', quantity = 0 WHERE part_no < 150

  • UPDATE inventory SET status = NULL, SET quantity = 0 WHERE part_no < 150

  • UPDATE inventory SET (status, quantity) = ('NULL', 0) WHERE part_no < 150

  • UPDATE inventory SET (status, quantity) = (NULL, 0) WHERE part_no < 150

Explanation

Question 33 of 100

1

CONNECT TO test;
CREATE TABLE tab_a (col1 INT);
CREATE TABLE tab_b (col1 INT);
INSERT INTO tab_a VALUES (1), (2), (3), (4);
INSERT INTO tab_b VALUES (1), (2), (2), (3);
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit executed successfully. If the statement shown below is executed:
DELETE FROM tab_a WHERE col1 IN (SELECT DISTINCT * FROM tab_b);
How many rows will be removed from table TAB_A?

Select one of the following:

  • 0

  • 1

  • 2

  • 3

Explanation

Question 34 of 100

1

Given the SQL statement shown below:
UPDATE address SET number_street =
(SELECT address FROM employee
WHERE address.empid = employee.empid)
WHERE number_street IS NULL
Which comment is true?

Select one of the following:

  • The UPDATE operation will succeed only if ADDRESS.EMPID has been defined as a primary key.

  • The UPDATE operation will succeed only if both ADDRESS.EMPID and EMPLOYEE.EMPID have been defined as primary keys.

  • The UPDATE operation will succeed only if the ADDRESS table does not contain duplicate values for EMPID.

  • The UPDATE operation will succeed only if the data retrieved by the sub query does not contain duplicate values for EMPLOYEE.EMPID.

Explanation

Question 35 of 100

1

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

Select one of the following:

  • DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR UPDATE;
    DELETE FROM emp WHERE CURRENT OF cursor1;

  • DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR DELETE;
    DELETE FROM emp WHERE CURRENT OF cursor1;

  • DECLARE cursor1 CURSOR FOR SELECT empno, workdept FROM emp FOR UPDATE;
    DELETE FROM emp;

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

Explanation

Question 36 of 100

1

An application uses the statement shown below to give employees in department "E11" a bonus equal to 10 of their salary:
UPDATE emp SET bonus = (SELECT .10 * salary FROM emp y WHERE empno = y.empno) WHERE CURRENT OF c1;
Which statement should be used to define cursor C1?

Select one of the following:

  • DECLARE c1 CURSOR FOR SELECT bonus FROMemp WHERE workdept = 11??

  • DECLARE c1 CURSOR FOR SELECT bonus FROM emp WHERE workdept = 11?FOR UPDATE OF bonus

  • DECLARE c1 CURSOR FOR SELECT * FROM emp WHERE workdept = ?11? FOR UPDATE

  • DECLARE c1 CURSOR FOR SELECT * FROM emp WHERE workdept = ?11? FOR UPDATE OF *

Explanation

Question 37 of 100

1

The scope of a cursor is limited to

Select one of the following:

  • the source program in which it is defined

  • the transaction in which the cursor is declared

  • all remotely called routines

  • applications local to the DB2 server

Explanation

Question 38 of 100

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?

Select one of the following:

  • FETCH ABSOLUTE 3

  • CLOSE csr1;
    OPEN csr1;
    FETCH;
    FETCH;
    FETCH

  • REOPEN csr1;
    FETCH;
    FETCH;
    FETCH;

  • FETCH PRIOR;
    FETCH PRIOR;
    FETCH PRIOR;
    FETCH PRIOR;

Explanation

Question 39 of 100

1

An application contains the statements shown below:

DECLARE csr1 CURSOR WITH HOLD FOR SELECT * FROM employee;
OPEN csr1;
FETCH csr1;
DECLARE csr2 CURSOR FOR SELECT * FROM employee;
OPEN csr2;
COMMIT;
CLOSE csr1;

When will cursor CSR1 lose position?

Select one of the following:

  • When the application issues the COMMIT statement.

  • When the last row is read from the CSR1 result set.

  • When cursor CSR2 is opened.

  • When cursor CSR1 is closed.

Explanation

Question 40 of 100

1

Given the application shown below:
DECLARE csr1 CURSOR WITH HOLD FOR SELECT * FROM employee;
OPEN csr1;
FETCH csr1;
COMMIT;
CLOSE csr1;
How long does cursor CSR1 remain open?

Select one of the following:

  • CSR1 remains open until a COMMIT is executed.

  • CSR1 remains open until the last row is read from the result set.

  • CSR1 remains open until CLOSE csr1 is executed.

  • CSR1 remains open until a cursor with a different name is opened.

Explanation

Question 41 of 100

1

A library keeps digital copies of books in the table shown below:
CREATE TABLE library.collection (
bookid INTEGER NOT NULL PRIMARY KEY,
ebook_contentBLOB(2G) )
A CLI/ODBC application is written that retrieves the EBOOK_CONTENT for a given BOOKID in 32K pieces. Which two APIs can be used to perform the retrieval? (Choose two.)

Select one or more of the following:

  • SQLGetLength()

  • SQLGetData()

  • SQLGetSubString()

  • SQLFetchNext()

Explanation

Question 42 of 100

1

The table shown below exists within a database:
CREATE TABLE s1.t1 ( c1 INTEGER NOT NULL PRIMARY KEY, c2 CLOB( 2G ) )
A CLI/ODBC application uses the SQLGetSubString() API to retrieve the first 1024 and last 1024 bytes of data from column C2. Which mechanism is used to minimize the amount of data sent to the client for this operation?

Select one of the following:

  • module

  • temp file

  • locator

  • log file

Explanation

Question 43 of 100

1

An application must perform maintenance on a table named EMPLOYEE. Which SQL statement can be used to restrict all activity on the EMPLOYEE table during maintenance by the application?

Select one or more of the following:

  • ALTER TABLE employee LOCK SIZE TABLE

  • LOCK TABLE employee IN SHARE MODE

  • ALTER TABLE employee LOCK SIZE ROWS

  • LOCK TABLE employee IN EXCLUSIVE MODE

Explanation

Question 44 of 100

1

INSERT INTO table1 VALUES(1,11);
INSERT INTO table1 VALUES(2,11);
SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;
INSERT INTO table1 VALUES(3,11);
INSERT INTO table1 VALUES(4,11);
INSERT INTO table1 VALUES(5,11);
SAVEPOINT sp2 ON ROLLBACK RETAIN CURSORS;
INSERT INTO table1 VALUES(6,11);
INSERT INTO table1 VALUES(7,11);
ROLLBACK TO SAVEPOINT sp2;
INSERT INTO table1 VALUES(8,11);
SAVEPOINT sp3 ON ROLLBACK RETAIN CURSORS;
INSERT INTO table1 VALUES(9,11);
ROLLBACK;

Application1 executes the SQL statements shown in the exhibit against table TABLE1 in manual commit mode. How many rows will be inserted in TABLE1?

Select one or more of the following:

  • 0

  • 5

  • 7

  • 9

Explanation

Question 45 of 100

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?

Select one or more of the following:

  • User USER2 receives an error immediately.

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

  • User USER1 will receive an error.

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

Explanation

Question 46 of 100

1

Which standard is used for XML document validation in DB2?

Select one of the following:

  • DTD

  • RelaxNG

  • XMLSchema

  • Schematron

Explanation

Question 47 of 100

1

Which two techniques guarantee that XML document validation will be performed? (Choose two.)

Select one or more of the following:

  • Implicit use of the XMLCHECK() function in the SQL statements.

  • Use triggers for INSERT and UPDATE operations with the XMLVALIDATE() function.

  • Implicit use of the XMLVALIDATE() function in SQL statements.

  • Use triggers for INSERT and UPDATE operations with the XMLSERIALIZE() function.

Explanation

Question 48 of 100

1

Function XMLPARSE is used in a INSERT statement. In which two situations will boundary whitespaces be stripped? (Choose two.)

Select one or more of the following:

  • When the CURRENT IMPLICIT XMLPARSE OPTION special register is set to 'STRIP WHITESPACE' and no XMLPARSE option is used.

  • When the STRIP WHITESPACE option is used, the XML element contains the xml:space="preserve" attribute.

  • When the STRIP WHITESPACE option is used, the XML element contains the xml:space="default" attribute.

  • When the PRESERVE WHITESPACE option is used.

Explanation

Question 49 of 100

1

Which three data types are supported as output of the XMLSERIALIZE function? (Choose three.)

Select one or more of the following:

  • CLOB

  • CHAR

  • DOUBLE

  • BLOB

  • GRAPHIC

Explanation

Question 50 of 100

1

PRODUCTS
ID NAME QUANTITY
--- ------------------ ----------------
1 Orange juice 30
2 Milk 25

Given the table shown in the exhibit. Which values will be returned by query shown below?
SELECT XMLGROUP(
id as "Id",name as "Name",quantity as "Quantity"
OPTION ROW "Product") as Products
FROM products

Select one of the following:

  • <rowset>
    <Product><Id>1</Id><Name>Orange juice</Name><Quantity>30</Quantity></Product>
    <Product><Id>2</Id><Name>Milk</Name><Quantity>25</Quantity></Product>
    </rowset>

  • <Product><Id>1</Id><Name>Orange juice</Name><Quantity>30</Quantity></Product>
    <Product><Id>2</Id><Name>MIlk</Name><Quantity>25</Quantity></Product>

  • <Products>
    <Product><Id>1</Id><Name>Orange juice</Name><Quantity>30</Quantity></Product>
    <Product><Id>2</Id><Name>Milk</Name><Quantity>25</Quantity></Product>
    </Products>

  • <PRODUCTS>
    <PRODUCT><ID>1</ID><NAME>Orange juice</NAME><QUANTITY>30</QUANTITY></PRODUCT>
    <PRODUCT><ID>2</ID><NAME>Milk</NAME><QUANTITY>25</QUANTITY></PRODUCT>
    </PRODUCTS>

Explanation

Question 51 of 100

1

Which statement should be used to obtain the result set shown below?
<Root>
<First>Hello</First>
<Second>World !</Second>
<?Test Pass test?>
</Root>

Select one of the following:

  • VALUES XMLDOCUMENT(
    XMLELEMENT (NAME "Root",
    XMLELEMENT(NAME "First", 'Hello'),
    XMLELEMENT(NAME "Second", 'World !'),
    XMLTEXT ('Test Pass test')
    ) )

  • VALUES XMLDOCUMENT(
    XMLELEMENT (NAME "Root",
    XMLFOREST('Hello' as "First", 'World !' as "Second"),
    XMLCOMMENT ('Test Pass test')
    ) )

  • VALUES XMLDOCUMENT(
    XMLELEMENT (NAME "Root",
    XMLELEMENT(NAME "First", 'Hello'),
    XMLELEMENT(NAME "Second", 'World !'),
    XMLATTRIBUTES ( 'Pass test' as "test")
    ) )

  • VALUES XMLDOCUMENT(
    XMLELEMENT (NAME "Root",
    XMLFOREST('Hello' as "First", 'World !' as "Second"),
    XMLPI (NAME "Test", 'Pass test')
    ) )

Explanation

Question 52 of 100

1

Which three SQL/XML functions support the default parameter's passing behavior? (Choose three.)

Select one or more of the following:

  • XMLTABLE()

  • XMLTRANSFORM()

  • XMLQUERY()

  • XMLEXISTS()

  • XMLSERIALIZE()

Explanation

Question 53 of 100

1

Click the Exhibit button.
CREATE TABLE store(sid INTEGER, info XML);
INSERT INTO store VALUES (1,
'<storeinfo sid="1">
<name>ABCDEF</name>
</storeinfo>');
INSERT INTO store VALUES (2,
'<storeinfo sid="2">
<name>FEDCBA</name>
</storeinfo>');
Given the statements shown in the exhibit, a user executes the query shown below:
XQUERY for $store in db2-fn:xmlcolumn('STORE.INFO')/storeinfo
let $name := fn:substring($store/name, 1, 3),
$lcase := fn:lower-case($name)
return $lcase
What is the output?

Select one of the following:

  • abcd
    fedc

  • aBC
    fED

  • abc
    fed

  • aBCDEF
    fEDCBA

  • abfe

Explanation

Question 54 of 100

1

CREATE TABLE gradereport(sid INTEGER, info XML);
INSERT INTO gradereport VALUES (1,
'<studentinfo sid="1">
<name>John Smith</name>
<honours>No</honours>
<grades>
<course><name>ECE100</name><grade>80</grade></course>
<course><name>CSC100</name><grade>70</grade></course>
<course><name>MAT100</name><grade>60</grade></course>
</grades>
<phone type="mobile">416-333-8725</phone>
</studentinfo>');
Given the statements shown in the exhibit, which two queries can be used to return the semester average of the student? (Choose two.)

Select one or more of the following:

  • XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo
    let $sum := fn:sum($studinfo/grades/course/grade),
    $count := fn:count($studinfo/grades/course/grade)
    return $sum div $count

  • XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo
    let $average := fn:average($studinfo/grades/course/grade)
    return $average

  • XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo
    let $avg := fn:avg($studinfo/grades/course/grade)
    return $avg

  • XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo
    let $avg := fn:avg($studinfo/grades/course)
    RETURN $avg

Explanation

Question 55 of 100

1

CREATE TABLE gradereport(sid INTEGER, info XML);
INSERT INTO gradereport VALUES (1,
'<studentinfo sid="1">
<name>John Smith</name>
<honours>No</honours>
<grades>
<course><name>ECE100</name><grade>80</grade></course>
<course><name>CSC100</name><grade>70</grade></course>
<course><name>MAT100</name><grade>75</grade></course>
</grades>
<phone type="mobile">416-333-8725</phone>
</studentinfo>');
Given the statements shown in the exhibit, a user wishes to obtain the following result:
<student><name>John Smith</name><phone>416-333-8725</phone></student>
Which query can the user execute to achieve this?

Select one of the following:

  • XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo where
    $studinfo/@sid="1" return <student>{$studinfo/name}<phone>{$studinfo/phone/text()}</phone></student>

  • XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo return
    <student>{$studinfo/name}{$studinfo/phone}</student>

  • XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo where
    $studinfo/@sid="1" return {$studinfo/name}{$studinfo/phone/text()}

  • XQUERY for $studinfo in db2-fn:xmlcolumn('GRADEREPORT.INFO')/studentinfo where
    $studinfo/@sid="1" return <student>{$studinfo/name}<phone>{$studinfo/phone}</phone></student>

Explanation

Question 56 of 100

1

<personinfo>
<name>John Wayne</name>
<address type="private">
<city>New York</city>
<country>US</country>
<phone>850-734-6672</phone>
</address>
<phone type="mobile">796-858-1272 </phone>
<phone type="security">646-252-1053</phone>
</personinfo>
<personinfo>
<name>Barbara Wayne</name>
<address type="private">
<city>New York</city>
<country>US</country>
<phone>850-734-6672</phone>
</address>
<phone type="mobile">796-858-1231 </phone>
<phone type="security">646-252-1153</phone>
</personinfo>
The table PERSON is declared as shown below:
CREATE TABLE person (id BIGINT, info XML)
The documents shown in the exhibit are successfully inserted into the table.
How many phone numbers will be affected by the statement shown below?
UPDATE xmlapp.person
SET info = xmlquery( 'copy $new := $INFO
modify for $j in $new/personinfo//phone return
do replace value of $j with "444-444-4444"
return $new' )
WHERE XMLEXISTS('$INFO/personinfo[name="John Wayne"]')

Select one of the following:

  • 0

  • 3

  • 4

  • 6

Explanation

Question 57 of 100

1

<personinfo>
<name>John Smith</name>
<address type="private">
<city>New York</city>
<country>US</country>
<phone>850-734-6672</phone>
</address>
<phone type="mobile">796-858-1272 </phone>
<phone type="security">646-252-1053</phone>
</personinfo>
The table PERSON is declared as shown below:
CREATE TABLE person (id BIGINT, info XML)
The document shown in the exhibit is successfully inserted into the table. How many rows will be returned for the following statement?
SELECT t.* FROM xmlapp.PERSON,
XMLTABLE (
'$INFO/personinfo'
COLUMNS
name VARCHAR(30) PATH './name',
phone VARCHAR(30) PATH './phone'
) AS t

Select one of the following:

  • 2

  • 4

  • 6

  • 0

Explanation

Question 58 of 100

1

The table PERSON is declared as shown below:
CREATE TABLE xmltest (id BIGINT, info XML)
What is the column type for the result of the following statement?
SELECT t.* FROM
xmltest,
XMLTABLE (
'$INFO/question'
) AS t

Select one of the following:

  • VARCHAR

  • CLOB

  • XML

  • BLOB

Explanation

Question 59 of 100

1

CREATE TABLE prod_type (prod_type VARCHAR(10), description VARCHAR (30));
CREATE TABLE prod_descr(info XML);
INSERT INTO prod_descr VALUES
('<product id="1">
<name>Orange juice</name>
<company>Orange Garden</company>
<type>Drinks</type>
</product>'),
('<product id="2">
<name>Coke</name>
<company>Best drinks</company>
<type>Drinks</type>
<type>Premium</type>
</product>');
INSERT INTO prod_type VALUES ('Drinks','Drinks');

You execute the SQL statement shown in the exhibit. Which two statements will return rows? (Choose two.)

Select one or more of the following:

  • SELECT name, company, description FROM prod_type, prod_descr,
    XMLTABLE('$INFO/product'
    COLUMNS
    name VARCHAR(20) PATH './name',
    company VARCHAR(20) PATH './company'
    ) WHERE XMLEXISTS('$INFO/product[type = $PROD_TYPE]');

  • SELECT name, company, description FROM prod_type, prod_descr,
    XMLTABLE('$INFO/product'
    COLUMNS
    type VARCHAR(10) PATH './type', name
    VARCHAR(20) PATH './name', company
    VARCHAR(20) PATH './company'
    ) WHERE type=prod_type;

  • SELECT name, company, description FROM
    prod_type, prod_descr, XMLTABLE(
    '$INFO/product'
    COLUMNS
    name VARCHAR(20) PATH './name',
    company VARCHAR(20) PATH './company'
    ) WHERE prod_type= XMLCAST (XMLQUERY('$INFO/product/type') AS VARCHAR(10));

  • SELECT name, company, description FROM prod_type, prod_descr,
    XMLTABLE('$INFO/product[type=$PROD_TYPE]'
    COLUMNS
    name VARCHAR(20) PATH './name',
    company VARCHAR(20) PATH './company'
    );

Explanation

Question 60 of 100

1

Which statement(s) will create and bind the package for the program myprogram.sqc?

Select one of the following:

  • BIND myprogram.bnd

  • PRECOMPILE myprogram.sqc

  • gcc myprogram.sqc -o myprogram
    BIND myprogram.bnd

  • gcc myprogram.sqc -o myprogram
    REBIND myprogram.sqc

Explanation

Question 61 of 100

1

Which two commands can be used to make use of the latest statistics? (Choose two.)

Select one or more of the following:

  • INSPECT

  • FLUSH PACKAGE CACHE

  • REBIND

  • RUNSTATS

Explanation

Question 62 of 100

1

Which two SQL statements demonstrate the correct usage of a parameter marker? (Choose two.)

Select one or more of the following:

  • DELETE FROM employee WHERE empno = ?

  • SELECT ? FROM employee

  • VALUES(?) INTO :hvar1

  • SET CURRENT SQLID = ?

Explanation

Question 63 of 100

1

Given the SQL statement shown below:
UPDATE employee SET lastname = CAST(? AS VARCHAR(12)) WHERE empno = ?00050??
What does CAST(? AS VARCHAR(12)) represent?

Select one of the following:

  • a typed host variable

  • a typed parameter marker

  • a null indicator variable

  • a host parameter descriptor

Explanation

Question 64 of 100

1

A JDBC application uses the "com.ibm.db2.jcc.DB2Driver" driver. Which two JDBC code snippets illustrate establishing valid connections to a DB2 database named MYDB? (Choose two.)

Select one or more of the following:

  • Stringurl = "jdbc:db2://myhost:5021/mydb:user=myuser;password=mypwd;";
    Connection conn = DriverManager.getConnection (url);

  • // Assume aDataSource object named mydb was created by the system administrator
    DB2SimpleDataSource dbds=new DB2SimpleDataSource();
    DataSourceds = (DataSource)dbds.lookup("jdbc/mydb");
    conn= ds.getConnection();

  • Stringurl = "jdbc:db2://myhost/mydb"
    Connection conn = DriverManager.getConnection (url);

  • // Assume aDataSource object named mydb was created by the system administrator
    Context ctx=new InitialContext();
    DataSourceds = (DataSource)ctx.lookup("jdbc/mydb");
    conn= ds.getConnection();

Explanation

Question 65 of 100

1

A PHP Web application uses the ibm_db2 extension to establish a persistent connection to a remote database. What must be true of the connections to make full use of the db2_pconnect() API?

Select one of the following:

  • The commitment control mode must be set to manual commit mode.

  • Connections must be established under the same user authentication credentials.

  • The persistent connection must be explicitly closed after the database work has completed.

  • The commitment control mode must be set to autocommit mode.

Explanation

Question 66 of 100

1

An application connecting to a DB2 database using the IBM Data Server Provider for .NET is seeing performance degradation after 100 active users. Which Visual Basic .NET command string enables the re-use of the database connections?

Select one of the following:

  • DimconnectString As String = "Database=SAMPLE;UID=admin;PWD=pwd; Min Pool Size=10"
    Dim conn As DB2Connection = new DB2Connection(connectString)

  • DimconnectString As String = "Database=SAMPLE;UID=admin;PWD=pwd; Min Pool Size=10"
    Dim conn As DB2Connection = new SqlConnection(connectString)

  • DimconnectString As String = "Database=SAMPLE;UID=admin;PWD=pwd;Pooling=100;"
    Dim conn As DB2Connection = new DB2Connection(connectString)

  • DimconnectString As String = "Database=SAMPLE;UID=admin;PWD=pwd;Pooling=100;"
    Dim conn As DB2Connection = new SqlConnection(connectString)

Explanation

Question 67 of 100

1

What are two valid connection options that you can use while creating a connection in PHP using the IBM_DB2 extension? (Choose two.)

Select one or more of the following:

  • "autocommit" => DB2_AUTOCOMMIT_FALSE

  • "CURSOR" => DB2_SCROLLABLE_SENSITIVE

  • "trustedcontext" => DB2_TRUSTED_CONTEXT_ENABLE

  • "DB2_ATTR_CASE" => DB2_CASE_UPPER

Explanation

Question 68 of 100

1

The database shown below exists on a remote server:
Database name: MYDB
Host IP: 12.34.56.78
DB2 Port: 56789
User: remote
password: password

This database has been cataloged on the local server with an alias of MYREMDB. You are developing a CLI/ODBC application that must establish a connection to the remote database shown in the exhibit. What are two ways by which you can connect to the database? (Choose two.)

Select one or more of the following:

  • SQLConnect(hdbc, "mydb", SQL_NTS, "remote", SQL_NTS, "password", SQL_NTS)

  • SQLConnect(hdbc, "myremdb", SQL_NTS, "remote", SQL_NTS, "password", SQL_NTS)

  • SQLDriverConnect(hdbc, (SQLHWND)NULL, "DSN=myremdb;UID=remote;PWD=password",
    SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT)

  • SQLDriverConnect(hdbc, (SQLHWND) NULL, "DSN=mydb;UID=remote;PWD=password", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT)

Explanation

Question 69 of 100

1

A CLI/ODBC application contains the lines of code shown below:

SQLHANDLE hstmt; /* statement handle */
SQLCHAR *stmt = (SQLCHAR *)"DELETE FROM org WHERE deptnumb = ? ";
SQLSMALLINT parameter1=5;
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
SQLSetStmtAttr(hstmt, SQL_ATTR_DEFERRED_PREPARE, SQL_DEFERRED_PREPARE_OFF);

If table ORG does not exist, which two CLI/ODBC APIs will return SQL_ERROR in the variable RC? (Choose two.)

Select one or more of the following:

  • rc = SQLExecute(hstmt);

  • rc = SQLSetConnectAttr( hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMM?IT_OFF, SQL_NTS);

  • rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT, SQL_SMALLINT, 0, 0, &parameter1, 0, NULL);

  • rc = SQLPrepare(hstmt, stmt, SQL_NTS);

Explanation

Question 70 of 100

1

While developing a CLI application, you use the code shown below:

SQLCHAR *stmt = (SQLCHAR *)"DELETE FROM org WHERE deptnumb = ? ";
cliRC= SQLSetStmtAttr(hstmt, SQL_ATTR_DEFERRED_PREPARE,
SQL_DEFERRED_PREPARE_ON )
cliRC= SQLPrepare(hstmt, stmt, SQL_NTS);

Now, the ORG table does not exist in the database. What will be the value of "cliRC" after executing the SQLPrepare command?

Select one of the following:

  • SQL_SUCCESS

  • SQL_SUCCESS_WITH_INFO

  • SQL_INVALID_HANDLE

  • SQL_ERROR

Explanation

Question 71 of 100

1

Which steps are necessary to issue a batch update using a single SQL statement? (Note: The statement has several sets of input parameter markers and is issued from a database application using the IBM Data Server Driver for JDBC.)

Select one of the following:

  • Create aPreparedStatement object; add the parameter markers to the batch using the addBatch() method; assign values to each set of parameter marker values; execute the statement using the executeQuery() method.

  • Create a Statement object; add the parameter markers to the batch using theaddBatch() method; assign values to each set of parameter marker values; execute the statement using the executeQuery() method.

  • Create aPreparedStatement object; assign values to each set of parameter marker values; add the parameter markers to the batch using the addBatch() method; execute the statement using the executeBatch() method.

  • Create a Statement object; assign values to each set of parameter marker values; add the parameter markers to the batch using theaddBatch() method; execute the statement using the executeBatch() method.

Explanation

Question 72 of 100

1

Which two C# .NET code snippets illustrate the valid execution of a SQL statement through the IBM Data Server Provider for .NET? (Choose two.)

Select one or more of the following:

  • SqlCommand cmd = new SQLCommand("SELECT fname, lname FROM employee WHERE empno=55",conn);
    SqlDataReaderreader = cmd.ExecuteReader();

  • SqlCommand cmd = new SQLCommand("INSERT INTO employee (fname,lname) VALUES('Susan','Smith')",conn);
    cmd.ExecuteNonQuery();

  • DB2Commandcmd = conn.CreateCommand();
    cmd.CommandText= "SELECT fname, lname FROM employee WHERE empno=55";
    DB2DataReader reader = cmd.ExecuteReader();

  • DB2Commandcmd = DB2Context.GetCommand();
    DB2Transaction trans = conn.BeginTransaction();
    cmd.Transaction= trans;
    cmd.CommandText= "INSERT INTO employee (fname,lname) VALUES('Susan','Smith');
    cmd.ExecuteNonQuery();

Explanation

Question 73 of 100

1

While developing a Java JDBC application, you use the set of statements shown below:
Connection con; PreparedStatement pstmt; int numUpd;
pstmt= con.prepareStatement("UPDATE EMPLOYEE SET PHONENO='4657' WHERE EMPNO=?");
pstmt.setString(0, "000010");
numUpd= pstmt.executeUpdate();
pstmt.close();

Assuming a connection has already been established, which statement is true?

Select one of the following:

  • Statements will be executed successfully.

  • Statements will give a runtime exception.

  • Statements will give a syntax error.

  • Statements will execute successfully, but will not update rows in the database.

Explanation

Question 74 of 100

1

While developing a Java JDBC application, you use the set of statements shown below:

Connection con; Statement stmt; int numUpd;
stmt = con.createStatement();
numUpd= stmt.executeUpdate("UPDATE EMPLOYEE SET PHONENO='4657' WHERE EMPNO=?");
stmt.close();
con.close();

Assuming a connection has already been established, which statement is true?

Select one of the following:

  • Statements will execute successfully, but will not update rows in the database.

  • Statements will give a syntax error.

  • Statements will give a runtime exception.

  • Statements will execute successfully.

Explanation

Question 75 of 100

1

Which two statements are true regarding performing batch updates in JDBC? (Choose two.)

Select one or more of the following:

  • SELECT statements can be included in batch update operations.

  • The addBatch() method can be used to add a set of input parameter values to the batch.

  • ResultSet objects can be used to retrieve values generated by CallableStatement objects in a batch.

  • The executeBatch() method can be used to execute a batch of statements.

Explanation

Question 76 of 100

1

A CLI/ODBC application contains the lines of code shown below:

SQLHANDLE hstmt; /* statement handle */
SQLCHAR *stmt = (SQLCHAR *)"DELETE FROM org WHERE deptnumb = ? ";
SQLSMALLINT parameter1=5;

Which CLI/ODBC API will use the current value of PARAMETER1?

Select one of the following:

  • SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

  • SQLPrepare(hstmt, stmt, SQL_NTS);

  • SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT, SQL_SMALLINT, 0, 0, &parameter1, 0, NULL);

  • SQLExecute(hstmt);

Explanation

Question 77 of 100

1

A PHP script calls a stored procedure that returns more than one result set. After the rows from the first result set are fetched, which function should be used to retrieve the subsequent result sets?

Select one of the following:

  • db2_fetch_assoc()

  • db2_fetch_array()

  • db2_following_result()

  • db2_next_result()

Explanation

Question 78 of 100

1

In a PHP script, which ibm_db2 functions can be used to retrieve the result set of a query executed against a database table?

Select one of the following:

  • db2_next_result()

  • db2_fetch_array()

  • db2_execute()

  • db2_result()

Explanation

Question 79 of 100

1

In a PHP script, which ibm_db2 functions should be used if the user wants to retrieve a row in a result set as an array, indexed by column name?

Select one of the following:

  • db2_fetch_both()

  • db2_fetch_array()

  • db2_fetch_object()

  • db2_fetch_assoc()

Explanation

Question 80 of 100

1

Which CLI/ODBC function would be used to update multiple rows in a table?

Select one of the following:

  • SQLUpdate()

  • SQLExecBatch()

  • SQLExecute()

  • SQLBulkUpdate()

Explanation

Question 81 of 100

1

Which return codes will be returned by SQLFetch() when there are no more records to be retrieved in a result data set?

Select one of the following:

  • SQL_SUCCESS

  • SQL_NO_DATA_FOUND

  • SQL_WARNING

  • SQL_NO_MORE_DATA

Explanation

Question 82 of 100

1

A stored procedure is invoked that can return multiple result sets. After processing the first result set, which CLI/ODBC API can be used to determine if another result set exists?

Select one of the following:

  • SQLMoreResults()

  • SQLFetchScroll()

  • SQLGetInfo()

  • SQLGetData()

Explanation

Question 83 of 100

1

Which two Java objects can be used to produce a result set using a SELECT statement? (Choose two.)

Select one or more of the following:

  • Statement

  • PreparedStatement

  • ResultSet

  • CallableStatement

Explanation

Question 84 of 100

1

A CLI/ODBC application uses the SQL FetchScroll() API to retrieve an updatable row set with five rows of data. Each column of the row set is bound to an array of application memory. The API is called with the SQL_FETCH_NEXT FetchOrientation option and returns SQL_SUCCESS_WITH_INFO. Diagnostics show a truncation SQLSTATE of "01004".

Which API call correctly identifies the record with the failure?

Select one of the following:

  • SQLGetDiagField( hStmt, SQL_DIAG_ROW_COUNT, &(intVal) );

  • SQLGetDiagField( hStmt, SQL_DIAG_ROW_NUMBER, &(intVal) );

  • SQLGetDescField( hStmt, SQL_DESC_COUNT, &(intVal) );

  • SQLGetDescField( hStmt, SQL_DESC_ARRAY_SIZE, &(intVal) );

Explanation

Question 85 of 100

1

An ODBC/CLI application invokes the SQLFetch() API resulting in the return code SQL_SUCCESS_WITH_INFO.
Which two APIs could be used to retrieve the message text explaining the return code? (Choose two.)

Select one or more of the following:

  • SQLGetInfo()

  • SQLGetDiagField()

  • SQLGetWarning()

  • SQLGetDiagRec()

Explanation

Question 86 of 100

1

A PHP application using the ibm_db2 extension encounters an error executing the SQL shown
below:
<?php
$applicant = array( $_POST["userid"] );
$stmt = db2_prepare( $conn, "INSERT INTO hr.applicants (userid) VALUES ( ? )" );
if ($stmt) {
$result = db2_execute( $stmt, $applicant );
if (!result) {
// Handle Errors
}
}
>
Which command retrieves the SQLSTATE explaining the error?

Select one of the following:

  • $this->state = db2_stmt_error();

  • $this->state = $conn->get_error()

  • $this->state = $stmt->get_error();

  • $this->state = db2_conn_error();

Explanation

Question 87 of 100

1

What are two valid values for the PARAMETER STYLE clause in the CREATE PROCEDURE statement? (Choose two.)

Select one or more of the following:

  • C

  • JAVA

  • SQL WITH NULLS

  • GENERAL WITH NULLS

Explanation

Question 88 of 100

1

UPDATE DBM CFG USING FEDERATED YES;
db2stop;
db2start;
CONNECT TO test;
CREATE WRAPPER oledb LIBRARY 'db2oledb';
CREATE SERVER nwind
WRAPPER oledb
OPTIONS (CONNECTSTRING 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=f:Northwind.mdb;', COLLATING_SEQUENCE 'Y');
CREATE FUNCTION nwind.customers()
RETURNS TABLE (customerid CHAR(5),
companynameVARCHAR(40),
contactnameVARCHAR(30),
contacttitleVARCHAR(30),
address VARCHAR(60),
city VARCHAR(15),
region VARCHAR(15),
postalcodeVARCHAR(15),
country VARCHAR(24),
phone VARCHAR(24),
fax VARCHAR(24))
LANGUAGE OLEDB
EXTERNAL NAME 'nwind!customers';
COMMIT;
CONNECT RESET;

A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
Which statement can be used to verify that an OLE DB table function designed to retrieve data from the CUSTOMERS table of the NORTHWIND Microsoft Access database was created successfully?

Select one of the following:

  • SELECT companyname FROM nwind.customers()

  • SELECT companyname FROM TABLE (nwind.customers()) AS t

  • SELECT companyname FROM oledb.nwind.customers()

  • SELECT companyname FROM TABLE (oledb.nwind.customers()) AS t

Explanation

Question 89 of 100

1

CONNECT TO test;
CREATE TABLE colors(id INT NOT NULL PRIMARY KEY,
descVARCHAR(12));
CREATE TABLE objects(desc VARCHAR(12),
c_idINT REFERENCES colors (id)
ON UPDATE NO ACTION);
INSERT INTO colors VALUES (1, 'Red'), (2, 'Blue'), (3, 'Yellow'), (4, 'Green');
INSERT INTO objects VALUES ('Fire Engine', 1), ('Grass', 4), ('Banana', 3);
CONNECT RESET;

A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
The statement shown below is executed: UPDATE colors SET id = id + 1;

How many records will be modified?

Select one of the following:

  • 0

  • 1

  • 2

  • 3

Explanation

Question 90 of 100

1

CONNECT TO test;
CREATE TABLE colors(id INT NOT NULL PRIMARY KEY,
descVARCHAR(12));
CREATE TABLE fruits(desc VARCHAR(12),
c_idINT REFERENCES colors (id)
ON UPDATE RESTRICT);
INSERT INTO colors VALUES (1, 'Red'), (2, 'Yellow'), (3, 'Blue');
INSERT INTO fruits VALUES ('Apple', 1), ('Banana', 2);
CONNECT RESET;
A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.

The statement shown below is executed: UPDATE colors SET id = id - 1;
How many rows will be modified in the COLORS table?

Select one of the following:

  • 0

  • 1

  • 2

  • 3

Explanation

Question 91 of 100

1

CONNECT TO test;
CREATE TABLE colors (id INT NOT NULL PRIMARY KEY,
descVARCHAR(12));
CREATE TABLE objects(desc VARCHAR(12),
c_idINT REFERENCES colors (id)
ON UPDATE NO ACTION);
INSERT INTO colors VALUES (1, 'Red'), (2, 'Yellow'), (3, 'Blue');
INSERT INTO objects VALUES ('Fire Engine', 1), ('Rain Coat', 2);
CONNECT RESET;

A DB2 Command Line Processor script file containing the commands and statements shown in the exhibit was executed successfully.
The statement shown below is executed: UPDATE colors SET id = id - 1;
How many rows will be modified in the COLORS table?

Select one of the following:

  • 0

  • 1

  • 2

  • 3

Explanation

Question 92 of 100

1

Which three are required for a global transaction? (Choose three.)

Select one or more of the following:

  • Client Application

  • Transaction Manager

  • Workload Manager

  • Application Coordinator

  • Resource Manager

Explanation

Question 93 of 100

1

A company has a requirement that will update data on multiple databases. The data currently resides on databases that are part of the DB2 family of products. What would be two minimum configurations needed to facilitate the correct update on all database servers without loss of data integrity? (Choose two.)

Select one or more of the following:

  • Configure DB2 as a Transaction Manager on the database server.

  • Configure an XA-compliant Transaction Manager on the client application server.

  • Configure DB2 as a Transaction Manager on the client application server.

  • Configure the Sync Point Manager on the database server.

Explanation

Question 94 of 100

1

When creating a trusted connection in CLI/ODBC, it is necessary to set a connection attribute. What is the connection attribute?

Select one of the following:

  • SQL_ATTR_TRUSTED_CONTEXT_USERID

  • SQL_ATTR_TRUSTED_CONTEXT_PASSWORD

  • SQL_ATTR_USE_TRUSTED_CONTEXT

  • SQL_ATTR_ENABLE_TRUSTED_CONTEXT

Explanation

Question 95 of 100

1

What are two different types of federated trusted connections? (Choose two.)

Select one or more of the following:

  • catalog federated trusted connections

  • end-to-end federated trusted connections

  • proxy federated trusted connections

  • outbound federated trusted connections

Explanation

Question 96 of 100

1

Given the table COURSES shown below:
COURSES
-------------------------------------
ID CODE NAME
1 ECE100 Operating Systems
2 ECE101 Programming Languages
3 ECE102 Intro to Databases
User USER1 executes the following statements:
DECLARE GLOBAL TEMPORARY TABLE tempcourses
LIKE db2user.courses;
INSERT INTO session.tempcourses SELECT * FROM db2user.courses;
SELECT max(ID) FROM session.tempcourses;

Assuming autocommit is on, which result will user USER1 obtain?

Select one of the following:

  • NO VALUE

  • VALUE 1

  • VALUE 2

  • VALUE 3

Explanation

Question 97 of 100

1

Given the table COURSES shown below:
COURSES
----------------------------------------
ID CODE NAME
1 ECE100 Operating Systems
2 ECE101 Programming Languages
3 ECE102 Intro to Databases
User USER1 executes the following statements:
DECLARE GLOBAL TEMPORARY TABLE tempcourses
LIKE db2user.courses
ON COMMIT PRESERVE ROWS;
INSERT INTO session.tempcourses SELECT * FROM db2user.courses;
SELECT max(ID) FROM session.tempcourses;

Assuming autocommit is on, which result will user USER1 obtain?

Select one of the following:

  • VALUE 1

  • VALUE 2

  • VALUE 3

  • NO VALUE

Explanation

Question 98 of 100

1

Given the table shown below:
COURSES
ID CODE NAME
1 ECE100 Operating Systems
2 ECE101 Programming Languages
User USER1 executes the following statements:
DECLARE GLOBAL TEMPORARY TABLE tempcourses
LIKE db2user.courses
ON COMMIT PRESERVE ROWS;
INSERT INTO session.tempcourses SELECT * FROM db2user.courses;
INSERT INTO session.tempcourses VALUES (3, 'ECE102', 'Databases');
SELECT max(ID) FROM db2user.courses;
User USER2 executes the following statements:
DECLARE GLOBAL TEMPORARY TABLE tempcourses
LIKE db2user.courses;
ON COMMIT PRESERVE ROWS;
INSERT INTO session.tempcourses SELECT * FROM db2user.courses;
INSERT INTO session.tempcourses VALUES (4, 'ECE103', 'Algorithms');
SELECT max(ID) FROM session.tempcourses;

Which results do USER1 and USER2 obtain?

Select one of the following:

  • User USER1 obtains the value 2; User USER2 obtains the value 3

  • User USER1 obtains the value 3; User USER2 obtains the value 4

  • User USER1 obtains the value 2; User USER2 obtains the value 4

  • User USER1 obtains no value; User USER2 obtains no value

  • User USER1 obtains the value 2; User USER2 obtains the value 2

Explanation

Question 99 of 100

1

A table and a sequence were created with the statements shown below:

CREATE TABLE orders(oid INTEGER, info VARCHAR(200));
CREATE SEQUENCE order_num AS INTEGER
START WITH 1;
Autocommitis on and the statements shown are executed:
INSERT INTO orders VALUES (NEXT VALUE FOR order_num, 'Order A');
INSERT INTO orders VALUES (PREVIOUS VALUE FOR order_num, 'Order B');
SELECT * FROM orders;

What is the output?

Select one of the following:

  • OID INFO
    1 Order A
    1 Order B

  • OID INFO
    0 Order A
    1 Order B

  • OID INFO
    1 Order A
    2 Order B

  • OID INFO
    2 Order A
    2 Order B

Explanation

Question 100 of 100

1

What is the final result of executing the statements shown below?
CREATE SEQUENCE order_seq
START WITH 100
INCREMENT BY 25
MAXVALUE 150
CYCLE;
VALUES NEXT VALUE FOR order_seq;
VALUES NEXT VALUE FOR order_seq;
VALUES NEXT VALUE FOR order_seq;
VALUES NEXT VALUE FOR order_seq;

Select one of the following:

  • 100

  • 125

  • 150

  • 175

Explanation

Previous
Next