DB2 Interview Questions:

Q: What is library in AS400?

A Library is a collection of objects. Type *LIB that is used to group related object and to find objects by name.

Q: What is the command to view the library list/ the list of libraries?

DSPLIBL

Q: Is there a command to display the current library?

Yes, DSPCURLIB

Q: What is library list and different types of library lists?

The system uses library lists to locate objects. Four different Library lists used by the IBM i are:

·         System Library contains objects needed by the system.

·    Product Library are handled by the program which places product libraries automatically in the reserved product library positions when needed

·        Current Library is the current working library. We can change the current library to personal library name (PLIBNMAE) by using command:

·         CHGCURLIB PLIBNMAE

·         User Library which was created by user.

Q: Different ways of changing the library lists in as400? 

·         Add Library List Entry (ADDLIBLE)

·         Remove Library List Entry (RMVLIBLE) command 

·         Change Library List (CHGLIBL) command or Edit Library List (EDTLIBL) command 

Q: What's the difference between the production library and the test library?

·         Files are not allowed to update in debug mode in the production library.

·         On the other hand, the files in the test library are allowed to be updated.

Q: What's the Physical File?

·         Physical file is a file that contains data in a structured format.

·         Created using the CRTPF command

Q: How many record formats a PF can consists of?

Only one

Q: What is the difference between the Source Physical File and the Physical File?

The Source Physical File contains a source for the various objects created by this file that have a specific structure.

The Physical File contains data, and the record format may be different for different physical files.

Q: How many levels of security are there in AS400?

Five different levels of security:

·         Level 10 — Physical Security.

·         Level 20 — Password Security.

·         Level 30 — Resource Security.

·         Level 40 — Operating System Integrity.

·         Level 50 — Enhanced Operating System Security.

Q: How many Levels of entries in physical file?

We do have four levels of entries in PF:

·         File-level

·         Record format level

·         Field-level

·         Key-field level

Q: What is the use of UNIQUE keyword and what level it is defined?

Avoid entering duplicate key values. We've got to define it at the file level.

Q: What are the different File level keywords?

·         FIFO:   The retrieval of duplicate key records in first in first out order.

·         LIFO:   The retrieval of duplicate key records in last in first out order.

·         FCFO: The retrieval of duplicate key records in first changed first out order.

Q: What is logical file in AS400?

Logical file which will be created on a Physical file. It’s just a view of the file, which does not occupy memory.

·         We can derive any no. of logical files from a physical file.

·         In simple terms, we can say it as a record selection definition from Physical File.

·         LF can contain up to 32 record formats.

·         LF selects records dynamically.

Q: What is keyword PFILE specifies?

PFILE is used define the Physical file being referenced.

Q: What are the different types of Logical file?

There are two types:

1)      Non-join logical file

2)      Join logical file

Q: What are the different between non-join logical files and join logical files.

 

Non join logical file	Join logical file
We can able to insert or delete or update records using non-logical file.	Insertion, updating or deletion of records is not possible in join logical files.
DFU can be used to display non-join logical file.	DFU is not available
1-32 record format is specified	Only one record format can be specified
Commitment control is used	Commitment control cannot be used.

 

Q: What is the Command to display file details?

DSPFD (Display file description)

Q: Is the members in a PF contain data?

yes, PF members contain data

Q: What is the Command to display fields in a file?

DSPFFD (Display file field description)

Q: What's the DYNSLT?

DYNSLT is a file-level keyword that is used to specify the selection & omission criteria in the file by using select / omit keywords. This will be done during the processing time.

Q: What is the difference between access path and dynamic select?

Access path describes the order,how the records should be read. Dynamic select is used for selection and omission criteria.

Q: What is the use of JDUPSEQ in AS400?

JDUPSEQ is a Join–level keyword, specifies the order how the duplicate join fields are presented when the Join-Logical file is read.

Format:

JDUPSEQ (Sequencing fieldname [*DESCEND])

Q: It is possible to insert record to JOIN LF?

NO, insertion, updating or deleting of records is not possible in JOIN LF.

Q: What is the use of JDFTVAL in AS400?

JDFTVAL assigns the default values for the fields to a secondary file when we join with a primary file.

Q: What happens when JDFTVAL isn't used in AS400 when you join the files?

If we won’t specify JDFTVAL while joining the files, the records will be skipped from the secondary file corresponding to primary file.

Q: What is the difference between physical file and logical file?

Physical file (PF)	Logical file (LF)
PF contains data.	LF does not contain any data.
Physical file contains only one record format	Logical file can contain up to 32 record formats.
Can exist even without LF	Without PF, we cannot create an LF.
PF cannot be deleted, if dependent logical file exists in system.	LF can be deleted without deleting the PF.
PF will be created by using CRTPF command	LF will be created by using CRTLF command

 

Q: Explain self-join?

Joining a file to itself is known as self-join.

Q: What is a field reference file?

This is PF, which does not have any data and contains only the field descriptions and these fields are referred in other PF by using REF and REFFLD

Q: What is the CPYF command used for?

a.) Copies all or partial file from one database/external device to another database/external device.

b.) To transfer records from one PF object to another PF object.

Q: What is the function of CPYSPLF command?

It copies the spooled file to the database file.

Q: What is the function of CRTDUPOBJ command?

To create the replica from the original object.

Q: What is access path in AS400?

·         Access path describes the order, how records should be read.

·         Can be maintained  permanently or temporarily on system.

·     By using the OPNQRYF command, we can create a temporary access path that will be discarded after scope.

Q: What are the types of access path?

Types of Access Path

·         Arrival sequence access path

·         Keyed sequence access path

Q: What is Keyed sequence access path and the ways we bound?

Keyed Sequence Access path: based on the contents of the key fields as defined in DDS.

There are three ways of bounding the access path:

·         Immediate

·         Rebuild

·         Delayed

Q: What is the default access path of a file?

Immediate

Q: What is the max number of fields that can be in a physical file?

8000.

 Q: What is the max number of rec format in a physical file and in join logical file?

1

Q: What is the max number of rec format in non-join logical file?

32

Q: What is the max number of rec format in a display file?

1024

Q: What is the max no of key fields in file?

120

Q: What is the purpose of reorganizing the physical file i. e. , RGZPFM?

Even if you delete records in a physical file through the program, the space used for deleted records is still not used for any other purpose. Therefore, using the RGZPFM command, we can compress the space for deleted records.

Q:  What is the purpose of CHGPF (Change physical file) command?

CHGPF command is used to change the attributes of a physical file. Attributes like

·         Maximum number of members

·         Member size

·         Level check enforced

·         Open data path is shared

Q: How do I change file attributes such as size, file wait time permanently?

Use the CHGPF command

Q: What is QTEMP?

A unique temporary library that is associated with each job. It will be deleted at the end of the job.

Q:  Which libraries are always in the library list (system)?

QTEMP & QGPL

Q: What is Job, what are Job's attributes?

A Job is a basic unit of work.

The attributes are as follows:

·         Job number : The unique system generates sequential number

·         Job name     : Any user name defined (max 10 characters)

·         Username    : The person who started the job.

Q:  What is Job description/JOBD?

A Job description defines an environment in which a batch job or an interactive job to be executed.

Q:  What's the difference between Interactive & Batch Job?

Interactive Job: Job started for a user who has signed up to a workstation.

Batch Job: a predefined group of processing actions submitted to the system to be performed.

Q:  What is Group Job?

A Group Job is attached to one workstation and a user.   There should be an initial group job and it can be branched to many other group jobs.   The group jobs can have different library lists and different output, message queues.   Group Job is useful in enhancing the programmer’s productivity.  It is as though logging into the same user-id many times.

Q: What is the purpose of the level check?

To ensure, format of the file have not modified/changed since compilation time.

Q: Is LEVEL CHECK ERROR possible if we do CHGPF?

If  LVLCHK value is * YES, when a file is opened, it checks for the record format level identifier, if it is not matched, it will cause an error.

If  LVLCHK value is * NO, when a file is opened, it will not check for the record format level identifier and will not cause any error.

Q: What is Journal?

Journal is used to track/record  the changes to the PF . The journal is an object of type *JRN. The information recorded by the journal is stored in an object *JRNRCV called journal receiver.

Q: Explain Commitment Control?

The AS/400 system has an built-in transaction recovery function called commitment control. Commitment control is an extension of the journal function on the system.

If the system fails before commit operation, all changes to the database will be rolled back to the previous commit operation and all records affected will be unlocked.

COMMIT-Commit is performed using the COMMIT command which makes the changes in a file permanent.

ROLLBACK – the transactions in the data file are NOT updated. Rollback happens when transactions are uncommitted and with a ROLLBACK command.

Q: What is the difference between Journaling and Commitment Control?

Journaling, records all transactions and is used to restore or revert transactions to a database file for a specific period of time. 

Commitment control ensures that the transaction is successfully updated to the database (all relevant files).

Q: Explain Assume and Overlay?

Assume Type Y (Yes) to select the ASSUME keyword.  It causes the AS/400 system to assume that this record appears on the display when the file is opened.  Use this keyword to receive data that a previous program has left on the display.

Overlay   Type Y (Yes) to allow the overlaying of fields on this record without erasing the entire display.  Note:  If you type anything other than Y or blank, your entry will be ignored.  You must specify the OVERLAY keyword to select the other keywords on this display, with the exception of PUTOVR.

Q: Explain the purpose of the keywords KEEP and ASSUME?

KEEP: Keep the display from being deleted when the display file is closed.

ASSUME: It is used to specify that the OS/400 program assumes that this record is already shown on the display, When the file is opened.

Q: What's the trigger?

A trigger is an action that occurs automatically whenever a change is made to a physical file.

Q: How many triggers can be associated to a single PF?

6 (max)

Q: How can we know the trigger associated with the PF?

DSPFD with parameter TYPE (*TRG)

Q: How can we add & remove trigger to a PF?

ADDPFTRG & RMVPFTRG

Q: How to create a trigger in AS/400?

The trigger is an event to be performed before or after any change to the database. When you add a trigger to a physical file, you need to define three attributes.

·        The first is an event that causes the trigger to fire. The trigger event can be inserted, updated or deleted from the file.

·         The second attribute to be defined is when to fire the trigger before or after the event.

·         The third attribute to be defined is the identification of the trigger program to run.

·         Insert and delete each update. You can define two triggers.

·         One that will run before the event. It's one that runs after the event.

Q: How to create a multi-member physical file?

By using ADDPFM

Q: What will the COMMIT keyword will accomplish?

COMMIT Keyword allows data changes to be permanent. This enables other work units to access the data. When a COMMIT occurs, locks are freed so that other applications can refer to the committed data.

Q: How does the RUNQRY display the output?

RUNQRY shows the output always in the RRN (Relative Record Number) and not in the keyed sequence.

Q: What is meant by embedded SQL?

These are SQL statements that are embedded in the application program and are prepared during the program preparation process prior to the execution of the program. The statement itself does not change after it has been prepared (although the values of the host variables specified in the statement may change).

Q: What's the Lock? How do you achieve this in AS/400?

To give permission to access the database file. The Locks are supplied by the AS/400 system itself.   The share lock is a read only operation (PF file). Exclusive lock the exclusive lock for inserting, updating, and deleting operations.

Q:  How are you going to release the lock?

By using the RCLRSC [Reclaim Resource] command, we can only release resources. The UNLOCK or CHAIN(N) command also helps release the lock. Use the WRKOBJLCK command and take the F4 command.

Q: What are the various locks available?

SHARE, EXCLUSIVE, UPDATE

Q: How do you place or remove locks on the objects?

 *ALCOBJ & *DLCOBJ

Q: Name some of the object types on which locks can be allocated?

Data Area, Data Queue, File, Library, Message Queue, Menu &Program.

Q: How do you know that records are locked?

Use the command WRKOBJLCK.

Q: When is the access path determined for dynamic SQL?

During run time, when PREPARE statement is issued.

Q: How you will count the number of rows from a table TABLE1?

By using count(*) function will give you the no of records present in table.

Query:

SELECT COUNT(*) FROM TABLE1

Q: What is the SQLCODE which we receive, when we tried to insert/update a duplicate key?

SQLCODE = -803.

Q: How can we eliminate the duplicate values in db2?

By selecting DISTINCT key in query

Query:

SELECT DISTINCT COLUMNNAME FROM TABLENAME

Q: SQLCA stands for?

SQLCA stands for SQL Communication Area.

Q: What is SQLCA’s maximum length?

136

Q: Is there a difference between view and index?

View is not going to have any data. It only shows the data from the table while the Index has the Indexing Data for the sequence in which the Index is created. View is similar to Logical File without having a Key and Index is similar to Logical File with a Key (as keyed logical file has access path data) Table is similar to PF.

Q: What is a host variable?

Host variable is a data item that is used in an SQL statement to send and receive value. It must be preceded by colon(:) to tell DB2 that the variable is not a column name.

Q: What is the stored procedure and how do you define a stored procedure.

Stored procedure is a common piece of code only needs to be written and maintained once and can be called from several programs. Stored procedures can be used for both distributed and non-distributed applications.

It is defined using DECLARE PROCEDURE statement, syntax is as below…

EXEC SQL

DELCARE P1 PROCEDURE

(:PARM1 INOUT CHAR(10))

(EXTERNAL NAME MYLIB/PROC1

LANGUAGE RPGLE

SIMPLE CALL WITH NULLS);

END-SQL

We can have parameters as IN, OUT, INOUT type. Language can be RPGLE, C, CL, etc.. MYLIB/PROC1 this PROC1 is a program written and compiled separately and it is of language which you are specifying in section LANGUAGE.

Q: What happens if we call the stored procedure again and again?

This procedure is included in the program called, so we can have different output set depending on the parameter passed to it. And if the creation is outside the program called, we'll be available with only one output set.

Q:  How to get the second highest salary from employee table?

SELECT MAX(EMPSAL) FROM EMPFILE WHERE EMPSAL< ( SELECT MAX(EMPSAL) FROM EMPFILE)

Q: What is CURSOR and its use?

Cursor is a temporary result-set area, created in memory of the system. Contains data that is retrieved from DB File.

Q: Cursor life cycle or steps of cursor?

Declare, Open, Fetch, Close.

Q: Does the opening of the cursor lock the record?

Yes, the selected records in the cursor are locked when the cursor is opened.

Q: How do I retrieve rows from a DB2 table in Embedded SQL?

The rows in Embedded SQL can be retrieved using the SELECT statement.

Q: What is the result of the statement OPEN CURSOR?

Open statement is used to retrieve rows from the result table.

Q: Can there be more than one cursor open for any program?

Yes! The user can have more than one cursor in program.