Build Views and Views of Views

Have you ever had one of those "Aha!" moments when, as we say in England, the penny drops when someone says something and you are left thinking: "Why didn't I think of that?" I had one of those moments at the OCEAN user group technical conference during a presentation by Paul Tuohy on embedded SQL in RPG.

I have described before what SQL Views are. If you look at the object attribute it is a "LF", but it is not Logical file. Confused?

When you have a Logical file and you update the "parent" Physical file, the Logical file is also updated. If you build hundreds of Logical files it slows downs updates as after the Physical file is updated all of its Logical files have to be updated too. In my consulting days I have seen a Physical file with over 200 dependent Logical files.

Views are different. They are not updated when the Physical file, or DDL table, is updated. Only when the View is used is the data accessed from the file or table. This leads to one of points Paul made where I wanted to exclaim "Right on!". You could have many hundreds, nay thousands, of Views on your IBM i and they would not affect the performance of your server.

In the interest of the build-once-use-many approach it would make sense to create Views over data in many different ways as they could be used by more than one program, rather than have similar extract and select code in RPG or the similar SQL statements in multiple SQL RPG programs.

He also made one point that was new to me. Why not build a View of Views? The Query engine is smart enough to combine the selections of the Views into one. Therefore, when a View built over another is used it is as fast as a more complex version that would combine the statements of the two (or more).

I am going to give a simple example of how I can build a couple of Views that I would use in more than one program, and then a third that is built using the Views I had created earlier.

I have four files I wish to include in these views:

1. ORDHDR – Order header file. Yes file, not DDL table. Like most of you I work with an existing ERP application that uses files. It would be nice if they are going to modernize their database and move to DDL tables, but I do not see it happening in the near future.
2. ORDDTL – Order detail file.
3. VENDOR – Vendor master file.
4. ITMMST – Item (part) master file.

I am going to create three views:

• VIEW1 – Combine ORDHDR and VENDOR.
• VIEW2 – Combine ORDDTL and ITMMST.
• VIEW3 – Combine VIEW1 and VIEW2.

Let me start with the code for VIEW1:

01 CREATE VIEW MYLIB/VIEW1 ( 02 ORDER_NBR FOR "ORDNBR", 03 VENDOR FOR "VNDNBR", 04 VENDOR_NAME FOR "VNDDSC", 05 ENTERED_DATE, 06 DUE_DATE, 07 ORDER_STATUS, 08 TOTAL_QTY FOR "TOTQTY") 09 AS SELECT A.ORDNBR,A.VNDNBR,B.VNDDSC, 10 DATE(TIMESTAMP_FORMAT(SUBSTR(DIGITS(A.ENTDTE+19000000),2,8),'YYYYMMDD')), 11 DATE(TIMESTAMP_FORMAT(SUBSTR(DIGITS(A.DUEDTE+19000000),2,8),'YYYYMMDD')), 12 CASE WHEN A.ORDSTS = 'O' THEN 'OPEN' 13 WHEN A.ORDSTS = 'P' THEN 'PARTIAL' 14 WHEN A.ORDSTS = 'C' THEN 'COMPLETE' 15 ELSE 'OTHER' 16 END, 17 A.TOTQTY 18 FROM ORDHDR A LEFT OUTER JOIN VENDOR B 19 ON A.VNDNBR = B.VNDNBR ; 20 LABEL ON COLUMN MYLIB/VIEW1 ( 21 ORDER_NBR IS 'Order number', 22 VENDOR IS 'Vendor number', 23 VENDOR_NAME IS 'Vendor name', 24 ENTERED_DATE IS 'Order entered date', 25 DUE_DATE IS 'Order due date', 26 ORDER_STATUS IS 'Order processing status', 27 TOTAL_QTY IS 'Total order quantity' 28 ) ;

Line 1 gives the name of the View and the library I want it in.

The lines, 2 – 8, between the parentheses (or brackets, ( ) ) give the names of the columns in the view. I am using log field names, even though the fields in the file are only six long. The FOR on lines 2, 3, 4, and 8 mean that the new columns have the same attributes as the file fields given with the quotes ( " ). ENTERED_DATE, DUE_DATE, and ORDER_STATUS will be defined in the SELECT part of the statement.

After the columns have been defined the SELECT is found on lines 9 – 19. The "dates" in the ERP database are really 7,0 numeric fields, *CYMD format, and lines 10 and 11 shows how I converted them to a Date data type value. Therefore, ENTERED_DATE and DUE_DATE are dates.

Lines 12 – 16 shows how I can use a CASE statement to give a more meaningful value that just the single letter code. For more information on this subject read Creating derived columns in SQL View.

The LEFT OUTER JOIN, line 18, means that if there is no matching record in VENDOR then the fields from the Vendor file will be null, not blank or zero as those columns contain no value (= null).

I have decided not to use the Column Headings from the files. On lines 20 – 28 I give the columns new headings.

One thing you cannot have in a View is an ORDER BY. The View will have to be ordered by whatever uses it.

To test the View I can go into STRSQL and type the following:

SELECT * FROM MYLIB/VIEW1

VIEW1 looks like:

Order Vendor Vendor Order entered Order due Order processing Total order number number name date date status quantity 4811 1147 BOEING 07/14/15 10/01/15 OPEN 1,500 4942 1146 ANZAC CLASS 08/19/14 06/20/15 COMPLETE 750 4121 1146 ANZAC CLASS 07/20/15 01/31/15 PARTIAL 2,000 ******** End of data ********

The code for VIEW2 is pretty much the same:

01 CREATE VIEW MYLIB/VIEW2 ( 02 ORDER_NBR FOR "ORDNBR", 03 ORDER_SEQ FOR "ORDSEQ", 04 ITEM, 05 ITEM_DESCRIPTION FOR "ITMDSC", 06 LINE_QTY FOR "LINQTY", 07 UNIT_PRICE FOR "PRICE", 08 EXTENDED_PRICE) 09 AS SELECT A.ORDNBR,A.ORDSEQ,A.ITEM,B.ITMDSC,A.LINQTY, 10 A.PRICE, 11 A.PRICE * A.LINQTY 12 FROM ORDDTL A LEFT OUTER JOIN ITMMST B 13 ON A.ITEM = B.ITEM ; 14 LABEL ON COLUMN MYLIB/VIEW2 ( 15 ORDER_NBR IS 'Order number', 16 ORDER_SEQ IS 'Order seq', 17 ITEM IS 'Item number', 18 ITEM_DESCRIPTION IS 'Item description', 19 LINE_QTY IS 'Order line qty', 20 UNIT_PRICE IS 'Order line unit price', 21 EXTENDED_PRICE IS 'Order line extended price' 22 ) ;

The only difference is that the extended price, EXTENDED_PRICE, is calculated within the View, line 11, rather than it needing to be calculated within whatever used this View.

When I test this View I get the following:

Order Order Item Item Order line Order line Order line number seq number description qty unit price extended price 4811 1 40607-09 16 OZ CUP 500 17.50 8,750.00 4811 2 40604-MID PLATE 1,000 12.00 12,000.00 4942 1 40604-MID PLATE 750 12.50 9,375.00 4121 1 40601-MINI FORK 1,000 5.00 5,000.00 4121 2 40604-MID PLATE 750 12.00 9,000.00 4121 3 40607-09 16 OZ CUP 250 17.50 4,375.00 ******** End of data ********

All the "work" was done in VIEW1 and VIEW2, therefore, the code for VIEW3, the view to combine the two, is simple:

01 CREATE VIEW MYLIB/VIEW3 ( 02 ORDER_NBR, 03 VENDOR_NAME, 04 ENTERED_DATE, 05 DUE_DATE, 06 ORDER_STATUS, 07 TOTAL_QTY, 08 ORDER_SEQ, 09 ITEM, 10 ITEM_DESCRIPTION, 11 LINE_QTY, 12 EXTENDED_PRICE) 13 AS SELECT A.ORDER_NBR,A.VENDOR_NAME,A.ENTERED_DATE,A.DUE_DATE, 14 A.ORDER_STATUS,A.TOTAL_QTY,B.ORDER_SEQ,B.ITEM, 15 B.ITEM_DESCRIPTION,B.LINE_QTY,B.EXTENDED_PRICE 16 FROM MYLIB/VIEW1 A LEFT OUTER JOIN MYLIB/VIEW2 B 17 ON A.ORDER_NBR = B.ORDER_NBR ;

As there is nothing in that statement that has not been seen in one of the ones above I am sure you can determine what it all means.

This time when I test the contents I do not want to see all the columns in the View, as it would be too wide to display here. Therefore, my SQL statement does not have the "select all comments" asterisk ( * ), it lists the columns in the order I want them:

SELECT ORDER_NBR,VENDOR_NAME,DUE_DATE,ORDER_STATUS, ORDER_SEQ,ITEM_DESCRIPTION,LINE_QTY,EXTENDED_PRICE FROM MYLIB/VIEW3 ORDER BY DUE_DATE,ORDER_NBR

Which gives me:

Order Vendor Order due Order processing Order Item Order line Order line number name date status seq description qty extended price 4121 ANZAC CLASS 01/31/15 PARTIAL 1 FORK 1,000 5,000.00 4121 ANZAC CLASS 01/31/15 PARTIAL 2 PLATE 750 9,000.00 4121 ANZAC CLASS 01/31/15 PARTIAL 3 16 OZ CUP 250 4,375.00 4942 ANZAC CLASS 06/20/15 COMPLETE 1 PLATE 750 9,375.00 4811 BOEING 10/01/15 OPEN 1 16 OZ CUP 500 8,750.00 4811 BOEING 10/01/15 OPEN 2 PLATE 1,000 12,000.00 ******** End of data ********

In real life I am not going to want to use STRSQL to view the View, I am going to want to use it in a program. So here is the Select statement in a RPG program:

01 dcl-ds ColData qualified dim(9999) ; 02 Order_Nbr char(7) ; 03 Vendor_Name char(20) ; 04 Due_Date date(*mdy) ; 05 Order_Status char(10) ; 06 Order_Seq packed(5) ; 07 Item_Description char(20) ; 08 Line_Qty packed(7:0) ; 09 Extended_Price packed(10:2) ; 10 end-ds ; 11 dcl-s wkMax packed(5) inz(%elem(ColData)) ; 12 dcl-s wkRowRetrieved uns(5) ; 13 exec sql SET OPTION COMMIT = *NONE, 14 DATFMT = *MDY ; 15 exec sql DECLARE C0 CURSOR FOR 16 SELECT ORDER_NBR,VENDOR_NAME,DUE_DATE, 17 ORDER_STATUS,ORDER_SEQ,ITEM_DESCRIPTION, 18 LINE_QTY, 19 CAST(EXTENDED_PRICE AS NUMERIC(10,2)) 20 FROM MYLIB/VIEW3 21 ORDER BY DUE_DATE,ORDER_NBR 22 FOR READ ONLY ; 23 exec sql OPEN C0 ; 24 exec sql FETCH NEXT FROM C0 25 FOR :wkMax ROWS INTO :ColData ; 26 SQLCOD = SQLCOD ; 27 wkRowRetrieved = SQLER3 ; 28 exec sql CLOSE C0 ; 29 *inlr = *on ;

In this program I am doing a block Fetch to retrieve more than one row at a time. This is explained in the post SQL blocking fetches, getting more than one row at a time.

Line 1 – 10 I define the data structure array that the rows from the Fetch will be placed in.

On line 11 I define variable that I will use in the Fetch for the number of the rows I want. This is initialized to be the value of the number of the elements of the array. This way if I increase or decrease the size of the array I will not have to change any other part of the program.

On line 12 I define the variable that will contain the number of rows Fetched. This is useful as if I only retrieve a few rows I know how many elements of the array contain the retrieved data.

Line 13 and 14 are the SQL options I want to use for this program. I have described these in the post Putting the SQL compile options into the source.

For those of you not familiar with embedded SQL this is where I define the cursor that will be used to read the Table/View/File/Index that is defined, lines 15 - 22. This is where I put the same SQL statement I used in STRSQL. On line 19 I have used the CAST to redefine the EXTENDED_PRICE row, in this case I am making it smaller than the way it is defined in the View. On line 22 I have FOR READ ONLY which means I will be only using the cursor for input.

I open the cursor on line 23.

On the Fetch, lines 24 and 25, I state that I want to retrieve the number of rows in the variable wkMax into the array, ColData. As we know from above that this value is the same as the number of elements in the array.

Line 26 is for CYA. I compile all my program with *NOUNREF, which means that all fields, variables, etc. that are not used in the program and not included, i.e. they cannot be used in debug if an error occurs. This statement allows me to be able to see the value of the SQLCOD in debug, and if I dump the program. For more information about the *NOUNREF see Which Control options/H-specs do you use?.

When the Fetch is performed the number of rows fetched is found in the SQLCA data structure subfield SQLER3. On line 27 I move that value to a variable with what I think is a better and more meaningful name.

I close the cursor on line 28 and the program finishes.

For those of you who cannot use free form definition specifications I have put fixed format definitions at the bottom of this post here.

I can see many Views I could build for the environment I work in and I have to thank Paul for making me thinking about this.

 

This article was written for IBM i 7.2, and should work for earlier releases too.

---

Fixed format definitions

01 D ColData DS qualified dim(9999) 02 D Order_Nbr 7 03 D Vendor_Name 20 04 D Due_Date D datfmt(*mdy) 05 D Order_Status 10 06 D Order_Seq 5P 0 07 D Item_Description... 08 D 20 09 D Line_Qty 7P 0 10 D Extended_Price... 11 D 10P 2 12 D wkMax S 5P 0 inz(%elem(ColData)) 13 D wkRowRetrieved S 5U 0 /free

Return