Convert RPG multi-File Read to SQL multi-Table query

RPG multi-File Reading and conversion to SQL multi-Table query for applications on the 1949th are mostly stuck in the RPG, C, COBOL, or Java file operation stages. IBM's strategy is to migrate these operations on physical or logical files to SQL; operate datasets through SQL, rather than access file records; traditional applications access and operate records, and programmers decide the method of data access. As a result, the performance of applications depends on the programming skills and experience of programmers; SQL operations are data-centric. DB2 provides more data access methods. DB2 can select the optimal combination of access methods to optimize complex data access for us. The following is an RPG program fragment that mainly processes the return values of two views and one table function, and then obtains the data: the value of the variable P_SYSID is the query condition, traverse the PMFM202 view (PMRM202 is the name of the PMFM202 logical file). perform the following operations on each record read: 1. the P20PCOD field is not displayed in the PMFM203 view. 2. Call the GETATRVAL function (which can be considered an SQL table function, and return related data in tables PMFM20 and PMFM21) to obtain the data corresponding to each P20PCOD. 3. Compare the data returned from the table function with the data in the program entry structure (DPM1000: P_SYSID, P_CCY, P_CPFLG. [SQL]... C P_SYSID SETLL PMRM202 c dow 1 = 1 C P_SYSID READE PMRM202 c if % eof c leave c endif * invalid c if P20EFCT = '0' c iter www.2cto.com c endif * Non-last product C p20PCOD CHAIN PMRM203 c if % found c iter c endif c * Get product attribute c exsr @ prdatr c * start date c if vycmidte <K_OPNDAT c iter c endif c * stop date C IF VYCMIDTE> K_ENDDAT c iter c endif c * Valid State c if K_VLDSTS <> '1' c iter c endif c * Unit individual logo c if P_CPFLG <> ''and p _ CPFLG <> export c iter c endif www.2cto.com c eval O01_PCOD = P20PCOD c eval O01_DESP = k_prdna m c if O01_DESP = 'C EVAL ready = P20DESP c endif c add 1 V_CNT C * generate the downstream information c movel (P) PM1000C01O msgtxt c * controls the frontend response to c eval format = VYCMTCDE + '01' c exsr # sndqry c enddo ...... C @ prdatr begsr c * function GETATRVAL is used to read PMFM20 tables and PMFM21 tables with the P20PCOD parameter and C * parameters, and then retrieve the relevant data. Here we can think of it: the C * Table Function getatrval takes the PMFM20 table and Related Data in the PMFM21 table. /FREE release = % TRIM (GETATRVAL (P20PCOD: 'hangzhou'); k_prdna M = GETATRVAL (P20PCOD: 'prdnam'); K_VLDSTS = GETATRVAL (P20PCOD: 'vldsts '); k_OPNDAT = % DATE (GETATRVAL (P20PCOD: 'opdat '); K_ENDDAT = % DATE (GETATRVAL (P20PCOD: 'enddat'); K_OPNORGLVL = GETATRVAL (P20PCOD: 'opnorglvl '); K_OPNORGTYP = GETATRVAL (P20PCOD: 'opnorgtyp'); Signature = GETATRVAL (P20PCOD: 'opntllvl'); Signature = GETATRVAL (P20PCOD: 'Optlrtyp'); www.2cto.com/END-free c endsr ...... from the above RPG code, we can prove this: "For access to traditional data records, the programmer decides the data access method. The application performance depends on the programmer's programming skills and experience ". The programmer must be clear about the file traversal sequence? In what way does the traversal work? READE or CHAIN? Without rich experience and understanding of the operation code of RPG reading files, it is likely that part of the data in the file will be omitted, or the file will not be read at all. If you convert to SQL to access a table, you do not have to worry about the access method of the table in the database. You only need to write the SQL statement with the correct logic. The code above www.2cto.com is converted into a DB2 SQL query statement, which can be written as follows: [SQL] select. p20pcod O01_PCOD, IFNULL (c. p21DESP, B. p20DESP) O01_DESP from pmfm202 a, pmfm203 B, TABLE (GETATRVAL (B. p20pcod) c where B. p20biz =. p20biz and B. p20pcod = c. p21pcod and. p20BIZ = P_SYSID and. p20EFCT <> '0' and c. p21STRD <= current date and c. p21ENDD> = current date and TRIM (c. p21VAL) = P_CPFLG