-
What is the difference between IN subselects and EXISTS subselect?
If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN. (IN for index is the mnemonic).
nlarsensmith
- Feb 22nd, 2011
***IN***IN subselects perform search on a list of values returned by the subquery.ex)select * from table1 where col1 IN(select col1 from table2 where <some condition>)-Based on the range of...
-
What is a DB2 access path?
An access path is the method used to access data specified in DB2 sql statements.
Mandar Dindorkar
- Jul 27th, 2012
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...
jzmoonrock56
- Aug 31st, 2011
The access path is determined by DB2 during the bind process. This determines what indexes will be used.
-
What is SPUFI?
SPUFI stands for SQL processing using file input. It is the DB2 interactive menu-driven tool used by developers to create database objects.
Read Best Answer
Editorial / Best Answer
Answered by: burelamanohar
- Feb 6th, 2007
SPUFI : SQL Processing Using File Input, it supports the on-line execution of SQL statements from a TSO terminal
vineeta bachani
- Oct 17th, 2019
In SPUFI: we can store and write multiple queries but will execute only one at a time. here we mention user-defined input and output datasets
IN QMF: every time we open it we have to write the SQL query again. we dont give input and output datasets -
-
Can anyone tell me when do we need a COLLECTION? I know the concept of PLAN and PACKAGE, but unable to understand about COLLECTION.
sethy.pradeep
- Nov 18th, 2008
Collection is generally used for grouping of db2 packages.For each collection there is one collection id should be created.
-
What is the difference between package and plan?
kamalbasha
- Nov 26th, 2015
The main difference between the plan and package is in db2+cobol pogramm we undergo precompilation because the compiler could not understand the statements are in between EXEC and END-EXEC so we go fo...
Mandar Dindorkar
- Jul 27th, 2012
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...
-
What happens when more than one row satisfies a Select statement that is executed outside of any cursor processing?
pawan_singh
- May 14th, 2010
If you using just a simple select SQL statement and it is giving you more than one row as output than you will get a SQL code retrun -811. To avaoid this depending your requirement you can modify your query or if you need more than one row than just use cursor.ThanksPawan
-
DB2 control structure
A control structure used by DB2 to communicate with the application program isa.Address spaceb.Authidc.Threadd.Viewe.String
Thread -
What is the full form of DBRM ?
a.Database Restrict Modelb.Database Resurrect Modelc.Database Request Moduled.Database Reference Modulee.Database Restrict ModuleDatabase Request Module
-
DB2 Index Space
A index space contains: Multiple indexesSingle index onlyIndexes for a single tableThere is no concept of index space
Single index only -
What is the result of the following statement?
Given the following transaction in an embedded SQL application: CREATE TABLE dwaine.mytab (col1 INT, col2 INT) INSERT INTO dwaine.mytab VALUES (1,2) INSERT INTO dwaine.mytab VALUES (4,3) ROLLBACK What is the result of issuing the following statement? SELECT * FROM dwaine.mytab
-
DB2 Large Objects (LOB) Columns
In DB2, Large Objects (LOB) Columns are used to store large objects (Documents, pictures, voice, and mixed media) in database. Which of the following clauses can't be used on LOB columns?a) GROUP BYb) HAVINGc) ORDER BYd) NONE OF THE ABOVE
kris mohan
- Sep 13th, 2011
None of the above
-
Bind process
What happens during pre-compilation and finally bind process?
naga1126
- Jun 16th, 2010
Bind process is mainly for two processes:1. To syntax check the Query (To see the Columns and tables referred are actually present etc. )2. To find the efficient access path.Result of Bind is an Executable form of the DBRM.
yuvaevergreen
- Apr 11th, 2010
Precompilation:Separates out the SQL statements in the COBOL program and creates the DBRM.BIND:Creates the executable format of DBRMs.
-
Exec sql and End exec
why do we declare include in between exec sql and end exec and why cant we declare copybook in place of include. Is their any reason why we declare include only
ABSS1981
- Sep 18th, 2009
In addition to the answers posted one of the additional things to have an INCLUDE between EXEC SQL and END-EXEC is that:You INCLUDE most likely will be a DCLGEN and it is going to have non COBOL langu...
memays
- Apr 30th, 2009
It all has to do with the sequence of compiling a program with imbedded SQL. It is important to understand that a COBOL program with SQL statements will actually go through 2 steps in the ...
-
Referential Integrity
Which one of the following does not help to maintain referential integrity?a. Unique constraintsb. Primary keysc. Cascading actions d. Foreign keys
-
What is the difference between group by and order by?
Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.
-
What is a cursor and what is its function?
An embedded sql statement may return a number of rows while the programming language can only access one row at a time. The programming device called a cursor controls the position of the row.
nnina
- Oct 12th, 2009
Cursor is a programming device. If you want to fetch more than one record from DB2 table it can be easily done with the help of cursor because single DB2 query fetch at a time one record if it find more than one records (Duplicate) it will gives -811 (trying to retrieve more than one row).
-
How do we unload the DB2 tables using JCL?
yuvaevergreen
- Mar 26th, 2010
We can use UNLOAD utility for loading a table to file. Following is a sample JCL.//STEP1 EXEC DSNUPROC,UID='SMPLUNLD',UTPROC='',SYSTEM='DSN'//SYSREC DD DSN=USERID.SMPLUNLD.SYSREC,// DISP=...
Smitha Brindavan
- Mar 15th, 2007
If DBP1 is the subsystem name, then the below unload step can be used//STEP001 EXEC DBP1UNLD,USER='USERNAME',ULDTAB='FILE1',DATE='UNLOAD(+1)' //UNLOAD.SYSREC00 DD SPACE=(CYL,(6...
-
DB2 WITH HOLD option
What does WITH HOLD option do ?forces cursor to close after commitb.Keeps cursor open after commitc.Does not allow cursor to close till all rows are fetchedd.Locks the cursore.None of the aboveKeeps cursor open after commit
prasadkss
- Dec 4th, 2009
When a commit is issued by a program all the cursors opened by that program are closed, but WITH HOLD option is used to retain the cursor pointer, even if the commit is used.
-
Bind Parameters
What are the important bind parameters to be used in bind card? What is the significance of each parameter?
Mandar Dindorkar
- Jul 27th, 2012
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...
jzmoonrock56
- Aug 31st, 2011
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.
DB2 Interview Questions
Read Best Answer
Ans