join is used to retrive data from different tables using a single sql statement.union is used to combine the results of two or more sql querries.
This is the unit of storage within a table space or index space that is accessed by DB2.
CURRENTDATA option ensures block fetch while selecting rows from a table. In DB2V4 the default has been changed to NO. Therefore it is necessary to change all the bind cards with CURRENTDATA(YES) which is default in DB2V3 & earlier to CURRENTDATA(NO).
It is an alternate name that can be used in SQL statements to refer to a table or view in the same or remote DB2 subsystem.
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.
The SYSIBM.SYSLINKS table contains information about the links between tables created by referential constraints.
minimum is 4.maximum is 64.
In the RECLENGTH column of SYSIBM.SYSTABLES
sql statement gets executed when we open cursor.
The DB2 catalog is a set of tables that contain information about all of the DB2 objects(tables, views, pl etc.).
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.
The attachment facility is an interface between DB2 and TSO, IMS/VS, CICS, or batch address spaces. It allows application programs to access DB2.
By having the length of the record greater than that of a page!
This is a seven part value that consists of a date (yymmdd) and time(hhmmss and microseconds).
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.
Plan is a DB2 object (produced during the bind process) that associates one or more database request modules with a plan name.
The ability to join rows and combaine data from two or more tables is one of the most powerful features of relational system.Three type of joins:
DB2 - participant CICS- coordinator
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).
The buffer manager is the DB2 component responsible for physically trferring 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).
Pagespace refers to either to an unpartitioned table, to an index space, or to a single partition of a partitioned table of index space.
In a subquerry, if the outer querry reffers back to the outcome of innerquerry it is called corelated subquerry. That's why the outer querry is evaluated first unlike an ordinary subquerry.
DSNDB07 is where DB2 does its sorting. It includes DB2’s sort work area and external storage.
With Hold option.
BSDS is a VSAM KSDS.
NO THE USER SHOULD BE GRANTED PRIVILAGE TO USE IT.
The wer is Locksize = ANY.Unless you are Sure what's the Purpose of tablespace ie.,Read-only or R/W.If you use lock size =any, Db2 would automatically determine what type of locks it should use.
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.
The optimizer is a DB2 component that processes SQL statements and selects the access paths.
A STOGROUP is a named collection of DASD volumes to be used by tablespaces and index spaces of databases. The volumes of STOGROUP must be of the same device type.
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.
BIND mainly performs two things syntax checking and authorization checking.It binds together all packages into an application plan hence the name BIND.Apart from this bind has optimiser as a subcomponent.Its function is to determine the optimum access strategy.
When an application program executes with repeatable read protection, rows referenced by the program can’t be changed by other programs until the program reaches a commit point.
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 traction ID to its associated DB2 authorization ID andplan ID(CICS attachment facility).
This information can be found in SYSIBM.SYSINDEXES.
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.
@Simple Table Space@Segmented Table Space and@Partitioned Table Space
Exclusive, update, and share.
Bind is a process that builds “access paths” to DB2 tables. A bind uses the Database Request Modules(s) (DBRM(s)) from the DB2 pre-compile step as input and produces an application plan. It also checks the user’s authority and validates the SQL statements in the DBRM(s).
Each plan is defined uniquely in the SYSIBM.SYSPLANS table to correspond to the traction (s) that are to execute that plan.