DB2 Interview Questions and Answers (31 - 40)

31. Is it possible to use the MAX function on a CHAR column?

Yes, the MAX function can be used on a CHAR (character) column. When applied to character data, the MAX function returns the highest value based on standard string sorting, which is typically the last value in alphabetical order. ​

SELECT MAX(NAME) FROM EMPLOYEE;

Returns the alphabetically last name.

32. What is the picture clause of the NULL Indicator variable?

In DB2, a Null Indicator variable is used to determine whether a column retrieved by a SELECT statement contains a NULL value. In COBOL, the picture clause for a Null Indicator variable is typically defined as S9(4) COMP. A value of zero indicates the column is not null, while a negative or positive value indicates it is null.​

33. Which field in SQLCA shows the number of updated rows after an update statement?

To determine the number of rows affected by an UPDATE statement, you can check the third element of the SQLERRD array, specifically SQLERRD(3). This field indicates how many rows were updated.

34. What is the physical storage length of the TIMESTAMP data type?

The TIMESTAMP data type occupies 10 bytes of storage. It represents date and time information in the format YYYY-MM-DD-HH.MM.SS.NNNNNN, where:

  • YYYY: Year​
  • MM: Month
  • DD: Day
  • HH: Hour​
  • MM: Minute​
  • SS: Second
  • NNNNNN: Microseconds

35. What is the physical storage length of the DATE data type?

The DATE data type requires 4 bytes of storage. It represents a date in the format YYYY-MM-DD, where:

  • YYYY: Year​
  • MM: Month
  • DD: Day

36. What are UNION and UNION ALL?

Both UNION and UNION ALL are used to combine the results of multiple SELECT statements:

  • UNION: Combines the results and eliminates duplicate rows.​
  • UNION ALL: Combines the results and includes all duplicate rows.

For example:

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

This query retrieves unique values from both table1 and table2.

37. Is there any restriction on the use of UNION in embedded SQL?

Yes, when using UNION in embedded SQL, it must be used within a cursor. This allows the program to fetch and process each row of the combined result set individually.

38. What is a CURSOR and what is its use?

A CURSOR is a database object used in embedded SQL to retrieve and process multiple rows one at a time from a result set. It fetches row-by-row data from a SELECT query and allows processing of each row individually in a loop.

39. What is the result of the OPEN CURSOR statement?

The OPEN CURSOR statement executes the associated SELECT query and positions the cursor before the first row of the result set. It allocates resources for the cursor and makes the result set available for row-by-row retrieval using FETCH.

40. Can there be more than one cursor open for any program?

Yes, a program can have multiple cursors open simultaneously. Each cursor operates independently, allowing the program to manage multiple result sets concurrently.