home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
ftp.robelle3000.ai 2017
/
ftp.robelle3000.ai.tar
/
ftp.robelle3000.ai
/
changes
/
supr4p7ux.txt
< prev
next >
Wrap
Text File
|
2005-07-19
|
68KB
|
1,854 lines
SUPRTOOL Version 4.8.01
Database Handyman for HP-UX
Change Notice
Installation Instructions
Addendum to 4.6 User Manual
Suprtool 4.8.01
Suprlink 4.8.01
STExport 4.8.01
Dbedit 4.8.01
Suprtool2 4.8.01
Robelle Solutions Technology
Suite 372, 7360 137 Street
Surrey, BC Canada V3W 1A3
Phone: 604.501.2001
Fax: 604.501.2003
E-mail: support@robelle.com
Web: www.robelle.com
April 2004
Program and Manual Copyright Robelle Solutions Technology Inc.
1981-2004
Permission is granted to reprint this document (but not for
profit), provided that copyright notice is given.
QEDIT and SUPRTOOL are trademarks of Robelle Solutions Technology
Inc. Other product and company names mentioned herein may be the
trademarks of their respective owners.
Introducing Suprtool/UX Version 4.8.01
* Highlights
* Known Problems
* Compatibility
* CPU Serial Number (uname)
* Documentation
Use Suprtool/UX to read, select, and sort data from Oracle,
Eloquence and Allbase databases and from data files with
fixed-length records. Suprtool/UX is designed to be similar to
Suprtool for MPE while providing necessary HP-UX features.
Suprlink/UX provides high-speed data-file linking based on a sort
key. Use STExport to convert fields in a self-describing input
file into an output file that can be imported into different
applications.
Highlights in Version 4.8.02
* The information about the data loaded in a Table would be lost
if the tabl being referenced was the second held table and the
previous task involved a chai command.
* The Get command would not get correct end of file signal if the
dataset was empty and being accessed with Set FastRead On.
Highlights in Version 4.8.01
* The Chain command would not return the correct record if used
with Set FastRead On.
Highlights in Version 4.8
* The Clean command in Suprtool would incorrectly upshift lower
case alpha characters.
* The $edit function will now work when nested within other
string functions
* The List command on HPUX now has the RECLEN parameter.
* Suprtool now has the command Set Oracle ZeroNull On |Off which
optionally turns null values into zeros.
Highlights in Version 4.7.12
* The $subtotal function has been re-written in order to use less
resources and fix some bugs.
* Suprtool now reads Eloquence databases up to five times faster.
* Eloquence routines are now dynamically loaded, using the
libraries on your system.
* The $split command can now trim off leading and trailing quote
characters in "PRN" files.
Highlights in Version 4.7.11
* Suprtool now has a $findclean function to identify records with
specific characters in it.
* Dbedit for HP-UX now works on HP Eloquence databases.
* There is a new version of Suprtool that works with IMAXSoft.
* The List command now has new options for listing to a file on
HP-UX.
* The $subtotal function would not work if the previous task used
the Dup None Keys feature.
* The $total function would appear to total data incorrectly when
sorting on the field that was being totalled.
* Variables that resolved to all spaces for the entire command
line would not work.
* The $subtotal function would not work at times due to an
uninitialized variable.
Highlights in Version 4.7.10
* Suprtool now has a $edit function for formatting data.
* Suprtool now allows up to 255 $split functions per task.
* Suprtool's Open command can now connect to a remote Oracle
database.
* Numrecs 100% would come up with the wrong output file size when
reading very large files.
* The new $split function would put random characters at the
point where the split would occur in some cases.
* The new $split function would incorrectly report an error in a
second task multiple $split operations.
* The new $number function did not handle numbers that consisted
of only a decimal place followed by any number of zeroes and a
number, as in .01 thru .09
* Suprlink would abort if the Join file was empty.
* Suprlink would hold the Join file open after the task was
completed.
* Suprtool would total incorrectly when using a $subtotal
function.
* The form command would display the percentage full as a very
large strange number if the capacity and the entries were both
zero for Eloquence databases.
* Update ciupdate would not be effective for Eloquence databases
in the case where Ciupdate was allowed.
* We have worked around an Oracle patch issue which stopped the
Open command from being able to connect to an Oracle database.
Highlights in Version 4.7.02
* Suprtool would report an incorrect error message when using the
$number function in some cases.
* A Dbupdate of a critical item or sort item would fail in some
cases due to a small bug in Eloquence.
Highlights in Version 4.7.01
* The Base command would fail in some cases with a Dblogon
message from Eloquence.
* A Dbupdate of a critical item or sort item would fail in some
cases.
Highlights in Version 4.7
* Suprtool would report an incorrect record number when
encountering an Illegal ascii digit, if the Suprtool task
involved the Duplicate command.
* Suprtool now has a $Number function which will allow Suprtool
to use a freeform ascii number with signs, decimal places and
currency symbol as a display field.
* STExport would in some cases attempt to format data in XML and
HTML.
* The Clean command in STExport and Suprtool has improved syntax
to define a range of characters to replace.
* Suprtool now has a $SubTotal function.
* Suprtool can now split byte strings into multiple fields via
the $split function.
* Suprlink can now do many-to-many links via the Join command.
* Suprlink and STExport now report the number of output records
in a manner similar to Suprtool.
* The $counter function was not reset in between tasks.
* Extract from a table would incorrectly report an error in some
cases.
* Suprtool for HP-UX now supports environment variable
substition.
* The Table command now supports filenames up to 80 characters.
* The Table command truncated filenames at the limit of 36
characters.
* The number of defines allowed in Suprtool has been increased to
768.
* Suprtool has been enhanced to support HP Eloquence 7.0
features.
* The Base and Put commands have been changed to support new
syntax.
* Suprtool now supports the expanded database limits in HP
Eloquence 7.0.
* Suprtool now support extract from a table and larger tables on
HP-UX.
* Suprtool and STExport now support features to Clean your data.
* STExport now supports an Escape command which will escape out
certain characters.
* Suprtool now supports a $Counter function which will increment
a field for every record selected.
* Suprtool now supports a $total function which will total a
specified field.
* Suprtool would incorrectly coerce large negative numbers from a
Packed field to another Packed or Display field.
* Suprtool would fail with prefetch point failure if the file had
an uneven record size and an uneven blocking facter.
* STExport now properly converts the Roman 8 universal monetary
symbol to the Euro-Symbol in the HTML and XML commands.
* The Item command would fail if a definition or Item command was
already issued for a field, if the input source was a
self-describing file and and extract range was used or all
items from a dataset were extracted.
* The XML command would fail to convert ">" to ">" and "<" to
"<".
* The XML and HTML commands failed to convert "&" to "&".
* Suprtool incorrectly rounded Real and Long target data in
arithmetic expressions.
Known Problems
There are two known issues associated with the Oracle interface.
In order to fix a problem introduced by a patch to Oracle 9, we
had to change to a new Oracle call interface call. You can invoke
this new call in one of two methods:
1) Set Oracle OpenFix On
2) Specify the Username and Password in the following manner
open oracle scott/tiger
When using this type of syntax and the Open command is preceded by
a system command, such as echo, the Open command will fail with a
core dump. The work around is to not have the system command
before the open command.
When connecting to a remote machine using the syntax:
open oracle username/password@machine
Suprtool uses the different method for connecting to a database,
which for some reason fails on the second and fourth connections.
While we normally would not release software with this kind of
defect, we have done so for two reasons:
1) We cannot find any reason in our code for the problem and the
failure appears to occur inside the Oracle call.
2) Some customers that need the functionality to connect to a
remote machine are willing to live with the problem.
Suprtool 4.6 and Suprtool 4.7 by default would forcefully return
zeroes for fields which were considered null. It is important to
note that Suprtool would return zeroes for Null fields, it is just
Suprtool 4.6 and 4.7 forced this as some null fields for a
customer were corrupt. We have made this optional with Set Oracle
NullZero with the default being off.
Suprtool by default no longer forces zeroes for null numeric
fields as Suprtool incorrectly zeroes out a field if the Select
statement contains the to_char function and has the field has null
values.
This should be fixed in a future version of Suprtool.
If you have any questions or concerns or feedback on these or any
other issue, please feel free to e-mail me at: neil@robelle.com
Compatibility
Suprtool/UX is compatible with HP-UX 9.0, all versions of HP-UX
10.x, all versions of HP-UX 11.x, as well as Oracle version
7.1.3.2.0.
On HP-UX 10.x, Suprtool/UX creates all of its temporary and
scratch files in /var/tmp, unless you have overridden the
temporary directory with the TMPDIR environment variable.
Suprtool for HP-UX typically comes with two versions in two
different directories on your tape. The version of Suprtool in
/opt/robelle is compatible with HP-UX 10.20 and later. The
version of Suprtool in /usr/robelle is compatible with versions
earlier than HP-UX 10.20.
Previously Suprtool would truncate a Table filename at 36
characters if the filename was greater than 36 characters and
therefore would open a file if a file existed at the previous 36
character limit. The Table filename has been increased to 80
characters. If the Table filename exceeds 80 characters Suprtool
will print an error.
CPU Serial Number (uname)
This program runs only on CPUs whose serial numbers have been
encoded (the "uname" on HP-UX). If it fails to run and you get an
"invalid HPSUSAN" error message, call Robelle for assistance.
Documentation
The user manuals for Suprtool and its components are all available
in the several popular formats such as PDF and HTMLHelp. You can
also order hardcopy manuals from our printing service. All
downloads and information is available in our library at:
http://www.robelle.com/library/manuals/
The manuals are also included as help files within the Suprtool
program and can be accessed by typing the help command:
>help
Installation of Suprtool for HP-UX
The new version overwrites an existing version of Suprtool on your
HP-UX system. Please do not use these instructions if you have
downloaded our software from the Web.
You can find the instructions for the Web download installation
here:
http://www.robelle.com/downloads/install-sxprod.html
This page assumes that you have already downloaded the tar file
from our web site.
If you have received a tape from us you can find the installation
instructions here:
http://www.robelle.com/support/install/tape/sxprod.html
Enhancements in Version 4.8
We constantly provide Suprtool users with new features. The
following section describes the new enhancements to Suprtool since
for the last two years.
Set Oracle ZeroNull
Suprtool for HP-UX has a new option to turn on changing null
fields to zeroes. Set Oracle ZeroNull On will change any fields
that are "null" (in the SQL sense of the word), will become zeroes
for the appropriate number type.
In order to turn this feature on for all accesses you can put the
command:
Set Oracle ZeroNull On
into the file /opt/robelle/suprmgr.
Previous to version 4.6 in Suprtool used to return nulls, but
Suprtool 4.6 and Suprtool 4.7 would return zeroes. We have
decided to make this optional and make the default to return
Nulls, due to problems when using the to_char function in the
select statement. See the Compatibility section for a more
detailed explanation.
Reclen on List command
Suprtool for HP-UX can output data from the List command to a
discfile. Although the concept of record size is not the same on
HP-UX as it is on MPE it is still important in some areas within
Suprtool.
In this case the RECLEN parm merely tells the List command where
to fold the lines. The Reclen parm can be a value from 56 to 256
and defaults to 80 bytes.
>List FILE myreport RECLEN 132
Enhancements in 4.7.12
FastRead
Suprtool for HP-UX by default calls dbget to do serial reads, now
with Suprtool you can utilize faster reads with the Set FastRead
On command. This command invokes more efficient large reads.
Testing has shown that the CPU time can be improved by anywhere
from two to five times and Wall time has improved anywhere from
two to six times faster. In order to turn this feature on for all
accesses you can put the command:
Set FastRead On
into the file /opt/robelle/suprmgr. This means that Suprtool will
use the faster reads for all runs of Suprtool.
Dynamic Load
Suprtool for HP-UX attempts to dynamically load the Eloquence
routines. Suprtool requires two Eloquence libraries, namely:
libimage3k.sl and libeqdb.sl. What Suprtool now does is look for
these libraries in two separate ways. First Suprtool looks for
libeqdb.sl and libimage3k.sl in any of the directories named in
the SHLIB_PATH variable. For example to insure that Suprtool
resolves the library loads you can set the SHLIB_PATH system wide
in your /etc/profile file in the following manner:
# add SHLIB_PATH for Eloquence library search
export SHLIB_PATH=/opt/eloquence6/lib/pa11_32
The line proceeding the export command is a comment line and does
not need to be in the file, but is just a reference to indicate
what it is used for.
If you do not have the SHLIB_PATH variable set to a value where
libeqdb.sl and libimage3k.sl can be found, Suprtool will then try
to load libimage3k.sl in the directory /opt/eloquence6/lib/pa11_32
and libeqdb.sl from the same directory. If the libraries still
fail to be loaded then Suprtool will print two warnings, however,
it will still continue to function, just any of the Eloquence
features will fail when called:
Warning: Could not load Eloquence image library
Warning: Could not load Eloquence scan library.
Enhancements in 4.7.11
Dbedit Module
The Dbedit Module has been enabled in Suprtool for HP-UX. It
works with Eloquence databases and with Oracle databases with the
IMAXSoft version of Suprtool.
The Dbedit Manual will be in the Suprtool for HP-UX 4.8 release.
In the meantime you can use the MPE version of the manual.
$Findclean Function
We recently added the $Clean function to primarily clean "bad"
characters in text fields. This has been extremely popular
enhancement but many wanted to do investigative work and try to
figure out what records had these bad characters, to hopefully
where the "bad" data was coming from.
For this reason we have created the $FindClean function.
$FindClean will return true if it finds a character defined using
the Clean command.
>in cleansd
>clean "^9","^10"
>if $findclean(nonprint)
>list
The above task will list the record if the field nonprint has a
Tab (Decimal 9) or a Line Feed (Decimal 10) anywhere in the field.
You can Find and clean the "bad" characters from a field at the
same time:
>in cleansd
>clean "^9","^10"
>if $findclean(nonprint)
>extract nonprint=$clean(nonprint)
>list
List Command
The List command has been enhanced with a new File keyword that
allows output to be directed to a file. The List command also has
a new option to Append to an existing file.
The File option takes the next parameter as being the filename:
>in test/file1sd
>list stan file myslist
>xeq
If the file myslist exists it will be over-written, unless you
specify the Append option. If you specify the append option the
new report will be added to the file.
So if you want to incorporate multiple reports you just need to do
the following:
>in test/file1sd
>list stan file myslist
>xeq
>in test/file2sd
>list stan file myslist append
>xeq
Enhancements in Version 4.7.10
Enhancements in 4.7.10
This version of Suprtool has some significant enhancements and are
listed below.
$Split Function
Suprtool now allows up to 255 $split functions per task. The
previous limit was 16, and the limit has been changed to assist in
reading data from "PRN" files.
$Edit Function
Suprtool can format fields using edit-mask features similar to
edit-mask features of Cobol. Suprtool employs two distinct types
of edit-masks: one for byte type fields and the other for numeric
fields.
The type of mask utilized depends on the source type of the field.
If the source field is numeric, then the numeric edit-mask logic
is applied, if the source field is byte type, then the byte
edit-mask logic and characters apply.
The target field must always be a byte type field.
Placeholders and Format Characters
An edit-mask consists of "placeholder" characters, such as "9" for
a numeric column, and "format" characters, such as "." for the
decimal place. Sometimes an edit-mask character acts as both a
placeholder and a format character, such as the "$" in floating
dollar signs.
Byte-Type Formatting
For Byte type fields there are two placeholder characters. These
are:
X place the data in the matching column for the X in the
edit-mask
Z place the data in the matching column unless the data is a
zero; if the
data is a zero, then replace with a space
The format characters are as follows:
B (space) / (slash) , (comma) . (period) + (plus) - (minus) *
(asterisk) and a Space. Please note that you can denote a space
using two methods, either by putting a "B" in the mask or a space
itself.
For example, suppose you have data that is in ccyymmdd format in
an X8 field. Here is how you would use a "xxxx/xx/xx" mask to
format the data:
>in mydate
>form
File: MYDATE.TEST.NEIL (SD Version B.00.00)
Entry: Offset
A X8 1 <CCYYMMDD>
Limit: 10000 EOF: 2 Entry Length: 8
>def formatdate,1,10
>ext formatdate=$edit(a,"xxxx/xx/xx")
>list
>xeq
>IN MYDATE.NEIL.GREEN (0) gt;OUT $NULL (0)
FORMATDATE = 2003/09/24
>IN MYDATE.NEIL.GREEN (1) gt;OUT $NULL (1)
FORMATDATE = 2003/09/24
As you see in the example above, the placeholder character is the
"x" and the "/" is the format character.
You insert a space either by specifying a "B" or by putting an
actual Space character in the edit-mask. An example of inserting
a space might be the formatting of Canadian postal codes (e.g.,
V3R 7K1):
>in postal
>form
File: POSTAL.NEIL.GREEN
Entry: Offset
POSTAL-CODE X6 1
Limit: 10000 EOF: 2 Entry Length: 6
>def post1,1,7,byte
>def post2,1,7,byte
>ext post1=$edit(postal-code,"xxx xxx")
>ext post2=$edit(postal-code,"xxxbxxx")
>list
>xeq
>IN POSTAL.NEIL.GREEN (0) >OUT $NULL (0)
POST1 = L2H 1L2 POST2 = L2H 1L2
>IN POSTAL.NEIL.GREEN (1) >OUT $NULL (1)
POST1 = L2H 1L2 POST2 = L2H 1L2
Z-placeholder for byte-fields
The Z-placeholder character works differently for byte-fields than
for numeric fields. For byte type fields, if the Z placeholder
and the corresponding data is "0", then the zero is suppressed,
regardless of the position. This is primarily for suppression of
zeroes in byte type date fields:
ext a=$edit(date-field,"xxxx/zx/zx")
The above edit mask would then edit a byte type date of 20031005,
to be:
2003/10/ 5
Overflow and limits
An edit mask is limited to 32 characters in total for both numeric
and byte type fields. If data overflows the edit-mask, by default
Suprtool will fill that field with asterisks. There is an option
to have Suprtool stop when it encounters a formatting overflow:
>set editstoperror on
will force Suprtool to stop if there is data left over after
applying the edit-mask. With byte-type fields, leading spaces do
not cause overflow.
Therefore if your data consists of:
" L2H1L2"
and your edit mask is:
"xxxBxxx"
It is not an overflow since there are only spaces to the left of
the "L". If the data was:
" JL2H1L2"
an overflow exception would occur.
Numeric field edit-masks
Our edit-masks for numeric fields are patterned after those in
COBOL. We provide four placeholder characters, each with a
slightly different effect:
"9" - insert a digit from 0 to 9 in this position
"$" - if you specify more than one dollar sign, you get a
floating dollar sig
This means that there can be as many numeric positions as there
are dollar
signs, but if some positions are not needed because the value
is small, the $
floats to the right next to the first digit and the preceding
positions are
blank.
"*" - if there are enough digits in the value, the * position
is replaced
by a numeric digit; if not, an asterisk is printed. Leading
asterisks are
often used for check writing, so that no one can insert a
different
value.
"z" - insert a numeric digit at this position; if the rest of
the data to
the left is a zero then a space will be placed at this
position. For example:
>ext a=$edit(int-field,"$$,$$$.99-")
>ext b=$edit(int-field,"99,999.99-")
>ext c=$edit(int-field,"cr99999.99")
>ext d=$edit(int-field,"-$9999.99")
>ext e=$edit(int-field,"**,***.99+")
>ext f=$edit(int-field,"zz,zzz.99+")
>list
>xeq
>IN FILE1SD.NEIL.GREEN (0) >OUT $NULL (0)
A = $11.11- B = 00,011.11-
C = CR00011.11 D = -$0011.11
E = ****11.11- F = 11.11-
>IN FILE1SD.NEIL.GREEN (1) >OUT $NULL (1)
A = $22.22- B = 00,022.22-
C = CR00022.22 D = -$0022.22
E = ****22.22- F = 22.22-
Signs
As shown in the example above, there are also numerous format
characters for numeric edits, including four ways to specify the
sign.
You can specify a sign, with +, -, or the typical accounting
specification of "CR" and "DB". You will note in the example
above that the "cr" in the mask was up-shifted to be "CR". This
is because the entire mask is up-shifted as the mask is being
parsed.
You can specify more than one sign in a numeric field edit,
although Suprtool will give you a warning that having two sign
edit-mask characters does not reall make sense. Cobol gives a
Questionable warning when compiling an edit-mask with characters.
Suprtool, will apply the sign in both places.
Keep in mind that most data has three states:
1) Postive
2) Negative
3) Neutral
Any neutral data will not display the sign. If you specify a "+"
sign in the edit-mask and the data is negative, it will of course
display a "-" sign.
Decimal Places
For numeric-type edits, Suprtool attempts to adjust the data
according to the number of decimal places in the edit-mask, when
compared to the number of decimal places defined in the field.
For example if the data field has one decimal place, and the edit
mask has two decimal places, then the data is adjusted:
Data and Edit mask:
102.3 ZZZZ.99
will result in the final data being:
102.30
Similarly, if the data has three decimal places and the edit-mask
only has two, then the data will be rounded appropriately with the
same rules as outlined in the $number function.
You can specify more than one decimal place in an edit-mask.
However, Suprtool will print a warning and it will utilize the
right-most decimal place for data alignment.
The decimal place character is defined by a set command:
>set decimalsymbol "."
If you define another character as the decimal symbol, Suprtool
will use that character as the point to align the decimals. If
you define a decimal symbol that is not an allowed edit-mask
character with Set Decimalsymbol, Suprtool will assume that the
field has zero decimal places and adjust the data accordingly.
Currency and Dollar signs
Suprtool edit-masks support both fixed and floating dollar signs.
Logic for floating dollar-signs will be invoked if more than two
dollar signs are defined in the edit-mask.
A floating-dollar edit mask attempts to put the dollar sign at the
left most position of the significant data. For example if you
have the following data and edit mask:
0001234.54 $$$$$$.$$
the data would end up as:
$1234.54
Suprtool will not however, put the dollar sign to the right of the
decimal place. If you had the same edit mask and the data was,
.09, the data would end up being formatted as:
$.09
Similarily, the $edit function will attempt to place the dollar
sign correctly in most cases. For example Suprtool will not
format data in the form of:
$,123.50
Suprtool, does attempt to fixup these cases and would format the
data in the following manner:
$123.50
Overflow and floating dollars
If the number of digits in the data is equal to the number of
placeholder dollar signs, then the dollar sign is dropped and not
added to the edited field.
12345.50 $$$$$.99
would result in:
12345.50
Set CurrencySymbol
If Set CurrencySymbol is not equal to "$", then after the
formatting has been applied, whatever symbol(s) are defined within
the set command, are used to replace the "$" symbol in the data.
For example, if you have the Currency symbol set as "CDN".
>set currencysymbol "CDN"
Suprtool will replace the "$" after the edit-mask has been applied
with CDN, provided there is room to the left of the dollar-sign.
It is recommended that if you are using multiple characters for
the dollar symbol that you leave enough characters to the left of
the symbol.
For example if the CurrencySymbol is defined as CDN, then you
should leave two spaces to the left of a fixed dollar sign
definition. If there is not enough room, to put in the currency
symbol, then the dollar symbol is blank.
Overflow and limits
An edit mask is limited to 32 characters in total for both numeric
and byte type fields. If data overflows the edit-mask, by default
Suprtool will fill that field with asterisks. There is an option
to have Suprtool stop when it encounters a formatting overflow:
>set editstoperror on
will force Suprtool to stop if there is data left over to place
when applying the edit-mask. With numeric-type fields, leading
zeroes do not cause overflow.
Oracle Open
The Open command has been enhanced to allow connections to a
remote oracle database. In order to invoke this feature the
username/password machine, need to be specified on a single line.
>open oracle suprtest/suprpass@remote
There are some issues with using this syntax, for more information
please read the section entitle Known Problems.
Bugs Fixed in Version 4.8.02
Chain Command. The information about the data loaded in a
Table would be lost if the table being referenced was the
second held table and the previous task involved the Chain
command.
Get Command. The Get command would not get correct end of file
signal if the dataset was empty and being accessed with Set
FastRead On.
Bugs Fixed in Version 4.8.01
Chain command. The chain command does not return the correct
record with Set FastRead On.
Bugs Fixed in Version 4.8
$Edit Function. The $edit function now returns a proper result
when nested within another string function such as $ltrim.
Clean Command. The clean command was improperly upshifting lower
case alpha characters.
Bugs Fixed in Version 4.7.12
$SubTotal Function. The $subtotal function would cause Suprtool
to fail if the size of the Output buffer was larger than the
record input.
Bugs Fixed in Version 4.7.11
Variable Substitution. Suprtool would report the error:
Error: >KEY has 2-4 parms: pos,len[,type][,DESC].
when resolving a variable that resolved to a blank line. This is
now fixed in Suprtool 4.7.11.
$Total Function. The $total function would appear to accumulate
incorrectly when sorting in the same task.
$SubTotal Function. The $subtotal function would not work if run
in the same copy of Suprtool if the previous task used the
Duplicate command.
$SubTotal Function. The $subtotal function would not work
randomly due to an un-initialized variable.
Bugs Fixed in Version 4.7.10
Eloquence and Form command. The Form command would show
percentage full as a very large strange number if the capacity and
entries were both zero for a given Eloquence database.
Eloquence and Update Ciupdate. An Update on a Critical Item
would not work if the CIUPDATE flag status for an Eloquence
database was only Allowed. The flag needed to be enabled for an
Update Ciupdate operation to work.
Incorrect Flimit. Suprtool would incorrectly calculate the
flimit on an output file when using Numrecs 100%, and if the input
file was very large.
$Split generating random characters. The new $split function
would put random characters at the point where the split would
occur in some cases.
$Split reported bogus error on repeated task. The new $split
function would incorrectly report an error in a second task with
multiple $split operations.
$Number decimal only numbers incorrect. The new $number function
did not handle numbers that consisted of only a decimal place
followed by any number of zeroes and a number, as in .01 thru .09.
Empty Join File caused abort. Suprlink would abort if the Join
file was empty.
Join File held open. Suprlink would hold the Output file open
after the task was completed.
$Subtotal incorrect. Suprtool would give incorrect numbers for a
$subtotal function in certain cases.
Percentage Full on Form command. The form command would report
an incorrect Percentage full if the capacity and entries were both
zero.
Oracle Open. With Oracle 9.2.0.2 and Interim Patch 2713497
installed, the Suprtool Open command could no longer connect to
Oracle databases. An Open command would fail with:
>open Oracle suprtest suprpass
Error: Unable to connect to the Oracle database
Error: ORA-01017: invalid username/password; logon denied
we have made changes to fix this problem by calling a different
Oracle Call Interface call. In order to invoke this other call
you can use the set command of:
>set oracle openfix on
There are two issues outstanding with this fix, which are
documented in the Known Problems section.
Bugs Fixed in Version 4.7.02
Bogus Error with $number. Suprtool would incorrectly report an error
when using the $number function in some cases.
Incorrect Flimit on Output file. Suprlink and STExport would build the
output file with an incorrect flimit.
Bugs Fixed in Version 4.7.01
DbLogon Error. The base command would fail with a dblogon error message
if security on an Eloquence database was setup a certain way.
Enhancements in Version 4.7
$Number Function if and Extract
Suprtool now has the ability to accept free-form "numbers" as
display data types. This means number in the form:
1234.45-
-12345
-123.2134
12343
can now be accepted and converted to any other numeric data type.
Consider the following data:
Item-number New-Price
12345 +123.45
34563 + 27.5
21312 + 1.545
Suprtool can now read and convert the data in New-Price using the
number function. Let's say we want New-Price to be a double
integer and currently occupies eight bytes starting in position
six.
Here is the task you would use to convert the New-Price
free-format number into a double integer.
>in mynums
>def item-number,1,5,byte
>def new-price-ascii,6,8,display
>def new-price,1,4,double
>item new-price-ascii,dec,2
>item new-price,dec,2
>ext item-number
>ext new-price=$number(new-price-ascii)
>out somefile,link
>xeq
The $number function take the free-format number and make it a
valid display number. It will determine the decimal, sign and add
leading zeroes. It will round the number to the defined number of
decimal places.
In the case of 1.545 number, Suprtool will round the value to be
1.55, since the given number of decimal places is two and the
preceding value is five or greater. If you have a whole number
such as 54, with no decimal point the value becomes 54.00.
Suprtool will not accept data that has:
More than one sign.
More than one decimal place.
Spaces in between numbers.
Signs that are in between numbers.
Characters that are not over punch characters.
Fields that when edited do not fit in the defined space for the
display field
You can control the character that defines the currency, thousand
and decimal symbol for other currencies and formats using the
following commands:
>set decimalsymbol "."
>set thousandsymbol ","
>set currencysymbol "$"
Suprtool in the above case will strip the currency and thousand
symbols and use the decimal symbol to determine the number of
decimal places. You can set these characters to any values you
want but the defaults for each are used in the above set commands.
The Decimal and thousand symbols are only single characters. The
currency symbol allows for four characters.
Suprlink Many-to-Many Link
Suprlink can now join files together that have multiple key
records in each file, what has been come to be know as a
many-to-many link. Suprlink has traditionally been able to link
an Input file with many records with the same key to a Link file
that has a single record with the same key value.
The Join command, will now link two files with many key records in
both the inpu and the "Linking" file.
The syntax of the Join command is exactly the same as the Link
command so a sample task would look as follows:
+input ordhist
+join orders
+output custord
+xeq
The above task will link multiple records of the file ordhist, to
the multiple records of the file in orders. This assumes that the
files are sorted by a common key. In SQL terms this is known as
an Inner Join.
An Outer Join, one where the keys do not necessarily have a match
can be achieved by adding the optional keyword to the Join
command:
+input ordhist
+join orders optional
+output joined
+xeq
In SQL parlance, once again you can achieve both a Left Outer Join
and Right Outer Join by reversing the order of the files, between
the input and the join commands.
To give you an example of how the Join operation would work
consider the following data. First we have an inventory file with
multiple records for the same product-no. This data is stored in
the file dinv:
50512001 {Rest of data}
50512001 {Rest of data}
50512003 {Rest of data}
The next file will have sales records, once again with multiple
key values, this data is stored in the file dsales:
50512001 {Rest of data}
50512001 {Rest of data}
If you did the following task assuming both files are sorted by
the product-no:
+in dinv
+join dsales
+out invsales
+xeq
The resulting file would have four records, with the multiple
matching dinv and dsales records. The record layout would have
the dinv information first followed by the dsales information.
If you add the optional keyword on the join command the resulting
file would have 5 records. The matching 4 records from dinv and
dsales as well as the dinv record that did not match with the
numeric fields set to zero and the byte fields set to spaces.
Only one Join operation is allowed per task.
By default, Suprlink will join files base on the primary sorted
key in the self-describing file. You can specify a secondary key
for the files to be joined on in a similar manner to how the Link
command did:
+in orders
+join dsales by order-no product-no
+out ordsales
+xeq
Splitting Byte Fields
Suprtool can extract portions of a byte field based on the
occurrence of certain characters.
Consider the following Data:
Armstrong/ Neil/ Patrick
Green/ Bob/ Miller
Fritshaw/ Elizabeth/
Edwards/ Janine/
Armstrong/Arthur/Derek
The $split function can extract each token into separate fields.
The syntax for the $split function is:
$split(Field,StartCharacter,occurrence,EndCharacter,occurrence)
The following task will $split the data in the wholefield into
three separate fields.
>in namefile
>define lastname,1,30
>define firstname,1,20
>define middlename,1,20
>extract lastname = $split(wholename,first,"/")
>extract firstname=$trim($split(wholename,"/","/"))
>extract middlename=$trim($split(wholename,"/",2," ",2))
>out names,link
>xeq
The first extract statement tells Suprtool extract the bytes from
the field wholename, starting at the beginning (first keyword),
and stopping at the "/" character.
The second extract statement, tells Suprtool to extract the bytes
between the first occurrence of the "/" character to the next
occurrence of the "/" character, and then that string is trimmed
of spaces as it is nested within the $trim function.
The third and final extract statement tells Suprtool to extract
the bytes beginning with the second occurence of the "/" character
to the second occurrence of the space character.
If the target field is not long enough to hold the data Suprtool
will abort with an error. You can easily prevent this from
happening on blank fields by nesting the $split statement within a
$trim or $rtrim function.
The $split function also has a Last keyword, whereby you can split
the field from a given occurrence of a character to the end of the
field.
So in the given example from above the extracting out of the
middlename could be coded as such:
>extract middlename=$trim($split(wholename,"/",2,last))
The above means to extract out all the data from the second
occurrence of the "/", to the end of the field and trim all
spaces.
Control Break Totals
Suprtool now has the ability to keep a running subtotal for any
numeric field based on a given sort key.
The target data must be a packed field with 28 digits, in order to
help avoid overflow issues.
A sample use of the $subtotal function could be:
>def mytotal,1,14,packed
>get orders
>sort order-number
>ext order-number
>ext part-number
>ext description
>ext sales-amount
>ext mytotal = $subtotal(sales-amount,order-number)
>out sales,link
>xeq
This would result in a file containing a running subtotal in the
field mytotal f order-number. You could then generate a simple
report with the simple Suprtool c
>in sales
>list standard
>xeq
The basic syntax for the $subtotal function in the extract command
is:
extract targetfield = $subtotal(field,sort-field)
You must specify the sort command before referencing the
sort-field in the $subtotal function.
You can subtotal up to ten fields per pass and the $subtotal
function is also available in the if command, however, is of
limited use.
Clean Command Syntax
The Clean command has improved syntax to specify which characters
to look to replace. You can specify special characters Decimal 0
thru Decimal 31 via the command:
Clean special
You can also specify a range or characters by using the following
syntax:
Clean "^0:^31","^240:^255"
This enhancement makes it much easier to define characters to
search for and Clean. This is available in both Suprtool and
STExport.
Outcount information
Suprlink and STExport on HP-UX now have a method for reporting how
many records have been output. In Suprtool, we wrote the number
of records to a file called .stoutcount. In Suprlink and STExport
we used the files, .sloutcount and .sxoutcount respectively.
For Suprlink:
#!/bin/sh
#
suprlink -oc << !EOD
+in orders
+join ordhist
+out ordcomb
exit
!EOD
if [ `cat .sloutcount` -ge 10 ]; then
echo "More than 10 records found"
fi
For STExport:
#!/bin/sh
#
stexport -oc << !EOD
$in orders
$heading fieldnames
$out ordprn
exit
!EOD
if [ `cat .sxoutcount` -ge 10 ]; then
echo "More than 10 records found"
fi
HP Eloquence 7
Suprtool has been enhanced to be compatible and work with HP
Eloquence version Seven. It takes advantage of many new features
and supports the new limits and data types.
Base Command
Suprtool's Base command has been enhanced to allow the new syntax
supported in HP Eloquence 7. HP Eloquence now allows the
servername and service to be specified in dbopen. To support this
new syntax Suprtool's Base command has been changed to allow the
servername, service and database name be specified. Suprtool uses
the same syntax as HP Eloquence whereby the database name consists
of the following elements:
[[host][:service]/]database
Examples of using this syntax within Suprtool using the sample
database that HP Eloquence provides.:
base sample,5
base :eloqdb/sample,5
base hostname.robelle.com:eloqdb/sample,5
Put Command
The Put command has always supported for a database name to be
specified. Since the Base command allows the new syntax for HP
Eloquence version 7 support, the Put command also allows this new
syntax.
put dataset,sample
put dataset,:eloqdb/sample
put dataset,hostname.robelle.com:eloqdb/sample
Expanded Database Limits
HP Eloquence version 7 now has new database limits that follow or
extend the current Turbo IMAGE limits.
Data Items per database 2048
Data Sets per database 500
Paths per dataset detail 16
Paths per dataset master 64
Cleaning your Data
In this day and age of migrations we were looking at issues that
customers have run into when importing data into new databases.
What came from this investigation where ways to Clean up your data
in any given byte type field.
We have added two methods to clean your data, you can use Suprtool
to clean an individual byte type field, or STExport to clean all
of the byte-type fields for a given file that you are exporting.
Suprtool
Sometimes un-printable or extraneous characters get stored in
files or databases that have no business being there. This may be
some tab characters in an address field or perhaps and embedded
carriage return or line-feed.
Suprtool now supports the clean function which will replace
individual characters for a given byte field.
There are three things that Suprtool needs to know in order to
"clean" a field. Suprtool needs to know which characters it needs
to clean, what character it needs to change the "bad" characters
to, and also what field does it need to clean.
Defining a Clean Character
The Clean command is used to tell Suprtool what characters it
needs to look for in a given byte type field. For example:
clean "^9","^10","."
will tell Suprtool to replace the tab character (Decimal 9), Line
Feed (Decimal 10), and a period to whatever the Clean character is
set to.
The CLean command takes both, decimal notation and the character
itself, however, it is probably most convenient to use the Decimal
notation for the characters that you wish to clean. The Decimal
notation is indicated by the "^" character.
Setting the Clean Character
By default, Suprtool will replace any of the characters specified
in the clean command with a space. You can specify what character
to use to replace any of the characters that qualify with the
following set command:
>set CleanChar "."
This will set the character to replace any of the qualifying "to
be cleaned" characters to be a period.
Cleaning a Field
You call the clean function, the same way you normally use other
functions available to if and extract. For example:
ext address1=$clean(address1)
shows how to clean the field address1. You do not necessarily
need to have the target field be the same as the source field.
def new-address,1,30
ext new-address=$clean(address1)
Cleaning your data
An example of how easy it would be to clean your database of
certain "bad" characters in byte-type fields would be as follows:
>base mydb,1,;
>get customer
>clean "^9","^10","^0","^7"
>set cleanchar " "
>update
>ext address(1) = $clean(address(1))
>ext address(2) = $clean(address(2))
>ext address(3) = $clean(address(3))
>xeq
The above task will look at the three instances of address and
replace the tab, linefeed, null and bell characters with a space.
STExport
This same feature has been added to STExport, except that STExport
will automati clean all the byte type fields for a given SD file.
The commands are very simila STExport just needs to know what the
replace character should be and what charac needs to look for.
$ in mysdfile
$clean "^9","^10","^0","^7"
$set cleanchar " "
$out myexport
$xeq
Since the Cleanchar is by default set to space, the above task
could simply be:
$in mysdfile
$clean "^9","^10","^0","^7"
$out myexport
$xeq
Escape Command
Many SQL importers allow you to add an escape character in front
of characters that may mean something else to the import program.
For example if the import program thinks that the delimiter
character is a comma, the importer may treat a comma in an address
field as an indication to move to the next field, which will throw
of the import.
Some import programs, will treat the next character as data as
opposed to a delimeter if the character is preceded by an escape
character, such as a slash.
Thus when the field is analyzed by STExport the data that
originally started as:
"Niagara Falls,Ontario, Canada"
would be transformed to be:
"Niagara Falls/,Ontario/, Canada"
This function will not work on fixed columns and can be invoked
with the escape command:
escape delimeter quote eol "/"
The above command will take the defined delimeter, quote and Eol
and escape with a "/", if found in any byte type field.
Table Command filename
Previously the permitted length for the filename for the Table
command was 36 characters. This has been increased to 80
characters.
Running Totals
Suprtool now has the ability to keep a running total for any
numeric field. The target data must be a packed field with 28
digits, in order to help avoid overflow issues.
A sample use of the total function could be:
>def mytotal,1,14,packed
>get orders
>ext mytotal = $total(sales-amount)
>xeq
You can total up to ten fields per pass and the $total function is
also available in the if command, however, is of limited use.
$Counter Function
For years Suprtool has had the ability to output a record number
to an output file with the num option of the output command:
>in mysdfile
>out myfile,num,data
The above could would generate an output file called myfile,
however, you would the SD information and you can only put the
number at the beginning or the end o data. Suprtool now has a
counter function that allows you to place a $counter at spot as
well as preserve the SD information.
>in mysdfile
>def mycount,1,4,double
>ext field1
>ext field2
>ext mycount=$counter
>out myfile,link
>xeq
The file myfile will be self-describing and contain the fields
field1, field2 an mycount. The field mycount is defined as a
double integer, since this is the onl field type that the $counter
function can use. Each record will have a unique ascending number
starting with one.
Variable Substitution
Suprtool for HP-UX 4.6.02 now supports environment variable
substitution. To use this enhancement you must do a:
>set varsub on
Due to how HP-UX processes work with environment variables any
variables must be exported prior to running Suprtool, STExport or
Suprlink.
All of these modules support HP-UX variable Substitution.
export tablefile='abcdefghijklmnopqrstuvwxyzabcd'
export infile='file1sd'
./suprtool -oc << !EOD
set varsub on
in $infile
table mytable,char-field,file, &
/users/robdev/suprtool/test/$tablefile
if $lookup(mytable,char-field)
out file05,link
exit
!EOD
Suprtool examines a command line and looks for variables denoted
by the "$" sign. Since Suprtool has some functions that begin
with a $-sign, these will take precedence regardless of the value
set in the variable.
Tables on HP-UX Improvements
We have re-written the underlying mechanisms for the Table and
$Lookup functions in Suprtool for HP-UX, to allow more data in the
table and have similar functionality to the Table command on MPE.
This includes Larger tables and Extract from a table functionality
described below.
Table Sizes
On HP-UX you can control the size of a table with the Set Limits
TableSize command. By default an individual Table will be 50
Megabytes in size and you can have up to 10 tables. The Global
limit for all tables is up to 500 Megabytes. You can control the
size of a given table with the command:
>Set Limits TableSize n
If you enter the command Set Limits TableSize 100 and the next
table command that you build will have a limit of 100 Megabytes.
Previously the Limits on Tables were 15Mb in total and defaulted
to 1Mb in size.
Extract from a Table
Suprtool now has the ability to load data into a table via the
Table command, and extract that data out of the table using the
Extract command.
The Table command now allows for data to be loaded along with
matching key values.
>table table-name,key-field,file,filename,&
data(field1,field2,...)
An example of loading two data fields called cost and desc along
with the key field of part into a table would be:
>table partab,part,file,partin,data(cost,desc)
You can specify up to 20 data fields as long as the total size of
the key fields and data does not exceed 256 bytes. The Table file
must be Self-Describing (Link) in order to use the data option.
When loading data into a table, Suprtool will eliminate the
duplicate entries based on the key value, so the associated data
values may not be loaded into the table.
The Extract command can utilize the $lookup function to return
data. The syntax for the $lookup function would look as follows:
>extract target = $lookup(table-name,key-field,data-field)
The Table name, key-field and data-field are all defined by the
Table command, which must be input before the Extract command.
A classic example: your boss comes to you with a list of new
prices and descriptions for certain parts for your Part-Master
dataset.
The basic steps to do this are to load the new prices and
descriptions into a Table, index by the product number (prodno),
then Extract the price field from each record and replace it with
a $lookup on the table.
Here is the Suprtool code:
>table newprices,prodno,file,bosslist,data(price,desc)
>get part-master
>if $lookup(newprices,prodno)
>update
>extract price = $lookup(newprices,prodno,price)
>extract desc = $lookup(newprices,prodno,desc)
>xeq
We do the If $lookup to select only the parts which have new
prices, then do Extract with $lookup to replace the existing price
with a new one. The Update command forces a database update on
each selected record and must come before the Extract command.
If you did not specify the If $lookup, then records that did not
qualify under the $lookup function in the extract field, will
result in zeroes for any numeric field and spaces for any byte
type fields.
Bugs Fixed in Version 4.6.04
Incorrect record Number. Suprtool would incorrectly report the record
number when an Illegal Ascii digit was encountered on a Duplicate
operation. We no longer attempt to show the record number from
the Input source.
Set Limits Tablesize. Suprtool would report an error on a second Set
Limits TableSize command if the value given was larger than the
previous.
Bugs Fixed in Version 4.6.03
Extract from a Table. Suprtool would incorrectly report an error in
some cases when doing an update from a table. The error "Field
offset is beyond the input record length" was incorrectly hit if
the input file was smaller than the actual length of the table
file.
$Counter Function. Suprtool did not correctly reset the $counter
variable in between tasks.
Bugs Fixed in Version 4.6.02
Define Commands. The number of Define commands allowed has been
increased to 768 defines. The number of defines allowed used to
be a variable number dependant on various system settings, this
was changed in Suprtool 4.4.10 to be a fixed structure.
Bugs Fixed in Version 4.6.01
Packed and Display Coercion. Suprtool would incorrectly coerce large
negative numbers from one Packed or Display field.
Input Filename (start/end). Suprtool would fail with prefetch point
failure if the file had an uneven record size and an uneven
blocking factor.
HTML and XML commands. STExport now properly converts the Roman 8
universal monetary symbol to the Euro-Symbol in the HTML and XML
commands.
The XML command would fail to convert ">" to ">" and "<" to
"<".
The XML and HTML commands failed to convert "&" to "amp".