System i Software

FileScope

Product Review

Database file editors are a necessity for the AS/400 programmer, and if you rely only on DFU or SQL to fix problems or make one off changes you are missing out on the fun. If you only use Query/400 to interrogate your data you are doing it the hard way.

Although there are a myriad of products on the market, each with similar ways of processing data, FileScope sits with the best of them.

Initial Menu

The two options that will be covered in this article are the Show File and Change File options, and a look at Viewpoints and what they mean to you. Extra security and enhanced usage traces are also available and have significant importance.

Displaying Data

Data is displayed in either list format or single record. In both formats the Control format at the top of the screen allows you navigate, position, and scan (list format only) records in your database. The list format displays both field names and field text in the header to assist in locating field data, additionally to which the field name format can be toggled to display either field length or buffer position which can be extremely useful.

Another view offers a split screen where 2 different files are displayed simultaneously, each capable of using all the product functions without interfering with the other. This comes beside the internal multi session manager which, enables the user to run up to 16 FileScope sessions simultaneously, and switch between them through an easy to use menu.

As you would expect, fields can be added or removed from view, as well as view sequence being changed. These features are very simple and subsequently very easy to get to grips with as they are not really any different from managing a query definition display.

The added fields can either be Calculated fields (including brackets), Fields based on function calls (A function is an external program. Users can add functions to the already supplied functions) or fields based on position in the record.

Subset and Sort

FileScope does, however, offer excellent methods to create Subset views. Structured Style method permits basic multiple selections using a field only once, Advanced allows multiple and/or statements similar to selection criteria in Query/400, and Free Style which allows entry of complex selection strings to be used within an OPNQRYF selection. This enables the shell of the query select to be built in one off the more simple to use functions to get the syntax right before moving on to more complex strings. The various selection methods can be toggled back and forth, but going from a complex string to the Structured Style could lose the selection of some of the unsupported criteria. Of course you are warned when moving between formats about the potential loss of string data and user confirmation is required when moving between formats.

Structured Style

Advanced Style

Free Style

Another powerful feature that provides ease is the capability to sort the data. The file is then referred to as if this was its original key.

A very cool feature is that once you have established your subset information, the associated select statement can be exported to a CL source member for use in other programs. Very useful for getting help with OPNQRYF syntax in CL programs.

Once a subset is in operation, OPNQRYF statistics can be displayed to give you a view of relevant information.

FileScope lets you define additional fields that can then be used with the display, change and print functions. This can also help you query and maintain files that do not have external definitions. Internal fields can be defined for files that do not have external definitions (DDS), as well as for files with external definitions. For files that contain large fields, you can break the fields into smaller ones. This will enable easier viewing and updating. The modified layout can then be kept for future use when exiting FileScope.

Internal Fields are fields that are defined using the 'From position' parameter. This denotes the starting position within the record. Internal fields which do not cross field boundaries (i.e. start and finish within one external field) can be used in selection tests for a SUBSET.

Calculated Fields are fields defined using either formulas or functions. Either a formula or function in combination with fields and constants can be used to define calculated fields. The functions supported by FileScope include date/time conversion, field concatenation, character and string conversion and IF logic functions.

Functions available are:

The IF, IFAND and IFOR functions can be used to return a value for a calculated field based on tests between one or two sets of values. The test operator(s) can be EQ, NE, GT, GE, LT or LE. There is a full explanation of the usage of these functions in the FileScope online help but a brief description and example is provided here.

The IF function allows a test to be made between two values. Each value can be either a constant or a field. The result of the test (true or false) determines which of another two values (fields or constants) are returned by the calculation.

Format: %IF (&value1 &test &value2 &true &false) Example: %IF (&PRICE GE 200 1 0)

The IFAND function returns a value based on the result of an AND relationship between two tests.

Format: %IFAND (&value1 &test1 &value2 &value3 &test2 &value4 &true &false) Example: %IFAND( &PRICE GE 200 &QTY LT 500 1 0 )

Format: %IFOR(&value1 &test1 &value2 &value3 &test2 &value4 &true &false) Example: %IFOR( &VEND EQ 50 &VENDCLASS EQ T 'YES' 'NO')

Join Files

With FileScope you can easily join 9 files to one query. The join is not made by use of the system join. Instead, common programming techniques make use existing indexes to join just records just for the displayed page. This entirely eliminates the delay and performance degradation observed when a system join occurs.

Report Generator

With FileScope you are not limited to just displays. A full featured report generator is also available to allow you to produce reports without programming as a similar yet more sophisticated alternative to Query reports.

Changing Data

The layout of displays when using the change options are similar to the display functions, except that when in update mode pressing enter on a record opens the fields selected in the view for update.

In a list view, records can only be changed, whereby in single record mode records can be added, changed or removed. Global changes based on subset or mass criteria can be made and run via FileScope's service menu, meaning that huge updates to data can be run simply without the need of programming. The ability to change a field to a value of another, which is a calculation of fields from several other files (including IF conditions) is what makes this option so useful.

When changes have been confirmed a single record is made available for entry of descriptive text. This is useful not only for audit purposes but for quickly identifying database changes to be revoked when using FileScope's undo feature.

The Undo feature is made possible by recording database changes in journals. For files which are not normally journalled, the product journals them to its own journal just for the duration of changes. Necessary for the Undo feature, but also useful for monitoring changes made at individual change level by providing a quick and easy link into the journal receiver holding the changes. FileScope enables revoking of groups of changes made up to 99 days ago without the need to revoke all other changes made since then.

Batch processing

All long running processes, including Global Changes, Report Generation and File Conversion can be submitted to batch instead of running them online. With pressing a function key rather then the standard Enter, you may continue working online while a batch job runs parallel.

It is also possible to run any preserved session (ViewPoint) directly as a batch job.

Viewpoints

A Viewpoint is a FileScope session preserved under a given name. The environment is saved, including all changes you make in field ordering, column headings and internal fields. Subset, Global changes, Report generation and File conversion definition are preserved as well. The Viewpoint session can be retrieved and modified at any time. Retrieving a file via a Viewpoint is quicker than a normal FileScope file access.

Viewpoints are especially useful for setting individual customized sessions for non-technical users. The end-user can simply select a menu option that contains a reference to one of two of FileScope's provided commands to either Display or Change data, with the appropriate pre-defined parameters including the Viewpoint. In this way users work only with the fields in which they are interested. Simple end-user reporting needs can also be handled using Viewpoints.

Viewpoints are also helpful for technical personnel who need to do repetitive complex scans, create and print test files, etc. and do not want to go through the trouble of re-specifying scan criteria, screen layout and print layout specifications each time.

These are very much FileScope's answer to DFU Programs and Query/400 definitions.

Summary

Under the covers of FileScope runs a solid engine with vast and wide capabilities. These capabilities are used in various areas to provide a wide range of functions. This cuts down the learning curve, because once you are familiar with one aspect, say defining a data subset, you can apply it for global change, report generation, file conversion etc. The wide variety of applications this product provides eliminates the need to learn and use several products.

Not tested in this review was the support for DBCS (Double Byte Character Set), Right to Left text and Accented characters. However, using DBCS is enabled by a parameter control, fields can be set to sort by value from right to left, and accented letters are said to be no problem.

This look at FileScope only mentions a few of the available features, but fully functional format, selection and sequencing criteria have left nothing to chance. Program described files as used by Sys/36 are not a problem. The file definition can be ported to DDS giving full functionality of the AS/400 to Sys/36 users.

FileScope has been enhanced to support export to PC Comma Delimited Files and Tab Delimited Files of the report generator output, file conversion and global changes result. This enables direct use of any such file on all PC software such as MS Excel, Word, Report generators and so on. The combination of the existing functionality, running in batch and providing the result as a PC file ready on the folder is another leading edge.

The User Guide documentation is very clear and presents a new user familiar with Query or DFU a learning curve of almost zero gradient.

There is a lot to explore. The pull-down menus have more options that are not best tried for yourself. You will soon find that FileScope takes qualities from many functions into one source location giving you a very sound utility.