Question 1.
Given the following code:
EXEC SQL EXECUTE IMMEDIATE :sqlstmt

Which of the following values must sqlstmt contain so that all rows are deleted from the STAFF table?

A. DROP TABLE staff
B. DELETE FROM staff
C. DROP * FROM staff
D. DELETE * FROM staff

Answer: B

Question 2.
Given the code:
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT name,age,b_date FROM person;
EXEC SQL OPEN cursor1;

Under which of the following situations will the above cursor be implicitly closed?

A. When a CLOSE statement is issued
B. When a COMMIT statement is issued
C. When there are no rows in the result set
D. When all rows are FETCHed from the result set

Answer: B

Question 3.
To prepare an embedded SQL program for use with a host-language compiler, which of the following database components is required?

A. Binder
B. Precompiler
C. Stored Procedure Builder
D. Application Development Center

Answer: B

Question 4.
An ODBC/CLI application has the following pseudocode:
SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc )
SQLConnect( hDbc, "CERTDB", SQL_NTS, "user1", SQL_NTS, "passwd", SQL_NTS )
SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt )
SQLExecDirect( hStmt, "SET CURRENT SQLID db2cert", SQL_NTS )
Assuming all of the above calls execute successfully, which of the following fully qualified SQL statements is equivalent to:
SQLExecDirect( hStmt, "SELECT * FROM user1.table1 t1, table2 t2 WHERE t1.col1=t2.col2", SQL_NTS )

A. SQLExecDirect( hStmt, "SELECT * FROM user1.table1 t1, table2 t2 WHERE t1.col1=t2.col2", 
     SQL_NTS )
B. SQLExecDirect( hStmt, "SELECT * FROM user1.table1 t1, user1.table2 t2 WHERE 
    t1.col1=t2.col2", SQL_NTS )
C. SQLExecDirect( hStmt, "SELECT * FROM user1.table1 t1, db2cert.table2 t2 WHERE 
    t1.col1=t2.col2", SQL_NTS )
D. SQLExecDirect( hStmt, "SELECT * FROM db2cert.table1 t1, db2cert.table2 t2 WHERE 
    t1.col1=t2.col2", SQL_NTS )

Answer: C

Question 5.
Given the EMPLOYEE table definition:
CREATE TABLE employee (
workdept CHAR(30),
salary INTEGER
)
Which of the following is a correct usage of parameter markers in a JDBC program?

A. String sql =
"UPDATE employee SET salary=salary*?WHERE workdept=?";
statement stmt=con.createStatement(sql);
B. String sql =
"UPDATE employee SET salary=salary*?WHERE workdept=?";
statement stmt=con.createStatement(sql);
C. String sql =
"UPDATE employee SET salary=salary*?WHERE workdept=?";
statement stmt=con.createStatement(sql);
D. String sql =
"UPDATE employee SET salary=salary*?WHERE workdept=?";
statement stmt=con.createStatement(sql);

Answer: C

Question 6.
Given the following code:
BEGIN ATOMIC
UPDATE country SET cities=:count WHERE CURRENT OF C1;
INSERT INTO country VALUES(:co11,:co12,:co13);
INSERT INTO country VALUES(:co14,:co15,:co16);
INSERT INTO country VALUES(:co17,:co18,:co19);
INSERT INTO country VALUES(:co110,:co111,:co112);
COMMIT;
END
Given that all statements succeed except the following:
INSERT INTO country VALUES(:co17,:co18,:co19);

How many rows will be affected in table COUNTRY?

A. 0
B. 3
C. 4
D. 5

Answer: A

Question 7.
The following commands are issued against a data source containing table user2.org:
CREATE ALIAS user1.org FOR sample.org
CREATE TABLE org.sample ( c CHAR(1))
CREATE ALIAS sample.org FOR user2.org
CREATE ALIAS user2.sample FOR sample.org
Given the user SAMPLE issues the following statement:
SELECT * FROM sample

For which of the following database objects will access be attempted?

A. user2.org
B. org.sample
C. sample.org
D. sample.sample

Answer: D

Question 8.
The following commands are issued against a data source:
CREATE TABLE userid.org ( i INT)
CREATE ALIAS user1.org FOR userid.org
CREATE TABLE org.sample ( c CHAR(1))
CREATE ALIAS sample.org FOR userid.org
CREATE ALIAS userid.sample FOR sample.org

When issued by USER1, which of the following statements will have a different result set than this SELECT statement?
SELECT * FROM org

A. SELECT * FROM org.sample
B. SELECT * FROM sample.org
C. SELECT * FROM userid.org
D. SELECT * FROM userid.sample

Answer: A

Question 9.
An application uses embedded dynamic SQL to connect to a remote DB2 server and inserts data into the CUST.ORDERS table on that remote DB2 server. To enable access of the remote DB2 server, Administrator FOO needs to create a package with default options such that BAR is the only non-administrative user that can use this package on the remote DB2 server.

Which statement describes the privileges that must be granted and/or revoked by FOO to accomplish this?

A. BAR requires EXECUTE privilege on the package and UPDATE privilege on CUST.ORDERS, 
    and the EXECUTE privilege for the package must be revoked from PUBLIC.
B. BAR requires EXECUTE privilege on the package and INSERT privilege on CUST.ORDERS, 
    and the EXECUTE privilege for the package must be revoked from PUBLIC.
C. BAR requires EXECUTE privilege on the package and INSERT privilege on CUST.ORDERS, 
    and the REFERENCES privilege for the package must be revoked from PUBLIC.
D. BAR requires EXECUTE privilege on the package and UPDATE privilege on CUST.ORDERS, 
    and the REFERENCES privilege for the package must be revoked from PUBLIC.

Answer: B

Question 10.
User FOO must create and own an SQL stored procedure, CUST.CALC1, that returns a calculation using static SQL. Both user FOO and user BAR need to call this stored procedure from each of their applications. 

Assuming FOO has the necessary privileges to perform the calculation, which two of the following statements describe the MINIMUM privileges that must be explicitly granted for users BAR and FOO?

A. BAR needs EXECUTE privilege on the CALC1 package
B. BAR needs EXECUTE privilege on the CALC1 procedure
C. FOO needs EXECUTE privilege on the CALC1 procedure
D. FOO and BAR need EXECUTE privilege on the CALL statement
E. FOO needs the privilege to create the procedure in the CUST schema

Answer: BE

Question 11.
An application uses static SQL to connect to a remote DB2 server and inserts data into the CUST.ORDERS table on that remote DB2 server. To enable access to the remote DB2 server, FOO needs to create a package with default options so that BAR is the only non-administrative user that can use this package on the remote DB2 server.

Which statement describes the privileges that FOO requires to accomplish this?

A. FOO requires EXECUTE privilege on the package.
B. FOO requires the privilege to create the package on the remote DB2 server.
C. FOO requires EXECUTE privilege on the package and INSERT privilege on CUST.ORDERS.
D. FOO requires the privilege to create the package on the remote DB2 server and INSERT 
    privilege on CUST.ORDERS.

Answer: D

	Web www.certsbraindumps.com