DB2-4

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.

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.

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.

Q      when does the sql statement gets executed when you use cursor in the application 
         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).

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.

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.

Q    How do you achieve record locking in DB2 in the versions which donot support  
       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 
  

Q    How do u achieve record level locking in DB2 versions when record level locking is 
       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.

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)

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

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 ?

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?

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?

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.

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.

DB2-3

Q.  What is a row?

A       A row is a single occurrence of columns (of data) described by the table definition.

Q.  Describe what a storage group (STOGROUP) is?

A       A STOGROUP is a named collection of DASD volumes to be used by tablespace and index space of databases. The volumes of a STOGROUP must be of the same device type.

Q.  What is meant by synonym?

A       A synonym is an alternate name for a table or view which is stored in the SYSIBM.SYSSYNONYMS table.

Q.  Describe what a table is?

A       A table is a DB2 structure in which column names are used to specify the information that is being stored by row.

Q.  What is a tablespace?

A       A tablespace is a VSAM dataset which is used to store one or more tables. The physical page can consist of 4K or 32K pages.

Q.  How would you move a tablespace (using STOGROUP) to a different DASD volume allocated to that tablespace?

A       1. If the tablespace used is only allocated to that STOGROUP :

·                    ALTER STOGROUP-add volume (new) delete volume (old)

·                    REORG TABLESPACE or RECOVER TABLESPACE

2. Create a new stogroup that points to the new volume. ALTER the tablespace and REORG or RECOVER the tablespace.

Q.  What is the format (internal layout) of 'TIMESTAMP'?

A       This is a seven-part value that consists of a date (yymmdd) and time (hhmmss and microseconds).

Q.  What is a unique index?

A       An index specified as unique is an index for which no duplicates are allowed.

Q.  What is meant by a unit of recovery?

A       This is a sequence of operations within a unit of work (i.e., work done between commit points).

Q.  What is a view?

A       A view is an alternative representation of data contained in one or more tables.  A view can include all or some of the columns contained in the table or tables.

Q.  What does a view do?

A       A view restricts access to specific columns and rows.

  

Q.  What is a data model?

A       A data model is a way of representing entities, attributes and relationships.

Q.  When a transaction issues a commit, to what is the commit writing?

A       A commit triggers a write to a log record.

Q.  Can DASD types assigned to storage groups by intermixed (i.e., 3350s and 3380s)?

A       No.

Q.  What type of information is contained on the BSDS?

A       The BSDS contains information about active and archive logs, their dataset names and volumes on which they reside.

Q.  What are the three types of page locks that can be "held"?

A       Exclusive, update, and share.

Q.  Can DB2 be accessed by TSO users? If yes, which command is used to invoke DB2?

A       DB2 can be invoked by TSO users by using the DSN RUN command.

Q.  What are the names of the different types of DB2 tablespaces?

A       Simple, segmented and partitioned.

Q.  What is the maximum number of partitions allowed in a partitioned tablespace?

A       The maximum is 64.

Q.  How are write I/Os from the buffer pool executed?

A       Asynchronously.

Q.  After a table has been recovered, which flag is turned on?

A       The Copy Pending flag is turned on.

Q.    What is the DB2 catalog?

A       The DB2 catalog is a set of tables that contain information about all the DB2 objects (tables, views, plans, etc.).

Q.    In which column of which DB2 catalog would you find the length of rows for all tables?

A       In the RECLENGTH column of SYSIBM.SYSTABLES.

Q.    What information is held in SYSIBM.SYSCOPY?

A       The SYSIBM.SYSCOPY table contains information about image copies made of the tablespaces. 

Q.    What information is contained in a SYSCOPY "entry"?

A       It includes the name of database, tablespace name, and the image copy type (full, incremental, etc.), as well as the date and time each copy was made.

Q.    What information can you find in SYSIBM.SYSLINKS table?

A       The SYSIBM.SYSLINKS table contains information about the links between tables created by referential constraints.

Q.    Where would you find information about the type of database authority held by a user?

A       SYSIBM.SYSDBAUTH.

Q.    Where could you look if you had a question about whether a column has been defined as an index?

A       This information can be found in SYSIBM.SYSINDEXES.

Q.    Once you create a view, where would information about the view be stored?

A       When a view is created, system information about the view is stored in SYSIBM.SYSVIEWS.

DB2-2

Q.  What will the EXPLAIN do?

A       EXPLAIN obtains information (which indexes are used, whether sorting is necessary, which level of locking is applied) about how SQL statements in the DBRM will be executed, inserting this information into "X".PLAN_TABLE where "X" is the authorization ID of owner of the plan.

Q.  What is a foreign key?

A       A foreign key is a column (or combination of columns) in a table whose values are required to match those of the primary key in some other table.

Q.  What will the FREE command do to a plan?

A       It will drop (delete) that existing plan.

Q.  What will the GRANT option do?

A       It will grant privileges to a list of one or more users. If the GRANT option is used in conjunction with the "PUBLIC" option, then all users are granted privileges. Also, you can grant privileges by objects and types.

Q.  What does the term "grant privileges" mean?

A       Grant privileges means giving access/authority to DB2 users.

Q.  What is a host variable?

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

Q.  What is an image copy?

A       It is an exact reproduction of all or part of a tablespace. DB2 provides utility programs to make full-image copies (to copy the entire tablespace) or incremental image copies to copy only those pages that have been modified since the last image copy.

Q.  What is meant by an index?

A       An index is a set of row identifiers (RIDS) or pointers that are logically ordered by the values of a column that has been specified as being an index. Indexes provide faster access to data and can enforce uniqueness on the row in a table.

Q.  What is an index key?

A       It is a column or set of columns in a table used to determine the order of index entries.

Q.  What is meant by an index scan?

A.      When an entire index (or a portion thereof) is scanned to locate rows, we call this an index scan. This type of access can be used, for example, to select rows of a table in some order and avoid sort for a query.

Q.  What is meant by indicator variable?

A       An indicator variable is an integer variable used to show whether its associated host variable has been assigned a null value.

Q.  What is a join?

A       A join is a relational operation that allows retrieval of data from two or more tables based on matching column values.

Q.  What is meant by locking?

A       Locking is a process that is used to ensure integrity of data. It also prevents concurrent users from accessing inconsistent data. The data (row) is locked until a commit is executed to release the updated data.

Q.  What is a “nonleaf” page?

A       This is a page that contains keys and page numbers of other pages in the index.  Nonleaf pages never point to actual data.

Q.  What is meant by null?

A       This is a special value that indicates the absence of data in a column. This value is indicated by a negative value, usually -1.

Q.  What is an object?

A       An object is anything that is managed by DB2 (that is, databases, tablespaces, tables, views, indexes, or synonyms), but not the data itself.

Q.  What will the DB2 optimizer do?

A       The optimizer is a DB2 component that processes SQL statements and selects the access paths.

Q.  What is a page?

A       This is the unit of storage within a tablespace or indexspace that is accessed by DB2.

Q.  What is a pagespace?

A       Pagespace refers either to an unpartitioned table to an index space, or to a single partition of a partitioned table of index space.

Q.  What is a predicate?

A       A predicate is an element of a search condition that expresses or implies a comparison operation.

Q.  Describe a primary key?

A       A primary key is a key that is unique, nonnull and is part of the definition of a table. A table must have a primary key to be defined as parent.

Q.  What is a recovery log?

A       A recovery log is a collection of records that describes sequence of events that occur in DB2. The information is needed for recovery in the event of a failure during execution.

Q.  What is a Resource Control Table (RCT)?  Describe its characteristics?

A       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 transaction ID to its associated DB2 authorization ID and plan ID (CICS attachment facility).

Q.  Where are plans stored?

A       Each plan is defined uniquely in the SYSIBM.SYSPLAN table to correspond to the transactions that are to execute that plan.

Q.  Describe referential integrity?

A       Referential integrity refers to a feature in DB2 that is used to ensure consistency of data in the database.

Q.  What is meant by a repeatable read?

A       When an application program executes with repeatable read protection, rows referenced by the program cannot be changed by other programs until the program reaches a commit point.