A Community of and for MVS Programmers













DB2 Technical FAQ's

COBOL IMS VSAM
DB2 JCL CICS


Q What are the different data types in DB2?
A Smallint
Integer
DECIMAL
FLOAT
Character,Varcharacter
Graphic,Vargraphic
Date
Time
Timestamp
Q What is a view ? What are the advantages and restrictions of using a view ?
A A view is a virtual table derived from one or more base tables. It prevents unauthorized users from having access to sensitive data. Cannot insert, delete or update a view, which is based on more than one base table.
Q What do you mean by referential Integrity?
A Referential integrity is a condition wherein all references from one table to another are valid. It is the enforcement of all referential constraints(a rule that the value of a foreign key must appear as the value of a primary key of some other specific table.)
Q What is a Subquery ?
A A subquery is a query that is written as part of another query's WHERE clause.
Q What is corrleated subquery ?
A A correlated subquery is one that has a correlation name as a table or view designator in the FROM clause of the outer query and the same correlation name as qualifier of a search condition in the WHERE clause of the subquery.
Q How does the processing of correlated subquery differ from a noncorrelated ?
A The subquery in a correlated subquery is reevaluated for every row of the table or view named on the outer query, while the subquery Of a noncorrelated subquery is evaluated only once.
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) from the DB2 precompile step as input and produces an application plan. It also checks the user's authority and validates the SQL Statements in the DBRM.
Q What is a plan?
A A plan is a DB2 (produced during the bind process) that associates one or more database request modules with a plan name.
Q What is a synonym? How is it used?
A A synonym is used to reference a table or view by another name. The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated.The synonym is linked to the AUTHID that created it.
Q What is an alias and how does it differ from a synonym?
A An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view. The alias is not dropped when the table is dropped.
Q Describe the primary key and the foreign key?
A The primary key is a column or a set of columns that provide a unique identifier to each row of a table. The foreign key is a column or a set of columns that refers to the primary key of another table.
Q What is DDL and DML?
A DDL - Data Definition Language (CREATE, ALTER, TRUNCATE)
DML - Data Manipulation Language (SELECT, INSERT, DELETE & UPDATE)
Q What is DCLGEN?
A DCLGEN stands for declarations generator; it is a facility to generate DB2 sql data structures in COBOL or PL/I programs.
Q What is the significance of the CURSOR WITH HOLD clause in a cursor declaration?
A The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened.
Q What are the three lock types?
A 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.
Q What is deadlock ?
A Deadlock occurs when transactions executing at the same time lock each other out of data that they need to complete their logical units of work.
Q What is the difference between group by and order by ?
A Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.
Q What is a clustered index ?
A For a clustered index DB2 maintains rows in the same sequence as the columns in the index for as long as there is free space. DB2 can then process that table in that order efficiently.
Q What is the equivalent cobol definiton for the datatypes in DB2 ?
A Smallint - S9(4) COMP or S9(4) COMP-4
Integer - S9(9) COMP or S9(9) COMP-4
Char(N) - PIC X(N)
Varchar(N) - 01 NAME.
49 NAME-LEN PIC S9(4) USAGE COMP.
49 NAME-TEXT PIC X(N).
DECIMAL(P,S) - If p<19: S9(p-s)V9(s) COMP-3
Float - USAGE COMP-2
Date - pic x(10).
Timestamp - pic x(26).
Q How do you add columns to an existing table ?
A ALTER table command can be used to add columns to an existing table.
Q What is SPUFI & QMF ?
A SPUFI - Sql Processing Using File Input
QMF - Query Management Facility

Both of them are DB2 interactive menu-driven tools used by developers to create & modify database objects.
Q Name a few utilities available in DB2 ?
A LOAD
MERGE
MODIFY
QUIESCE
REBUILD
RECOVER
REORG
REPORT
REPAIR
RUNSTATS
Q What are the different COLUMN functions ?
A SUM-Returns the total value.
MIN-Returns the minimum value.
AVG-Returns the average value.
MAX-Returns the maximum value.
COUNT-Returns the number of selected rows.
STDDEV-Returns the standard deviation of the column values.
VARIANCE- Returns the variance of the column values.
Q What is a scalar function ?
A A scalar function also produces a single value, but unlike the argument of a column function, an argument of a scalar function is a single value.
Q what is a COLUMN function ?
A A column function produces a single value for a group of rows. You can use the SQL column functions to calculate values based on entire columns of data. The calculated values are from selected rows only (all rows that satisfy the WHERE clause).
Q What are the different types of Table spaces ?
A Simple Table Space
Segmented Table Space
Partitioned Table Space
Q What is Cursor Stability(CS) ?
A A page lock is held only while the cursor is positioned on that page. When the cursor moves to another page, the lock is released. When a page is locked concurrent application programs cannot update or delete a row of the locked page. The current lock is not released until a new lock is acquired. If an application program updates or deletes data, the lock is held until the data is committed. CS applies only to data that is read. All changed data remains locked until COMMIT or ROLLBACK.
Q What is the purpose of a null indicator variable? and what are the possible values in the variable and what do they mean ?
A A Null Indicator is used to indicate whether the host variable has been assigned a null. It used to indicate whether a retrieved character value has been truncated. It is used to set a column to null. The indicator value of –1 means the associated host variable contains null. 0 means the associated host variable does not contain null, nor the returned value is truncated. –2 means the associated host variable contains null due to a numeric or arithmetic conversion error. >0 means the value assigned to the host variable is truncated. The value represented by the indicator variable is the actual length of the character value before truncation.
Q What is commit and rollback ?
A A commit occurs automatically at the end of the program, or by request one or more times during the execution of the program. Releases all locks acquired by the program since the last commit, so that other programs and users may obtain access to the data. Any open cursors are automatically closed. All database changes are made permanent.If any changes made to the tables are not appropriate, then all the changes must by rolled back resulting in the same state of data as they were prior to making changes. Release all locks acquired by the program.





Home | Help Boards | Contact Us | News | Top

© 2002, MVSFORUMS. All Rights Reserved.