Top 100+ Db2 Using Sql Interview Questions And Answers
Question 1. What Is Db2 (ibm Database 2)?
Answer :
DB2 is a subsystem of the MVS running gadget. It is a Database Management System (DBMS) for that operating system.
Question 2. How Would You Find Out The Total Number Of Rows In A Db2 Table?
Answer :
Use SELECT COUNT(*) ... In db2 question
PL/SQL Interview Questions
Question three. How Do You Eliminate Duplicate Values In Db2 Select ?
Answer :
Use SELECT DISTINCT ... In db2 question
Question 4. How Do You Select A Row Using Indexes In Db2?
Answer :
Specify the indexed columns in the WHERE clause of db2 question.
IBM DB2 Tutorial
Question 5. How Do You Find The Maximum Value In A Column In Db2?
Answer :
Use SELECT MAX(...) .. In db2 question
IBM DB2 Interview Questions
Question 6. How Do You Retrieve The First five Characters Of Firstname Column Of Db2 Table Emp ?
Answer :
SQL Query : SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;
Question 7. What Are Aggregate Functions?
Answer :
Built-in mathematical features for use in SELECT clause.
COBOL Tutorial Oracle Interview Questions
Question eight. Can You Use Max On A Char Column?
Answer :
YES.
Question nine. My Sql Statement Select Avg(income) From Emp Yields Inaccurate Results. Why?
Answer :
Because SALARY is not declared to have NULLs and the personnel for whom the revenue is not regarded also are LASTNAME FROM EMP;
DB2 Using SQL Tutorial
Question 11. What Is The Use Of Value Function?
Answer :
1. Avoid -ve SQLCODEs by means of handling nulls and zeroes in computations
2. Substitute a numeric value for any nulls utilized in computation
IBM Mainframe Interview Questions
Question 12. What Is Union,union All?
Answer :
UNION : gets rid of duplicates
UNION ALL: keeps duplicates
Both these are used to combine the effects of various SELECT statements.
PL/SQL Interview Questions
Question thirteen. What Is The Restriction On Using Union In Embedded Sql?
Answer :
It needs to be in a CURSOR.
IBM Mainframe Tutorial
Question 14. In The Where Clause What Is Between And In?
Answer :
BETWEEN materials a number values at the same time as IN elements a list of values.
Question 15. Is Between Inclusive Of The Range Values Specified?
Answer :
Yes.
MYSQL DBA Interview Questions
Question 16. What Is 'like' Used For In Where Clause? What Are The Wildcard Characters?
Answer :
LIKE is used for partial string fits. ‘%’ ( for a string of any person ) and ‘_’ (for any single man or woman ) are the two wild card characters.
IMS/DB Tutorial
Question 17. When Do You Use A Like Statement?
Answer :
To do partial seek e.G. To search worker by way of name, you need not specify the entire call; the usage of LIKE, you could look for partial string matches.
DB2 SQL Programming Interview Questions
Question 18. What Is The Meaning Of Underscore ( '-' ) In The Like Statement?
Answer :
Match for any unmarried man or woman.
IBM DB2 Interview Questions
Question 19. What Do You Accomplish By Group By ... Having Clause?
Answer :
GROUP BY partitions the selected rows at the wonderful values of the column on that you organization through.
HAVING selects GROUPs which match the standards distinct
Question 20. Consider The Employee Table With Column Project Null Able. How Can You Get A List Of Employees Who Are Not Assigned To Any Project?
Answer :
SELECT EMPNO
FROM EMP
WHERE PROJECT IS NULL;
IMS/DB Interview Questions
Question 21. What Is The Result Of This Query If No Rows Are Selected:
Answer :
SELECT SUM(SALARY)
FROM EMP
WHERE QUAL=‘MSC’;
NULL
Question 22. Why Select * Is Not Preferred In Embedded Sql Programs?
Answer :
For 3 motives:
If the table structure is modified ( a discipline is brought ), this system will need to be modified.
Program may retrieve the columns which it might not use, main on I/O over head.
The risk of an index handiest test is misplaced.
Question 23. What Are Correlated Subqueries?
Answer :
A subquery in which the internal ( nested ) question refers to the desk within the outer query. Correlated subqueries must be evaluated for every qualified row of the outer query this is cited.
Mainframe DB2 Interview Questions
Question 24. What Is A Cursor? Why Should It Be Used?
Answer :
Cursor is a programming tool that allows the SELECT to discover a set of rows however go back them separately.
Cursor need to be used because the host language can cope with most effective one row at a time.
Oracle Interview Questions
Question 25. How Would You Retrieve Rows From A Db2 Table In Embedded Sql?
Answer :
Either via the usage of the single row SELECT statements, or by using the CURSOR.
Question 26. How Do You Specify And Use A Cursor In A Cobol Program?
Answer :
Use DECLARE CURSOR statement both in running storage or in process department(before open cursor), to specify the SELECT statement. Then use OPEN, FETCH rows in a loop and finally CLOSE.
Question 27. What Happens When You Say Open Cursor?
Answer :
If there may be an ORDER BY clause, rows are fetched, taken care of and made to be had for the FETCH statement. Other smart really the cursor is placed on the first row.
COBOL Interview Questions
Question 28. Is Declare Cursor Executable?
Answer :
No.
Question 29. Can You Have More Than One Cursor Open At Any One Time In A Program?
Answer :
Yes.
Question 30. When You Commit, Is The Cursor Closed?
Answer :
Yes.
Question 31. How Do You Leave The Cursor Open After Issuing A Commit? ( For Db2 2.3 Or Above Only )
Answer :
Use WITH HOLD option in DECLARE CURSOR declaration. But, it has not impact in psuedo-conversational CICS programs.
Question 32. What Is The Physical Storage Length Of Each Of The Following Db2 Data Types: Date, Time, Timestamp?
Answer :
DATE: 4bytes
TIME: 3bytes
TIMESTAMP: 10bytes
Question 33. What Is The Cobol Picture Clause Of The Following Db2 Data Types: Date, Time, Timestamp?
Answer :
DATE: PIC X(10)
TIME : PIC X(08)
TIMESTAMP: PIC X(26)
IBM Mainframe Interview Questions
Question 34. What Is The Cobol Picture Clause For A Db2 Column Defined As Decimal(11,2)?
Answer :
PIC S9(9)V99 COMP-three.
Note: In DECIMAL(11,2), eleven indicates the size of the facts kind and a couple of suggests the precision.
Question 35. What Is Dclgen
Answer :
Declarations GENerator: used to create the host language copy books for the desk definitions. Also creates the DECLARE desk.
Question 36. What Are The Contents Of A Dclgen?
Answer :
EXEC SQL DECLARE TABLE announcement which gives the format of the desk/view in phrases of DB2 datatypes.
A host language copy e-book that gives the host variable definitions for the column names.
MYSQL DBA Interview Questions
Question 37. Is It Mandatory To Use Dclgen? If Not, Why Would You Use It At All?
Answer :
It is not obligatory to use DCLGEN.
Using DCLGEN, enables come across wrongly spelt column names etc. At some point of the pre-assemble degree itself ( because of the DECLARE TABLE ). DCLGEN being a device, could generate accurate host variable definitions for the table lowering chances of errors.
Question 38. Is Declare Table In Dclgen Necessary? Why It Used?
Answer :
It not essential to have DECLARE TABLE announcement in DCLGEN. This is utilized by the pre-compiler to validate the desk-name, view-call, column call and many others., for the duration of pre-compile.
Question 39. Will Precompile Of An Db2-cobol Program Bomb, If Db2 Is Down?
Answer :
No. Because the precompiler does now not check with the DB2 catalogue tables.
Question 40. How Is A Typical Db2 Batch Program Executed ?
Answer :
1. Use DSN utility to run a DB2 batch software from local TSO.
An instance is proven:
DSN SYSTEM(DSP3)
RUN PROGRAM(EDD470BD) PLAN(EDD470BD) LIB('ED 01T.OBJ.LOADLIB')
END
2. Use IKJEFT01 application software to run the above DSN command in a JCL.
DB2 SQL Programming Interview Questions
Question 41. Name Some Fields From Sqlca.
Answer :
SQLCODE, SQLERRM, SQLERRD
Question 42. How Can You Quickly Find Out The # Of Rows Updated After An Update Statement?
Answer :
Check the value stored in SQLERRD(3).
IMS/DB Interview Questions
Question forty three. What Is Explain?
Answer :
EXPLAIN is used to display the get entry to route as decided by the optimizer for a SQL declaration. It can be utilized in SPUFI (for unmarried SQL statement) or in BIND step (for embedded SQL ).
Question forty four. What Do You Need To Do Before You Do Explain?
Answer :
Make sure that the PLAN_TABLE is created beneath the AUTHID.
Question 45. Where Is The Output Of Explain Stored?
Answer :
In userid.PLAN_TABLE
Question forty six. Explain Has Output With Matchcols = 0. What Does It Mean?
Answer :
A non matching index test if ACCESSTYPE = I.
Question forty seven. How Do You Do The Explain Of A Dynamic Sql Statement?
Answer :
1. Use SPUFI or QMF to EXPLAIN the dynamic SQL statement
2. Include EXPLAIN command in the embedded dynamic SQL statements
Question 48. What Are The Isolation Levels Possible ?
Answer :
CS: Cursor Stability
RR: Repeatable Read
Question 49. What Is The Difference Between Cs And Rr Isolation Levels?
Answer :
CS: Releases the lock on a web page after use
RR: Retains all locks received till cease of transaction
Question 50. Where Do You Specify Them ?
Answer :
ISOLATION LEVEL is a parameter for the bind procedure.
Question 51. What Are The Various Locking Levels Available?
Answer :
PAGE, TABLE, TABLESPACE
Question 52. How Does Db2 Determine What Lock-length To Use?
Answer :
1. Based at the lock-length given whilst creating the tablespace
2. Programmer can direct the DB2 what lock-length to use
3. If lock-size ANY is detailed, DB2 typically chooses a lock-size of PAGE
Question 53. What Is Alter ?
Answer :
SQL command used to change the definition of DB2 gadgets.
Question fifty four. What Is A Dbrm, Plan ?
Answer :
DBRM: Data Base Request Module, has the SQL statements extracted from the host language application via the pre-compiler.
PLAN: A end result of the BIND method. It has the executable code for the SQL statements in the DBRM.
Question 55. What Is Acquire/release In Bind?
Answer :
Determine the point at which DB2 acquires or releases locks towards table and tablespaces, inclusive of reason locks.
Question 56. What Else Is There In The Plan Apart From The Access Path?
Answer :
PLAN has the executable code for the SQL statements in the host program
Question 57. What Happens To The Plan If Index Used By It Is Dropped?
Answer :
Plan is marked as invalid. The subsequent time the plan is accessed, it's far rebound.
Question fifty eight. What Are Packages ?
Answer :
They incorporate executable code for SQL statements for one DBRM.
Question fifty nine. What Are The Advantages Of Using A Package?
Answer :
1. Avoid having to bind a large wide variety of DBRM contributors right into a plan
2. Avoid value of a huge bind
three. Avoid the whole transaction being unavailable at some stage in bind and automatic rebind of a plan
4. Minimize fallback complexities if modifications result in an blunders.
Question 60. What Is A Collection?
Answer :
A person described call this is the anchor for applications. It has no longer bodily existence. Main usage is to institution packages.
In SPUFI think you need to choose max. Of one thousand rows , however the select returns only 200 rows.
Question 61. What Are The 2 Sqlcodes That Are Returned?
Answer :
a hundred ( for a success final touch of the query ), 0 (for successful COMMIT if AUTOCOMMIT is ready to Yes).
Question 62. How Would You Print The Output Of An Sql Statement From Spufi?
Answer :
Print the output dataset.
Question sixty three. Lot Of Updates Have Been Done On A Table Due To Which Indexes Have Gone Haywire. What Do You Do?
Answer :
Looks like index page split has took place. DO a REORG of the indexes.
Question 64. What Is Dynamic Sql?
Answer :
Dynamic SQL is a SQL assertion created at program execution time.
Question 65. When Is The Access Path Determined For Dynamic Sql?
Answer :
At run time, when the PREPARE assertion is issued.
Question sixty six. How Does Db2 Store Null Physically?
Answer :
As an additional-byte prefix to the column price Physically, the null prefix is Hex ’00’ if the cost is gift and Hex ‘FF’ if it isn't.
Question sixty seven. How Do You Retrieve The Data From A Nullable Column?
Answer :
Use null indicators. Syntax ... INTO :HOSTVAR:NULLIND
Question 68. What Is The Picture Clause Of The Null Indicator Variable?
Answer :
S9(4) COMP.
Question 69. What Does It Mean If The Null Indicator Has -1, zero, -2?
Answer :
-1 : the sphere is null
zero : the sector isn't always null
-2 : the sector cost is truncated
Question 70. How Do You Insert A Record With A Nullable Column?
Answer :
To insert a NULL, pass -1 to the null indicator
To insert a legitimate value, flow zero to the null indicator
Question seventy one. What Is Runstats?
Answer :
A DB2 utility used to accumulate records about the statistics values in tables which can be used by the optimizer to determine the get entry to route. It also collects statistics used for space management. These data are stored in DB2 catalog tables.
Question 72. When Will You Chose To Run Runstats?
Answer :
After a load, or after mass updates, inserts, deletes, or after REORG.
Question seventy three. Give Some Example Of Statistics Collected During Runstats?
Answer :
# of rows inside the desk
Percent of rows in clustering sequence
# of wonderful values of listed column
# of rows moved to a nearby/a ways way web page due to row duration boom
Question 74. What Is Reorg? When Is It Used?
Answer :
REORG reorganizes statistics on bodily storage to reclutser rows, positioning overflowed rows in their proper series, to reclaim space, to repair free area. It is used after heavy updates, inserts and deletes hobby and after segments of a segmented tablespace have come to be fragmented.
Question seventy five. What Is Copy Pending Status?
Answer :
A state in which, an photo reproduction on a table needs to be taken, In this fame, the table is to be had best for queries. You can't update this desk. To cast off the COPY PENDING status, you are taking an image copy or use REPAIR utility.
Question seventy six. What Is Check Pending ?
Answer :
When a desk is Loaded with ENFORCE NO option, then the table is left in CHECK PENDING fame. It way that the LOAD utility did now not carry out constraint checking.
Question seventy seven. What Is Quiesce?
Answer :
A QUIESCE flushes all DB2 buffers on to the disk. This offers a correct image of the database and need to be used before and after any IMAGECOPY to keep consistency.
Question 78. What Is A Clustering Index ?
Answer :
Causes the statistics rows to be stored within the order designated within the index. A obligatory index defined on a partitioned table space.
Question 79. How Many Clustering Indexes Can Be Defined For A Table?
Answer :
Only one.
Question 80. What Is The Difference Between Primary Key & Unique Index ?
Answer :
Primary : a relational database constraint. Primary key includes one or greater columns that uniquely become aware of a row in the desk. For a normalized relation, there may be one precise primary key.
Unique index: a physical object that stores handiest precise values. There can be one or extra specific indexes on a table.
Question 81. What Is Sqlcode -922 ?
Answer :
Authorization failure
Question 82. What Is Sqlcode -811?
Answer :
SELECT assertion has resulted in retrieval of more than one row.
Question eighty three. What Does The Sqlcode Of -818 Pertain To?
Answer :
This is generated when the consistency tokens in the DBRM and the load module are distinct.
Question eighty four. Are Views Updateable?
Answer :
Not they all. Some perspectives are updateable e.G. Unmarried table view with all of the fields or obligatory fields. Examples of non-updateable perspectives are perspectives which are joins, views that incorporate mixture capabilities(inclusive of MIN), and views which have GROUP BY clause.
Question eighty five. If I Have A View Which Is A Join Of Two Or More Tables, Can This View Be Updateable?
Answer :
No.
Question 86. What Are The 4 Environments Which Can Access Db2 ?
Answer :
TSO, CICS, IMS and BATCH
Question 87. What Is An Inner Join, And An Outer Join ?
Answer :
Inner Join: combine data from or greater tables by using comparing all values that meet the hunt criteria within the designated column or columns of on e desk with all the clause in corresponding columns of the other table or tables. These sorts of be a part of which contain a healthy in both columns are called inner joins.
Outer be part of is one in which you want both matching and non matching rows to be lower back. DB2 has no unique operator for outer joins, it could be simulated with the aid of combining a be a part of and a correlated sub query with a UNION.
Question 88. What Is Freepage And Pctfree In Tablespace Creation?
Answer :
PCTFREE: percentage of every web page to be left loose
FREEPAGE: Number of pages to be loaded with information among each free page
Question 89. What Are Simple, Segmented And Partitioned Table Spaces ?
Answer :
Simple Tablespace:
Can include one or more tables
Rows from multiple tables can be interleaved on a page beneath the DBAs control and protection
Segmented Tablespace:
Can incorporate one or greater tables
Tablespace is divided into segments of four to 64 pages in increments of 4 pages. Each phase is devoted to single desk. A desk can occupy multiple segments.
Partitioned Tablespace:
Can contain one table
Tablespace is split into elements and each component is put in a separate VSAM dataset.
Question ninety. What Is Index Cardinality?
Answer :
The number of wonderful values a column or columns comprise.
Question 91. What Is A Synonym ?
Answer :
Synonym is an alternate call for a desk or view used specifically to cover the leading qualifier of a table or view. A synonym is accessible simplest via the author.
Question 92. What Is The Difference Between Synonym And Alias?
Answer :
SYNONYM: is dropped while the table or tablespace is dropped. Synonym is available handiest to the author.
ALIAS: is retained despite the fact that table or tablespace is dropped. ALIAS may be created even though the table does not exist. It is used specially in dispensed surroundings to hide the region info from applications. Alias is a worldwide item & is available to all.
Question ninety three. What Do You Mean By Not Null With Default? When Will You Use It?
Answer :
This column cannot have nulls and at the same time as insertion, if no cost is supplied then it will have zeroes, spaces or date/time depending on whether it's miles numeric, man or woman or date/time.
Use it whilst you do now not want to have nulls however at the same time can not give values all the time you insert this row.
Question 94. What Do You Mean By Not Null? When Will You Use It?
Answer :
The column cannot have nulls. Use it for key fields.
Question ninety five. When Would You Prefer To Use Varchar?
Answer :
When a column which includes long text, e.G. Remarks, notes, may have in maximum cases much less than 50% of the maximum period.
Question 96. How Do I Create A Table Manager ( Emp#, Manager) Where Manager Is A Foreign Key Which References To Emp# In The Same Table? Give The Exact Ddl.
Answer :
First CREATE MANAGER table with EMP# as the primary key. Then ALTER it to define the overseas key.
Question 97. When Is The Authorization Check On Db2 Objects Done - At Bind Time Or Run Time?
Answer :
At run time.
Question 98. What Is Auditing?
Answer :
Recording SQL statements that get admission to a desk . Particular at table advent time or thru alter.
Question ninety nine. What Is An Access Path?
Answer :
The direction that is used to get to information specified in SQL statements.
Question a hundred. What Is An Alias?
Answer :
It is an alternate name that can be used in SQL statements to refer to a desk or view in the equal or remote DB2 subsystem.
Question a hundred and one. Explain What A Plan Is?
Answer :
Plan is a DB2 object (produced for the duration of the bind system) that pals one or extra database request modules with a plan call.
Question 102. What Is A Db2 Bind?
Answer :
Bind is a method that builds “access paths” to DB2 tables. A bind makes use of the Database Request Modules(s) (DBRM(s)) from the DB2 pre-assemble step as enter and produces an utility plan. It also tests the consumer’s authority and validates the SQL statements in the DBRM(s).
Question 103. What Information Is Used As Input To The Bind Process?
Answer :
The database request module produced all through the pre-collect. The SYSIBM.SYSSTMT desk of the DB2 catalog.
Question 104. What Is Meant By The Attachment Facility?
Answer :
The attachment facility is an interface among DB2 and TSO, IMS/VS, CICS, or batch address areas. It permits software applications to get entry to DB2.
Question one zero five. What Is Meant By Auto Commit?
Answer :
AUTO COMMIT is a SPUFI option that commits the effects of SQL statements robotically if they are efficiently done.
Question 106. What Is A Base Table?
Answer :
A base table is a real desk - a desk that physically exists in that there are physical stored statistics.
Question 107. What Is The Function Of Buffer Manager?
Answer :
The buffer supervisor is the DB2 issue accountable for physically moving facts between an outside medium and (virtual) garage (performs the actual I/O operations). It minimizes the quantity of physical I/O honestly achieved with state-of-the-art buffering strategies(i.E., examine-ahead buffering and appearance-aside buffering).
Question 108. What Is A Buffer Pool?
Answer :
A buffer pool is most important storage that is reserved to meet the buffering necessities for one or greater tablespaces or indexes, and is made of both 4K or 32K pages.
Question 109. How Many Buffer Pools Are There In Db2?
Answer :
There are four buffer swimming pools: BP0, BP1, BP2, and BP32.
Question 110. On The Create Tablespace, What Does The Close Parameter Do?
Answer :
CLOSE physically closes the tablespace when nobody is working at the object. DB2 (release 2.3) will logically near tablespaces.
Question 111. What Is A Clustering Index?
Answer :
It is a form of index that (1) locates desk rows and (2) determines how rows are grouped collectively inside the tablespace
Question 112. What Will The Commit Accomplish?
Answer :
COMMIT will permit data modifications to be everlasting. This then lets in the information to be accessed via different units of labor. When a COMMIT occurs, locks are freed so different packages can reference the just dedicated data.
Question 113. What Is Meant By Concurrency?
Answer :
Concurrency is what permits more than one DB2 utility process to get right of entry to the same facts at essentially the equal time. Problems may additionally arise, which includes misplaced updates, get admission to to uncommitted records, and un-repeatable reads.
Question 114. What Is Cursor Stability?
Answer :
It is cursor stability that “tells” DB2 that database values study through this software are blanketed handiest whilst they're being used. (Changed values are covered till this software reaches the devote point). As soon as a application actions from one row to another, different programs may additionally read or the first row.
Question 115. What Is The Function Of The Data Manager?
Answer :
The Data Manager is a DB2 element that manager the physical databases. It invokes other gadget additives, as essential, to carry out particular capabilities inclusive of locking, logging, and physical I/O operations (such as seek, retrieval, replace, and index renovation).
Question 116. What Is A Database Request Module(dbrm)?
Answer :
A DBRM is a DB2 issue created by way of the DB2 pre-compiler containing the SQL source statements extracted from the utility application. DBRMs are input to the bind process.
Question 117. What Is A Data Page?
Answer :
A records web page is a unit of retrievable data, both 4K or 32K (relying on how the desk is described), containing user or catalog records.
Question 118. What Are Data Types?
Answer :
They are attributes of columns, literals, and host variables. The information sorts are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE and TIME.
Question 119. What Is Declaration Generator(dclgen)?
Answer :
DCLGEN is a facility this is used to generate SQL statements that describe a table or view. These desk or view descriptions are then used to test the validity of other SQL statements at precompile time. The table or view declares are used by the DB2I application DCLGEN to construct a bunch language structure, that is utilized by the DB2 precompiler to verify that accurate column names and facts kinds have been designated inside the SQL assertion.
Question one hundred twenty. What Does Dsndb07 Database Do?
Answer :
DSNDB07 is in which DB2 does its sorting. It includes DB2’s type work area and external garage
Question 121. What Will The Free Command Do To A Plan?
Answer :
It will drop(delete) that present plan.
Question 122. What Is A Host Variable?
Answer :
This is a facts item this is utilized in an SQL declaration to receive a fee or to supply a price. It ought to be preceded with the aid of a colon (:) to inform DB2 that the variable is not a column name.
Question 123. What Will The Db2 Optimizer Do?
Answer :
The optimizer is a DB2 component that tactics SQL statements and selects the get admission to paths.
Question 124. What Is A Page?
Answer :
This is the unit of garage inside a table space or indexspace this is accessed by DB2.
Question one hundred twenty five. What Is Pagespace?
Answer :
Pagespace refers to both to an unpartitioned desk, to an index area, or to a single partition of a partitioned table of index area.
Question 126. What Is A Predicate?
Answer :
A predicate is an element of a seek circumstance that expresses or implies a contrast operation.
Question 127. What Is A Resource Control Table(rct)? Describe Its Characteristics.
Answer :
The RCT is a desk that is described to a DB2/CICS area. It consists of manage traits which can be assembled via the DSNCRCT macros. The RCT matches the CICS transaction ID to its related DB2 authorization ID andplan ID(CICS attachment facility).
Question 128. Where Are Plans Stored?
Answer :
Each plan is described uniquely in the SYSIBM.SYSPLANS table to correspond to the transaction (s) that are to execute that plan.
Question 129. What Is Meant By Repeatable Read?
Answer :
When an application program executes with repeatable study protection, rows referenced with the aid of the program can’t be changed by way of different packages until this system reaches a dedicate point.
Question a hundred thirty. Describe What A Storage Group(stogroup) Is?
Answer :
A STOGROUP is a named series of DASD volumes to be utilized by tablespaces and index spaces of databases. The volumes of STOGROUP ought to be of the identical device type.
Question 131. What Is The Format (inner Layout) Of "timestamp"?
Answer :
This is a seven element fee that includes a date (yymmdd) and time(hhmmss and microseconds).
Question 132. What Is Meant By A Unit Of Recovery?
Answer :
This is a chain of operations inside a unit of labor(i.E., paintings done between commit points).
Question 133. Can Dasd Types Assigned To Storage Groups Be Intermixed(i.E., 3350s And 3380s)?
Answer :
No
Question 134. What Are The Three Types Of Page Locks That Can Be Held?
Answer :
Exclusive, update, and share.
Question 135. Can Db2 Be Accessed By Tso Users? If Yes, Which Command Is Used To Invoke Db2?
Answer :
DB2 may be invoked through TSO users by way of the use of the DSN RUN command.
Question 136. How Are Write I/os From The Buffer Pool Executed?
Answer :
Asynchronously.
Question 137. What Is A Db2 Catalog?
Answer :
The DB2 catalog is a set of tables that incorporate records approximately all the DB2 items(tables, perspectives, plans and so on.).
Question 138. In Which Column Of Which Db2 Catalog Would You Find The Length Of The Rows For All Tables?
Answer :
In the RECLENGTH column of SYSIBM.SYSTABLES
Question 139. What Information Is Held In Sysibm.Syscopy?
Answer :
The SYSIBM.SYSCOPY table carries records about photo copies product of the tablespaces.
Question one hundred forty. What Information Is Contained In A Syscopy Entry?
Answer :
Included is the name of the database, the table area call, and the photo replica type(complete or incremental etc.,) as well as the date and time every reproduction turned into made.
Question 141. What Information Can You Find In Sysibm.Syslinks Table?
Answer :
The SYSIBM.SYSLINKS desk contains data about the links among tables created by referential constraints.
Question 142. Where Would You Find Information About The Type Of Database Authority Held By The User?
Answer :
SYSIBM.SYSDBAUTH
Question 143. Where Could You Look If You Had A Question About Whether A Column Has Been Defined As An Index?
Answer :
This facts can be found in SYSIBM.SYSINDEXES.
Question 144. Once You Create A View, Where Would Information About The View Be Stored?
Answer :
When a view is created, gadget facts about the view is stored in SYSIBM.SYSVIEWS.
Question a hundred forty five. What Is The Sql Communications Area And What Are Some Of Its Key Fields?
Answer :
It is a records structure that should be protected in any host-language software using SQL. It is used to pass comments approximately the square operations to this system. Fields are go back codes, errors messages, handling codes and warnings.
Question 146. How To Get The Value With Leading Zeros?
Answer :
SELECT POLICY_NUMBER, DIGITS (BASE_PREM_TOT)
FROM DB2K.CGIST.TW4T0
If the value of BASE_PREM_TOT is 472.00 then it will be decided on as like this 00000047200
Question 147. Correlated Sub Query?
Answer :
SELECT A.DRIVER_REFERENCE
FROM CGDEV4.TU4ML A
WHERE A.LICENSE_NUMBER = 'POTAPA*355B2' AND
A.DRIVER_REFERENCE =
(SELECT C.CLIENT_REFERENCE
FROM CGDEV4.TZ1SR C
WHERE A.DRIVER_REFERENCE = C.CLIENT_REFERENCE AND
SUBSTR(C.Z_UPPER_FIRST_NAME,1,1) = 'A' AND
SUBSTR(C.Z_UPPER_LAST_NAME,1,three) = 'JZB')
Question 148. How To Delete Duplicated Based On Certain Column Retaining One
Answer :
DELETE FROM TABLE1 A WHERE A.PRIMARYKEY<>(SELECT MIN(PRIMARYKEY) FROM TZCHC B
WHERE A.COLUMN1= B.COLUMN1);
Question 149. How To Count Occurrence Of Letter In A String?
Answer :
SELECT (LENGTH('SUBASH') - LENGTH(REPLACE('SUBASH','S',''))) AS S_COUNT FROM SYSIBM.SYSDUMMY1
Question 150. How To Execute The Sql Through Jcl Using Utility
Answer :
//CREATE EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2T)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) LIB('DB2T.RUNLIB.LOAD')
END
/*
//SYSIN DD *
SELECT COUNT(USER_UPDATED)
FROM CGDEV4.TWC39
WHERE USER_UPDATED='DPPLSPP'
GROUP BY USER_UPDATED
/*
Question 151. How To Eliminate Duplicate Records Except One
Answer :
DELETE FROM TZCHC TD1
WHERE HOLIDAY_DATE<> (SELECT MIN (HOLIDAY_DATE)
FROM TZCHC TD2
WHERE TD1.LONG_DESC = TD2.LONG_DESC
AND TD2.LONG_DESC = 'CHRISTMAS DAY’);
Question 152. How To Subtract The Years, Months And Days Separately From Date
Answer :
SELECT DATE ('2003-10-thirteen') - DEC ('03') YEARS - DEC ('07') MONTHS - DEC ('13') DAYS FROM SYSIBM.SYSDUMMY1;
SELECT DATE ('2003-10-13') - DEC ('2000') YEARS –
DEC ('27') MONTHS - DEC ('forty five') DAY
FROM SYSIBM.SYSDUMMY1;
Question 153. How To Get The Max Count When Group By Some Other Field
Answer :
SELECT USER_UPDATED, COUNT (USER_UPDATED) AS COUNT1
FROM CGDEV4.TWC39
GROUP BY USER_UPDATED
HAVING COUNT (USER_UPDATED) >= ALL
(SELECT COUNT (USER_UPDATED)
FROM CGDEV4.TWC39
GROUP BY USER_UPDATED);
Question 154. How To Find N Th Max Or Min
Answer :
SELECT DISTINCT A.TIMESTAMP_UPDATED, A.USER_UPDATED
FROM CGDEV4.TWC39 A
WHERE five =
(SELECT COUNT (DISTINCT B.TIMESTAMP_UPDATED)
FROM CGDEV4.TWC39 B
WHERE B.TIMESTAMP_UPDATED >= A.TIMESTAMP_UPDATED)
Question one hundred fifty five. What Is Tsq And Tdq?
Answer :
TSQ:
Its like a scratch pad, This will not go to permanent reminiscence until you explicitly specify.
This is used if you have page up/page down logic for your CICS screens.
Program A calling Program B which selects db2 rows, So in application B you can write all your rows fetched to a TSQ and come again to application A, now you can study those rows from TSQ rather than going to Db2 on every occasion.
If you have got big quantity of statistics to be exceeded between to responsibilities of a transaction you may use TSQ.
TDQ:
If you need a few processing have to manifest depending at the facts that you have examine from the screen for example. Person desires to print the display, take the statistics write it to a TDQ this can trigger a transaction which does the printing.
Trigger level tells and shows, after nth file is written to a TDQ it must trigger the transaction associated with it.
TDQ must be defined earlier to apply.
