Wednesday, September 23, 2015

Build Views and Views of Views

sql view 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

10 comments:

  1. I also wasn't aware that views were not dynamically updated like regular logical files. That makes views effectively just predefined query statements. Nice article.

    ReplyDelete
  2. Please do not specify the date format within the SET OPTION clause to any format with a 2 digit year (*DMY, *MDY, *YMD) this may cause huge problems.

    Any date is internally stored as numeric value (scaliger no) representing the days since 01/01/4713 BC (begin of the julian date). Date formats are only used for making this scaliger readable.

    When using dates in RPG the scaliger no is always converted into a character representation and retranslated immediately before the date value is written/updated. RPG itself can handle the different date formats in multiple date fields very nicely. But your program will crash if a date value from a fields with a 4 digit year format is moved to a date field with a 2 digit year format, at least if the date is outside the valid range for a 2 digit year (valid range 01/01/1940 to 12/31/2039).

    When using date host variables in an (embedded) SQL statement, the SQL precompile will add a additional host variable with the date format specified either in the compile command or in the SET OPTION statement.
    RPG may crash if the date format is not compatible with the date format in the RPG variable or even worse if the SQL variable has a 2 digit year format while the RPG variable has a 4 digit year format.

    In your example it will work because the date format is explicitly set in the D-Specs and for the SQL variable.

    In either way I'd suggest always using a 4 digit year format, which one doesn't matter (even though *ISO would be the best one)
    BTW SQL itself uses always the date format that is set in the connection or in the STRSQL statement or JDBC Set Up.

    Birgitta

    ReplyDelete
  3. To say "You could have many hundreds, nay thousands, of Views on your IBM i and they would not affect the performance of your server" is a bit misleading since for views to be efficient you would require the supporting SQL indexes which would be updated along with the PF as native LF files are ...granted if done correctly you could have hundreds of views that would only require a handfull of key field indexes as opposed to each native LF having it's own embedded and sometimes repetitive key field indexes

    ReplyDelete
    Replies
    1. The point I was trying to make is that unlike LF and DDL indexes which are updated when their "parent" PF/DDL table is updated the View is not.

      You take the "performance hit" when using the View.

      Delete
  4. Except that Logical Files can also not be updated realtime. Simply specify MAINT(*REBLD) and the index is rebuilt when the LF is opened. It's been that way for over 30 years.

    ReplyDelete
  5. Yeah Edward, except the user will think their session is locked up while the index is rebuilt, and generate an IT Ticket and by the time IT investigates, the index has been rebuilt and *REBLD wasted everyone's time. It's best to just update the index real time unless it's an LF that is only used by batch jobs. Chris Ringer

    ReplyDelete
  6. I was also in the OCEAN session that Paul held and I also had that "aha" feeling... there were some interesting ideas presented. Thanks for writing this article.

    I think some other benefits of views are that whereas a LF utilizing select/omit will be forced to use the "classic query engine", views can use the newer/faster "sql query engine. (Although I have a feeling that I read somewhere that in 7.2 this was fixed with LFs as well.)

    But also, changing the selection criteria in a LF will cause you to recompile it and therefore also all the programs using it. With a view, selection criteria can be changed and the programs do not have to be recompiled. Correct?

    ReplyDelete
    Replies
    1. If you do not change what columns are in the view then you should be OK.

      For example: changing the rows selected should not cause any issues.

      Delete
  7. A view of views. That's an excellent idea. It creates the possibility of having component views. Cool.

    ReplyDelete
  8. Good article, thank you for share this information.
    I have a questions; if the view is updated every time that we consulte him, what is the cost of the use the view many times at the same times.
    Is possible that we have issues of performance.
    Thank for you time and answer.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.