The best way of putting a CURSOR to use in a COBOL program is to make use of DECLARE CURSOR, which can be used either in procedure division operation or in working storage. This is being done basically to highlight the SELECT statement. Once DECLARE CURSOR is used, this is followed by OPEN, FETCH and finally CLOSE.
Either you can use SELECT statements of single rows or an alternative way is to use CURSOR.
Yes. The cursor is closed during COMMIT.
A simple tablespace and a segmented tablespace are used to accommodate more than one table. Records are held in a single statement present in the segments held in the pages of segmented tablespaces.
Correlated sub-queries are those queries wherein the nester query on the inner side refers directly back to outer query’s table. For each and every row that is qualified, the evaluation of correlated sub-query is a must.
There are primarily three reasons why SELECT* is never given preference in an embedded SQL program. These are: –
>> In case if there is an alteration in the structure of the table, then the program has to undergo a modification process.
>> All columns will be retrieved by the program including those columns , which might not be used.
>> In case if the user wishes to scan the index, then that will not be possible.
This is the table that contains information on the links that exists between the tables created through referential constraints.
Tablespace that you are using is allocated only to STOGROUP, then you can enter the command ALTER STOGROUP for adding as well as deleting volume. REORG TABLESPACE and RECOVER TABLESPACE are statements that are helpful in creating new STOGROUP that can point towards the new volume. ALTER tablespace and REORG and RECOVER are statements used for altering and recovering the tablespace allocated in the memory.
RCT is expanded as Resource – Control Table and is defined in the DB2/CICS region. This is the component that comprises of features that are gathered through macros of DSNCRCT. RCT matches with the transaction ID that of CICS, with the authorization ID that of DB2. This should also be matched with plan ID.
Data manager can be considered as a component that is capable of managing the databases that are physically present and is capable of invoking other components associated with the system for performing functionalities like logging, locking and in performing other I/O operations.
More than one DB2 application process can access the same data at the same time, is known as concurrency. However, problems can happen such as, lost updates access to unrepeatable reads and uncommitted data.
Predicate is an element of a search condition. It expresses or implies a search condition.
A recovery log is used to identify all the changes formulated in the database. A recovery log can recover the sequence of events that had taken place before the failure when the system failure had occurred.
A simple tablespace and a segmented tablespace are used to accommodate more than one table. Records are held in a single statement present in the segments held in the pages of segmented tablespaces.
IMS, CICS, TSO, and BATCH are the four environments that can access DB2.
COPY PENDING status means an image copy to be taken still pending, and therefore, till that time table cannot be updated. To withdraw this status, one can get the image copy. REPAIR command can be used to eradicate the COPY PENDING status.
UNION and UNION ALL are used to collaborate the results provided by multiple SELECT statements. UNION eliminates duplicates present in the table, whereas UNION ALL retains the duplicates.
Two tasks can be performed using the value function:
-To avoid negative SQLCODEs by taking care of Nulls and 0s in the computation.
-Nulls can be substituted for numeric values used in the computation.
The buffer manager uses some extremely sophisticated buffering techniques to reduce the amount of physical output and input. He also transfers data between an external medium and virtual storage.
Check constraint enables the insertion or updating of the table, which ensures data integrity by considering what values are granted permission.
The DB2 isolation levels are Cursor Stability (CS), Red Stability (RS), Repeatable Read (RR), and Uncommitted Read (UR). UR is used to get access to read-only queries and read-only tables. CS can lock or unlock currently referred rows by the cursor. RS enables the result set stability and unlocks the rows that don’t coordinate with query predictions. RR, known for higher isolation level, ensures the data’s integrity for the complete duration of the entry.
It is also known as “dash DB for Transactionsâ€, it is a cloud SQL database that is fully managed with a high-availability option that has 99.99 per cent uptime SLA. DB2 on the cloud presents independent scaling of computing and storage and rolling security updates.
An SQLCA is a collection of variables that are updated after the implementation of SQL statements. Only one SQLCA is needed for an application that contains executable SQL statements.
To run in multiple environments like private cloud, on-premise, and various public clouds, whether managed and unmanaged, DB2 Warehouse uses Docker containers. DB2 Warehouse can be employed as software, appliance, Intel x86, mainframe, and Linux platforms.
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.
DSNDB07 is where DB2 does its sorting. It includes DB2’s sort work area and external storage.
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.
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.
A DBRM is a DB2 component created by the DB2 pre-compiler containing the SQL source statements extracted from the application program. DBRMs are input to the bind process.
The Data Manager is a DB2 component that manager the physical databases. 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).
It is cursor stability that “tells†DB2 that database values read by this application are protected only while they are being used. (Changed values are protected until this application reaches the commit point). As soon as a program moves from one row to another, other programs may read or the first row.
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 un-repeatable reads.
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.
It is a type of index that (1) locates table rows and (2) determines how rows are grouped together in the tablespace.
CLOSE physically closes the tablespace when no one is working on the object. DB2 (release 2.3) will logically close tablespaces.
There are four buffer pools: BP0, BP1, BP2, and BP32.
The lock modes in DB2 are used to determine whether one lock is compatible to another lock.
<> S Lock (Share): This mode allows the Lock owner and any concurrent process to read but not change the locked page or row.
<> U Lock (Update): This mode allows the Lock owner to read but not change the locked page. This lock mode reduces the chance of deadlocks.
<> X Lock (Exclusive): This mode allows the Lock owner to read or change the Locked page or row.
SPUFI- SQL Processing Using File Input
Usage- It allows direct input of SQL commands in the time sharing option environment.
DCLGEN- DeCLarations GENerator
Usage- It is used to create the DECLARE table and to create the host language copy books for the table definitions.
A buffer pool is the 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.
UR- Uncommitted Read:
It is good for accessing read-only tables and read-only queries.
No record Locking is done.
CS- Cursor Stability:
<> It is a default isolation level.
<> It locks or unlocks each row at a time.
<> It guarantees to return only data which was committed at the time of reading.
RS- Read Stability:
<> It releases Locks on rows that do not satisfy the query predicates.
<> It is used for result set stability or when the future actions on returned rows may be taken.
RR- Repeatable Read:
<> It allows the application to retrieve and operate on rows as many times as needed.
<> It locks the entire table.
AUTO COMMIT is a SPUFI option that commits the effects of SQL statements automatically if they are successfully executed.
The attachment facility is an interface between DB2 and TSO, IMS/VS, CICS, or batch address spaces. It allows application programs to access DB2.
The database request module produced during the pre-compile. The SYSIBM.SYSSTMT table of the DB2 catalog.
DB2 is the huge relational database management system with many components in it. The four major components of DB2 are:
>> System services component: It supports system operations, other communication, logging and similar functions. It handles startup and shutdowns.
>> Locking services component:It provides the necessary controls for managing concurrent access to data. This component is known as Internal Resource Lock Manager (IRLM) and it manages the concurrency issues and deadlocks.
>> Database services component: It supports the definition, retrieval, and update of user and system data. It is responsible for the execution of SQL statements and it also manages the buffer pool.
>> Distributed data facility component: It provides DB2’s, distributed database support.
PACKAGE:They are units, which consists of many executable codes that are meant for SQL statements for one respective DBRM (Database request module).
Below are some advantages of Packages:
<> It ensures that you do not have to bring a large collection of DBRM members together for a particular plan.
<> In case of making changes in a program that will eventually lead to errors, then the decrease in fallback complexities can be made with the help of Package.
<> It is advisable to go for a small collection instead of a large one. Abstain from the cost of large collection of a bind.
<> In the course of automatic binding or rebinding of a specific plan, ensure that the total transactions attached to the process are unavailable.
It is a reserved main storage which is to satisfy the buffer needs for one or more tablespaces or indexes. It is made up of either 4K or 32K pages.
There are four Buffer pools in DB2.
<> BP0
<> BP1
<> BP2
<> BP32
The data types used in DB2 are:
<> SMALLINT
<> DECIMAL
<> CHAR
<> FLOAT
<> INTEGER
<> VARCHAR
<> DATE
<> TIME
It is an alternate name that can be used in SQL training Hyderabad statements to refer to a table or view in the same or remote DB2 subsystem
The path that is used to get to data specified in SQL statements.
DB2 is a subsystem of the MVS operating system. It is a Database Management System ( DBMS ) for that operating system.