IBM Software Group EGL SQL Advanced Development and

® IBM Software Group EGL SQL – Advanced Development and Coding Concepts This Learning Module shows how to understand use the EGL language abstractions to create advanced SQL database access, including custom cursors, prepared statements and calling stored procedures. © 2006 IBM Corporation

EGL Custom SQL Coding The implicit and explicit SQL shown in the previous examples were simple and powerful ways of going about coding data access requests, and should be considered “best practice” solutions to requirements – whenever possible, as they increase development productivity and minimize maintenance risk and time. However, there will be many business requests with complex logical requirements that will mandate a lower-level or custom solution to data access. EGL supports this with the following constructs: 4 Execute 4 Open 4 Prepare These keywords are actually used somewhat interchangeably in data access routines, and are best learned through example. 2

EGL – Execute Keyword The EGL execute verb allows you a custom coding way to write: 4 Non-DML statements: § § Create Table Drop Table 4 DML statements that update your database: § § § Insert Update Delete With execute you: 4 Have total control over the SQL code – so this is a statement for SQL-experts to use 4 Do not have to reference sql. Records – so this makes the database access: § § § More independent of the table import and schema More time-consuming to write and test The format of execute is: execute #sql{ … your native SQL statement … } Notes: 4 Any valid SQL statement (except for SELECT) 4 Must surround the statement with curly braces 4 #sql{ is all four characters together (no spaces) 3

EGL – Execute Keyword – Examples Non-DML Examples SQL DML Examples (no sql. Record) 4

Workshops for EGL execute statement You will do the following labs, to help you understand the user of the EGL execute SQL I/O statement: 4 4 4 4 § Generate CREATE table – SQL DDL using the Data Tools, in the Data Perspective Create the following EGL execute #sql{ statements: Which will create a new (temporary) table Populate this temporary table with rows from EGL. Customer Do some EGL DML statements against the table using custom SQL statements Drop the table Test your code using the sql. Test program There will also be an optional workshop that will allow you to combine EGL Debugging and your temporary table work, with the Data Perspective 5

Data Perspective – Generate Create Table Statement – 1 of 2 From the Data Perspective – from the Database Explorer 4 Reconnect to EGLDerby. R 7 4 Right-click over Customer and select Generate DDL… 4 From Options select CREATE statements 4 From Objects select all 4 From Save and Run DDL check Open DDL file for editing 4 Click Next then Finish 6

Data Perspective – Generate Create Table Statement – 2 of 2 From script 1. sql – loaded into the Content Area 4 Select and copy all of the Create table statement ( you will use this in the next part of this workshop) § § Close the Data Perspective From the Web Perspective, leave script 1. sql open !!! 4 Cause you’ll be coming back to this generated DDL 7

Execute Keyword – Create Table In eglderby 7. access/Customer. Lib. egl 4 Create a new function, named: exec. Create. Table as shown below § Notes: 4 Paste in the generated SQL Create table statement – as shown 4 Code the execute #sql{ #sql 4 4 Change the table name to: EGL. CUSTOMERTEMP Copy and paste the try, Handle. Success and on. Exception clauses from other statements Pass the function (status Status. Rec) Status. Rec as a parameter Press Ctrl/S – and remove all syntax errors 8

Execute Keyword – Insert Into Table In eglderby 7. access/Customer. Lib. egl 4 Create another new function, named: exec. Insert. Into. Table as shown below § Notes: 4 Copy the majority of the code from some other function in Customer. Lib. egl …or… you can use Content Assist to build the statement 9

Execute Keyword – Drop Table In eglderby 7. access/Customer. Lib. egl 4 Create another new function, named: exec. Drop. Table as shown below § Notes: 4 Copy the majority of the code from some other function in Customer. Lib. egl …or… you can use Content Assist to build the statement 4 Be sure to drop EGL. CUSTOMERTEMP …not EGL. CUSTOMER! 10

Execute Keyword – Update Table In eglderby 7. access/Customer. Lib. egl 4 Create another new function, named: exec. Update. Set. Level as shown below § Notes: 4 Copy the majority of the code from some other function in Customer. Lib. egl 4 Make sure your function parameters are correct 4 Note the use of the host variable : id. In …in the WHERE clause 11

Execute Keyword – Delete From Table In eglderby 7. access/Customer. Lib. egl 4 Create another new function, named: exec. Delete. Set. Level as shown below § Notes: 4 Copy the majority of the code from some other function in Customer. Lib. egl 4 Make sure your function parameters are correct 4 Note the use of the host variable : id. In …in the WHERE clause § Generate the Customer. Lib – Ctrl/G 12

Execute Keyword – Debug using sql. Test. egl Program – 1 of 3 From Project Explorer, open: programs/sql. Test. egl 4 Create another new function, named: test. Exec. Statements() § Notes: 4 Ensure that all of your connections to the EGLDerby. R 7 are closed (check the Data Perspective) 4 Use Content Assist to build the Customer. Lib. function invoke statements 4 Add statements to main() in sql. Test. egl that invoke the new function(s) 4 Add new Break Points as shown § § And remove the other Break Points from the program while you’re at it From Project Explorer – right-click on sql. Test. egl 4 Debug the EGL program § From Debug 4 Click through your calls to the execute SQL statements 4 From the Variables view, monitor the results of each call by expanding status 4 Finish your debug session (run all of the statements in the program) 4 Reopen the Web Perspective 13 Status. Rec

Execute Keyword – Debug using sql. Test. egl Program – 2 of 3 How often have you wanted to view the results of SQL statements – WHILE your programs were executing? Using a combination of the Debugger and Data Perspective – for certain kinds of testing use cases this is possible. Let’s see how! In /programs/sql. Test. egl 4 In test. Exec. Statements() – comment out the call to: // Customer. Lib. exec. Drop. Table(status); Important. Please comment out this line and read the ***Notes section of this slide Save and Debug sql. Test. egl again § Resume through § Continue/Resume until your program/debug session has terminated § Then reopen the Data Perspective each break point – this will create and populate the temp table 14

Execute Keyword – Debug using sql. Test. egl Program – 3 of 3 From the Data Perspective / From the Database Explorer 4 Reconnect to EGLDerby. R 7 4 Expand the folders down to…… 4 Right-Click over CUSTOMERTEMP 4 Select: Data > Sample Contents § You should see the rows you inserted with your EGL program and Customer. Lib function call using execute #sql (minus row #5 … because you deleted it ) 15

EGL – open Keyword The EGL open verb allows you to create custom SQL cursor processing, where you have complete control over the result set produced by your SELECT statement. This can be very helpful in several types of situations: 4 You need to process each row returned from the database as soon as it’s returned 4 You want control over the number of occurrences in the EGL record array created from the result set By using open, open you can create an array of n size, open a cursor, and get exactly n rows into the array § The format of open is: open result. Set into <sql. Record. fld…> with #sql{ … code your SQL statement … }; for. Each(from result. Set) //do something - like append to an array //Or update the row…etc. end Notes: 4 Open creates a result. Set – a temporary area in the database that holds the results of your SELECT statement. A result. Set is like a DB 2 cursor (on the mainframe). 4 You can use get next result. Set … or … a keyword like for. Each to return each row in the result. Set for processing 16

Optional Workshop - EGL – Open Keyword – with for. Each to Process Result. Set Here’s a live example of an Open Keyword function that uses for. Each as well. Code or copy/paste and add this function to: Customer. Lib (the code is in the slide ***Notes) To call this function, make the following changes to sql. Test 4 Comment out all the code in test. Exec. Statements() 4 Add a new function 4 From main() call the new function 17

EGL – Open Keyword – with while to Process Result. Set “Conditionally” You may want more control over the loop that returns the result. Set. You can use While to explicitly bump through the rows in your result. Set – one at a time. Add another function to Customer. Lib (the code is in the slide Notes) § To call this function, make the following changes to sql. Test. egl 4 Comment out all the code in test. Exec. Statements() 4 Add a new function: 4 From main() call the new function 18

(NOT a workshop) EGL – Delete/Replace from Result. Set You may want to use the “UPDATE WHERE CURRENT OF CURSOR” CURSOR construct in your EGL logic. Note that this is especially useful in batch processing. Typical logic flow: 1. 2. 3. Read a row Determine if that (specific) row should be updated or deleted Do so (update or delete the row) – through the cursor being maintained by the DBMS on the row Note from the example that you can either: - Delete an entire row - Replace specific columns in a row *** See Notes for: 1. Additional conceptual information on using where current of 2. Example code (as shown here) to try calling from your sql. Test. egl program 19

EGL – prepare Keyword The EGL prepare verb allows you to completely customize your SELECT, FROM, WHERE, and ORDER BY at run-time. It is very useful for requirements like custom search pages, where users may select 0 – n fields for processing, and build their own dynamic query conditions and specify custom sorting results. § The format of prepare is: prepared. Stmt. ID from string. Var; open result. Set with prepared. Stmt. ID into… for. Each (from result. Set) //do something - like append to an array //Or update the row…etc. end Notes: 4 prepare creates a DBMS prepared. Statement. You can think of a prepared. Statement as a temporary DECLARE for a custom SELECT statement compiled at run-time. Prepare’d statements can be subsequently opened and fetched (with get next as seen in the previous example) § open creates a result. Set – a temporary area in the database that holds the results of your SELECT statement. It is like a DB 2 cursor (on the mainframe). § You can use get next result. Set … or … a keyword like for. Each to return the rows for processing 20 *** Notes

Prepare Keyword – Workshop – 1 of 2 § In Customer. Lib, add this function (code in the slide Notes) § Read the comments § Save your work 21

Prepare Keyword – Workshop – 2 of 2 In sql. Test – add the following function: 4 Note the spaces in the string before WHERE and ORDER BY § Why do you think this is necessary? § Comment out the code in test. Open. Statement() § In main() add a statement to invoke: test. Dynamic. SQL(); § Debug sql. Test. egl 22

OPTIONAL Workshop – prepare With Dynamic SQL Variables – 1 of 2 In the previous example, although you used a dynamic SQL statement, host variables that would normally be supplied at run-time by (for instance) user choices on a search page – were not part of the example. You can supply host variables in prepare statements in two ways: 1. 2. § By using ? (question mark) placeholders throughout the statement, where the open supplies the values as variables Through standard EGL variable values – in string fields (not shown below, but shown in an example on the next slide) The format of prepare that contains dynamically-supplied host variables is: str. Var string = “SELECT stuff… FROM stuff…” + “ Where colm = ? And colm = ? ”; prepared. Stmt. ID from string. Var; //where string. Var is shown above open result. Set with prepared. Stmt. ID using var 1, var 2 for <sql. Record>; for. Each (from result. Set) //do something - like append to an array, etc. end Notes: 4 Use a question mark – within a string variable to hold a place for an EGL variable, referenced by: with var 1… in the open statement 4 The datatypes of the variables reference by with must match their table column types 4 You can pass a Schema name as a variable into the statements. This may be necessary for dynamic Schema table identifiers – although there are other techniques 23 *** Notes

Truly OPTIONAL Workshop – prepare With Dynamic SQL Variables – 2 of 2 Here’s an example of a modified function (the code is in the Notes section of this slide) – that uses question marks (? ) as placeholders for variable data, resolved at run-time. Note also that we are dynamically supplying the Schema name. This is a useful technique. 4 Note: For <record. Name> 24

Miscellaneous EGL/SQL Topics There are 4 4 4 Reusing production SQL Null values Tables that contain reserved words in their names (or in the column names) Connecting to different databases Importing records with unqualified table names Stored Procedures SQL Performance SQLLIB Commit and Rollback Best Practices Sys. Var. sqldata 25

Reusing Production SQL § In most “Legacy Modernization” projects you will probably want to reuse your production SQL statements. Here’s how this can be accomplished: 4 Select (get) get § Create a new record for each unique combination of joined tables: – SQLRetrieve all tables that participate in the SELECT – HINT: Specify the table alias from the original statement in the Record’s tablenames [[…]] property [“tname”, ”alias”], § Create a new function for each production statement § Code get <new. SQLRecord>; § Right-click and create either a single-row (Add with into) or multi-row (array add) explicit EGL/SQL statement – #sql{ select § Copy the production SQL DECLARE statement from the original code and paste it over the explicit EGL/SQL statement inside the curly braces § Validate the statement § Test by running in the Data Perspective and Debug the statement, using a batch program 4 (Alternatively) Use: open result. Set into <sql. Record. fld…> with #sql{ § Copy/Paste the original cursor DECLARE statement after the with #sql{ curly brace § Reconcile host-variable records (into <sql. Record. fld…> ) with the original FETCH statement 4 SQL DDL and Insert/Update/Delete statements (add, replace, delete) § Use: execute #sql{ § Reconcile host-variable requirements with EGL records and variables 26

Dealing with Table Null Values – is. SQLNullable=yes § § Null values are the “absence of information”. They are logical values and represent “not/available” …or… “not/applicable” status for a field If you accept the default setting for SQL nulls as generated by the Data Access wizard, EGL internally maintains a null indicator for each variable that has the following characteristics: 4 Is in an SQLrecord 4 Is declared with the property is. SQLNullable = yes …or… Note that you could also 4 Is defined with a ? After the type - Customer. Id? ; declare a variable and add a question mark to the type, to allow null values in the field § To test for NULL in a nullable host variable, use an EGL if statement. Example: if(order. DB. order_date == null) § You can set-null an SQL table column in either of two ways: 4 Use an EGL set statement to null a nullable host variable, then write the related SQL record to the database; set order. DB. order_date = null; 4 Use the appropriate SQL syntax, either by writing an SQL statement from scratch or by customizing an SQL statement that is associated with the EGL add or replace statement. 27

Relational Tables With Reserved Words as Identifiers § With the broad range of technology, it helps to pay close attention to naming conventions, else your code will step on some reserved word somewhere in: 4 4 § EGL Java SOAP SQL Easy to say – but harder in reality to achieve. In SQL, if there is a reserved word declared as a table or column name RDBMS’s typically allow you to enclose the reserved word with double-quotes – so that the various parsers don’t get confused. 4 4 Here’s an example, from the Customer table Note the use of the Java escape character - to ensure that the double-quote is honored throughout the generated SQL 28

Debugging SQL – 1 of 2 § § In order to assist you in debugging SQL for your applications you can trace the events that occur at runtime by doing the following: For Web Applications 4 4 § Open Web. ContentWEB-INFweb. xml From the Variables tab, scroll down in the Environment Variables list until you find: Env. Entry vgj. trace. type Select it, and set its Value to: -1 Then select Env. Entry vgj. trace. device. option and set its value to: 1 For batch applications 4 4 Open Java Resources: srcrununit. properties Add two entries: § vgj. trace. type=-1 § vgj. trace. device. option=1 29

Debugging SQL– 2 of 2 § When you run a page, you will get EGL trace information in the Console that shows the EGL functions, Library calls, and DB return codes § When you debug a batch program, you will get the same kind of information in the Debugger Console 30

Tables With # signs SQL Names with # Signs § § Some (seems especially) RPG/System i users have pound signs in their table column names. Not a ANSI SQL standard practice, but you can allow them as EGL variables if you set Visual Age Generator – compatibility to true § Under: Windows > Preferences > EGL Set Visual. Age Generator compatibility § Note that this will re-build/re-generate your application. § Note also that by setting Visual. Age Generator compatibility, your variable names can contain dashes ( - ) 31

Connecting to Different Databases – 1 of 3 § The sql. Lib. connect() system function creates a named connection to a database at run time, and makes that connection active. § Example: 4 sqllib. set. Current(…) can be used to specify which connected database is the current one (in use) for a given SQL statement. This allows you to maintain open connections (and threads) among two or more databases and access them in an efficient manner. § You connect to multiple physical database “connections” established either through the. EAR file – or in the application server itself. Here’s how… 32

Connecting to Different Databases – 2 of 3 § Edit the. EAR file’s Deployment Descriptor. For each unique connection/Data Source/Schema add the following: ***Notes 4 4 The JDBC Provider. One per DBMS Data Source. One per: § § 4 Schema/Library Database – within the DBMS For the Data Source, use the current. Schema property – if you wish to provide different Schema names for unqualified Tables/Views/Synonyms 33

Connecting to Different Databases – 3 of 3 § In your EGL logic part, you can connect through the SQL Data Source. Here’s an example – referring back to the previous. EAR file Data Source entries: 34

current. Schema and i. Series DB 2 Access For the i. Series JDBC drivers, the property "current. Schema" current. Schema does not exist. You need to use the "libraries" property which provides one library that will be used as the qualifier. If you want to use the libraries library list of the user's job description, then you need to change "libraries" to the value of: *LIBL and the "naming" naming property to a value of system ***Notes 35

Importing Records with Unqualified Table Names Recall from a prior section of the course, that when you used the Data Access Application Wizard you checked Qualify table names – and all of the generated statements were fullyqualified. § This worked well for our test database and application, but in many production scenarios the following naming standards are in play: 4 unit. Test. Schema. Name. Table 4 integration. Test. Schema. Name. Table 4 production. Schema. Name. Table Where the same table. Name is qualified with different schema names according to the environment you are working within § How to handle this? By defining the: current. Schema property for your JNDI database properties in the EAR or Server’s Deployment descriptor (see below and previous slides) 4 Then by simply un-checking: Qualify table names - when you do your Data Access import 36 *** Notes

table. Name. Variables – a Third Way to Qualify a Table Schema A final method of programmatically qualifying a table name, with the table schema is to use the EGL/SQL keyword: table. Name. Variables= Note the following: table. Name. Variables= clause § § “my. Table” – becomes a variable name “Customer” – becomes a table alias in the statement produced at run-time column= clause without the Schema-Qualification § CUSTOMER. STREET not EGL. CUSTOMER. STREET To use the table. Name. Variables= technique 1. 2. 3. Declare a variable of type SQLRecord (for the table. Name. Variables) record Define a variable “in scope” of the same name as the variable name in the clause, and assign it a runtime value, with the Schema Name Use the EGL data access verbs 37 *** Notes

EGL Support for Stored Procedures Many production applications use SQL Stored Procedures – which are mini-programs running in the database that combine SQL with procedural business logic, using input and output parameters. Stored procedures are used for many different purposes but their primary attraction is SQL performance. Reliable benchmarks have been formalized Calling a stored procedure § § § EGL’s open and execute statements can invoke a stored procedure. Use open when the procedure returns the results of a query (i. e. a SELECT statement), otherwise use execute. In both cases the SQL statement is a CALL. // Returns the results of a query (row select) open results with #sql{ call Owner. ID. my. Proc 1() }; // Returns nothing – most likely an insert/update/delete/create or drop statement execute #sql{ call Owner. ID. my. Proc 2() }; § There are some restrictions on using Open with certain databases. If you are not using DB 2, consult your IBM technical contact for details 38

Arguments to Stored Procedures § Stored procedures have IN, OUT, and INOUT parameters, just like functions in EGL. § You can pass literals or host variables from EGL. § If a parameter is OUT or INOUT, the stored procedure can assign it a value, and you’ll see the new value in EGL after the call returns. a int; b decimal(7, 0); open results with #sql{ call my. Proc 3( : a, : b, 'hello' ) }; execute #sql{ call my. Proc 4( : a, : b, 'hello' ) }; 39

Stored Procedure Example – 1 of 2 This function executes SQL to drop an existing stored procedure, then executes a statement to create a stored procedure (using the code shown here). try execute #sql{ drop PROCEDURE spdept 3 }; end execute #sql{ CREATE PROCEDURE spdept 3 ( in dept varchar(5) ) RESULT SETS 1 LANGUAGE SQL ------------------------------------- SQL Stored Procedure ------------------------------------P 1: BEGIN -- Declare cursor DECLARE cursor 1 CURSOR with return FOR SELECT * FROM SAMPLE. DEPARTMENT where deptno >= dept; -- Cursor left open for client application OPEN cursor 1; END P 1 }; 40

Stored Procedure Example – 2 of 2 This code calls the Stored Procedure, passing it a single Char variable, initialized to a department number. Finally, it issues an execute #sql{ #sql statement to drop (delete) the Stored Procedure created in the previous slide dept. Nbr char(5) = "E 01"; try open result. Set 1 with #sql{ call spdept 3( : dept. Nbr ) }; write. Std. Out("get. Via. SP 3()- dept: "" + x + """ ); for. Each (from result. Set 1 into deptno, deptname, dept. location); write. Std. Out(…show results…); End // end of for. Each on. Exception write. Std. Out ("get. Via. SP 3()- Problem with call to Stored Procedure" ); exit program; end // end of try block execute #sql{ drop PROCEDURE spdept 3 }; 41

SQL Performance – 1 of 3 SQL Performance is a very big topic. If you are responsible for writing EGL code that must generate to high-performance SQL database access statements you should research this topic on the Internet, and with books written by experts such as Joe Celko and Craig Mullins (among others) Background § § § SQL efficiency is a direct correlation to the # of rows physically accessed by the DBMS versus the # of rows needed by your business logic. As an example, if a list page only displays 10 rows at a time, but accesses 2, 000 rows from the database per execution of the SQL function this is probably inefficient. However, if a batch process needs 225, 000 rows for its business process, and accesses 300, 000 rows to get them, this is relatively efficient. It’s the correlation of # of rows needed, versus DBMS work-to-be-done to get those rows that is the true measure of SQL efficiency. In order to determine the above, you must fully understand the service level requirements for your database access. Typically, you are concerned with web-page performance and user-wait time. Problems with pages occur when the SQL run to access the data for the page returns thousands (or tens or hundreds of thousands of rows). So, you will need to understand both the logical AND physical structure of your database – and its choices of data access, in order to completely understand how much effort it takes the database to get the data you need. This can be (read, “usually is”) a very complex and time-consuming process, unique to every different business application The best we can do in this tutorial is to give you general rules-of-thumb that will make queries “more” efficient. However, it is strongly recommended that you discuss this with your systems database administrator (DBA) – for any/all pages that require maximum SQL performance Note that there is no end of decent SQL performance articles – mostly database-specific on this topic available freely on the internet – and a # of good books as well. 4 4 http: //www-128. ibm. com/developerworks/db 2/library/techarticle/0210 mullins. html http: //www. javaworld. com/javaworld/jw-02 -2005/jw-0228 -apm. html 42

SQL Performance – Rules of Thumb – 2 of 3 § § For Batch applications that process all or a significant % of table rows, index access is often inefficient. This is because the database must first read the index dataset – then read the table dataset to retrieve each row. Index access is efficient only when your SQL requirements are for a small percentage of the entire table: (typically less than 20%). For online/pages 4 Attempt to limit the number of rows retrieved per get statement to the minimum needed per page view. This will require you to do at least two things (neither of which are easy or quick): § § Add additional WHERE clause conditions using SQL BETWEEN that reference database index columns – and in your EGL code manage the values so that the database is retrieving as close to the # of records needed per page view as possible Code your own programmatic paging – by incrementing the values in the SQL BETWEEN clauses per page forward – and decrement the values for page backward. 4 Separate the database access from the page fields by using basic. Records to render the fields. In this tutorial, you have primarily dragged and dropped database records onto a form. This is not as efficient as doing the database access through sql. Records, then moving the records return 4 Using explicit SQL, select only the # of columns you need in your page view (i. e. if you are only showing 4 fields tied to a 30 column table row, delete the other 26 columns. 4 Attempt to avoid ORDER BY unless it is on an indexed column AND the index exists in the same order you need for your sort process. Obviously this is driven by the business requirements, but database sorting can be very expensive. Statements that force sorts include: § § § ORDER BY GROUP BY TABLE JOINS UNION Certain Sub-selects 4 Avoid using SQL WHERE clause keywords that disallow index access § § Computations in the where clause Like – with the % or underscore before the literal data 43

SQL Performance – Rules of Thumb – 3 of 3 § Attempt to limit the number of JSF data. Table cells rendered by EGL/JSF 4 A cell is the intersection of a row and column in a data. Table, so that if you have 5, 000 rows and 20 columns, you generating 100, 000 cells. This will almost definitely cause severe page performance problems. As a rule of thumb, keep the number of total cells under 10, 000 (i. e. 500 rows/20 columns), etc. Large-scale SQL performance improvements have been documented by so doing. § SQL Stored Procedures will always out-perform dynamic SQL access. So for your ultra-performanceintensive pages, consider using SQL Stored Procedures. This has been well-documented in IBM Redbooks § Use the EGL MAXSIZE property - to halt the fetch process from the database on potentially large queries. MAXSIZE, which you specify as a property of a dynamic array interacts with the database engine’s fetch looping, and once reached, closes the cursor and stops the database access process. You should note that the majority of the cost of the database access occurs when a cursor is OPEN’d. However, if your page only renders 10 rows at a time, and you are doing programmatic paging, specifying MAXSIZE = 10 will help performance – somewhat § Use any of your DBMS’s SQL efficiency features. For example DB 2 has several keywords that influence the Optimizer to access a stated # of rows: 4 OPTIMIZE FOR n ROWS; //tells the optimizer you will probably only fetch n rows 4 FETCH FIRST n ROWS ONLY; //tells the optimizer you will ONLY fetch n rows 4 FOR READ ONLY //tells the optimizer you will not do any update where current of commands 4 WITH UR //allows you to read uncommitted rows 44

EGL’s SQLLIB system library provides a number of extremely useful system API calls. We’ve already seen connect. Others include: Check out: § load. Table § unload. Table Example 1: Say you needed to: 1. show users rows in a data. Table. 2. Allow them to select certain rows. 3. Write these Rows out to a data file on the Server. You could use unload. Table to do this easily and efficiently, by: 1. Creating a new temporary table. 2. Insert the rows into that table. 3. Unloading the table to the file Example 2 – Reverse. Say you needed to read a. DAT file on a user’s desktop into a table. You could: 1. Use the JSF file. Upload component to transfer the file to the server. 2. Use the LOBLIB. update. Blob. To. File API to Write the file to the server. 3. Use load. Table to populate an RDBMS table. 45

EGL’s System Variables – sys. VAR. sql. Data and sql. Lib. sql. Data § sys. Var. sql. Data and sql. Lib. sql. Data are structured records that contain data available to your EGL programs used in SQL data access routines (primarily error-handling situations, but not necessarily limited to that). § The variables in both records are updated after your program accesses a relational database. The differences between the two record 4 The scope of sys. Var. sql. Data is limited to a program. For example, if program. A calls program. B and both programs access a relational database, each program has its own copy of sys. Var. sql. Data. The values set in program. B are not available after you return to program. A. 4 The scope of sql. Lib. sql. Data is global across the programs in the run unit. For example, if program. A calls program. B and both programs access a relational database, the values set by the last relational database access in program. B are available in program. A immediately after the return to program. A and until the next relational database access. Note – if you are using the Data Access Application wizard function calls, you’ve seen that the built-in library code initializes the status. Record data after each DBMS SQL access. 46

EGL’s Commit/Rollback Processing Commit processing is managed by the EGL build file’s SQLCommit. Control build descriptor option. 4 § Note that you’ll need to un-check: Show only specified options to see it You have three alternatives for transaction control – or commit processing in EGL: 4 AUTOCOMMIT § Which commits after each successful database update operation 4 NOAUTOCOMMIT § § Which does not issue a commit after each successful database operation You can manually Commit/Rollback with calls to the EGL System Library functions: – syslib. commit(); – syslib. rollback(); 4 NOCOMMIT § § Which never issues a commit, and is used primarily by certain Informix databases that do not allow transaction support Note that the end of every run-unit in EGL issues an implicit commit – this includes JSFHandlers. 47

EGL/SQL Summary and Best Practices The following chart lists the best practices approach to coding SQL and using the EGL SQL tools SQL Data Access Requirement Best Practice – EGL Keywords Simple SQL DML (Select, Insert, Update, Delete). Primary Key as Where & Order By clause Implicit SQL (get, add, replace, delete ) SQL DML with reusable custom Where clause Create custom record with default. Select. Condition SQL Select statements with custom WHERE clause Explicit SQL - get <sql. Record> with #sql{ … } Table Join SQL Record > SQLRetrieve (and don’t forget to code the Join Condition) Derived Data in SELECT – Max(…), Avg(…), Select First_Name || ‘, ’ || Last_Name, etc. Explicit SQL get with #sql{derived fields} – or custom record with column= specifying the derived or computed expression Complex or custom SQL Insert, Update or Delete add, replace, delete with #sql{…} …or… execute #sql{…} Non-DML SQL Statement - Create table…etc. execute #sql{…} Dynamic SQL (Prepare) execute #sql{…} prepare also Stored Procedure open result. Set with #sql{ call stored_proc (: host. Var) } Process individual rows of a SELECT statement’s result-set open result. Set with #sql{…} for. Each(from result. Set)… or … while ( SQLData. SQLCode == 0) Programmatic Paging Use Data Access Application code as example. (See slide Notes) Add data to your table Data Perspective/Table Editor Test your SQL Validate SQL, Run interactive SQL in SQL Editor of Data Perspective 48