MF302 - Performance Engineering

What are the 4 functions of a DB2 Optimizer. Receive and Verify SQL Syntax. Analyze and optimize method. Create machine readable instructions to execute Optimized SQL. Execute instructions or store them for future execution. Write to System Catalog Tables. SQL Operations. Generate Best Access Path Tree.

What are the 4 types of Query cost formulas that DB2 optimizer applies?. CPU Based. I/O Based. Stats in DB2 System Catalog. Actual SQL Statement. Parse Tree. Syntax Check.

What are the 4 parts of an Optimizer Statement Processing?. Parsing. Optimization Checks. Plan and Package Creation. Execution of SQL Statements. Validate Tables. Prepare Parse Tree.

What are the steps of Optimizer Parsing?. Parsing. Optimization Checks. Syntax Error check. Execution of SQL Statements. Validate Tables, columns referred in SQL exist in system catalogs. Prepare Parse Tree.

What are the 4 Optimization checks done by DB2 Optimizer?. User Authorization to tables. Checks data types of the columns and their compatibility. Syntax Error check. Gets Information on Indexes on Tables and table statistics from Catalog tables. Validate Tables, columns referred in SQL exist in system catalogs. Prepare Parse Tree. Build Best Access Path.

During the execution of SQL Statements, what checks does Optimizer do?. User has authoritiy to Plan/Package. Checks if the tables, columns used in the plan still exist in Catalog Tables. Prepare Parse Tree. Syntax Error Check. OPtimization Checks.

What does the Parse Tree created in Optimizer Parsing Step contain?. Columns to be retrieved from the table. SQL Operations like JOin, Order By, etc...to be executed. Plan/Package Details. Leaf and Node Information. Number of trips to the tables.

What are the inputs to Optimizer?. System Catalog Tables. Access Path Tree. Best Path Way. Configuration Parameters. Memory Resources. Concurrency Environment. Processor.

What are the key SYSIBM.* System Catalog Tables: SYSCOLDIST. SYSCOLUMNS. SYSINDEXES. SYSINDEXPART. SYSTABLES. SYSTABLESPACE. SYSTABS. SYSSTATS. SYSTABSTATS. SYSPARTINDEX.

Physical Ordering of data as close as possible to the order of the Index is called?. Concurrency. Clustering. Congruency. Compactness.

How many Clustering Indexes can be there on a DB2 table. 1. 3. 2. 4.

If there is no Clustering Index defined on a table with multiple indexes, then the table does not have an index that is clustered. True. False.

What are the valid values of the column SYSINDEXES.CLUSTERING?. Y. N. Space. NULL.

Choose the right answer(s) – The valid value(s) for the column SYSINDEXES.CLUSTERRATIOF is: 0 to 100. 1 to 100. 0 to 1. 0,1,02.

Number between 0 -> 1 when multiplied by 100 gives the % of rows that are in clustering order in the table, is the definition of?. SYSINDEXES.CLUSTERRATIOF. SYSINDEXES.CLUSTERRATIO. SYSINDEXES.FIRSTKEYCARDF. SYSINDEXES.FULLKEYCARDF.

Number between 0 -> 100 and directly indicates the possibility of rows being clustered on Data Page, is the definition of?. SYSINDEXES.CLUSTERRATIOF. SYSINDEXES.CLUSTERRATIO. SYSINDEXES.FIRSTKEYCARDF. SYSINDEXES.FULLKEYCARDF.

Weighted Average of all Index Partitions in terms of num. of rows in the partition, is the definition of?. SYSINDEXES.CLUSTERRATIOF. Partitioned Index on SYSINDEXES.CLUSTERRATIOF. SYSINDEXES.FIRSTKEYCARDF. SYSINDEXES.FULLKEYCARDF.

For a _________Index the value the column SYSINDEXES.CLUSTERRATIOF is -2. Multiple Indexes on the table. Clustering Index. Partitioned Index. Auxiliary Index.

To improve the performance for remote access choose the right answer. ISOLATION(RR). Minimize use of parameter markers. User Access Path Tree. Usage of Delimited markers. Minimize use of Delimited Markers.

Select false statement for reducing the time required for I/O. Preformatting during LOAD and REORG. Keep access path statistics updated. Space allocation to ensure allocation by Cyl. Specify primary quantity for non-partition Indexes.

If SYSINDEXES.CLUSTERRATIO = 0, choose the right answers what it means: Statistics have not been gathered. Statistics have been gathered. DB2 would think that rows are not clustered on data pages. This can make DB2 alter its strategy of the query and can be more expensive. Strategy can be inexpensive.

If SYSINDEXES.CLUSTERRATIO = 100, choose the right answers what it means: Statistics have not been gathered. Statistics have been gathered. DB2 would think that rows on the data pages are clustered. This can make DB2 alter its strategy of the query and can be more expensive. DB2 would think that all the rows on the data pages are clustered. DB2 can do a less expensive strategy to query the Index.

Number of distinct values of the first key column is the definition for the column ________?. SYSINDEXES.FIRSTKEYCARDF. SYSCOLUMNS.FIRSTKEYCARDF. SYSINDEXES.FULLKEYCARDF. SYSCOLUMNS.FULLKEYCARDF.

Number of distinct values of the sum of both the columns part of the index is the definition for the column ____________?. SYSINDEXES.FIRSTKEYCARDF. SYSCOLUMNS.FIRSTKEYCARDF. SYSINDEXES.FULLKEYCARDF. SYSCOLUMNS.FULLKEYCARDF.

What are the Concurrency Environment inputs to Optimizer?. Avg Number of Concurrent Users. Isoloation Level. Number of Locks. Processors. Disk Space.

What are the Configuration Parameter inputs to Optimizer?. Avg Number of Concurrent Users. Isoloation Level. Number of Locks. CPU. Storage Device Characteristics.

The column SYSCOLDIST.CARDF always contains the number of distinct values that are stored in a particular column. True. False.

What are the possible values for SYSCOLDIST.TYPE?. C - Cardinality. F - Frequent Values. After DB2 V8 Version has a value N. N - None Space. F - Frequency. C - Congruent.

SYSCOLDIST table is not updatable. True. False.

Actual value of the Frequently occurring values in the Key Distribution. SYSCOLDIST.COLVALUE. SYSCOLDIST.FREQYENCYF. SYSCOLUMNS.COLVALUE. SYSCOLUMNS.FREQUENCYF.

Stores Values 0 -> 1. When multiplied with 100 gives the % of rows that contain the value stored in the column COLVALUE. SYSCOLDIST.COLCARDF. SYSCOLDIST.FREQYENCYF. SYSCOLUMNS.COLCARDF. SYSCOLUMNS.FREQUENCYF.

SYSCOLUMNS table is not updatable. True. False.

SYSCOLUMNS is very useful in the following areas of SDLC: Build. Implementation. Testing. Design.

Choose the Right Option: The column COLCARDF belongs to the following table. SYSINDEXES. SYSCOLDIST. SYSCOLUMNS. SYSTABLESPACE.

_________________is a condition coded in the where clause of a SQL Statement, meant to filter and get the right set of results that the SQL statement is designed for. Predicate. Decision. Accuracy. Destination.

Filter factor is: 1/COLCARDF. COLCARDF. COLCARDF/COLVALUE. 1/COLVALUE.

_________ gives an idea of how many rows will qualify for the result: FILTER FACTOR. COLCARDF. COLVALUE. COLDIST.

All the predicates of a SQL have the same filter factor. True. False.

Higher the filter Factor, higher are the chances that Optimizer will consider the user of an Index and vice-versa. True. False.

I/O Cost is calculated as: Filter Factor*No. of rows/pages. Filter Factor*No. of rows/pages, using cluster ratio of the Index. Only by using cluster ratio of the Index. I/O cost is dependent on the processor speed.

Pick the correct answer that correctly defines the column SYSCOLUMNS.HIGH2KEY. First 8 bytes of highest value in the column. Bytes 1 to 8 of the highest value in the column. First 8 bytes of the second highest value in the column. Highest value in the column.

Pick the correct answer that correctly defines the column SYSCOLUMNS.LOW2KEY. First 8 bytes of lowest value in the column. Bytes 1 to 8 of the lowest value in the column. First 8 bytes of the second lowest value in the column. Lowest value in the column.

Which columns of Catalog table column(s) give the Optimizer an idea of the size of index Tree and how many active leaf pages are available? (Choose exactly two options). SYSTABSTATS.NPAGES. SYSINDEDXES.NLEAF. SYSTABLESPACE.NACTIVE. SYSINDEXES.NLEVELS. SYSTABLES.NPAGES.

Number of Active Leaf Pages in the Index is called: SYSINDEXES.NLEAF. SYSINDEXPART.NLEAF. SYSINDEXES.NLEVELS. SYSINDEXPART.NLEVELS.

Number of Levels in the Index Tree is called: SYSINDEXES.NLEAF. SYSINDEXPART.NLEAF. SYSINDEXES.NLEVELS. SYSINDEXPART.NLEVELS.

Which catalog tables contain partition statistics predominantly for parallel processing? (Check exactly two): SYSCOLDIST. SYSCOLUMNS. SYSINDEXES. SYSINDEXPART. SYSTABLES. SYSTABLESPACE. SYSTABSTATS.

LIMITKEY is part of the catalog table SYSTABSTATS and specifies the maximum value up to which the data in the table can be stored. True. False.

Choose the correct answers about LIMITKEY: Is applicable only when the Index is partitioned. Gives the max value (in an Internal format) up to which the data can be stored in the partition. None of the above. Is applicable only when the Index is non-partitioned.

Which system catalog table column can be calculated using the number of I/O calls to be made to fetch all the data using table space scan?. SYSTABLES.NPAGES. SYSINDEXES.NLEAF. SYSINDEXES.NLEVELS.

What does the catalog table column SYSTABLES.CARDF give?. Catalog table has one row for each table. Gives the number of rows in the table. Total number of LOBs in an Auxiliary table. All the options. None of the options.

Number of pages on which rows of a table appear IS: SYSTABLES.NPAGES. SYSTABLES.PCTROWCOMP. SYSTABLES.CARDF. SYSTABLES.NLEVELS.

Percentage of rows compressed (Value <= 100) within the active rows in the table: SYSTABLES.NPAGES. SYSTABLES.PCTROWCOMP. SYSTABLES.CARDF. SYSTABLES.NLEVELS.

Higher the SYSTABLES.PCTROWCOMP value indicates: Lower CPU Overhead. Higher CPU Overhead. No overhead at all. CPU & I/O cost is not related in this regard.

Which system catalog column gives the Number of active pages in the table space?. SYSTABLES.NPAGES. SYSTABLESPACE.NACTIVE. SYSINDEXES.NLEVELS. SYSINDEXES.NPAGES.

When a page is formatted for rows, even if it currently contains no rows at all. The page in the table space is termed active. The page in the table space is termed inactive. The page in the table space is termed dormant. The page in the table space is termed unusable.

Identify the key columns of the catalog table SYSTABSTATS. NPAGES. CARDF. NLEVELS. NLEAF. LIMITKEY.

Identify the false statements about Optimizer Hints from below. It is possible to influence the Optimizer by giving Hints. Should be only done by an experienced programmer. It is a temporary fix to resolve crisis situation immediately. Optimizer hints cannot be given in dynamics queries.

Identify the ones that are not pre-requisites for providing hints: PLAN_TABLE should be created in the right format specific to the version. COST_TABLE should be created in the right format. Optimization hints should be enabled in the sub-system. The Optimization hints field on the Installation Panel DNSTIP4 should be set to Yes.

If the Optimization hints on the installation Panel DNSTIP4 is marked No, when hints are given in the SQL statement, will return an error code -220. True. False.

The default filter factor is: 1/75. 1. 1/25. 0.5.

Which field in PLAN_TABLE contains the join information. METHOD. JOINC. OPTHINT.

Choose the false answer about DB2 optimizer. Creates Machine readable instruction to execute optimized sql. Analyze the environment and optimize the method of satisfying SQL. Verify syntax of SQL. Does not execute the SQL instruction.

Which of the following is not true about filter factor. For each variable there could be only a single filter factor. For a high filter factor, DB2 uses index. Catalog table is not updated, in this case filter factor = 1/25 is used. Filter factor and COLCARDF function are interdependent.

Put the sequence below in right order related to Optimizer Hint: 1. Optimizer hint column of plan table is updated 2. Find the existing Access Path 3. Query Number column updated in Plan table 4. Inform DB2 to use hint. 1,2,3,4. 2,3,1,4. 2,3,4,1. 3,2,1,4.

Match the SQLCODE with the scenario: SQLCODE returned +394. SQLCODE returned +395. SQLCODE returned +20007. SQLCODE returned 0.

Match the UDF and associated usage. External Scalar Function. External table function. Sourced Function.

Select the correct answer about ASUTIME. Indicates how much ASU time can be defined for a function. Indicates how much CPU time can be defined for a function. Indicates total CPU time needed by all the scalar function. None of the above.

For before trigger, the must keyword is. RETURNS. WLM ENVIRONMENT. NO CASCADE. REFERENCING.

Buffer pool does not have the following type of page. New. In-Use. Updated. Available.

Buffer pool threshold that can be updated is: Immediate wire Threshold. Data Management Threshold. Sequential Prefetch Threshold. Sequential Steal Threshold.

For a branch table in bank having 100 rows and is being updated by every transaction. Best way of tuning will be. High Value of deferred write threshold. Low value of deferred write threshold. Very Low value of vertical deferred write threshold. high value of sequential prefetch threshold.

For a customer table in bank having millions of rows and are being accessed randomly or are updated sequentially in batch. Best way of tuning will be. High Value of deferred write threshold. Very low value of deferred write threshold. Very low value of vertical deferred write threshold. Low Value of Vertical Deferred write Threshold.

For Mixed workloads (sequential and parallel processing), the best way of tuning would be: VPSEQT should be very high. VPSEQT should be set according to the ratio of Sequential to Parallel processing. Very low value of deferred write threshold. Very low value of vertical deferred write threshold. Low Value of Vertical Deferred write Threshold.

Match the table name and information about the same: PLAN_TABLE. DSN_STATEMENT_TABLE. DSN_FUNCTION_TABLE.

Check the BUFFER Pool Thresholds that can be updated from below: VPSEQT (Sequential Steal Threshold). IWTH (Immediate Write Threshold). SPTH (Sequential Prefetch Threshold). VPPSEQT (Virtual Buffer pool Parallel Sequential Threshold). DMTH (Data Management Threshold). VPXPSEQT (Virtual buffer pool assisting parallel sequential threshold). DWQT (Deferred Write Threshold). VDWQT (Vertical Deferred Write Threshold).

How Many buffer pools of 4 KB are available?. 0. 10. 50. 49.

Match the following: Merge Scan Join. Sorts Needed. Hybrid Join. Nested Loop Join. First table accessed.

Method column of PLAN_TABLE can be Alphanumeric. True. False.

Identify the items that do not help in improving the performance of a Join: Reduce the number of rows used in the Join by specifying more predicates. The join columns which are indexed should match in data type and length to avoid conversions. Use Application Join over DB2 Join. Use DB2 Join over Application Join. Do the Join and then filter the rows. Always allow DB2 to choose the best possible join for you instead of you influencing it.

Identify the Memory Resources available to DB2 Optimizer as Inputs: RID. FID. SORT. BUFFER. KAS.

Identify the inputs for Optimizer. System Catalog. SQL Statements. Available Resources. PLAN_TABLE.

A Page written gets updated when?. DB2 takes a Checkpoint. When DMTH is reached. When SWQT is reached. When DWQT is reached. When VDWQT is reached.

Correct order of Steps to retain OLD access path even after rebind: 1. Update PLAN_TABLE Set HINT = OLDPATH 2. Give that path some Name 3. Modify Query to include Query No In it 4. Find out Existing Path and Query Number. 4,3,2,1. 3,2,4,1. 3,1,2,4. 4,3,1,2.

Name the 3 tables that get updated when the EXPLAIN Tool is used in conjunction with the SQL: PLAN_TABLE. DSN_STATEMNT_TABLE. DSN_FUNCTION_TABLE. SYSIBM.TABLES. SYSCOLUMNS.COLCARDF.

There are 5 SQL Statement, whats the effective way of executing those SQLs wrt to PERFORMANCE?. Include those SQLs in your online application and run it. Include those SQLs in batch stored procedures and run it.

Choose the correct order of the evaluation of Predicates: 1. Range Predicates and NOT NULL 2. All Other Predicates 3. Equal Predicates. 1,2,3. 3,2,1. 3,1,2.

Order the following predicates in the right Order: 1. State 1 Predicates 2. Stage 2 Predicates 3. Indexable Predicates. 3,1,2. 3,2,1. 1,2,3. 1,3,2.

Which of the following joins is better for below characteristics 1. Number of rows to be joined is less 2. OPTIMIZE for n ROWS is used 3. Inner Table has an Index. MERGE SCAN JOIN. NESTED LOOP JOIN. HYBRID JOIN. SORTS NEEDED.

Choose the correct Options regarding DB2 Joins. USE DB2 Joins. Reduce number of rows by specifying more predicates. Use Application Joins. The join Column should match data type and length.

Which of the following are false statements regarding Triggers 1. Before Triggers will be executed before any action on the table and activates another trigger 2. Before Triggers are used to validate data before performing action on a table. 1 ONLY. 1 AND 2. 2 ONLY.

Identify correct Buffer Pools available for DB2 Optimizer: BP0-BP49 4KB Pages. BP8K0-BP8K9 8KB Pages. BP16K0-BP16K9 16KB Pages. BP32K0-BP32K9 32KB Pages. BP0-BP49 8KB Pages. BP8K0-BP16K9 8KB Pages.

Rows need to be sorted for which kind of a Join?. Sort Merge join. Hybrid. Nested Loop. Sort Join.

Merge Scan Join is also known as Merge Join or Sort Merge Join. True. False.

To tune Storage performance, EDM storage is better controlled by which measures?. Use More Packages. Use RELEASE(COMMIT). Use DEGREE(ANY) only if required. Use DEGREE(ANY) Always. Use less Packages.

How to maximize 16MB Region?. Set Region = 0. Set Region = NULL. Set Region = 8. Set Region = 16.

Choose the incorrect statement as to how long does a modified page remain in buffer pool?. A sync point is taken. A checkpoint is taken. When VDWQT threshold is met. When DWQT threshold is met.

____ Join is chosen by DB2 when 1. When Outer table has duplicate qualifying rows 2. When Inner table column has smaller number of RIDs. Sort Merge join. Hybrid. Nested Loop. Sort Join.

The predicates of the table which do not participate in the join will sometimes be evaluated after the Join Processing. This degrades Performance to a greater extent. True. False.

Match the following. External Scalar Function. External Table Function. Sourced Function.

When is an updated page written to DASD, choose the wrong answer. When DB2 Takes Checkpoint. When VDWQT reaches its default preset value. When DWQT reaches 30%. When WITH reaches 90%.

What needs to be done to inform DB2 to use the Hint for Dynamic Queries?. Set Current Optimization hint="OLDPATH". Execute the SQL Again. Rerun the query. Refresh the table PLAN_TABLE.

What needs to be done to inform DB2 to use the Hint for Static Queries?. For embedded SQL rebind the package or plan. Use Option EXPLAIN(YES). Use Option OPTHINT('OLDPATH'). Use Option EXPLAIN(NO). Remove OPTHINT.

Order the Steps below in the right sequence when existing access path needs to be modified: 1. Set the Optimizer Hint when running the Query 2. Explain the Query and associate the query number 3. Update the Method to the desired strategy 4. Set the OPTHINT column based on Query Number. 2,4,3,1. 2,4,1,3. 1,2,3,4. 4,3,2,1.

Choose the incorrect option(s) for maximizing count of functions that run in an address space: Each TCB (WLM-established) address space uses approx 200KB below the 16MB line. Set Region size for address space to Zero. Link Edit with AMODE (31) and RMODE(any) attributes. Link Edit with AMODE (24) Attribute. Use RES and Data (24) for Cobol Programs. Use RES and Data (31) for Cobol Programs.

What are the maximum number of triggers that can be defined on a table. 45. 200. 300. 6.

Identify the incorrect options for performance improvement of Triggers: Try to avoid calling triggers recursively. Ordering of multiple triggers on same table. Try to avoid calling UDFs and stored procedures from Triggers. Try to avoid triggers with FOR EACH ROW clause. None of the above.

Resource Limit Facility prohibits SQL Statements from running if the users do not have the right authorization. It also governs how the resources are utilized. True. False.

Identify the incorrect statement(s). Using DRDA, you can call stored procedure which is in remote server and improves performance by reducing Network Traffic. Using three part name and aliases provides application with concurrency transparency. Using three part name and aliases provides application with location transparency. Programs that use 3 part naming convention can be executed by non Z/OS SQL. Improved connectivity can be ensured by binding the packages at remote server.

Match the following Program Preparation performance enhancers: Use CONNECT (2). Use SQL (ALL) or SQL(DB2). Use DEFER (PREPARE). Use SQLRULES (DB2).

Match the following Efficient Query techniques: Use FOR FETCH ONLY or FOR READ ONLY. OPTIMIZE FOR n ROWS. FETCH FIRST n ROWS ONLY. ISOLATION (RR).

Managing CPU and I/O to improve response time and throughput with the following techniques: 1. Controlling the number of I/O Operations 2. Reduce the time required for I/O Operations. True. False.

Identify the one that does not reduce the time required for I/O Operations. Put frequently used data sets on fast devices. Distributing the I/O. Allocating space in cylinders or in Large Primary and secondary quantities. Pre-formatting during LOAD or REORG. Avoid excessively small extents. Specifying primary quantity for non-partitioned indexes. Maximum number of extents. None of the above.

What is the default secondary storage allocation?. SECQTY=10% PRIORITY. SECQTY=20% PRIORITY. SECQTY=1% PRIORITY. SECQTY=2% PRIORITY. 3 Times.

Which of the following do not improve real/virtual storage performance?. Minimize storage needed for locks by specifying larger LOCK SIZE. Do not have unnecessarily huge buffer pools. Improve sorting performance by assigning any other buffer pool other than BP0 to DSNDB07. DISP = SHR.

Correct formula for RID Pool Size is: Num of Concurrent RID processing activities * Avg Num of RIDs per activity * bytes per RID. Num of Concurrent RID processing activities * Avg Num of RIDs per activity * 1 * 5 bytes per RID. Num of Concurrent RID processing activities * Avg Num of RIDs per activity * 5 bytes per RID. Num of Concurrent RID processing activities * Avg Num of RIDs per activity * 2 * 5 bytes per RID.

What is the formula for Sort Pool Size?. 32000 * (16 + Sort Key Length + Sort Data Length) bytes. 32000 * (16 + Sort Data Length) bytes. 32000 * (16 + Sort Key Length) bytes. 32000 * (Sort Key Length + Sort Data Length) bytes.

DB2 Optimizer is a ________ based OptimizerDB2 Optimizer is a ________ based Optimizer. Cost Based. Value Based. CPU Based. Performance Based.

During Plan or Package creation Optimizer builds an __________ using inputs from Optimization Checks. Access Path Tree. Build Tree. Link Tree. Parse Structures.

Optimizer processes the SQL Request during the execution step, by executing the request with the ________ generated in the Plan/Package Creation Step. Access Plan. Parse Tree. Link Tree. Parse Structures.

The column SYSINDEXES.CLUSTERRATIO is used in calculating the ______ cost. I/O. Usage. Performance.

Which column does DB2 use if SYSINDEXES.CLUSTERRATIOF = 0?. CLUSTERRATIO. CARDF. COLCARDF. COLVALUE.

When SYSINDEXES.CLUSTERRATIOF=1, then it means ____ row(s) is/are in clustering order. All. 1. 100. 0.

__________decides the I/O cost of the SQL. Filter Factor. I/O Count. Cost Based Optimization.

Which column is used by Optimizer to resolve =, IN when coded in predicates. SYSCOLUMNS.COLCARDF. SYSCOLUMNS.CUSTERATIONF. SYSCOLUMNS.CLUSTERING. SYSINDEXES.COLCARDF.

Which column(s) is used by Optimizer to resolve the range predicates coded with BETWEEN, <=, >=, LIKE, etc. SYSCOLUMNS.HIGH2KEY. SYSCOLUMNS.LOW2KEY. SYSINDEXES.FULLKEYCARDF. SYSINDEXES.COLCARDF.

Method 2 in PLAN_TABLE means ________ type of join. Merge Scan Join. Nested Loop Join. Hybrid Join. Table Join.

Which Join is needed for each row of inner table to be scanned for each row of outer table (with no index on column)?. Nested Loop Join. Sorts Needed. Hybrid Join. Merge Scan Join.

Which Join expects DB2 to do a SORT. Nested Loop Join. Sorts Needed. Hybrid Join. Merge Scan Join.

______ command details the access path defined by DB2 Optimizer for a Query. EXPLAIN. PLAN. SORT. TREE.

Selecting all rows of inner table for each row in outer table and there is no Index on the inner table indicates which type of Join?. Nested Loop Join. Sorts Needed. Hybrid Join. Merge Scan Join.

Which Option helps in reducing contention for Partition Index?. PIECESIZE. BUFFER POOL SIZE. B0. Large Space allocated in Cylinders.

What should be the cluster ratio if all the rows in all the pages are to be searched?. 100. 1. 0. 25.

Which table.column confirms the hint that was used?. PLAN_TABLE.HINT_USED. PLAN_TABLE.PLAN_TYPE. PLAN_TABLE.HINTS. PLAN_TABLE.METHOD.

If multiple triggers are created on a single table are to be fired, they will be fired based on _________________. CREATION TIME STAMP. Indexes. Priority of the Trigger indicated in the table. All triggers will be fired at the same time.

DRDA stands for. Distributed Relational Database Architecture. Distributed Rational Database Architecture. Distributed Relational Data Architecture. Direct Relational Data Architecture.

If Statistics have not been gathered then SYSTABLESPACE.NACTIVE has a value: 0. 1. 100. -2.

SYSTABLESPACE.NACTIVE is note an updatable column. True. False.

In the Installation Panel, ____specifying 'YES' for 'Optimization hints' field. If No is mentioned, the hints will be ignored: DSNTIP4. DSNTIPS. DSNHINT. DSNOPT.

Order the following in the right sequence for Retaining the old access path even after rebind: 1) Modify the query to include the QUERYNO clause2) Inform DB2 to use this hint. 3) Find the existing access path for the query and find out its Query NO, in the PLAN_TABLE 4) Update the OPT_HINT column of PLAN_TABLE with Hint Name. 3,1,4,2. 1,2,3,4. 1,3,2,4. 1,2,4,3. .

For Static Queries: After the query is executed, what shows that DB2 has used the hint provided. HINT_USED Column is updated with the hint name of the table PLAN_TABLE. Method has the method of Join used. PLAN_METHOD has the HINT_USED column.

Using QUERYNO even for Statis SQL, STMTNO changes but QUERYNO does not. True. False.

It is a good pratice to save the EXPLAIN results especially for critical queries or packages. It also helps you in evaluating or comparing the access paths. True. False.

This type of join is chosen only if the 2 columns in the join operation has same column length and data type: Nested Loop Join. Merge Scan Join. Hybrid Join. Sorts Needed.

The join where row from outer table is selected and compared with the inner table to get all the matches: Nested Loop Join. Merge Scan Join. Hybrid Join. Sorts Needed.

The algorithm requires sorted rows: Nested Loop Join. Merge Scan Join. Hybrid Join. Sorts Needed.

The second row from outer table is selected and compared with the inner table from last position. This process continues until all the matches are found: Nested Loop Join. Merge Scan Join. Hybrid Join. Sorts Needed.

____ Join is favoured when ORDER bBY clause is used on the column to be joined and the column contains high cluster ratio: Nested Loop Join. Merge Scan Join. Hybrid Join. Sorts Needed.

Merge Scan Join is chosen by DB2 when: High Cluster Ratio on inner table or outer table. Low Cluster Ratio on Inner table. Less number of columns are selected adn the rows to be sorted is shorter. No index on the tables to be joined. Small buffer pool. Large number of rows is resulted from the join. Cost of the algorithm is high compared to others.

Identify the type of Join: 1) The algorithm does not require sorted rows 2) The first row will be selected from the outer table and compared with all the rows of the inner table to get teh matching columns. 3) The second row will be selected from the outer table adn compared with all the rows of the inner table 4) This process continues until all matching rows are found. Nested Loop Join. Hybrid Join. Merge Scan Join. Sorted Tables.

Advantage of Nested Loop Join is: No Pre-processing is required. I/O Cost Reduces. CPU Time Reduces. There is no INDEX on the inner table.

When does DB2 us Nested Loop Join?. Number of rows to be joined is less. OPTIMIZE FOR n ROWS is used and n value is very small. Inner table column has index. When Outer table has duplicate qualifying rows. When iner table column has small number of RID's.

When does DB2 uses Hybrid Join?. Number of rows to be joined is less. OPTIMIZE FOR n ROWS is used and n value is very small. Inner table column has index. When Outer table has duplicate qualifying rows. When iner table column has small number of RID's.

Hybrid Join is a mixture of: Nested Loop Join. Merge Join. Sorts Needed. First table accessed.

Identify the Join that is being talked about here: 1) Applies to an inner join 2) Inner table must have index on the column 3) Method requies obtaining RID's in the order needed to use list pre-fetch. Nested Loop Join. Hybrid Join. Merge Scan Join. First Table Accessed.

The predicates of the table which do not participate in the join will sometimes be evaluated after the join processing in Nested Loop Join. True. False.

The predicates of the table which do not participate in the join will sometimes be evaluated after the join processing is a advantage of a Hybrid Join. True. False.

Identify the characteristics of Hybrid Join: Predicates of the table which do not participate in the join will sometimes be evaluated after teh join processing. Evaluating predicates after teh join processing degrades performance. OPtimizer checsk for the catalog table to avoid join in these cases. This problem of Optimizer checking catalog tables to sometimes avoid hybrid join can be flipped by programmer.

Identify the correct tips on joins: Reduce the number of rows used in teh join by specifying more predicates. The join columns which are indexed should match in data type adn length to avoid conversions. Use Db2 Joins as Opposed to application Joins. Use Application Joins as opposed to DB2 Joins.

Performance of the Join is inversely proportional to the number of rows used in the algorithm. True. False.

The impact of number of rows is less on the performance compared to the number of tables used in the join. False. True.

Most of the Time Join is performed on the columns which are indexed. The index usage on these columns will improve the performence. But if the index becomes not usable the the performance will be degraded. False. True.

When two columns which are indexed are used in a Join, if the indexed columns do not have the same data type and the length of these columns do not match, the index does not become usable. False. True.

It is always better to allow the optimizer to choose the join for you rather than you influencing it. False. True.

REturns Keyword is not mandatory for external scalar functions. False. True.

External Table functions return more than one row and column: True. False.

Identify the correct optinos about UDF: UDF's are sourced functions. It can be DB2 build-in function or again user defined function. SOURCE CLAUSE indicates that a function is being created by using existing funcion. SOURCE CLAUSE can be specified for scalar or column functions or table functions. UDF's are not sourced functions.

Choose the correct options to maximize count of functions running in an address space or improve performance of UDF's in a WLM address space: Set Region Size for address space to 1MB. Set Region Size for address space to Zero. Link Edit with AMODE(31) AND RMODE(ANY) attributes. USE RES and DATA (31) for COBOL programs. Use DATA (24) and RMODE (ANY) link edit options.

Each TCB(WLM-established) address space uses approx 250KB below the 16MB line. True. False.

To improve then performance of UDF we need to : UPdate ASUTIME column in SYSIBM.SYSROUTINES. Try to increase the number of functions run parallel in WLM. Study your work load for UDF carefully. Set region size to 6000MB. Make ASUTIME column in sysibm.sysfunctions to 0.

ASUTIME sets the max limit on CPU units. True. False.

If a function is runnning in a loop and it has utilized all mentioned CPU Units, what should have been done have done to terminate it?. Set the ASUTIME in the create statement of the User defined function. Set ASUTIME of the function after creating it. There is no need for ASUTIME as it is taken care by the CPU.

Triggers can have SQL statements inside the body, but having UDF's and stored procedures, may leave to integration and perforamnce issues. True. False.

Triggers that get fired for each and every row change for a table, then. Mass deletion from a table is beind being done. FOR EACH ROW clause is not specified when creating the Trigger. These triggers cause performance issues. Cause Integration problems.

Distributed Data Access, choose the right options about it: INvolves connecting to several database over network. Architecture may be two-tier or multitier. Connection to database does not depend on DRDA. Provides many ways to request data from remote server.

Select true statements about performance issues with Distributed Data Access. Distributed environment authorizations is necessary so a user for users to eb able to query on remote environments. Resource Limit Facility is at the server is used to govern the distributed dynamic SQL statements. User DRDA, calling stored procedures in remote servers, improves performance by reducing the network traffic. Using three part name and does not provide application with Location transparency. Some SQL that cannot be supported by local server cannot be executed at remote server as well in spite of using a program which uses DRDA access. SQL not supported by local environment should not use three part naming convention as they might not be executed by non z/OS sql.

Choose options that do not improve performance for remote access: REduce number of columns adn rows requested. Use FOR FETCH ONLY or FOR READ ONLY. Bind the plans and packages with ISOLOATION(RR). Use Parameter markers. Specify OPTIMIZE FOR n ROWS. Specify FETCH FIRST n ROWS ONLY.

ISOLATION(RR) keeps the lock after reading teh data. True. False.

Parameter markers might limit DB2 from strealining the processing of dynamic queries. True. False.

OPTIMIZE FOR n ROWS in SELECT statements, limits the locate data server to send minimum number of rows. False. True.

Identify the factors that affect performance on remote access. Network head. Binding required at remote server. Message Length. Message Response. Queue Length.

Configuration storage for better performance, below should be done: Reduce Disk Storage allocated. Improve use of real. Balancing teh storage controller cache and buffer resources. Reduce Virtual Storage.

Identify the options to control the number of I/O Operations. Keeping access path statistics updated. Making buffer pools large enough for the workload. Distributing datasets efficiently. Formatting early and speed-up formatting. Avoiding excessively small extents.

Identify the options to reduce the time required for I/O Operations. Keeping access path statistics updated. Making buffer pools large enough for the workload. Distributing datasets efficiently. Formatting early and speed-up formatting. Avoiding excessively small extents.

Identify the false statements that do not keep the access path statistics updated. Run RUNSTATS at least once against the table only. If data in table varies considerablly in a short span , then RUNSTATS need to be run regularly. RUNSTATS is most beneficial in sicuations when table spaces contain frequentlyl accessed tables, involved in a sort, have too many rows, etc...

Identify the false statements that do not keep Make buffer pools large enough for workload: A value of 300KB or more for improved performance is recommended. Buffer pool At least 10KB for each concurrent user. Do not allocate more virtual storage than twice the real storage available. Allocate more storage for buffer pools than available real storage for buffer pools.

Identify the correct options for formatting early and speed-up formatting: 1) Allocating space in traks in small primary and secondary quantities 2) Pre-Formatting during LOAD or REORG. 1 & 2. 2 Only. 1 Only. NONE.

To avoid Excessively small extents: 1) Maximum number of extents to be used 2) Specifying primary quantity for non-partitioned indexes. 1. 2. Both. None.

To Distribute data sets efficiently: 1) Putting frequently used datasets on fast devices 2) Distribute I/O operations. 1. 2. Both. None.

Partitioning is a good way of distrbuting I/O. True. False.

DPSI (Data Partitionned secondary index) is possible for V9 or DB2 onwards. True. False.

DPSI is to logically partition the data according to the secondary index adn itis very similar to the concept of alternate index in VSAM KSDS. True. False.

Space allocation in cylinders can reduce the time required to do SQL mas inserts adn to perform LOGONLY recovery. True. False.

Default SECQTY is 10% of the PRIQTY, or 3 times the page size, whichever is larger. True. False.

SECQTY value that is too small in relation to the PRIQTY value results in CYL ALLOCATION. True. False.

To reduce time required for I/O Operations - Using the PREFORMAT option available in LOAD and REORG utility be very useful for those tables which tends to read more than insert. True. FAlse.

Sequential scan must move to a new extent ten times per second. Since it is a small amount it is okay to give small extents for small table reads. True. FAlse.

An SMS-Managed LDS is 123 extents on a volume and 7257 on all volumes. True. FAlse.

An non SMS-Managed LDS is 123 extents on a volume and 253 on all volumes. True. FAlse.