Sample Test

Previous page
Table of content
Next page
Team-Fly    
 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Appendix A.  IBM Certified Solutions ExpertDB2 UDB Family Application Development

Below is a sample test that has questions very similar to those on the actual 514 exam.

1:

Given that the message "QUALIFY.X" is an undefined name is returned during a precompile, which of the following is true for QUALIFY.X?

  1. It is defined as a database object.

  2. It is defined as an "undefined" name.

  3. It is not defined as a host variable in the program.

  4. It is not defined as a database object in the database.

A1:

D

2:

Given a table created using the statement CREATE TABLE abc.stuff (i INT), a user called XYZ is to be enabled to access data from table ABC.STUFF using an implicit schema. Assuming the necessary privileges have been granted, which of the following statements issued by user ABC will provide this result?

  1. CREATE ALIAS xyz.stuff FOR abc.stuff

  2. CREATE VIEW abc.stuff FOR xyz.stuff

  3. CREATE ALIAS abc.stuff FOR xyz.stuff

  4. CREATE VIEW stuff AS SELECT i FROM abc.stuff

A2:

A

3:

The FINANCE application contains static SQL and issues the statements 

 SELECT name, id FROM prod.employee  UPDATE prod.dept SET dept=dept+1 INSERT INTO prod.dept VALUES(:id, :name)

Assuming a user can connect to the database, but has no other privileges or authorities, which of the following privileges must be granted to the user so that the application FINANCE can be run by a user?

  1. EXECUTE privilege on FINANCE

  2. RUN privilege on tables EMPLOYEE and DEPT

  3. EXECUTE privilege on tables EMPLOYEE and DEPT

  4. CONTROL privilege on tables EMPLOYEE and DEPT

A3:

A

4:

Which of the following privileges is required to successfully execute a DB2 Call Level Interface application?

  1. The user must have EXECUTE privilege on the application.

  2. The developer must have EXECUTE privilege on the application.

  3. The user must have sufficient privileges on referenced tables.

  4. The developer must have sufficient privileges on referenced tables.

A4:

C

5:

Assume that the following SQL statements have been successfully issued: 

 CONNECT TO db1 USER user1 USING pw1  DECLARE GLOBAL TEMPORARY TABLE temp1 (val INTEGER)

Which of the following statements will successfully insert a row into the temporary table TEMP1?

  1. INSERT INTO db1.temp1 VALUES (100)

  2. INSERT INTO user1.temp1 VALUES (100)

  3. INSERT INTO session.temp1 VALUES (100)

  4. INSERT INTO temporary.temp1 VALUES (100)

A5:

C

6:

Given the tables 

 EMPLOYEE                                DEPT  emp_num   emp_name  dept                dept_id      dept_name   1         Adams     1                   1           Planning   2         Jones     1                   2            Support   3         Smith     2   4         Williams  1

and the statement 

 ALTER TABLE employee    ADD FOREIGN KEY (dept) REFERENCES dept (dept_id)   ON DELETE CASCADE

how many rows will be deleted with the following statement? 

 EXEC SQL DELETE FROM dept WHERE dept_id=1

  2. 1

  3. 2

  4. 3

  5. 4

  6. 6

A6:

E

7:

Given the tables 

 COUNTRY                                      STAFF    id  name       person cities               id   name   1   Argentina  1      10                   1    Aaron   2   Canada     2      20                   2    Adams   3   Cuba       2      10   4   Germany    1       0   5   France     7       5

and the code 

 EXEC SQL DECLARE CURSOR C1 FOR      SELECT b.name, cities     FROM country a, staff b     WHERE a.person=b.id     ORDER BY 1,2; EXEC SQL OPEN C1; EXEC SQL FETCH C1 INTO :name1:n_ni, :city1:c_ni; EXEC SQL FETCH C1 INTO :name1:n_ni, :city1:c_ni; EXEC SQL FETCH C1 INTO :name1:n_ni, :city1:c_ni;

which of the following is the value of :city1 after the third FETCH?

  2. 1

  3. 2

  4. 10

  5. 20

A7:

D

8:

Given the tables 

 PRICE                               SALE    item description cost code        sale_id    salefactor   1    camera      10   2              1       2   2    watch        5   1              2       3   3    shirt       20   1

and the SQL statement 

 SELECT a.cost, (a.cost * b. salefactor) AS saleprice    FROM sale b, price a   WHERE a.code=b.sale_id   ORDER BY a.cost

which of the following is the value of SALEPRICE in the second row of the result set returned by the above query?

  1. 10

  2. 15

  3. 20

  4. 30

  5. 40

A8:

D

9:

Given the tables: 

 ORG                                  STAFF    id   name         person           id   name   1    Programming    1              1    Aaron   2    Testing        2              2    Adams   2    Testing        3              3    Jones

and the code 

 stmt="DELETE FROM staff WHERE name='Adams' ";  EXEC SQL EXECUTE IMMEDIATE :stmt;

if a referential integrity constraint with the SET NULL option exists between person in ORG and id in STAFF, how many rows will be deleted?

  2. 1

  3. 2

  4. 3

A9:

B

10:

Which of the following CLI/ODBC functions will return the number of rows affected by an INSERT, UPDATE, or a DELETE statement?

  1. SQLNumRows()

  2. SQLRowCount()

  3. SQLNumParams()

  4. SQLUpdateCount()

  5. SQLNumResultCols()_

A10:

B

11:

Given the following code from a SQLJ source file, 

 Connection con = DriverManager.getConnection(url);  DefaultContext ctx = new DefaultContext(con); DefaultContext.setDefaultContext(ctx);

an UPDATE statement is issued using the ctx context. Which of the following SQLJ methods must be used to obtain the number of rows modified by the UPDATE statement?

  1. ctx.rowsUpdated()

  2. ctx.SQLRowCount()

  3. ctx.getUpdateCount()

  4. con.getUpdateCount()

A11:

C

12:

In order to bind application variables to data in the result set, which two of the following CLI/ODBC functions are used?

  1. SQLFetch()

  2. SQLBindCol()

  3. SQLGetData()

  4. SQLBindResult()

  5. SQLBindParameter()

A12:

B & E

13:

Which of the following function types can only be specified in the FROM clause of a SELECT statement?

  1. TABLE function

  2. COLUMN function

  3. SCALAR function

  4. SELECT function

A13:

A

14:

Which of the following is similar to a common table expression?

  1. A temporary view

  2. A correlated query

  3. A qualified predicate

  4. An update statement

A14:

A

15:

Given the following statement, 

 DECLARE cursor1 CURSOR FOR  SELECT firstname, lastname, street, city FROM citytable

which of the following SQL statements will retrieve a row using the defined cursor?

  1. FETCH cursor1 INTO :hvfirst :hvlast :hvstreet :hvcity

  2. SELECT cursor1 INTO :hvfirst :hvlast :hvstreet :hvcity

  3. FETCH cursor1 INTO :hvfirst, :hvlast, :hvstreet, :hvcity

  4. SELECT cursor1 INTO :hvfirst, :hvlast, :hvstreet, :hvcity

A15:

C

16:

Given the code 

 EXEC SQL UPDATE t1 SET column1 = :col WHERE CURRENT OF C1;

which of the following indicates how the cursor C1 has been defined?

  1. As a normal cursor

  2. As a with hold cursor

  3. As a fetch only cursor

  4. As an updateable cursor

A16:

D

17:

Which of the following defines when a cursor is considered a deleteable cursor?

  1. When the DECLARE CURSOR includes a FOR FETCH ONLY clause

  2. When the DECLARE CURSOR select statement includes an ORDER BY

  3. When the DECLARE CURSOR select statement includes a HAVING clause

  4. When the DECLARE CURSOR select statement references one base table

A17:

D

18:

Given the pseudocode 

 DECLARE CURSOR C1  DECLARE CURSOR C2 OPEN C1 FETCH C1 OPEN C2 COMMIT FETCH C2

which of the following changes will ensure that the FETCH C2 is successful?

  1. BLOCKING ALL during bind

  2. WITH HOLD on COMMIT statement

  3. WITH HOLD on DECLARE CURSOR C2

  4. WITH HOLD on OPEN C2 statement

A18:

C

19:

Which of the following is a characteristic of all cursors in embedded SQL?

  1. Cannot span units of work

  2. Can be reserved in the database

  3. Must be unique within a source module

  4. Must be unique for all applications against a database

A19:

C

20:

A cursor called c2 must be created to fetch rows from table t2 and update column c1 in table t2 for every row fetched . Which of the following cursor definitions will create this type of cursor?

  1. DECLARE c2 CURSOR WITH HOLD FOR SELECT * FROM t2;

  2. DECLARE c2 CURSOR FOR SELECT * FROM t2 FOR UPDATE OF t2;

  3. DECLARE c2 CURSOR FOR SELECT * FROM t2 FOR UPDATE OF c2;

  4. DECLARE c2 CURSOR FOR SELECT * FROM c1 FOR UPDATE OF c1;

  5. DECLARE c2 CURSOR FOR SELECT * FROM t2 FOR UPDATE OF c1;

A20:

E

21:

Given a cursor c1 on table t1, for every row fetched from t1, open a cursor c2 on table t2. For every row fetched from t2, update column c1 in table t2 and issue a COMMIT. Which of the following must define cursor c1?

  1. DECLARE c1 CURSOR FOR SELECT * FROM t1;

  2. DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM t1;

  3. DECLARE c1 CURSOR FOR SELECT * FROM t1 FOR UPDATE OF t2;

  4. DECLARE c1 CURSOR FOR SELECT * FROM t1 FOR UPDATE OF c1;

A21:

B

22:

Given a table that has columns defined, 

 SMALLINT_COLUMN SMALLINT NOT NULL  VARCHAR_COLUMN VARCHAR(20)

which of the following statements is used to retrieve rows from the table if the second column can contain null values?

  1. FETCH * INTO :hv1, :hv2;

  2. FETCH CURSOR1 INTO :hv1, :hv2;

  3. FETCH * INTO :hv1, :hv2:hv2ind;

  4. FETCH CURSOR1 INTO :hv1, :hv2:hv2ind;

A22:

D

23:

Which two of the following are required for the DB2 Optimizer to consider a star join?

  1. At least four tables must be joined.

  2. More than one table can be considered to be a fact table.

  3. The fact and dimension tables must contain the same columns.

  4. A single-column index must be defined on each join-column of the fact table.

  5. The fact table must be joined to at least three dimension tables.

A23:

D

24:

Which of the following DB2 application development methods/environments must not be used when enabling end users to choose which tables to select data from at application runtime?

  1. CLI

  2. ODBC

  3. Net.Data

  4. Embedded static SQL

  5. Embedded dynamic SQL

A24:

D

25:

During which of the following are SQL statements optimized when using DB2 CLI?

  1. precompile

  2. bind processing

  3. statement preparation

  4. the closing of each cursor

A25:

C

26:

In order to access a DB2 database from a workstation that does not have any DB2 code installed, which of the following must be used?

  1. Triggers

  2. Constraints

  3. Java Applet

  4. Java stored procedure

A26:

C

27:

Which of the following programming methods does NOT lend itself to accessing DB2 data over the Internet using Web browser based clients ?

  1. Net.Data

  2. SQLJ Applets

  3. JDBC Applets

  4. SQLJ Applications

A27:

D

28:

Which of the following statements allows an SQL routine to be created?

  1. CREATE VIEW

  2. CREATE ROUTINE

  3. CREATE TRIGGER

  4. CREATE PROCEDURE

A28:

D

29:

In order to process an embedded SQL (non-REXX) program, which of the following database components is required?

  1. Binder

  2. Precompiler

  3. Control Center

  4. Visual Explain

A29:

B

30:

Which of the following static SQL statements can contain a host variable?

  1. FETCH

  2. ROLLBACK

  3. DROP INDEX

  4. ALTER TABLE

A30:

A

31:

Which of the following SQL statements require a host variable within the statement?

  1. DELETE

  2. DECLARE

  3. INSERT INTO

  4. SELECT INTO

A31:

D

32:

Which of the following data types must be used for a host variable that will be used to retrieve a TIMESTAMP from the database?

  1. A decimal

  2. An integer numeric

  3. A character string

  4. A float (scientific notation)

A32:

C

33:

Given the following, 

 EXEC SQL BEGIN DECLARE SECTION;       char hv_name[20]; EXEC SQL END DECLARE SECTION;

which of the following examples correctly demonstrates the use of the host variable within a SQL statement?

  1. EXEC SQL name_column INTO :hv<name

  2. EXEC SQL SELECT name_column INTO :hv_name

  3. EXEC SQL SELECT name_column INTO^:hv_name

  4. SELECT firstnme FROM employee WHERE lastnme = :?

A33:

B

34:

Which of the following SQLCA elements contains the number of rows affected by the SQL statement?

  1. sqlcabc

  2. sqlcode

  3. sqlerrp

  4. sqlerrd

  5. sqlstate

  6. sqlerrml

A34:

D

35:

A package was created by DB2ADMIN, and the bind file or DBRM contains only dynamic SQL statements. Also, the package to be created already exists. Which of the following privileges does the user DB2USER require in order to perform the BIND command on this package?

  1. BINDADD

  2. DB2ADMIN

  3. ALTERIN on schema DB2USER

  4. CREATEIN on schema DB2ADMIN

  5. IMPLICIT_SCHEMA on schema DB2ADMIN

A35:

C

36:

Which of the following is required when using the REBIND command?

  1. The database

  2. The bind file

  3. The application source

  4. The application executable

A36:

A

37:

Given the following embedded SQL pseudocode, 

 Start Program  EXEC SQL BEGIN DECLARE SECTION   USERA     CHARACTER (8)   USERB     CHARACTER (8)   PW        CHARACTER (8)   COLVAL    CHARACTER (16) EXEC SQL END DECLARE SECTION EXEC SQL INCLUDE SQLCA EXEC SQL WHENEVER SQLERROR GOTO ERRCHK   (program logic)   (:usera contains the string "usera")   (:pwa contains a valid password)   (:userb now contains the string "userb")   (:pwb contains a valid password) EXEC SQL CONNECT TO samplea USER :usera USING :pwa EXEC SQL SELECT col1, col2 FROM tablea   (program logic to retrieve results) EXEC SQL COMMIT   (more program logic) EXEC SQL CONNECT TO sampleb USER :userb USING :pwb EXEC SQL SELECT col1, col2 FROM tablex EXEC SQL DELETE FROM tablea WHERE col1= :colval  //1st delete EXEC SQL COMMIT   (more program logic) EXEC SQL CONNECT TO samplea EXEC SQL DELETE from tablea where col1= :colval  //2nd delete EXEC SQL COMMIT EXEC SQL CONNECT RESET ERRCHK   (check error information in SQLCA)   (Cleanup) End Program

which of the following tables will be updated in "samplea" if the second DELETE completes sucessfully?

  1. usera.tablex

  2. userb.tablea

  3. usera.tablea

  4. userb.tablex

A37:

C

38:

Which of the following limits the number of concurrent CLI statement handles that can be in use on a given connection?

  1. Unlimited

  2. Limited to 10 statements

  3. The number of concurrent connections

  4. The number of sections in the CLI packages

A38:

D

39:

Which of the following describes where a DSN (data source name) can be registered prior to its use in connecting from a CLI/ODBC application?

  1. In the DCS directory

  2. In the node directory

  3. With the DB2 CLI/ODBC driver

  4. With the ODBC driver manager

A39:

D

40:

For which of the following handle types would you call SQLERROR after SQLExecDirect returns a SQLERROR.

  1. Exception

  2. Statement

  3. Connection

  4. Descriptor

  5. Environment

A40:

B

41:

Which of the following ODBC/CLI functions must be called before SQLExecDirect can be successfully executed if the SQL statement contains parameter markers?

  1. SQLColumns

  2. SQLParamData

  3. SQLBindParameter

  4. SQLExtendedPrepare

A41:

C

42:

Given the following table and SQL statement, 

 Table A  Column1 ------- R1 R2 R3 R4 R5 R6 SELECT * FROM tablea ORDER BY column1

using a keyset-driven cursor with a rowset size of 3, which of the following rows are returned when the following ODBC APIs are issued in the order below? 

 1) SQLFetchScroll with the SQL_FETCH_FIRST option 2) SQLFetchScroll with the SQL_FETCH_RELATIVE option, offset=1 3) SQLFetchScroll with the SQL_FETCH_ABSOLUTE option, offset=3

  1. R1,R2,R3,R2,R3,R4

  2. R1,R2,R3,R4,R5,R6

  3. R1,R2,R3,R2,R3,R4,R3,R4,R5

  4. R1,R2,R3,R4,R5,R6,R3,R4,R5

A42:

D

43:

Which SQLDriverConnect DriverCompletion option will result in the return code SQL_ERROR if a required field is not supplied in the connection string?

  1. SQL_DRIVER_PROMPT

  2. SQL_DRIVER_NOPROMPT

  3. SQL_DRIVER_COMPLETE

  4. SQL_DRIVER_COMPLETE_REQUIRED

A43:

B

44:

Which of the following JDBC objects are queried to determine the indexes defined on a given table?

  1. ResultSet

  2. Statement

  3. Connection

  4. DatabaseMetaData

  5. CallableStatement

A44:

D

45:

Which of the following JDBC objects must be queried to determine the SQLSTATE in the event an SQL operation fails?

  1. ResultSet

  2. Statement

  3. Exception

  4. Connection

  5. SQLException

A45:

E

46:

Which of the following is a required parameter for running db2profc?

  1. user ID

  2. password

  3. prep options

  4. profile name

A46:

D

47:

Which of the following can be used to specify a user ID and password when connecting to a remote database called sample from an SQLJ application?

  1. #sql con={CONNECT TO SAMPLE : userid :password}

  2. getConnection("jdbc:db2:sample",userid,password)

  3. getConnection("jdbc:db2:sample/userid:password")

  4. #sql con={CONNECT TO SAMPLE USER :userid USING :password}

A47:

B

48:

Which of the following code fragments correctly performs a static SELECT statement of the integer column C1 from table T1? (Assume that the host variable hv has the proper type.) 

  1.  EXEC SQL SELECT c1 FROM t1 USING cur  EXEC SQL FETCH cur INTO :hv EXEC SQL CLOSE cur 
  2.  EXEC SQL OPEN cur FOR SELECT c1 FROM t1  EXEC SQL FETCH cur INTO :hv EXEC SQL CLOSE cur 
  3.  EXEC SQL DECLARE cur CURSOR FOR SELECT c1 FROM t1  EXEC SQL OPEN cur EXEC SQL FETCH cur INTO :hv EXEC SQL CLOSE cur 
  4.  EXEC SQL PREPARE stmt FOR SELECT c1 FROM t1          EXEC SQL DECLARE cur CURSOR FOR stmt         EXEC SQL OPEN cur         EXEC SQL FETCH cur INTO :hv         EXEC SQL CLOSE cur_
A48:

C

49:

Which of the following statements uses dynamic SQL?

  1. EXEC SQL DECLARE c1 CURSOR FOR s1

  2. EXEC SQL UPDATE c1 SET name = :name

  3. EXEC SQL SELECT name INTO :name FROM t1

  4. EXEC SQL DECLARE c1 CURSOR FOR SELECT name FROM t1

A49:

A

50:

Which of the following is used to convert between a user-defined distinct type and its source type?

  1. CAST function

  2. COALESCE function

  3. BUILT- IN SQL function

  4. USER DEFINED function

A50:

A

51:

Which of the following is not a type of UDF?

  1. Table

  2. Column

  3. Scalar

  4. Summary

A51:

D

52:

An application needs to retrieve a large number of rows from a database across the network and compute a single value from the retrieved data. Which of the following methods will reduce the network traffic?

  1. Stored procedure

  2. Table constraints

  3. User-defined types

  4. Combine all rows into a LOB

A52:

A

53:

Given the code 

 BEGIN COMPOUND NOT ATOMIC STATIC    UPDATE country SET cities = :count WHERE CURRENT OF C1;   INSERT INTO country VALUES (:col1, :col2, :col3);   INSERT INTO country VALUES (:col4, :col5, :col6);   INSERT INTO country VALUES (:col7, :col8, :col9);   INSERT INTO country VALUES (:col10, :col11, :col12);   COMMIT; END COMPOUND

if the fifth SQL statement in the block fails, how many rows will be affected in table country?

  2. 1

  3. 2

  4. 3

  5. 4

  6. 5

A53:

E

54:

Which of the following indicates when a table cannot be locked explicitly?

  1. When using DDL

  2. If it is a catalog table

  3. If it is from a specific application

  4. If other users must have access to the table

A54:

B

55:

Application A is bound using cursor stability, updates row X, and does not issue a COMMIT. Application B reads row X. Which of the following isolation levels must application B be using?

  1. Read stability

  2. Repeatable read

  3. Cursor stability

  4. Uncommitted read

A55:

D

56:

Which of the following operating systems can the Stored Procedure Builder run on?

  1. Windows, AIX, HP/UX

  2. Windows, AIX, Linux

  3. OS/2, Windows, Linux

  4. Windows, AIX, Solaris

A56:

D

57:

Which of the following is not available in a distributed unit of work?

  1. IMPORT

  2. UPDATE

  3. INSERT

  4. DELETE

  5. COMMIT

A57:

A

58:

Which of the following demonstrates a correct usage of parameter markers?

  1. CALL outsrv(?, ?)

  2. SELECT col1, col2 FROM tab1 WHERE tab1.? = 15

  3. SELECT count(*) FROM foo.tab1 WHERE col1 = '?'

  4. SELECT firstnme FROM employee WHERE lastnme = :?

A58:

A

Team-Fly    
Top

Previous page
Table of content
Next page
DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson
BUY ON AMAZON
ERP and Data Warehousing in Organizations: Issues and Challenges
Managing Enterprise Systems with the Windows Script Host
Cisco CallManager Fundamentals (2nd Edition)
Introducing Microsoft ASP.NET AJAX (Pro - Developer)
Logistics and Retail Management: Emerging Issues and New Challenges in the Retail Supply Chain
Telecommunications Essentials, Second Edition: The Complete Global Source (2nd Edition)
flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net
Privacy policy