DB2 Programming 2015

1. Which of the following is true about locking

Answers:

• Locking is a process that is used to ensure data integrity.

• Locking prevents concurrent users from accessing inconsistent data.

• All of the above

• The data (row) is locked until a commit is executed to release the updated data.

2. Which of the following is true about the EXPLAIN command

Answers:

• EXPLAIN is used to display the access path as determined by the optimizer for a SQL statement

• It can be used in BIND step(for embedded SQL).

• C,D & E

• It can be used in SPUFI(for single SQL statement)

3. What are two examples of steps that must be taken before coding an application with embedded static or dynamic SQL statements?

Answers:

• Transcribe, and bind.

• Pretranscribe, and bind.

• Precompile, and bind.

• Compile, and bind.

4. Which of the following statements eliminates all but one of each set of repeated rows inthe final result table?

Answers:

• SELECT UNIQUE * FROM t1

• SELECT * FROM DISTINCT T1

• SELECT DISTINCT * FROM t1

• SELECT UNIQUE (*) FROM t1

5. Given the following DDL statement: CREATE TABLE newsmart1 LIKE smart1 Which of the following would occur as a result of the statement execution?

Answers:

• newsmart1 has the same primary key as smart1

• newsmart1 has same triggers as smart1

• newsmart1 is populated with smart1 data

• newsmart1 columns have same attributes as smart1

6. What must happen before using the EXPLAIN statement?

Answers:

• A stored procedure must be made to process it.

• A planned procedure must be made to execute it.

• A Java wrapper must be written to use it.

• A plan table must be created to hold the results of EXPLAIN.

7. When are dynamic SQL applications prepared?

Answers:

• While the program is running.

• When the program is not running.

• When the system is turned off.

• When the program is sandboxed.

8. What code is used to start the CICS attachment facility?

Answers:

• CICSstart

• CICSgo

• EXEC CICS LINK PROGRAM('DSNyCOM ')

• CICS

9. Which of the following occurs if a DB2 procedure or application ends abnormally during an active unit of work?

Answers:

• The unit of wok moves to pending state

• The unit of work remains active

• The unit of work moves to CHECK_PENDING

• The unit of work is rolled back

10. A declared temporary table is used for which of the following purposes?

Answers:

• To provide an area for database manager sorts

• To share result sets between applications

• To create a backup copy of a database or table space

• To store intermediate results

11. What is SQLCA used for?

Answers:

• It checks the execution of SQL statements.

• It parses stored procedures.

• It checks the locking of SQL statements.

• It checks the code of SQL statements.

12. Which of the following is a DB2 mechanism that ensures data integrity between tables related by Primary & Foreign Keys

Answers:

• Pre-compilation

• Referential integrity

• Data Integrity

• B & C

13. What would the “SQL CONNECT” statement be used for when coding stored procedures?

Answers:

• It allows an application to communicate with DB2.

• It connects DB2 with Active Directory.

• It connects DB2 with the database.

• It connects DB2 with Java.

14. What is a stored procedure?

Answers:

• An SQL statement to execute a program.

• A program to execute SQL statements.

• A method for using wrappers to encode SQL.

• An SQL statement.

15. A role is a database entity that groups together one or more privileges. Which of the following is true for a "role"

Answers:

• A role cannot be set by using a SET CURRENT SQLID statement.

• A role can be the schema qualifier of an object. However, when it is used as a schema qualifier, a role is considered to be a character string and does not add any implicit schema privileges (ALTERIN, CREATEIN, or DROPIN) to this role.

• A role cannot be a primary authorization ID.

• All of the above

16. What is a host structure?

Answers:

• A single host variable that an SQL statement can refer to by using a single name.

• A group of host variables that an SQL statement can refer to by using a single name.

• A variable group of hosts that an SQL statement can refer to by using any name.

• A group of host variables that an SQL statement can process.

17. What are three examples of languages you can use to program in DB2?

Answers:

• COBOL, Fortran, and Perl.

• PHP, Ruby on Rails, and Haskell.

• Perl, Python, and PL/SQL.

• HTML, MSDOS, Python.

18. Which of the following is an example of a programming method not used in DB2 programming?

Answers:

• Static SQL

• OBDC

• Hadoop

• Dynamic SQL.

19. Which of the following DB2 objects allows multiple users to access data in a table with each only being able to access certain subsets of the data?

Answers:

• Indexes

• Mirrored Tables

• Views

• Aliases

20. What type of SQL operator would you use to combine the results of 2 Select statements while retaining the duplicates

Answers:

• Union All

• Intersect

• Inner Join

• Union

21. What technique can DB2 use to more effectively interpret data from EXPLAIN tables?

Answers:

• Parallel processing.

• Unilateral processing.

• Quadratic processing.

• Networked processing.

22. What are the three primary development environments for DB2?

Answers:

• PhotoData Studio, Microsoft Visual Studio, and IBM Optim Development Studio.

• Silverlight Studio, Microsoft Visual Studio, and IBM Optim Development Studio.

• WebSphere Studio, Adobe Acrobat Studio, and IBM Optim Development Studio.

• WebSphere Studio, Microsoft Visual Studio, and IBM Optim Development Studio.

23. What is the name of the effect when a system continues to receive work, but is down?

Answers:

• Hutchins effect.

• Auger effect.

• Stormdrain effect.

• Faraday effect.

24. When is it necessary to precompile DB2 REXX procedures before running them?

Answers:

• Only sometimes, because they vary from static to dynamic SQL.

• Never because they use dynamic SQL.

• Every time because they use static SQL.

• Only when you're using a Java wrapper.

25. What is a lightweight web application created from multiple sources?

Answers:

• A mixup.

• A smashup.

• A crackup.

• A mashup.

26. Before executing SQL statements, you want to know whether the CICS attachment facility is available. How do you check?

Answers:

• Use the EXTRACT EXIT command in your application, or INQUIRE EXITPROGRAM in version 4.0.

• You check in the CICS configuration menu.

• You shut down DB2 and check in the CICS kernal.

• You check in the CICS configuration file.

27. When coding in a language that requires a host variable declaration, what must precede it?

Answers:

• ;iostream.h

• ;hostchar

• ;varchar

• ;hostvar

28. Which of the following is the correct syntax for an input variable of a PARMLIST string?

Answers:

• DCL SINTAR BIN FAXED(15);

• DCL SINTVAR BIN FIXED(15);

• DCL SINvTAR BIN FIXED(15);

• (15)DCL SINTVAR BIN FIXED;

29. Which of the following can be accomplished with a single UPDATE statement?

Answers:

• Updating a table based on a sub-select using joined tables

• Updating a view consisting of joined tables

• Updating multiple tables

• Updating multiple tables based on a WHERE clause

30. What is an example of a benefit of using the Java programming language?

Answers:

• Once you write the program, it can be used with any other program or software.

• Once you write the program, it acts like a stored procedure.

• Once you develop an application, it can be run anywhere.

• There is no benefit to programming with Java.

31. What must be done to a DB2 application before it can run, and why?

Answers:

• It has to be binded first so it can recognize SQL statements.

• It has to be compressed first so it can recognize SQL statements.

• It has to be encrypted first so it can recognize SQL statements.

• It has to be compiled first so it can recognize SQL statements.

32. Can static SQL statements be changed without altering the program itself?

Answers:

• Yes, but the program has to be altered.

• Yes, but the statements have to be changed as well.

• No.

• Yes. DB2 is a fully dynamic program, accepting all forms of SQL.

33. SQL statements embedded into an application is called what?

Answers:

• Static SQL.

• T-SQL.

• PL/SQL

• Dynamic SQL.

34. Why would you receive a command response of NORMAL when the attachment facility is not available?

Answers:

• The exit was not ENABLE STARTED.

• The exit was not enabled.

• The database was disabled.

• The exit was disabled.

35. Which statement about an index is NOT true?

Answers:

• An index always has a balanced tree structure.

• The name of an index can be mentioned in a Select statement, to improve the performance of the query.

• A clustering index influences the physical sequence of data in the tablespace.

• An index has its own INDEX SPACE, automatically created

36. Consider the following: DECLARE MYCURS CURSOR FOR SELECT * FROM MYTABLE WHERE COL1 > :NUM  Which of the following embedded SQL statements will NOT generate an error?

Answers:

• FETCH * INTO :HOSTMYTABLE:IND FROM MYTABLE

• SELECT * INTO :HOSTMYTABLE:IND FROM MYCURS

• FETCH MYCURS INTO :HOSTMYTABLE:IND

• SELECT MYCURS INTO :HOSTMYTABLE:IND

37. Given the statement:  CREATE TABLE t1 (c1 INTEGER NOT NULL,c2 INTEGER,PRIMARY KEY(c1),FOREIGN KEY(c2) REFERENCES t2)  How many non-unique indexes are defined for table t1?

Answers:

• 1

• 2

• 0

• 3

38. When you don't know the format of an SQL statement within a program you're writing, what is a good option?

Answers:

• NoSQL.

• T-SQL.

• Dynamic SQL.

• Static SQL.

39. What's the output of a DB2 Bind

Answers:

• Database request module

• DB2 Application plan

• DB2 Catalog

• Load Module

40. What is a benefit of concurrency in SQL application programming?

Answers:

• Minimization of data access conflicts.

• Increase of data conflicts.

• Increasing data access points.

• Multi-threaded compiling.

41. How does one prepare a Java program that contains JDBC methods?

Answers:

• Use the “javago” command.

• Use the “DB2java” command.

• Use the “JBDC” command.

• Use the “javac” command.

42. How many indexes will be created by the following statement? Create table Smarterer { Col1 int not null primary key, Col2 char (64), Col3 char (32), Col4 int not null, Constraint c4 unique (Col4,Col1) }

Answers:

• 0

• 2

• 3

• 1

43. When a user has a SELECT authorization on a certain base table, and he creates a view on that table alone, then which of the following is true?

Answers:

• He/She also has DELETE authorization on that view because he/she is the creator of the view.

• He/She only has a SELECT authorization on that view.

• He/She will face an authorization error when trying to create the view

• He/She can only execute an UPDATE as long as the definition of the view is not exceeded.

44. How are ODBC calls binded?

Answers:

• They are binded at compile time.

• They are not binded because they use standard functions to execute SQL.

• They are binded using standard functions in SQL.

• They are pre-process binded.

45. Which of the following is not a DB2 object

Answers:

• Synonym

• Table

• Storage Group

• Column

46. Which of the following is not one of the types of authorizations associated with a DB2 user?

Answers:

• Current SQLID

• Primary Authorization ID

• Secondary Authorization ID

• SQL Authorization ID

47. Given the following DDL statements: CREATE TABLE Smart1 (a INT, b INT, c INT) CREATE VIEW View1 AS SELECT a,b,c FROM Smart1 WHERE a > 250 WITH CHECK OPTION Which of the following INSERT is correct

Answers:

• INSERT INTO Smart1 VALUES (200, 2, 3)

• INSERT INTO Smart1 VALUES (350, 2, 3)

• INSERT INTO View1 VALUES (250, 2, 3)

• INSERT INTO View1 VALUES (300, 2, 3)

48. What four languages can host variable arrays be specified in?

Answers:

• C, C++, Perl, or PL/I.

• C, C++, Fortran, or PL/I.

• C, C++, COBOL, or PL/I.

• Javascript, C++, COBOL, or PL/I.

49. SQLSTATE is a standard set of error messages and warnings in which the first two characters defines the class and the last three defines the subclass of the error. Which of the following SQLSTATE codes is interpreted as  "No data returned"?

Answers:

• 00xxx  

• 01xxx

• 22xxx

• 02xxx

50. In what catalog table must stored procedures be defined?

Answers:

• STOPROCEDURES

• CONFPROCEDURES

• SYSPROCEDURES

• CONFPROC

51. What is an example of an underlying cause for regressions caused by changes in DB2?

Answers:

• Regression analysis.

• Accurate statistics.

• Redundant statistics.

• Inadequate statistics.

52. What is the Java data access platform used in DB2?

Answers:

• dQuery.

• aQuery.

• jQuery.

• pureQuery.

53. Why must the DB2 precompiler be used to execute SQL statements?

Answers:

• It isn't necessary to use the DB2 precompiler.

• The DB2 precompiler wraps the SQL statements in Java for easier use.

• The compiler does not recognize SQL statements.

• The DB2 precompiler executes a HIVE database for SQL operation.

54. Can the SQL procedural language be used for advanced functions?

Answers:

• No.

• Yes, but with some supplementary coding.

• Yes, but they must be precompiled first.

• Yes, but with a supplementary set of Java wrappers.

55. When declaring a foreign key on a table, referencing an existing primary key with complete definition, what might differ between foreign key and primary key?

Answers:

• The nullability of one of the included columns.

• B & E

• The order of the included columns.

• The data type of the included columns.

56. Consider the following SQL statement, executed by user S001: CREATE VIEW BOSTON_TEAMS AS SELECT * FROM TEAMS WHERE STATE = 'MA' User S002 has INSERT authority on this view, what would happen if he tries to insert a row into this view, where the STATE field of that line contains a value of ‘IN’?

Answers:

• You can’t insert into a view, because views don’t contain actual data; they’re just virtual windows on base tables.

• The row won’t be inserted because the field STATE doesn’t have the value ‘MA’.

• The row will be inserted only if the user has an authority to insert on table S001.TEAMS, but it will never show up in a SELECT on this view.

• The row will be inserted in the table S001.TEAMS, but it will never show up in a SELECT on this view.

57. Which of the following represents a function that is performed for each row in a DB2 table

Answers:

• Group by Having

• Scalar function

• Group by

• Aggregate Function

58. When a column has an extension of WITH DEFAULT NULL and a unique index is created on this column, what will be the effects on the possible null values in that column?

Answers:

• It has no influence on the possible null values; DB2 doesn’t consider nulls when it comes to an index.

• There is still a single null allowed, since it is unique as such.

• C & F

• Nulls are no longer allowed, since nulls aren’t unique.

59. Which of the following statements concerning locking on TABLESPACE level is correct?

Answers:

• When a TABLESPACE is S-locked (Shared use) by another user, a U lock (Update use) can be placed. However, an X lock (eXclusive use) on this level will force the S lock to be dropped, its transaction to be rolled-back to be able to place the X lock.

• When a TABLESPACE is S-locked by another user, a U lock can be placed. However, an X lock is not compatible. A IX lock (Intent to eXclusively use) will be placed to indicate that a X lock is waiting to be placed.

• When a TABLESPACE is S-locked by another user, neither a U lock or an X lock is com- patible on this level. In both cases an I lock (IU lock, IX lock) will be placed to indicate that a U lock or X lock is waiting to be placed.

• When a TABLESPACE is S-locked by another user, a U-lock can be placed. However, an X-lock is not compatible and will have to wait until the S-lock is released.

60. Which of the following is a correct syntactical example of written SQL code?

Answers:

• TYPE INTO //:book_type FROM BOOK_TYPES WHERE//

• SELECT TYPE INTO :book_type FROM BOOK_TYPES WHERE

• EXEC SQL SELECT TYPE INTO :book_type FROM BOOK_TYPES WHERE

• EXEC: SELECT INTO :book_type FROM BOOKTYPES WHERE

61. What happens when the execution of a utility is terminated by the TERM command?

Answers:

• The execution ends normally, the corresponding row in the SYSUTIL table is not removed, all resources are freed.

• The execution ends abnormally, the corresponding row in the SYSUTIL table is removed, all resources are freed.

• The execution ends abnormally, the corresponding row in the SYSUTIL table is not removed, all resources are freed.

• The execution ends normally, the corresponding row in the SYSUTIL table is removed, all resources are freed.

62. Which statement about tablespaces is true?

Answers:

• When creating a tablespace, one must indicate either the storage group or the bufferpool to be used by this tablespace. If neither is defined, the creation of the tablespace will fail.

• A tablespace is divided in units called pages , which hold one or more rows of a table. If a row of 8000 bytes is placed in a 4K page, th e row will automatically be split over two pages.

• Dropping a tablespace will not only remove all tables of the tablespace itself, but also all indexes created on these tables, even tho ugh they are stored separately in their own indexspace.

• A tablespace is divided in units called pages , which hold one or more rows of a table. If a row of 8000 bytes is placed in a 4K page, the page will automatically be resized to 8K.

63. If the SQLCA is included in the program, which of the following host-variables is NOT known and as such unavailable for the program?

Answers:

• SQLNUM

• SQLSTATE

• SQLCODE

• SQLWARN

64. What are the maximum number of tables that can be joined in DB2

Answers:

• 10

• 15

• 12

• 16

65. Which of the following is not a type of dynamic SQL?

Answers:

• Interactive SQL.

• Deferred embedded SQL.

• Enhanced PL/SQL

• Embedded dynamic SQL.

66. Which subquery operator compares a single value to every member of set of value.

Answers:

• Between

• Any

• All

• In

67. Which is an example of a column that would contain statistics necessary for programming?

Answers:

• CRDF

• CARDF

• CARDY

• CRDX

68. What happens to SQL statements if there are no stored procedures?

Answers:

• They are disregarded.

• They are embedded.

• They are locked.

• They are corrupted.

69. Given the following cursor declaration: DECLARE CLASSUPDATE CURSOR FOR SELECT CSTITLE FROM T001.CLASSS FOR UPDATE OF CDUR  Which of the following embedded SQL statements will use this cursor correctly?

Answers:

• UPDATE T001.CLASSS C SET C.CSTITLE = 'New Title' WHERE CURRENT OF CLASSUPDATE

• UPDATE T001.CLASSS SET CDUR = 5 WHERE CURRENT OF CLASSUPDATE

• UPDATE SET CDUR = 5 WHERE CURRENT OF CLASSUPDATE

• UPDATE T001.CLASSS SET CDUR = 5

70. What must a SELECT statement be coded within?

Answers:

• A DEFINE CURSOR

• A DECLARE CURSOR.

• A SELECT CURSOR

• A STATIC CURSOR

71. When should we execute a REBIND rather than a BIND

Answers:

• C & E

• After the execution of a RUNSTATS.

• When the embedded SQL of the application is changed.

• When an index is added to one of the tables used by the embedded SQL.

72. Which of the following is not a DB2 datatype

Answers:

• Long Graphic

• Vargraphic

• Long Vargraphic

• Graphic

73. In what form is data physically stored in DB2

Answers:

• Flat Files

• VSAM LDS

• VSAM ESDS

• Tables

74. Any database needs to go through a normalization process to make sure that data is represented only once. This will eliminate problems with creating or destroying data in the database. The normalization process is done usually in three steps which results in first, second and third normal forms. Which best describes the process to obtain the third normal form?

Answers:

• If a table has columns not dependent on the primary keys, they need to be moved in a separate table.

• We have a table with multi-valued key. All columns that are dependent on only one or on some of the keys should be moved in a different table.

• Each table should have related columns.

• Each separate table should have a primary key.

75. Consider the following embedded SQL statement:  SELECT CLASSNAME INTO :CLASSNAME:IND FROM CLASS WHERE STUDNAME = :STUDNAME  In which order should the following program variables be evaluated by the part of the program just after this SQL statement?

Answers:

• SQLCODE, CLASSNAME, IND

• IND, STUDNAME, CLASSNAME

• SQLCODE, STUDNAME, CLASSNAME

• SQLCODE, IND, CLASSNAME

76. SELECT empname , paygrade , salary FROM emp, salgrade  WHERE salary BETWEEN lowsal AND highsal ORDER BY paygrade The above is an example of a

Answers:

• Non equi join

• Inner join

• Outer join

• None of the above

77. Besides on TABLE and TABLESPACE level, on what level can implicit locks be placed by DB2?

Answers:

• On PAGE , SEGMENT and PARTITION level.

• On PAGE , SEGMENT and PARTITION level.

• On ROW , PAGE and SEGMENT level.

• On ROW,PAGE and PARTITION level

78. When using JBDC, do you have to recode your Java application if you want to change between drivers?

Answers:

• Yes, however there is a tool that must be used to change to a new driver.

• No, however a special code must be written to use a new driver.

• No.

• No, however the new drivers need wrappers.

79. When can you not drop a database

Answers:

• Before all indexes connected to tables of this database are dropped.

• If there is still a user connected to the database.

• If there is a referential constraint pointing to a table of this database from a table of another database.

• When a DB2 utility has control of any part of the database.

80. Which of the following is correct SQLJ syntax?

Answers:

• #sql [myConnCtxt] :: UPDATE//EMP

• #sql [myConnCtxt] { UPDATE EMP

• ///#sql [myConnCtxt]}}{{||\\ { UPDATE EMP///

• #//sql [myConnCtXt] { update EMP