还是那只鱼-newcoin's blog

Q161) what’s the best lock size that you could use when you create a tablespace?

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

Q162) what’s the error code for Unique Index Violation?

A162) -803

Q163) Can you define an Index if the table size less than 10 PAGES?

A163) NO

Q164) What’s the Maximum Length of SQLCA and what’s the content of SQLCABC?

A164) The Max length is 136. and the SQLCABC has the Value of SQLCA.

Q165) What’s the maximum number of volumes that can be added to a STOGROUP?

A165) The answer is 133.Usually it will be difficult monitor more than 3 or 4 volumes to a Stogroup.

Q166) What’s the maximum number of characters that a tablename can have?

A166) The answer is 18 characters.

Q167) What is the meaning of -805 SQL return code?

A167) Program name not in plan. Bind the plan and include the DBRM for the program named as part of the plan.

Q168) when does the SQL statement gets executed when you use cursor in the application programming ?

A168) SQL statement gets executed when we open cursor

Q169) What does CURRENTDATA option in bind indicate

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

Q170) What is the difference between TYPE 1 index & TYPE 2 index

A170) 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 retrieved faster as only the data pages are locked and not the index pages. Hence TYPE 2 index is recommended.

Q171) What are the levels of isolation available with DB2V4

A171) CS RR UR( added new for DB2V4 which stands for uncommitted read which allows to retrieve 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.

Q172) How do u achieve record level locking in DB2 versions when record level locking is not allowed?（文章来源：http://www.newcoin.info）

A172) By having the length of the record greater than that of a page!

Q173) In a DB2-CICS program which is acts as co-ordinator and which is participant?

A173) DB2 – participant CICS- coordinator

Q174) What does DML stand for and what are some examples of it?

A174) Data Manipulation Language. Some examples are SELECT, INSERT, DELETE, REPLACE.

Q175) How to define the data items to receive the fetch items for the SQL?

A175) Using the DSECT, followed by lines of – ‘data items DS datatype’.

Q176) How will you delete duplicate records from a table?

A176) Delete From Table1Where Id In (Select Id From Table1 As Temp Group By Id Having Count(*) >1)

Q177) What is the difference between Where and Having Clause

A177) WHERE is for Rows and HAVING is for Groups

Q178) How to see the structure of db2 table??

A178) Using QMF.

Q179) How do you declare a host variable (in COBOL) for an attribute named emp-name of type VARCHAR(25) ?

A179) 01 EMP-GRP. 49 E-LEN PIC S9(4) COMP. 49 E-NAME PIC X(25).

Q180) What is the maximum number of tables that can be stored on a Partitioned Table Space ?

A180) ONE

Q181) Name the different types of Table spaces.

A181) Simple Table Space, Segmented Table Space and Partitioned Table Space

Q182) what are the max. & min. no. of partitions allowed in a partition tablespace?

A182) minimum is 4. maximum is 64.

Q183) what is the maximum number of tables that can be joined ?

A183) fifteen

Q184) What technique is used to retrieve data from more than one table in a single SQL statement?

A184) The Join statement combines data from more that two tables

Q185) Explain the use of the WHERE clause.

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

Q186) What is a DB2 bind?

A186) DB2 bind is a process that builds an access path to DB2 tables.

Q187) What is a DB2 access path?

A187) An access path is the method used to access data specified in DB2 SQL statements.

Q188) What is a DB2 plan?

A188) An application plan or package is generated by the bind to define an access path.

Q189) What is normalization and what are the five normal forms?

A189) Normalization is a design procedure for representing data in tabular format. The five normal forms are progressive rules to represent the data with minimal redundancy.

Q190) What are foreign keys?

A190) These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables.

Q191) Describe the elements of the SELECT query syntax?

A191) SELECT element FROM table WHERE conditional statement.

Q192) Explain the use of the WHERE clause?

A192) WHERE is used with a relational statement to isolate the object element or row.

Q193) What techniques are used to retrieve data from more than one table in a single SQL statement?

A193) Joins, unions and nested selects are used to retrieve data.

Q194) What do the initials DDL and DML stand for and what is their meaning?

A194) DDL is data definition language and DML is data manipulation language. DDL statements are CREATE, ALTER, TRUNCATE. DML statements are SELECT, INSERT, DELETE and UPDATE.

Q195) What is a view? Why use it?

A195) A view is a virtual table made up of data from base tables and other views, but not stored separately.

Q196) Explain an outer join? （文章来源：http://www.newcoin.info）

A196) An outer join includes rows from tables when there are no matching values in the tables.

Q197) What is a subselect? Is it different from a nested select?

A197) A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.

Q198) What is the difference between group by and order by?

A198) Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

Q199) Explain the EXPLAIN statement?

A199) The explain statement provides information about the optimizer’s choice of access path of the sql.

Q200) What is tablespace?

A200) Tables are stored in tablespaces (hence the name)! There are three types of tablespaces: simple, segmented and partitioned.