DB2 Frequently Asked Questions and Answers:Part 3
Question: What is EXPLAIN in DB2
A: EXPLAIN is used to detail the access paths chosen by DB2 optimizer for SQL statement. This command is used to monitor the performance of SQL statement used in a program.
Answer: 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.
Question: MAX. NO OF ROWS PER PAGE
Answer: 127
Question: The only place of VSAM KSDS in DB2 is?
Answer: BSDS is a VSAM KSDS.
Question: CAN ALL USERS HAVE THE PRIVILAGE TO USE THE SQL STATEMENT SELECT * (DML)?
Answer: NO THE USER SHOULD BE GRANTED PRIVILAGE TO USE IT.
Question: What is the size of a data page?
Answer: 4K to 8K
Question: what's the best locksize that you could use when you create a tablespace?
Answer: 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.
Question: what's the error code for Unique Index Voilation:
Answer: -803
Question: what's the percentage free space for
Answer: ZERO
Question: Can you define an Index if the table size less than 10 PAGES?
Answer: the Answer is : NO
Question: What's the Maximum Length of SQLCA and what's the content of SQLCABC?
Answer: The Max length is 136. and the SQLCABC has the Value of SQLCA.
Question: Can you define an Index if the table size less than 10 PAGES?
Answer: The answer is
Question: what's the percentage free space for
Answer: The answer is ZERO.
Question: What's the maximum number of volumes that can be added to a STOGROUP?
Answer: The answer is 133. Usually it will be difficult monitor more than 3 or 4 volumes to a Stogroup.
Question: What's the maximum number of characters that a tablename can have? Answer: The answer is 18 characters.
Question: What is the meaning of -805 sql return code?
Answer: Program name not in plan. Bind the plan and include the DBRM for the program named as part of the plan.
Question: What is the use of DSNDB07 ?
Answer: This is the area where sorting takes place in DB2
Question: when does the sql statement gets executed when you use cursor in the application programming ?
Answer: sql statement gets executed when we open cursor
Question: What does CURRENTDATA option in bind indicate
Answer: 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).
Question: What is the difference between TYPE 1 index & TYPE 2 index
Answer: 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.
Question: What are the levels of isolation available with DB2V4
Answer: 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.
Question: How do you achieve record locking in DB2 in the versions which do not support record level locking?
Answer: 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 ! Sorry again & Thanx
Question: How do u achieve record level locking in DB2 versions when record level locking is not allowed?
Answer: By having the length of the record greater than that of a page!
Question: In a DB2-CICS program which is acts as co-ordinator and which is participant?
Answer: DB2 - participant CICS- coordinator
Question: How to define the dataitems to receive the fetch items for the SQL?
Answer: Using the DSECT, followed by lines of - 'dataitems DS datatype'.
Question: Re:How will you delete duplicate records from a table?
Answer: Delete From Table1Where Id In (Select Id From Tabel1 As Temp Group By Id Having Count(*) >1)
Question: What is the difference between Where and Having Clause
Answer: WHERE is for Rows and HAVING is for Groups.
Question: How to see the structure of db2 table??
Answer: Using QMF
Question: How do you declare a host variable (in COBOL) for an attribute named EMP-NAME of type VARCHAR(25) ?
Answer: 01 EMP-GRP. 49 E-LEN PIC S9(4) COMP. 49 E-NAME PIC X(25).
Question: What is the maximum number of tables that can be stored on a Partitioned Table Space ?
Answer: ONE
Question: Name the different types of Table spaces.
Answer: 1. Simple Table Space2. Segmented Table Space and3. Partitioned Table Space.
Question: What is EXPLAIN in DB2
A: EXPLAIN is used to detail the access paths chosen by DB2 optimizer for SQL statement. This command is used to monitor the performance of SQL statement used in a program.
Answer: 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.
Question: MAX. NO OF ROWS PER PAGE
Answer: 127
Question: The only place of VSAM KSDS in DB2 is?
Answer: BSDS is a VSAM KSDS.
Question: CAN ALL USERS HAVE THE PRIVILAGE TO USE THE SQL STATEMENT SELECT * (DML)?
Answer: NO THE USER SHOULD BE GRANTED PRIVILAGE TO USE IT.
Question: What is the size of a data page?
Answer: 4K to 8K
Question: what's the best locksize that you could use when you create a tablespace?
Answer: 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.
Question: what's the error code for Unique Index Voilation:
Answer: -803
Question: what's the percentage free space for
Answer: ZERO
Question: Can you define an Index if the table size less than 10 PAGES?
Answer: the Answer is : NO
Question: What's the Maximum Length of SQLCA and what's the content of SQLCABC?
Answer: The Max length is 136. and the SQLCABC has the Value of SQLCA.
Question: Can you define an Index if the table size less than 10 PAGES?
Answer: The answer is
Question: what's the percentage free space for
Answer: The answer is ZERO.
Question: What's the maximum number of volumes that can be added to a STOGROUP?
Answer: The answer is 133. Usually it will be difficult monitor more than 3 or 4 volumes to a Stogroup.
Question: What's the maximum number of characters that a tablename can have? Answer: The answer is 18 characters.
Question: What is the meaning of -805 sql return code?
Answer: Program name not in plan. Bind the plan and include the DBRM for the program named as part of the plan.
Question: What is the use of DSNDB07 ?
Answer: This is the area where sorting takes place in DB2
Question: when does the sql statement gets executed when you use cursor in the application programming ?
Answer: sql statement gets executed when we open cursor
Question: What does CURRENTDATA option in bind indicate
Answer: 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).
Question: What is the difference between TYPE 1 index & TYPE 2 index
Answer: 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.
Question: What are the levels of isolation available with DB2V4
Answer: 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.
Question: How do you achieve record locking in DB2 in the versions which do not support record level locking?
Answer: 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 ! Sorry again & Thanx
Question: How do u achieve record level locking in DB2 versions when record level locking is not allowed?
Answer: By having the length of the record greater than that of a page!
Question: In a DB2-CICS program which is acts as co-ordinator and which is participant?
Answer: DB2 - participant CICS- coordinator
Question: How to define the dataitems to receive the fetch items for the SQL?
Answer: Using the DSECT, followed by lines of - 'dataitems DS datatype'.
Question: Re:How will you delete duplicate records from a table?
Answer: Delete From Table1Where Id In (Select Id From Tabel1 As Temp Group By Id Having Count(*) >1)
Question: What is the difference between Where and Having Clause
Answer: WHERE is for Rows and HAVING is for Groups.
Question: How to see the structure of db2 table??
Answer: Using QMF
Question: How do you declare a host variable (in COBOL) for an attribute named EMP-NAME of type VARCHAR(25) ?
Answer: 01 EMP-GRP. 49 E-LEN PIC S9(4) COMP. 49 E-NAME PIC X(25).
Question: What is the maximum number of tables that can be stored on a Partitioned Table Space ?
Answer: ONE
Question: Name the different types of Table spaces.
Answer: 1. Simple Table Space2. Segmented Table Space and3. Partitioned Table Space.
