INFDS (File Information Data Structure)

FILE INFORMATION DATA STRUCTURE (INFDS)

* -------------------------------------------------------------------

 * FILE INFORMATION DATA STRUCTURE

 * -------------------------------------------------------------------

 * The INFDS contains the following feedback information:

 * File Feedback (length is 80)

 * Open Feedback (length is 160)

 * Input/Output Feedback (length is 126)

 * Device Specific Feedback (length is variable)

 * Get Attributes Feedback (length is variable)

 * -------------------------------------------------------------------

 * Standard RPG feedback area 1-80

 * -------------------------------

 D INFDS ds

 D File *FILE

 * * File name

 D OpenInd 9 9

 * * File open?

 D EOFInd 10 10

 * * File at eof?

 D FileStatus *STATUS

 * * Status code

 D OpCode *OPCODE

 * * Last opcode

 D Routinr *ROUTINE

 * * RPG Routine

 D ListNum 30 37

 * * Listing line

 D SpclStat 38 42S 0

 * * SPECIAL status

 D RecordFmt *RECORD

 * * Record name

 D MsgID 46 52

 * * Error MSGID

 * ----------------------------------------------------------------

 * The next 4 fields are available after POST

 D Screen_P *SIZE

 * * Screen size

 D NLSIn_P *INP

 * * NLS Input?

 D NLSOut_P *OUT

 * * NLS Output?

 D NLSMode_P *MODE

 * * NLS Mode?

 * ----------------------------------------------------------------

 * Open feedback area 81-240

 * NOTE that getting data beyond column 80 is expensive

 * in terms of program opens...

 D ODP_TYPE 81 82

 * * ODP Type

 D FILE_NAME 83 92

 * * File name

 D LIBRARY 93 102

 * * Library name

 D SPOOL_FILE 103 112

 * * Spool file name

 D SPOOL_LIB 113 122

ΓΒ©copyright 2

 * * Spool file lib

 D SPOOL_NUM 123 124I 0

 * * Spool file num

 D RCD_LEN 125 126I 0

 * * Max record len

 D KEY_LEN 127 128I 0

 * * Max key len

 D MEMBER 129 138

 * * Member name

 D TYPE 147 148I 0

 * * File type

 D ROWS 152 153I 0

 * * Num PRT/DSP rows

 D COLUMNS 154 155I 0

 * * Num PRT/DSP cols

 D NUM_RCDS 156 159I 0

 * * Num of records

 D ACC_TYPE 160 161

 * * Access type

 D DUP_KEY 162 162

 * * Duplicate key?

 D SRC_FILE 163 163

 * * Source file?

 D VOL_OFF 184 185I 0

 * * Vol label offs

 D BLK_RCDS 186 187I 0

 * * Max rcds in bl

 D OVERFLOW 188 189I 0

 * * Overflow line

 D BLK_INCR 190 191I 0

 * * Blk increment

 D FLAGS1 196 196

 * * Misc flags

 D REQUESTER 197 206

 * * Requester name

 D OPEN_COUNT 207 208I 0

 * * Open count

 D BASED_MBRS 211 212I 0

 * * Num based mbrs

 D FLAGS2 213 213

 * * Misc flags

 D OPEN_ID 214 215

 * * Open identifie

 D RCDFMT_LEN 216 217I 0

 * * Max rcd fmt le

 D CCSID 218 219I 0

 * * Database CCSID

 D FLAGS3 220 220

 * * Misc flags

 D NUM_DEVS 227 228I 0

 * * Num devs defin

 * ----------------------------------------------------------------

 * I/O feedback area 241-366

 * * 241-242 not used

 D WRITE_CNT 243 246I 0

 * * Write count

 D READ_CNT 247 250I 0

 * * Read count

 D WRTRD_CNT 251 254I 0

 * * Write/read count

 D OTHER_CNT 255 258I 0

 * * Other I/O count

 D OPERATION 260 260

 * * Cuurent operatio

 D IO_RCD_FMT 261 270

 * * Rcd format name

 D DEV_CLASS 271 272

 * * Device class

 D IO_PGM_DEV 273 282

 * * Pgm device name

ΓΒ©copyright 3

 D IO_RCD_LEN 283 286I 0

 * * Rcd len of I/O

 * ----------------------------------------------------------------

 * POST area 241-nnn

 * Display

 D PGM_DEV_P 241 250

 * * Program device

 D DEV_DSC_P 251 260

 * * Dev description

 D USER_ID_P 261 270

 * * User ID

 D DEV_CLASS_P 271 271

 * * Device class

 D DEV_TYPE_P 272 277

 * * Device type

 D REQ_DEV_P 278 278

 * * Requester?

 D ACQ_STAT_P 279 279

 * * Acquire status

 D INV_STAT_P 280 280

 * * Invite status

 D DATA_AVAIL_P 281 281

 * * Data available

 D NUM_ROWS_P 282 283I 0

 * * Number of rows

 D NUM_COLS_P 284 285I 0

 * * Number of cols

 D BLINK_P 286 286

 * * Allow blink?

 D LINE_STAT_P 287 287

 * * Online/offline?

 D DSP_LOC_P 288 288

 * * Display location

 D DSP_TYPE_P 289 289

 * * Display type

 D KBD_TYPE_P 290 290

 * * Keyboard type

 D CTL_INFO_P 342 342

 * * Controller info

 D COLOR_DSP_P 343 343

 * * Color capable?

 D GRID_DSP_P 344 344

 * * Grid line dsp?

 * ----------------------------------------------------------------

 * The following fields apply to ISDN.

 D ISDN_LEN_P 385 386I 0

 * * Rmt number len

 D ISDN_TYPE_P 387 388

 * * Rmt number type

 D ISDN_PLAN_P 389 390

 * * Rmt number plan

 D ISDN_NUM_P 391 430

 * * Rmt number

 D ISDN_SLEN_P 435 436I 0

 * * Rmt sub-address

 D ISDN_STYPE_P 437 438

 * * Rmt sub-address

 D ISDN_SNUM_P 439 478

 * * Rmt sub-address

 D ISDN_CON_P 480 480

 * * Connection

 D ISDN_RLEN_P 481 482I 0

 * * Rmt address len

 D ISDN_RNUM_P 483 514

 * * Rmt address

 D ISDN_ELEN_P 519 520

 * * Extension len

 D ISDN_ETYPE_P 521 521

 * * Extension type

ΓΒ©copyright 4

 D ISDN_ENUM_P 522 561

 * * Extension num

 D ISDN_XTYPE_P 566 566

 * * X.25 call type

 * ----------------------------------------------------------------

 * ICF

 D PGM_DEV_P 241 250

 * * Program device

 D DEV_DSC_P 251 260

 * * Dev description

 D USER_ID_P 261 270

 * * User ID

 D DEV_CLASS_P 271 271

 * * Device class

 D DEV_TYPE_P 272 272

 * * Device type

 D REQ_DEV_P 278 278

 * * Requester?

 D ACQ_STAT_P 279 279

 * * Acquire status

 D INV_STAT_P 280 280

 * * Invite status

 D DATA_AVAIL_P 281 281

 * * Data available

 D SES_STAT_P 291 291

 * * Session status

 D SYNC_LVL_P 292 292

 * * Synch level

 D CONV_TYPE_P 293 293

 * * Conversation typ

 D RMT_LOC_P 294 301

 * * Remote location

 D LCL_LU_P 302 309

 * * Local LU name

 D LCL_NETID_P 310 317

 * * Local net ID

 D RMT_LU_P 318 325

 * * Remote LU

 D RMT_NETID_P 326 333

 * * Remote net ID

 D APPC_MODE_P 334 341

 * * APPC Mode

 D LU6_STATE_P 345 345

 * * LU6 conv state

 D LU6_COR_P 346 353

 * * LU6 conv

 * * correlator

 * ----------------------------------------------------------------

 * The following fields apply to ISDN.

 D ISDN_LEN 385 386I 0

 * * Rmt number len

 D ISDN_TYPE 387 388

 * * Rmt number type

 D ISDN_PLAN 389 390

 * * Rmt number plan

 D ISDN_NUM 391 430

 * * Rmt number

 D ISDN_SLEN 435 436I 0

 * * sub-addr len

 D ISDN_STYPE 437 438

 * * sub-addr type

 D ISDN_SNUM 439 478

 * * Rmt sub-address

 D ISDN_CON 480 480

 * * Connection

 D ISDN_RLEN 481 482I 0

 * * Rmt address len

 D ISDN_RNUM 483 514

 * * Rmt address

ΓΒ©copyright 5

 D ISDN_ELEN 519 520

 * * Extension len

 D ISDN_ETYPE 521 521

 * * Extension type

 D ISDN_ENUM 522 561

 * * Extension num

 D ISDN_XTYPE 566 566

 * * X.25 call type

 * ----------------------------------------------------------------

 * The following information available only when program started

 * result of a received program start req. (P_ stands for protected)

 D TRAN_PGM 567 630

 * * Trans pgm name

 D P_LUWIDLN 631 631

 * * LUWID fld len

 D P_LUNAMELN 632 632

 * * LU-NAME len

 D P_LUNAME 633 649

 * * LU-NAME

 D P_LUWIDIN 650 655

 * * LUWID instance

 D P_LUWIDSEQ 656 657I 0

 * * LUWID seq num

 * ----------------------------------------------------------------

 * Below info is available only when a protected conversation

 * is started on a remote system. (U_ stands for unprotected)

 D U_LUWIDLN 658 658

 * * LUWID fld len

 D U_LUNAMELN 659 659

 * * LU-NAME len

 D U_LUNAME 660 676

 * * LU-NAME

 D U_LUWIDIN 677 682

 * * LUWID instance

 D U_LUWIDSEQ 683 684I 0

 * * LUWID seq num

 * ----------------------------------------------------------------

 * Device independent area 367-nnn

 * NOTE that this area is shared with the POST feedback area above!

 * ----------------------------------------------------------------

 * Printer

 D CUR_LINE 367 368I 0

 * * Current line num

 D CUR_PAGE 369 372I 0

 * * Current page cnt

 D PRT_MAJOR 401 402

 * * Major ret code

 D PRT_MINOR 403 404

 * * Minor ret code

 * ----------------------------------------------------------------

 * Disk

 D FDBK_SIZE 367 370I 0

 * * Size of DB fdbk

 D JOIN_BITS 371 374I 0

 * * JFILE bits

 D LOCK_RCDS 377 378I 0

 * * Nbr locked rcds

 D POS_BITS 385 385

 * * File pos bits

 D DLT_BITS 384 384

 * * Rcd deleted bits

 D NUM_KEYS 387 388I 0

 * * Num keys (bin)

 D KEY_LEN 393 394I 0

 * * Key length

 D MBR_NUM 395 396I 0

ΓΒ©copyright 6

 * * Member number

 D DB_RRN 397 400I 0

 * * Relative-rcd-num

 D KEY 401 2400

 * * Key value (max

 * * size 2000)

 * ----------------------------------------------------------------

 * ICF

 D ICF_AID 369 369

 * * AID byte

 D ICF_LEN 372 375I 0

 * * Actual data len

 D ICF_MAJOR 401 402

 * * Major ret code

 D ICF_MINOR 403 404

 * * Minor ret code

 D SNA_SENSE 405 412

 * * SNA sense rc

 D SAFE_IND 413 413

 * * Safe indicator

 D RQSWRT 415 415

 * * Request write

 D RMT_FMT 416 425

 * * Remote rcd fmt

 D ICF_MODE 430 437

 * * Mode name

 * ----------------------------------------------------------------

 * Display

 D DSP_FLAG1 367 368

 * * Display flags

 D DSP_AID 369 369

 * * AID byte

 D CURSOR 370 371

 * * Cursor location

 D DATA_LEN 372 375I 0

 * * Actual data len

 D SF_RRN 376 377I 0

 * * Subfile rrn

 D MIN_RRN 378 379I 0

 * * Subfile min rrn

 D NUM_RCDS 380 381I 0

 * * Subfile num rcds

 D ACT_CURS 382 383

 * * Active window

 * * cursor location

 D DSP_MAJOR 401 402

 * * Major ret code

 D DSP_MINOR 403 404

 * * Minor ret code

ΓΒ©copyright 7

PROGRAM STATUS DATA STRUCTURE (PSDS)

* -------------------------------------------------------------

 * Program Status Data Structure -- PSDS

* -------------------------------------------------------------

 D SDS

 D PROC_NAME *PROC

 * Procedure name

 D PGM_STATUS *STATUS

 * Status code

 D PRV_STATUS 16 20S 0

 * Previous status

 D LINE_NUM 21 28

 * Src list line nu

 D ROUTINE *ROUTINE

 * Routine name

 D PARMS *PARMS

 * Num passed parms

 D EXCP_TYPE 40 42

 * Exception type

 D EXCP_NUM 43 46

 * Exception number

 D PGM_LIB 81 90

 * Program library

 D EXCP_DATA 91 170

 * Exception data

 D EXCP_ID 171 174

 * Exception Id

 D DATE 191 198

 * Date (DATE fmt)

 D YEAR 199 200S 0

 * Year (YEAR fmt)

 D LAST_FILE 201 208

 * Last file used

 D FILE_INFO 209 243

 * File error info

 D JOB_NAME 244 253

 * Job name

 D USER 254 263

 * User name

 D JOB_NUM 264 269S 0

 * Job number

 D JOB_DATE 270 275S 0

 * Date (UDATE fmt)

 D RUN_DATE 276 281S 0

 * Run date (UDATE)

 D RUN_TIME 282 287S 0

 * Run time (UDATE)

 D CRT_DATE 288 293

 * Create date

 D CRT_TIME 294 299

 * Create time

 D CPL_LEVEL 300 303

 * Compiler level

 D SRC_FILE 304 313

 * Source file

 D SRC_LIB 314 323

 * Source file lib

 D SRC_MBR 324 333

 * Source file mbr

 D PROC_PGM 334 343

 * Pgm Proc is in

 D PROC_MOD 344 353

 * Mod Proc is in

 D CURR_USER 358 367

 * Mod Proc is in

 * -------------------------------------------------------------

ΓΒ©copyright 8

 * Values of PGM_STATUS (*STATUS) -- if %Status = 00100 ...

 * -------------------------------------------------------------

 * Normal Codes

 *

 * Code Condition

 * 00000 No exception/error occurred

 * 00001 Called program returned with the LR indicator on.

 * Exception/Error Codes

 * Code Condition

 * 00100 Value out of range for string operation

 * 00101 Negative square root

 * 00102 Divide by zero

 * 00103 An intermediate result is not large enough to

 * contain the result.

 * 00104 Float underflow. An intermediate value is too

 * small to be contained in the intermediate

 * result field

 * 00112 Invalid Date, Time or Timestamp value.

 * 00113 Date overflow or underflow. (For example, when

 * the result of a Date calculation

 * results in a number greater than HIVAL or less

 * than LOVAL.)

 * 00114 Date mapping errors, where a Date is mapped from a 4

 * character year to a 2 character year and the date range

 * is not 1940-2039.

 * 00120 Table or array out of sequence.

 * 00121 Array index not valid

 * 00122 OCCUR outside of range

 * 00123 Reset attempted during initialization step of program

 * 00202 Called program or procedure failed; halt indicator

 * (H1 through H9) not on

 * 00211 Error calling program or procedure

 * 00222 Pointer or parameter error

 * 00231 Called program or procedure returned with halt

 * indicator on

 * 00232 Halt indicator on in this program

 * 00233 Halt indicator on when RETURN operation run

 * 00299 RPG IV formatted dump failed

 * 00333 Error on DSPLY operation

 * 00401 Data area specified on IN/OUT not found

 * 00402 PDA not valid for non-prestart job

 * 00411 Data area type or length does not match

 * 00412 Data area not locked for output

 * 00413 Error on IN/OUT operation

 * 00414 User not authorized to use data area

 * 00415 User not authorized to change data area

 * 00421 Error on UNLOCK operation

 * 00425 Length requested for storage allocation is out of range

 * 00426 Error encountered during storage management operation

 * 00431 Data area previously locked by another program

 * 00432 Data area locked by program in the same process

 * 00450 Character field not entirely enclosed by shift-out

 * and shift-in characters

 * 00501 Failure to retrieve sort sequence.

 * 00502 Failure to convert sort sequence.

 * 00802 Commitment control not active.

 * 00803 Rollback operation failed.

 * 00804 Error occurred on COMMIT operation

 * 00805 Error occurred on ROLBK operation

 * 00907 Decimal data error (digit or sign not valid)

 * 00970 The level number of the compiler used to generate

 * the program does not agree with the level number

 * of the RPG IV run-time subroutines

 * 09998 Internal failure in ILE RPG/400 compiler or in

 * run-time subroutines

 * 09999 Program exception in system routine.

SQL statements in ILE RPG applications

Defining the SQL communication area in ILE RPG applications

The SQL precompiler automatically places the SQL communication area (SQLCA) in the definition specifications of the ILE RPG program prior to the first calculation specification, unless a SET OPTION SQLCA = *NO statement is found.
INCLUDE SQLCA should not be coded in the source program. If the source program specifies INCLUDE SQLCA, the statement will be accepted, but it is redundant.

If a SET OPTION SQLCA = *NO statement is found, the SQL precompiler automatically places SQLCODE and SQLSTATE variables in the definition specification. They are defined as follows when the SQLCA is not included:

D SQLCODE           S           10I 0
D SQLSTATE          S            5A

Defining SQL descriptor areas in ILE RPG applications

• EXECUTE…USING DESCRIPTOR descriptor-name
• FETCH…USING DESCRIPTOR descriptor-name
• OPEN…USING DESCRIPTOR descriptor-name
• CALL…USING DESCRIPTOR descriptor-name
• DESCRIBE statement-name INTO descriptor-name
• DESCRIBE INPUT statement-name INTO descriptor-name
• DESCRIBE TABLE host-variable INTO descriptor-name
• PREPARE statement-name INTO descriptor-name

 Unlike the SQLCA, there can be more than one SQLDA in a program and an SQLDA can have any valid name.

Dynamic SQL is a programming technique. With dynamic SQL, your program can develop and then run SQL statements while the program is running. A SELECT statement with a variable SELECT list (that is, a list of columns to be returned as part of the query) that runs dynamically requires an SQL descriptor area (SQLDA). This is because you cannot know in advance how many or what type of variables to allocate in order to receive the results of the SELECT.
 You can specify an INCLUDE SQLDA statement in an ILE RPG program; however, it is not allowed in free format.

The format of the statement is:
C/EXEC SQL INCLUDE SQLDA
C/END-EXEC

The INCLUDE SQLDA generates the following data structure.
D*      SQL Descriptor area
D SQLDA           DS
D  SQLDAID                1      8A
D  SQLDABC                9     12B 0
D  SQLN                  13     14B 0
D  SQLD                  15     16B 0
D  SQL_VAR                      80A   DIM(SQL_NUM)
D                        17     18B 0
D                        19     20B 0
D                        21     32A
D                        33     48*
D                        49     64*
D                        65     66B 0
D                        67     96A
D*
D SQLVAR          DS
D  SQLTYPE                1      2B 0
D  SQLLEN                 3      4B 0
D  SQLRES                 5     16A
D  SQLDATA               17     32*
D  SQLIND                33     48*
D  SQLNAMELEN            49     50B 0
D  SQLNAME               51     80A
D*  End of SQLDA

Embedding SQL statements in ILE RPG applications

Fixed-form RPG

The keywords EXEC SQL indicate the beginning of an SQL statement. EXEC SQL must occupy positions 8 through 16 of the source statement, preceded by a / in position 7.

The keyword END-EXEC ends the SQL statement. END-EXEC must occupy positions 8 through 16 of the source statement, preceded by a slash (/) in position 7.

An UPDATE statement coded in an ILE RPG program might be coded as follows:
C/EXEC SQL UPDATE DEPARTMENT
C+           SET MANAGER = :MGRNUM
C+           WHERE DEPTNO = :INTDEP
C/END-EXEC

Free-form RPG

Each SQL statement must begin with EXEC SQL and end with a semicolon (;).

Example: An UPDATE statement coded in free form might be coded in the following way:
EXEC SQL UPDATE DEPARTMENT
  SET MGRNO = :MGR_NUM
  WHERE DEPTNO = :INT_DEP;

Comments in ILE RPG applications

In addition to SQL comments (--), ILE RPG comments can be included within SQL statements wherever SQL allows a blank character.

Fixed-form RPG

To embed an ILE RPG comment within the SQL statement, place an asterisk (*) in position 7.

Free-form RPG

Bracketed comments (/*...*/) are allowed within embedded SQL statements between positions 8 through 80 and whenever a blank is allowed, except between the keywords EXEC and SQL. Comments can span any number of lines. Single-line comments (//) can also be used.

Continuation for SQL statements in ILE RPG applications

 SQL statements can be continued across many records in ILE RPG.

Fixed-form RPG

When additional records are needed to contain the SQL statement, positions 9 through 80 can be used. Position 7 must be a plus sign (+), and position 8 must be blank. Position 80 of the continued line is concatenated with position 9 of the continuation line.

In this example, the SQL statement has a valid graphic constant of G'<AABBCCDDEEFFGGHHIIJJKK>'.

C/EXEC SQL    SELECT  *  FROM  GRAPHTAB  WHERE  GRAPHCOL  =   G'<AABBCCDDEE>
C+<FFGGHHIIJJKK>'
C/END-EXEC

Free-form RPG

SQL statements can be contained on one or more lines. To continue an SQL statement across multiple lines, the SQL statement can be split wherever a blank is allowed. The plus sign (+) can be used to indicate a continuation of a string constant. The literal continues with the first nonblank character on the next line.

Names in ILE RPG applications

Any valid ILE RPG variable name can be used for a host variable with these restrictions.

• Do not use host variable names or external entry names that begin with the characters SQ, SQL, RDI, or DSN. These names are reserved for the database manager.
• The length of host variable names is limited to 64.
• The names of host variables must be unique within the program. The one exception is that if a stand-alone field, parameter, or both, are defined exactly the same as another stand-alone field, parameter, or both, the duplicated name is accepted.
• If a host variable is a duplicated name and does not belong to the exceptional category mentioned in the previous item, but does have the same type, the precompiler issues SQL0314 as a severity 11 error instead of its normal severity of 35. If you want to ignore these severity 11 errors, change the GENLVL parameter value on the CRTSQLRPGI command to be 11 or higher.

Statement labels in ILE RPG applications

A TAG statement can precede any SQL statement. Code the TAG statement on the line preceding EXEC SQL.

Using host variables in ILE RPG applications that use SQL

All host variables used in SQL statements must be explicitly declared.

SQL embedded in ILE RPG does not use the SQL BEGIN DECLARE SECTION and END DECLARE SECTION statements to identify host variables. Do not put these statements in the source program.

All host variables within an SQL statement must be preceded by a colon (:).

The names of host variables must be unique within the program, even if the host variables are in different procedures. However, if a data structure has the QUALIFIED keyword, then the subfields of that data structure can have the same name as a subfield in a different data structure or as a stand-alone variable. The subfield of a data structure with the QUALIFIED keyword must be referenced using the data structure name to qualify the subfield name.

An SQL statement that uses a host variable must be within the scope of the statement in which the variable was declared.

If an error stating that a host variable is not defined or not usable is issued, look at the cross-reference in the precompiler listing to see how the precompiler defined the variable. To generate a cross-reference in the listing, run the precompile command with *XREF specified on the OPTIONS parameter.

Declaring host variables in ILE RPG applications that use SQL

The SQL ILE RPG precompiler only recognizes a subset of valid ILE RPG declarations as valid host variable declarations.

Most variables defined in ILE RPG can be used in SQL statements. A partial listing of variables that are not supported includes the following:

• Unsigned integers
• Pointer
• Tables
• UDATE
• UDAY
• UMONTH
• UYEAR
• Look-ahead fields
• Named constants
• Multiple dimension arrays
• Definitions requiring the resolution of %SIZE or %ELEM
• Definitions requiring the resolution of constants unless the constant is used in OCCURS or DIM.

Using host structure arrays in ILE RPG applications that use SQL

A host structure array is defined as an occurrence data structure or a data structure with the keyword DIM coded. Both types of data structures can be used on the SQL FETCH or INSERT statement when processing multiple rows.

The following list of items must be considered when using a data structure with multiple row blocking support.

• All subfields must be valid host variables.
• All subfields must be contiguous. The first FROM position must be 1 and there cannot be overlaps in the TO and FROM positions.
• If the date and time format and separator of date and time subfields within the host structure are not the same as the DATFMT, DATSEP, TIMFMT, and TIMSEP parameters on the CRTSQLRPGI command (or in the SET OPTION statement), then the host structure array is not usable.

For all statements, other than the blocked FETCH and blocked INSERT, if an occurrence data structure is used, the current occurrence is used. For the blocked FETCH and blocked INSERT, the occurrence is set to 1.

The following example uses a host structure array called DEPARTMENT and a blocked FETCH statement to retrieve 10 rows from the DEPARTMENT table.
DDEPARTMENT               DS                  OCCURS(10)
D DEPTNO                 01     03A
D DEPTNM                 04     32A
D MGRNO                  33     38A
D ADMRD                  39     41A

DIND_ARRAY        DS                  OCCURS(10)
D INDS                           4B 0 DIM(4)
…
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR
C+    SELECT *
C+       FROM  CORPDATA.DEPARTMENT
C/END-EXEC
…

C/EXEC SQL
C+   FETCH C1 FOR 10 ROWS
C+     INTO :DEPARTMENT:IND_ARRAY
C/END-EXEC

Blocked FETCH and blocked INSERT are the only SQL statements that allow a data structure with the DIM keyword. A host variable reference with a subscript like MyStructure(index).Mysubfield is not supported by SQL.

Example 
Dfststruct        DS                      DIM(10)              QUALIFIED        
D sub1                           4B 0              
D sub2                           9B 0
D sub3                          20I 0              
D sub4                           9B 0  

C/EXEC SQL                                  
C+   FETCH C1 FOR 10 ROWS INTO :fststruct    

Sample SQLRPGLE program to Read data from db2 using Select and Fetch SQL Statements

d DataDS          ds                                   

d $cmp                           2s 0                  

d $empId                         5s 0                  

d $empName                      30a                    

d $empDate                       8s 0                  

d $empSalary                    11s 2                  

                                                       

 /free                                                 

                                                       

  //Set SQL options                                    

  exec sql                                             

   SET OPTION                                          

       commit=*none,                                   

       datfmt=*iso;                                    

                                                       

  //Read one Record from the SQL table using unique keys

  exec sql                                             

    Select d1cmp,d1id,d1name,d1sdat,d1saly into :DataDS 

    from qgpl/mydata where d1cmp = 1 and d1id = 102;    

                                                       

    if sqlcod = 0;                                     

    dsply $cmp;                                        

    dsply $empId;                                       

    dsply $empName;                             

    dsply $empDate;                             

    dsply $empSalary;                           

    else;                                       

    dsply sqlcod; //do something                

    endif;                                      

                                                

  //Read more than one record from the SQL table

                                                

  //Declare the cursor                          

  exec sql                                      

    Declare C1 cursor for                       

      Select d1cmp,d1id,d1name,d1sdat,d1saly    

      from qgpl/mydata where d1cmp = 1;          

                                                

  //Open the cursor                             

  exec sql                                      

    Open C1;                                    

                                                

  //Fetch data in a loop                        

  Dou sqlcod <> 0;                              

  exec sql                                      

    Fetch C1 INTO :DataDS;                      

                          

    If (sqlcod <> 0);     

      Leave;              

    Endif;                

                          

    dsply $cmp;           

    dsply $empId;         

    dsply $empName;       

    dsply $empDate;       

    dsply $empSalary;     

                          

  Enddo;                  

                          

  //Close the cursor      

  exec sql                

    Close C1;             

                          

  *inlr = *on;            

                          

 /end-free    

Free Form RPG

Overview

All RPG specs have free-form support except I and O specs.

Each free-form statement begins with an operation code and ends with a semicolon. Here is a list of the new operation codes:

• CTL-OPT for control specs (H)
• DCL-F for file specs (F)
• DCL-S, DCL-DS, DCL-SUBF, DCL-C, DCL-PR, DCL-PI, DCL-PARM for data specs (D)
• DCL-PROC for procedure specs (P)

Advantages

• Free-form RPG allows code to be specified as free-form statements rather than in specific fixed columns.
• Free-form code is still restricted to columns 8 – 80.
• The /FREE and /END-FREE compiler directives are tolerated, but are no longer required for free-form.
• Multiple line definitions are easier to code, easier to maintain, and easier to understand.
• Defining data items with long symbol names is no longer problematic.
• Free-form definitions can have /IF, /ELSEIF, /ELSE, and /ENDIF within the statement.

Changes for control specifications (H spec)

This section describes the changes for a control specification (H spec). The free-form keyword is CTL-OPT (Control Option). The free-form control statement starts with CTL-OPT, followed by zero or more keywords, and ends with a semicolon. Allowed keywords are the same keywords as an H spec.

Example Additional control statements

CTL-OPT OPTION(*SRCSTMT:*NODEBUGIO)

        DFTACTGRP(*No);

    // intermixed free-form and fixed-form

    CTL-OPT DATFMT(*ISO);

H TIMFMT(*ISO)

Changes for declaration statements (D spec)

This section describes the changes for declaration statements (D spec). A free-form data definition statement starts with DCL-<x>, and is followed by the data item name (which can be *N if the data item is unnamed), then by an optional data type, and then by zero or more keywords, and finally ends with a semicolon.

Example Fixed-form and free-form keywords

D* fixed-form declarations

D string        S       50A VARYING

D date          S         D DATFMT(*MDY)

D obj           S         O CLASS(*JAVA:'MyClass')

D ptr           S         * PROCPTR

   

    // free-form declarations

    DCL-S string VARCHAR(50);

    DCL-S date DATE(*MDY);

    DCL-S obj OBJECT(*JAVA:'MyClass');

    DCL-S ptr POINTER(*PROC);

Named constants

A named constant declaration starts with DCL-C, and is then followed by the name, then by the optional keyword CONST, and then by the value and finally ends with a semicolon.

Example  Named constants

// without the optional CONST keyword

DCL-C lower_bound -50;

DCL-C max_count 200;

DCL-C start_letter 'A';

// with the optional CONST keyword

DCL-C upper_bound CONST(-50);

DCL-C min_count CONST(200);

DCL-C end_letter CONST('A');

Specifying the CONST keyword makes no difference in the meaning of the declaration.

Stand-alone fields

A stand-alone field declaration starts with DCL-S.

Example  Stand-alone fields

DCL-S first_name CHAR(10) INZ('John');

DCL-S last_name  VARCHAR(20);

DCL-S index PACKED(6);

DCL-S salary PACKED(8:2);

Example  Stand-alone fields with LIKE

// Define using the LIKE keyword

DCL-S cust_index LIKE(index);

//Specify length adjustment with LIKE keyword

DCL-S big_index LIKE(index : +6);

Example  Declarations using named constants

DCL-C name_len CONST(10);

DCL-S one CHAR(name_len);

DCL-S two VARCHAR(name_len);

DCL-C digits 10;

DCL-C positions 3;

DCL-S value PACKED(digits:positions);

Example  Data structures

// Program described data structure

DCL-DS data_str_1;

    emp_name CHAR(10);

    first_name CHAR(10);

    salary PACKED(8:2);

END-DS;

// Program described data structure

DCL-DS data_str_2;

    value VARCHAR(4);

    index INT(10);

END-DS data_str_2;

//Unnamed data structure

DCL-DS *N;

    item VARCHAR(40);

END-DS;

Example Data structure using LIKEREC

DCL-DS custoutput LIKEREC(custrec);

Example Data structure with END-DS

DCL-DS PRT_DS LEN(132) END-DS;

Example Two equivalent data structures (END-DS)

DCL-DS myrecord EXT;

END-DS;

DCL-DS myrecord EXT END-DS;

Example Data structure with the DCL-SUBF keyword

DCL-DS record_one;

    buffer CHAR(25);

    DCL-SUBF read INT(3);

END-DS;

Example Two equivalent data structures (OVERLAY)

D* fixed-form declaration

D myds          DS

D   subf1           11      15A

D   subf2                    5P 2   OVERLAY(myds)

D   subf3                   10A     OVERLAY(myds:100)

D   subf4                   15A     OVERLAY(myds:*NEXT)

   

    //free-form declaration

    DCL-DS myds;

        subf1 CHAR(5) POS(11);

        subf2 PACKED(5:2) POS(1);

        subf3 CHAR(10) POS(100);

        subf4 CHAR(15);

    END-DS;

The OVERLAY(dsname:*NEXT) keyword means the same as no OVERLAY keyword at all.

Procedure prototypes

A procedure prototype declaration starts with DCL-PR, and is followed by a name and then by zero or more keywords, and ends with a semicolon.

Example Cosine procedure prototype

DCL-PR cosine FLOAT(8) EXTPROC('cos');

    angle FLOAT(8) VALUE;

END-PR;

Example GetCurTotal procedure prototype

DCL-PR getCurTotal PACKED(31:3) END-PR;

Example Procedure prototype parameter with the DCL-PARM keyword

DCL-PR proc_one;

    buffer CHAR(25) CONST;

    DCL-PARM read INT(3) VALUE;

END-PR;

Example  Procedure interface without parameters

DCL-PI *N CHAR(10);

END-PI;

Example Procedure interface parameter with the DCL-PARM keyword

DCL-PI proc_one;

    buffer CHAR(25) CONST;

    DCL-PARM read INT(3) VALUE;

END-PI;

Changes for procedure specifications (P spec)

This section describes the changes for a procedure specification (P spec). The free-form procedure statement declaration starts with DCL-PROC, and is followed by a name, then by an optional return type, and then by zero or more keywords, and ends with a semicolon.

Example Procedure statement

DCL-PR SubProc1 VARCHAR(100) DIM(2);

    varchar1    VARCHAR(10) CONST;

    ucs1        UCS2(5) CONST;

    varucs1     VARUCS2(5) CONST;

    graph1      GRAPH(20) CONST;

    vgraph1     VARGRAPH(50) CONST;

    packed1     PACKED(10) CONST;

    binary1     BINDEC(2) CONST;

    uns1        UNS(3) CONST;

    float1      FLOAT(4) CONST;

END-PR;

Changes for file specifications (F spec)

This section describes the changes for a file specification (F spec). The free-form file definition statement starts with DCL-F, and is followed by a file name and then by zero or more keywords, and ends with a semicolon.

Example File definition statement

DCL-F dspf WORKSTN

            EXTDESC('TABF035001');

Example 23. File definition statement (with additional keywords)

DCL-F dspf WORKSTN(*EXT) USAGE(*INPUT:*OUTPUT)

            EXTDESC('TABF035001');