DB2 is the relational database system that runs in an MVS
environment. It was developed by IBM and interfaces with SQL. With the use of
SQL, DB2, databases can be accessed by wide range of host languages. SQL is the
relational database “application language” that interfaces with DB2. Because of
its capabilities, SQL and , in turn, DB2 have gained considerable acceptance.
Thus, a working knowledge of DB2 increases one’s marketability. The questions
and answers that follow are intended for those with working knowledge of DB2 as
a “self-test”. If you need to brush up, the nearest book store is your next
step.
Q: What
is DB2 (IBM Database 2)?
A: DB2 is a subsystem of MVS operating system. It is a database Management system (DBMS) for that operating system.
Q: What is an access path?
A: The path that is used to get to data specified in SQL statements.
Q: What is an alias?
A: It is an alternate name that can be used in SQL statements to refer to a table or view in the same or a remote DB2 subsystem.
Q: Explain what a plan is.
A: A plan is a DB2 object (produced during the bind process) that associates one or more database request modules with a plan name.
Q: What is meant by concurrency?
A: Concurrency is what allows more than one DB2 application process to access the same data at essentially the same time. Problems may occur, such as lost updates, access to uncommitted data and unrepeatable reads.
Q: What is cursor stability?
A: It is cursor stability that tells DB2 that database value read by this application are protected only while they are being used. (Changed values are protected until this application reaches a commit point) .As soon as a program moves from one row to another, other programs may read or change the first row
Q: What is the function of the Data Manager?
A: The Data Manager is a DB2 component that manages the physical database(s). It invokes other system components, as necessary, to perform detailed functions such as locking, logging and physical I/O operations (such as search, retrieval, update and index maintenance).
Q: What is a Database Request Module (DBRM)?
A: A DBRM is a DB2 component created by the DB2 precompiler containing the SQL source statements extracted from the application program. DBRMs are input to the bind process.
Q: What is data page?
A: 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.
Q: What are data types?
A: They are attributes of columns, literals and host variables. The data types are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE and TIME.
Q: What is a Declarations Generator (DCLGEN)?
A: 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 pre compile time. The table or view declares are used by the DB2 pre compiler to verify that correct column names and data types have been specified in the SQL statement.
Q: What does DSNDB07 database do?
A: DSNDB07 IS WHERE db2 does its sorting. It includes DB2’s sort workarea and external storage.
Q: What is meant by dynamic SQL?A: Dynamic SQL are SQL statements that are prepared and executed within a program while the program is executing. The SQL source is contained in host variables rather than being “hard coded” into the program. The SQL statement may change from execution to execution.
Q: What is DB2 bind?
A: A bind is a process that builds “access paths” to DB2 tables. A bind uses the Database Request Module(s) (DBRMS) from the DB2 precompile step as input and produces an application plan. It also checks the user’s authority and validates the SQL statements n the DBRMS.
Q: What information is used as input to the bind process?
A: 1. The database request model produced during the precompile
2. The SYSIBM.SYSTEM table of the DB2 catalog.
Q: What is meant by AUTO COMMIT?
A: AUTO COMMIT is a SPUFI option that commits the effects of SQL statements automatically if they are successfully executed
Q: What is a base table?
A: A base table is a “real” table – a table that physically exist in that there are physical stored records.
Q: What is the function of Buffer Manager?
A: The Buffer Manager is the DB2 component responsible for physically transferring data between an external medium and (virtual) storage (performs the actual I/O operations). It minimizes the amount of physical I/O actually performed with sophisticated buffering techniques (i.e., read-ahead buffering and look-aside buffering)
Q: What is a buffer pool?
A: 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.
Q: How many buffer pools are there in DB2?
A: There are four buffer pools BP0,BP1,BP2 and BP32.
Q: On the create tablespace, what does the CLOSE parameter do.
A: CLOSE physically closes the tablespace when no one is working on the object. DB2 (release 2.3) will logically close tablespaces.
Q: What is a clustering index?
A: It is a type index that (1) locates table rows and (2) determines how rows are grouped together in the tablespace.
Q: What will the COMMIT accomplish?
A: COMMIT will allow data changes to be permanent. This then permits the data to be accessed by other units of work. When a COMMIT occurs, locks are freed so other applications can reference the just-committed data.
Q: What is meant by embedded SQL?
A: They are SQL statements that are embedded within an application program and are prepared during the program preparation process before the program is executed. After it is prepared the statement itself does not change (although values of the host variables specified within the statement might change).
Q: What is meant by entity integrity?
A: Entity integrity is when the primary key is in fact unique and not null.
Q: What will the EXPLAIN do?
A: EXPLAIN obtains information (which indexes are used, whether sorting is necessary, which level of locking is applied) about how SQL statements in the DBRM will be executed, inserting this information into the “X” PLAN TABLE where “X” is the authorization id of the owner of the plan.
Q: What is a foreign key?
A: A foreign key is a column for combination of columns in a table whose values are required to match those of the primary key in some other table.
Q: What will the FREE command do to a plan?
A: It will drop (delete) the existing plan.
Q: What will the GRANT option do?
A: It will grant privileges to a list of one or more users. If the GRANT options is used in conjunction with the “PUBLIC” option, then all users will be granted privileges. Also, you can grant privileges by objects and types.
Q: What does the term “Grant Privileges” mean?
A: Grant Privileges means giving access/authority to DB2 users.
Q: What is a host variable?
A: 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 a colon(:) to tell DB2 that the variable is not a column name.
Q: What is an image copy?
A: It is an exact reproduction of all or part of the tablespace. DB2 provides utility programs to make full-image copies (to copy the entire tablespace) or incremental image copies to copy only those pages that have been modified since the last image copy.
Q: What is meant by an index?
A: An index is a set of row identifiers (RIDs) or pointers that are logically ordered by the values of a column that has been specified as being an index. Indexes provide faster access to data and can enforce uniqueness on the row in the table.
Q: What is an index key?
A: It is a column or set of columns in a table used to determine the order of index entries.
Q: What is meant by an index scan?
A: When an entire index (or a portion thereof) is scanned to locate rows, we call this an index scan. This type of access can be used, for example, to select all rows of a table in some order and avoid a sort for a query.
Q: What is meant by indicator variable?
A: An indicator variable is an integer variable used to show whether its associated host variable has been assigned a null value.
Q: What is a join?
A: A join is a relational operation that allows retrieval of data from two or more tables based on matching column values.
Q: What is meant by locking?
A: Locking is a process that is used to ensure the integrity of data. It also prevents concurrent users from accessing inconsistent data. The data (row) is locked until a commit is executed to release the updated data.
Q: What is a “nonleaf” page?
A: This is a page that contains keys and page numbers of other pages in the index. Nonleaf pages never point to actual data.
Q: What is meant by null?
A: This is a special value that indicates the absence of data in a column. This value is indicated by a negative value, usually – 1.
Q: What is an object?
A: An object is anything that is managed by DB2 (that is, databases, tablespaces, tables, views, indexes or synonyms), but not the data itself
Q: What will the DB2 optimizer do?
A: The optimizer is a DB2 component that processes SQL statements and selects the access paths.
Q: What is a page?
A: This is the unit of storage within a tablespace or index space that is accessed by DB2.
Q: what is a pagespace?
A: Pagespace refers either to an unpartitioned table, to an index space or to a single partition of a partitioned table of index space.
Q: What is a predicate?
A: A predicate is an element of a search condition that expresses or implies a comparison operation.
Q: Describe a primary key?
A: A primary key is a key that is unique, non null and is part of the definition of a table. A table must have a primary key to be defined as a parent.
Q: What is recovery log?
A: 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.
Q: What is a Resource Control Table (RCT)? Describe its characteristics.
A: The RCT is a table that is defined to a DB2/CICS region. It contains control characteristics which are assembled via the DSNCRCT macros. The RCT matches the CICS transaction ID to its associated DB2 authorization ID and plan ID (CICS attachment facility).
Q: Where are plans stored?
A: Each plan is defined uniquely in the SYSIBM.SYSPLAN table to correspond to the transaction(s) that are to execute that plan.
Q: Describe referential integrity.
A: Referential integrity refers to a feature in DB2 that is used to ensure consistency of the data in the database.
Q: What is meant by repeatable read?
A: DB2 is a subsystem of MVS operating system. It is a database Management system (DBMS) for that operating system.
Q: What is an access path?
A: The path that is used to get to data specified in SQL statements.
Q: What is an alias?
A: It is an alternate name that can be used in SQL statements to refer to a table or view in the same or a remote DB2 subsystem.
Q: Explain what a plan is.
A: A plan is a DB2 object (produced during the bind process) that associates one or more database request modules with a plan name.
Q: What is meant by concurrency?
A: Concurrency is what allows more than one DB2 application process to access the same data at essentially the same time. Problems may occur, such as lost updates, access to uncommitted data and unrepeatable reads.
Q: What is cursor stability?
A: It is cursor stability that tells DB2 that database value read by this application are protected only while they are being used. (Changed values are protected until this application reaches a commit point) .As soon as a program moves from one row to another, other programs may read or change the first row
Q: What is the function of the Data Manager?
A: The Data Manager is a DB2 component that manages the physical database(s). It invokes other system components, as necessary, to perform detailed functions such as locking, logging and physical I/O operations (such as search, retrieval, update and index maintenance).
Q: What is a Database Request Module (DBRM)?
A: A DBRM is a DB2 component created by the DB2 precompiler containing the SQL source statements extracted from the application program. DBRMs are input to the bind process.
Q: What is data page?
A: 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.
Q: What are data types?
A: They are attributes of columns, literals and host variables. The data types are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE and TIME.
Q: What is a Declarations Generator (DCLGEN)?
A: 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 pre compile time. The table or view declares are used by the DB2 pre compiler to verify that correct column names and data types have been specified in the SQL statement.
Q: What does DSNDB07 database do?
A: DSNDB07 IS WHERE db2 does its sorting. It includes DB2’s sort workarea and external storage.
Q: What is meant by dynamic SQL?A: Dynamic SQL are SQL statements that are prepared and executed within a program while the program is executing. The SQL source is contained in host variables rather than being “hard coded” into the program. The SQL statement may change from execution to execution.
Q: What is DB2 bind?
A: A bind is a process that builds “access paths” to DB2 tables. A bind uses the Database Request Module(s) (DBRMS) from the DB2 precompile step as input and produces an application plan. It also checks the user’s authority and validates the SQL statements n the DBRMS.
Q: What information is used as input to the bind process?
A: 1. The database request model produced during the precompile
2. The SYSIBM.SYSTEM table of the DB2 catalog.
Q: What is meant by AUTO COMMIT?
A: AUTO COMMIT is a SPUFI option that commits the effects of SQL statements automatically if they are successfully executed
Q: What is a base table?
A: A base table is a “real” table – a table that physically exist in that there are physical stored records.
Q: What is the function of Buffer Manager?
A: The Buffer Manager is the DB2 component responsible for physically transferring data between an external medium and (virtual) storage (performs the actual I/O operations). It minimizes the amount of physical I/O actually performed with sophisticated buffering techniques (i.e., read-ahead buffering and look-aside buffering)
Q: What is a buffer pool?
A: 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.
Q: How many buffer pools are there in DB2?
A: There are four buffer pools BP0,BP1,BP2 and BP32.
Q: On the create tablespace, what does the CLOSE parameter do.
A: CLOSE physically closes the tablespace when no one is working on the object. DB2 (release 2.3) will logically close tablespaces.
Q: What is a clustering index?
A: It is a type index that (1) locates table rows and (2) determines how rows are grouped together in the tablespace.
Q: What will the COMMIT accomplish?
A: COMMIT will allow data changes to be permanent. This then permits the data to be accessed by other units of work. When a COMMIT occurs, locks are freed so other applications can reference the just-committed data.
Q: What is meant by embedded SQL?
A: They are SQL statements that are embedded within an application program and are prepared during the program preparation process before the program is executed. After it is prepared the statement itself does not change (although values of the host variables specified within the statement might change).
Q: What is meant by entity integrity?
A: Entity integrity is when the primary key is in fact unique and not null.
Q: What will the EXPLAIN do?
A: EXPLAIN obtains information (which indexes are used, whether sorting is necessary, which level of locking is applied) about how SQL statements in the DBRM will be executed, inserting this information into the “X” PLAN TABLE where “X” is the authorization id of the owner of the plan.
Q: What is a foreign key?
A: A foreign key is a column for combination of columns in a table whose values are required to match those of the primary key in some other table.
Q: What will the FREE command do to a plan?
A: It will drop (delete) the existing plan.
Q: What will the GRANT option do?
A: It will grant privileges to a list of one or more users. If the GRANT options is used in conjunction with the “PUBLIC” option, then all users will be granted privileges. Also, you can grant privileges by objects and types.
Q: What does the term “Grant Privileges” mean?
A: Grant Privileges means giving access/authority to DB2 users.
Q: What is a host variable?
A: 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 a colon(:) to tell DB2 that the variable is not a column name.
Q: What is an image copy?
A: It is an exact reproduction of all or part of the tablespace. DB2 provides utility programs to make full-image copies (to copy the entire tablespace) or incremental image copies to copy only those pages that have been modified since the last image copy.
Q: What is meant by an index?
A: An index is a set of row identifiers (RIDs) or pointers that are logically ordered by the values of a column that has been specified as being an index. Indexes provide faster access to data and can enforce uniqueness on the row in the table.
Q: What is an index key?
A: It is a column or set of columns in a table used to determine the order of index entries.
Q: What is meant by an index scan?
A: When an entire index (or a portion thereof) is scanned to locate rows, we call this an index scan. This type of access can be used, for example, to select all rows of a table in some order and avoid a sort for a query.
Q: What is meant by indicator variable?
A: An indicator variable is an integer variable used to show whether its associated host variable has been assigned a null value.
Q: What is a join?
A: A join is a relational operation that allows retrieval of data from two or more tables based on matching column values.
Q: What is meant by locking?
A: Locking is a process that is used to ensure the integrity of data. It also prevents concurrent users from accessing inconsistent data. The data (row) is locked until a commit is executed to release the updated data.
Q: What is a “nonleaf” page?
A: This is a page that contains keys and page numbers of other pages in the index. Nonleaf pages never point to actual data.
Q: What is meant by null?
A: This is a special value that indicates the absence of data in a column. This value is indicated by a negative value, usually – 1.
Q: What is an object?
A: An object is anything that is managed by DB2 (that is, databases, tablespaces, tables, views, indexes or synonyms), but not the data itself
Q: What will the DB2 optimizer do?
A: The optimizer is a DB2 component that processes SQL statements and selects the access paths.
Q: What is a page?
A: This is the unit of storage within a tablespace or index space that is accessed by DB2.
Q: what is a pagespace?
A: Pagespace refers either to an unpartitioned table, to an index space or to a single partition of a partitioned table of index space.
Q: What is a predicate?
A: A predicate is an element of a search condition that expresses or implies a comparison operation.
Q: Describe a primary key?
A: A primary key is a key that is unique, non null and is part of the definition of a table. A table must have a primary key to be defined as a parent.
Q: What is recovery log?
A: 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.
Q: What is a Resource Control Table (RCT)? Describe its characteristics.
A: The RCT is a table that is defined to a DB2/CICS region. It contains control characteristics which are assembled via the DSNCRCT macros. The RCT matches the CICS transaction ID to its associated DB2 authorization ID and plan ID (CICS attachment facility).
Q: Where are plans stored?
A: Each plan is defined uniquely in the SYSIBM.SYSPLAN table to correspond to the transaction(s) that are to execute that plan.
Q: Describe referential integrity.
A: Referential integrity refers to a feature in DB2 that is used to ensure consistency of the data in the database.
Q: What is meant by repeatable read?
A: When
an application program executes with repeatable read protection, rows
referenced by the program cannot be changed by other program until the program
reaches a commit point.
Q: What is a row?
Q: What is a row?
A: A
row is a single occurrence of the columns(of data) described by the table
definition.
Q: Describe what a storage group (STOGROUP) is.
A: STOGROUP is a named collection of DASD volumes to be used by tablespaces and index spaces of databases. The volumes of a STOGROUP must be of the same device type.
Q: What is meant by synonym?
A: A synonym is an alternate name for a table or view which is stored in the SYSIBM.SYSSYNONYMS table.
Q: Describe what a table is
A: A table is a DB2 structure in which column names are used to specify the information that is being stored by row.
Q: What is a tablespace?
A: A tablespace is a VSAM dataset, which is used to store one or more tables. The physical page can consist of 4k or 32k pages.
Q: How would you move a tablespace (using STOGROUP) to a different DASD volume allocated to that tablespace?
A: 1. If the tablespace used is only allocated to the 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.
Q: What is the format (internal layout) of “TIMESTAMP”?
A: This is a seven-part value that consists of a date (yymmdd) and time (hhmmss and microseconds).
Q: What is a unique index?
A: An index specified as unique is an index for which no duplicates are allowed.
Q: What is meant by a unit of recovery?
A: This is a sequence of operations within a unit of work (i.e., work done between commit points).
Q: What is a view?
A: A view is an alternative representation of data contained in one or more tables. A view can include all or some of the columns contained in the table or tables.
Q: What does a view do?
A: A view restricts access to specific columns and rows.
Q: What is a data model?
A: A data model is a way of representing entities, attributes and relationships.
Q: When a transaction issues a commit, to what is the commit writing?
A: A commit triggers a write to a log record.
Q: Can DASD types assigned to storage groups to be intermixed (i.e., 330s and 3380s)?
A: NO.
Q: What type of information is contained on the BSDS
A: The BSDS contains information about active and archive logs, their dataset names and the volumes on which they reside.
Q: What are the three types of page locks that can be “held”?
A: Exclusive, update, and share.
Q: Can TSO users access DB2? If yes, which command is used to invoke DB2.
A: TSO users can invoke DB2 by using the DSN RUN command.
Q: What are the names of the different types of DB2 tablespaces?
A: Simple, Segmented and partitioned.
Q: What is the maximum number of partition allowed in a partitioned tablespace?
A: The maximum is 64.
Q: How are write I/Os from the buffer pool executed?
A: Asynchronously.
Q: After a table is recovered, which flag is turned on?
A: The Copy Pending Flag is turned on.
Catalogs
Q: What is the DB2 catalog?
A: The DB2 catalog is a set of tables that contain information about all of the DB2 objects (tables, views, plans, etc).
Q: In which column of which DB2 catalog would you find the length of the rows for all tables?
A: In the RECLENGTH column of SYSIBM.SYSTABLES.
Q: What information is held in SYSIBM.SYSCOPY?
A: The SYSIBM.SYSCOPY table contains information about image copies made of the tablespace.
Q: What information is contained in a SYSCOPY entry?
A: Included is the name of the database, the tablespace name, and the image copy type (full, incremental, etc.,) as well as the date and time each copy was made.
Q: What information can you find in SYSIBM.SYSLINKS table?
A: The SYSIBM.SYSLINKS table contains information about the links between tables created by referential constraints.
Q: Where would you find information about the type of database authority held by a user?
A: SYSIBM.SYSDBAUTH.
Q: Where could you look if you had a question about whether a column has been defined as an index?
A: This information can be found in SYSIBM.SYSINDEXES.
Q: Once you create a view, where would information about the view be stored?
A: When a view is created, system information about the view is stored in SYSIBM.SYSVIEWS.
Q: Describe what a storage group (STOGROUP) is.
A: STOGROUP is a named collection of DASD volumes to be used by tablespaces and index spaces of databases. The volumes of a STOGROUP must be of the same device type.
Q: What is meant by synonym?
A: A synonym is an alternate name for a table or view which is stored in the SYSIBM.SYSSYNONYMS table.
Q: Describe what a table is
A: A table is a DB2 structure in which column names are used to specify the information that is being stored by row.
Q: What is a tablespace?
A: A tablespace is a VSAM dataset, which is used to store one or more tables. The physical page can consist of 4k or 32k pages.
Q: How would you move a tablespace (using STOGROUP) to a different DASD volume allocated to that tablespace?
A: 1. If the tablespace used is only allocated to the 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.
Q: What is the format (internal layout) of “TIMESTAMP”?
A: This is a seven-part value that consists of a date (yymmdd) and time (hhmmss and microseconds).
Q: What is a unique index?
A: An index specified as unique is an index for which no duplicates are allowed.
Q: What is meant by a unit of recovery?
A: This is a sequence of operations within a unit of work (i.e., work done between commit points).
Q: What is a view?
A: A view is an alternative representation of data contained in one or more tables. A view can include all or some of the columns contained in the table or tables.
Q: What does a view do?
A: A view restricts access to specific columns and rows.
Q: What is a data model?
A: A data model is a way of representing entities, attributes and relationships.
Q: When a transaction issues a commit, to what is the commit writing?
A: A commit triggers a write to a log record.
Q: Can DASD types assigned to storage groups to be intermixed (i.e., 330s and 3380s)?
A: NO.
Q: What type of information is contained on the BSDS
A: The BSDS contains information about active and archive logs, their dataset names and the volumes on which they reside.
Q: What are the three types of page locks that can be “held”?
A: Exclusive, update, and share.
Q: Can TSO users access DB2? If yes, which command is used to invoke DB2.
A: TSO users can invoke DB2 by using the DSN RUN command.
Q: What are the names of the different types of DB2 tablespaces?
A: Simple, Segmented and partitioned.
Q: What is the maximum number of partition allowed in a partitioned tablespace?
A: The maximum is 64.
Q: How are write I/Os from the buffer pool executed?
A: Asynchronously.
Q: After a table is recovered, which flag is turned on?
A: The Copy Pending Flag is turned on.
Catalogs
Q: What is the DB2 catalog?
A: The DB2 catalog is a set of tables that contain information about all of the DB2 objects (tables, views, plans, etc).
Q: In which column of which DB2 catalog would you find the length of the rows for all tables?
A: In the RECLENGTH column of SYSIBM.SYSTABLES.
Q: What information is held in SYSIBM.SYSCOPY?
A: The SYSIBM.SYSCOPY table contains information about image copies made of the tablespace.
Q: What information is contained in a SYSCOPY entry?
A: Included is the name of the database, the tablespace name, and the image copy type (full, incremental, etc.,) as well as the date and time each copy was made.
Q: What information can you find in SYSIBM.SYSLINKS table?
A: The SYSIBM.SYSLINKS table contains information about the links between tables created by referential constraints.
Q: Where would you find information about the type of database authority held by a user?
A: SYSIBM.SYSDBAUTH.
Q: Where could you look if you had a question about whether a column has been defined as an index?
A: This information can be found in SYSIBM.SYSINDEXES.
Q: Once you create a view, where would information about the view be stored?
A: When a view is created, system information about the view is stored in SYSIBM.SYSVIEWS.
UTILITIES
Q: What will the copy utility do?
A: The copy utility will create an image copy of a tablespace or a dataset within a tablespace. There are two types of image copies : full and incremental. A full image copy copies all pages in a tablespace or dataset. An incremental image copy copies only pages that have been modified since the last use of the COPY utility.
Q: What will the load utility do?
A: The LOAD utility will load data into one or more tables in a tablespace or partition. The LOAD can also replace the contents of a single partition or of an entire tablespace.
Q: What can the MERGECOPY utility do?
A: It can merge several incremental copies of a tablespace to make a single incremental copy and it can merge incremental copies with a full-image copy to make a new full-image copy.
Q: What will the RECOVER utility do?
A: This utility recovers data to the current state or a previous state. The largest unit of data recovery is the tablespace; the smallest is a page. Data is recovered from image copies of a tablespace and database log change records.
Q: What will REORG utility do?
A: It will reorganize a tablespace to improve access performance and reorganize indexes so that they are more efficiently clustered.
Q: What will the REPAIR utility do?
A: It will repair invalid data with valid data and / or reset status conditions. The data may be your own data or data you would not normally access; space amp pages and index entries.
Q: What will the RUNSTATS utility do?
A: RUNSTATS will scan tablespaces and indexes gathering information about utilization of space and efficiency of indexes. The information is stored in the DB2 catalog and is used by the SQL optimizer to select access paths to data during the bind.
Q: What will the STOSPACE utility do?
A: This utility updates DB2 catalog columns that tell how much space is allocated for storage groups ,related tablespace and indexes.
Q: While the copy pending flag is on, is the tablespace that was just recovered available for use?
A: No, it is not available.
----------***-------------
1. Give examples of DCL, DDL,
DML SQL commands.
A. DATA CONTROL LANGUAGE(DCL)-
grant , revoke.
DATA DEFINITION LANGUAGE(DDL)- create,
alter , drop.
DATA MANIPULATION LANGUAGE(DML)- select,
insert, update, delete.
2.What are data types? What is
meant by null? Give examples of
operators.
A. Data types are attributes of
columns, literals and host variables. They are SMALLINT, INTEGER, FLOAT,
DECIMAL, CHAR, VARCHAR, DATE, TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC. NULL is a
special value that indicates the absence of data in a column. Operators: - , +
, * , / , :: concatenation, date, day, year, days, substr , current date, current time, user.
3. What are the column or
aggregate functions available in SQL?
A. AVG for average, COUNT for
counting the number of rows in the result table, MAX which returns the maximum
value in the column , MIN which returns
the minimum value, SUM which gives the
sum total of the column.
4. Explain the usage of the
terms GROUP BY, HAVING, ORDER BY.
A. GROUP BY - the column function
will calculate its result based on the individual groups created by GROUP BY
specification creating one result for
each group. HAVING - is used to specify the conditions each row of each returned group must
satisfy.
ORDER BY - used to sort the output of a
query using the column names or position of
column in the list of columns named in the select expression.
5. What does the LIKE and IN
and BETWEEN search operators allow you to do?
A. They search for rows where the
column specified satisfies one among a number of values.
LIKE - to select rows based on a
similarity of partial strings.
( - for a single unknown char
and % for 0 to any no. of unknown char)
IN - to select rows where the column
values are one among a specific list of values.
BETWEEN - to select rows having column
values within the given limits.
6. What are the DB2 objects
that can be created using CREATE statement?
What are the DB2 objects you require
before creating a Table?
A. TABLE, INDEX, VIEW, SYNONYM, ALIAS, STOGROUP,
DATABASE, TABLESPACE.
Tablespace and Database are required
before creating a table.
QUESTIONS : medium difficulty
##########################
1. What is a CURSOR and how is
it operated for read, update, delete?
A. A cursor is a named control
structure used to make a selected set of rows available to a program one row at
a time for read or update.
DECLARE CURSOR, OPEN CURSOR, FETCH CURSOR,
CLOSE CURSOR
are used to operate the cursor.
The results table will be created during the execution of the
OPEN CURSOR statement. The values
are fetched from the table row into the host variables and the cursor position remains on the row until the next
FETCH or CURSOR CLOSE statement. During this time ,the cursor position could be
used to update or delete this row from the table using the WHERE CURRENT OF
clause.
2. What is the difference
between Dynamic SQL and Embedded SQL?
A. Dynamic SQL
statements are prepared and executed within a program while a
program is being executed. The SQL source is contained in the host
variables rather than being hard coded into the program and may change from
execution to execution.
Embedded
SQL statements are hard coded within
the application program and are prepared during the program preparation process
before the program is executed.
3. What is the difference
between cascade and restrict with ref. to DELETE statement? What is a primary
key and a foreign key?
A. Cascade and Restrict are part
of the delete rule when specifying the referential constraints between two
tables. Cascade on Delete - deletes all dependent rows from the dependent table
while deleting a row in the parent table. The Restrict rule fails the delete request if a dependent row exists.
A primary key is the unique
identifier of the rows in a table.
A foreign key is a column or
combination of columns in a table whose values are required to match with those
of the primary key in some other table.
4. What is the difference
between a base table, view, synonym, alias, index?
Base table - table that
physically exists and has physical stored records ,can be updated.
View - an alternative representation of data
contained in one or more tables including all or some of the columns from the
constituent tables , cannot be updated, automatically dropped when source
tables are dropped.
Synonym - alternate name
for a table or view, accessible only by creator of synonym, cannot be updated
unless authorized to update base table.
Alias - similar to synonym
, but accessible by all users who have access to the source tables represented
by the alias, can be defined for local or remote objects before they exist,
remains intact after the object it represents has been dropped.
Index - set of row
identifiers or pointers that are logically ordered by the values of a column
that has been specified as being an index, provides faster access to data and
can enforce uniqueness on the row in a table.
5. What is the difference between JOIN and
UNION?
A. JOIN - relational operation
that allows retrieval of data from two or more tables based on
matching column values,
results in addition of columns of the
different constituent tables
with the same key values.
UNION - used to combine the results of two
or more Select statements into a single results
table, results in the
addition of rows of the different tables.
6. What are Subqueries ? What is SQLCA , where is it given in a COBOL
pgm. ?
A. A Subquery
is a query that is written as a part of another query’s WHERE clause.
SQLCA ( SQL Communication Area ) is made up of a series of variables that are
updated
after each SQL statement is executed and contains the sqlcode ,
it is given in the working-storage
section using an SQL INCLUDE statement.
QUESTIONS : Difficult
####################
1. What are the different types of locks that can be held? What is meant by
locking and concurrency ?
What does COMMIT accomplish and what does ROLLBACK accomplish?
A. Locks- Exclusive, Shared , Update.
Locking - used to ensure the integrity
of data and prevents concurrent users from accessing
inconsistent data. The data
is locked until a commit is executed to release the updated data.
Concurrency - allows more than one DB2 application process to access the same data
at
essentially the same time.
COMMIT - allows data changes to be made permanent,
frees all locks so that other
applications can access
the data, closes any open cursors being processed.
The recoverable
processing is that done between two commits.
ROLLBACK - allows data changes (updates)
made by pgm. since the previous commit
to
be undone, locks are
released, cursors are closed.
2. What is cursor stability
and repeatable read? What are the AQUIRE & RELEASE parameters?
- Cursor stability - tells DB2 that database values read by its application are protected only while being used. Changed values are protected until the application reaches a commit point.
Repeatable read - rows referenced by the
program cannot be changed by other pgms. until the program reaches a
commit point.
AQUIRE, RELEASE parameters on the BIND
command specify when tablespace-level
locks are to be acquired and released. The
parameters are:
Aquire- USE : locks are required on first
use.
ALLOCATE: locks are required when the PLAN
is allocated.
Release- COMMIT: all Tablespace-level
locks are released at each synchpoint.
DEALLOCATE: locks are held
until the PLAN is deallocated.
3. What is a PLAN? What is
DBRM? Where are PLANs stored? What does FREE command do?
A. PLAN- a DB2 object produced during the bind process
that associates one or more DBRMs.
DBRM- (data base request module) is a DB2
component created by DB2 precompiler
containing the SQL
source statements extracted from the application program
and is used as input to BIND
process.
BIND- process that builds ‘access paths’ to
DB2 tables, uses the DBRMs and produces
an application plan. It also
checks the users authorization level and validates the
SQL statements in the DBRMs.
PLANs are stored in sysibm.sysplan.
FREE - will drop the existing PLAN.
4. When do the following SQL
codes occur?
A. +000 successful
execution.
-904
unavailable resource
-811
embedded SQL returning more than one rows
-100
row not found
-925
commit not valid
5. What is meant by attachment
facility? What command is used by
TSO users to invoke DB2 ?
What does DSNDB07
database do?
A. The attachment facility is an
interface between DB2 and TSO, IMS/VS,
CICS, or batch address
spaces. It allows application programs to
access DB2.
DSN RUN command is used.
DSNDB07 is where the DB2 does its sorting,
it includes DB2’s sort work area and external storage.
6. What do the following contain?
A. sysibm.sysdatabase - one row for each database.
sysibm.sysdbrm - one row for each dbrm.
sysibm.sysplan - one row for each plan.
sysibm.sysdbauth - shows which authids
have privileges on which databases.
sysibm.sysplanauth- shows which authids
have privileges on which plans.
sysibm.sysrels- one row for each
referential constraint.
No comments:
Post a Comment