• Allow you to control the operation of DB2 utility programs, which run separately from the DB2 processor. Using the task panel, you can create and submit a utility job, create and edit a utility job, display the STATUS of or STOP a utility.
- CHECK DATA - Indicates which rows in the table space are to be checked for referential constraints.
- CHECK INDEX - Tests whether indexes are consistent with the data they index, and issues a warning message if it finds an error.
- CHECK LOB - Identifies any structural defects in a LOB table space and any invalid LOB values.
- COPY - Copies data from a table space to a sequential data set. It can be used to copy the entire space or only the data that changed since the last copy was made.
- DIAGNOSE - Generates information useful in diagnosing problems.
- LOAD - Loads data from a sequential data set to DB2 tables.
- MERGECOPY - Merges several partial copies of a table space into one copy and merges partial copies with a full image copy to make a new full image copy.
- MODIFY - Deletes a row from the SYSIBM.SYSCOPY catalog table and the SYSIBM.SYSLGRNG directory table.
- QUIESCE - Establishes a point of recovery for a table space, partition, table space set or a list of table spaces and table space sets.
- REBUILD INDEX - Reconstructs indexes from the table they reference.
- RECOVER - Recovers data to the current state or to a previous point in time by restoring to an image copy, then applying log records.
- REORG INDEX - Reorganizes indexes & unloads data in the format acceptable to the LOAD utility.
- REORG TABLESPACE - Reorganizes table spaces & unloads data in the format acceptable to the LOAD utility.
- REPAIR - Allows you to change specific values within a DB2 database without using SQL.
- REPORT - Reports table space information, including recovery history from SYSIBM.SYSCOPY catalog table, log ranges from SYSIBM.SYSLGRNG, volume serial numbers where archive log datasets reside, and name of all table spaces in a table space set.
- RUNSTATS - Gathers summary information about the characteristics of data in table spaces and indexes.
- STOSPACE - Updates DB2 catalog columns that indicate how much space is allocated for storage groups and related table spaces and indexes.
- UNLOAD - Unloads data from one or more table spaces or image copy data sets to one or more sequential datasets in an external format.
• LOAD – is used to load one or more tables of a table space. It loads records into the tables and builds or extends any indexes defined on them. If the table space already contains data, you can choose whether you want to add the new data to the existing data (LOAD RESUME) or replace the existing data (LOAD REPLACE). The loaded data is processed by any edit or validation routine associated with the table, and any field procedure associated with any column of the table
• RUNSTATS - The RUNSTATS online utility gathers summary information about the characteristics of data in table spaces, indexes, and partitions. DB2 records this information in the DB2 catalog and uses it to select access paths to data during the bind process. It is available to the database administrator for evaluating database design and to aid in determining when table spaces or indexes must be reorganized. There are two formats for the RUNSTATS utility: RUNSTATS TABLESPACE and RUNSTATS INDEX. RUNSTATS TABLESPACE gathers statistics on a table space and, optionally, on indexes or columns; RUNSTATS INDEX gathers statistics only on indexes. To update the catalog statistics, run the RUNSTATS utility after a LOAD or REBUILD INDEX job.
• REPAIR - The REPAIR online utility repairs data. The data can be your own data, or data you would not normally access, such as space map pages and index entries. It can be used to:
o Reset a pending status on a table space or index
o Verify the contents of data areas in table spaces and indexes
o Replace the contents of data areas in table spaces and indexes
o Delete a single row from a table space
o Produce a hexadecimal dump of an area in a table space or index
o Delete an entire LOB from a LOB table space
o Dump LOB pages
• QUIESCE - The QUIESCE online utility establishes a quiesce point (the current log RBA or log record sequence number (LRSN)) for a table space, partition, table space set, or list of table spaces and table space sets, and records it in the SYSIBM.SYSCOPY catalog table. A successful QUIESCE improves the probability of a successful RECOVER or COPY. You should run QUIESCE frequently between regular executions of COPY to establish regular recovery points for future point in time recovery
After loading a table, what other steps needs to be done?
• You need to run RUNSTATS. RUNSTATS update the DB2 catalog of the table. It provides statistics on the table for use for optimization during bind
When loading the table, what will happen to the current records in the table?
• It depends on the LOAD parameter you will use. If you use LOAD RESUME, the records loaded will be appended to the existing records. If you use LOAD REPLACE, the records loaded will overlay the records.
What is EXPLAIN?
• EXPLAIN is used as a BIND option [EXPLAIN (YES)]
• Can be invoked by executing the SQL EXPLAIN statement either statically or dynamically (QMF or SPUFI).
• EXPLAIN is a monitoring tool that obtains information about the access paths for all explainable SQL statements in a package or the DBRMs of a plan (the information appears in table PLAN_TABLE); obtains information on estimated cost of executing an SQL SELECT, INSERT, UPDATE or DELETE statement (the information appears in table DSN_STATEMENT_TABLE); obtains information on User-defined functions referred to in a SQL statement, which includes the specific name and schema (the information appears in table DSN_FUNCTION_TABLE).
Information gathered by EXPLAIN can be of help for designing databases, indexes, and application programs. This information can also tell when to rebind an application or can be of help in determining the access path chosen for a query.
What do you need to do before you do EXPLAIN?
• Make sure that the PLAN_TABLE is created under the AUTHID.
Where is the output of EXPLAIN stored?
• In USERID.PLAN_TABLE.
EXPLAIN has output with MATCHCOLS = 0. What does it mean?
• A nonmatching index scan if ACCESSTYPE = I.
What is SPUFI?
• SPUFI is short for SQL Processor Using File Input.
• SPUFI processes SQL statements that are not embedded in a program. It is especially useful for granting an authorization or creating a table when a host language is not necessary and for testing statements that are to be embedded in a program later. You may name a new member of a previously allocated library. Using the ISPF/PDF editor, you may enter one or more SQL statements to be processed. Each statement must end with a semicolon. Other choices on the task panel allow you to process the statement(s), automatically commit (or not) any changes to the database, and browse the output.
What is QMF?
• QMF is short for Query Management Facility.
• Query Management Facility is an interactive query product that allows you to create reports and charts from relational data under MVS/XA and MVS/ESA. With QMF, you can:
o Access data kept in DB2 tables
o Perform calculations on that data
o Insert new data and change or delete existing data
o Produce and print customized reports for your data
o Create and format charts
o Communicate with other products
What are the different DB2 SQLCODES you have encountered?
• +100 - No row met the search conditions specified in an UPDATE or DELETE statement; The result of a SELECT INTO statement was an empty table; A FETCH statement was executed when the cursor was positioned after the last row of the result table; The result of the subselect of an INSERT statement is empty.
• +0 - found condition / no error condition
• -803 - Execution of the requested INSERT or UPDATE would result in duplicate values occurring on the unique index. The update might also be caused by a DELETE operation of a parent row that cascades to a dependent row with a delete rule of SET NULL.
• -811 - Execution of an embedded SELECT statement or a subselect in the SET clause of an UPDATE statement has resulted in a result table that contains more than one row. Alternatively, a subquery contained in a basic predicate has produced more than one value.
• -818 - The precompiler-generated timestamp in the load module is different from the bind timestamp in the DBRM.
• -805 - An application program attempted to use a DBRM or package that was not found on the plan.
• -911 - The current unit of work was the victim in a deadlock, or experienced a timeout, and had to be rolled back
• -904 – Unsuccessful execution caused by unavailable resource.
How would you correct an SQLCODE of –803?
• Make sure that a unique index is used
• Change the error handling logic inside the application (e.g. If SQLCODE= -803 Then Exec SQL Update…)
• In the application, do a SELECT prior to INSERT or UPDATE. If row is found, do an UPDATE. Otherwise, do an INSERT.
How would you handle an SQLCODE of –811?
• Although not advisable, the easiest way of eliminating a –811 is by using the DISTINCT clause on the SELECT statement (e.g. Exec SQL Select DISTINCT…)
• Check if the WHERE clause used on the SELECT comprises the unique index of the table. If not unique, either ALTER the index of the table or change the WHERE clause on the application such that it uses all columns of the unique index.
• If multiple rows is really expected, Utilize a CURSOR
What are the different logical steps in employing a cursor?
• DECLARE CURSOR
• OPEN
• Perform a FETCH routine until SQLCODE is +100
• CLOSE CURSOR
When will you use SELECT or CURSOR?
• If the query returns only one row, use SELECT. To ensure one row is returned, WHERE clause should be using the unique index columns.
• When the query returns multiple rows and you want to process each row, use a CURSOR, if you just want to check if at least one row exists, use SELECT with a clause of FETCH FIRST 1 ROW ONLY or using DISTINCT function.
What will happen if I issue a COMMIT on a CURSOR?
• CURSOR will be close when COMMIT is issued.
• CURSOR will NOT be closed is the CURSOR has a WITH HOLD option.
What would you do if you encountered an SQLCODE +100 on OPEN cursor?
• There is no SQLCODE +100 on OPEN cursor
What is SQLCODE –502? Would the application abend if not handled?
• Error is raised by DB2 when an OPEN cursor is executed to an already open cursor
• No data changes, the application does not abend
What is an SQLCODE –501? Would the application ABEND if not handled?
• SQLCODE is raised when a CLOSE cursor is executed on a cursor that is not open
No data changes, the application does not ABEND.
What causes an SQLCODE –818? How would you handle/correct an SQLCODE –818?
• The SQL pre-compiler places timestamp in the DBRM and in the load module of the application program. At run-time, the two timestamps are compared with each other. If the two timestamps do not match, the DBRM and the load module were not the result of the same pre-compile. An –818 condition is then raised.
• Recompile and bind the program
What are the different causes of an SQLCODE –805? How would you handle/correct this error?
• The DBRM name was not found in the member list of the plan and there is no package list for the plan.
• The package name was not found because there is no package list for the plan.
• The 'location-name' in the package list was not correct when the application was bound.
• The application was not connected to the proper location.
• Rebind would correct the error
• If due to wrong ‘location name’ or connected to the wrong location, make sure that the application connects to the right location at runtime.
What is deadlock (give a scenario)? What can you do with an SQLCODE –911?
• PROGRAM A has ROW Y locked and PROGRAM B has ROW X locked. Later on, PROGRAM A requests for ROW X and was suspended since PROGRAM B currently locks the row. Then PROGRAM B request for ROW Y and was suspended since PROGRAM A currently locks the row. At this point, PROGRAM A and PROGRAM B are in DEADLOCK. DB2 would then try to rollback changes made by both programs. If the rollback is successful, an SQLCODE –911 is raised. Otherwise, an SQLCODE –913 is raised.
• Rerun the program and hope that the scenario above is not repeated.
• Use the WITH UR clause on the SELECT statements on both programs for minimal row locking.
What causes an SQLCODE –904? How do you fix it?
• Unsuccessful execution caused by unavailable resource.
• Somebody must have loaded the table and is still in the error status. Possible error status are CHECK PENDING, RECOVER PENDING, COPY PENDING. Other possibility is the LOAD job is still running.
• Tablespace or index is in STOP status.
• There could be several reasons for this, but usually it is one of three:
1. An image copy needs to be taken. DB2 turns the copy pending flag on if the DB2 load utility has been run with the parameter "log no". Since "log no" does not log transactions to the system log, DB2 requires an image copy be performed. This gives DB2 a starting place in case recovery needs to be run at some later time. If an image copy was not taken, the rows inserted with the load utility (with "log no") would be lost.
2. Contention with another program. DB2 allows concurrency. This means more than one process can update a table at the same time. But long running update programs can change this. If an update program is started, every time an update happens a lock is placed on the CI (or row if row level locking is being used). DB2 only has a certain amount of locks. Once this maximum threshold of locks is used, DB2 escalates to a tablespace lock. This means only the process that caused the escalation can use the table. All other processes will receive a -904.
3. A DB2 utility is being executed. Certain DB2 utilities require exclusive control of the table. If a process is started while one of these is executing, a -904 is issued.
What is the difference between BIND, REBIND and FREE?
• Modifies the relationship between the DB2 plan/package and the associated application.
o Bind - Establishes or replaces an application plan/package.
o Rebind - Modifies an existing application plan/package.
o Free - Deletes an application plan/package.
• BIND (not to be confused with REBIND) uses as input the SQL/DBRM in the DBRM Library. The assumption with BIND is that the program source code has been changed and that a new DBRM exists.
• REBIND uses the current copy of the DBRM in the DB2 Catalog. The assumption is that the program has not changed but that something external to the program has. REBINDs are normal for situations where indexes are added or dropped or RUNSTATS has been run and the statistics have changed significantly. Probably the most dramatic external change is the installation of a new version of DB2
What does BIND do?
• Check authorizations
• Check DB2 access paths
• Creates the most effective path
When do you need to REBIND?
• Table change
• DB2 upgrade
What is a plan? When will it be created?
• A PLAN is a control structure that is used to execute SQL statements. The control structures can be thought of as the bound or operational form of SQL statements taken from a database request module (DBRM). It can also contain a list of package names. It relates an application process to a local instance of DB2, specifying the processing options.
• The control structure that is contained inside a plan is generated when a BIND PLAN or a BIND PACKAGE is executed. Thus, a PLAN is created after the successful execution of a DB2 BIND subcommand.
What is COMMIT and ROLLBACK?
• Both operations ends a unit of work
• COMMIT is an operation that ends a unit of work by releasing locks so that the database changes that are made by that unit of work can be perceived by other processes
• ROLLBACK is the process of restoring data changed by SQL statements to the state at its last commit point. All locks are freed.
On a batch program that writes a record to a file and at the same time updates a DB2 table, what will happen when a DB2 error occurs? What will happen to the records written into the sequential file?
• The DB2 updates are rolled back but the sequential file inserts stays.
Can we use EXEC SQL COMMIT or EXEC SQL ROLLBACK on a CICS/DB2 application?
• You must not use EXEC SQL COMMIT or EXEC SQL ROLLBACK commands in a CICS application. Since CICS is the coordinator of the two phase commit process, you must use EXEC CICS SYNCPOINT or EXEC CICS SYNCPOINT ROLLBACK commands instead.
On a CICS program that updates a VSAM file and at the same time updates a DB2 table, what will happen when a DB2 error occurs? What will happen to the records inserted into the VSAM file?
• The DB2 updates and VSAM updates are rollbacked.
• Issue EXEC CICS SYNCPOINT to commit both records. Exec CICS Syncpoint command is called a two phase Commit because CICS will first commit changes to the resources under its control like VSAM files, before DB2 changes are committed. Usually CICS signals DB2 to complete the next phase and release all the locks.
What is the basic difference between a JOIN and a UNION?
• A join selects columns from 2 or more tables.
• A union selects rows from 2 or more queries.
What is the difference between UNION and UNION ALL?
• A UNION does not return duplicates.
• A UNION ALL returns duplicates.
When you do UNION, are the results ordered?
• No, it is not ordered.
What are the different types of JOINS?
• Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of one table with all the values 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 Database has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION.
What is COALESCE? What is VALUE?
• The COALESCE function returns the value of the first no null expression.
• The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. The result can be null only if all arguments can be null. The result is null only if all arguments are null.
• VALUE can be specified as a synonym for COALESCE.
Example: This example selects title and price for all books. If the price for a given title is NULL, the price shown is 0.00.
SELECT title, COALESCE(price, 0.00) AS price FROM titles;
What are null indicators? and what are the possible values in the variable and what do they mean?
• A NULL INDICATOR is an indicator variable that could be used to determine:
o whether the value of an associated output host variable is null or indicate that an input host variable value is null. Value = -1.
o the original length of a character string that was truncated during assignment to a host variable. Value = +n. Where n is the original length of the field.
o The value returned is not null. Value = 0.
• Null indicators are defined as binary, PIC S9(4) COMP.
Example: This example selects title and author for all books and the author for a given title is NULLABLE. Ws-author-null-indicator is the null variable.
SELECT title, author
INTO :ws-title
,:ws-author:ws-author-null-ind
FROM titles;
What will happen if you forgot NULL indicator is missing?
• If a null indicator is not used, an SQLCODE -305 is returned.
What SQLCODE will you get if you have a NULL INDICATOR coded?
• It will always be zeroes.
In the sample query below, if ACCOUNT is NULL, will it be returned?
SELECT ACCOUNT
FROM TABLE_1
WHERE ACCOUNT < ‘00000000’
• No, it will not be returned. You should add addition criteria in the WHERE clause of OR ACCOUNT IS NULLS.
What is GTT?
• GTT is short for Global Temporary Table. It is use store immediate SQL results when you don’t want to store it in a permanent table.
• Two types of GTT are Created Temporary Table and Declared Temporary Table.
• Created Temporary Tables
A created temporary table exists only as long as the process that uses it. Temporary tables are created using the CREATE GLOBAL TEMPORARY TABLE statement. When created, the schema for the table is stored in the DB2 system catalog (SYSIBM.SYSTABLES) just like any other table, but the TYPE column is set to 'G' to indicate a global temporary table. Created temporary tables are sometimes referred to as global temporary tables – but this is confusing since declared temporary tables are also referred to as global declared tables.
It is important to remember that a created global temporary table must be created using a DDL CREATE statement before it can be used in any program.
A created temporary table is instantiated when it is referenced in an OPEN, SELECT INTO, INSERT, or DELETE statement, not when it is created. Each application process that uses the temporary table creates a new instance of the table for its use. When using a created temporary table, keep the following in mind:
o Because they are not persistent, some typical database operations including locking, logging, and recovery do not apply to created temporary tables.
o Indexes can not be created on created temporary tables so all access is by a complete table scan.
o Constraints can not be created on created temporary tables.
o A null is the only default value permitted for columns of a created temporary table.
o Created temporary tables can not be referenced by DB2 utilities.
o Created temporary tables can not be specified as the object of an UPDATE statement.
o When deleting from a created temporary table, all rows must be deleted.
o Although views can be created on created temporary tables, the WITH CHECK OPTION can not be specified.
Work file data sets are used to manage the data of created temporary tables. The work database (DSNDB07) is used as storage for processing SQL statements that require working storage – not just for created temporary tables. So if you are using created temporary tables be sure to examine the DB2 Installation Guide for tactics to estimate the disk storage required for temporary work files.
When a temporary work file result table is populated using an INSERT statement, it uses work file space. No other process can use the same work file space as that temporary work file table until the table goes away. The space is reclaimed when the application process commits or rolls back, or when it is deallocated, depending which RELEASE option was used when the plan or package was bound. It is a good idea to keep the work files in a separate buffer pool to make it easier to monitor. IFCID 0311 in performance trace class 8 can be used to distinguish these tables from other uses of the work file.
• Declared Temporary Tables
With Version 7 of DB2, IBM introduced declared temporary tables. Actually, to be more accurate, declared temporary tables were made available in the intermediate DB2 Version 6 refresh.
This new type of temporary table is different than a created temporary table and overcomes many of their limitations. The first significant difference between declared and created temporary tables is that declared temporary tables are specified using a DECLARE statement in an application program – and not using a DDL CREATE statement. Because they are not persistent they do not have descriptions in the DB2 Catalog.
Additionally, declared temporary tables offer significant features and functionality not provided by created temporary tables. Consider:
o Declared temporary tables can have indexes and CHECK constraints defined on them.
o You can issue UPDATE statements and positioned DELETE statements against a declared temporary table.
o You can implicitly define the columns of a declared temporary table and use the result table from a SELECT.
So declared temporary tables are much more functional than created temporary tables. To “create” an instance of a declared temporary table you must issue the DECLARE GLOBAL TEMPORARY TABLE statement inside of an application program. That instance of the declared temporary table is known only to the process that issues the DECLARE statement. Multiple concurrent programs can be executing using the same declared temporary table name because each program will have its own copy of the temporary table.
But there is more work required to use a declared temporary table than there is to use a created temporary table. Before you can declare temporary tables you must create a temporary database and table spaces for them to use. This is accomplished by specifying the AS TEMP clause on a CREATE DATABASE statement. Then, you must create segmented table spaces in the temporary database. Only one temporary database for declared temporary tables is permitted per DB2 subsystem.
When a DECLARE GLOBAL TEMPORARY TABLE statement is issued, DB2 will create an empty instance of the temporary table in the temporary table space. INSERT statements are used to populate the temporary table. Once inserted, the data can be accessed, modified, or deleted. When the program completes, DB2 will drop the instance of the temporary table. Also, be aware that users of temporary tables must have been granted USE authority on the temporary table space.
What is the difference between CHAR and VARCHAR?
• The char is a fixed-length character data type, the varchar is a variable-length character data type.
• Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.
• You can use char when the data entries in a column are expected to be the same size. Maximum length of 254.
• You can use varchar when the data entries in a column are expected to vary considerably in size. Maximum length is 32,672.
How do you define the different DB2 data-types?
• SMALLINT or BINARY PIC S9(4) COMP.
• INTEGER PIC S9(9) COMP.
• DECIMAL(m,n) PIC S9(m)v9(n) COMP-3.
• CHAR(n) PIC X(n)
• VARCHAR(n) 01 VARCHAR
49 LENGTH PIC S9(4) COMP.
49 TEXT PIC X(n).
• DATE PIC X(10).
• TIME PIC X(8).
• TIMESTAMP PIC X(26).
What are SCALAR and COLUMN functions?
• Column functions are an operation that derives its result by using values from one or more rows and produces a single value.
Example:
AVG
COUNT
MAX
MIN
SUM
DISTINCT
• Scalar functions return values to each row.
Example:
CHAR
COALESCE
DATE
DAY
DAYS
DECIMAL
DIGITS
FLOAT
HEX
HOUR
INTEGER
LENGTH
MICROSECOND
MINUTE
MONTH
SECOND
SUBSTR
TIME
TIMESTAMP
VALUE
VARGRAPHIC
YEAR
What is SUBSTR function?
• The SUBSTR function returns a substring of a string.
• SUBSTR is used for string manipulation with column name, first position and string length used as arguments. Eg. SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.
• Example:
SELECT * FROM PROJECTS
WHERE SUBSTR(PROJNAME,1,10) = 'CONVERSION';
Will INDEX be use if we use the function SUBSTR?
• Yes.
What does GROUP BY means?
• The GROUP BY clause specifies a result table that consists of a grouping of the rows
What does HAVING means?
• The HAVING clause filters results obtained by the GROUP BY clause.
• Example:
SELECT DEPT, AVG(SALARY)
FROM Q.STAFF
GROUP BY DEPT
HAVING COUNT(*) > 4
What is the difference between IN and BETWEEN?
• The BETWEEN predicate determines whether a given value lies between two other given values specified in ascending order. The given value will be inclusive to the two other given values.
• The IN predicate compares a value or values with a set of values.
What is LIKE and how is it being used?
• The LIKE predicate searches for strings that have a certain pattern.
• The underscore character (_) represents any single character.
• The percent sign (%) represents a string of zero or more characters.
• Any other character represents a single occurrence of itself.
In using the LIKE predicate, how do you do the following:
• Retrieve NAME that starts with ‘A’?
o WHERE NAME LIKE ‘A%’
• Retrieve NAME whose 2nd character is ‘B’?
o WHERE NAME LIKE ‘_B%’
• Retrieve NAME whose 2nd to the last character is ‘R’?
o WHERE NAME LIKE ‘%R_’
• Retrieve NAME whose last character is ‘Z’?
o WHERE NAME LIKE ‘%Z’
• Retrieve NAME that has ‘X’ in their name?
o WHERE NAME LIKE ‘%X%’
What is correlated sub-query?
• A correlated subquery is an inner query that must be evaluated for each row of the outer query. The query uses aliases to identify the table references.
• Example:
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY
FROM DSN8710.EMP A
WHERE 10 > (SELECT COUNT(*)
FROM DSN8710.EMP B
WHERE A.SALARY < B.SALARY
AND B.SALARY IS NOT NULL)
ORDER BY SALARY DESC;
o The alias “A” identifies the table in the outer query, so that in the subquery, the A.SALARY identifies that column as belonging to the outer query's table. The alias “B” is used for the subquery table (though it is not required).
What is referential integrity (R.I.)?
• The state of a database in which all values of all foreign keys are valid. Maintaining referential integrity requires the enforcement of referential constraints on all operations that change the data in a table upon which the referential constraints are defined.
Explain Primary Key and Foreign Key concepts
• PRIMARY KEY is a unique, non-null key that is part of the definition of a table. A table cannot be defined as a parent unless it has a unique key or primary key.
• FOREIGN KEY is a column or set of columns in a dependent table of a constraint relationship. The key must have the same number of columns, with the same descriptions, as the primary key of the parent table. Each foreign key value must either match a parent key value in the related parent table or be null.
What will happen if I delete a row with R.I.?
• You cannot delete a row of the parent table if records exist in the dependent table.
• If you specify CASCADE, delete the row of parent table will delete the row if the child at the same time.
What are VIEWS?
• A view is a virtual table made up of data from base tables and other views, but not stored separately.
• We use views if we want to hide other columns to the users.
• Views can be updated if there is only one base table.
What are the isolation levels possible?
• CS: Cursor Stability
• RR: Repeatable Read
What is the difference between CS and RR isolation levels?
• CS: Releases the lock on a page after use
• RR: Retains all locks acquired till end of transaction
When do you specify the isolation level? How?
• During the BIND process(ISOLATION LEVEL is a parameter for the bind process).
ISOLATION ( CS/RR )...
I use CS and update a page. Will the lock be released after I am done with that page?
• No.
What are the various locking levels available?
• PAGE, TABLE, TABLESPACE
How does DB2 Database determine what lock-size to use?
• There are three methods to determine the lock-size. They are:
1. Based on the lock-size given while creating the tablespace
2. Programmer can direct the DB2® Database what lock-size to use
3. If lock-size ANY is specified, DB2® Database usually chooses a lock-size of PAGE
What are the three lock types?
• 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.
What are the disadvantages of PAGE level lock?
• High resource utilization if large updates are to be done
What is lock escalation?
• 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 to occur.
Can I use LOCK TABLE on a view?
• No. To lock a view, take lock on the underlying tables.
DB2 Reviewer Part 2 can be viewed here.
No comments:
Post a Comment