VISIT WEBSITE >>>>> http://gg.gg/y83ws?6408155 <<<<<<
Plan is a DB2 object produced during the bind process that associates one or more database request modules with a plan name. The database request module produced during the pre-compile. It allows application programs to access DB2.
A base table is real table — a table that physically exists in that there are physical stored records. A buffer pool is main storage that is reserved to satisfy the buffering requirements for one or more tablespaces or indexes, and is made up of either 4K or 32K pages. CLOSE physically closes the tablespace when no one is working on the object. DB2 release 2. This then permits the data to be accessed by other units of work.
Changed values are protected until this application reaches the commit point. As soon as program moves from one row to another.
Other programs may read or the first row. The Data Manager is a DB2 component that manager the physical databases. A data page is a unit of retrievable data, either 4K or 32K depending on how the table is defined , containing user or catalog information.
These table or view descriptions are then used to check the validity of other SQL statements at precompile time. This is a data item that is used in an SQL statement to receive a value or to supply a value. It must be preceded by colon : to tell DB2 that the variable is not a column name. Page Space refers to either to an unpartitioned table, to an index space, or to a single partition of a partitioned table of index space.
A predicate is an element of a search condition that expresses or implies a comparison operation. A recovery log is a collection of records that describes the sequence of events that occur in DB2.
The information is needed for recovery in the event of a failure during execution. This is a sequence of operations within a unit of work i. The DB2 catalog is a set of tables that contain information about all of the DB2 objects tables, views, plans etc. Included is the name of the database, the table space name, and the image copy type full or incremental etc.. Where could you look if you had a question about whether a column has been defined as an index?
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. But, it has not effect in pseudo-conversational CICS programs. DeClarations GENerator: used to create the host language copy books for the table definitions.
This is used by the pre-compiler to validate the table-name, view-name, column name etc.. Give an example with a host variable in where clause?
Use a question mark in place of a host variable or an unknown value. Locks should be taken on objects in single tablespace for escalation to occur. Determine the point at which DB2 acquires or releases locks against table and tablespaces, including intent locks. A user defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages.
Suppose I have a program which uses a dynamic SQL and it has been performing well till now. Off late, I find that performance has deteriorated. What happened? As an extra-byte prefix to the column value. To insert a NULL, move -1 to the null indicator. To insert a valid value, move 0 to the null indicator. A DB2 utility used to collect statistics about the data value in tables which can be used by the optimizer to decide the access path.
It also collects statistics used for space management. These statistics are stored in DB2 catalog tables. Question 3. Declaration Generator is a facility that is used to form SQL statements that describes a table or view.
At pre-compile time, the table or view descriptions are then used to check the validity of SQL statements. One data row exists for each row pointer, thereby enforcing a maximum of data rows per data page.
Each data row contains a 6-byte row header used to administer the status of the data row. LOB columns are stored in auxiliary tables, not with the primary data. An auxiliary table is stored in a LOB table space. For complete details on large object support, refer to Chapter 9.
There are two types of LOB pages:. Figure There are potentially five components of the LOB map page. The layout of a LOB data page is depicted in Figure The data pages for a DB2 index are somewhat more complex than those for a DB2 table space.
Before you delve into the specifics of the layout of index data pages, you should examine the basic structure of DB2 indexes. A DB2 index is a modified b-tree balanced tree structure that orders data values for rapid retrieval. The values being indexed are stored in an inverted tree structure, as shown in Figure As values are inserted and deleted from the index, the tree structure is automatically balanced, realigning the hierarchy so that the path from top to bottom is uniform.
This realignment minimizes the time required to access any given value by keeping the search paths as short as possible. Every DB2 index resides in an index space. This physical structure is called the index space. Index data pages are always 4KB in size. To implement indexes, DB2 uses the following types of index data pages:. Space map pages determine what space is available in the index for DB2 to utilize. An index space map page is required every 32, index pages.
Only one root page is available per index. The third page in the index space, after the header page and first space map page, is the root page. The root page must exist at the highest level of the hierarchy for every index structure. It is always structured as a non-leaf page. Non-leaf pages are intermediate-level index pages in the b-tree hierarchy. Non-leaf pages need not exist.
If they do exist, they contain pointers to other non-leaf pages or leaf pages. They never point to data rows. Leaf pages contain the most important information within the index.
Leaf pages contain pointers to the data rows of a table. Each RID is a combination of the table space page number and the row pointer for the data value, which together indicate the location of the data value. A DB2 record is the combination of the record prefix and the row. Each record prefix is 6 bytes long. But don't let this information change the way you think. The level of a DB2 index indicates whether it contains non-leaf pages.
The smallest DB2 index is a two-level index. A two-level index does not contain non-leaf pages. The root page points directly to leaf pages, which in turn point to the rows containing the indexed data values. A three-level index, such as the one shown in Figure The larger the number of levels for an index, the less efficient it will be. You can have any number of intermediate non-leaf page levels.
The more levels that exist for the index, the less efficient the index becomes, because additional levels must be traversed to find the index key data on the leaf page. Try to minimize the number of levels in your DB2 indexes; when more than three levels exist, indexes generally start to become inefficient.
Type 1 indexes are DB2's legacy index type. These are the indexes that were available with DB2 since V1. They started to be called Type 1 indexes with the introduction of DB2 V4, which added a new type of index Type 2 indexes.
However, if you are using a past release of DB2 and want to read more about Type 1 indexes, refer to the CD accompanying this book, where you can find a file containing a section from a previous edition of this book covering Type 1 indexes.
Non-leaf pages are physically formatted as shown in Figure Each non-leaf page contains the following:. A byte index page header that houses consistency and recoverability information for the index. A byte physical header that stores control information for the index page. For example, the physical header controls administrative housekeeping, such as the type of page leaf or non-leaf , the location of the page in the index structure, and the ordering and size of the indexed values.
Each non-leaf page contains high keys with child page pointers. The last page pointer has no high key because it points to a child page that has entries greater than the highest high key in the parent. Additionally, Type 2 index non-leaf pages deploy suffix truncation to reduce data storage needs and increase efficiency. Suffix truncation allows the non-leaf page to store only the most significant bytes of the key. For example, consider an index in which a new value is being inserted.
The value, ABCE , is to be placed on a new index page. The last key value on the previous page was ABCD In the older, Type 1 indexes, the entire length of each key was stored.
Entries on a Type 2 leaf page are not stored contiguously in order on the page. A collated key map exists at the end of the Type 2 leaf page to order the entries. Type 2 index leaf pages are formatted as shown in Figure When an entry is added to the index, the collated key map grows backward from the end of the page into the page. By traversing the key map within the page, DB2 can read entries in order by the index key.
Additionally, Type 2 indexes have no subpages. Type 2 leaf page entries add a flag byte. The flag byte indicates the status of the RID. The first bit indicates whether the RID is pseudo-deleted. A pseudo-delete occurs when a RID has been marked for deletion. An array of RIDs is stored contiguously in ascending order to allow binary searching. For non-unique indexes, each index entry is preceded by a count of the number of RIDs.
Type 2 indexes will need to be reorganized periodically to physically delete the pseudo-deleted RIDS. Consider tracking the historical size of indexes for those that are larger than normal and thus, candidates for reorganization. The final physical index structure to explore is the index entry. You can create both unique and non-unique indexes for each DB2 table. When the index key is of varying length, DB2 pads the columns to their maximum length, making the index keys a fixed length.
Email Address:. Let me follow! Blog at WordPress. Follow: RSS Twitter. Mainframe Interview Questions. Mainframe Refresher. Left outer join example 2 How do you use and implement sub-selects? Uncommitted Read Explain statement?
How do you design a table? What are the different consideration for this. What is deadlock and ways of avoiding it. What is isolation level and which parameter is preferred in it and why? What are the common SQL codes? Questions on cursors. Null indicator. What are foreign keys? What is the difference between group by and order by?
What is a subselect? Is it different from a nested select? Compare a subselect to a join Any subselect can be rewritten as a join, but not vice versa. The LIKE keyword allows for string searches. What is cursor stability? Explain other Isolation levels also. It locks all rows that an application references within a unit of work, no matter how large the result set. It can be used to access uncommitted data changes of other applications.
For example, an application using the Uncommitted Read isolation level will return all of the matching rows for the query, even if that data is in the process of being modified and may not be committed to the database. What happens when more than one row satisfies a Select statement that is executed outside of any cursor processing?
How do you retrieve the data from a nullable column? Use null indicators. What are the different values of a null indicator? How do you insert a record with a nullable column? To insert a NULL, move -1 to the null indicator To insert a valid value, move 0 to the null indicator There is one input file and one database is there.
Give the logic? Using a view to access the table Target table T1 is getting deleted. Table T2 has the foreign key.
Comentarios