|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.
- SQL INSERT
- SQL UPDATE
- SQL DELETE
- SQL Triggers
- External Triggers
- CREATE TRIGGER statement is used to create SQL trigger in DB2 AS400.
- INSERT, UPDATE, DELETE triggers.
- Can be defined on VIEW.
- ADDPFTRG cl command is used to create an External trigger in DB2 AS400.
- INSERT, UPDATE, DELETE, READ triggers.
- 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.
- Before Delete Trigger
- Before Insert Trigger
- Before Update Trigger
- After Delete Trigger
- After Insert Trigger
- After Update Trigger
- Read Only Trigger (External Trigger)
- Maintain Referential Integrity
- Log Historical Data
- 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.
- 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.
- 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.
- 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.
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