AS400 Interview Questions (DB2) - Part 1

DB2 AS400 Interview questions and Answers

DB2 AS400 Interview Questions and Answers  - part 1, Interview questions, AS400 Interview questions, DB2 Interview question, IBM i Interview question, AS400 Interview questions and Answers,as400 interview questions and answers for experienced,as400 interview question and answer,as400 db2 interview questions and answers,as400 interview questions,as400 cl interview questions,as400 interview questions and answers,as400 rpg interview questions,interview questions on as400 rpg,as400 interview questions hsbc,ibmi interview questions,rpgle interview questions,rpg developer interview questions,ibmi Interview Questions and Answers,AS400 DB2 Interview Questions and Answers
AS400 Interview Questions (DB2)  - part 1

Q1: Brief about triggers? What is the purpose of using triggers? Where we use the concept of the trigger in real-time.


Triggers in IBM i (AS400):

  • Set of Actions/programs.
  • Execute automatically.
  • Change operation in Table/View/LF/View over view.
Change operation in DB2 Database:
Types of Triggers in IBM i (AS400):
  • SQL Triggers
  • External Triggers
SQL Triggers:
  • CREATE TRIGGER statement is used to create SQL trigger in DB2 AS400.
  • INSERT, UPDATE, DELETE triggers.
  • Can be defined on VIEW.
External Triggers:
  • ADDPFTRG cl command is used to create an External trigger in DB2 AS400.
  • Can be defined for TABLE only.
  • Up to 300 triggers can be defined for a single table or view.
  • SQL and External triggers can be applied to the same table.
  • More than one trigger event can be specified in a single trigger program.
  • We can add READ trigger only in case of an External trigger.
Types of Triggers on a Table:
  • Before Delete Trigger
  • Before Insert Trigger
  • Before Update Trigger
  • After Delete Trigger
  • After Insert Trigger
  • After Update Trigger
  • Read Only Trigger (External Trigger)
Why we use Triggers:
  • Maintain Referential Integrity
  • Log Historical Data
Real time use of Triggers:
  • Validate Input Data.
  • Enforce business rules in an application.
  • Generate a unique Id for the newly inserted row.
  • Write to other files in an application for Auditing purposes.
  • Maintain data consistency etc.

Q2: What is the purpose of Journaling and why do we use Journaling in DB2 AS400 (IBM i)?


  • Record Database changes are Journaling.
  • Journal and Journal Receiver objects.
  • Journal records and writes journal entries in Journal receiver.
  • Journal and Journal receiver objects are used to perform COMMIT and ROLLBACK.
  • Support FORWARD and BACKWARD recovery.
  • SQL SCHEMA created using CREATE SCHEMA (library), this will create a Journal and Journal Receiver in the Schema.
  • Table created inside Schema automatically journaled to the Journal QSQJRN present inside the schema.
  • Users can stop Journaling, but this will stop the process to run under the COMMIT CONTROL option.
  • CRTJRNRCV (Create Journal Receiver object).
  • CRTJRN (Create Journal object).
  • STRJRNPF (Start Journaling a file).

Q3: Why do we create Join Logical files in real-time in DB2 AS400(IBM i) ?


  • PF1 + PF2 = JLF
  • We can create a JLF over more than one PF and save the read operations. Only one read operation is required to read multiple files.
Restrictions in JLF:
  • Cannot change PF using JLF.
  • Cannot use DFU to display JLF.
  • Can specify 1 record format in JLF.
  • Commitment control cannot be used on JLF.

Q4: Why do we have a field reference file in our AS400 application?


  • PF with no data, only field descriptions.
  • Also known as Data Dictionary.
  • All definitions are present in one place.
  • The definition needs to be defined once.
  • Definitions need to be changed only in one place.
Referring to Data Dictionary:
  • Device DSPF, printer PRTF, and Database file(PF) except LF can refer to Data Dictionary.
  • REFFLD keyword is used.

Q5: Why do we get level check error(CPF4131) in real-time applications? Explain a few scenarios?


  • The program attempts to access files whose record format level is different from the record format level when the program was compiled.
Solution to level check error(CPF4131):
  • Set LVLCHK option(*NO) when creating a file or writing an Override statement. (Not a good approach)
  • Recompile the program (preferred approach)
    • Run cl command DSPPGMREF from the command line on the program facing level check error.
    • Check files record format id within the program.
    • Match with Actual file record format.
    • Unmatched   --> RECOMPILE PGM.

Related Post

AS400 Interview Questions - Part 1
AS400 Interview Questions - Part 2
AS400 Interview Questions - Part 3
AS400 Interview Questions - Part 4
AS400 Interview Questions - Part 5
AS400 Interview Questions - Part 6
AS400 Interview Questions - Part 7
AS400 Interview Questions - Part 8
AS400 Interview Questions - Part 9
AS400 Interview Questions - Part 10
AS400 Interview Questions - Part 11
AS400 Interview Questions - Part 12
As400 Interview Questions - Part 13
AS400 Interview Questions (DB2) - Part 2

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain