Read the most frequently asked 32 top IBM DB2 interview questions and answers for freshers and experienced.
IBM DB2 Interview Questions and Answers PDF Experienced Freshers
1. what are the bind parameters IBM DB2?
Bind parameters are:
MEMBER - In bind package,
LIRARY - DBRM library name ,
ACTION(add/replace)- package or plan can be add or replace.
ISOLATION - Determines the duration of the page lock.
AQUIRE - Lock a tableon use
RELEASE - releases when the plan terminates
VALIDATE - It will be check about authorization.
EXPLAIN - loads the access path selected by the optimizer in table
2. Is it Possible to declare or create a cursor for UPDATE of table? If yes tell me how? If no Tell me why?
Updating a column: You can update columns in the rows that you retrieve. Updating a row after you use a cursor to retrieve it is called a positioned update. If you intend to perform any positioned updates on the identified table, include the FOR UPDATE clause. The FOR UPDATE clause has two forms:
• The first form is FOR UPDATE OF column-list. Use this form when you know in advance which columns you need to update.
• The second form is FOR UPDATE, with no column list.
Use this form when you might use the cursor to update any of the columns of the table.
For example, you can use this cursor to update only the SALARY column of the employee table:
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM DSN8810.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8810.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE OF SALARY;
If you might use the cursor to update any column of the employee table, define the cursor like this:
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM DSN8810.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8810.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE;
DB2 must do more processing when you use the FOR UPDATE clause without a column list than when you use the FOR UPDATE clause with a column list.
3. Cursors can be declared in both working-storage & procedure division, Agreed.
But is there any difference? If could you please suggest what is the difference.
TIA
There is no difference. But it is always better to declare Cursor in Working-Storage Section because you will not code Open Cursor before Declare Cursor by mistake. It is just a standard to declare Cursor in WSS. As best practice to avoid oversight.
4. If I have 5 Queries in a DB2 Cobol program , while precompiling how many DBRMs will get created and How many Plans and Packages will get created while Bind Process?
when u bind , 5 queries has 5 sql statements in 1 DBRM, its regroup into 1 package, 1 plan...Plan is a collection of packages..
correct me if i'm wrong..
5. could you give me an example how, where i code CHECKPOINT and restart. I need and example You should pass CHECKpoint frequency value from JCL to
cobol program.Intern cobol program will have the table of retart logic.
Table contents(coloumns)be: 1.No of records ,2.No of records + 1, 3.no of records processed etc.
Once the updattion or insertion got stucked while processing ,All the relative data will be stored the above mentioned table.
So check the record from table .Fix the abend and restart your job for the failed step.
This is mainly production support work .manually u have to check the record .and get the records info from the table and restart the job from the failed step
6. Can you search give an array in the WHERE clause of a db2 query?
Arrays are not supported by sql so trying to add an array in a where clause of sql may throw some errors...
only dot operators are possibly used.
7. Can i insert bulk records into a db2 table using qmf of spufi only.
Thru SPUFI/QMF/FileAID you can insert bulk records by selecting the records from one table and inserting into other one.
8. I have some 3 particular fields ..i want to know which all tables have those 3 fields. Is there any way to identify..
can we know by quering system tables.
select * from sysibm.syscolumns where name = <name you 3
columns here>
The above query to metadata will show you the list of table names where these 3 columsn present
9. when we are tying to update a table having 100 rows. if the program abends when updating 51 row . how to start updating again from the 51 row . What was the logic?
when we are tying to update a table having 100 rows.
if the program abends when updating 51 row . how to start updating again from the 51 row .
what was the logic
Ans: The Possible answer would be..if you had used COMMIT before 51st ROW .. the Former records
would have been updated in the table .. If No COMMIt was used.. The whole transaction would have been ROLLBACKED.
Now If you want to start a fresh Transaction and want to start Updating directly from 51st Row
Then There are two ways
1> Perform a loop to scroll till u have read 50 rows
Then Point ur cursor as CURRENT to the 51st Row
Start Updating the Records Till end of table.
or
2> Declare a Scrollable cursor & use FETCH ABSOLUTE option
to fetch a particular row directly
EXEC SQL FETCH ABSOLUTE +51 C1
INTO :TEMP1, :TEMP2, :TEMP3;
10. what is the difference between normal select query and currosor
using select in embedded sql with where clause should fetch only one row , but cursor can be used when we need more rows to be retrieved one at a time.
In case more than one row is retrieved in a select clause it will throw -811 sql error.
11. What are Bind concepts in DB2 cobol?
The first thing is Host languages.
Whatever we are using in cobol (other than cobol langauge command) that are called HOST language.
DB2 is also one of the host language.
COBOL compiler does not know the host language and does not compile the same.
we will take cobol-db2 program..
Here, we are introduce PRE-COMPILER....
Pre-compiler will spilt the cobol db2 program into two module.
1. Cobol program (fully cobol,all the host language commands will get replaced with "MOVE and CALL" statements.
2. DBRM (DataBast Request Module)- only those commands, which are code within 'EXEC SQL .. END-EXEC.
Now, we have spited and we have separate for each..(COBOL and DB2)..
We know about compilation process for COBOL.
Now come to BIND process....
Bind is nothing but, compilation process of DBRM.
The output of this compilation process(BIND) is Package.
If we bind the packages then we will get Plan/Application plan.
When we do the link-edit the cobol program, a thread will be created between the load module of cobol and plan.
12. When DB2 connection fails, will online program work or give errors?
Online program will not work and it will give you abend AEY9 when retry to use if connection has not been
established.
13. EXPLAIN has output with MATCHCOLS = 0. What does it mean? ?
a nonmatching index scan if ACCESSTYPE = I
14. What is DCLGEN ?
DeCLarations GENerator: used to create the host language copy books for the table definitions. Also creates the DECLARE table.
15. Can GROUP BY and ORDERED BY used in a single query?
YES we can use and Group by must come in order to code query.Ex. select * from tab01 group by col1 order by col02
YES we can use and Group by must come in order to code query.Ex. select * from tab01 group by col1 order by col02
16. Can you have more than one cursor open at any one time in a program ?
Yes.
17. What is a composite index and how does it differ from a multiple index?
A multiple index is not one index but two indexes for two different columns of a table. A composite index is one index made up of combined values from two columns in a table. If two columns in a table will often be accessed together a composite index will be efficient.
18. What are the disadvantages of PAGE level lock?
High resource utilization if large updates are to be done
19. How does DB2 determine what lock-size to use?
1. Based on the lock-size given while creating the tablespace
2. Programmer can direct the DB2 what lock-size to use
3. If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE
20. What are delete-connected tables?
Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect the contents of the foreign key table.
21. What is a precompiler?
Precompilor-:
Precompilor's main purpose is to check the syntactical error of COBOL programes which contains embeded SQL statements.It first compiles & then differentiates cobol & DB2 statements.It sends all the DB2 queries to DBRM(database request module) and in that cobol program control makes those query lines as comment lines & issues CALL statement & that moves to MSC(modified sourse code).
22. What is the diff bet plan,package,dbrm?
Plan is generated when you compile the DB2-SQL program.
This plan is stored in the DBRM and binded to database as packages. (stored in syscat.syspackages - pkgname)
DBRM: Data base request module is generated by precompiler which contains the sql statements which are separated from the source program.
PACKAGE: By binding the DBRM package is generated.Package contains the internal structure of the original sql statements.
PLAN: It is the combination of packages that are bind to form a PLAN.
23. Give the COBOL definition of a VARCHAR field.
A VARCHAR column REMARKS would be defined as follows:
...
10 REMARKS.
49 REMARKS-LEN PIC S9(4) USAGE COMP.
49 REMARKS-TEXT PIC X(1920).
24. When can an insert of a new primary key value threaten referential integrity?
Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity
25. What are foreign keys in DB2?
These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables.
Keys (columns) that exist on one table and that are primary keys on another table.
26. Is DECLARE CURSOR executable?
No.
27. 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.
28. A user
defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages.
In SPUFI suppose you want to select max. of 1000 rows , but the select returns only 200 rows.
100 ( for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).
29. What is a clustering index ?
Causes the data rows to be stored in the order specified in the index. A mandatory index defined on a partitioned table space.
30. What is the difference between primary key & unique index ?
Primary : a relational database constraint. Primary key consists of one or more columns that uniquely identify a row in the table. For a normalized relation, there is one designated primary key.
Unique index: a physical object that stores only unique values. There can be one or more unique indexes on a table
31. What is sqlcode -922 ?
Authorization failure
32. What is sqlcode -811?
SELECT statement has resulted in retrieval of more than one row.
IBM DB2 Interview Questions and Answers PDF Experienced Freshers
Bind parameters are:
MEMBER - In bind package,
LIRARY - DBRM library name ,
ACTION(add/replace)- package or plan can be add or replace.
ISOLATION - Determines the duration of the page lock.
AQUIRE - Lock a tableon use
RELEASE - releases when the plan terminates
VALIDATE - It will be check about authorization.
EXPLAIN - loads the access path selected by the optimizer in table
2. Is it Possible to declare or create a cursor for UPDATE of table? If yes tell me how? If no Tell me why?
Updating a column: You can update columns in the rows that you retrieve. Updating a row after you use a cursor to retrieve it is called a positioned update. If you intend to perform any positioned updates on the identified table, include the FOR UPDATE clause. The FOR UPDATE clause has two forms:
• The first form is FOR UPDATE OF column-list. Use this form when you know in advance which columns you need to update.
• The second form is FOR UPDATE, with no column list.
Use this form when you might use the cursor to update any of the columns of the table.
For example, you can use this cursor to update only the SALARY column of the employee table:
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM DSN8810.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8810.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE OF SALARY;
If you might use the cursor to update any column of the employee table, define the cursor like this:
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
FROM DSN8810.EMP X
WHERE EXISTS
(SELECT *
FROM DSN8810.PROJ Y
WHERE X.EMPNO=Y.RESPEMP
AND Y.PROJNO=:GOODPROJ)
FOR UPDATE;
DB2 must do more processing when you use the FOR UPDATE clause without a column list than when you use the FOR UPDATE clause with a column list.
3. Cursors can be declared in both working-storage & procedure division, Agreed.
But is there any difference? If could you please suggest what is the difference.
TIA
There is no difference. But it is always better to declare Cursor in Working-Storage Section because you will not code Open Cursor before Declare Cursor by mistake. It is just a standard to declare Cursor in WSS. As best practice to avoid oversight.
4. If I have 5 Queries in a DB2 Cobol program , while precompiling how many DBRMs will get created and How many Plans and Packages will get created while Bind Process?
when u bind , 5 queries has 5 sql statements in 1 DBRM, its regroup into 1 package, 1 plan...Plan is a collection of packages..
correct me if i'm wrong..
5. could you give me an example how, where i code CHECKPOINT and restart. I need and example You should pass CHECKpoint frequency value from JCL to
cobol program.Intern cobol program will have the table of retart logic.
Table contents(coloumns)be: 1.No of records ,2.No of records + 1, 3.no of records processed etc.
Once the updattion or insertion got stucked while processing ,All the relative data will be stored the above mentioned table.
So check the record from table .Fix the abend and restart your job for the failed step.
This is mainly production support work .manually u have to check the record .and get the records info from the table and restart the job from the failed step
6. Can you search give an array in the WHERE clause of a db2 query?
Arrays are not supported by sql so trying to add an array in a where clause of sql may throw some errors...
only dot operators are possibly used.
7. Can i insert bulk records into a db2 table using qmf of spufi only.
Thru SPUFI/QMF/FileAID you can insert bulk records by selecting the records from one table and inserting into other one.
8. I have some 3 particular fields ..i want to know which all tables have those 3 fields. Is there any way to identify..
can we know by quering system tables.
select * from sysibm.syscolumns where name = <name you 3
columns here>
The above query to metadata will show you the list of table names where these 3 columsn present
9. when we are tying to update a table having 100 rows. if the program abends when updating 51 row . how to start updating again from the 51 row . What was the logic?
when we are tying to update a table having 100 rows.
if the program abends when updating 51 row . how to start updating again from the 51 row .
what was the logic
Ans: The Possible answer would be..if you had used COMMIT before 51st ROW .. the Former records
would have been updated in the table .. If No COMMIt was used.. The whole transaction would have been ROLLBACKED.
Now If you want to start a fresh Transaction and want to start Updating directly from 51st Row
Then There are two ways
1> Perform a loop to scroll till u have read 50 rows
Then Point ur cursor as CURRENT to the 51st Row
Start Updating the Records Till end of table.
or
2> Declare a Scrollable cursor & use FETCH ABSOLUTE option
to fetch a particular row directly
EXEC SQL FETCH ABSOLUTE +51 C1
INTO :TEMP1, :TEMP2, :TEMP3;
10. what is the difference between normal select query and currosor
using select in embedded sql with where clause should fetch only one row , but cursor can be used when we need more rows to be retrieved one at a time.
In case more than one row is retrieved in a select clause it will throw -811 sql error.
11. What are Bind concepts in DB2 cobol?
The first thing is Host languages.
Whatever we are using in cobol (other than cobol langauge command) that are called HOST language.
DB2 is also one of the host language.
COBOL compiler does not know the host language and does not compile the same.
we will take cobol-db2 program..
Here, we are introduce PRE-COMPILER....
Pre-compiler will spilt the cobol db2 program into two module.
1. Cobol program (fully cobol,all the host language commands will get replaced with "MOVE and CALL" statements.
2. DBRM (DataBast Request Module)- only those commands, which are code within 'EXEC SQL .. END-EXEC.
Now, we have spited and we have separate for each..(COBOL and DB2)..
We know about compilation process for COBOL.
Now come to BIND process....
Bind is nothing but, compilation process of DBRM.
The output of this compilation process(BIND) is Package.
If we bind the packages then we will get Plan/Application plan.
When we do the link-edit the cobol program, a thread will be created between the load module of cobol and plan.
12. When DB2 connection fails, will online program work or give errors?
Online program will not work and it will give you abend AEY9 when retry to use if connection has not been
established.
13. EXPLAIN has output with MATCHCOLS = 0. What does it mean? ?
a nonmatching index scan if ACCESSTYPE = I
14. What is DCLGEN ?
DeCLarations GENerator: used to create the host language copy books for the table definitions. Also creates the DECLARE table.
15. Can GROUP BY and ORDERED BY used in a single query?
YES we can use and Group by must come in order to code query.Ex. select * from tab01 group by col1 order by col02
YES we can use and Group by must come in order to code query.Ex. select * from tab01 group by col1 order by col02
16. Can you have more than one cursor open at any one time in a program ?
Yes.
17. What is a composite index and how does it differ from a multiple index?
A multiple index is not one index but two indexes for two different columns of a table. A composite index is one index made up of combined values from two columns in a table. If two columns in a table will often be accessed together a composite index will be efficient.
18. What are the disadvantages of PAGE level lock?
High resource utilization if large updates are to be done
19. How does DB2 determine what lock-size to use?
1. Based on the lock-size given while creating the tablespace
2. Programmer can direct the DB2 what lock-size to use
3. If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE
20. What are delete-connected tables?
Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect the contents of the foreign key table.
21. What is a precompiler?
Precompilor-:
Precompilor's main purpose is to check the syntactical error of COBOL programes which contains embeded SQL statements.It first compiles & then differentiates cobol & DB2 statements.It sends all the DB2 queries to DBRM(database request module) and in that cobol program control makes those query lines as comment lines & issues CALL statement & that moves to MSC(modified sourse code).
22. What is the diff bet plan,package,dbrm?
Plan is generated when you compile the DB2-SQL program.
This plan is stored in the DBRM and binded to database as packages. (stored in syscat.syspackages - pkgname)
DBRM: Data base request module is generated by precompiler which contains the sql statements which are separated from the source program.
PACKAGE: By binding the DBRM package is generated.Package contains the internal structure of the original sql statements.
PLAN: It is the combination of packages that are bind to form a PLAN.
23. Give the COBOL definition of a VARCHAR field.
A VARCHAR column REMARKS would be defined as follows:
...
10 REMARKS.
49 REMARKS-LEN PIC S9(4) USAGE COMP.
49 REMARKS-TEXT PIC X(1920).
24. When can an insert of a new primary key value threaten referential integrity?
Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity
25. What are foreign keys in DB2?
These are attributes of one table that have matching values in a primary key in another table, allowing for relationships between tables.
Keys (columns) that exist on one table and that are primary keys on another table.
26. Is DECLARE CURSOR executable?
No.
27. 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.
28. A user
defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages.
In SPUFI suppose you want to select max. of 1000 rows , but the select returns only 200 rows.
100 ( for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).
29. What is a clustering index ?
Causes the data rows to be stored in the order specified in the index. A mandatory index defined on a partitioned table space.
30. What is the difference between primary key & unique index ?
Primary : a relational database constraint. Primary key consists of one or more columns that uniquely identify a row in the table. For a normalized relation, there is one designated primary key.
Unique index: a physical object that stores only unique values. There can be one or more unique indexes on a table
31. What is sqlcode -922 ?
Authorization failure
32. What is sqlcode -811?
SELECT statement has resulted in retrieval of more than one row.
0 comments:
Post a Comment