WilsonAlano   2005 pts.  |   May 26 2009  3:49PM GMT

Hi Dave,

IMO, what you planed to do is the path to follow and in my experience, I saw a LOT of LF unnecessarily created just because programmers don’t understand LF uses and concept. For example, I saw a LF with a key of 3 fields, Branch, Account and Currency and another LF with 2 fields key Branch and Account. It’s totally redundant and this kind of error is frequent.
So, you have a lot of work to do but I’m sure it will worth effort.

Regards,
Wilson

Gilly400   23625 pts.  |   May 26 2009  4:20PM GMT

Hi Dave,

150 logical files over one PF is absolute madness IMHO. Sounds to me like people have built temporary logicals for specific tasks and then never removed them.

Do you have any reporting tools that may be building logicals (something like business objects or showcase vista) - this might be worth checking? Do you use any program generators/3GL’s/Case tools (synon/lansa/asset/etc) - these could also be building unnecessary logicals?

Many of the systems I’ve worked with (in all different sorts of business - banking/finance, manufacturing, retail, etc) don’t have 150 logical files on the whole system, let alone over one PF.

Your approach looks sound to me.

I’ve never gone down the road to convert to tables and indexes and I’m not sure what the benefits are of this, but reducing the number of logicals and the amount of data held in PF’s is bound to give an improvement in performance (as well as a reduction in used disk space).

Regards,

Martin Gilbert.

Dcantwell   165 pts.  |   May 26 2009  5:49PM GMT

@Phil:
Most of the records are really old. A history file system was just never implemented. That’s part of my idea to fix it. around 67 million of them are from 2005 on back. That’s about a 70% reduction if we do it.

I agree with you completely about the logical files. I’ve begun analyzing them and there are a lot of repeated keys. This biggest problem is the idea here goes like this: “Lets create a new logical so all we have to do is setll reade the data we want” Instead of compensating for it in the code. A ton of them have Select and Omits included in their definition.

So if a new report program was needed, the programmer made a new logical to make his/her life easier. Never mind the consequences. There’s also no one in charge of the databases (No DBA).

@Martin:
No reporting tools, no program generators. This is an insurance system I’m working with. We’re constantly audited and constantly changing to bring business in the door.

That’s why it’s going to be hard for me to prove that the time and effort spent on this will be worth it while continuing the support of the system. I believe a staged approach to this whole concept would work best for the business.

Overall, we’ve been told to avoid using SQLRPGLE for programs because the initial time you use the program, it’s slow. (In reality, it isn’t compared to using RPGLE with logical files defined each time). I took a program that used to run 5 hours, re-wrote it in SQLRPGLE and it runs in 20 - 30 minutes.

But, from some research, using SQL defined tables and indexes would fix the SQLRPGLE problem due to the fact that no real “indexes” are defined. If you look at the iSeries navigator, all the logical files right now are considered “views” and not “indexes”. <<<< Please tell me if I’m wrong.

WilsonAlano   2005 pts.  |   May 26 2009  6:57PM GMT

Hi Dave,

LF are real indexes. The difference between an SQL index and a LF is basically the page size (block of keys) used in each case. SQL indexes have bigger page size than LF and for sequential by key processes it’s better than LF. For random access the difference is almost zero and in some cases it’s worst.

You must be aware that SQL indexes doesn’t allow select/omit and, if the file will stay with several millions records in it after purge, may be a LF is your better solution. There a rule that if a LF with select/omit select 30% or less of records in the file, it’s a good a idea to keep or create one.

You can take a look at this article

Regards,
Wilson

WilsonAlano   2005 pts.  |   May 26 2009  6:58PM GMT

The link doesn’t work! Here it again.

 <a href="http://systeminetwork.com/article/performance-comparison-dds-defined-files-and-sql-defined-files" title="http://systeminetwork.com/article/performance-comparison-dds-defined-files-and-sql-defined-files" target="_blank">http://systeminetwork.com/article/perfor…</a>

Dcantwell   165 pts.  |   May 26 2009  7:32PM GMT

Thank you very much for the article Wilson. I think it’s an excerpt from
This document by Dan Cruikshank

This was the basis of my whole idea. But, as I look more into i don’t now how much it will benefit us immediately. The boss did like the idea of making our entire database “portable” to another system using the SQL generated tables.

One thing about the Select/Omit stuff after reading that article:

Dan did mention using DYNSLT when changing the logical files. This is supposed to help improve the access path.

JennyMack   3205 pts.  |   May 27 2009  5:02PM GMT

Hey Dcantwell,

Welcome to IT Knowledge Exchange. It looks like your question has created quite a dialogue here! Are you finding the information useful?

Jenny
Community Manager

Dcantwell   165 pts.  |   May 27 2009  5:16PM GMT

Hello Jenny,

Yes, everyone here is very knowledgeable and helpful.

Actually, funny I should be bringing this topic up. System i News just published another article about the topic by Dan Cruikshank.

Thank you,
Dave

Yorkshireman   3200 pts.  |   May 28 2009  8:04AM GMT

I’d suggest you need to look *very* closely at the business case for doing this.

Whilst it all makes sense from a technical point of view, given unlimited cost free resource, you are proposing to create an entirely new system which will require complete and thorough testing to much higher standard (probably) than the original code, and will therefore throw out all kinds of ‘bugs’ which were always there but now need fixing. This will taint your project.

Unless you have that unlimited resource, I’d follow the paths outlined.

First - get some good metrics together - how many files, records, disk space used. If you can gather some performance data by runing some ‘typical’ work under performance monitor, then do that.
Examine the code - how many programs, lines, File definitions (F lines) of what type of file access
Write a utility to extract the data from the LF’s and determine how many are duplicated, or could be consolidated, and what the criteria for the select omits are. Where are they used - someone said that you may find data extractions were being done by creating a LF.

Second - look at the archiving story - what can go, and how you preserve a means fo gaining access. How much disk real estate is regained, backup times improved, DR startup times improved.
you only mention 1 file - there must be more which are referentially linked - you’ll need a function of some sort - SQL/RPG that will extract the old stuff - into, say, a history library, and remove from live after a verification check.

When you have numbers, you can estimate effort and detail benefits.

88 million isn’t a big number for iSeries, and if it’s performance now is perceived to be ‘OK’ then why spend the money? - unless you can show cost reductions in ownership of the box and performance improvements which will reduce/remove/delay the need for upgrades. .

- just my thoughts..

Graybeard52   2355 pts.  |   May 29 2009  11:25AM GMT

Another tip. If you haven’t already done so, make sure the QAQQINI file has IGNORE_DERIVED_INDEXES set to *YES. This can improve SQL performance by 400% or more. In your case, any SQL query over this PF will never use the SQL engine, but the older (and slower) CQE engine, unless this value has been set.