Programmers and administrators working with the iSeries, especially with its database, need to know lots of OS/400 commands. While this works for long-time RPG programmers and iSeries operation gurus, developers or administrators from different worlds, like Linux or Windows, are faced with a system that feels a bit cumbersome. Surveyor/400 from Linoma Software could be the appropriate solution for them. This client/server product gives a nicely structured GUI for doing common tasks when working on iSeries projects.
Surveyor offers many features that help programmers and administrators handle the iSeries. There are many functions that provide assistance with the DB2 Universal Database for iSeries. This is perhaps the most fascinating part for the developers out there. Accessing the IFS, the iSeries' file system, is another major function, especially when running a server on your system that operates on text files. A spool file manager, for viewing and exporting iSeries spool files, and a Data Area Editor are also available in Surveyor/400 to reduce the need to turn on the 5250 terminal emulation for the iSeries, which is, by the way, also part of the product. Another important fact is that the client of the product can run on any operating system that offers a Java Virtual Machine. You can add as many iSeries systems as you want; they would be displayed in the tree as first-level nodes under AS/400 Network (Figure 1).
Accessing and managing the iSeries DB2 database is a common task for many software project teams using the iSeries. A lot of these projects, at least Java-based ones, are driven by engineers who are not exactly enthusiastic about the green-screen world of OS/400. For them, creating or changing the database tables via DDL, or changing the tables content with UPDDTA, is perhaps not the best solution. In fact, without any guidance from iSeries experts, these techniques may not be possible at all. Remember that the object-oriented-files system of the iSeries is something outsiders won't understand too quickly.
Before we look at how Surveyor structures the DB2 database content, review the following table, which maps iSeries terminology with common SQL terminology, because the screen shots and my personal wording in this article are iSeries flavored.
|iSeries Terms||SQL Terms|
|Library: Groups related objects together and allows you to find the objects by name.||Schema: Consists of a library, a journal, a journal receiver, an SQL catalog, and optionally a data dictionary. A schema groups related objects and allows you to find the objects by name.|
|Physical file: A set of records.||Table: A set of columns and rows.|
|Record: A set of fields.||Row: The horizontal part of a table containing a serial set of columns.|
|Field: One or more characters of related information of one data type.||Column: The vertical part of a table of one data type.|
|Logical file: A subset of fields and records of one or more physical files.||View: A subset of columns and rows of one or more tables.|
Figure 1 shows how you can navigate in your database with Surveyor.
You can see the different libraries that are in the library list for the current connected user. It is possible to change the library list inside the software to suit your needs. When clicking a library, as done with CEBITLIB, you see the physical files together with all associated logical files. You can examine the tables by clicking Fields or Formats in the tree. Non-iSeries users and developers should remember a physical file is just a database table on the iSeries.
One of the most common tasks for developers is to fill a table with some kind of test data and run applications against this table. Developers were forced to use iSeries tools like UPDDTA, a native application used inside a 5250 terminal session. This tool does not come free on the iSeries and must be purchased with the 5722-WDS licensed program. It is not what developers from other systems expect to use. This is where Surveyor/400 will play its strongest card; it has a very intuitive and elegant table editor.
The table editor comes with a bunch of useful features. You can filter records based on customized queries and supply a custom ordering. The layout of the data display can also be modified in a way that the user selects the fields he wants to see, with a useful function that protects fields against manual changes. The layout can be saved and accessed directly from the tree, as you can see in Figure 1, where a node of the tree has the label Layouts. This provides extremely fast access to a customized table editor. Exporting selected rows, or the complete table, with different output targets is also available, along with some other minor features.
Another feature expected in such a product is an SQL editor. This replaces the native iSeries variant, called STRSQL. For those who are a little bit lazy or don't want to remember each SQL statement, Surveyor provides an SQL Wizard with form-based SQL creation. A Procedure Editor for creating stored procedures completes the picture of the SQL helper tools available. Developers will appreciate this easy and fast access to the iSeries database world.
Accessing the IFS is crucial in any project where the software will be deployed on the iSeries itself. IBM is strongly pushing the WebSphere platform on the iSeries, with its WebSphere Application Server at the heart of the system. If you have ever worked in Java 2 Enterprise Edition (J2EE), you will notice that modifying various deployment descriptors, which are, in fact, XML files, is a daily business. Also, Tomcat, the most popular servlet engine available on iSeries, has some descriptor files. So one major concern is how easy it is to edit text-based files on the iSeries. Figure 2 shows the main entry point when working with the IFS.
Copying files to the iSeries can be done by clicking Copy from Workstation in the context menu. You might wonder if you can drag and drop files into Surveyor/400 for more intuitive usage. The answer is no. This is one thing Linoma has to work on. Users have to use a file-chooser dialogue after selecting the highlighted menu item.
Editing files is quite easy. The context menu provides an "Open" item, where you can choose if you want to open via ASCII or EBCDIC. Surveyor provides a file editor that is good enough to handle most of the work. It compares in functionality to Notepad, the well-known Microsoft Windows editor.
There is a lot more that Surveyor/400 has to offer. Most of these features are more interesting to iSeries administrators or operators than programmers working on projects, at least when they are Java-based. Nevertheless, they are important to mention in order to get the big picture of the product.
One really nice feature is the Spoolfile Manager. It replaces the Work with Spooled Files (WRKSPLF) command on the iSeries and adds valuable functionality when printing or exporting the contents of a spool file. You know the pain when your e-mail system is PC-based and a vendor needs the content of a specific spool file via e-mail, in order to check an error of his software on your system. With the Spoolfile Manager, this is no problem. It is accomplished in a matter of seconds.
The Import and Export features of the product are very mature. Physical files can be exported in various formats and also imported based on delimited and fixed-width PC files. You can even map the data to the fields in your physical files.
As frequent releases show, Surveyor/400 is alive and well. In the next couple of releases--Linoma officials say in a timeframe of about three months--we will see more features that will make the 5250 interface obsolete for handling the database. You will be able to right-click a physical file and do things like compile-PF (replaces CRTPF on 5250) or edit-DDL. In the long run, Linoma plans to eliminate the need for STRPDM on 5250.
If you know the iSeries, you might ask if IBM's iSerires Navigator (formerly Operations Navigator) can do the same things equally as well. From a developer's perspective the answer would be yes, but not as well. The database management of iSeries Navigator contains only a fraction of what Surveyor/400 has to offer. The easy modification of table content is not possible, and the overall speed of iSeries Navigator is not as good as Linoma's product. But it's not fair to compare these two products, because they are positioned differently and have only minor overlapping of features.
If you really want to administer the iSeries from a PC client, with full access to the servers and services, iSeries Navigator might be the better choice, but when it comes to work a programmer has to do, Surveyor/400 is a good replacement for the 5722-WDS package for iSeries, which, as I mentioned, is not free. The rights management lets you define what users can do with Surveyor/400, and the file-searching capabilities, with criteria like "not used tables since 30 days," are awesome. When working with the product, I did not find a single bug. The product, which has been under development since 1998, is very stable.
The single-user license for Surveyor/400 on any iSeries processor group is $495. Multi-user licenses start at $995 for a P5 processor group and continue up to $3,495 for a P50 processor group. That's not a lot of money for a tool that will save a lot of time in projects and everyday tasks.
Marc Logemann is a senior e-business consultant at Spirit/21 AG, a German consulting company that focuses on iSeries services and development. Marc is involved in Java- and PHP-based open-source projects and likes reading books about new technologies. E-mail: firstname.lastname@example.org
Surveyor/400 is a graphical database utility that claims to be easier and more efficient to work with than legacy applications. Written in Java and running on any machine with a compliant Java virtual machine and a TCP/IP connection (which includes, but isn't limited to, Windows 9x and NT/2000) it promises to make file viewing, editing and exporting easier than existing AS/400 applications like DBU and WRKDBF, and the graphical facilities in Operations Navigator.
I’m normally pretty skeptical of graphical applications that are totally dependent on the AS/400 for data. Performance is often comparatively poor, making it quicker to do the job on the AS/400 using native facilities. With that bias in mind, lets look at what Surveyor has to offer.
Installation is extremely easy. The CD runs automatically and prompts you to install the PC code. Firing Surveyor up for the first time asks for the initial AS/400 machine and sign-on details and then automatically downloads the required AS/400 code. This is a model that other software suppliers should adopt!
Taking updates to Surveyor is equally easy. Take the Web Updates menu option while connected and you see a list of updates available. You then have the option to download them. Once downloaded, you just need to restart Surveyor and the updates are in place.
If you’ve got multiple users of Surveyor, the update received from the web can be published to the AS/400 via a menu option and other users are then prompted to upgrade from the AS/400 the next time they start Surveyor.
Once installed, opening Surveyor presents a screen showing configured AS/400s in a standard tree display. The tree can be expanded to show AS/400 libraries and the Integrated File System (IFS). These can then be further expanded in the case of libraries to files, then fields, formats and members. For the IFS, you can expand directories. Individual IFS files can be viewed as text or images but can’t be edited.
Expanding the lists can take some time, especially if there are a lot of files in the libraries. On my setup (an AS/400 model 170 with a 366Mhz PC attached by fast Ethernet) it took about 2 ½ minutes to expand a library containing 1150 physical files and at this stage I was thinking that all my prejudices were about to be confirmed. There’s no facility to cache the library contents on the PC, and going through this delay every time I started Surveyor would make it largely useless for me. Linoma has thought of that, however, and there’s a fast path option (the lightning flash on the toolbar) which takes you straight to a dialog allowing entry of a single file name and quick access to the most used features. We’ll look at this later.
On the way through, expanding the lists, right-clicking on each of the levels reveals a number of options which weren’t immediately apparent. For example, right clicking on a system icon takes you into the configuration for that AS/400. From here you can, among other things, run a command on the AS/400; run an interactive AS/400 session using a basic but adequate version of telnet that doesn’t require Client Access or any other emulation software; look at system properties; set the library list; and configure user access security. The user access security is quite powerful and runs in addition to any AS/400 authority. Users can be set up to be able to view but not update data, or to have single record but not bulk update authority. SQL can be limited to no access, queries only or query and updates etc.
Right clicking on a library gives options of file search, object search, properties and FTP. The properties option includes being able to see the true size of a library (the size of the library plus the size of its contents), and then details of the physical files, logical files, procedures, DDM files, authorities and locks all specific to the selected library. All of the lists can be sorted, the columns rearranged or excluded, exported to a variety of formats or printed at the click of a mouse.
Right clicking on a file gives options to edit (which I’ll come back to), view the properties, export, generate DDS, add to a group or FTP. The Properties button takes you to a tabbed dialog allowing access to more than you ever wanted to know about the selected file. The General tab shows mostly the same information that you’d see from a native AS/400 DSPFD command – the name, type of file, maximum size etc. The Object tab shows the equivalent of a DSPOBJD command, with creation and save information. The fields tab is the equivalent to a DSPFFD detailing fields and their attributes including field reference information. In any of these listings, standard options are available to export the contents to the clipboard, to various file formats including HTML and CSV, to print the information or to sort or search. You can also exclude or reorganize the columns of information shown. Display of each of the sets of information is at least as fast on my setup as using the native commands. Other tabs include Database relations showing any logical files that may be based over the current physical file, keys and select/omit criteria, triggers, constraints, authority, formats and members. You can also view locks on the file and there’s a rather neat option to be able to send a message to whoever has the file locked, and to display the DDS. If Surveyor can’t find the DDS it can generate DDS for you, including field reference details where found. This can then be saved to the AS/400 if required – great for those “oops” moments. The final tab allows a preview of the file in a standard grid layout, showing by default the first 100 records (the default is configurable). Again this can be exported, sorted, printed, etc.
The Export option allows you to export the whole file from the AS/400 to either comma-separated, tab-delimited or HTML formats on the PC. Generate DDS works in the same way as from the properties dialog, creating DDS for you from the object. Adding to a group lets you create groups of often-used files, which can come from multiple libraries or multiple systems. These groups can be accessed from a button on the toolbar and are a quick alternative to expanding the file listings for one or more libraries. You can also track changes to files sizes within groups, as a number of bytes or percentage. FTP allows you to FTP the file to another AS/400, automating the creation of a save file in the process. The FTP can be done interactively or in batch, and can be scheduled to run at any date/time.
The edit option opens up with a grid display of the file (see right) by default, showing descriptions from the field’s column heading. You can choose to have the field name instead if you prefer. Letting the mouse rest over one of the headings pops up a window with details of the field name, size etc. of the field. Columns can be moved around by dragging them with the mouse, or a right click lets you hide a column, print the page, export the page (all the usual options plus the clipboard), find a value in the column or do a mass-replace . A ‘page’ in Surveyor terms is just a block of records that are retrieved from the AS/400 in one go. The number of records in the block is customizable. If you don’t want to use drag and drop to rearrange fields on the screen, you can go to the layout dialog which will let you add or remove a number of fields at once, reposition fields, protect fields upper-case them etc. Layouts can be saved and accessed directly from the main Surveyor screen. Saved layouts can be private or available to everyone. You can also use a filter and/or a sort, which will be saved with the layout with up to 99 combinations of select and all the comparisons you’d expect including ‘contains’. Field names, comparison operators etc. are all available in drop-down boxes.
If you’d rather see one record at a time, you can click a button and go into single record mode, or set this to be the default. In either case, changing an existing value is just a case of moving the cursor to the appropriate field and using the standard windows edit keys. If the record has been changed by another user while you’ve been viewing it, Surveyor tells you and reloads the affected record; you can review the changes and if necessary, redo yours. Fields can be viewed and edited in hex if required.
As well as changing a record, Surveyor also lets you copy, copy with prompt, add and delete. The Add has a neat feature that lets you indicate fields whose values are to be carried forward for the next add as a sort of template.
In the edit screens you can use the VCR-type buttons to move around the file, or you can position to by key or relative record number depending on whether the file is keyed or not.
From the Surveyor toolbar there are a number of other buttons available. The SQL button takes you to the SQL entry screen – this allows entry of interactive SQL statements and will retrieve the default number of records. The retrieved records are displayed in the standard record grid, and this can then be exported, sorted and printed. It’s great for when you know there’s only a small number of records to be retrieved, but there’s no way for example to page past the default number of records. There’s also no prompt facility for fields, you’d have to have another Surveyor window open with the field definitions if you didn’t know them. SQL statements are saved and can be retrieved.
The fast path option (the lightning bolt) gives you quick access to any file without having to go through the potentially time-consuming navigation through system and library. It presents a dialog (see right) allowing direct entry of an object and optionally it’s library. You can then go straight to the most common options of Properties, Edit, Export, FTP, Add to group (although as the fast path maintains a list of the most recently used files I have not made much use of the group facility yet), Generate DDS and SQL (the SQL screen is pre-loaded with a ‘select *’ for the entered file if you go in this way). In practice I ended up using the fast path option for most of my interaction with Surveyor.
The last two options are file and object search. These are similar in that they allow a search across a wide variety of criteria, for example I could search for all non-system physical files not used in the last year that are bigger than 500Mb and have at least 5% of deleted records, with the string ‘customer’ in the text. This obviously takes some time to run, but setting the search up is significantly easier than trying to do the same thing on the AS/400 would be. The list is displayed in the now familiar grid and can be sorted, rearranged, exported etc. Using similar searches I found a number of large files on my system that I wasn’t aware of and managed to reclaim a significant amount of disk space.
The object search is similar, but allowing searches on object-related attributes. Once retrieved, right clicking on any object takes you straight into a tabbed properties dialog showing the equivalent of a DSPOBJD, authority, locks and the source if it’s available where the object description says it should be. You can also FTP the object.
Immediate development plans for Surveyor include being able to do a lot more AS/400 management type-things to the files, such as deleting or reorganizing files, tighter integration with the built-in emulator and more work on SQL.
In day-to-day use I was pleasantly surprised at the response times. Being able to have multiple windows open while programming with the fields for the files I was using was especially helpful. I got into the habit of right-clicking everything as options I wasn’t expecting often turned up. The file and object searches are in regular use to keep an eye on storage usage, and they were a pleasant surprise in something I was just expecting to edit files.
I have a couple of minor gripes. Startup time could be improved. Initial screen load is pretty fast, but the first time you try to access an AS/400 it goes through update checks that cause a niggly delay of around 40 seconds on my setup. Removing the update check via the configuration option drops this down to a much more acceptable 10 seconds, but you’d have to remember to enable it every so often to pick up any changes. Once this has been done once, the rest of the interaction is fine, and often quicker than the native tools. I ended up starting Surveyor first thing in the morning and looking at a file on each of the AS/400s to get through the initialization, then response was snappy for the rest of the day. The other thing I would like to see is support for the wheel on the mouse. In all, my experience with Surveyor has been very positive and it’s been in daily use since we got it.
As far as I know, I was the last confirmed DOS user. When everybody else was using Windows, I still preferred my command line interface. I knew what the function keys did. The first line in my autoexec.bat file loaded doskey. Then my boss took away my terminal and replaced it with a Windows machine, and I had no choice but to use it. I'm better off with a GUI, but I confess that I occasionally start up a DOS window because I know how to do something more easily with DOS, or I don't know how to do a task with Windows. For example, can anybody tell me the Windows equivalent of C:\>ren *.dta *.txt?
As much as I hate to admit it, it's time to put the OS/400 command line to rest and control the AS/400 with a GUI. I'm resisting with all my power, but I don't think I'll be able to hold out much longer. Not since I've seen Operations Navigator (OpsNav) from IBM and Surveyor/400 from Linoma Software.
You probably know something about OpsNav. We've written about it quite a bit already in AS/400 Technology SHOWCASE and Midrange Computing. Chances are you don't know much about Surveyor/400, so let me tell you a bit about it.
Surveyor/400 is a GUI interface to the AS/400's database management system, DB2/400 UDB. It looks a lot like Windows Explorer. That is, it presents two main panels. The one on the left is a structural diagram organized in a hierarchical fashion. The bigger one, on the right, shows the contents of the selected object in the left panel. The top of the screen is adorned with the usual stuff-title bar, menu bar, and toolbar.
Let me tell you about the left panel. The outermost level of the hierarchy is a list of AS/400s. Within that is a list of groups-the Database, the AS/400 Integrated File System (AS/400 IFS), and Layouts. You can expand Database and IFS to a third level (and beyond), but what you see differs for each group.
Expanding the Database icon brings up a list of libraries. Expanding a library brings up a list of three file types-physical, logical, and Distributed Data Management (DDM). Expanding physicals or logicals brings up a list of file names. Expanding a file name brings up icons for fields, formats, members, logical files (if the file is a physical file), triggers, constraints, and journals.
As with similar software, such as Windows Explorer, what the right panel shows depends on what you've double-clicked in the left panel. For example, if you double-click on the Members folder under a physical file, you see a list of file members.
So much for getting lists. The important thing is what Surveyor/400 lets you do with them. Right-clicking on a name (a file name, a file member name, a library name, etc.) brings up a popup menu. One of the choices in that menu is Properties. You can find out almost anything you need to know about the files in your database from the Properties window.
This feature gathers in one place all the information returned by OS/400 commands like Display Object Description (DSPOBJD), Display File Description (DSPFD), Display File Field Description (DSPFFD), and Work with Object Locks (WRKOBJLCK).
But Surveyor/400 goes beyond these commands to show you even more information. For example, there is no OS/400 command to show you the source code from which a file was created. But Surveyor/400 has that information readily available. Have you lost data description specifications (DDS) source code for a file? Surveyor/400 can recreate it for you.
Surveyor/400 includes an editor that allows you to make instant changes to a file in an easy-to-use, spreadsheet-like grid. If you want a good laugh, compare it to DFU sometime.
Surveyor/400 has two easy ways to transfer data from an AS/400 to another machine. You can export files to a client in various data formats, for example, comma-separated values (CSV), or you can use FTP.
Surveyor/400 includes an SQL query facility, which lets you dynamically execute SQL statements. You can restrict a user to data retrieval, or allow him to execute insert, update, and delete operations as well.
Surveyor/400 also includes a 5250 emulator. It currently allows access only to OS/400, but soon will also permit users to run certain Surveyor/400 components (e.g., the database editor and properties displays) from an OS/400 command line.
Surveyor/400 does not handle operations functions-creating a file, moving a file from one library to another, deleting a file, adding and removing members, etc. I should say it doesn't handle them yet. Those features and more are in the works and should be available before summer is over.
Many functions are available in more than one way. You'll find many of them available by right-clicking the mouse. Of course, they're on the menu bar. And the Surveyor/400 panel includes a toolbar that accesses certain functions.
It's easy to try out Surveyor/400 in your shop. Direct your Web browser to www.linomasoftware.com and proceed to the Surveyor/400 page. There, you'll find a link that allows you to download the latest version. There are two client versions available-one for PCs, the other for everything else. The client side of Surveyor/400 is written in Java, so it runs on any machine with a Java Virtual Machine (JVM).
Once it's on your PC's hard disk, install it as you would other PC software by running Setup.exe. If you're not using a Windows-based system, follow whatever convention you use to install software on your machine.
Once installed to the client machine, start up Surveyor/400, configure an AS/400 connection, and attempt to connect to that AS/400. Surveyor/400 will tell you that it cannot find itself on the host and ask you whether or not to install it. When you reply in the affirmative, Surveyor/400 installs itself to the AS/400 as a licensed product. You'll have 30 days to use Surveyor/400 without having to pay for it. At the end of the trial period, you must register it to continue using it.
Surveyor/400 is a growing product. The people of Linoma Software are working on a long list of enhancements to their software. I mentioned already that object management enhancements are on the way. Soon, Surveyor/400 will be able to copy files between a client machine and the AS/400 IFS, as well as between DB2 and the AS/400 IFS. Many more SQL enhancements are also on the way. Linoma Software welcomes customer feedback, and seriously considers customer input when enhancing Surveyor/400.
A business machine is only as good as its database management system. The tight integration of DB2/400 with the AS/400 hardware and language compilers makes the AS/400 the fantastic business machine it is. Surveyor/400 is a good complement to DB2/400 and can make this database management system (DBMS) even easier to care for.
Take the Surveyor/400 Challenge and you will see that Surveyor/400 is the ULTIMATE database utility! That's what the marketing release stated. So with a blurb as good as that - who could resist?
The marketing example gave 5 simple tasks to complete using a demo file, but to put the software through its paces, I decided to use my own examples.
Ok, just another database utility? Not really. This one is purely graphical using Java to the best of its capabilities that I have seen so far.
Ok. another GUI "boy" sent to do a "man's" job then? No its not just that either. The interface brings together just about every attribute inspector related to database files. And then some.....
Back momentarily to the marketing sheet. Surveyor/400 claim that Surveyor/400 is more efficient and that the tasks could be worked through in ten minutes. True that working with files is more efficient in that all the utilities are grouped in one place and there is no dashing in and out of menus/qcmd/.programs to get where you want to be. And that I like. But the initial load time on an under-worked 150e (Mini-Me!) and a token ring MAU (given to me by Noah!), ran its little processor off before returning control to the PC. This is not a gripe. I know I don't have the kit used in most production environments, but I was left with 4½ minutes to complete the Challenge. Must say though - that once in it goes like stink!
So now we are into Injury Time!
Well the front end is in the familiar Navigator style. Rich in features (selective library list, naming conventions, log files, TNT5250 etc.) that make it fun to play with and easy to navigate through.
But the real fun starts with the Fast Path facility, accessed by the "Lightning Strike" icon pictured below.
Now this little fellow gives a wealth of utilities at your fingertips! The Fast Path will let you:
From this point you could just about do whatever you wanted, but there is much, much more attached to the properties tab:
And it doesn't end here! Built in to the product is a Query front end that allows the selected file to be Queried and/or Sorted. Note that it is possible to place up to 99 conditions, with parenthesis and and/or support to build up an impressive selection criteria.
Output from the query can be exported from Surveyor/400's own Spreadsheet utility, or simply "cut & pasted" into your favorite spreadsheet.
And guess what? From the neat query interface, it is possible to update records back to the database with a built in "Replace" Function.
So, it's a Database Query Tool?
It is, but not just for querying data. It queries objects too! The Find Function from the main Surveyor/400 menu allows you to search Objects or Files by all the usual selection criterion.
The result was fast and allows sorting of the result before export to another format or pasting into Excel (or whatever!).
The file search is similar. In the example below, I haven chosen to inquire on my favorite file selector, Deleted Records. My system is clean having just re-orged the lot, but the two files that I have with deleted records were located in under 7 seconds. Cool or what?
Now I am hooked! Give me more!
I have saved the best till last with this! I have to quote the Marketing sheet for this, scenario Number 5:
"You need to test a new enhancement you've made to a program and you want to copy real data from your Production AS/400 down to your Development/Test Box (LUXURY! - DG). The file is rather large and you don't want to cause unnecessary traffic on the production box, so you choose to copy the files after normal business hours. How would you perform this task using your legacy applications?"
When I saw this product I first noticed the FTP button, and thought that that could be useful, especially when I saw the "Use Save File" button.
But get this, you can actually schedule FTP transfers. At first I thought that I was unable to test this feature, as I a only have one AS/400 in my network. But this is not the case. The FTP function will allow the scheduled transfers from one file to another on the same box! This is a valuable asset for scheduling saves or copy functions from one area to another, be it a remote system or a separate library. Well done Linoma!!
Back to the Marketing form for one moment.
This document states that the future of the AS/400 lies in Graphical Software.
Times, they are a Changin'. As Java sweeps us up and embraces those API's to deliver fast and reliable utilities to make our life's easier. Up until recently, I thought that GUI's were there to impress the people with the buying power, whilst the troops battled on with old faithful 5250 emulation.
This week I was proved wrong.
With all the database utilities on the market today, finding a package that suits your future and current needs can be difficult. In addition, many of the utilities on the AS/400 market are laden with features that make finding the right one even more difficult. Previewing Linoma Software's Surveyor/400, however, made the choice easy for me.
Surveyor/400 displays your AS/400 database in a convenient tree format.
Surveyor/400 provides a graphical view of AS/400 databases, giving you a point-and-click interface to accomplish daily tasks associated with database management and design. The design of Surveyor/400 is well-thought out and simple to navigate. In addition, Surveyor/400 is a pure Java application and can be run on any client that has a compatible Java Virtual Machine (JVM); it requires only a TCP/IP connection to an AS/400.
Like Windows Explorer, Surveyor/400 presents a tree view of your AS/400 database (see Figure 1). With this interface, you can dig into your libraries, physical and logical files, file members, record formats, and stored procedures and view information on triggers and referential integrity constraints. You can also build a freeform SQL statement for a narrower view of your data. Surveyor/400 even comes with a file editor that gives you hands-on access to data for quick changes or additions.
Whether you have Surveyor/400 on CD or download it from the Linoma Web site at www.linomasoftware.com, installation and setup are simple. The only parameters you need to log on automatically are the AS/400 name, the IP address or host name, and a user ID and password (which are optional). Once connected to your AS/400, Surveyor/400 asks you to load the server portion if it is not already installed on your AS/400. The utility requires no user intervention, provided the proper IP services are running.
Surveyor/400 loads onto the AS/400 as a licensed program product and utilizes the software-licensing features of the operating system. After the provided 30-day trial period expires, you need a vendor-supplied license key to run the utility. The product is licensed to the AS/400, so you can install it on as many clients as you desire that connect to the licensed machine.
You can also set up Surveyor/400 to fit your particular tastes as well as your specific needs. Options you can modify include customizing the library list or tree view displayed, setting naming conventions to use AS/400 or SQL standards, displaying tool tips, and setting a window style (Windows standard, motif, or metal). Surveyor/400 fully extends the security already in place on the AS/400 and enables you to grant or revoke access to functions provided with Surveyor/400. An administrator supplies the AS/400 account and selects access rights for the account you wish to grant or deny specific functions. You can configure security for the file editor either to allow access to the database for editing data or viewing only or to allow no access at all.
Surveyor/400 provides a wealth of information that is easily accessible through its visual tree by right-clicking on an object and selecting Properties from the popup menu. The program then presents a detailed view of information that would take several commands and keystrokes to accomplish on the AS/400 itself.
For a library, you can see much of the information shown from the visual tree, such as physical and logical files contained within as well as stored procedures. Authority to the library is also available along with any current locks that may be in effect. Other nifty pieces of information you can view are the size of the library and number of objects contained within it; they're just a mouse click away.
The Properties view for a file appears in the same manner as it does for the library and contains the same information. In addition, you can view members, formats, fields, and any constraints or triggers bound to the database. Surveyor/400 can display the DDS for the database and even generates it for you if it cannot find the source to display. It also allows you to save the DDS on your AS/400 in a file and library of your choosing-an option that can be very useful.
Surveyor/400 offers a couple of ways for you to peek at the records inside your database. You can preview the records in the database from the Properties window of the file. The Properties window shows you the first 100, 500, or 1,000 records in the file (depending on your preference). You can look at the entire database by using the *NOMAX setting, but that setting fetches all the records at once, making this a long process to complete for a large database.
SQL is another option you can use to view your databases. By typing a simple Select statement, you can retrieve a view of your database that is currently limited to the first 1,000 rows. You can also sort the results (ascending, descending, or default) by right-clicking on the column header and selecting Sort and your desired option. (I did not perform anything more elaborate than a simple Select statement.) You can perform an Update and Delete statement, but Help says that it is not supported. Bug or feature? The choice is yours.
Surveyor/400 supports file exports to common PC file formats. Exporting a database to a comma-separated value (CSV) or tab-delimited file format allows you to further examine the data in your favorite PC application, whether Microsoft Access or Excel. HTML is another supported format that allows you to create tables for placement on a Web page.
The program's file editor gives you quick and easy access to your data. The view is like that of Access or Excel-a familiar interface to work with (that is, if you use those products). To edit a file, simply right-click on the file object within the tree view and select Edit. A file may also be selected from the toolbar by clicking on the editor button and entering the file, library, and member name to be edited. You can select a different system if Surveyor/400 is installed on more than one.
After you have opened the editor, a view of the records (or rows) in the database appears (see Figure 2). You can set rows to show single or multiple records, and the field description can show either the actual field name or the text description for that field. In addition, you can configure a default edit mode for View, Edit, or Add. All these options are also accessible from the toolbar for changing on the fly.
Surveyor/400's editor displays a view of the records in the database.
You can even customize a layout for more specific viewing or editing. You can designate fields to be hidden, visible, protected from input, or displayed only in capitals. When in Add mode, the "carry-on add" feature can replicate or carry over the input from the previous entry for specified fields. You can then save the layout for use by everyone or hide it so it gives access only to you.
Clicking on Filter from the toolbar allows you to limit the view of the database and change the default sort order. By default, Surveyor/400 sorts the data as defined in the file. You can define a custom sort to let the user select the fields and order of data (ascending or descending) to be displayed. You can also select no sorting and simply display the file by relative record number.
The query portion of the filter works much the same way as defining a query through Query/400. You select a field along with an op code (*EQ, *NE, *LT, *GT, *GE, or *LE) and the value with which you are comparing it. You may also enter multiple statements and join them together by using a supplied *AND or *OR field.
Surveyor/400 offers features for searching and monitoring many aspects of your databases. A basic search allows for finding files that match a given name (fully specified or generic) and library. You can limit a search by any number of the different options provided to give as broad or narrow a list as you want. Search criteria include date range, type of change made to a database (created, saved, or not saved), size of the database, who created the database, and even percentage of deleted records contained in the database.
Database monitoring lets you track changes to or growth of databases over time.
You can group files together to give a beginning reference date and size for each file to be watched. Further viewing shows the percentage of growth since the beginning reference, allowing you to make informed decisions about your databases.
As you can see, Surveyor/400 provides an abundance of features for managing your AS/400 data. The product is easy to install and use and gives first-time users instant access to database information without having to browse through documentation to get started. The program is tier-priced by processor group. (Prices are subject to change upon next release.) You can receive updates through the Web via a menu option, and they're implemented the next time you start Surveyor/400. If you are looking for a database utility rich with features, Surveyor/400 is the place to start.