Converting RLA Chain to Embedded SQL

[worksofliam]

In the last blog, we covered what some of the specific RLA operations. This time around, we're going to look at the Chain operation.

Simply put, the actions of chain are the following:

1. Set the cursor to the top of the file (SETLL *LOVAL)
2. Loop through rows until the key(s) matches

There are two cases of using chain:

• Against a non-keyed file, using the RRN as the key. I will not be showing fetching against RRN since the ABSOLUTE keyword is not available in Db2 for i.
• Against a keyed file

Chain against a key

Sadly, a chain is not as simple in SQL. In our Embedded SQL, we will have to keep looping through the records until the key matches. We will also do this in a seperate procedure, so we can pass in the key(s) as parameters. There would also have to be a different procedure for each file/cursor

Dcl-f PRODUCTSP UsrOpn Keyed;

Open PRODUCTSP;

Chain (103) PRODUCTSP;
Dsply PRNAME;

Close PRODUCTSP;

Return;

Might become

ctl-opt dftactgrp(*no);

Dcl-Ds PRODUCTSP EXTNAME('PRODUCTSP');
End-Ds;

EXEC SQL DECLARE C1 SCROLL CURSOR FOR
  SELECT * FROM PRODUCTSP;
EXEC SQL OPEN C1;

ChainPRODUCTSP(103);
Dsply PRNAME;

EXEC SQL CLOSE C1;

Return;

//*********************

Dcl-Proc ChainPRODUCTSP;
  Dcl-Pi *N;
    pPRID Like(PRID) Const;
  End-Pi;

  EXEC SQL FETCH BEFORE FROM C1;
  EXEC SQL FETCH NEXT FROM C1 INTO :PRODUCTSP;
  Dow NOT (sqlstate = '02000');
    If (PRID = pPRID);
      Return; //Return when the record is found
    Endif;
    
    EXEC SQL FETCH NEXT FROM C1 INTO :PRODUCTSP;
  Enddo;
End-Proc;

Chain against a key - part 2

You can make the chain much nicer if you know that there is no reads after the chain. Instead, instead of using the cursor, you can just use a new SELECT statement to select the row out of the table. This might be better than reading each row at a time for performance.

ctl-opt dftactgrp(*no);

Dcl-Ds PRODUCTSP EXTNAME('PRODUCTSP');
End-Ds;

ChainPRODUCTSP(103);
Dsply PRNAME;

Return;

//*********************

Dcl-Proc ChainPRODUCTSP;
  Dcl-Pi *N;
    pPRID Like(PRID) Const;
  End-Pi;

  EXEC SQL 
    SELECT * 
    INTO :PRODUCTSP
    FROM PRODUCTSP
    WHERE PRID = :pPRID;
End-Proc;