DB2 Interview Questions and Answers:Part 6
Q1 - What is SPUFI & QMF ?
Ans - SPUFI - Sql Processing Using File Input
QMF - Query Management Facility
Both of them are DB2 interactive menu-driven tools used by developers to create & modify database objects.
Q2 - Name a few utilities available in DB2 ?
Ans - LOAD
MERGE
MODIFY
QUIESCE
REBUILD
RECOVER
REORG
REPORT
REPAIR
RUNSTATS
Q3 - What are the different COLUMN functions ?
Ans - SUM-Returns the total value.
MIN-Returns the minimum value.
AVG-Returns the average value.
MAX-Returns the maximum value.
COUNT-Returns the number of selected rows.
STDDEV-Returns the standard deviation of the column values.
VARIANCE- Returns the variance of the column values.
Q4 - What is a scalar function ?
Ans - A scalar function also produces a single value, but unlike the argument of a column function, an argument of a scalar function is a single value.
Q5 - What is a COLUMN function ?
Ans - A column function produces a single value for a group of rows. You can use the SQL column functions to calculate values based on entire columns of data. The calculated values are from selected rows only (all rows that satisfy the WHERE clause).
Q6 - What are the different types of Table spaces ?
Ans - Simple Table Space
Segmented Table Space
Partitioned Table Space
Q7 - What is Cursor Stability(CS) ?
Ans - A page lock is held only while the cursor is positioned on that page. When the cursor moves to another page, the lock is released. When a page is locked concurrent application programs cannot update or delete a row of the locked page. The current lock is not released until a new lock is acquired. If an application program updates or deletes data, the lock is held until the data is committed. CS applies only to data that is read. All changed data remains locked until COMMIT or ROLLBACK.
Q8 - What is the purpose of a null indicator variable? and what are the possible values in the variable and what do they mean ?
Ans - A Null Indicator is used to indicate whether the host variable has been assigned a null. It used to indicate whether a retrieved character value has been truncated. It is used to set a column to null. The indicator value of –1 means the associated host variable contains null. 0 means the associated host variable does not contain null, nor the returned value is truncated. –2 means the associated host variable contains null due to a numeric or arithmetic conversion error. >0 means the value assigned to the host variable is truncated. The value represented by the indicator variable is the actual length of the character value before truncation.
Q9 - What is commit and rollback ?
Ans - A commit occurs automatically at the end of the program, or by request one or more times during the execution of the program. Releases all locks acquired by the program since the last commit, so that other programs and users may obtain access to the data. Any open cursors are automatically closed. All database changes are made permanent.If any changes made to the tables are not appropriate, then all the changes must by rolled back resulting in the same state of data as they were prior to making changes. Release all locks acquired by the program.
Q10 - In a single table,How to retrieve a employee-id of an employee who works in more than one department?
Ans:Select emp_idfromemployeegroup by emp_id,depthaving count(*)>1;
Q11 - How can you split a table in to exactly half?
Q12 - Say CUST Table contains records like: CUSTNO CUSTNAME CUSTLOC 100 ABC SSS 200 XYZ 300 PQR 400 MNO WWW 500 CVV ------------- -------------Now write a query to retrieve all records with CUSTLOC no data.
SELECT * FROM CUST WHERE CUSTLOC IS NULL
Q13 - A Table feild is declared as Decimal(7,2). I want to insert the decimal in to this column thrucobol-db2 program. How should I declare my local input file variable or any suggestion?
01 dec pic 9(5).9(2). is equal to dec(7,2)
Q14 - what is check point and restart Logic ? why do we go for that ??
Check point and restart logic are mainly used when our table has 1000 records and we are getting abend after 100 records and we want to start the execution after 100th record we use this.for this we have to use check points for every 100 recordsQ40) what needs to be done if a table is in copy pending / check pending status?
Use repair utility REPAIR SET TABLESPACE DATABASE.TABLESPACENAME NOCOPYPEND REPAIR SET TABLESPACE DATABASE.TABLESPACENAME NORCVRPEND REPAIR SET TABLESPACE DATABASE.TABLESPACENAME NOCHECKPEND
Q1 - What is SPUFI & QMF ?
Ans - SPUFI - Sql Processing Using File Input
QMF - Query Management Facility
Both of them are DB2 interactive menu-driven tools used by developers to create & modify database objects.
Q2 - Name a few utilities available in DB2 ?
Ans - LOAD
MERGE
MODIFY
QUIESCE
REBUILD
RECOVER
REORG
REPORT
REPAIR
RUNSTATS
Q3 - What are the different COLUMN functions ?
Ans - SUM-Returns the total value.
MIN-Returns the minimum value.
AVG-Returns the average value.
MAX-Returns the maximum value.
COUNT-Returns the number of selected rows.
STDDEV-Returns the standard deviation of the column values.
VARIANCE- Returns the variance of the column values.
Q4 - What is a scalar function ?
Ans - A scalar function also produces a single value, but unlike the argument of a column function, an argument of a scalar function is a single value.
Q5 - What is a COLUMN function ?
Ans - A column function produces a single value for a group of rows. You can use the SQL column functions to calculate values based on entire columns of data. The calculated values are from selected rows only (all rows that satisfy the WHERE clause).
Q6 - What are the different types of Table spaces ?
Ans - Simple Table Space
Segmented Table Space
Partitioned Table Space
Q7 - What is Cursor Stability(CS) ?
Ans - A page lock is held only while the cursor is positioned on that page. When the cursor moves to another page, the lock is released. When a page is locked concurrent application programs cannot update or delete a row of the locked page. The current lock is not released until a new lock is acquired. If an application program updates or deletes data, the lock is held until the data is committed. CS applies only to data that is read. All changed data remains locked until COMMIT or ROLLBACK.
Q8 - What is the purpose of a null indicator variable? and what are the possible values in the variable and what do they mean ?
Ans - A Null Indicator is used to indicate whether the host variable has been assigned a null. It used to indicate whether a retrieved character value has been truncated. It is used to set a column to null. The indicator value of –1 means the associated host variable contains null. 0 means the associated host variable does not contain null, nor the returned value is truncated. –2 means the associated host variable contains null due to a numeric or arithmetic conversion error. >0 means the value assigned to the host variable is truncated. The value represented by the indicator variable is the actual length of the character value before truncation.
Q9 - What is commit and rollback ?
Ans - A commit occurs automatically at the end of the program, or by request one or more times during the execution of the program. Releases all locks acquired by the program since the last commit, so that other programs and users may obtain access to the data. Any open cursors are automatically closed. All database changes are made permanent.If any changes made to the tables are not appropriate, then all the changes must by rolled back resulting in the same state of data as they were prior to making changes. Release all locks acquired by the program.
Q10 - In a single table,How to retrieve a employee-id of an employee who works in more than one department?
Ans:Select emp_idfromemployeegroup by emp_id,depthaving count(*)>1;
Q11 - How can you split a table in to exactly half?
Q12 - Say CUST Table contains records like: CUSTNO CUSTNAME CUSTLOC 100 ABC SSS 200 XYZ 300 PQR 400 MNO WWW 500 CVV ------------- -------------Now write a query to retrieve all records with CUSTLOC no data.
SELECT * FROM CUST WHERE CUSTLOC IS NULL
Q13 - A Table feild is declared as Decimal(7,2). I want to insert the decimal in to this column thrucobol-db2 program. How should I declare my local input file variable or any suggestion?
01 dec pic 9(5).9(2). is equal to dec(7,2)
Q14 - what is check point and restart Logic ? why do we go for that ??
Check point and restart logic are mainly used when our table has 1000 records and we are getting abend after 100 records and we want to start the execution after 100th record we use this.for this we have to use check points for every 100 recordsQ40) what needs to be done if a table is in copy pending / check pending status?
Use repair utility REPAIR SET TABLESPACE DATABASE.TABLESPACENAME NOCOPYPEND REPAIR SET TABLESPACE DATABASE.TABLESPACENAME NORCVRPEND REPAIR SET TABLESPACE DATABASE.TABLESPACENAME NOCHECKPEND
