C2090-545 Online Practice Questions and Answers

Click the Exhibit button.

A user-defined function was created using the statement shown in the exhibit.

Which additional option can be added to the CREATE FUNCTION statement to tell the optimizer that the function doesnot always return the same results for a given argument value?

A. NO EXTERNAL ACTION

B. NOT FENCED

C. NOT DETERMINISTIC

D. STATIC DISPATCH

What is a reason to use SQL procedures in DB2?

A. to use different programming languages

B. to reduce code sharing

C. to move business logic rules into the database

D. to eliminate the need for testing

Which two statements are true with regards to nesting SQL procedures? (Choose two.)

A. An SQL procedure cannot invoke procedures built in other languages (e.g., C, Java).

B. An SQL procedure can invoke the same SQL procedure more than once.

C. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with the READS SQL DATA clause.

D. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with the MODIFIES SQL DATA clause.

E. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQL procedure defined with the NO SQL clause.

Which statement can be used to define variable that can beshared between procedures?

A. DECLARE my_gl_var INTEGER DEFAULT 1

B. CREATE OR REPLACE VARIABLE my_gl_var INTEGER

C. DEFINE GLOBAL VARIABLE my_gl_var INTEGER

D. CREATE GLOBAL VARIABLE my_gl_var

Which of the following statements is true for declared global temporary tables?

A. Declared global temporary table descriptions are stored in the system catalog

B. When a session terminates, all declared global temporary tables created in the session are dropped.

C. Declared global temporary tables are persistent and can be shared with multiple sessions.

D. Declared temporary tables support LONG VARCHAR columns.

Which statement will successfully create an SQL procedure that returns the name of the current month?

A. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20)) BEGIN DECLARE today DATE; SET (today = CURRENT_DATE); SET month = MONTHNAME(today); END

B. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20)) BEGIN DECLARE today DATE; SELECT (CURRENT_DATE) INTO today; SET month = MONTHNAME(today); END

C. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20)) BEGIN DECLARE today DATE; VALUES (CURRENT_DATE) INTO today; SET month = MONTHNAME(today); END

D. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20)) BEGIN SET month = MONTHNAME(SELECT (CURRENT_DATE)) END

What values are generated each time an SQL statement is executed?

A. SQLERRN and SQLMSG

B. SQLSTATE and EXCEPTION

C. EXCEPTION and SQLCODE

D. SQLSTATE and SQLCODE

Assuming EMP_ROW and NEW_HIRE have the same user-defined row data type, what is the best way to assign the elements of NEW_HIRE to EMP_ROW?

A. emp_row.rowid = new_hire.rowid;

B. emp_row.all = new_hire.all;

C. SET emp_row = new_hire;

D. SET emp_row.* = new_hire.*;

Click the Exhibit button.

Which statement is true about the CASE statement shown in the exhibit?

A. An employee with a rating of 1 receives a 10% salary increase.

B. An employee with a rating of 3 receives no salary increase.

C. An employee with a rating of 2 receives a 3%salary increase.

D. All employees will receive at least a 5% salary increase.

Click the Exhibit button.

Which statement correctly describes the result of the FOR loop shown in the exhibit?

A. FULLNAME isset to the last name of the employee, followed by a comma, the first name, a blank space, and the middle initial. Only the last value for FULLNAME is inserted into table TNAMES.

B. FULLNAME is set to the last name of the employee, followed by a comma, thefirst name, a blank space, and the middle initial. Only the first value for FULLNAME is inserted into table TNAMES.

C. FULLNAME is set to the last name of the employee, followed by a comma, the first name, a blank space, and the middle initial for each row. Each value for FULLNAME is inserted into table TNAMES in alphabetical order.

D. FULLNAME is set to the last name of the employee, followed by a comma, the first name, a blank space, and the middle initial for each row. Each value for FULLNAME is insertedinto table TNAMES.

Which statement will create a scalar function named FCN1?

A. CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS CHAR(3) ?CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS CHAR(3)

B. CREATE FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3) ?CREATE FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3)

C. CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3) ?CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3)

D. CREATE FUNCTION fcn1(v1 CHAR(3)) RETURNS CHAR(3) ?CREATE FUNCTIONfcn1(v1 CHAR(3)) RETURNS CHAR(3)

Given the following procedures:

What would be value of the variable TOTAL_SCORE if the PROCESS_SCORES procedure is called with the value 23 provided as input?

A. 0

B. 6B.6

C. 23C.23

D. 10D.10

Which of the following steps are required to debug a stored procedure using IBM Data Studio?

A. Deploy the stored procedure from the data development project, debug the stored procedure.

B. Debug the stored procedure from the Data Source Explorer.

C. Deploy the stored procedure from the data development project, choose option for debugging and then debug.

D. Debug the stored procedure from the IBM Data Studio command line console.

In the code segment shown below:

Which statement accurately describes the result?

A. For activities whose description has not changed, update the description in the ARCHIVE table. For new activities, insert into the ARCHIVE table. The ARCHIVE and ACTIVITIES table both have ACTIVITY as a primary key.

B. For activities whose description has changed, update the description in the ARCHIVE table. For new activities, insert into the ARCHIVE table. The ARCHIVE and ACTIVITIES tableboth have ACTIVITY as a primary key.

C. For activities whose description has changed, update the description in the ACTIVITY table. For new activities, insert into the ACTIVITIES table. The ARCHIVE and ACTIVITIES table both have ACTIVITY as a primary key.

D. The statement will fail since MERGE is not a supported operator.

Which statement is true about the NUMBER data type?

A. In DB2, theNUMBER data type has maximum precision of 31; in Oracle, it has a maximum precision of 38.

B. In DB2, the NUMBER data type is the same as in Oracle, provided the DB2 database was created in Oracle compatibility mode.

C. Declaring a column as NUMBER(10,-2)is valid both in Oracle and in DB2 provided the DB2 database was created in Oracle compatibility mode.

D. Declaring a column as NUMBER(6,8) is valid both in Oracle and in DB2 provided the DB2 database was created in Oracle compatibility mode.