Chain vs Select RPG

Question

I have a CHAIN operation as defined below:

chain(e) (GetIacIsrKey.BaseKeyNumber:                     
          wNameKeyNumber:                                 
          GetIacIsrKey.SocietyCode) IACISR001R IacIsrData;

The file is declared as below:

FIACISR001 IF E K DISK rename(IACISR:IACISR001R) usropn

I know it retrieves the first found record.. If I were to convert this to SQL, how would I write it considering I want to fetch the same record fetched by chain?

SELECT * FROM IACISR WHERE 
  BASEKEY = <VALUE> AND 
  NAMEKEYNUMBER = <VALUE> AND 
  SOCIETYCODE = <VALUE> FETCH FIRST ONLY ..

I am concerned if the ordering would change bw chain and select ?

In summary, my question should be what is the default order in which chain retrieves a record. This is a table created using SQL and has PRIMARY KEY( BASEKEY , NAMEKEY , SOCCODE , LOCAFFNO ) ) defined.

Answer 1

I am not expert in SQL so I don't know what the default order is. But you could add "ORDER BY LOCAFFNO" to get it to return the record with the earliest LOCAFFNO value.

Answer 2

When you use CHAIN, you are using an index (logical file) where the sort order is determined by the order of the key fields. When you use SQL, it builds a query plan itself and there the order of records can be in any order (unless you explicitly specify ORDER BY). In addition, SQL can be “static” (the query text is explicitly written in the program code, the query plan is built at the compilation stage)

exec sql declare CUR cursor for select ...

or “dynamic” (the query text is generated during program execution, the query plan is built during program execution).

query = 'select ...';
exec sql declare STMT statement;
exec sql prepare STMT from :query;
exec sql declare CUR cursor for STMT;

The second case is not very good in terms of speed and efficiency - building a query plan can take about 30% of the program execution time and about the same amount of processor resources.

Based on many years of experience, we have come to the conclusion that in cases where work with one table (one logical file) is required, it is more profitable to use opcodes - CHAIN / SETLL / SETGT / READ, and where a complex query is required on several related tables and the sample size can be significant it is preferable to use static SQL (we use dynamic SQL only in the most extreme cases, when it is absolutely impossible to do without it).

Some operations are faster to perform using opcodes. For example, if you need to check the presence of at least one record in a table with a given value of key fields, it is not at all necessary to read this record from the physical file - you just need to make sure that it is present in the logical

setll (key value) logical file;
recExist = %equal(logical file);

Such a check will require a minimum of time and processor resources.