How do you improve db2 SQL performance?
a
To improve the performance following are some of the tips :
1. Always use FOR UPDATE OF when updating with a cursor.
2. Use WHERE CURRENT OF to delete a single row.
3. Check the SQLCODE after each SQL statemnt.
4. Avoid using WHENEVER.
A control structure used by db2 to communicate with the application program isa.Address spaceb.Authidc.Threadd.Viewe.String thread
SQLCA or SQL Communication area (136 bytes) is used by DB2 to communicate with the Program
c. Thread
Difference between embedded SQL and dynamic SQL?
Embedded SQL -> Access Path is generated during BIND time. also table authorizations are checked during BIND (if VALIDATE(BIND) is used).
Dynamic SQL => Access path is generated during Run-time. Table authorizations can only be checked during run-time.
STATIC sql(embedded) : It is hardcoded in the program itself and the changeable &n...
The cursor can be declared in working storage sectionlinkage sectioninput-output section. working storage section
Cursor Declaration can be done either in WORKING STORAGE SECTION in DATA DIVISION or in PROCEDURE DIVISION, the only rule is that it has to be coded before the OPEN CURSOR statement in the Procedure Division.
Correct ans is WORKING STORAGE section
If SQL return -805 , what will user want to do
-805 will never occur if a DBRM is directly bound to a PLAN. -805 occurs when 1) Source Program is changed and per-compiled and new LOAD Module created as a result of change is used at Run time, but ...
Rebind the plan.
An access path is the method used to access data specified in db2 SQL statements.
DB2 Access path is (in most cases) the path which results in the most optimized execution of an SQL query. Whenever any Static SQL (Embedded SQL) is bound to a package/plan, the DB2 optimizer creates...
The access path is determined by DB2 during the bind process. This determines what indexes will be used.
What is check pending and copy pending status in db2, how do you resolve it?
Check Pending is when you have data added to the table using either insert or LOAD utility which violate table check constraints. The IBM DB2 LOAD UTILITY has ENFORCE CONSTRAINTS and similar options w...
SET INTEGRITY Option....please read IBM libraries before using this.
What are the important bind parameters to be used in bind card? What is the significance of each parameter?
I will say the Answer Depends barring a few parameters which are mostly used. 1) ISOLATION -> Isolation Level to decide level of locking used along with LOCK parameters on the DB2 objects. 2) ACQUI...
The bind parameters are generally generic the most important is isolation level which is always CS and sometimes UR if you want to rock right thru.
To maintain the integrity of db2 objects the dbd permits access to only on object at a time. Lock contention happens if several objects are required by contending application processes simultaneously.
Lock contention are used for maintaining concurrency in the DB2® environment.There are several types of contention situations that degrade DB2 performance, including suspension, timeout, and deadlock.
Begin program-specific programming interface information.
What is the self-referencing constraint?
A31. The self-referencing constraint limits in a single table the changes to a primary key that the related foreign key defines. The foreign key in a self referencing table must specify the delete cascade rule.
Self-referencing constraint makes the table both the parent and dependent table in the same referential constraint.
Here the table is created first and then the foreign key is defined to that table.
How does db2 use multiple table indexes?
Db2 use the multiple indexes to satisfy multiple predicates in a select statement that are joined by an and or or.
By using the concept of list prefetch DB2 uses multiple index processing.I is used to satisfy multiple predicates in a select statement that are joined by an AND or OR.
The opposite of a leaf page; it is the highest level index page. An index can contain only the one root page; all other index pages are associated to the root.
The indexes of DB2 are represented using b-tree,the b-trees top page is called as the root page.The root page entries represent the upper range limits of the index and are referenced first in a search.
What is an asynchronous write?
. It is a write to disk that may occur before or long after a commit. The write is controlled by the buffer manager.
Asynchronous write is when the pages from bufferpool is written to disk by the page cleaners.
Asynchronous write is a function where the the function call immediately returns after the operation was enqueued or if before this happens an error was encountered.This function is present in "aio.h"...
How many rows will the query return ?
Table1 has 6 rows and table2 has 6 select * from table1, table2; a.6b.12c.36d.720e.Cannot sayrows.36
This will yield to a correlated subquery return in SQL which will be each combination of record from the first table to second table. Which will be 6*6 = 36.
Ans: a. 6
How can you split db2 table in to exactly half?
Can we divide it row wise or col wise.?Is there any query for this.?
You can table partitioning feature
How do you display last 5 records in db2?
There is nothing called "fetch last n records only" in db2...
You can use select col1 from table1 order by col1 desc fetch first 5 records only
First sort the records by using order by clause and then issue the following query
Then it will give the last five records of the tableCode
SELECT * FROM tablename fetch first 5 rows only;
The hex form 102d... What is the actual value for this?
4141... in decimal
Can we use sum function on alphanumeric column ?
yes
I ) unique index can be created only on primary key. Ii) query will execute faster if predicates are applied on indexed column. 1 both are true2 i) is only true3 ii) is only true4 neither is true
Answer is 1 Both are True.
ii) is Only True
Unique indexes can be created on any column.
the difference is that a Package is at a more granular level. If a module is bound to a Package, any change in module requires only the Package to be re-bound, without the necessity to re-bind the ent...
Package can be bound for single DBRM only. Plan can contain both DBRM and Packages. Packaqes can't be executed until they are bound into a plan.