IBM Software Group EGL SQL Introduction This Learning

® IBM Software Group EGL SQL Introduction This Learning Module shows how to understand use the EGL language abstractions to create simple-to-medium complexity SQL database access logic. © 2006 IBM Corporation

Unit Database and File Access Sub-Topics: § § § EGL/SQL Concepts and Facilities SQLRecord type Implicit SQL Explicit SQLRetrieve Table Joins Data Perspective Custom SQL Creation Stored Procedures SQL Optimization Sequential File Access 2

Data Access With EGL At the beginning of this course you used the default data access functions created by the Data Access wizard to perform simple reads and writes to the database. In this section you will learn how to use the features of EGL and SQL to do more advanced database access functionality. There a number of workshops in this section, and many discussion points, including: 4 4 4 4 § § EGL/SQL concepts and facilities The SQLRecord Implicit … versus Explicit SQL Creating SQL – the Process SQLRetrieve Complex Where clauses The Data Perspective and coding/testing complex SQL statements Table joins SQL Prepare (dynamic SQL) Custom SQL Cursor Processing Stored Procedures SQL Performance and EGL considerations EGL COMMIT Processing Optional SQL Workshops (many) In a topic at the end of this unit you will learn how to read and write sequential files It is assumed that you understand SQL (Structured Query Language) – as teaching SQL along with EGL’s data access features is beyond the scope of this course. 3

Learning DB 2 and SQL § Many (in the thousands of) books exist that do an excellent job teaching SQL. § Additionally, sites exist on the Internet (GOOGLE: “SQL tutorials” – or “Learn SQL”) for online (and typically free) education. § IBM Also supplies excellent SQL and DB 2 documentation: 4 DB 2 Documentation 4 SQL Getting Started 4 SQL Reference Manual 4 Message (error code) Reference. Cached pdf version of full guide. 4 DB 2 Application Development Guide with example embedded SQL programs. 4 Triggers in DB 2 4 Constraints in DB 2 § Note: as before, in order for you to get the above links to work, run the Power. Point in Slide Show mode. § Finally – here’s an EXCELLENT article on EGL database access: 4 http: //www. ibm. com/developerworks/rational/library/07/0424_kenichi-paul-timothyjim/index. html? S_TACT=105 AGX 15&S_CMP=LP 4 *** Notes

Overview of EGL Data Access – SQL Generation EGL generates SQL statements to access your relational database. 4 This generation includes all database connect APIs and a lot of other “plumbing” code 4 EGL’s SQL generation can be to either COBOL or Java 4 There a number of EGL resources (programming elements) that participate in SQL generation EGL Data Access and SQL Generation (Conceptual view) 5

Elements of EGL SQL Programming There are eight elements of EGL/SQL programming which you will use to become proficient and effective using EGL to access your relational data sources. 1. The SQLRecord type 4 Records defined as type sql. Record contain properties that form the basis of SQL generation 2. EGL Data Access Macro (language abstraction) 4 There are four key data access keywords that interact with sql. Record type variables 3. DBMS Connections 4 Connections are used in the RBD tooling for development productivity 4. The SQL Editor Options 4 More productivity can be obtained with some of the Context Menu options 5. Explicit SQL Coding 4 Often production requirements are complex enough to demand lower-level programming 6. Advanced Custom Coding (#sql, execute and prepare statements) 4 And sometimes production requirements require native SQL coding 7. The Data Perspective 4 There is a Workbench Perspective dedicated to your SQL development and testing 8. Miscellaneous Topics 4 4 4 Stored Procedures Handling Nulls Programmatic paging SQL run-time performance EGL Built-in Functions from SQLLib 6

The EGL SQL Record – Records of type: sql. Record The screen capture below is of the Orders sql. Record we’ve been using to-date to access the EGL. Orders table. From the record definition and properties, note the following 4 Record_name becomes a new EGL Type definition § Type=sql. Record enables variables of this Record type to perform SQL I/O (database access) 4 tablenames generates the default SQL FROM clause § Note that the fully qualified “schema. Name. table. Name” is used in the example § table. Names(plural) because you use this property to generate SQL table joins 4 key. Items generates the default: § WHERE clause – if a single record is used as the variable § ORDER BY clause – if a dynamic array is used as the variable § Can optionally create a default. Select. Condition – that allows more flexibility in Where clause coding 4 The column list generates the default SQL SELECT, UPDATE/SET, INSERT INTO clauses § Based on the column=“xxx. yyy. zzz” property § is. Sql. Nullable=yes allows NULL values in column variable 7

EGL get – Implicit Statement (What is Generated)? Assume the following sql. Record definition § EGL get statement targeting single record § EGL get statement targeting record array 8

EGL SQL – Implicit (Default) Data Access Keywords Based on the EGL sql. Record type, EGL supports all four SQL DML (Data Manipulation Language) verbs with the following: EGL/SQL keyword get add replace delete SQL statement generated SELECT INSERT UPDATE DELETE Implicit – or default data access statements generate SQL statements completely from the properties of the sql. Record type they reference. Syntax for using (coding pattern): <EGL/SQL keyword> <EGLVariable. Of. Type. SQLRecord>; orders. Array Orders[0]; //array variable declaration of type sql. Record order. Single Orders; //single record variable declaration … get orders. Array; //Implicit select statement replace order. Single no. Cursor; //Implicit update statement delete order. Single no. Cursor; //Implicit delete statement 9

Get sql. Record using. Keys – Overriding Individual Statements Another way of overriding the default WHERE clause produced by keyitems= in implicit SQL statements is the using. Keys clause. using. Keys allows you to specify one or more search fields from EGL variables that are within scope. If you specify more than one using. Keys variable, the WHERE clause generated will AND the two expressions (see below): Coding syntax: get <sql. Record> using. Keys <EGLvar 1>, Example: 10 <EGLvar 2>;

default. Select. Condition There will be times when you want to override the default WHERE clause for your sql. Records. EGL allows you to do this with the default. Select. Condition keyword. 4 default. Select. Condition is typically used as a replacement for keyitems= ***Notes 4 You code it like an SQL WHERE clause, but without the keyword “WHERE”. WHERE 4 You can code any legal SQL WHERE condition – including complex sub-selects and host variables § However – if you reference Host Variables they must be available to the function (typically they’d be passed in via function parameters) Coding syntax: - Replace keyitems= in your sql. Record definition with the following: default. Select. Condition = #sql. Condition{ <where clause without WHERE> } Example: This sql. Record now yields the following for an EGL get statement 11

EGL Host Variables Your EGL code passes data to and from the DBMS using standard variables. But when they are referenced inside a SQL statement we refer to them as: “Host Variables”. Host variables are 4 EGL variables – fields defined as primitive types, (structures) record. fields, record. fields or Data. Item types 4 Referenced inside of SQL statements – prefixed by a : colon 4 Referenced and manipulated outside of SQL statements in standard EGL parlance Use Host Variables in the following: 4 As the output target of an SQL INTO clause – get statement 4 Referenced as part of an SQL WHERE expression, in – get, update, delete statements 4 As part of a SELECT logical or mathematical expression; § Select order_amount * : tax, order_date - : num. Days, … 4 As VALUES – in an add statement 4 As part of the SET clause: SET column = : host_variable 4 In dynamic SQL (Prepare) statements (covered later in this unit) Host Variables into clause where clause 12

EGL replace – Implicit Statement (What is Generated)? The replace statement produces an SQL UPDATE statement in the generated code. EGL can produce this statement implicitly, based on information and properties in your SQL record variable, or you can embed explicit SQL code in the replace statement using the #sql directive § There a number of useful and important extensions to replace (replace after read, replace “where current of” cursor, etc. - allowing for more efficient database access – for certain code patterns. See the notes section – or the product HELP for additional information on replace. Assume the same sql. Record… ***Notes 13

EGL delete – Implicit Statement (What is Generated)? The delete statement produces an SQL DELETE statement in the generated code. EGL can produce this statement implicitly, based on information and properties in your SQL record variable, or you can embed explicit SQL code in the delete statement using the #sql directive § There a number of useful and important extensions to delete (delete after read, delete “where current of” cursor, etc. - allowing for more efficient database access. See the notes section – or the product HELP for additional information on delete. Assume the same sql. Record… ***Notes 14

Workshop – Implicit EGL Statements Short workshop - If you have not been doing so during the lecture: From any of the table libraries xxx. acces (Customer. Lib, Orders. Lib, etc. ) - Scroll down until you see one of the EGL SQL statements - Position your mouse cursor (click) in between the keyword and record within the statement - Press Ctrl/Shift/V … or… use the Right-click, Context-menu to view the statement produced Ctrl/Shift/V View the SQL to be generated for all four of the different implicit EGL data access macros in the data access functions in either Orders. Lib or Customer. Lib – get (against a single record & array), replace, add, delete OPTIONAL – Create a new function by copying/pasting one of the Orders. Lib functions. Code the using. Keys clause and View the SQL Statement 15

EGL Preferences – SQL Database Connections Several of the upcoming topics depend on your EGL DBMS Connection settings. The SQL Database Connections are available for review and setting from: § § § Windows (menu) Preferences EGL SQL Database Connections For now, you should be able to use the EGLDerby. R 7 connection you specified, when you did the Data Access Application wizard (in a previous unit). Please ensure that your SQL Database Connection is setup in this project, so you can validate, and SQLRetrieve 16

EGL Preferences – SQL Statements Also from Preferences you have the option of re-specifying certain EGL code generation defaults – for your Data Access Application import. You did not change any of these for your labs, but you might, depending on your production application requirements. For example – you might want to change the EGL field names to all lower or upper case, to comply with corporate standards, etc. 17

EGL Editor (Context Menu) SQL Statement Facilities As you’ve seen, there a number of useful productivity features available to assist your SQL programming, as Context Menu options – under: SQL Statement you with 4 Add – This option converts implicit SQL code to explicit SQL code and adds it to your program. 4 Add with Into – Similar to Add only just for EGL get statements Add with Into includes an EGL into clause for the field names in the EGL record variable, allowing you to remove the field names you do not want to update from the set, the Select into, etc. 4 View This option displays the implicit SQL without adding to the code. You can, however, highlight the code in the pop up display and copy it into your copy buffer by pressing Ctrl-C. 4 Validate - This option checks to see if the implicit SQL is well-formed and will work syntactically. 4 Remove - This option removes the explicit SQL and returns you to your original I/O statement. 4 Reset - If you have edited the explicit code that EGL added to your program, this will undo all of your edits and restore the original explicit code. 18

EGL Editor (Context Menu) SQL Record Facilities Right click within the first line of an SQL Record definition and select SQL Record. A second context menu offers you the following choices: § Retrieve SQL 4 If you are in the process of defining the record, this option asks EGL to construct the record definition for you, based on fields in the database table – through the connection defined in your EGL Preferences. Note that you can Retrieve SQL through a partial Record definition (see upcoming lab) § View Default Select 4 (Like SQL Statement View) View This option pops up a window containing the SQL SELECT statement that would return all information in the current record. § Validate Default Select 4 This option compares the information in the SELECT statement to the structure of the referenced SQL database and makes sure that such a query would work properly. Right-Click 19

EGL Explicit SQL Statements – 1 of 2 While the implicit (default) SQL statements are a place to start in learning about SQL database access, a significant percentage of your programming requirements will demand more complex and/or custom written SQL. There are two ways you can handle custom/complex SQL coding with EGL: 1. Embedded SQL – where you hand code an EGL open or execute #sql{…} statement. In this case you are responsible for the full SQL statement syntax and structure (more on this later in this section) 2. EGL #sql directive. #sql works with your sql. Records and is essentially a pass-thru command, that tells the EGL parser: “Wrap what is between #sql{…} – VERBATIM – and pass it to the DBMS”. The Context Menu options Add or Add with Into (or Ctrl/Shift/A) Ctrl/Shift/A – can be used to create the initial #sql{. You may then customize SQL statement to meet your requirements. 4 This process is very efficient, and allows you to quickly create and test your data access logic, leveraging the EGL tooling and language abstractions. Ctrl/Shift/A 20

EGL Explicit SQL Statements – 2 of 2 (Best Practice Steps) Here’s what you would do to create explicit SQL: 1. Create a callable Library function or EGL service § For now, you’ll copy a library function, rename it and use the code to create a callable routine 2. Specify the parameters needed in your SQL (including all host-variables) § Records? Search field(s)? Arrays, return code(s)? Etc. 3. Code the EGL data access statement get <sql. Record. Var>; add <sql. Record. Var>; replace <sql. Record. Var>; delete <sql. Record. Var>; 4. Use the Context Menu (or Ctrl/Shift/A) to Add – or if a customized SELECT clause: Add with Into 5. Customize the SQL 4 get 4 Modify Select, From, Where, Order By 4 replace 4 Modify Set, Where 4 delete 4 Modify Where 4 add 4 Modify column/values 4 If incomplete row…or… 4 If Inserting result of Sub-select 6. Validate the SQL against the database (again using the Context Menu) 21

Workshop – EGL Tooling – Context Menu Using explicit SQL (steps on the previous page) create the following SQL data access statements: § § § Retrieve all customers by a given State Retrieve the max (highest) Customer. ID number in the table Select all customers who have placed more than 2 orders Join the Customer and Orders tables Work with EGL/SQL update statements: § § Replace Add Insert To do this, you will do the following: 1. 2. 3. 4. 5. Specify two options in your EGL build file, to enable batch program database access Create a new, batch, EGL program Create the four SQL data access routines (above) as library functions (following the steps on the previous slide) Create calls to the library functions from your new, EGL program Generate and test your code by running the Debugger Sound like a lot? Maybe we should get going But before we do, we need to stop the server: § From the Servers tab, click the Red stop sign and stop the server ***Notes 22

Workshop – 1. Specify Batch Program Generation with DB Access From Project Explorer § § § Open: batch. Buildfile. eglbld – using the EGL Build Parts Editor From the Load DB options using Connection: drop down, select: EGLDerby. R 7 For the j 2 ee option, select the drop down (far right side of the entry) and make sure j 2 ee is se to: NO § Save (press Ctrl/S) your changes § Close the batch. Build file. eglbld file § Generate your EGLWeb project 23

Workshop – 2. Create a New EGL Program From Project Explorer § Right-click over the EGLSourceprograms directory and specify: New § > Program Name the EGL source file: sql. Test § (un-check) Do not create it as a called program • Delete all of the boiler-plate code – except the following: 24

Workshop – 3 a. Create a Simple EGL Data Access Function Open Customer. Lib. egl and do the following: 1. Copy Get. Customer. List. All Paste it below the original function in Customer. Lib, and Rename the function to: Get. Customer. List. By. State 2. Add: state. In char(2) to the parameter list 3. Use the Context Menu to Add the explicit SQL to get customer. Array; 4. Customize the SQL: 4 Add the where clause shown here 5. Validate the SQL: 1. 2. Right-click inside the statement Select SQL Statement 4 Select Validate 25

Workshop – 3 b. Create an SQL Column Scalar Function Still from inside Customer. Lib: 1. Find and copy Get. Customer Paste it below the original function in Customer. Lib, and Rename the copied function: Get. Customer. Max. ID 1. Use the Context Menu to Add with Into the explicit SQL to: get search. Record; 2. Customize the SQL: 4 into - Leave only the search. Record. customer. ID 4 Create the max(EGL. Customer_ID) SELECT clause 4 Remove the where clause 3. Validate the SQL: 1. 2. Right-click inside the statement Select: SQL Statement 4 Select Validate See notes section and next slide for an alternative to this coding approach 26

Workshop – 3 c. Create an SQL Sub-Select Function More (still from within Customer. Lib): 1. Copy Get. Customer. List. All Paste/Rename the copied function: Get. Best. Customers 1. Use the Context Menu to Add the explicit SQL to get customer. Array; 2. Customize the SQL 4 Add the complex WHERE Clause 3. Validate the SQL 1. Right-click inside the statement 2. Select Validate If SQL isn’t your strength, the Notes section of this slide has the source for this sub-select 27

Workshop – 3 d. Create a Table Join Record From: Customer. egl 1. Copy the top (only – not all the fields) portion of the Record statement of the Customer record definition. Paste/Rename the copied record: Customer. Orders. Join 2. Make the following changes: 4 Specify the tablenames= clause as shown below 4 Remove the keyitems clause 4 Add end Ctrl/Shift/R 4 Click your mouse inside the statement, and use the Context Menu/SQL Record to Retrieve SQL. After, you can use the Context Menu/SQL Record to view Default Select When you are finished save your source code changes to the file: Ctrl/S 28

Workshop – 3 d. Create a Table Join SQL Function From Customer. Lib. egl 1. Copy Get. Customer. List. All Paste/Rename the copied function: Get. Customer. Orders 1. Use the Context Menu to Add the explicit SQL to get customer. Orders. Array; 2. Customize the SQL 4 Add the WHERE Clause** 3. Validate the SQL 1. Right-click inside the statement 2. Select Validate ** This WHERE clause is known as a “Join Condition” because it logically joins the two tables correctly based on matching primary key/foreign key values. See the Notes for more on this topic. 29

Workshop – 4. Call the Library Functions from the sql. Test Program Using Content Assist as much as possible – create the following function and variable declarations in sql. Test. egl – the batch program you created in step 2. Try this development approach: • Add the new function (name and end) end • Use Content Assist and add the four Customer. Lib. get…. calls inside the new function. Variable declarations • Code the assignment to state. In • Copy/Paste the parameter names as variable declarations • Use Content Assist to return the record types (Customer, Status. Rec etc. ) • Code the statement in main() that invokes your new function. • Be sure your variable declarations specify arrays and single records where appropriate for their respective library calls Library function calls • Press Ctrl/S to save and validate 30

Workshop – 5. Generate and Debug – 1 of 2 From within edit on sql. Test. egl – set some break points From Project Explorer § Right-click over the EGLWeb project (top icon) and Generate all of your modified source § Right-click over sql. Test. egl and select: Debug EGL Program 31

Workshop – 5. Generate and Debug – 2 of 2 From within Debug § § Step through the code – note that you end up in the Customer. Lib functions when invoked When you hit the Condition. Handling. Lib. egl file, press the Resume icon – to go directly to your next break point. Note the SQL data access operations, data and records returned from the database, etc. When you are finished close your Debug session 32

Workshop – CHECKPOINT At this point, you have used the EGL data access keywords and tooling to: § § § Create four new SQL data access functions And test them But these have been all read-only (get) get data access. Next we’ll do the following: § § Insert a new record Delete a single record Update a set of records § Note that deleting a set of records is like updating a set of records § For this we will: § § § Use the default Add/Insert and Delete statements generated by the Data Access Application wizard Customize a Replace/Update statement View a number of the other options for Delete and Update 33

Workshop – 6. Customize an Add/Replace statement From Customer. Lib. egl: 1. Copy Update. Customer Paste&Rename the copied function: Update. Customer. Address 2. Use the Context Menu to add the explicit SQL: replace update. Record no. Cursor; 3. Customize the SQL 4 Remove the columns from the set clause 4 Except for those shown here Hint – remove extraneous commas 4. Validate the SQL 1. 2. Right-click inside the statement Select SQL Statement 4 Select Validate 34

Workshop – 7. EGL/SQL Add Record Logic Using Content Assist as much as possible – create the following function and variable declarations in sql. Test. egl – the batch program you created in step 2. Steps: 4 Add the test. Record variable (you will use this to retrieve add/change/delete rows from the database 4 In main() add three new statements to call three new functions 4 Note that we’ve collapsed the test. SQLGet. Statements function in the screen capture (just to save room) 4 Use Content Assist and build this test. SQLAdd() function …more on the next slide… …Copy/Paste code in the Notes Section… 35

Workshop – 8. EGL/SQL Update Record Logic Using Content Assist as much as possible – create the table update function – which changes the address of the new customer record added to the database: Steps: 4 Add the test. SQLUpdate() function Note that we’ve collapsed the main(), test. SQLGet. Statements() and test. SQLAdd() function in the screen capture 4 The first five statements in test. SQLUpdate() initialize values 4 Then you invoke your new Customer. Lib. update function 4 You blank out test. Record 4 Re-initialize the record key…and… 4 Re-read the database …more on the next slide… …Copy/Paste Code in the Notes section… 36

Workshop – 9. EGL/SQL Delete Record Logic Using Content Assist as much as possible – create the table row delete function – which deletes the new record added to the database: Steps: 4 Add the test. SQLDelete() function 4 Note that we’ve collapsed many functions so you can read this more easily 4 You start by deleting test. Record – which should still have the values (including customer_ID value) from the previous calls 4 You blank out test. Record 4 Re-initialize the record key…and… 4 Re-read the database § Should return a +100 (not found), if you look at the System Variable: Sys. Var. sql. Data. sqlcode - During Debug § Copy/Paste code in the Notes section 37

Workshop – 10 a. Generate and Test – 1 of 2 From within edit on sql. Test. egl – change your break points, to reflect the new code you need to test. § Note that you will need to execute through the existing test. SQLGet. Statements() function in order to set up your Add/Replace/Delete function calls. From Project Explorer § § Right-click over the EGLWeb project (top icon) and: Generate all of your modified source Right-click over sql. Test. egl and select: Debug EGL Program 38

Workshop – 10 b. Generate and Test – 2 of 2 From within Debug § Step through the code – examine the variables at the program and function level § From the Variables tab – expand/contract: test. SQLLAdd, and the other new functions § § Check out the status record values Feel free to Resume as well, when you hit Condition. Handling. Lib (as before) When you are finished close your Debug session 39

OPTIONAL Workshop – Experiment with default. Select. Condition From within Customer. egl: § § § Copy and paste the Customer record generated by the Data Access Application wizard Rename the new record: Customerdefault. Select Remove the keyitems= clause and replace it with the default. Select. Condition shown below: Save (Ctrl/S) remove any syntax errors Right-click over the record, and select: § SQL Record § View Default Select – to see the where clause that you will be generated when you reference this record in your EGL get, replace and delete statements. 40

(OPTIONAL Topic) Miscellaneous System and SQL Library API’s § There are many useful EGL API’s available to you. Let’s explore a few of them – specifically: 4 Sys. Lib. start. Log() 4 Sys. Lib. error. Log() 4 SQLLib. unload. Table() 4 SQLLib. load. Table() From the product Help, have a look at what each of these APIs do before starting this optional lab. Note also that these functions are only available for Java. Gen applications Steps Create a new EGL program in the programs directory, named: misc. EGLapi. egl 41

System and SQL Library API’s – EGL Program Code Note the format and syntax of the API calls § Replace the boiler plate code with the code in the notes and note the following: 4 Syslib. start. Log() creates a log file – we’ve chosen to write to the C: drive, but can change the file. Spec if you want 4 Syslib. error. Log() writes individual messages to the log file 4 Sqllib. unload. Table() is used to unload a table into a file, value-delimited by a string of our choosing, and using a native SQL Select/From/Where statement – which gives you control over the output produced. 4 We then drop and create a new temporary table (based on EGL. Customer) with an execute #sql statement, which you will learn about in an upcoming section. Note that we drop the table in order to run this program more than once 4 Sqllib. load. Table is used to load the new temporary table – from the commadelimited unload file. 4 In an upcoming course section you will learn how to use the Data Perspective to see the rows in a database table 42

System and SQL Library API’s – Debug § Make sure to add a breakpoint somewhere in the code. (From Project Explorer) Right-click over misc. EGLapi. egl and select Debug EGL Program 43

System and SQL Library API’s – File Error. Log Results § When you’ve finished Debugging, open up: C: error. Log. txt § It is useful to point out, that if you had an EGL system error (like an SQL Exception) that error message would automatically be written to this file § You can simulate this by changing the program source – and do something like modifying the execute #sql to drop a table that does not exist. 44

System and SQL Library API’s – File Unload File Results § When you’ve finished Debugging, open up: C: my. Data. File. txt § Note that this comma-delimited file could be imported into an Excel Spreadsheet (and vice-versa, you could use a comma-delimited spreadsheet as input into a SQLLIB. load. Table(…) API call 45

Not A Workshop – Alternative Approach to Derived Data Use Case You can create a custom SQL record, with column= properties that matches the specific SELECT clause you need, with the SQL elements in the column= clause. Examples: Record Site. User. Concat. Rec type sql. Record {table. Names=[["EGL. Site. User"]], keyitems=[SITEUSER_ID]} SITEUSER_ID int; ADDRESS 1 string; CITYZIP char(44) { column="CITY || ', ' || POSTALCODE"}; FIRSTMIDDLELAST char(88) { column="FIRSTNAME || ', ' || MIDINIT', ' || LASTNAME"}; AGE int; OCCUPATION string; //Note your current table does not have this column end Generates Record derived. Cust. Rec type SQLRecord { table. Names = [["EGL. CUSTOMER"]] } max. Nbr string {column = "MAX(CUSTOMER_ID)"}; max. Lname string {column = "MAX(LAST_NAME)"}; End Generates 46

Not A Workshop – Another Example of Deriving Data Here’s another example of extending the #sql{ operator - (created by Joe Pluta, international authority on System I technology and EGL) Assume the following SQLRecord definition: You can extend the get <SQLrecord. Name> with #sql{ select… functionality, basically with carte blanche provided that the “into” <SQLrecord. Name> column names match: - Spelling wise - Datatype wise From the above note the following: § § § The SQL statement passed into DB 2 references columns NOT defined in the SQLRecord (that is okay, as long as they ARE defined in the DB 2 table listed after the FROM clause) The CATEGORY column had better be an EGL string (and a Char or Varchar on DB 2) - because of the SQL DIGITS operator and concatenation The TOTAL column had better be a Decimal (or at least numeric type) 47

Topic Summary § Now that you have completed this topic, you should be able to: 4 List the four different EGL implicit I/O statements 4 Describe the RBD tooling that allows you to § View the SQL to be created § Add the SQL in to your statements as “explicit SQL” § Create new SQL records “on the fly” 4 Create new Library functions of custom SQL 4 Call these new functions from an EGL batch program 4 Test/Debug your work Summary 48