Explore topic-wise InterviewSolutions in Current Affairs.

AUTO COMMIT is a SPUFI option that COMMITS the EFFECTS of SQL STATEMENTS automatically if they are SUCCESSFULLY executed.

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 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.

The database request module produced during the pre-compile. The SYSIBM.SYSSTMT table of the DB2 catalog.

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).

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).

Plan is a DB2 OBJECT (produced during the bind PROCESS) that ASSOCIATES one or more database REQUEST MODULES with a plan name.

Plan is a DB2 object (produced during the bind process) that associates one or more database request modules with a plan name.

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.

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.

The path that is USED to get to data SPECIFIED in SQL STATEMENTS.

The path that is used to get to data specified in SQL statements.

Recording SQL STATEMENTS that access a table . SPECIFIED at table CREATION TIME or through alter.

Recording SQL statements that access a table . specified at table creation time or through alter.

At RUN TIME.

At run time.

First CREATE MANAGER table with EMP# as the primary KEY. Then ALTER it to DEFINE the FOREIGN key.

First CREATE MANAGER table with EMP# as the primary key. Then ALTER it to define the foreign key.

When a COLUMN which CONTAINS long text, e.g. remarks, notes, MAY have in most cases less than 50% of the maximum length.

When a column which contains long text, e.g. remarks, notes, may have in most cases less than 50% of the maximum length.

The COLUMN cannot have NULLS. USE it for KEY FIELDS.

The column cannot have nulls. Use it for key fields.

This column cannot have nulls and while insertion, if no VALUE is supplied then it will have zeroes, spaces or date/TIME depending on WHETHER it is numeric, character or date/time.
Use it when you do not want to have nulls but at the same time cannot give values all the time you INSERT this row.

This column cannot have nulls and while insertion, if no value is supplied then it will have zeroes, spaces or date/time depending on whether it is numeric, character or date/time.
Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row.

SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator.
ALIAS: is retained EVEN if table or tablespace is dropped. ALIAS can be created even if the table does not exist. It is used MAINLY in distributed environment to hide the LOCATION info from PROGRAMS. Alias is a global object & is available to all.

SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator.
ALIAS: is retained even if table or tablespace is dropped. ALIAS can be created even if the table does not exist. It is used mainly in distributed environment to hide the location info from programs. Alias is a global object & is available to all.

Synonym is an ALTERNATE name for a table or view used mainly to hide the LEADING QUALIFIER of a table or view. A synonym is ACCESSIBLE only by the creator.

Synonym is an alternate name for a table or view used mainly to hide the leading qualifier of a table or view. A synonym is accessible only by the creator.

The number of DISTINCT values a column or COLUMNS CONTAIN.

The number of distinct values a column or columns contain.

Simple Tablespace:

• Can contain one or more tables
• Rows from multiple tables can be INTERLEAVED on a page under the DBAs control and maintenance

Segmented Tablespace:

• Can contain one or more tables
• Tablespace is divided into segments of 4 to 64 pages in INCREMENTS of 4 pages. Each SEGMENT is DEDICATED to single table. A table can occupy multiple segments.

Partitioned Tablespace:

• Can contain one table
• Tablespace is divided into parts and each part is put in a separate VSAM dataset.

Simple Tablespace:

Segmented Tablespace:

Partitioned Tablespace:

PCTFREE: PERCENTAGE of each PAGE to be left free
FREEPAGE: NUMBER of PAGES to be loaded with data between each free page

PCTFREE: percentage of each page to be left free
FREEPAGE: Number of pages to be loaded with data between each free page

Inner Join: COMBINE information from two or more TABLES by comparing all values that meet the search CRITERIA in the designated column or columns of on e table with all the clause in corresponding columns of the other table or tables. These kinds of join which involve a match in both columns are called inner joins.
Outer join is one in which you want both matching and non matching rows to be RETURNED. DB2 has no specific operator for outer joins, it can be simulated by combining a join and a CORRELATED sub query with a UNION.

Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of on e table with all the clause in corresponding columns of the other table or tables. These kinds of join which involve a match in both columns are called inner joins.
Outer join is one in which you want both matching and non matching rows to be returned. DB2 has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION.

TSO, CICS, IMS and BATCH

TSO, CICS, IMS and BATCH

No.

No.

Not all of them. Some VIEWS are updateable e.g. single table view with all the fields or mandatory fields. EXAMPLES of non-updateable views are views which are joins, views that contain AGGREGATE FUNCTIONS(such as MIN), and views that have GROUP BY clause.

Not all of them. Some views are updateable e.g. single table view with all the fields or mandatory fields. Examples of non-updateable views are views which are joins, views that contain aggregate functions(such as MIN), and views that have GROUP BY clause.

This is generated when the CONSISTENCY TOKENS in the DBRM and the load MODULE are DIFFERENT.

This is generated when the consistency tokens in the DBRM and the load module are different.

SELECT statement has RESULTED in RETRIEVAL of more than one ROW.

SELECT statement has resulted in retrieval of more than one row.

AUTHORIZATION FAILURE

Authorization failure

PRIMARY : a relational database constraint. Primary key consists of one or more COLUMNS that UNIQUELY IDENTIFY a row in the table. For a normalized relation, there is one designated primary key.
Unique index: a physical OBJECT that stores only unique values. There can be one or more unique indexes on a table.

Primary : a relational database constraint. Primary key consists of one or more columns that uniquely identify a row in the table. For a normalized relation, there is one designated primary key.
Unique index: a physical object that stores only unique values. There can be one or more unique indexes on a table.

Only ONE.

Only one.

Causes the data rows to be stored in the ORDER specified in the index. A MANDATORY index defined on a PARTITIONED table space.

Causes the data rows to be stored in the order specified in the index. A mandatory index defined on a partitioned table space.

A QUIESCE flushes all DB2 buffers on to the disk. This GIVES a correct SNAPSHOT of the database and should be used before and after any IMAGECOPY to MAINTAIN consistency.

A QUIESCE flushes all DB2 buffers on to the disk. This gives a correct snapshot of the database and should be used before and after any IMAGECOPY to maintain consistency.

When a table is Loaded with ENFORCE NO OPTION, then the table is LEFT in CHECK PENDING status. It means that the LOAD utility did not perform CONSTRAINT CHECKING.

When a table is Loaded with ENFORCE NO option, then the table is left in CHECK PENDING status. It means that the LOAD utility did not perform constraint checking.

A state in which, an IMAGE copy on a table needs to be TAKEN, In this STATUS, the table is available only for queries. You cannot UPDATE this table. To remove the COPY PENDING status, you take an image copy or USE REPAIR utility.

A state in which, an image copy on a table needs to be taken, In this status, the table is available only for queries. You cannot update this table. To remove the COPY PENDING status, you take an image copy or use REPAIR utility.

REORG reorganizes data on physical STORAGE to reclutser rows, positioning overflowed rows in their proper SEQUENCE, to reclaim space, to restore free space. It is USED after heavy updates, INSERTS and deletes activity and after segments of a segmented tablespace have BECOME fragmented.

REORG reorganizes data on physical storage to reclutser rows, positioning overflowed rows in their proper sequence, to reclaim space, to restore free space. It is used after heavy updates, inserts and deletes activity and after segments of a segmented tablespace have become fragmented.

# of rows in the table
Percent of rows in clustering sequence
# of DISTINCT values of INDEXED column
# of rows moved to a nearby/far way page due to row LENGTH INCREASE

# of rows in the table
Percent of rows in clustering sequence
# of distinct values of indexed column
# of rows moved to a nearby/far way page due to row length increase

After a LOAD, or after mass updates, INSERTS, DELETES, or after REORG.

After a load, or after mass updates, inserts, deletes, or after REORG.

A DB2 UTILITY used to collect STATISTICS about the DATA values 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.

A DB2 utility used to collect statistics about the data values 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.

To insert a NULL, move -1 to the null INDICATOR
To insert a VALID value, move 0 to the null indicator

To insert a NULL, move -1 to the null indicator
To insert a valid value, move 0 to the null indicator

-1 : the FIELD is null
0 : the field is not null
-2 : the field value is truncated

-1 : the field is null
0 : the field is not null
-2 : the field value is truncated

S9(4) COMP.

S9(4) COMP.

USE NULL INDICATORS. SYNTAX ... INTO :HOSTVAR:NULLIND

Use null indicators. Syntax ... INTO :HOSTVAR:NULLIND

As an extra-byte PREFIX to the column VALUE PHYSICALLY, the null prefix is Hex ’00’ if the value is present and Hex ‘FF’ if it is not.

As an extra-byte prefix to the column value Physically, the null prefix is Hex ’00’ if the value is present and Hex ‘FF’ if it is not.

At RUN TIME, when the PREPARE STATEMENT is ISSUED.

At run time, when the PREPARE statement is issued.

Dynamic SQL is a SQL STATEMENT created at program EXECUTION TIME.

Dynamic SQL is a SQL statement created at program execution time.

LOOKS like index page SPLIT has OCCURRED. DO a REORG of the indexes.

Looks like index page split has occurred. DO a REORG of the indexes.

PRINT the OUTPUT DATASET.

Print the output dataset.

100 ( for SUCCESSFUL completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is SET to Yes).

100 ( for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).

1. Avoid having to bind a large number of DBRM members into a plan
2. Avoid cost of a large bind
3. Avoid the entire transaction being UNAVAILABLE during bind and AUTOMATIC rebind of a plan
4. MINIMIZE fallback complexities if changes result in an ERROR.

1. Avoid having to bind a large number of DBRM members into a plan
2. Avoid cost of a large bind
3. Avoid the entire transaction being unavailable during bind and automatic rebind of a plan
4. Minimize fallback complexities if changes result in an error.

They CONTAIN executable code for SQL statements for ONE DBRM.

They contain executable code for SQL statements for one DBRM.

PLAN is marked as INVALID. The next time the plan is accessed, it is REBOUND.

Plan is marked as invalid. The next time the plan is accessed, it is rebound.

PLAN has the EXECUTABLE code for the SQL statements in the HOST program

PLAN has the executable code for the SQL statements in the host program

Determine the POINT at which DB2 acquires or releases locks against table and TABLESPACES, including INTENT locks.

Determine the point at which DB2 acquires or releases locks against table and tablespaces, including intent locks.