Explore topic-wise InterviewSolutions in .

We can have only one clustering index for a table.

DROP ALIAS AL1 is the query to drop a alias.

A partitioned table space must have a clustered index.

An indexed is stored in B-tree format.

SPUFI stands for SQL Processor Using File Input.

Varchar data type maximum size is of 4046 bytes.

Char data type maximum size is of 254 bytes.

DELETE * FROM table-name is the syntax to delete all the rows from a table.

DROP TABLE table-name is the syntax to delete a table from database.

A table can have only one primary key.

Primary keys are optional and we can define them in CRETAE TABLE & ALTER TABLE statements.

GRANT keyword is used to grant privileges on DB2 tables.

REVOKE statement is used to take away the permissions from a Database.

A negative SQL code indicates a failure while a positive one indicates an exception.

Bind step converts all the SQL statements into executable form in COBOL-DB2 program.

We will bind only that package which contains the modified DBRM. No need to bind all the packages.

Application plan consists of one or more DBRM & application package which is kept in buffer pool during program execution.

When you will try to insert or update a record with duplicate key then you will face SQLCODE = -803.

At the time of deadlock or timeout you will face SQLCODE -911.

If a user is not authorized to access DB2 objects, he will face SQLCODE -922.

SQLCODE -818 is a timestamp mismatch of load module and bind timestamp built from DBRM.

We can declare a cursor either i Working Storage Section or in Procedure Division also.

When we use ALTER statement for adding a new column then it will be added at the end of the table.

SELECT COUNT(*) FROM TAB query is used to count the number of rows in a table.

DBRM is the input to the bind process which is produced in the pre-compile step.

A transaction gets an Exclusive lock when it is about to write at that time no other lock can be applied on it.

Repeatable Read ensures highest data integrity as it holds page and lock the rows until a COMMIT point.

Uncommitted read provides maximum concurrency.

SYNCPOINT command is used to commit a transaction in CICS program.

Runstats utility updated the catalog tables with the statistical information.

Reorg utility is used for reorganization of data on physical storage.

Load utility is used to load data into tables.

Locks can be classified based on size, duration and mode.

If a transaction takes a Shared lock on some data, then other transactions can get either a Shared or Update lock on it.

If a transaction takes an Update lock on some data, then other transactions can only get only Shared lock on it.

When a program abends in middle of some transaction then DB2 performs a auto rollback.

This statement is incorrect. Synonyms get dropped automatically when we drop the table but alias of the table remains there. We need to manually drop the alias.

This statement is incorrect as host variables cannot be renamed & redefined.

This statement is incorrect as cursors are used to handle multiple row selections at a time.

This statement is incorrect as SQL statements must be coded in Area B.

DB2 optimizer is used to select the access paths & to process the SQL queries.

DATE data types take 4 bytes and default is 'YYYY-MM-DD'

TIMESTAMP data type takes 10 bytes and default is YYYY-MM-DD:HH:MM:SS-NNNNNN

Check the value of SQLERRD to know how many rows got updated after an update statement.

In Bind step we specify the isolation level.

Locking can be applied on either of these − Page, table and table space.

System services component handles DB2 startup and shutdown.

Database services component is responsible for execution of SQL statements and it manages buffer pool also.

Locking services are provided by Locking services component which is known as Internal Resource Lock Manager (IRLM) and manages concurrency issues and dead locks.

S9(4) COMP is the picture clause of a null indicator.