A View of a View of a View
April 13, 2011 Ted Holt
One of the traits of a good database is that users are not forced to perceive the structure of the data as it is stored in the tables, but are free to perceive the data in ways that are meaningful to them. Today I share an SQL technique that achieves that goal. (No, you can’t do this with logical files.)
Before I illustrate the technique, let’s be sure we understand this idea of allowing the user to perceive the data as he wants to. Suppose a table (physical file) of invoices includes the date that an invoice is generated. That date may be stored as an eight-digit number in YYYYMMDD format. However, the user may not like that format. Instead of 20101116, he may prefer to see 11/16/10, 16-11-2011, or 16-Nov-2011. The following idea gives more ideas of how the user can have his own version of reality.
Under DB2 for i, alternate reality is achieved through the use of logical files and SQL views. Logical files are so 1980. They can only take you so far. To create an effective alternate reality, you need SQL.
Let me begin my illustration by generating some test data. The following command creates a physical file called OBJD.
DSPOBJD OBJ(SOMELIB/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) OUTFILE(MYLIB/OBJD)
A View of a Table
If you run this command, you’ll see a lot of information about the objects in a library. To create an alternate reality that is easier to work with, let’s:
create view mylib/objects as select obj.ODLBNM as Library, obj.odobnm as Object, obj.ODOBTP as ObjectType, obj.odobat as ObjectAttr, obj.ODOBTX as Description, case when substr(obj.odcdat,5,1) between '0' and '3' then date('20'|| substr(obj.odcdat,5,2)||'-'|| substr(obj.odcdat,1,2)||'-'|| substr(obj.odcdat,3,2)) when substr (obj.odcdat,5,1) <> ' ' then date('19'|| substr(obj.odcdat,5,2)||'-'|| substr(obj.odcdat,1,2)||'-'|| substr(obj.odcdat,3,2)) else null end as CreationDate, case when substr(obj.odldat,5,1) between '0' and '3' then date('20'|| substr(obj.odldat,5,2)||'-'|| substr(obj.odldat,1,2)||'-'|| substr(obj.odldat,3,2)) when substr (obj.odldat,5,1) <> ' ' then date('19'|| substr(obj.odldat,5,2)||'-'|| substr(obj.odldat,1,2)||'-'|| substr(obj.odldat,3,2)) else null end as ChangeDate, case when substr(obj.odudat,5,1) between '0' and '3' then date('20'|| substr(obj.odudat,5,2)||'-'|| substr(obj.odudat,1,2)||'-'|| substr(obj.odudat,3,2)) when substr (obj.odudat,5,1) <> ' ' then date('19'|| substr(obj.odudat,5,2)||'-'|| substr(obj.odudat,1,2)||'-'|| substr(obj.odudat,3,2)) else null end as LastUsedDate, obj.oducnt as NbrDaysUsed from mylib/objd as obj
The new view is called OBJECTS. The data looks a lot different under this view, even though how it’s stored in the database hasn’t changed. I can use this view from any query tool, read it with an RPG program, or otherwise reference it as I would the underlying physical file.
A View of a View of a Table
But we’re not finished yet. Let’s make it even more meaningful by creating some new fields to help us categorize the data.
create view mylib/ObjectUsage as select Library, Object, ObjectType, ObjectAttr, Description, CreationDate, ChangeDate, LastUsedDate, case when LastUsedDate is null then '0-Never' when LastUsedDate >= current date - 1 month then '4-Within last month' when LastUsedDate >= current date - 6 months then '3-Within last six months' when LastUsedDate >= current date - 1 year then '2-Within last year' else '1-Long ago' end as UsagePeriod, NbrDaysUsed, case when NbrDaysUsed > 100 then '3-A Lot' when NbrDaysUsed > 25 then '2-Some' when NbrDaysUsed > 0 then '1-A Little' else '0-Never' end as UsageFrequency from mylib/objects
I have two new columns called UsagePeriod and UsageFrequency, which I can read for my own benefit. But I can also sort and select on those columns.
But what I really want you to notice is the FROM clause. Did you notice that it refers not to the physical file, but to the first view I created. Yes, that’s right! You can create views over views! Try creating a logical file over a logical file.
of a Table
But wait, there’s more! Let’s say that our analysis requires that we concentrate on objects that are deemed critical, which we define as those that have been used many times and/or have been used recently. Let’s create an alternate reality that helps us zero in on those objects.
create view mylib/ObjectUsageAnalysis as select Library, Object, ObjectType, ObjectAttr, Description, CreationDate, ChangeDate, LastUsedDate, UsagePeriod, NbrDaysUsed, UsageFrequency, case when UsagePeriod >= '3' or UsageFrequency >= '3' then '1' else '0' end as Critical from mylib/objectusage
You are probably not surprised to see that this view was created from the second view. ObjectUsageAnalysis is a view of a view of a view of a table. And I can query that view, like this:
select * from mylib/ObjectUsageAnalysis order by Critical desc, Library, Object
The system presents the data to me much differently from the way the DSPOBJD command stored it in a physical file. Try it for yourself.
It Isn’t Necessarily What It Is
“It is what it is” is a saying I have heard more and more over the past few years. I am not impressed with this saying. “It is what it is” seems to me to be an excuse to maintain a less-than-desirable situation, rather than take action to improve.
“It is what it is” accurately described disk files back in my System/34 and System/36 days, when I had to use input and output specs in RPG programs, and beginning and ending positions in #GSORT specs. But those days are long gone. The database tools we use today allow us to create realities of our own. We should take advantage of it.