DB2 Interview Questions -1

What are the 3 types of locks ? The 3 types are Shared, Update and eXclusive: S locks allow programs to read but not change the locked space. X lock bars all other programs from accessing the locked space. U lock allows programs to acquire shared locks on the space.

What is a Check Constraint ? How is it enforced ? The DBA can implement these to place specific data value restrictions on certain columns using the CONSTRAINT clause. They consists of 2 components: 1-a CONSTRAINT name 2-a check condition

What is the use of the WHENEVER clause ? What conditions can it check ? The WHENEVER clause checks the values associated with an error in SQLCA each time a SQL statement is executed WHENEVER NOT FOUND - checks for data not found WHENVER SQLERROR - checks for a error code WHENEVER SQLWARN - checks SQLWARN for W It is bad practice to use WHENEVER, since it is analogous to a GO TO DEPENDING ON and does not tell you where the problem occurred.

What is a Declarations Generator (DCLGEN) ? DCLGEN is a facility that is used to generate SQL statements that describe a table or view. These table or view descriptions are then used to check the validity of other SQL statements at precompile time. The table or view declares are used by the DB2I utility DCLGEN to build a host language structure, which is used by the DB2 precompiler to verify that correct column names and data types have been specified in the SQL statement. .

What is a Resource Control Table (RCT) ? Describe its characteristics. It is a CICS table that is defined to a DB2/CICS region. It contains control characteristics which are assembled via the DSNRCT macros. It also matches the CICS transaction ID to its associated DB2 authorization ID and plan ID (CICS attachment facility).

How would you move a tablespace (using STOGROUP) to a different DASD volume allocated to that tablespace ? 1-If the tablespace used is only allocated to that STOGROUP: ALTER STOGROUP - add volume (new) delete volume (old) REORG TABLESPACE or RECOVER TABLESPACE 2-Create a new STOGROUP that points to the new volume. ALTER the tablespace and REORG or RECOVER the tablespace.

What do you do when you receive a SQLCODE -911 or -913 ? This indicates a deadlock or timeout which occured when accessing locked data. This is resolved by re-executing the SQL statement until the lock is removed. If the lock isn't removed in a timely fashion, a ROLLBACK is issued since the unit of work cannot be completed.

Q SQL can be embedded in a host program that uses a relational database as a persistent data repository. Some of the most important pre-defined structures for this mechanism are SQLDA ("SQL Descriptor Area") and SQLCA ("SQL Communications Area") SQLCA contains two structures - SQLCODE and SQLSTATE. 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"? (Check one that applies the best) A. 00xxx B. 01xxx C. 02xxx D. 22xxx E. 2Axxx A C. 00 - is successful completion, 01 - warnings, 22 - is data exception and 2A is syntax error. The SQLSTATE code format returned for "No data returned" is "02xxx".

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.

1. What are the disadvantages of using VARCHAR? 1. Can lead to high space utilization if most of the values are close to maimum. 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.

List the common SQLCODES. +100 = no more rows found, also means not found if trying to get first row -305 = NULL indicator variable is needed -407 = value is NULL and column cannot contain nulls -551 = unauthorized access on object -803 = attempt to INSERT a duplicate row -805 = DBRM or package name not found in plan, you also get this instead of -818 when using a package -811 = SELECT returned more than one row, use a cursor instead -818 = plan versus load module timestamp mismatch, do another BIND -904 = resource not available Examples: DB2 subsystem is down or RCT table in CICS not defined properly or NUMLKUS which is the maximum allowed page locks was exceeded during an update operation (issue COMMITS more often) -911 = deadlock or timeout, unit of work has been rolled back -913 = deadlock or timeout, unsuccessful execution -922 = connection authority failed, DBA has to grant you authority

What is the EXPLAIN statement ? How many ways can this be done ? Where does it get stored ? The EXPLAIN statement will show the access paths the DB2 optimizer uses to process the SQL statements in a program. There are 2 ways to do an EXPLAIN: 1-interactively using EXPLAIN ALL SET QUERYNO=n FOR sql statement... ; or 2-as part of the BIND process using the parameter EXPLAIN(YES) The results are stored in userid.PLAN_TABLE (which you should have already created with the preassigned columns).

What is the SQL Communications Area ? It is a data area defined in working storage which tells your program whether SQL statements were executed successfully. It contains SQLCODE which indicates whether the SQL statement was successful or unsuccessful. SQLWARN0 which if set to ‘W’, means that at least one of the SQL warning flags are set. SQLERR(3) indicates the number of rows updated, inserted or deleted by DB2.

Q28. 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? (Check one that applies the best) A. Each table should have related columns. B. Each separate table should have a primary key. C. 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. D. If a table has columns not dependent on the primary keys, they need to be moved in a separate table. E. Primary key is always UNIQUE and NOT NULL. A28. D. All columns in a table should be dependent on the primary key. This will eliminate transitive dependencies in which A depends on B, and B depends on C, but we're not sure how C depends on A.

Q17. What is a synonym? How is it used? A17. A synonym is used to reference a table or view by another name. The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated. The synonym is linked to the AUTHID that created it.

What option is used in the DECLARE statement to enhance data retrieval performance ? Use the OPTIMIZE FOR 1 ROW to disable list prefetch for online applications that display data on a page-to-page basis. When list prefetch is used, DB2 acquires a list of RIDs from the matching index entries, sorts the RIDs, and then accesses data pages using the RID list. The overhead associated with list prefetch usually causes performance degradation in an online, paging environment. Otherwise, use OPTIMIZE FOR n ROWS, where n is the estimated maximum number of rows that will be retrieved. This influences the access path selection chosen by the DB2 optimizer

What is the SQL syntax used to change all COBOL skills to DB2 in the SKILLS column on the KIT table ? EXEC SQL UPDATE KIT SET SKILLS = 'DB2' WHERE SKILLS = 'COBOL' END-EXEC

What is the difference between primary key & unique index? 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.

2. What are the advantages of using a PACKAGE? 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.

3. What do you mean by NOT NULL WITH DEFAULT? When will you use it? This column cannot have nulls and while insertion, if no value is supplied then it will 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.

4. What are simple, segmented and partitioned table spaces ? Simple Tablespace: Can contain one or more tables Rows from multiple tables can be interleaved on a page under the DBAs 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.

Where would you find information about the type of database authority held by a user ? SYSIBM.SYSDBAUTH.

How about the type of plan authority held by a user ? SYSIBM.SYSPLANAUTH.

How about the type of package authority held by a user ? SYSIBM.SYSPACKAUTH.

How about system privileges held by a user ? SYSIBM.SYSUSERAUTH.

Where could you look if you had a question about whether a column has been defined as an index ? SYSIBM.SYSINDEXES.

Once you create a view, where would information about it reside ? SYSIBM.SYSVIEWS.

If you need to know when a plan was bound and who did it last, where is the information ? SYSIBM.SYSPLAN.

How about getting package information ? SYSIBM.SYSPACKAGE.