还是那只鱼-newcoin's blog

Q121) What are simple, segmented and partitioned table spaces ?

A121) Simple Tablespace: Can contain one or more tables. Rows from multiple tables can be interleaved on a page

under the DBA’s control and maintenance

Segmented Tablespace: Can contain one or more tables. Tablespace is divided into segments of 4 to 64 pages in

increments of 4 pages. Each segment is dedicated to single table. A table can occupy

multiple segments

Partitioned Tablespace: Can contain one table. Tablespace is divided into parts and each part is put in a separate

VSAM dataset.

Q122) What is filter factor?

A122) One divided by the number of distinct values of a column.

Q123) What is index cardinality? – GS

A123) The number of distinct values a column or columns contain.

Q124) What is a synonym ?

A124) Synonym is an alternate name for a table or view used mainly to hide the leading qualifier of a table or view.. A synonym is accessible only by the creator.

Q125) What is the difference between SYNONYM and ALIAS?

A125) SYNONYM : is dropped when the table or tablespace is dropped. Synonym is available only to the creator.

ALIAS : is retained even if table or tablespace is dropped. ALIAS can be created even if the table does

not exist. It is used mainly in distributed environment to hide the location information from

programs. Alias is a global object & is available to all.

Q126) What do you mean by NOT NULL WITH DEFAULT? When will you use it?

A126) This column cannot have nulls and while insertion, if no value is supplied then it will have zeroes, spaces or date/time depending on whether it is numeric, character or date/time.Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row.

Q127) What do you mean by NOT NULL? When will you use it?

A127) The column cannot have nulls. Use it for key fields.

Q128) When would you prefer to use VARCHAR?

A128) When a column which contains long text, e.g. remarks, notes, may have in most cases less than 50% of the maximum length.

Q129) What are the disadvantages of using VARCHAR?

A129) Can lead to high space utilization if most of the values are close to maximum.

Positioning of VARCHAR column has to be done carefully as it has performance implications.

Relocation of rows to different pages can lead to more I/Os on retrieval.

Q130) How do I create a table MANAGER (EMP-NO, MANAGER) where MANAGER is a foreign key which references to EMP-NO in the same table? Give the exact DDL.

A130) First CREATE MANAGER table with EMP-NO as the primary key. Then ALTER it to define the foreign key.

Q131) When is the authorization check on DB2 objects done – at BIND time or run time?

A131) At run time.

Q132) What is auditing?

A132) Recording SQL statements that access a table. Specified at table creation time or through alter.

Q133) max number of columns in a db2 table

A133) 224

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

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

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

A135) The query SELECT * FROM SYSTABLES WHERE OWNER= should work.

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

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

Q137) What is JOIN and different types of JOIN.

A137) The ability to join rows and combine data from two or more tables is one of the most powerful features of relational system. Three type of joins:1. Equi-join 2.Non-equijoin 3.self-join

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

A138) yes possible. until the updation or selection is committed 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 committed.

Q139) How many sub queries can you combine together ?

A139) Total 16 queries and sub queries are 15

Q140) What are the different methods of accessing db2 from tso? How is the connection established between TSO & DB2? （文章来源：http://www.newcoin.info）

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

Q141) How many buffer pools are available in db2?

A141) Ten 32k size buffer pools and fifty 4k size buffer pools (bp0 to bp49)default buffer pools are bp0,bp1,bp2 & bp32

Q142) B37 abend during SPUFI

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

Q143) How many Buffer pools are there in DB2 and what are they?

A143) There are 4 Buffer pools. They are BP0,BP1,BP2 and BP32.

Q144) What is the command used by TSO users to invoke DB2?

A144) DSN RUN

Q145) What is the error code -803 ?

A145) unique index violation

Q146) How do you filter out the rows retrieved from a Db2 table ?

A146) one way is to use The SQL WHERE clause.

Q147) what is a collection?

A147) collection is something that every programmer should assign/Specify for every package. this about 1-18 characters long.

Q148) What is Skeleton cursor table (SKCT)?

A148) The Executable form of a Plan. This is stored in sysibm.sct02 table.

Q149) what’s the equivalent Cobol Data type for Decimal(x,y) in DB2? what does the current SQLID register contain?

A149) Pic s9(x-y)V9(Y) Comp-3; the current SQLID contains the current authorization ID.

Q150) Can we declare DB2 HOST variable in COBOL COPY book?

A150) 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 DCLGEN name END-EXEC or we directly hardcode it in the working storage section.

Q151) What should be specified along with a cursor in order to continue updating process after commit?

A151) With Hold option.

Q152) what is the name of the default db2 catalog database?

A152) DSNDB06

Q153) When Can you be sure that a query will return only one row?

A153) When you use the primary key and only the primary key in the where clause.

Q154) what is the difference between join and union?

A154) join is used to retrieve data from different tables using a single sql statement. union is used to combine the results of two or more sql queries.

Q155) What is a correlated sub query?

A155) In a sub query, if the outer query refers back to the outcome of inner query it is called correlated sub query. That’s why the outer query is evaluated first unlike an ordinary sub query

Q156) What are the functions of Bind?

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

Q157) Max. No of rows per page

A157) 127

Q158) The only place of VSAM KSDS in DB2 is?

A158) BSDS is a VSAM KSDS.

Q159) Can All Users Have The Privilege To Use The SQL Statement Select * (DML)?

A159) No the user should be granted privilege to use it.

Q160) What is the size of a data page?

A160) 4K to 8K