AS400 Interview Questions (DB2) - Part 1

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.

Answer:

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:

• SQL INSERT
• SQL UPDATE
• SQL DELETE

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.
• INSERT, UPDATE, DELETE, READ triggers.
• Can be defined for TABLE only.

Note:

• 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)?

Answer:

• 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) ?

Answer:

• 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?

Answer:

• 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?

Answer:

• 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