Questions
ayuda
option
My Daypo

ERASED TEST, YOU MAY BE INTERESTED ONMF302 - Performance Engineering

COMMENTS STATISTICS RECORDS
TAKE THE TEST
Title of test:
MF302 - Performance Engineering

Description:
MF 302 Test

Author:
A
(Other tests from this author)

Creation Date:
23/09/2010

Category:
Computers

Number of questions: 198
Share the Test:
Facebook
Twitter
Whatsapp
Share the Test:
Facebook
Twitter
Whatsapp
Last comments
No comments about this test.
Content:
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.
Report abuse Consent Terms of use