But SQL defined database objects are not just for new databases; they also have a role to play when used in conjunction with traditionally defined physical and logical files.
I'll use a simple example to show you how SQL defined views may be of benefit in an existing environment. The easiest way to start using views is with iSeries Navigator, which is what I will be using in the following examples. I will also provide the corresponding SQL statements.
Some base data
This example is based on two physical files, both of which are in the library NEWSCHEMA. The first file (EMPDATA1) simply contains an employee ID and name for each employee and it contains the following records.
Employee Name Id 1 Paul 2 Michael 3 John
The second file (EMPDATA2) contains the date of birth and the date joined for each employee. The dates are stored as numeric fields in ISO format (sound familiar?). The file contains the following records.
Employee DOB Joined Id 1 19,560,514 19,811,012 2 19,700,620 20,030,114 3 19,661,220 19,900,401
The First View
You will start by creating a view that represents the two date fields as proper date fields as opposed to numeric fields.
In iSeries Navigator, expand the Databases section and ensure that the required library (NEWSCHEMA in this case) is one of the available schemas. Expand the library and select New -- View from the context menu for Views. Complete the resulting window, as shown in Figure 1, and click OK to continue.
Figure 1: Defining a New View
On the following window (Figure 2), click the Select Tables button and, in the pop up window, expand the required library (NEWSCHEMA), select the first table (EMPDATA1) and click the Add button, select the second table (EMPDATA2) and click the Add button and click the OK button to return to the view definition, as shown in Figure 2.
Figure 2: Defining a New View -- Tables Selected
You are defining a Join Logical so the next step is to define the link between the two files. Simply drag and drop the EMPID field from the EMPDATA1 window to the EMPID field in the EMPDATA2 window – the window shown in Figure 3 is displayed.
Figure 3: Defining a New View – Join Properties
The Join Properties allows you to select the type of join. You are going to use the default inner join so just click OK to return to the view definition.
To select the required columns for the view drag and drop each required column from the two table windows to the bottom panel. You are going to make a change to the two date fields so it is a good idea to rename them; simply click on the field name and key in the new name. The resulting window is shown in Figure 4.
Figure 4: Defining a New View – With Fields Renamed
You now need to change the definition of the BIRTHDATE field; select the BIRTHDATE field and click the Formula button. In the Formula window, key in the clause to convert a date stored as a numeric field to a proper date field, as shown in Figure 5.
Figure 5: Defining a New View – Defining a Formula
The clause that is entered is
DATE( SUBSTR(CHAR(BIRTH) ,1 ,4 ) || '-' || SUBSTR(CHAR(BIRTH) ,5 ,2 ) || '-' || SUBSTR(CHAR(BIRTH) ,7 ,2 ) )
You have probably done the same in Query more than once! The inner part of the clause uses the SUBSTR and CHAR functions to construct a character representation of the numeric date in ISO format with a '-' separator. The DATE function then converts it to a DATE field.
Now you repeat the same process for the JOINED field and finish by clicking OK to create the view.
The data presented by the view (e.g. in Query) is as follows:Employee Name BIRTHDATE JOINEDDATE Id 1 Paul 1956-05-14 1981-10-12 2 Michael 1970-06-20 2003-01-14 3 John 1966-12-20 1990-04-01
You could also have created the view using the following SQL statement:
CREATE VIEW EMPVIEWA ( EMPID , NAME , BIRTHDATE , JOINEDDATE ) AS SELECT a.EMPID, NAME, DATE( SUBSTR( CHAR(BIRTH) ,1 ,4 ) || '-' || SUBSTR( CHAR(BIRTH) ,5 ,2 ) || '-' || SUBSTR( CHAR(BIRTH) ,7 ,2 ) ), DATE( SUBSTR( CHAR( JOINED ) ,1 ,4 ) || '-' || SUBSTR( CHAR(JOINED ) ,5 ,2 ) || '-' || SUBSTR( CHAR( JOINED ) ,7 ,2 ) ) FROM EMPDATA1 a INNER JOIN EMPDATA2 b ON a.EMPID = b.EMPID ;
The second view
You are now going to create a second view that calculates the age of an employee -- as well as their age when they joined the company. You don't want to have to go through the entire process of defining the date calculations for a second time so you will use one of the very neat features of defining views -- you will define a view of a view!
Follow the process to define a new view and click on the Select Tables button. Expand the library and page to the end of the list, which will contain any views in the library, select the EMPVIEWA view just created and click OK.
Drag and drop each of the fields to the bottom pane. Drag and drop the two date fields for a second time, rename the fields to AGE and AGEJOINED and change the corresponding descriptions. You now need to define the Formulas for the two new fields. The clause to calculate the AGE is:
YEAR(CURDATE( ) - BIRTHDATE) The clause to calculate the age when the employee joined is: YEAR(JOINEDDATE - BIRTHDATE) Click OK to finish defining the view. The data presented by the view is as follows: Employee Name BIRTHDATE JOINEDDATE AGE AGEJOINED Id 1 Paul 1956-05-14 1981-10-12 50 25 2 Michael 1970-06-20 2003-01-14 36 32 3 John 1966-12-20 1990-04-01 40 23
You could also have created the view using the following SQL statement: CREATE VIEW EMPVIEWy ( EMPID , NAME , BIRTHDATE , JOINEDDATE , AGE , AGEJOINED ) AS SELECT EMPID, NAME, BIRTHDATE, JOINEDDATE, YEAR( CURDATE( ) - BIRTHDATE), YEAR(JOINEDDATE - BIRTHDATE) FROM EMPVIEWA ;
Every silver lining has a cloud. Powerful and all as these views are there is a gotcha if you want to use them in an RPG program. A view is not keyed which means that, to be able to use them properly, you must make use of embedded SQL as opposed to traditional I/O. But, given the power of what you can do with views, it more than often worth the effort.
In the end
Remember, the base tables do not have to be defined with SQL so you can easily mix and match views with your existing database.
Another point about views is that they do not have an access path so they do not effect performance on the system as data is maintained -- any performance overhead is incurred when the view is used.
Give it a try – views will make you look at things differently.
About the author: Paul Tuohy is CEO of ComCon, an iSeries consulting company. He is the author of "Re-engineering RPG Legacy Applications", "The Programmers Guide to iSeries Navigator" and the self teach course "iSeries Navigator for Programmers". He is also an award winning speaker who speaks regularly at US Common conferences and the renowned RPG Summit conferences.