6D ILESQL Bob Minor

ILE/SQL in 2EBob Minor: 

ILE/SQL in 2E Bob Minor

RPG IV: 

RPG IV

Overview – RPG IV: 

Overview – RPG IV Longer field names Source level debugging Additional data types: floating point, boolean, integer, date, time, timestamps, pointers

F spec: 

F spec The F spec also has a keywords syntax at the end of the line. *.. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 10 FFilename++IPEASFRlen+LKlen+AIDevice+.Keywords+++++++++++++++++++++++++++++Comments+++++++++++++

New D Specs: 

New D Specs D specs exist to declare variable for work use (not for fields in files) *.. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 10 DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++++++Comments++++++++++++

C specs: 

C specs Two new C-spec syntaxes *.. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 10 CL0N01Factor1+++++++Opcode(E)+Factor2+++++++Result++++++++Len++D+HiLoEq....Comments+++++++++++++ *.. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 10 CL0N01Factor1+++++++Opcode(E)+Extended-factor2+++++++++++++++++++++++++++++Comments+++++++++++++ Factor 1, Factor 2, and Result form: Factor 1 and Extended Factor 2 form:

Built-in Functions: 

Built-in Functions Useful in Extended-factor-2 form or in free-format mode Many functions are available; examples:

Free-form syntax: 

Free-form syntax Allows writing code in a 'modern' style that is not sensitive to whitespace or character positioning Enclose free-format code between the directives No specification type is used /FREE /END-FREE

Free-form code: 

Free-form code Each statement has the syntax: Result, Factor 1, and Factor 2 can be omitted if the opcode allows it Statement ends with semicolon; automatically continues to next line OpCode [Factor 1] [Factor 2] [Result] ;

Free-form: 

Free-form The opcodes EVAL and CALLP may be omitted in free-form mode x = y + z; SubFunc1(x);

RPG IV Syntaxes: 

RPG IV Syntaxes Comparison of different styles of syntax available in RPG IV Traditional long-form ADD operation code: C Cost Add MarkUp Price

RPG IV Syntaxes: 

RPG IV Syntaxes Traditional short-form ADD operation code (no Factor 1 used): C Z-ADD Cost Price C Add MarkUp Price

RPG IV Syntaxes: 

RPG IV Syntaxes Contemporary EVAL operation code in fixed-format: C Eval Price = Cost + MarkUp

RPG IV Syntaxes: 

RPG IV Syntaxes Contemporary EVAL operation code in free-format: /FREE Eval Price = Cost + MarkUp; /END-FREE Spacing is not critical

RPG IV Syntaxes: 

RPG IV Syntaxes Contemporary EVAL operation code in free-format (omitting operation code) /FREE Price = Cost + MarkUp; /END-FREE The EVAL opcode is implied

RPG IV Pointers: 

RPG IV Pointers Pointers are useful for dealing with dynamically-allocated memory and API functions The data type code for a pointer is ‘*’ D Var1 S 12A D Ptr S * Var1 Ptr

Pointers – BASED keyword: 

Pointers – BASED keyword The BASED keyword uses a pointer to declare an alias In this example, Var2 has no storage of its own. Instead, it is simply an alias (name) for the data pointed to by Ptr. D Var1 S 12A D Ptr S * D Var2 S 12A based(Ptr) Var1 Ptr Var2

Pointers - Aliasing: 

Pointers - Aliasing By setting the pointer to the address of Var1, Var2 now aliases Var1. Any change to one will affect the other, as they share the same storage space. D Var1 S 12A D Ptr S * D Var2 S 12A based(Ptr) C eval Ptr = %addr(Var1) Var1 Ptr Var2

Pointers – Memory allocation: 

Pointers – Memory allocation In this example, Var2 is allocated space of its own using the %alloc built-in function. The memory allocator ensures that no other variables shares its storage space. D Var1 S 12A D Ptr S * D Var2 S 12A based(Ptr) C eval Ptr = %alloc(12) Var1 Ptr Var2

Pointers - Arithmetic: 

Pointers - Arithmetic Pointer addition/subtraction may be performed The pointer is moved by the specified number of bytes D Ptr S * D Var2 S 12A based(Ptr) C eval Ptr = %alloc(12) C eval Var2 = ‘HELLO’ C eval Ptr = Ptr + 1 Ptr Var2 ‘H’ ‘E’ ‘L’ ‘L’ ‘O’ Original value of ptr New value of ptr Var2 Var2 advances at the same time

Slide21: 

Changing HLL generation language to RPG IV – Before

Slide22: 

Changing HLL generation language to RPG IV – After

Slide23: 

ILE

What is ILE?: 

What is ILE? Integrated Language Environment New way of creating and running programs The traditional environment has been named OPM (Original Program Model)

Why ILE?: 

Why ILE? Support for multiple languages working together (C, RPG, Cobol, CL, etc.) within a single program Support for modularization (code sharing) via service programs

OPM vs ILE: 

OPM vs ILE ILE introduces a compile/bind program creation process as opposed to the traditional compile-only process Source member *PGM CRTxxxPGM OPM Source member *PGM CRTxxxMOD ILE *MODULE CRTPGM

ILE program creation: 

ILE program creation Multiple source members can bind together to form a single program Source member *PGM CRTxxxMOD *MODULE CRTPGM Source member CRTxxxMOD *MODULE

ILE program creation: 

ILE program creation Shortcuts for RPG and CL programs that only use one module for the program CRTBNDRPG – Create Bound RPG Program CRTBNDCL – Create Bound CL Program Similar to CRTRPGPGM/CRTCLPGM

ILE – Activation groups: 

ILE – Activation groups An activation group is a group of running programs in a job sharing certain state: files, resources, exception handling Choices for activation group Default activation group DFTACTGRP(*YES) Named activation group Caller’s activation group ACTGRP(*CALLER) New activation group ACTGRP(*NEW)

ILE Service Program (*SRVPGM): 

ILE Service Program (*SRVPGM) A set of modules which are bound together, meant to be used from another program Source member *SRVPGM CRTxxxMOD *MODULE CRTSRVPGM Source member CRTxxxMOD *MODULE

ILE Service Program: 

ILE Service Program A service program cannot run alone (CALL); can only be used by binding it to another program Source member *PGM CRTxxxMOD *MODULE CRTPGM Source member CRTxxxMOD *MODULE *SRVPGM

Service programs: 

Service programs Useful to collect common subprocedures into a reusable library Binding to service program is faster than recompiling duplicate code, and saves space More flexible than using inter-program CALL

Binding directory: 

Binding directory Object type *BNDDIR Stores a list of service programs Avoids having to list every single service program used in the CRTPGM command ILE APIs often include binding directories

CALL operation: 

CALL operation Same meaning as in classic RPG Calls an external program, possibly with parameters The argument is a name of a program object (*PGM) The name is resolved at run-time with respect to the library list

CALL Example: 

CALL Example Calling a program named ‘YPGM01CL’, passing some parameters C CALL 'YPGM01CL' C PARM OPTION C PARM RTNCODE

CALLB operation: 

CALLB operation Call Bound Procedure Calls a procedure by name The procedure must be defined in a module or service program bound to the caller’s *PGM object The procedure is identified at bind time

CALLB example: 

CALLB example Calling a procedure named ‘YPRC01CL’, passing some parameters C CALLB 'YPRC01CL' C PARM OPTION C PARM RTNCODE

CALLP operation: 

CALLP operation Call Prototyped Procedure Calls a procedure by name, similar to CALLB The procedure must have a prototype declared Type-checking is done at compile-time The procedure is identified at bind time The most modern, safest method

CALLP example: 

CALLP example Declaration of procedure Call of procedure C CALLP YPRC01CL (OPTION : RTNCODE) D YPRC01CL PR EXTPROC('YPRC01CL') D Option 1A D ReturnCode 5P 0

2E and ILE: 

2E and ILE

Setting 2E to generate ILE *MODULE: 

Setting 2E to generate ILE *MODULE EDIT FUNCTION DETAILS EJL Function name . . : Select horse Type : Select record Received by file. : horse Acpth: SQL Retrieval index Workstation . . . : NPT Source library. . : EJSGEN Object Source Target ? Type Name HLL Text T PGM UUAFSRR RP4 Select horse Select record DSP UUAFSRRD DDS Select horse Select record HLP UUAFSRRH UIM Select horse Select record SEL: E-STRSEU, O-Compiler Overrides, T-ILE Compilation Type (*PGM/*MODULE) F3=Exit F5=Action diagram F7=Options F8=Change name F9=Device design F20=Narrative A 2E function can be toggled to generate an ILE module rather than a program.

Setting 2E to generate ILE *MODULE: 

Setting 2E to generate ILE *MODULE EDIT FUNCTION DETAILS EJL Function name . . : Select horse Type : Select record Received by file. : horse Acpth: SQL Retrieval index Workstation . . . : NPT Source library. . : EJSGEN Object Source Target ? Type Name HLL Text MOD UUAFSRR RP4 Select horse Select record DSP UUAFSRRD DDS Select horse Select record HLP UUAFSRRH UIM Select horse Select record SEL: E-STRSEU, O-Compiler Overrides, T-ILE Compilation Type (*PGM/*MODULE) F3=Exit F5=Action diagram F7=Options F8=Change name F9=Device design F20=Narrative This function will now be compiled as a module. Like any ILE module, it must be bound into a program in order to be used.

Using a Binding directory: 

Using a Binding directory Specify a binding directory for the YBNDDIR model value The binding directory does not have to already exist; it will be created Modules will be automatically added to the binding directory Modules must be compiled before the programs that call them

SQL: 

SQL

Tables: 

Tables An SQL table is very similar to a physical file (*PF) Columns (fields) are part of the definition of a table Differences: Tables do not have an inherent order Tables may only have one member

Views: 

Views Essentially a 'saved query' that can be used like a table Can contain: Functions and expressions WHERE clauses (select/omit) Joins to multiple tables Sorting Grouping/aggregate functions Views are not necessary to perform any given operation; views are an organizational convenience

Access path-independence: 

Access path-independence SQL is designed to be access path-independent Indexes are not part of a query Views may be used in a query (but are entirely optional) as a convenience The DBMS automatically selects the appropriate indexes and formulates a (hopefully close to) optimal query plan

Indexes: 

Indexes An index is created manually by the DBA, but is used automatically (as appropriate) by the DBMS to execute users’ queries Too few indexes created: Queries require full-table scans often: performance impact on tables with lots of rows Too many indexes created: Indexes must be kept up to date: insert/update/delete performance is impacted.

Collections: 

Collections Special kind of i5/OS library Must be created using the CREATE COLLECTION or CREATE SCHEMA SQL commands Otherwise, can be used like an ordinary library; can contain programs, source code, etc.

SQL Syntax: 

SQL Syntax Basic syntax SELECT CustomerID FROM Customer WHERE CustomerName LIKE ‘Bill%’ SQL Keywords Identifiers Literal string constant (in single quotes)

SQL Syntax: 

SQL Syntax Identifiers may be escaped with double quotes Allows entering spaces, punctuaion as part of an object name SELECT 'Field Name' …

SQL Syntax: 

SQL Syntax Select a certain set of columns from a table Select all columns from a table SELECT (column), (column) FROM (table) SELECT * FROM (table)

SQL Syntax: 

SQL Syntax WHERE clause: select or omit records from the final result set ORDER BY clause: sort the final result set SELECT … WHERE (expr) SELECT … WHERE … ORDER BY (expr)

Performance tips for DB2/400: 

Performance tips for DB2/400 Avoid using SELECT *, unless all columns are needed Generally better to list columns out If an operation only needs a read-only result set, use the following syntax: SELECT … FOR FETCH ONLY

Performance tips for DB2/400: 

Performance tips for DB2/400 If only a certain number of rows are needed, use the following syntax: Where n is the maximum expected number of rows that will be fetched More than n rows may be fetched with a possible performance penalty SELECT … OPTIMIZE FOR n ROWS

ILE and SQL: 

ILE and SQL

Embedded SQL in RPG: 

Embedded SQL in RPG It is possible to embed SQL statements in RPG source code – type SQLRPGLE The RPG source code must be compiled with a special SQL preprocessor (CRTSQLRPGI) The preprocessor converts the embedded SQL into API calls – you never have to use the API directly

Embedded SQL in RPG: 

Embedded SQL in RPG SQL can appear between /EXEC SQL and /END-EXEC directives The directives must be part of a C spec, and all SQL lines must have 'C' in the leftmost position C/exec sql C+ CREATE TABLE MyTable ( id int, name varchar(20) ) C/end-exec

Embedded SQL in RPG - Cursors: 

Cursors are a convenient way to retrieve multiple rows from a query into a host program Embedded SQL in RPG - Cursors DECLARE MyCursor Cursor FOR SELECT id, name FROM MyTable OPEN MyCursor FETCH NEXT FROM MyCursor INTO :myrec CLOSE MyCursor

Embedded SQL in RPG - Cursors: 

Embedded SQL in RPG - Cursors A closer look In order to use the FETCH statement to fetch a record, the record must be declared as a data structure (DS) D myrec DS D id 10A VARYING D name 20A VARYING FETCH NEXT FROM MyCursor INTO :myrec

SQL Communications Area: 

SQL Communications Area The SQL precompiler inserts a declaration of a data structure known as the SQL Communications Area (SQLCA) Contains fields about the current state of SQL commands Commonly used SQLCA fields: SQLSTT : SQL State SQLCOD : SQL Code

2E ILE and SQL: 

2E ILE and SQL

SQL Model Values: 

SQL Model Values YDSPMDLVAL-*GENACP Op: EAJ EAJ1 12/06/05 9:53:27 ACCESS PATH GENERATION VALUES EJL Model library. . . . . . . : : EJSQL Database generation options YDBFGEN Database implementation . . . : *SQL YDBFACC Data access method . . . . . . : *DBFGEN YFRFVNM Field reference file . . . . . : *NONE YFRFPFX Field ref. prefix . . . . . . : $$ YFRFTXT Field reference file text . . : EJL - field reference file YOLDDBS Use old DBF names . . . . . . : *YES YSQLVNM SQL naming . . . . . . . . . . : *DDS YSQLLEN SQL naming length . . . . . . : 25 Press Enter to continue. More... F3=Exit F16=Top menu

SQL Model Values: 

SQL Model Values Possible values: *SQL *DDS Set to *SQL to generate SQL YDBFGEN Database implementation . . . : *SQL

SQL Model Values: 

SQL Model Values Possible values: *DBFGEN: Access data via views *TABLE: Access data directly via tables Must use *DBFGEN to access virtuals However, *DBFGEN has bugs when accessing date/time/timestamp fields YDBFACC Data access method . . . . . . : *DBFGEN

Slide66: 

Navigating to the Access Path Details screen

Slide67: 

Overriding access path method to use Table direct access Before

Slide68: 

Overriding access path method to use Table direct access After

SQL Model Values: 

SQL Model Values Possible values: *DDS: Use DDS generated names as names of SQL objects *SQL: Attempt to use names of model objects as names for SQL objects (SQL extended naming) However, SQL extended naming has bugs in the 2E generated code. DDS naming is still used in places YSQLVNM SQL naming . . . . . . . . . . : *DDS

Slide70: 

Effect of generating SQL: Access path with virtuals CREATE VIEW EJSCOL.UUAFREL1 ( AFAFCD ,AFAGTX ,AFAEST ,AFABVA ,AFACDZ ,AFAJCD ,AFASTX ,AFALDZ ,AFAKCD ,AFATTX ,AFAMDZ ) AS SELECT X1.AFAFCD X1.AFAFCD , X1.AFAGTX , X1.AFAEST , X1.AFABVA , X1.AFACDZ , X1.AFAJCD , X2.AFAGTX , X2.AFACDZ , X1.AFAKCD , X3.AFAGTX , X3.AFACDZ FROM EJSCOL.UUAFREP X1 ,EJSCOL.UUAFREP X2 ,EJSCOL.UUAFREP X3 WHERE X1.AFAJCD = X2.AFAFCD AND X1.AFAKCD = X3.AFAFCD

Slide71: 

Effect of generating SQL: Access path with virtuals A closer look FROM EJSCOL.UUAFREP X1 ,EJSCOL.UUAFREP X2 ,EJSCOL.UUAFREP X3 WHERE X1.AFAJCD = X2.AFAFCD AND X1.AFAKCD = X3.AFAFCD Inner join is used to access virtual fields

Slide72: 

Effect of generating SQL: Access path with virtuals Index creation CREATE UNIQUE INDEX EJSCOL.UUAFREL1I ON EJSCOL.UUAFREP (AFAFCD ASC ) Index is created by default, unless overridden in Edit Access Path Auxiliaries screen

Slide73: 

Navigating to the Edit Access Path Auxiliaries screen

Slide74: 

Preventing the creation of SQL index for access path Before

Slide75: 

Preventing the creation of SQL index for access path After

Slide76: 

Effect of generating SQL: RPG Input files DDS: Uses F-Specs in IF mode SQL: Uses externally-typed structures

Slide77: 

Effect of generating SQL: RPG Key setup DDS: Uses KLIST’s and SETLL SQL: Uses SQL cursors

Slide78: 

Effect of generating SQL: RPG Read DDS: RPG READ SQL: Uses SQL fetch

Slide79: 

Effect of generating SQL: RPG Chain DDS: Uses CHAIN to read related record SQL: Uses single direct SELECT (no cursor, no join)

Slide80: 

Effect of generating SQL: RPG Miscellaneous: Error handling, cursor close on exit

Slide81: 

Slide82: 

Slide83: 

ADC Austin 14121 W. Hwy 290, Bldg. 4 Austin, Texas 78734 512-858-7622 www.adcaustin.com