10 More Reasons to Like Arbutus (61-70)
- Edit your SQL queries before submitting
- Field arrays
- Cross-platform procedure compatibility
- Sort / Index performance enhancement
- Date and Time access functions
- Fatal error procedure handling
- NOFORMAT output parameter
- EOMONTH() function
- User-selectable Procedure Editor colors
- Native mode iSeries (AS/400) Server
Note: The following 10 More Reasons to Like Arbutus are aimed at technical or advanced users. Questions or comments? Please contact us.
When submitting data queries through ODBC, you can edit the SQL to be submitted before it is sent. This allows you to access specific capabilities of the data source that may not be supported by Arbutus. You can also implement custom SQL queries that are beyond the capabilities of the user interface to specify.
Often data files contain sets of fields that are identical, except for the data they reference. A common example might be the 12 monthly budget amounts in an accounting file. When your data has this characteristic, Arbutus allows you to define an array field, so that you don’t have to define each instance individually. You can then refer to any of the fields in the array individually. This supports:
- Arrays of fields that are physically adjacent, like Jan_amt, Feb_amt, Mar_amt, …
- Arrays of structures that repeat, like code1, date1, amt1, code2, date2, amt2, …
Arbutus specifically supports procedures that will operate transparently on any platform. You can create a procedure or process on your desktop and then have it run unchanged on any of our supported server platforms (e.g., Windows, MVS/zSeries, AS400/iSeries). This is a great boon when developing new applications.
Modern desktop computers and servers have much larger memory than computers of just a few years ago. To maximize performance, Arbutus has optimized its Sort and Index operation to take advantage of this expanded memory. In addition, Arbutus also provides the ability to dynamically change the memory allocation rules to allow further optimization in special situations.
Arbutus allows you to inspect any component of a date, time or date-time field, so that:
- Year() returns the year portion of a date-time as a number, like 2012
- Month() returns the month portion of a date-time as a number, like 12
- Day() returns the day portion of a date-time as a number, like 31
- Hour() returns the hour portion of a date-time as a number, like 23
- Minute() returns the minute portion of a date-time as a number, like 59
- Second() returns the second portion of a date-time as a number, like 42
These values can then be used as required by the processing steps.
Accidents (or bugs) can happen. As a result, sometimes procedures don’t operate as expected. With Arbutus, you can trap processing when an error occurs, through the SET FATAL command. This command allows a variable response, based on the needs of the procedure. For example, on error, you can:
- Have Arbutus end, and return a specific value to the calling program. This is most useful in situations where Arbutus is being called as an “engine” by some other application.
- Return from the currently executing procedure and continue with the calling procedure as if the error procedure completed successfully. This is most useful in situations where a (large) number of independent tests are being run, meaning a failure of one test won’t affect the other tests in the run.
- Automatically run a specified “clean up” procedure. This is most useful when there is some possibility that the procedure can recover from the error, or where additional steps should be performed in the event of an error (such as emailing an administrator).
Particularly for ETL processing, it is quite common to want to refresh the data, but not to affect the table definition, which may contain computed definitions or other business rules.
This is where the NOFORMAT parameter comes in. Any of the commands that create data files will accept the NOFORMAT parameter. What it does is create the data file without creating a new table definition. This allows the ETL process to update the data without disturbing the remainder of the environment.
The EOMONTH() function is a copy of the equivalent Excel function. This returns a date-time that is equal to the end of the month for the specified date-time. It is often required for specialized calculations.
All of the colors in the Procedure Editor are user-customizable. This is particularly useful in situations where you have pre-existing expectations about what colors to use, or where certain colors do not show up well on a particular display device.
This native mode (no emulation) iSeries server gives access to all the power of your iSeries environment. Not only can you access DB2, but you can also read flat files, IFS files, and spool files. The Arbutus iSeries server provides secure, read-only access to all of your data, regardless of type. Security of data is maintained at all times using standard login protocols.
- For more information, visit the Arbutus Servers page.