1 / 48

EGL SQL Introduction

EGL SQL Introduction. This Learning Module shows how to understand and use the EGL language abstractions to create simple-to-medium complexity SQL database access logic. Database and File Access. Unit. Sub-Topics:. EGL/SQL Concepts and Facilities SQLRecord type Implicit SQL

mvela
Télécharger la présentation

EGL SQL Introduction

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. EGL SQL Introduction This Learning Module shows how to understand and use the EGL language abstractions to create simple-to-medium complexity SQL database access logic.

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

  3. 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 are a number of workshops in this section, and many discussion points, including: • 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.

  4. Learning DB2 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 DB2 documentation: • DB2 Documentation • SQL Getting Started • SQL Reference Manual • Message (error code) Reference. Cached pdf version of full guide. • DB2 Application Development Guide with example embedded SQL programs. • Triggers in DB2 • Constraints in DB2 • Note: as before, in order for you to get the above links to work, run the PowerPoint in Slide Show mode. • Finally – here’s an EXCELLENT article on EGL database access: • http://www.ibm.com/developerworks/rational/library/07/0424_kenichi-paul-timothy-jim/index.html?S_TACT=105AGX15&S_CMP=LP *** Notes

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

  6. 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. • The SQLRecord type • Records defined as type sqlRecord contain properties that form the basis of SQL generation • EGL Data Access Macro (language abstraction) • There are four key data access keywords that interact with sqlRecord type variables • DBMS Connections • Connections are used in the RBD tooling for development productivity • The SQL Editor Options • More productivity can be obtained with some of the Context Menu options • Explicit SQL Coding • Often production requirements are complex enough to demand lower-level programming • Advanced Custom Coding (#sql, execute and prepare statements) • And sometimes production requirements require native SQL coding • The Data Perspective • There is a Workbench Perspective dedicated to your SQL development and testing • Miscellaneous Topics • Stored Procedures • Handling Nulls • Programmatic paging • SQL run-time performance • EGL Built-in Functions from SQLLib

  7. The EGL SQL Record – Records of type: sqlRecord The screen capture below is of the Orders sqlRecord we’ve been using to-date to access the EGL.Orders table. From the record definition and properties, note the following • Record_name becomes a new EGL Type definition • Type=sqlRecord enables variables of this Record type to perform SQL I/O (database access) • tablenames generates the default SQL FROM clause • Note that the fully qualified “schemaName.tableName” is used in the example • tableNames(plural) because you use this property to generate SQL table joins • keyItems 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 defaultSelectCondition – that allows more flexibility in Where clause coding • The column list  generates the default SQL SELECT, UPDATE/SET, INSERT INTO clauses • Based on the column=“xxx.yyy.zzz” property • isSqlNullable=yes allows NULL values in column variable

  8. EGL get – Implicit Statement (What is Generated)? Assume the following sqlRecord definition  • EGL get statement targeting single record • EGL get statement targeting record array

  9. EGL SQL – Implicit (Default) Data Access Keywords • Based on the EGL sqlRecord type, EGL supports all four SQL DML (Data Manipulation Language) verbs with the following: EGL/SQL keyword SQL statement generated get SELECT add INSERT replace UPDATE delete DELETE Implicit – or default data access statements generate SQL statements completely from the properties of the sqlRecord type they reference. Syntax for using (coding pattern): <EGL/SQL keyword> <EGLVariableOfTypeSQLRecord>; ordersArray Orders[0];//array variable declaration of type sqlRecord orderSingle Orders; //single record variable declaration … get ordersArray;//Implicit select statement replace orderSingle noCursor;//Implicit update statement delete orderSingle noCursor; //Implicit delete statement

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

  11. defaultSelectCondition • There will be times when you want to override the default WHERE clause for your sqlRecords. EGL allows you to do this with the defaultSelectCondition keyword. • defaultSelectCondition is typically used as a replacement for keyitems=***Notes • You code it like an SQL WHERE clause, but without the keyword “WHERE”. • 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 sqlRecord definition with the following: defaultSelectCondition = #sqlCondition{ <where clause without WHERE> } Example: This sqlRecord now yields the following for an EGL get statement

  12. 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 • EGL variables – fields defined as primitive types, (structures) record.fields, or DataItem types • Referenced inside of SQL statements – prefixed by a :colon • Referenced and manipulated outside of SQL statements in standard EGL parlance Use Host Variables in the following: • As the output target of an SQL INTO clause – get statement • Referenced as part of an SQL WHERE expression, in – get, update, delete statements • As part of a SELECT logical or mathematical expression; • Select order_amount * :tax, order_date - :numDays, … • As VALUES – in an add statement • As part of the SET clause: SET column = :host_variable • In dynamic SQL (Prepare) statements (covered later in this unit) Host Variables into clause whereclause

  13. 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 are 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 sqlRecord… ***Notes

  14. 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 are 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 sqlRecord… ***Notes

  15. Workshop – Implicit EGL Statements • Short workshop - If you have not been doing so during the lecture:  From any of the table libraries xxx.acces (CustomerLib, OrdersLib, 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 View the SQL to be generated for all four of the different implicit EGL data access macros in the data access functions in either OrdersLib or CustomerLib– get (against a single record & array), replace, add, delete OPTIONAL – Create a new function by copying/pasting one of the OrdersLib functions. Code the usingKeys clause and View the SQL Statement Ctrl/Shift/V

  16.  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 EGLDerbyR7 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

  17. 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.

  18. EGL Editor (Context Menu) SQL Statement Facilities  As you’ve seen, there are a number of useful productivity features available to assist you with your SQL programming, as Context Menu options – under: SQL Statement  • Add – This option converts implicit SQL code to explicit SQL code and adds it to your program. • 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. • ViewThis 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. • Validate - This option checks to see if the implicit SQL is well-formed and will work syntactically. • Remove - This option removes the explicit SQL and returns you to your original I/O statement. • 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.

  19. 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 • 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 • (Like SQL Statement View) This option pops up a window containing the SQL SELECT statement that would return all information in the current record. • Validate Default Select • 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

  20. 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: • 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) • EGL #sql directive. #sql works with your sqlRecords 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) – can be used to create the initial #sql{. You may then customize SQL statement to meet your requirements. • 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

  21. EGL Explicit SQL Statements – 2 of 2 (Best Practice Steps) Here’s what you would do to create explicit SQL: • 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 • Specify the parameters needed in your SQL (including all host-variables) • Records? Search field(s)? Arrays, return code(s)? Etc. • Code the EGL data access statement get <sqlRecordVar>; add <sqlRecordVar>; replace <sqlRecordVar>; delete <sqlRecordVar>; • Use the Context Menu (or Ctrl/Shift/A) to Add – or if a customized SELECT clause: Add with Into • Customize the SQL • get • Modify Select, From, Where, Order By • replace • Modify Set, Where • delete • Modify Where • add • Modify column/values • If incomplete row…or… • If Inserting result of Sub-select • Validate the SQL against the database (again using the Context Menu)

  22.  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) CustomerID 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: • 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

  23.  Workshop – 1. Specify Batch Program Generation with DB Access From Project Explorer • Open: batchBuildfile.eglbld – using the EGL Build Parts Editor • From the Load DB options using Connection: drop down, select: EGLDerbyR7 • For the j2ee option, select the drop down (far right side of the entry) and make sure j2ee is se to: NO • Save (press Ctrl/S) your changes • Close the batchBuild file.eglbld file • Generate your EGLWeb project

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

  25.  Workshop – 3a. Create a Simple EGL Data Access Function Open CustomerLib.egl and do the following: • Copy GetCustomerListAll. Paste it below the original function in CustomerLib, and Rename the function to: GetCustomerListByState • Add: stateIn char(2) to the parameter list • Use the Context Menu to Add the explicit SQL to get customerArray; • Customize the SQL: • Add the where clause shown here • Validate the SQL: • Right-click inside the statement • Select SQL Statement • Select Validate

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

  27.  Workshop – 3c. Create an SQL Sub-Select Function More (still from within CustomerLib): • Copy GetCustomerListAll. Paste/Rename the copied function: GetBestCustomers • Use the Context Menu to Add the explicit SQL to get customerArray; • Customize the SQL • Add the complex WHERE Clause • Validate the SQL • Right-click inside the statement • Select Validate If SQL isn’t your strength, the Notes section of this slide has the source for this sub-select

  28.  Workshop – 3d. Create a Table Join Record From: Customer.egl • Copy the top (only – not all the fields) portion of the Record statement of the Customer record definition. Paste/Rename the copied record: CustomerOrdersJoin • Make the following changes: • Specify the tablenames= clause as shown below • Remove the keyitems clause • Add end • 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 Ctrl/Shift/R When you are finished save your source code changes to the file: Ctrl/S

  29.  Workshop – 3d. Create a Table Join SQL Function From CustomerLib.egl • Copy GetCustomerListAll. Paste/Rename the copied function: GetCustomerOrders • Use the Context Menu to Add the explicit SQL to get customerOrdersArray; • Customize the SQL • Add the WHERE Clause** • Validate the SQL • Right-click inside the statement • 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.

  30. Workshop – 4. Call the Library Functions from the sqlTest Program Using Content Assist as much as possible – create the following function and variable declarations in sqlTest.egl – the batch program you created in step 2. Try this development approach: • Add the new function (name and end) • Use Content Assist and add the four • CustomerLib.get…. calls inside the • new function. • Code the assignment to stateIn. • Copy/Paste the parameter names as • variable declarations • Use Content Assist to return the record • types (Customer, StatusRec, 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 • Press Ctrl/S to save and validate Variable declarations Library function calls

  31.  Workshop – 5. Generate and Debug – 1 of 2 From within edit on sqlTest.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 sqlTest.egl and select: Debug EGL Program

  32.  Workshop – 5. Generate and Debug – 2 of 2 From within Debug • Step through the code – note that you end up in the CustomerLib functions when invoked  • When you hit the ConditionHandlingLib.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

  33. 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) data access. Next we’ll do the following: • Insert a new record • Delete a single record • Update 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

  34.  Workshop – 6. Customize an Add/Replace statement From CustomerLib.egl: • Copy UpdateCustomer. Paste&Rename the copied function: UpdateCustomerAddress • Use the Context Menu to add the explicit SQL: replace updateRecord noCursor; • Customize the SQL • Remove the columns from the set clause • Except for those shown here Hint – remove extraneous commas • Validate the SQL • Right-click inside the statement • Select SQL Statement • Select Validate

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

  36.  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: • Add the testSQLUpdate() function Note that we’ve collapsed the main(), testSQLGetStatements() and testSQLAdd() function in the screen capture  • The first five statements in testSQLUpdate() initialize values • Then you invoke your new CustomerLib. update function • You blank out testRecord • Re-initialize the record key…and… • Re-read the database …more on the next slide… …Copy/Paste Code in the Notes section…

  37.  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: • Add the testSQLDelete() function • Note that we’ve collapsed many functions so you can read this more easily • You start by deleting testRecord – which should still have the values (including customer_IDvalue) from the previous calls • You blank out testRecord • Re-initialize the record key…and… • Re-read the database • Should return a +100 (not found), if you look at the System Variable: SysVar.sqlData.sqlcode - During Debug • Copy/Paste code in the Notes section

  38.  Workshop – 10a. Generate and Test – 1 of 2 From within edit on sqlTest.egl – change your break points, to reflect the new code you need to test. • Note that you will need to execute through the existing testSQLGetStatements() 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 sqlTest.egl and select: Debug EGL Program

  39. Workshop – 10b. 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: testSQLLAdd, and the other new functions • Check out the status record values • Feel free to Resume as well, when you hit ConditionHandlingLib(as before) When you are finished close your Debug session

  40.  OPTIONAL Workshop – Experiment with defaultSelectCondition From within Customer.egl: • Copy and paste the Customer record generated by the Data Access Application wizard • Rename the new record: CustomerdefaultSelect • Remove the keyitems= clause and replace it with the defaultSelectCondition 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, replaceanddelete statements.

  41. (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: • SysLib.startLog() • SysLib.errorLog() • SQLLib.unloadTable() • SQLLib.loadTable() 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 JavaGen applications Steps Create a new EGL program in the \programs\ directory, named: miscEGLapi.egl

  42.  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: • Syslib.startLog() creates a log file – we’ve chosen to write to the C: drive, but can change the fileSpec if you want • Syslib.errorLog() writes individual messages to the log file • Sqllib.unloadTable() 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. • 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 • Sqllib.loadTable is used to load the new temporary table – from the comma-delimited unload file. • In an upcoming course section you will learn how to use the Data Perspective to see the rows in a database table

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

  44.  System and SQL Library API’s – File ErrorLog Results • When you’ve finished Debugging, open up: C:\errorLog.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.

  45.  System and SQL Library API’s – File Unload File Results • When you’ve finished Debugging, open up: C:\myDataFile.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.loadTable(…) API call

  46.  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 SiteUserConcatRec type sqlRecord {tableNames=[["EGL.SiteUser"]], keyitems=[SITEUSER_ID]} SITEUSER_ID int; ADDRESS1 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 derivedCustRec type SQLRecord { tableNames = [["EGL.CUSTOMER"]] } maxNbr string {column = "MAX(CUSTOMER_ID)"}; maxLname string {column = "MAX(LAST_NAME)"}; End Generates 

  47. 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 <SQLrecordName> with #sql{ select… functionality, basically with carte blanche provided that the “into” <SQLrecordName> column names match: - Spelling wise - Datatype wise From the above note the following: • The SQL statement passed into DB2 references columns NOT defined in the SQLRecord (that is okay, as long as they ARE defined in the DB2 table listed after the FROM clause) • The CATEGORY column had better be an EGL string (and a Char or Varchar on DB2) - because of the SQL DIGITS operator and concatenation • The TOTAL column had better be a Decimal (or at least numeric type)

  48. Topic Summary • Now that you have completed this topic, you should be able to: • List the four different EGL implicit I/O statements • 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” • Create new Library functions of custom SQL • Call these new functions from an EGL batch program • Test/Debug your work Summary

More Related