C2090-543 - DB2 9.7 Application Development Exam

Go back to IBM

Example Questions

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? 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? What are two valid values for the PARAMETER STYLE clause in the CREATE PROCEDURE statement? (Choose two.) Which three data types are supported as output of the XMLSERIALIZE function? (Choose three.) 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? Which two statements are true regarding performing batch updates in JDBC? (Choose two.) Which two commands can be used to make use of the latest statistics? (Choose two.) 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 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? In a PHP script, which ibm_db2 functions can be used to retrieve the result set of a query executed against a database table? 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? Which statement should be used to obtain the result set shown below? <Root> <First>Hello</First> <Second>World !</Second> <?Test Pass test?> </Root> 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? A library keeps digital copies of books in the table shown below: CREATE TABLE library.collection ( bookid INTEGER NOT NULL PRIMARY KEY, ebook_content BLOB(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.) 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? Which CLI/ODBC function would be used to update multiple rows in a table? You have created a stored procedure (MULTIRESULTS()) on the server which will return multiple result sets. Whiledeveloping 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? 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? 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? Which two Java objects can be used to produce a result set using a SELECT statement? (Choose two.) Given the SQL statement shown below: UPDATE employee SET lastname = CAST(? AS VARCHAR(12)) WHERE empno = ?00050?? What does CAST(? AS VARCHAR(12)) represent? Which object can be invoked on a single invocation to return both output parameters and a result set back to the invoking application? 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? 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? 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? Which two techniques guarantee that XML document validation will be performed? (Choose two.) Which condition will prevent a developer from using the DB2 Call Level Interface in an application? 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; Autocommit is 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? Which standard is used for XML document validation in DB2? 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? 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? 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? 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? Which two C# .NET code snippets illustrate the valid execution of a SQL statement through the IBM Data Server Provider for .NET? (Choose two.) 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? 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? Which three are required for a global transaction? (Choose three.) 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? When creating a trusted connection in CLI/ODBC, it is necessary to set a connection attribute. What is the connection attribute? An application must insert values into a column that has a TIMESTAMP data type. What is a valid string representation of a TIMESTAMP? For which two database objects can an ALIAS be created? (Choose two.) Which return codes will be returned by SQLFetch() when there are no more records to be retrieved in a result data set? 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? 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; In a query, which clause can reference a CLOB data type? Given the two 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? 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? 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.) 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? 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;

Study Guides