Q. What will the copy utility do?
A The copy utility will create an image copy of a tablespace or a dataset within a tablespace. There are two types of image copies : full and incremental. A full image copy copies all pages in a tablespace or dataset. An incremental image copy copies only pages that has been modified since the last use of the COPY utility.
Q. What will the LOAD utility do?
A The LOAD utility can load data into one or more tables in a tablespace or partition. The LOAD can also replace contents of a single partition or an entire tablespace.
Q. What can the MERGECOPY utility do?
A It can merge several incremental copies of a tablespace to make a single incremental copy and it can merge incremental copies with a full-image copy to make a new full-image copy.
Q. What will the RECOVER utility do?
A This utility recovers data to the current state or a previous state. The largest unit of data recovery is the tablespace; the smallest is a page. Data is recovered from image copies of a tablespace and database log change records.
Q. What will the REORG utility do?
A It will reorganize a tablespace to improve access performance and reorganize indexes so that they are more efficiently clustered.
Q. What will the REPAIR utility do?
A It will repair invalid data with valid data and/or reset status conditions. The data may be your own data or data you would not normally access : space map pages and index entries.
Q. What will the RUNSTATS utility do?
A RUNSTATS will scan tablespaces or indexes gathering information about utilization of space and efficiency of indexes. The information is stored in the DB2 catalog and is used by SQL optimizer to select access paths to data during the bind.
Q. What will the STOSPACE utility do?
A This utility updates DB2 catalog columns that tell how much space is allocated for storage groups and related tablespace and indexes.
Q. While the Copy Pending flag is on, is the tablespace that is just recovered available for use?
A No, it is not available.
Q. How do you debug a DB2 STORED PROCEDURE?
A I want someone to answer it.
Q. Max number of columns in a db2 table?
A 224
Q I need to view the number of tables existing under one particular Owner. Is it
possible? If so, pl give the SQL query for this?
A The query SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR =
'owner id' This displays the table names with that If you want only the number of
tables give the following query. SELECT COUNT(*) FROM
SYSIBM.SYSTABLES WHERE CREATOR = 'owner id' Make sure that you
are in correct subsystem.
Q I need to view the number of tables existing under one particular Owner. Is it
possible? If so, pl give the SQL query for this?
A The query SELECT * FROM SYSTABLES WHERE OWNER= should work.
Q I need to view the number of tables existing under one particular Owner. Is it
possible? If so, pl give the SQL query for this?
A Answer to Ravi's query: Db2 records information for its operation in a catalog
which is actually a group of tables. So we can use the SYSTABLES to get
answer to ur query.
Q What is JOIN and different types of JOIN?
A 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:1. Equi-
join2.Non-equijoin3.self-join
Q. Can I alter a table (e.g. adding a column) when other user is selecting some
columns or updating some columns from the same table?
A yes possible. until the updation or selection is commited db2 table will not be
restructured. new column definition will be there but it will not be included until all
the tasks on the table are commited.
Q. How many subqueries can you combine together ?
A Total 16 queries and subqueries are 15
Q . What are the different methods of accessing db2 from tso?B.How is the
connection established between TSO & DB2?
A . There are three ways in establishing tso/db2 connection 1. SPUFI 2. QMF 3.
CATALOG VISIBILITY B. A thread between TSO & DB2 is established while
attempting to make connection between tso & db2.
Q. How many buffer bools are available in db2?
A Ten 32k size bufferpools and fifty 4k size buffer pools (bp0 to bp49)default
buffer pools are bp0,bp1,bp2 & bp32
Q B37 abend during spufi
A The b37 abend in the spufi is because of space requirements , the query has
resulted in so many rows that the spufi.out file is not large enough to handle it,
increase the space allocation of spufi.out file.
Q How many Bufferpools are there in DB2 and what are they?
A There are 4 Bufferpools.They are BP0,BP1,BP2 and BP32.
Q What is the command used by TSO users to invoke DB2?
A DSN RUN
Q what is the error code -803 ?
A unique index violation
Q How do you install DB2?
A Install DB2 according to the procedure fo the manual
Q how do you filter out the rows retrieved from a Db2 table ?
A one way is to use The Sql WHERE clause.
Q what is a collection?
A collection is something that every programmer should assign/Specify for every
package. this about 1-18 characters long.
Q What is Skeleton cursor table (SKCT)?
A The Executable form of a Plan. This is stored in sysibm.sct02 table.
Q what's the equivalent Cobol Data type for Decimal(x,y) in DB2? what does the
current SQLID register contain?
A Pic s9(x-y)V9(Y) Comp-3; the current SQLID contains the current authorization
ID.
Q Can we declare DB2 HOST variable in COBOL COPY book?
A NO.If we declare DB2 host variable in COBOL COPY book, at the time of Pre-
compilation we get the host variable not defined, because pre-compiler will not
expand COBOL COPY book. So we declare it either in DCLGEN with EXEC
SQL INCLUDE Dclgenname END-EXEC or we directly hardcode it in the
working storage section.
Q What should be specified along with a cursor in order to continue updating
process after commit?
A With Hold option.
Q WHAT IS THE NAME OF THE DEFAULT db2 CATALOG DATABASE?
A DSNDB06
Q When Can you be sure that a query will return only one row?
A When you use the primary key and only the primary key in the where clause.
Q what is the difference between join and union?
A 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.
Q What is a corelated subquerry?
A 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
Q What are the functions of Bind?
A 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.
Q MAX. NO OF ROWS PER PAGE?
A 127
Q The only place of VSAM KSDS in DB2 is?
A BSDS is a VSAM KSDS.
Q CAN ALL USERS HAVE THE PRIVILAGE TO USE THE SQL
STATEMENT SELECT * (DML)?
A NO THE USER SHOULD BE GRANTED PRIVILAGE TO USE IT.
Q What is the size of a data page?
A 4K to 8K
Q what's the best locksize that you could use when you create a tablespace?
A The answer 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.
tablespace ie.,Read-only or R/W.If you use lock size =any, Db2 would
automatically determine what type of locks it should use.
Q what's the error code for Unique Index Voilation?
A 803
Q what's the percentage free space for?
A ZERO
Q Can you define an Index if the table size less than 10 PAGES?
A NO
Q What's the Maximum Length of SQLCA and what's the content of SQLCABC?
A The Max length is 136. and the SQLCABC has the Value of SQLCA.
Q what's the percentage free space for?
A The answer is ZERO.
Q What's the maximum number of volumes that can be added to a STOGROUP?
A The answer is 133.Usually it will be difficult monitor more than 3 or 4 volumes to a
Stogroup.
Stogroup.
Q What's the maximum number of characters that a tablename can have?
A The answer is 18 characters.
Q What is the meaning of -805 sql return code?
A Program name not in plan. Bind the plan and include the DBRM for the program
named as part of the plan.
named as part of the plan.
Q when does the sql statement gets executed when you use cursor in the application
programming ?
programming ?
A sql statement gets executed when we open cursor
Q What does CURRENTDATA option in bind indicate?
A 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).
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).
Q What is the difference between TYPE 1 index & TYPE 2 index?
A TYPE 1 & TYPE 2 are specified when an index is created on the table. TYPE 2
index is the option which comes with DB2V4. With TYPE 2 index data can be
retreived faster as only the data pages are locked and not the index pages. Hence
TYPE 2 index is recommended.
index is the option which comes with DB2V4. With TYPE 2 index data can be
retreived faster as only the data pages are locked and not the index pages. Hence
TYPE 2 index is recommended.
Q What are the levels of isolation available with DB2V4?
A CS RR UR( added new for DB2V4 which stands for uncommited read which
allows to retreive records from the space which has exclusive locks also but data
integrity will be affected if this option is used )The best available option for data
integrity & data concurrency is CS.
allows to retreive records from the space which has exclusive locks also but data
integrity will be affected if this option is used )The best available option for data
integrity & data concurrency is CS.
Q How do you achieve record locking in DB2 in the versions which donot support
record level locking?
record level locking?
A Y'day I had posted this que. The answer shud hv read as follows:By having the
record length more than half of the page size
record length more than half of the page size
Q How do u achieve record level locking in DB2 versions when record level locking is
not allowed?
not allowed?
A By having the length of the record greater than that of a page!
Q In a DB2-CICS program which is acts as co-ordinator and which is participant?
A DB2 - participant CICS- coordinator
Q What does DML stand for and what are some examples of it?
A Data Manipulation Language. Some examples are SELECT, INSERT, DELETE,
REPLACE.
REPLACE.
Q How to define the dataitems to receive the fetch items for the SQL?
A Using the DSECT, followed by lines of - 'dataitems DS datatype'.
Q How will you delete duplicate records from a table?
A Delete From Table1Where Id In (Select Id From Tabel1 As Temp Group By Id
Having Count(*) >1)
Having Count(*) >1)
Q What is the difference between Where and Having Clause?
A WHERE is for Rows and HAVING is for Groups
Q How to see the structure of db2 table?
A Using QMF.
Q How do you declare a host variable (in COBOL) for an attribute named EMP-
NAME of type VARCHAR(25) ?
NAME of type VARCHAR(25) ?
A 01 EMP-GRP. 49 E-LEN PIC S9(4) COMP. 49 E-NAME PIC X(25).
Q What is the maximum number of tables that can be stored on a Partitioned Table
Space ?
Space ?
A ONE
Q Name the different types of Table spaces?
A 1. Simple Table Space2. Segmented Table Space and3. Partitioned Table Space
Q what are the max. & min. no. of partitions allowed in a partition tablespace?
Q what are the max. & min. no. of partitions allowed in a partition tablespace?
A minimum is 4.maximum is 64.
Q what is the maximum number of tables that can be joined ?
A fifteen
Q What technique is used to retrieve data from more than one table in a single SQL
statement?
statement?
A The Join statement combines data from more that two tables
Q What is a foreign key?
A It identifies a releated row in another table and establishes a logical relationship
between rows in two tables.
between rows in two tables.
Q Explain the use of the WHERE clause?
A It directs DB2 to extract data from rows where the value of the column is the same
as the current value of the host variable.
as the current value of the host variable.
