1. What do the initials DDL and DML stand for? What does they mean?

A. DDL is data definition language and DML is data manipulation language. DDL statements are CREATE, ALTER, TRUNCATE. DML statements are SELECT, INSERT, DELETE and UPDATE.

2. What is the difference between static SQL and dynamic SQL?

A. The static SQL statement is prepared before the program is executed and the operational form of the statement persists beyond the execution of the program. The source form of a static SQL statement is embedded within an application program written in a host language such as COBOL. Unlike static SQL, the dynamic statements are constructed and prepared at run time. The source form of a dynamic statement is a character string that is passed to DB2 by the program using the static SQL statement PREPARE or EXECUTE IMMEDIATE. Whether the operational form of the statement is persistent depends on whether dynamic statement caching is enabled.

3. What is deferred embedded SQL?

A. A deferred embedded SQL statement is neither fully static nor fully dynamic. Like a static statement, it is embedded within an application, but like a dynamic statement, it is prepared during the execution of the application. Although prepared at run time, a deferred embedded SQL statement is processed with bind-time rules such that the authorization ID and qualifier determined at bind time for the plan or package owner are used. Deferred embedded SQL statements are used for DB2 private protocol access to remote data.

4. What is DB2 ODBC?

A. DB2 Open Database Connectivity (DB2 ODBC) is an alternative to using embedded static or dynamic SQL. DB2 ODBC is an application-programming interface in which functions are provided to application programs to process SQL statements. The function calls are available only for C and C++ application programs. Through the interface, the application invokes a C function at execution time to connect to the data source, to issue SQL statements, and to get returned data and status information. Unlike using embedded SQL, no pre-compilation is required. Applications developed using this interface might be executed on a variety of data sources without being compiled against each of the databases. Note that only C and C++ applications can use this interface.

5. What are JDBC and SQLJ?

A. JavaSoft(TM) Java Database Connectivity (JDBC) and SQLJ are two methods for accessing DB2 data from the Java programming language. In general, Java applications use JDBC for dynamic SQL and SQLJ for static SQL.

6. What is a schema?

A. A schema is a collection of named objects. The objects that a schema can contain include distinct types, functions, stored procedures, and triggers. An object is assigned to a schema when it is created.

The schema name of the object determines the schema to which the object belongs. When a distinct type, function, or trigger is created, it is given a qualified, two-part name. The first part is the schema name (or the qualifier), which is either implicitly or explicitly specified. The second part is the name of the object. When a stored procedure is created, it is given a three-part name. The first part is a location name, which is implicitly or explicitly specified, the second part is the schema name, which is implicitly or explicitly specified, and the third part is the name of the object.

7. What are the differences between base table, auxiliary table and temporary table?

A. A base table is a table created with the SQL statement CREATE TABLE and used to hold persistent user data. An auxiliary table is a table created with the SQL statement CREATE AUXILIARY TABLE and used to hold the data for a column that is defined in a base table. A temporary table is a table described by the SQL statement CREATE GLOBAL TEMPORARY TABLE and used to hold data temporarily, such as the intermediate results of SQL transactions. Temporary tables persist as long as the application supports them. Table space and database operations, locking, logging, and recovery do not apply.

8. What is an index?

A. An index is an ordered set of pointers to rows of a base table or an auxiliary table. Each index is based on the values of data in one or more columns. An index is an object that is separate from the data in the table. When you define an index using the CREATE INDEX statement, DB2 builds this structure and maintains it automatically.

9. Define a composite key

A. A key composed of more than one column is called a composite key.

10. Define unique key

A. A unique key is a key that is constrained so that no two of its values are equal (no duplicates).

11. How can the uniqueness of a key be enforced? When does DB2 enforce this constraint?

A. The mechanism used to enforce the uniqueness of a key is a unique index. Thus, every unique key is a key of a unique index. Such an index is also said to have the UNIQUE attribute. A unique key can be defined using the UNIQUE clause of the CREATE TABLE statement. A table can have an arbitrary number of unique keys.

12. When does DB2 enforce the various constraints.

A. DB2 enforces the constraint during the execution of the LOAD utility and the SQL INSERT and UPDATE statements.

13. How many primary keys are possible for a table?

A. One

14. Describe primary index.

A. The unique index on a primary key is called a primary index. When a primary key is defined in a CREATE TABLE statement, the table is marked unavailable until the primary index is created by the user unless the CREATE TABLE statement is processed by the schema processor. In that case, DB2 automatically creates the primary index.

15. Does every table need a primary key?

A. No, primary keys are optional.

16. Define a trigger.

A. A trigger defines a set of actions that are executed when a delete, insert, or update operation occurs on a specified table. When such an SQL operation is executed, the trigger is said to be activated.

17. Which statement is used to create a trigger?

A. CREATE TRIGGER

18. How many indexes can be stored in an index space?

A. One

19. How many tables can be stored in a table space?

A. One or more

20. How many tables can be stored in a partitioned table space?

A. One

21. What is a view? Why use it?

A. A view is a named specification of a result table. The specification is an SQL SELECT statement that is effectively executed whenever the view is referenced in an SQL statement. In other words, a view is a virtual table made up of data from base tables and other views, but not stored separately.

22. Can an index be defined for a view? How does an index improve the performance of a view?

A. An index cannot be created for a view. However, an index created for a table on which a view is based might improve the performance of operations on the view.

23. How will you create a Read-only view? Can you use COMMIT and ROLLBACK in the application program when the program is executed under IMS or CICS?

A. No, under CICS and IMS environment, CICS or IMS performs commit and rollback

24. What is a DB2 package?

A. A package contains control structures used to execute SQL statements. Packages are produced during program preparation. During the program preparation, the pre-compiler generates DBRM (database request module) that contains SQL statements extracted from the source program. From DBRM, the bind operation generates operational form of SQL or internal control structures to access the data. All control structures in a package are derived from the SQL statements embedded in a single source program.

25. What is a DB2 plan?

A. An application plan relates an application process to a local instance of DB2, specifies processing options, and contains one or both of the following elements: A list of package names. The bound form of SQL statements taken from one or more DBRMs. Every DB2 application requires an application plan. Plans and packages are created using the DB2 subcommands BIND PLAN and BIND PACKAGE

26. What is the difference between a package and a plan? How does one bind 2 versions of a CICS transaction with the same module name in two different CICS regions that share the same DB2 subsystem?

A. Package and plan contain optimized code for SQL statements - a package for a single program, module or subroutine contained in the database request module (DBRM) library. A plan may contain multiple packages and pointers to packages. The one CICS module would then exist in a package that could be referenced in two different plans.

27. What is a distributed operation?

A. A DB2 application program can use SQL to access data at other database management systems (DBMSs) other than the DB2 at which the application's plan is bound. This DB2 is known as the local DB2. The local DB2 and the other DBMSs are called application servers. Any application server other than the local DB2 is considered a remote server, and access to its data is a distributed operation.

28. What is meant by local DB2?

A. The DB2 application server in which the application's plan is bound is known as local DB2.

29. How many connections are possible for an application program at a time?

A. At a time, only one connection is possible for an application program.

A) The local DB2 is connected to a remote a DBMS. The application program issues a SELECT statement. Then it tries to connect to another DBMS. What can you expect?

DB2 will not allow it. An application program can have only one connection at a time and cannot connect to new application server until it executes a commit or rollback operation.

B) What if the program closes the connection and tries to connect to another DBMS?

30. What are the different types of methods by which a local DB2 connect to a remote DBMS?

A. DB2 provides two methods of accessing data at remote application server

1. DRDA: This protocol allows the application program to connect to DB2 as well as other types of DBMS.

2. DB2 private protocol access: It allows DB2 connect to another DB2 running on a different application server

31. What does Remote unit of work mean?

A. A unit of work is a transaction made up of a related set of SQL statements. Under remote UOW, DB2 on one computer can send multiple, related set of SQL statements to another DBMS on a remote computer. The remote site performs the processing for the statements, but the sending site controls whether to commit or rollback the UOW.

32. What does an SQL connection mean?

A. An SQL connection is an association between an application process and a local or remote application server. SQL connections can be managed by the application or by using bind options. At any time:

33. Max number of columns in a DB2 table ?

A. 224. Tables without foreign keys and parent tables can have up to 750 columns. Tables with foreign keys can have up to 749 columns.

34. What is the maximum length of a column name?

A. 18

35. How will you set default values for columns? What is the minimum and max value of n in CHAR (n)?

A. PRIMARY_ID CHAR (8) WITH DEFAULT USER,

SQL_ID CHAR (8) WITH DEFAULT CURRENT SQLID

The min and max value of n in char(n) of var-char is 1 and 254.

36. What is a thread?

37. If not specified what are the default values for numbers, fixed-length strings, variable length strings dates, time and timestamp?

A. Numbers - 0 (SMALLINT, INTEGER, DECIMAL, or FLOAT); Fixed-length strings - blanks (CHAR or GRAPHIC); Varying-length strings - empty strings (VARCHAR, LONG VARCHAR, VARGRAPHIC, or LONG VARGRAPHIC); DATE - CURRENT DATE; TIME - CURRENT TIME; TIMESTAMP - CURRENT TIMESTAMP

37. What is RUNSTATS command in DB2? When will you need to run the RUNSTATS utility?

Run RUNSTATS at least once against each table and its associated indexes, After a load, or after mass updates, inserts, deletes, or after REORG...

39. I need to view the number of tables owned by one particular Owner. Is it possible? If so, please give the SQL query for this?

A. The query SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR = 'owner id' this displays the table names with that If you want only the number of tables give the following query. SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE CREATOR = 'owner id' Make sure that you are in correct subsystem.

40. What is JOIN and different types of JOIN.

A. The ability to join rows and combine data from two or more tables is one of the most powerful features of relational system. Three type of joins:1. Equi-join2.Non-equijoin3.self-join

41. Can I alter a table (e.g. adding a column) when other user is selecting some columns or updating some columns from the same table?

A. Yes possible. until the updating or selection is committed db2 table will not be restructured. new column definition will be there but it will not be included until all the tasks on the table are committed.

42. How many sub-queries can you combine together ?

A Total 16 queries and sub-queries are 15

43. What are the different methods of accessing db2 from TSO?

A. There are three ways in establishing tso/db2 connection 1. SPUFI 2. QMF 3. CATALOG VISIBILITY

44. How is the connection established between TSO & DB2?

A. A thread between TSO & DB2 is established while attempting to make connection between TSO & DB2.

45. What are buffer pools?

A. Buffer pools, also known as virtual buffer pools, are areas of virtual storage used temporarily to store pages of table spaces or indexes. When an application program needs to access a row of a table, DB2 retrieves the page containing that row and places the page in a buffer. If the row is changed, the buffer must be written back to the table space. If the needed data is already in a buffer, the application program will not have to wait for it to be retrieved from DASD. The result is faster performance. The sizes of virtual buffer pools can be changed while DB2 is running. The result is greater flexibility.

46. How many buffer pools are available in db2?

A. Ten 32k size buffer-pools and fifty 4k size buffer-pools (bp0 to bp49) default buffer pools are bp0,bp1,bp2 & bp32

47. How many Bufferpools are there in DB2 and what are they?

A. There are 4 Bufferpools. They are BP0,BP1,BP2 and BP32.

48. What is normalization? Explain the different rules?

A. Normalization rules help to avoid redundancies and inconsistencies in the data. It protects against update and delete anomalies and provide smaller tables and fewer total bytes. The rules of normalization are: 1st Normal Form:- No data item (repeating groups) should not be repeated within a given record.

2nd Normal Form: Each column that is not in the key provides a fact that depends on the entire key.

3rd Normal Form: Each non-key column provides a fact that it’s independent of other non-key columns and depends only on key columns. 4th Normal Form: No row contains two or more independent multi-valued facts about an entity. 5th Normal Form: Sub-relations that cannot be reconstructed.

49. Explain de-normalization technique?

A. The rules of normalization do not consider performance. De-normalization concentrates on performance, but not on redundancy. What you have to consider is the trade-off--whether duplication, in several tables, of often-requested columns is less expensive than the time it takes to perform joins. This duplication of columns in multiple tables is de-normalization, and increases redundancy.

50. What are the possible reasons to use views instead of tables?

A. Some of your users might find that no single table contains all the data they need; rather, the data might be scattered among several tables. A view is an alternative way of describing data that exists in one or more tables. To limit access to certain kinds of data. One table might contain more data than the users want to see, or more than they should be authorized to see. For those situations, you can create views. To allow you to alter tables without affecting application programs

51. What is referential integrity?

A. Referential integrity refers to the consistency that must be maintained between primary and foreign keys.

It is the state in which all values of all foreign keys at a given DB2 are valid. Rules of referential integrity:

Every foreign key value must have a matching primary key value.

An insert into a primary key table can not violate referential integrity and no checking is required.

A deletion of foreign key value can not violate referential integrity and no checking is required.

Changes in primary key values are allowed only for those values that don't have a matching foreign key.

Insert and update rules: The insertion of any given foreign key value (non-null value) or an update to that value is allowed only if the matching value exists in the primary key.

Delete rules for primary key value:

If RESTRICT is specified on foreign key definition, deletion of primary key value is not allowed.

If CASCADE is specified, both primary key and the foreign key values will be deleted.

IF SET NULL is specified, the foreign key value will be set to null, provided nulls are allowed in the foreign key column.

52. When can an insert of a new primary key value threaten referential integrity?

A. Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity.

53. What is the self-referencing constraint?

A. A single table can include both a primary key and a related foreign key. The limitations to changes in the primary key that the foreign key defines are called self-referencing constraints. The foreign key in a self-referencing table must specify the DELETE CASCADE rule.

54. What happens if either the DELETE RESTRICT or DELETE SET NULL is specified on a self-referencing table during the execution of an ALTER TABLE statement?

A. DB2 issues error message defining an invalid constraint is attempted.

55. What is table check constraint?

56. What is Check integrity and check pending state?

A. If the check integrity is compromised, or cannot be guaranteed for a table, the table space or partition that contains the table is placed in a check pending state. Check integrity is the condition that exists when each row of a table conforms to the check constraints defined on that table.

57. What are delete-connected tables?

A. Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect or be affected by the contents of the foreign key table.

58. What is a cycle in DB2 mean?

59. Which statement would you use to find out whether DB2 has used index to access the data?

A. EXPLAIN statement or the EXPLAIN option of bind.

60. What are the difference types of indexes and how will you create them?

A. There are two types of indexes: TYPE 1 & TYPE 2. Use the statement CREATE INDEX to create either type. TYPE 2 index comes with DB2V4 onwards. With TYPE 2 index data can be retrieved faster as only the data pages are locked and not the index pages. Hence TYPE 2 index is recommended.

61. What is the default value of index when you use CREATE INDEX?

A. The default value depends on the value of LOCKSIZE for the associated table space.

1. If LOCKSIZE is ROW then the default index type is type 2. You can't use row locking with a type 1 index.

2. If LOCKSIZE is not ROW, then the default for CREATE INDEX is the type specified in field DEFAULT INDEX TYPE of installation panel DSNTIPE.

62. What is a Leaf Page?

A. Index page that points directly to the data in the table is called a leaf page. The leaf page contains the key of the record and a pointer to the record.

63. What is a root page?

A. The opposite of a leaf page; it is the highest-level index page. An index can contain only one root page; all other index pages are associated to the root. Each record of a root page contains a pointer to another index page and the highest of key of that page.

64. What is B-tree Index. Explain.

A. The simplest DB2 index is the B-tree and the B-tree's top page is called the root page. The root page entries represent the upper range limits of the index and are referenced first in a search.

65. How many indexes a table can have?

A. A table can have more than one index

66. What is the function of UNIQUE WHERE NOT NULL clause?

A. The clause is uses with CREATE INDEX command. This ensures the uniqueness of the key column on which the index is defined, but it allows null values in the column. Or, DB2 will not allow duplication of non-null values in the key column.

67. What advantage does clustering index provide?

A. When a table has a clustering index, an INSERT statement inserts records as nearly as possible in the order of their index values. These clustered inserts can provide a significant performance advantage in some operations, particularly those that involve many records, such as comparisons other than equal, grouping and ordering. Although a table can have several indexes, only one can be a Clustering index.

68. How many clustering indexes are allowed for a table?

A. Only one.

69. How will you specify a clustering index?

70. You have loaded data using a non-clustering index. Now, You are defining a clustering index for the table. What is its effect on the new inserts?

A. It does not have any effect on the new insert. The data will still be organized using the non-clustering index. However, when the table space is reorganized by REORG utility, DB2 clusters data in accordance with the clustering index.

71. What is a partitioning index?

A. When you store a table in a partitioned table space, you tell DB2 how to divide the data among partitions by using the PART clause of a CREATE INDEX statement. The index that divides the data is called a partitioning index. It is also a clustering index, because the data is clustered by the index key values. Thus, your PART clause must be preceded by the CLUSTER clause.

72. Can you compress data in LOB space?

A. No

73. Name the different types of Table spaces.

A. 1. Simple Table Space

2. Segmented Table Space

3. Partitioned Table Space (some partitioned table spaces can also be EA-enabled table spaces)

4. LOB Table Space

74. How will compress data in a table space or partition?

A. To compress data in a table space or partition, specify COMPRESS YES on CREATE TABLESPACE or ALTER TABLESPACE, then run LOAD or REORG. When you compress data, bit strings that occur frequently are replaced by shorter strings. Information about the mapping of bit strings to their replacements is stored in a compression dictionary. Computer processing is required to compress data before it is stored and to decompress the data when it is retrieved from storage.

75. B37 abend during SPUFI

A. The B37 ABEND in the SPUFI is because of space requirements, the query has resulted in so many rows that the SPUFI.OUT file is not large enough to handle it, increase the space allocation of spufi.out.

76. What is the command used by TSO users to invoke DB2?

A. DSN RUN

77. What is the error code -803?

A. Unique index violation (Duplicate rows are not allowed)

78. How do you filter out the rows retrieved from a Db2 table?

A. One way is to use The SQL WHERE clause.

79. What is a collection?

A. A collection is something that every programmer should assign/Specify for every package. The Collection name is about 1-18 characters long. It's a user defined name that is the anchor for packages. It has no physical existence. Main usage is to group packages.

80. What is Skeleton cursor table (SKCT)?

A. When you bind a plan, DB2 creates a skeleton cursor table in the skeleton cursor table space (SCT02). IT is the executable form of a Plan. This is stored in sysibm.sct02 table.

81. What is a record in a DB2 environment?

A. In DB2, a record is the storage representation of a row.

82. What is the size constrain of a row?

A. In DB2, records or rows are stored in 4K or 32K pages and a single record cannot occupy more than one page. Therefore, a table cannot be created with a maximum record size greater than the page size.

A. When you use alter to add a new column to an existing table, it is added a the end of the row.

A. PIC S9(x-y)V9(Y) Comp-3;

85. What does the CURRENT SQLID register contain?

86. Can we declare DB2 HOST variable in COBOL COPY book?

A. NO. If we declare DB2 host variable in COBOL COPY book, at the time of Pre-compilation we get the host variable not defined, because pre-compiler will not expand COBOL COPY book. So we declare it either in DCLGEN with EXEC SQL INCLUDE Dclgenname END-EXEC or we directly hardcode it in the working storage section.

87. What should be specified along with a cursor in order to continue updating process after commit?

A. With Hold option.

88. What is the name of the default DB2 Catalog Database?

A. DSNDB06

89. When can you be sure that a query will return only one row?

A. When you use the primary key and only the primary key in the where clause.

90. What is the difference between join and union?

A. Join is used to retrieve data from different tables using a single SQL statement. Union is used to combine the results of two or more SQL queries.

91. What is the difference between a UNION and UNION ALL?

A. UNION: eliminates duplicates. UNION ALL: retains duplicates. Both are used to combine the results from different SELECT statements.

92. What is a correlated sub-query?

93. What are the functions of Bind?

A. BIND mainly performs two things syntax checking and authorization checking. It binds together all packages into an application plan hence the name BIND. Apart from this bind has optimizer as a sub-component. Its function is to determine the optimum access strategy.

94. MAX. NO OF ROWS PER PAGE

A. 127

95. The only place of VSAM KSDS in DB2 is?

A. BSDS is a VSAM KSDS.

96. Question: Can all Users have the privilege to use the SQL Statement SELECT * (DML)?

A. No, the user should be granted privilege to use it.

97. Question: what's the best locksize that you could use when you create a tablespace?

A. The answer is Locksize = ANY. Unless you are Sure what's the Purpose of tablespace ie.,Read-only or R/W. If you use lock size =any, Db2 would automatically determine what type of locks it should use.

98. What's the error code for Unique Index Violation:

A. -803

99. What's the percentage free space for

A. ZERO

100.Can you define an Index if the table size less than 10 PAGES?

 101.What's the Maximum Length of SQLCA and what's the content of SQLCABC?

A. The Max length is 136. and the SQLCABC has the Value of SQLCA.

2. What's the percentage free space for

A. The answer is ZERO.

3. What's the maximum number of volumes that can be added to a STOGROUP?

A. The answer is 133.Usually it will be difficult monitor more than 3 or 4 volumes to a STOGROUP.

4. What's the maximum number of characters that a table name can have?

A. The answer is 18 characters.

5. What is the meaning of -805 SQL return code?

A. Program name not in plan. Bind the plan and include the DBRM for the program named as part of plan.

6. When does the SQL statement gets executed when you use cursor in the application programming?

A. SQL statement gets executed when we open cursor

7. What does CURRENTDATA option in bind indicate?

A. CURRENTDATA option ensures block fetch while selecting rows from a table. In DB2V4 the default has been changed to NO. Therefore it is necessary to change all the bind cards with CURRENTDATA(YES), which is default in DB2V3 & earlier to CURRENTDATA(NO).

8. What are the levels of isolation available with DB2V4 ?

9. How do you achieve record locking in DB2 in the versions, which don’t support record level locking?

A. Yesterday I had posted this queue. The answer should have read as follows: By having the record length more than half of the page size !Sorry again & Thanks

10. How do u achieve record level locking in DB2 versions when record level locking is not allowed?

A. By having the length of the record greater than that of a page!

11. In a DB2-CICS program, which is, acts as co-coordinator and which is participant?

A. DB2 - participant CICS- coordinator

12. What does DML stand for and what are some examples of it?

A. Data Manipulation Language. Some examples are SELECT, INSERT, DELETE, REPLACE.

13. How to define the data items to receive the fetch items for the SQL?

A. Using the DSECT, followed by lines of - 'dataitems DS datatype'.

14. How will you delete duplicate records from a table?

A. Delete From Table1Where Id In (Select Id From Tabel1 As Temp Group By Id Having Count(*) >1)

15. What is the difference between Where and Having Clause?

A. WHERE is for Rows and HAVING is for Groups.

16. How to see the structure of db2 table?

A. Using QMF.

17. How do you declare a host variable (in COBOL) for an attribute named EMP-NAME of type VARCHAR(25) ?

A. 01 EMP-GRP. 49 E-LEN PIC S9(4) COMP. 49 E-NAME PIC X(25).

18. What is the maximum number of tables that can be stored on a Partitioned Table Space ?

A. ONE .

19. What are the max. & min. no. of partitions allowed in a partition tablespace?

A. Minimum is 4. maximum is 64.

A. Fifteen

21.What technique is used to retrieve data from more than one table in a single SQL statement?

A. The Join statement combines data from more that two tables

22. Question: What is a foreign key?

A. It identifies a related row in another table and establishes a logical relationship between rows in two tables.

23. Explain the use of the WHERE clause.

24. What is a DB2 bind?

Answer: A DB2 bind is a process that builds an access path to DB2 tables.

25. What is a DB2 access path?

A. An access path is the method used to access data specified in DB2 SQL statements.

26. What is normalization and what are the five normal forms?

A. Normalization is a design procedure for representing data in tabular format. The five normal forms are progressive rules to represent the data with minimal redundancy.

27. What are foreign keys?

A. These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables.

28. Describe the elements of the SELECT query syntax.

29. Explain the use of the WHERE clause.

A. WHERE is used with a relational statement to isolate the object element or row.

30. What techniques are used to retrieve data from more than one table in a single SQL statement?

A. Joins, unions and nested selects are used to retrieve data.

31. Explain an outer join.

A. An outer join includes rows from tables when there are no matching values in the tables.

32. What is a subselect? Is it different from a nested select?

A. A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.

33. What is the difference between group by and order by?

A. Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

34. Explain the EXPLAIN statement.

A. The explain statement provides information about the optimizer's choice of access path of the SQL.It can be used in SPUFI (for single SQL statement ) or in BIND step (for embedded SQL ).

35. What is a tablespace?

A. Tables are stored in tablespaces (hence the name)! There are three types of tablespaces: simple, segmented and partitioned.

36. What is a cursor and what is its function?

A. An embedded SQL statement may return a number of rows while the programming language can only access one row at a time. Cursor is a programming device that allows the SELECT to find a set of rows but return them one at a time. Cursor should be used because the host language can deal with only one row at a time.

37. Usually, which is more important for DB2 system performance - CPU processing or I/O access?

A. I/O operations are usually most critical for DB2 performance (or any other database for that matter).

38. Is there any advantage to de-normalizing DB2 tables?

A. Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.

39. What is the database descriptor?

A. The database descriptor, DBD is the DB2 component that limits access to the database whenever objects are created, altered or dropped.

A. To maintain the integrity of DB2 objects the DBD permits access to only on object at a time. Lock contention happens if several objects are required by contending application processes simultaneously.

41. What is SPUFI?

A. SPUFI stands for SQL processing using file input. It is the DB2 interactive menu-driven tool used by developers to create database objects.

42. What is the significance of DB2 free space and what parameters control it?

A. The two parameters used in the CREATE statement are the PCTFREE which specifies the percentage of free space for each page and FREEPAGE which indicates the number of pages to be loaded with data between each free page. Free space allows room for the insertion of new rows.

43. What is a NULL value? What are the pros and cons of using NULLS?

A. A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value. It's the DB2 equivalent of TBD on an organizational chart and often correctly portrays a business situation. Unfortunately, it requires extra coding for an application program to handle this situation.

44. What is a synonym? How is it used?

45. What is an alias and how does it differ from a synonym?

A. An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view. The alias is not dropped when the table is dropped.

46. What is a LIKE table and how is it created?

A. A LIKE table is created by using the LIKE parameter in a CREATE table statement. LIKE tables are typically created for a test environment from the production environment.

47. If the base table underlying a view is restructured, e.g. attributes are added, does the application code accessing the view need to be redone?

A. No. The table and its view are created anew, but the programs accessing the view do not need to be changed if the view and attributes accessed remain the same.

48. Under what circumstances will DB2 allow an SQL statement to update more than one primary key value at a time?

A. Never. Such processing could produce duplicate values violating entity integrity. Primary keys must be updated one at a time.

49. What is the cascade rule and how does it relate to deletions made with a subselect?

A. The cascade rule will not allow deletions based on a subselect that references the same table from which the deletions are being made.

50. How does Db2 use multiple table indexes?

A. DB2 use the multiple indexes to satisfy multiple predicates in a SELECT statement that are joined by an AND or OR.

51. What are some characteristics of columns that benefit from indexes?

A. Primary key and foreign key columns; columns that have unique values; columns that have aggregates computed frequently and columns used to test the existence of a value.

52. What is a composite index and how does it differ from a multiple index?

A. A multiple index is not one index but two indexes for two different columns of a table. A composite index is one index made up of combined values from two columns in a table. If two columns in a table will often be accessed together a composite index will be efficient.

53. What is meant by index cardinality?

A. The number of distinct values for a column is called index cardinality. DB2's RUNSTATS utility analyzes column value redundancy to determine whether to use a tablespace or index scan to search for data.

54. What keyword does an SQL SELECT statement use for a string search?

A. For a clustered index DB2 maintains rows in the same sequence as the columns in the index for as long as there is free space. DB2 can then process that table in that order efficiently. It is a mandatory index defined on a partitioned table space. Only one clustered index can be defined on a table.

56. What are some SQL aggregates and other built-in functions?

A. The common aggregate, built-in functions are AVG, SUM, MIN, MAX, COUNT and DISTINCT, which are built-in mathematical functions for use in Select clause.

57. How is the SUBSTR keyword used in SQL?

A. SUBSTR is used for string manipulation with column name, first position and string length used as arguments. E.g. SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.

A. The three data types are DATE, TIME and TIMESTAMP. CHAR can be used to specify the format of each type. The DAYS function calculates the number of days between two dates. (It's Y2K compliant). Physical Storage length is DATE: 4bytes TIME: 3bytes TIMESTAMP: 10bytes. COBOL data-types to be declared are DATE: PIC X(10) TIME : PIC X(08) TIMESTAMP: PIC X(26).

59. Explain transactions, commits and rollbacks in DB2.

A. In DB2 a transaction typically requires a series of updates, insertions and deletions that represent a logical unit of work. A transaction puts an implicit lock on the DB2 data. Programmers can use the COMMIT WORK statement to terminate the transaction creating smaller units for recovery. If the transaction fails DB2 uses the log to roll back values to the start of the transaction or to the preceding commit point.

60. What is deadlock?

a. Deadlock occurs when transactions executing at the same time lock each other out of data that they need to complete their logical units of work.

61. What are the four lockable units for DB2?

A. DB2 imposes locks of four differing sizes: pages, tables, tablespace and for indexes subpage.

62. What are the three lock types?

A. The three types are shared, update and exclusive. Shared locks allow two or more programs to read simultaneously but not change the locked space. An exclusive lock bars all other users from accessing the space. An update lock is less restrictive; it allows other transactions to read or acquire shared locks on the space.

63. What is isolation level?

A. SQL statements may return any number of rows, but most host languages deal with one row at a time by declaring a cursor that presents each row at a unique isolation level.

64. What is an intent lock?

A. An intent lock is at the table level for a segmented tablespace or at the tablespace level for a non-segmented tablespace. They indicate at the table or tablespace level the kinds of locks at lower levels.

65. What is the difference between static and dynamic SQL?

A. Static SQL is hard-coded in a program when the programmer knows the statements to be executed. For dynamic SQL the program must dynamically allocate memory to receive the query results.

A. Cursor stability means that DB2 takes a lock on the page the cursor is accessing and releases the lock when the cursor moves to another page.

67. What is the significance of the CURSOR WITH HOLD clause in a cursor declaration?

A. The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened.

68. What is the SQL Communications Area and what are some of its key fields?

A. It is a data structure that must be included in any host-language program using SQL. It is used to pass feedback about the SQL operations to the program. Fields are return codes, error messages, handling codes and warnings.

A. The WHENEVER statement is coded once in the host program to control program actions depending on the SQL-CODE returned by each SQL statement within the program.

70. What is DCLGEN?

A. DCLGEN stands for declarations generator; it is a facility to generate DB2 SQL data structures in COBOL or PL/I programs.

71. What is the FREE command?

72. DB2 can implement a join in three ways using a merge join, a nested join or a hybrid join. Explain the differences.

A. A merge join requires that the tables being joined be in a sequence; the rows are retrieved with a high cluster ratio index or are sorted by DB2. A nested join does not require a sequence and works best on joining a small number of rows. DB2 reads the outer table values and each time scans the inner table for matches. The hybrid join is a nested join that requires the outer table be in sequence.

73. Compare a subselect to a join.

A. Any subselect can be rewritten as a join, but not vice versa. Joins are usually more efficient as join rows can be returned immediately, subselects require a temporary work area for inner selects results while processing the outer select.

74. What is the difference between IN subselects and EXISTS subselect?

A. If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN. (IN for index is the mnemonic).

75. What is a Cartesian product?

76. What is an asynchronous write?

A. It is a write to disk that may occur before or long after a commit. The write is controlled by the buffer manager.

77. What is a lock?

A. A lock is the mechanism that controls access to data pages and table spaces.

78. What is meant by isolation level?

A. This is a key concept for any relational database. Isolation level is the manner in which locks are applied and released during a transaction. For DB2 a 'repeatable read' holds all locks until the transaction completes or a SYNCPOINT is issued. For transactions using 'cursor stability' the page lock releases are issued as the cursor 'moves', i.e. as the transaction releases addresses to the records.

79. What are leaf pages?

A. They are the opposite of root pages. Leaf pages are the lowest level index pages - the pages that contain index entries and information to the corresponding table rows.

80. What is a pre-compiler?

A. It is a DB2 facility for static SQL statements - it replaces these statements with calls to the DB2 language interface module.

201.How would you find out the total number of rows in a table?

A. Use SELECT COUNT(*) ... Allstate uses DB2v6. and QMF V3.3.

2. How do you eliminate duplicate values in SELECT?

A. Use SELECT DISTINCT ...

3. How do you select a row using indexes?

A. Specify the indexed columns in the WHERE clause.

4. Can you use MAX on a CHAR column?

A. YES.

5. My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results. Why?

A. Because SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted.

6. What is the use of VALUE function?

A. 1. Avoid -ve SQLCODEs by handling nulls and zeroes in computations

2. Substitute a numeric value for any nulls used in computation

7. How do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a complete name?

A. SELECT FIRSTNAME || ' ' || LASTNAME FROM EMP;

8. What is the use of VALUE function?

A. Avoid -ve SQLCODE s by handling nulls and zeroes in computations by substituting a numeric value for any nulls used in computation

9. What is the restriction on using UNION in embedded SQL?

A. It has to be in a CURSOR.

10. In the WHERE clause what is BETWEEN and IN?

A. BETWEEN supplies a range of values while IN supplies a list of values.

11. Is BETWEEN inclusive of the range values specified?

A. Yes.

12. What is 'LIKE' used for in WHERE clause? What are the wildcard characters?

A. LIKE is used for partial string matches. '%' ( for a string of any character ) and '_' (for any single character ) are the two wild card characters.

13. When do you use a LIKE statement?

A. To do partial search e.g. to search employee by name, you need not specify the complete name; using LIKE, you can search for partial string matches.

14. What is the meaning of underscore ( '_' ) in the LIKE statement?

A. Match for any single character.

15. What do you accomplish by GROUP BY ... HAVING clause?

A. GROUP BY partitions the selected rows on the distinct values of the column on which you group by. HAVING selects GROUP s, which match the criteria specified

16. Apart from cursor, what other ways are available to you to retrieve a row from a table in embedded SQL?

17. What are correlated Sub-queries?

A. Sub-query in which the inner ( nested ) query refers back to the table in the outer query. Correlated. Sub-queries must be evaluated for each qualified row of the outer query that is referred to.

18. Consider the employee table with column PROJECT nullable. How can you get a list of employees who are not assigned to any project?

A. SELECT EMPNO

FROM EMP

WHERE PROJECT IS NULL;

19. How and where do you specify and use a cursor in a COBOL program?

20. What is the result of this query if no rows are selected?

FROM EMP

WHERE QUAL='MSC';

A. NULL

21. Why SELECT * is not preferred in embedded SQL programs?

A. For three reasons:

22. How would you retrieve rows from a DB2 table in embedded SQL?

A. Either by using the single row SELECT statements, or by using the CURSOR.

23. What happens when you say OPEN CURSOR?

A. If there is an ORDER BY clause, rows are fetched, sorted and made available for the FETCH statement. Other wise simply the cursor is placed on the first row.

24. Is DECLARE CURSOR executable?

A. No.

25. Can you have more than one cursor open at any one time in a program ?

A. Yes.

26.. When you COMMIT, is the cursor closed?

A. Yes.

27. How do you leave the cursor open after issuing a COMMIT? ( for DB2 2.3 or above only )

28. Give the COBOL definition of a VARCHAR field.

A. A VARCHAR column REMARKS would be defined as follows:

...

10 REMARKS.

49 REMARKS-LEN PIC S9(4) USAGE COMP.

49 REMARKS-TEXT PIC X(1920).

29. What is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)?

A. PIC S9(9)V99 COMP-3. Note: In DECIMAL(11,2), 11 indicates the size of the data type and 2 indicates the precision.

30. What is DCLGEN ?

A. It's an acronym for DeCLarations GENerator: used to create the host language copy books for the table definitions. Also creates the DECLARE table.

31. What are the contents of a DCLGEN?

A. 1. EXEC SQL DECLARE TABLE statement which gives the layout of the table/view in terms of DB2 datatypes. 2. A host language copy book that gives the host variable definitions for the column names.

32. Is it mandatory to use DCLGEN? If not, why would you use it at all?

A. It is not mandatory to use DCLGEN. Using DCLGEN, helps detect wrongly spelt column names etc. during the pre-compile stage itself ( because of the DECLARE TABLE ). DCLGEN being a tool, would generate accurate host variable definitions for the table reducing chances of error.

33. Is DECLARE TABLE in DCLGEN necessary? Why it used?

A. It not necessary to have DECLARE TABLE statement in DCLGEN. This is used by the pre-compiler to validate the table-name, view-name, column name etc., during pre-compile.

34. Will precompile of an DB2-COBOL program bomb, if DB2 is down?

A. No. Because the precompiler does not refer to the DB2 catalogue tables.

35. Name some fields from SQLCA.

36. How is a typical DB2 batch program executed ?

A. 1. Use DSN utility to run a DB2 batch program from native TSO. An example is shown:

DSN SYSTEM(DSP3)

RUN PROGRAM(EDD470BD) PLAN(EDD470BD) LIB('EDGS01T.OBJ.LOADLIB')

END

2. Use IKJEFT01 utility program to run the above DSN command in a JCL.

37. Assuming that a site's standard is that pgm name = plan name, what is the easiest way to find out which programs are affected by change in a table's structure ?

A. Query the catalogue tables SYSPLANDEP and SYSPACKDEP.

38. How can you quickly find out the # of rows updated after an update statement?

A. Check the value stored in SQLERRD(3).

39. What do you need to do before you do EXPLAIN?

A. Make sure that the PLAN_TABLE is created under the AUTHID.

40. Where is the output of EXPLAIN stored?

A. In userid. PLAN_TABLE

41. EXPLAIN has output with MATCHCOLS = 0. What does it mean?

42. How do you do the EXPLAIN of a dynamic SQL statement?

A. 1. Use SPUFI or QMF to EXPLAIN the dynamic SQL statement

2. Include EXPLAIN command in the embedded dynamic SQL statements

43. How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF? Give an example with a host variable in WHERE clause.)

A. Use a question mark in place of a host variable ( or an unknown value ). e.g.

SELECT EMP_NAME

FROM EMP

WHERE EMP_SALARY > ?

44. What are the isolation levels possible? Where do you specify them?

A. CS: Cursor Stability - Releases the lock on a page after use. RR: Repeatable Read - Retains all locks acquired till end of transaction. By using the ISOLATION LEVEL parameter during the bind process.

45. When do you specify the isolation level? How?

A. During the BIND process. ISOLATION ( CS/RR )...

46. I use CS and update a page. Will the lock be released after I am done with that page?

A. No.

47. What are the various locking levels available?

48. How does DB2 determine what lock-size to use?

A. 1. Based on the lock-size given while creating the tablespace. 2. Programmer can direct the DB2 what lock-size to use. 3. If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE.

49. What are the disadvantages of PAGE level lock?

A. High resource utilization if large updates are to be done

50. What is lock escalation?

A. Promoting a PAGE lock-size to table or tablespace lock-size when a transaction has acquired more locks than specified in NUMLKTS. Locks should be taken on objects in single tablespace for escalation.

51. What are the various locks available?

A. SHARE, EXCLUSIVE, UPDATE

52. Can I use LOCK TABLE on a view?

A. No. To lock a view, take lock on the underlying tables.

53. What is ALTER ?

A. SQL command used to change the definition of DB2 objects.

54. What is a DBRM, PLAN ?

A. DBRM: DataBase Request Module, has the SQL statements extracted from the host language program by the pre-compiler. PLAN: A result of the BIND process. It has the executable code for the SQL statements in DBRM.

55. What is ACQUIRE/RELEASE in BIND?

A. Determine the point at which DB2 acquires or releases locks against table and tablespaces, including intent locks.

56. What else is there in the PLAN apart from the access path?

A. PLAN has the executable code for the SQL statements in the host program

57. What happens to the PLAN if index used by it is dropped?

A. Plan is marked as invalid. The next time the plan is accessed, it is rebound.

58. What are PACKAGES ?

A. They contain executable code for SQL statements for one DBRM.

59. What are the advantages of using a PACKAGE?

A. 1. Avoid having to bind a large number of DBRM members into a plan. 2. Avoid cost of a large bind. 3. Avoid the entire transaction being unavailable during bind and automatic rebind of a plan. 4. Minmize fallback complexities if changes result in an error.

60. What is a collection?

A. 100 (for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).

62. How would you print the output of an SQL statement from SPUFI?

A. Print the output dataset.

63. How do you pull up a query which was previously saved in QMF ?

A. By using the 'DISPLAY' command. LIST QUERIES (OWNER=ALL) is used to list the queries saved by using the SAVE QUERY 'dataset name' by various users with

64. Lot of updates have been done on a table due to which indexes have gone haywire. What do you do?

A. Looks like index page split has occurred. DO a REORG of the indexes.

65. What is dynamic SQL?

A. Dynamic SQL is a SQL statement created at program execution time.

66. When is the access path determined for dynamic SQL?

A. At run time, when the PREPARE statement is issued.

67. Suppose I have a program which uses a dynamic SQL and it has been performing well till now. Off late, I find that the performance has deteriorated. What happened?

A. Probably RUN STATS is not done and the program is using a wrong index due to incorrect statistics. Probably RUNSTATS is done and optimizer has chosen a wrong access path based on the latest stats.

68. How does DB2 store NULL physically?

A. As an extra-byte prefix to the column value. physically, the nul prefix is Hex '00' if the value is present and Hex 'FF' if it is not.

69. How do you retrieve the data from a nullable column?

A. Use null indicators. Syntax ... INTO :HOSTVAR:NULLIND

70. What is the picture clause of the null indicator variable?

A. S9(4) COMP.

71. What does it mean if the null indicator has -1, 0, -2?

A. -1 : the field is null. 0 : the field is not null. -2 : the field value is truncated

A. To insert a NULL, move -1 to the null indicator. To insert a valid value, move 0 to the null indicator.

73. Give some example of statistics collected during RUNSTATS?

A. # of rows in the table, Percent of rows in clustering sequence, # of distinct values of indexed column. # of rows moved to a nearby/farway page due to row length increase

74. What is REORG? When is it used?

75. What is IMAGECOPY ?

A. It is full backup of a DB2 table which can be used in recovery.

76. When do you use the IMAGECOPY?

A. To take routine backup of tables After a LOAD with LOG NO

77. What is COPY PENDING status?

A. State in which, an image copy on a table needs to be taken, In this status, the table is available only for queries. You cannot update this table. To remove the COPY PENDING status, you take an image copy or use REPAIR utility.

78. What is CHECK PENDING ?

A. When a table is LOADed with ENFORCE NO option, then the table is left in CHECK PENDING status. It means that the LOAD utility did not perform constraint checking.

79. What is QUIESCE?

A QUIESCE flushes all DB2 buffers on to the disk. This gives a correct snapshot of the database and should be used before and after any IMAGECOPY to maintain consistency.

80. What is a clustering index ?

A. Causes the data rows to be stored in the order specified in the index. A mandatory index defined on a partitioned table space.

81. How many clustering indexes can be defined for a table?

A. Only one.

82. What is the difference between primary key & unique index ?

A. Primary : a relational database constraint. Primary key consists of one or more columns that uniquely identify a row in the table. For a normalized relation, there is one designated primary key.

Unique index: a physical object that stores only unique values. There can be one or more unique indexes on a table.

83. What is sqlcode -922 ?

A. Authorization failure.

84. What is sqlcode -811?

A. SELECT statement has resulted in retrieval of more than one row.

85. What does the sqlcode of -818 pertain to?

A. This is generated when the consistency tokens in the DBRM and the load module are different.

86. Are views updateable?

A. Not all of them. Some views are updateable e.g. single table view with all the fields or mandatory fields. Examples of non-updateable views are views which are joins, views that contain aggregate functions(such as MIN), and views that have GROUP BY clause.

87. If I have a view, which is a join of two or more tables, can this view be updateable?

A. No.

88. What are the 4 environments which can access DB2?

A. TSO, CICS, IMS and BATCH

A. One divided by the number of distinct values of a column.

90. What is an inner join, and an outer join ?

A. Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns on table with all the calues in corresponding columns of the other table or tables. This kind of join which involve a match in both columns are called inner joins. Outer join is one in which you want both matching and non matching rows to be returned. DB2 has no specific operator for outer joins it can be simulated by combining a join and a correlated sub query with a UNION.

91. What is FREEPAGE and PCTFREE in TABLESPACE creation?

92. What are simple, segmented and partitioned table spaces ?

A. Simple Tablespace: Can contain one or more tables. Rows from multiple tables can be interleaved on a page under the DBA 's control and maintenance

Segmented Tablespace: Can contain one or more tables. Tablespace is divided into segments of 4 to 64 pages in increments of 4 pages. Each segment is dedicated to single table. A table can occupy multiple segments

Partitioned Tablespace: Can contain one table. Tablespace is divided into parts and each part is put in a separate VSAM dataset.

93. What is the difference between SYNONYM and ALIAS?

A. SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator.

ALIAS: is retained even if table or tablespace is dropped. ALIAS can be created even if the table does not exist. It is used mainly in distributed environment to hide the location info from programs. Alias is a global object & is available to all.

94. What do you mean by NOT NULL WITH DEFAULT? When will you use it?

A. This column cannot have nulls and while insertion, if no value is supplied then it wil have zeroes, spaces or date/time depending on whether it is numeric, character or date/time.

Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row.

95. What do you mean by NOT NULL? When will you use it?

A. The column cannot have nulls. Use it for key fields.

96. When would you prefer to use VARCHAR?

A. When a column which contains long text, e.g. remarks, notes, may have in most cases less than 50% of the maximum length.

97. What are the disadvantages of using VARCHAR?

A. 1. Can lead to high space utilization if most of the values are close to maximum length.

2. Positioning of VARCHAR column has to be done carefully as it has performance implications.

3. Relocation of rows to different pages can lead to more I/Os on retrieval.

98. How do I create a table MANAGER ( EMP#, MANAGER) where MANAGER is a foreign key which references to EMP# in the same table? Give the exact DDL.

A. First CREATE MANAGER table with EMP# as the primary key. Then ALTER it to define the foreign key.

99. When is the authorization check on DB2 objects done - at BIND time or run time?

A. At run time.

A. Recording SQL statements that access a table. Specified at table creation time or through alter.

Note: This Document is divided into Four different sections, with a few questions repeating. Section 2 will be expanded soon.