Db2 Using Sql Interview Questions with 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.