db2 udb v8.1 familu application development

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
Correct:B
2.Which of the following CLI/ODBC functions should be used to delete rows from a DB2 table?
A.SQLDelete()
B.SQLExecDirect()
C.SQLBulkDelete()
D.SQLExecuteUpdate()
Correct:B
3.An ODBC/CLI application executes the following batch SQL: SQLExecDirect( hStmt, "SELECT c1 FROM t1; SELECT c2 FROM t2;" SQL_NTS ); Which API is used to discard the first result set and make the second available for processing?
A.SQLFetch()
B.SQLRowCount()
C.SQLMoreResults()
D.SQLCloseCursor()
Correct:C
4.Given the tables T1 and T2 with INTEGER columns:

A.0
B.2
C.3
D.6
Correct:B
5.Given the table T1 with the following data:

A.A
B.B
C.C
D.D
Correct:C
6.Given an ODBC/CLI program with a single connection, two threads and the following actions which complete successfully: Thread 1: INSERT INTO mytab VALUES (1) Thread 2: INSERT INTO mytab VALUES (2) Thread 1: COMMIT Thread 2: INSERT INTO mytab VALUES (3) Thread 1: ROLLBACK Thread 2: COMMIT How many records will be inserted and retained in the table MYTAB?
A.0
B.1
C.2
D.3
Correct:C
7.Given the table T1 with the following data:

A.10
B.20
C.30
D.40
Correct:D
8.Using Read Stability isolation level, Application A updates row X and does NOT issue a COMMIT. Using Uncommitted Read isolation level, Application B attempts to read row X. What is the result of Application B's attempt to read row X?
A.Application B will receive a lock timeout error.
B.Application B will wait until Application A completes its unit of work.
C.Application B will receive the version of row X that exists after Application A's update.
D.Application B will receive the version of row X that existed prior to Application A's update.
Correct:C
9.Which of the following JDBC interfaces has methods that permit the use of parameter markers?
A.ResultSet
B.Statement
C.Connection
D.CallableStatement
Correct:D
10.Which of the following will retrieve results that will only be in lower case?
A.SELECT NAME FROM EMPLOYEE WHERE NAME='ali'
B.SELECT NAME FROM EMPLOYEE WHERE LCASE(NAME)='ali'
C.SELECT UCASE(NAME) FROM EMPLOYEE WHERE LCASE(NAME)='ali'
D.SELECT NAME FROM EMPLOYEE WHERE NAME IN (SELECT NAME FROM EMPLOYEE WHERE LCASE(NAME)=LCASE('ALI'))
Correct:A
11.Given the following SQL Stored Procedure:

A.'Start' 'Handler Fired'
B.'Start' 'Handler Fired' 'End'
C.'Start' 'Handler Fired' 'Error'
D.'Start' 'Handler Fired' 'Error' 'End'
Correct:B
12.Given the expression: WITH most_cities AS ( SELECT b.id,b.name,a.cities FROM country a, staff b WHERE a.person = b.id AND cities > :threshold ) SELECT * FROM most_cities In which of the following does MOST_CITIES exist?
A.user tables
B.server memory
C.user table space
D.system catalog tables
Correct:B
13.The following commands are issued against a data source containing table user2.org:

A.user2.org
B.org.sample
C.sample.org
D.sample.sample
Correct:D
14.Given the tables:

A.1
B.2
C.3
D.4
E.5
Correct:D
15.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
Correct:B
16.A JDBC connection is established as follows: Connection con = DriverManager.getConnection("jdbc:db2:sample"); If the connection does not throw any exceptions, which of the following code pieces determine if there are any warnings?
A.SQLWarning warn = con.getWarnings(); if (warn != null ) { /* There were warnings */ };
B.SQLWarning warn = con.getExceptions(); if (warn != null ) { /* There were warnings */ };
C.SQLException warn = DriverManager.getWarnings(); if (warn != null ) { /* There were warnings */ };
D.SQLException warn = con.getExceptions(); if (warn != null ) { /* There were warnings */ };
Correct:A
17.Which of the following can modify the contents of host variables within a program?
A.Only SQL statements
B.Only using the DECLARE section
C.Program statements and SQL statements
D.Program statements but not SQL statements
Correct:C
18.Which two of the following ODBC/CLI API function calls can be used to retrieve diagnostic information?
A.SQLGetMsg
B.SQLGetError
C.SQLGetDiagRec
D.SQLGetErrorRec
E.SQLGetDiagField
Correct:C E
19.Given the table T1, created using the following statement:

A.1, 2, 2
B.1, 2, 3
C.2, 3, 4
D.0, 1, 2
Correct:A
20.Given the tables, the relationships and the statements:

A.One row is inserted in EMPLOYEE
B.No rows are inserted in EMPLOYEE
C.Two rows are inserted in EMPLOYEE
D.One row is inserted in DEPT and 2 rows are inserted in EMPLOYEE
Correct:A
21.A programmer wants to create an applet that connects to a DB2 UDB database from the web browser. Which two of the following can be used?
A.CLI
B.SQLJ
C.JDBC
D.ODBC
E.OLEDB
Correct:B C
22.A cursor is declared with the WITH HOLD option. Which of the following statements is always true?
A.The cursor will remain open after a COMMIT.
B.All rows retrieved are locked until a COMMIT.
C.A COMMIT will not be allowed until the cursor is closed.
D.Locks obtained by the cursor will be kept after a COMMIT.
Correct:A
23.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.
Correct:D
24.A column is defined as INTEGER and contains the following values in no particular order: -1, 0, 1, NULL How many host variables must be included in a DECLARE section in order to select all the values into the program one row at a time?
A.1
B.2
C.3
D.4
Correct:B
25.Given the following table: CREATE TABLE employee (name CHAR(10), salary DEC NOT NULL WITH DEFAULT) INSERT INTO employee (name, salary) VALUES ('SMITH', 30000) INSERT INTO employee (name) VALUES ('JONES') INSERT INTO employee (name, salary) VALUES ('ALI', 35000) Which of the following statements will retrieve more than one row?
A.SELECT salary FROM employee WHERE salary IN (SELECT (salary/(SELECT SUM(salary) FROM employee)) FROM employee)
B.SELECT COALESCE(AVG(salary)) FROM employee
C.SELECT SUM(salary)/COUNT(*) FROM employee
D.SELECT salary/(SELECT SUM(salary) FROM employee) FROM employee
Correct:D
26.Given the following ODBC/CLI code that executes successfully: SQLRETURN rc; SQLHANDLE hEnv, hDbc, hStmt; rc = SQLAllocHandle( SQL_HANDLE_ENV, NULL, &hEnv ); rc = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc ); rc = SQLConnect( hDbc, "CERTDB", SQL_NTS, NULL, NULL, NULL, NULL ); Which of the following calls is expected to return SQL_SUCCESS?
A.rc = SQLExecute( hStmt );
B.rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
C.rc = SQLPrepare( hStmt, "SELECT * FROM SYSIBM.SYSTABLES", SQL_NTS );
D.rc = SQLSetStmtAttr( hStmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_STATIC, NULL );
Correct:B
27.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
Correct:B
28.Given the following table T1 containing an INTEGER column:

A.SQLFetch()
B.SQLPrepare()
C.SQLExecute()
D.SQLSetStmtAttr()
E.SQLBindParameter()
Correct:D
29.Which two (2) of the following are correct embedded SQL syntax for connecting to a database?
A.EXEC SQL CONNECT TO sample;
B.EXEC SQL CONNECT TO :dbalias :uid :passwd;
C.EXEC SQL CONNECT TO sample USER ? USING ?;
D.EXEC SQL CONNECT TO :dbalias USER :uid USING :passwd;
E.EXEC SQL CONNECT TYPE 2 TO sample USER :uid USING :passwd;
Correct:A D
30.By default, when an ODBC application connects to DB2, the SQL_ATTR_CURSOR_HOLD attribute is set to SQL_CURSOR_HOLD_ON. Given an ODBC application that connected to a local database called TARGETDB, which of the following commands could be used to change this default behavior?
A.SQL_ATTR_CONCURRENCY
B.SQL_ATTR_CURSOR_TYPE
C.SQL_ATTR_RETRIEVE_DATA
D.SQL_ATTR_CURSOR_SENSITIVITY
Correct:D