Introduction

An SQL function that you define and may call is known as a user-defined function (UDF). A UDF’s logic usually extends or improves SQL by adding capabilities that SQL lacks or can’t accomplish adequately, just like with built-in functions you may call from SQL. You may also encapsulate functionality with a UDF and call it again from different places within your code.

Stored procedure types

There are two categories into which stored procedures can be divided:

SQL stored procedures External stored procedures.

• SQL stored procedures
  • SQL stored procedures are written in the SQL language. This makes it easier to port stored procedures from other database management systems (DBMS) to the iSeries server and from the iSeries server to other DBMS. Implementation of the SQL stored procedures is based on procedural SQL standardized in SQL99.

  Example 1: Create a stored procedure to update a balance for customer using input parameters.

  • Create a table as BANK using RUNSQLSTM command.

    CREATE OR REPLACE TABLE BANK
    (
     CUSTNO      NUMERIC(10),
     CUSTNAME    VARCHAR(30),
     BALANCE     DECIMAL(9,2),
     PRIMARY KEY(CUSTNO)
     )
     RCDFMT RBANK;
    

  • Create a SQL Stored procedure to update BALANCE column in BANK table based on input parameter:

    CREATE OR REPLACE PROCEDURE UPDATECUSTBANK
       (IN IN_CUSTNO   NUMERIC(10),
        IN IN_BALANCE  DECIMAL(9,2))
    LANGUAGE SQL MODIFIES SQL DATA
    UPDATE BANK
       SET BALANCE = BALANCE + IN_BALANCE
       WHERE CUSTNO = IN_CUSTNO
    
    

  • Now add the BALANCE as 500 for CUSTNO-100,003 in the BANK table:

                            CUSTNO   CUSTNAME                BALANCE
    100,001   JAMES        100.15
    100,003   BRYAN     500.00
    100,002   JOHN     214.00
    100,004   RICHS     854.00
    100,005   RHONDA      10,000.00
    
    

  • Now add the BALANCE as 500 for CUSTNO-100,003 in the BANK table using CALL statement in STRSQL:

    CALL UPDATECUSTBANK(100003,500)
     CALL statement complete.
    

    CUSTNO   CUSTNAME                     BALANCE
    100,001   JAMES                       100.15
    100,003   BRYAN                       1000.00
    100,002   JOHN                        214.00
    100,004   RICHS                       854.00
    100,005   RHONDA                      10,000.00
    
    

  Example 2: Create a stored procedure with CASE condition to update a balance based on a performance for customer using input parameters.

  • Create a stored procedures using RUNSQLSTM command.

    CREATE OR REPLACE PROCEDURE INCREMENTSALARY
       (IN IN_CUSTNO    NUMERIC(10),
        IN IN_RATING    CHAR(10) )
    LANGUAGE SQL MODIFIES SQL DATA
    CASE IN_RATING
      WHEN 'GOOD' THEN
         UPDATE BANK
            SET BALANCE = BALANCE * 10 WHERE CUSTNO = IN_CUSTNO;
      WHEN 'AVG ' THEN
         UPDATE BANK
            SET BALANCE = BALANCE * 5  WHERE CUSTNO = IN_CUSTNO;
      WHEN 'BAD ' THEN
         UPDATE BANK
            SET BALANCE = BALANCE * 1  WHERE CUSTNO = IN_CUSTNO;
    END CASE
    
    

  • Now increment the BALANCE as 10x for CUSTNO-100,001 in the BANK table using CALL statement in STRSQL:

    CALL INCREMENTSALARY(100001,’GOOD’)
    CALL statement complete.
    

  • CUSTNO 100001 balance updated successfully.

    CUSTNO   CUSTNAME                             BALANCE
    100,001   JAMES                               1,001.50
    100,003   BRYAN                               1,000.00
    100,002   JOHN                                  214.00
    100,004   RICHS                                 854.00
    100,005   RHONDA                             10,000.00
    
    

• External stored procedures
  • An external stored procedure is written by the user in one of the programming languages on the iSeries server. You can compile the host language programs to create *PGM objects. To create an external stored procedure, the source code for the host language must be compiled so that a program object is created. Then the CREATE PROCEDURE statement is used to tell the system where to find the program object that implements this stored procedure. The stored procedure registered in the following example returns the name of the supplier with the highest sales in a given month and year.The procedure is implemented in ILE RPG with embedded SQL:

    c/EXEC SQL
    c+ CREATE PROCEDURE HSALE
    c+ (IN YEAR INTEGER ,
    c+ IN MONTH INTEGER ,
    c+ OUT SUPPLIER_NAME CHAR(20) ,
    c+ OUT HSALE DECIMAL(11,2))
    c+ EXTERNAL NAME SPROCLIB.HSALES
    c+ LANGUAGE RPGLE
    c+ PARAMETER STYLE GENERAL
    c/END_EXEC
    
    

  • The following SQL CALL statement calls the external stored procedure, which returns a supplier name with the highest sales:

    c/EXEC SQL
    c+ CALL HSALE(:PARM1, :PARM2, :PARM3, :PARM4) c/END-EXEC
    

An SQL function that user defines and may invoke is known as a user-defined function (UDF). A UDF’s logic usually extends or improves SQL by adding capabilities that SQL lacks or cannot accomplish adequately, just like with built-in functions, you may invoke it from SQL. You may also encapsulate functionality with a UDF and call it again from different places within your code.

Example 1: Program based UDF

The table structure that will be utilized in the example is listed below.

Table1- EMPLOYEE

R REMP
EMPID 10S 0
EMPNAME 20A
EMPJOIND 8A
K EMPID

Create a procedure in NOMAIN module as GET0001 for retrieving a day for a date compile it using the 15 option from PDM.

ctl-opt nomain;
dcl-proc getdays export;
dcl-pi getdays char(3);
in_date char(8) const;
end-pi;
dcl-s day char(3);
dcl-s dowk packed(1:0);
dowk = %rem(%diff(%date(in_date:*iso0):d'0001-01-01': *days): 7);
select;
when dowk = 0;
day = 'MON';
when dowk = 1;
day = 'TUE';
when dowk = 2;
day = 'WED';
when dowk = 3;
day = 'THU';
when dowk = 4;
day = 'FRI';
when dowk = 5;
day = 'SAT';
when dowk = 6;
day = 'SUN';
endsl;
return day ;
end-proc;

Create a service program and bind the module with it.

CRTSRVPGM SRVPGM(PIOLIB/GETDATSV) MODULE(PIOLIB/GET0001) EXPORT(*ALL)

Now create a user-defined function (UDF) that can access this module.

create function rtvday( in_date char(8))
returns char(3)
language rpgle
deterministic
no sql
external name 'PIOLIB/GET0001(GETDAYS)'
parameter style general
program type sub
RTVDAY was created, changed, or dropped, but object not modified.

Using UDF in SQL query:

SELECT EMPID, EMPNAME, EMPJOIND , RETDAY(EMPJOIND) FROM PIOLIB.EMPLOYEE

Result Set

EMPID     EMPNAME      EMPJOIND       RETDAY ( EMPJOIND )
10,001     JAMES       20240319           TUE
10,001     KINGS       20220219           SAT
10,001     YORKS       20200110           FRI

Example 2: SQL based UDF.

create or replace function priority(indate date) returns char(7) 
language sql
begin
return(
case when in_date < current date then 'NONE'
when in_date <= current date +2 days then 'HIGH'
else 'MEDIUM'
end
);
end

Invoke the UDF from an SQL query as shown below:

SELECT EMPID, EMPNAME, EMPJOIND, EMPORDER, PRIORITY(EMPJOIND) FROM PIOLIB/EMPLOYEE

Result Set

EMPID    EMPNAME     EMPJOIND     EMPORDER    PRIORITY
10,001    EMP1       20221005     04/04/24        NONE

Introduction

Why do we need Triggers?

• Monitors Database Activity
• Maintain Database Consistency and Enforce business rules
• Concept of Trigger
• Insert, Delete, Update on Table/View
• Called By Database
• Allow granularity at the column level
• UDF/stored procedure

Simple SQL Triggers Example

• Trigger Condition
• Trigger Action
• Example#1: After Trigger
• Example#2 Before Trigger
• Example#3 Multi condition Triggers
• Example#4 Conditional Triggers
• Example#5 Trigger Using Stored Procedure

How to create SQL triggers

How to see SQL triggers

How to remove SQL triggers

Introduction

When an add, remove, or update operation is made to a table, triggers offer a mechanism to keep an eye on, adjust, and manage the tables

When there is significant interdependence between the tables or when a certain action has to be taken in response to a table change, it is highly beneficial.

Why do we need Triggers?

• Monitors Database Activity

  • Only when there is an addition, deletion, or insert made to the trigger-associated table does the trigger become active.

• Maintain Database Consistency and Enforce business rules

  • When two or more tables are co-linked to one another, any changes made to the trigger-associated table will cause all related tables to synchronize with one another.

    Additionally, the trigger’s action will modify the related table to achieve this synchronization.

• Concept of Trigger

  • A certain set of activities are carried out upon the trigger’s execution.

• Insert, Delete, Update on Table/View

  • On insert, remove, or upon adding a record to the table or view, a trigger can be inserted.

• Called By Database

  • The database (database management system) itself calls the specific activities when the trigger performs them.

• Execute actions that are not database-related.

  • The trigger can also be used for non-database tasks, such as emailing or sending messages.

• Allow granularity at the column level

  • Instead of adding a trigger to the entire table, you may apply it to a single column.

• UDF/stored procedure

  • When the triggering process is carried out, SQL triggers are called by the database management system, which may then run UDF or SQL stored procedures.

Simple SQL Triggers Example

The table structures that will be utilized in the example are listed below.

Table1- TGRPF1

    A          R RTGRPF1
    A            CUSTID           9P 0
    A            NAME           10A
    A            DEPARTMENT     50A
    A          K CUSTID
    

    Table2 – TGRPF2

A          R RTGRPF2
A            TRGTIME         Z         COLHDG('Trigger' 'time')
A            JOBNAME       28A         COLHDG('Job' 'name')
A            TRGTYPE        2A         COLHDG('Trigger' 'type')

• Trigger Condition

  • The trigger will be added to TGRPF1 and it will get triggered when any record is inserted in the table.

• Trigger Action

  • Newly Added information will be logged in TGRPF2 field on each row insert.

• Example#1: After Trigger

  • The trigger name that will be activated upon the insertion of a record in TGRPF1 is New Customer.

    CREATE TRIGGER NEW_CUSTOMER
    AFTER INSERT ON TGRPF1
    FOR EACH ROW MODE DB2ROW
    INSERT INTO RTGRPF2
                   VALUES(CURRENT TIMESTAMP,
                          JOB_NAME,
                          'I') ;
    
                    

• Example#2 Before Trigger

  • The trigger name that will be activated Before to a record being placed into TGRPF1 is New Customer.

                        CREATE OR REPLACE TRIGGER NEW_CUSTOMER
                        BEFORE INSERT ON TRGPF1
                        FOR EACH ROW MODE DB2ROW
                        INSERT INTO RTGRPF2
                                       VALUES (CURRENT TIMESTAMP,
                                              JOB_NAME,
                                              'I');
                    

• Example#3 Multi condition Triggers

  • 01 CREATE OR REPLACE TRIGGER NEW_CUSTOMER
    02 AFTER INSERT OR DELETE OR UPDATE ON TRGPF1
    03 REFERENCING NEW ROW AS N OLD ROW AS O
    04 FOR EACH ROW MODE DB2ROW
    05 BEGIN
    06    DECLARE TSTAMP TIMESTAMP;
    
    07    IF INSERTING THEN
    08      INSERT INTO TRGPF2
                   VALUES (CURRENT TIMESTAMP,
                          JOB_NAME,
                          'I' ) ;
    09    END IF;
    
    10    IF DELETING THEN
    11      INSERT INTO TRGPF2
                   VALUES(CURRENT TIMESTAMP,
                          JOB_NAME,
                          'D') ;
    12    END IF ;
    
    13    IF UPDATING THEN
    14      SET TSTAMP = CURRENT TIMESTAMP ;
    
    15      INSERT INTO TRGPF2
                   VALUES(TSTAMP,
                          JOB_NAME,
                          'U') ;
    
    16    END IF ;
    17  END
    
                    

  Line 1: One excellent feature added to IBM i 7.2 and subsequent 7.1 TRs is CREATE OR REPLACE. It keeps me from having to let go of the trigger before making the updated version. Previously, I would have just had CREATE. The remainder of the line specifies that NEW_CUSTOMER will be the name of my trigger.

  Line 2: AFTER specifies that the trigger will take place subsequent to the database activity for updates, deletes, and inserts into my library’s TRGPF2 file.

  Line 3: This line indicates that all fields/columns in the new row/record will have the prefix “N” and all fields in the previous row/record will have the prefix “O”.

  Line 4: DB2ROW indicates that each row/record action will be followed by the trigger’s execution. Only once all row operations are finished will the alternative, DB2SQL, run.

  Line 5: Indicates where the trigger code starts.

  Line 6: A timestamp (or variable TSTAMP) is defined. This is what we’ll use to insert the update rows.

  Lines 7-9: In the event that the action involved an insert, a row is added to the trigger output file. Only the updated values from the file are utilized because this is an insert.

  Lines 10–12: This portion of the trigger, which puts the previous values into the output file, is executed when a deletion is carried out.

  Lines 13–17: We would like both the old and new values for an update. Additionally, we need the timestamp in both rows to match. The timestamps in the two rows would have been different if we had used CURRENT TIMESTAMP. We can ensure that the timestamp value in both rows is the identical by relocating CURRENT TIMESTAMP to the variable specified on line 6.

  Line 18: The code for trigger ends here, matching the BEGIN on line 5.

• Example#4 Conditional Triggers

  CREATE OR REPLACE TRIGGER NEW_CUSTOMER
  AFTER UPDATE ON TRGPF1
  REFERENCING NEW ROW AS NEW OLD ROW AS OLD
  FOR EACH ROW MODE DB2ROW
  WHEN(NEW.CUSTID <> OLD.CUSTID)
  BEGIN
  INSERT INTO TRGPF2
                 VALUES (CURRENT TIMESTAMP,
                        JOB_NAME,
                        'U' ) ;
  END;
  
          

  The line below retrieves the values from both the current and previous rows.

  REFERENCING NEW ROW AS NEW OLD ROW AS OLD 

  Additionally, the condition that compares the value of the old and new College fields is shown below.

  WHEN(NEW.COLLEGE <> OLD.COLLEGE)

• Example#5 Trigger Using Stored Procedure
  

          CREATE OR REPLACE PROCEDURE SQLTRGPROC(
          IN P_CUSTID  DECIMAL(9,0),
          IN P_FLAG  CHAR(1)
           )
  SPECIFIC SQLTRGPROC
  BEGIN
   IF P_FLAG = 'I' THEN
    INSERT INTO TRGPF1(CUSTID) VALUES(P_CUSTID);
   END IF;
  
   IF P_FLAG = 'D' THEN
    DELETE FROM TRGPF1 WHERE CUSTID = P_CUSTID;
   END IF;
  
   IF P_FLAG = 'U' THEN
    UPDATE TRGPF1 SET  CUSTID = P_CUSTID WHERE CUSTID = P_CUSTID;
   END IF;
  END
  
      

  The CALLPROCFROMTRIGGER trigger is generated in this example, and if it already exists, it is replaced with this trigger.

  CREATE OR REPLACE TRIGGER CALLPROCFROMTRIGGER
  AFTER INSERT OR DELETE OR UPDATE OF NAME ON TRGPF1
  REFERENCING NEW ROW AS NEW OLD ROW AS OLD
  FOR EACH ROW MODE DB2ROW
  PROGRAM NAME TRIGGER9
  BEGIN
  DECLARE L_CUSTID DECIMAL(9,0);
  DECLARE L_FLAG CHAR(1);
   IF INSERTING THEN
      SET L_FLAG = 'I';
      SET L_CUSTID = NEW.CUSTID;
     CALL SQLTRGPROC1(L_CUSTID, L_FLAG);
  END IF;
  
  IF DELETING THEN
      SET L_FLAG = 'D';
      SET L_CUSTID = NEW.CUSTID;
     CALL SQLTRGPROC1(L_CUSTID, L_FLAG);
  END IF;
  
  IF UPDATING AND NEW.CUSTID <> OLD.CUSTID THEN
     SET L_FLAG = 'U';
     SET L_CUSTID = OLD.CUSTID;
     CALL SQLTRPROC1(L_CUSTID, L_FLAG);
   END IF;
  END;
  
      

How to create SQL triggers

We use the Run SQL Statements command, RUNSQLSTM, add the trigger to the file. The CREATE TRIGGER creates an ILE C program in the library which is a trigger program.

OBJECT      TYPE        ATTRIBUTE   TEXT
    TEST_00001  *PGM        CLE         SQL TRIGGER TEST_TESTFILE
    

How to see SQL triggers

As with the RPG trigger program if we want to see what trigger is on the file can use the Display File Description command, DSPFD…

DSPFD FILE(TESTFILE) TYPE(*TRG)

Or we can use the SYSTRIGGER view.

    SELECT CAST(TABSCHEMA AS CHAR(10)) AS Table_library,
       CAST(TABNAME AS CHAR(10)) AS Table_name,
       TRIGTIME,
       EVENT_U,EVENT_I,EVENT_D,
       CAST(TRIGPGMLIB AS CHAR(10)) AS Trigger_library,
       CAST(TRIGPGM AS CHAR(10)) AS Trigger_program,
       TRIGNAME
  FROM QSYS2.SYSTRIGGER
 WHERE TABSCHEMA = 'MYLIB'
   AND TABNAME = 'TESTFILE'

Which gives:

TABLE_LIBRARY  TABLE_NAME  TRIGTIME  EVENT_U  EVENT_I  EVENT_D
    MYLIB          TESTFILE    AFTER        Y        Y        Y


    TRIGGER_LIBRARY  TRIGGER_PROGRAM  TRIGNAME
    MYLIB              TEST_00001     TRG_TESTFILE
    

How to remove SQL triggers

If we only want to use the second trigger, then we need to remove the existing triggers from the file. We could use the Remove Physical File Trigger command, RMVPFTRG,…

RMVPFTRG FILE(PGMSDHTST3/WA042P)

Or we could use DROP TRIGGER:

DROP TRIGGER TRG_TESTFILE

This DROP TRIGGER leaves the other trigger, the one with only the delete, in place.

INTRODUCTION:

DB2 allows us to write queries within a query and this concept is called sub-querying. This is basically just writing a nested SQL inside another SQL statement.
Sub-querying allows us to look up data on a file based on a subset data from same/another file in the system.

Sub query can be written in following places:

• In the SELECT clause
• In the FROM clause
• In the WHERE clause using IN/NOT IN/ANY/ALL/EXISTS/NOT EXISTS keywords.

Let us use the following tables to understand subqueries and its possibilities better.

STUDMAST:

A student master file/table.

SUBMAST:

A subject master file/table which holds minimum marks to pass against each subject.

STUDMARKS:

A file/table to hold marks and results that each student has attained against each subject.

1. SUBQUERY IN SELECT CLAUSE:

Let us now look at the table STUDMARKS where all data is available, but nothing is readily understandable. No one can say which student scored how much against what subject just with the help of this single table.

We will now make an SQL with subqueries to display ‘Student Full Name’ and ‘Subject Description’, so that it becomes easier to understand the data that is presented.

Select Marks.Stud_Id, 
(Select (Trim(Stud.Stud_Fname)||' '||Trim(Stud.Stud_Lname)) From DEMO.STUDMAST Stud
Where Stud.Stud_Id = Marks.Stud_ID) As Student_Name,  
(Select Sub.Sub_Desc From DEMO.SUBMAST Sub Where Sub.Sub_Id = Marks.Sub_Id),
Marks.Marks, Marks.Results
From  DEMO.STUDMARKS Marks

This query would fetch name and subject description from tables STUDMAST and SUBMAST respectively using the respective key values and return text.

QUERY RESULT:

This makes the data much easier to understand for everybody.

2. SUBQUERY IN FROM CLAUSE:

Let’s say the above data needs to be filtered out more and we need to only display the records of students who have failed, the subset can be fetched as follows.

Select Marks.Stud_Id,
(Select (Trim(Stud.Stud_Fname)||' '||Trim(Stud.Stud_Lname)) From DEMO.STUDMAST Stud 
Where Stud.Stud_Id = Marks.Stud_ID) As Student_Name, 
(Select Sub.Sub_Desc From DEMO.SUBMAST Sub Where Sub.Sub_Id = Marks.Sub_Id),
Marks.Marks, Marks.Results
From (Select * From DEMO.STUDMARKS Where Results = 'FAIL') As Marks

Though in this case the result could be achieved simply by placing the condition in the where clause. In real life, this result set could be narrowed down using complex queries over multiple files/tables and use that subset feed to out query.

3. SUBQUERY IN WHERE CLAUSE:

1. USING IN/NOT IN:A simple SQL with a sub query to just give the names of students who have failed will be as follows:

   Select * From  DEMO.STUDMAST Stud Where Stud.Stud_Id In 
   (Select Stud_Id From DEMO.STUDMARKS Where Results = 'FAIL')
   

   QUERY RESULT:

   

   A query to get the name of students who have not failed would go as follows:

   

   Select * From  DEMO.STUDMAST Stud Where Stud.Stud_Id Not In 
   (Select Stud_Id From DEMO.STUDMARKS Where Results = 'FAIL')
   

   QUERY RESULT:

   

2. USING EXISTS/NOT EXISTS:The subquery creates a result set which becomes a lookup for the main query to filter out and display the final results. The result set achieved by using IN/NOT IN can also be achieved with the EXISTS/NOT EXISTS clause.Find the names of students who have failed as follows

   Select * From  DEMO.STUDMAST Stud Where Exists
   (Select * From DEMO.STUDMARKS Where Results = 'FAIL' And Stud_Id = Stud.Stud_ID)
   

   QUERY RESULT:

   

   Find the names of students who have not failed as follows

   

   Select * From  DEMO.STUDMAST Stud Where Not Exists
   (Select * From DEMO.STUDMARKS Where Results = 'FAIL' And Stud_Id = Stud.Stud_ID)
   

   QUERY RESULT:

   

3. USING ANY:The ANY keyword when used before a subquery determines if any value in the result subset matches to the any of the values in the left hand side (or the main query) of subquery and returns result if TRUE.The subquery for finding out if there are any students who have scored above 95 is as follows.

   Select * From  DEMO.STUDMAST Stud Where Stud. 
   Stud_ID  = ANY(Select Stud_ID From  DEMO.STUDMARKS Where Marks >= 95)
   

   QUERY RESULT:

   

4. USING ALL:The ALL keyword when used before a subquery returns all values in the result subset that matches to the values in the left hand side (or the main query) of subquery and returns result if TRUE.The below query returns the list of subjects per student in which the students have scored above the average of the total marks per subject.

   Select Mark.Stud_ID, (Select Sub.Sub_Desc From DEMO.SUBMAST Sub Where Sub.Sub_Id = Mark.Sub_Id), Mark.Marks,
   Mark.Results From  DEMO.STUDMARKS Mark Where Mark.Marks >= ALL(Select AVG(MARKS) From DEMO.STUDMARKS GROUP BY Sub_Id)
   

   QUERY RESULT:

   

Introduction:

In RPGLE (Report Program Generator Language), a cursor is a database feature that allows you to work with a set of rows from a result set one at a time. Cursors are particularly useful when you need to interact with data stored in a relational database, such as IBM Db2 on the AS400 platform. Cursors offer precise control over retrieving, updating, or deleting rows from database tables.

A cursor contains information on the statement executed and the rows of data accessed by it. The set of rows the cursor holds is called the active set.

Basically, there are four types of cursors in RPGLE-

• Sequential/Serial Cursor
• Scrollable Cursor
• Sensitive Cursor
• Insensitive Cursor

Now before directly jump over the types of cursors, let us understand what major operations we generally do for retrieval of data from as400 data base using cursor

• Declare Cursor
• Open Cursor
• Fetch Operation
• Close Cursor

• Declare CursorTo declare the cursor, you just need to write the statement in below format-

  Declare + Your Cursor Name + Cursor For + Your SQL Statement

  Example in fix format:

  

  Example in free format:

  

• Open CursorAfter declaring the cursor, you can open it using the ‘OPEN’ statement as explained below-

  Open + Your Cursor Name

  Example in fix format:

  

  Example in free format:

  

• Fetch OperationAfter opening the cursor, you can retrieve the data from it using the fetch statement. You can write the fetch statement as below-

  Fetch From + Cursor Name + Into + Variable names with colon and separated by commas.

  Example in fix format:

  

  Example in free format:

  

• Close CursorAfter fetching the data, you need to close the cursor using CLOSE keyword. You can write the statement as explained below-

  Close + Your Cursor Name

  

  Example in free format:

  

Sequential/Serial Cursor:

• In sequential cursor, we can fetch each row once after open cursor.
• In sequential cursor, we can fetch row only in forward direction that’s why it is also known as forward-only cursors.
• Once row is fetched, we can not move in any other direction within the data set.
• Sequential cursor is defined without scroll keyword.
• If we do not define the cursor type then by default it will be considered as sequential/serial cursor.

Fix Format Example of Serial Cursor

Free Format Example for Serial Cursor

Scrollable Cursor:

• Scrollable cursor is defied with keyword Scroll.
• Scrollable cursor provides us ability to navigate result set in both forward and backward direction.
• In scrollable cursor, we can move to any row within the result set.
• In scrollable cursor, we can fetch any row of result set multiple times.
• In scrollable cursor, row fetch from result set also depends on the keyword used with scroll.
  There are following keywords that can be used:
  • NEXT – It will fetch the next row within the data set with respect to current row.
  • PRIOR – It will fetch the previous row within the data set with respect to
    current row.
  • FIRST – It will FETCH the first row in the results set.
  • LAST – It will FETCH the last row in the results set.
  • CURRENT – It will re-FETCH the current row from the result set.
  • BEFORE – It will position the cursor before the first row of the results set.
  • AFTER – It will position the cursor after the last row of the results set.
  • RELATIVE n – It will fetch the nth with respect to current row within the data set.
    here n represent the integer value that can be positive or negative.
  • ABSOLUTE n – If n is 0 then cursor will be positioned before the first row of the result
    table. If n is positive then cursor will be positioned on the nth record of
    result table from TOP. If n is negative then cursor will be positioned
    on the nth record of the result table from BOTTOM.

Use of Next Keyword with Scroll Cursor in Fix Format:

Use of Next Keyword with Scroll Cursor in Free Format:

Use of Prior Keyword with Scroll Cursor in Free Format:

Use of Prior Keyword with Scroll Cursor in Free Format:

Use of First Keyword with Scroll Cursor in Fix Format:

Use of First Keyword with Scroll Cursor in Free Format:

Use of Last Keyword with Scroll Cursor in Fix Format:

Use of Last Keyword with Scroll Cursor in Free Format:

Use of Current Keyword with Scroll Cursor in Fix Format:

Use of Current Keyword with Scroll Cursor in Free Format:

Use of Before Keyword with Scroll Cursor in Fix Format

Use of Before Keyword with Scroll Cursor in Free Format

Use of After Keyword with Scroll Cursor in Fix Format

Use of After Keyword with Scroll Cursor in Free Format

Use of Relative Keyword with Scroll Cursor in Fix Format

Use of Relative Keyword with Scroll Cursor in Free Format

Use of Absolute Keyword with Scroll Cursor in Fix Format

Use of Absolute Keyword with Scroll Cursor in Free Format

Sensitive Cursor:

• Sensitive cursor has ability to detect changes made to the underlying data by the other processor or users while the cursor is active.
• This means that if another user or program modifies a row in the database table that your sensitive cursor is currently working with, the cursor can recognize that change.
• This helps you to keep your data up to date and avoid errors in a multi-user environment.

Fix Format Example

Free Format Example

Insensitive Cursor:

• Insensitive cursor doesn’t detect changes made to the underlying data by other processes or users while the cursor is active.
• This means insensitive cursor treats that data as static and doesn’t keep track of changes made by others.
• Insensitive cursors are helpful in situations where you don’t want to be affected by changes made by others or processes while you are working with specific data.
• They offer data consistency and can be more efficient than sensitive cursors in certain scenarios.

Free Format Example

Fix Format Example

Cursor in Dynamic/Embedded SQL

To use the cursor in dynamic SQL, we need to follow below steps-

• We need to store our SQL statement into a variable as string.
• Then we can prepare our SQL statement using ‘PREPARE’ keyword.
• Now we can declare the cursor for our prepared SQL statement.
• Now we can process our cursor as normal cursor.

Example of dynamic SQL without parameter marker:

Example of dynamic SQL with parameter markers:

• Inner join
• Left outer join
• Right outer join
• Exception join
• Cross join

Inner join:

An inner join exclusively presents the rows from each table where there are corresponding values in the join columns. Rows lacking a match between the tables are omitted from the resulting table.

There are two ways to perform an inner join:

• JOIN syntax
• WHERE clause

• Using Join Syntax:

  SELECT STUNO, LASTNAME, PROJNO, MARKS
  FROM GRAD.STUDENT INNER JOIN GRAD.PROJECT
  ON STUNO = STUID
  WHERE MARKS > 60
  

  

  Consider the below example, where we need to fetch a student’s project details alongwith the student’s lastname, roll number, project number and marks. The student’s identity details are in table STUDENT and the project details are stored in PROJECT table. So, to identify what project is assigned to a student we would require a relationship between STUDENT and PROJECT table, this relationship can be called a common column on which the join will be performed. In our case those columns are STUNO and STUID.

• Using Where clause:To achieve the equivalent join as the JOIN syntax using the WHERE clause, include both the join condition and any additional selection condition within the WHERE clause. The tables intended for joining are specified in the FROM clause, separated by commas. See the example below.

  SELECT STUNO, LASTNAME, PROJNO, MARKS
  FROM GRAD.STUDENT , GRAD.PROJECT
  WHERE STUNO = STUID
  and MARKS > 60
  

Left outer join:

A left outer join retrieves all the rows obtained from an inner join, along with an additional row for each unmatched row in the first table.

Consider a scenario where you aim to identify all students and their current project assignments, including those who aren’t currently overseeing any projects. The subsequent query will furnish the details of all students with marks greater than 60, along with the project numbers they’re assigned to.

SELECT STUNO, LASTNAME, PROJNO, MARKS
FROM GRAD.STUDENT LEFT OUTER JOIN GRAD.PROJECT
ON STUNO = STUID
WHERE MARKS > 60

Right outer join:

A right outer join retrieves all the rows obtained from an inner join, along with an additional row for each unmatched row in the second table.

Consider a scenario where you aim to identify all projects and the assigned students, including projects which aren’t currently assigned to any student. The subsequent query will furnish the details of all the projects where project group is ‘SCIENCE’, along with the student details.

SELECT PROJNO, STUNO, LASTNAME, MARKS
FROM GRAD.STUDENT RIGHT OUTER JOIN GRAD.PROJECT
ON STUNO = STUID
WHERE PROJGRP =’SCIENCE’

Exception join:

An exception selectively retrieves only the rows from the first table that lack a corresponding match in the second table based on the join condition.

Utilizing the identical tables as previously mentioned, we will fetch the student details who aren’t assigned to any projects.

SELECT STUNO, LASTNAME, PROJNO, MARKS
FROM GRAD.STUDENT EXCEPTION JOIN GRAD.PROJECT
ON STUNO = STUID
WHERE MARKS > 60

Cross join:

A cross join, also referred to as a Cartesian Product join, produces a resultant table wherein each row from the first table is paired with every row from the second table. The quantity of rows in the resultant table equals the product of the row count in each table. The result of a cross join will include all the combinations of records from the two tables.

When the involved tables are extensive, this join operation may consume significant time. So, it is advised to filter the tables with a selection criterion that reduces the number of resulting rows as per your requirement.

Consider the tables in our previous examples viz. STUDENT and PROJECT, when a cross join will be performed each row in table STUDENT will be joined with every row in table PROJECT.

SELECT * FROM STUDENT CROSS JOIN PROJECT

A ‘Common Table Expression’ or a CTE is a temporary view that is created and used for executing SQL statement and destroyed at the end of execution.  CTE can be used everywhere where an entire SQL statement can be written. CTEs improve the readability of the code and reduces repeated usage of same query within an SQL query.

CTE Syntax:

WITH cte_Name (Column_List) As
(CTE_Definition)
SQL_Statement;

Cte_Name – This would be the CTE name which will be used to refer the same in the desired SQL Statement.
Column_List – This would be the column list from the CTE_Definition. The number of columns defined here should match to what is defined within the CTE_Definition. The column names can be renamed here if required. This is optional.
CTE_Definition – This contains the SQL statement that needs to be defined for the CTE being created.

Let us use the following tables to understand CTEs better.
A student master file/table named STUDMAST.

A subject master file/table which holds minimum marks to pass against each subject, named SUBMAST.

A file/table to hold marks and results that each student has attained against each subject, named STUDMARKS.

A simple CTE over STUDMARKS table to find out the list of students that have failed can be written as follows:

With FAILEDSTUDS As (
        Select Stud_Id, Sub_Id, Marks
        From  DEMO.StudMarks
        Where Results = 'FAIL')
Select * From  FAILEDSTUDS

QUERY RESULT:-

Here, FAILEDSTUDS is the CTE that is defined prior to writing the actual query itself. And the use of CTE makes this SQL look like one of the simplest select queries.

Another sample query to find out the subjects in which students are able to score at least an average mark of 50 can be written as follows:

With MarksAvg As (
        Select Sub_ID, Avg(Marks) As Avg
        From   DEMO.STUDMARKS
        Group By Sub_Id)
Select S.*, A.Avg
From  DEMO.SUBMAST S, MarksAvg A Where S.Sub_Id = A.Sub_Id And A.Avg >= 50

QUERY RESULT:-

Simply, once the CTE is defined, the CTE name itself can be used in the query just like any other database table name and this gives us the freedom to use CTEs in any possible ways like joins, subqueries, etc.

CTEs can also be used with Insert statements

If we have a table with all the minute information about a subject in each record, and user requires a custom information to be extracted out of that data and move into another table, it can also be achieved in one single query.

We will use the same example of the subjects in which students are able to score at least an average mark of 50 to be inserted into another table of same structure as output columns as follows:

Insert into library/File_name

With MarksAvg As (
     Select Sub_ID, Avg(Marks) As Avg
     From DEMO.STUDMARKS
     Group By Sub_Id)
Select S.*, A.Avg
From DEMO.SUBMAST S, MarksAvg A Where S.Sub_Id = A.Sub_Id And A.Avg >= 50

In simple terms, the MERGE statement compares key fields between two tables and then modifies one table based on the results of that comparison. This helps in managing data effectively. While the MERGE statement might seem more complicated than basic INSERTs or UPDATEs at first, once you grasp its concept, you’ll find it more convenient to use than handling INSERTs or UPDATEs separately.

Performance considerations for the SQL MERGE statement

The MERGE statement’s efficiency relies heavily on using the right indexes for matching the source and target tables. It’s important to optimize the join conditions and filter the source table to fetch only the required records for the statement to perform its tasks effectively.

Let’s look at example below to have a better understanding.

Imagine you have two tables named source and target. You’re tasked with updating the target table based on matching values from the source table. Here are three scenarios to consider:

• Some rows exist in the source table but not in the target table. In this situation, you’ll need to insert these rows from the source table into the target table.
• Both the source and target tables contain rows with identical keys, but their non-key column values differ. In this case, you’ll need to update the rows in the target table with the corresponding values from the source table.
• There are rows in the target table that don’t exist in the source table. Here, you can keep these unmatched rows.

Using INSERT, UPDATE, and DELETE statements separately requires constructing three distinct statements to update data in the target table with matching rows from the source table.

However, DB2 for i simplifies this process with the MERGE statement, enabling you to perform all three actions simultaneously. Here’s the example of the MERGE statement:

MERGE INTO tbl_target target USING tbl_source source
     ON target.EMPNO = source.EMPNO
          AND target.EMPDEP = source.EMPDEP
WHEN NOT MATCHED THEN
   INSERT VALUES(EMPNO, EMPADR, EMPLVL, EMPSAL, EMPDEP)
WHEN MATCHED THEN
   UPDATE SET EMPSAL = source.SALARY

The statement above compares rows in the target table with those in the source table based on the EMPNO and EMPDEP columns. These columns are the primary keys, so that unique records are selected.

For any row in the source table without a matching EMPNO and EMPDEP row in the target table (NOT MATCHED), a record INSERT is performed. It involves adding a new row to the target table, including the EMPNO,EMPADR, EMPLVL, EMPSAL and EMPDEP values from the source table.

On the other hand, for rows in the source table that do have corresponding rows in the target table (MATCHED), the EMPSAL value in the target table’s row is updated with the value from the source table.

Deletion using MERGE statement:

SQL MERGE can also be used to delete records from the table. Below is an example where employee having EMPLVL less than 2 are deleted from target table.

MERGE INTO tbl_target target USING tbl_source source
     ON target.EMPNO = source.EMPNO
          AND target.EMPDEP = source.EMPDEP.

WHEN MATCHED and EMPLVL < 2
   THEN DELETE

WHEN MATCHED THEN
   UPDATE SET EMPSAL = source.SALARY