SUPRTOOL Version 4.0 Database Handyman for the HP 3000 Enhancements Since the last major release, Suprtool has been enhanced with the following new features. Allbase Databases Suprtool now supports Allbase databases. You can open an Allbase database with the Open command and choose input tables with the Select command. See the "Allbase" section later in this document for a complete description of the Allbase features that are available in Suprtool. We have tested the new Allbase module with Allbase version G.1.13 for MPE. We believe that it will be compatible with future versions of Allbase. We have not tested Suprtool with any of the F versions of Allbase. Add Command Suprtool now has an Add command which is intended to add records to an Oracle or Allbase SQL database. This is currently available only for Oracle access on HP-UX. It is documented here for completeness. Export Command The Export command has better error checking on activation of STExport. Extract Command Previous versions of Suprtool would not allow arithmetic expressions that started with a constant. If you have an arithmetic expression that starts with a constant, Suprtool assumes that you are attempting to extract a single constant value and not an arithmetic expression. To specify an arithmetic expression that starts with a constant, surround the expression with parentheses. For example, Incorrect >extract c = 6000 - cost Error: Missing comma or invalid arithmetic expression Correct >extract c = (6000 - cost) Form Command The Form command has been enhanced to identify columns that allow null values in SQL tables. Before the Form command can show the columns in a selected table, it has to be preceded by valid Open and Select commands. >form Column Name: Allbase Type: Nulls: Suprtool Type: CUSTOMERNUM Decimal (8) N Packed DELIVERYDATE Decimal (8) Y Packed PRODUCTNUM Decimal (8) N Packed PRICE Decimal (8) Y Packed PURCHASEDATE Decimal (8) N Packed SALESQTY Decimal (4) Y Packed SALESTAX Decimal (8) Y Packed SALESTOTAL Decimal (8) Y Packed Link Command The Link command has better error checking on activation of Suprlink. Set Date Command Suprtool now has two new Set Date commands: Set Date ForceCentury On | Off Set Date Cutoff nn See the "Suprtool and the Year 2000" section for a complete description for each of these settings. Item Command You can specify Oracle dates in the Item command by using the keyword Oracle. For example, >item transdate,date,oracle The Oracle date attribute is only allowed on fields that are exactly seven characters long. If $null(fieldname) The If $null(fieldname) command has been added to select rows that have null values in them. This feature is available only for SQL databases, and you can use this command only on columns that allow null values. >if $null(SALESTOTAL) If you want to find only values that are not null, you can add the NOT keyword in front of $null. >if not $null(SALESTOTAL) If $Invalid(date-field) The If command now has a $invalid function to find all invalid dates for a particular field. An invalid date is any number of a particular date format whose date equivalent cannot be found on the calendar. For example, a date with a month of 99 will be considered invalid. >base store.dbold >get d-sales >item deliv-date,date,yymmdd >item purch-date,date,yymmdd >if $invalid(deliv-date) or $invalid(purch-date) >out baddates,link >xeq Item Command The Item command now allows the following new date formats: ccyymmdd, ccyy, ccyymm, aammdd. See the "Suprtool and the Year 2000" section for a complete description. Oracle Dates Use the Item command to specify that a field contains an Oracle date. Once you have identified an Oracle date, you can use the $date and $today features in both the Extract and If commands to extract or select on Oracle dates. Oracle dates have both a date and a time component, but Suprtool processes only the date component. Link Command The Link command has improved error checking on activation of Suprlink. Output,Link Option If you use the Item command to identify the fields that are Oracle dates, the self-describing files created by the Link option of the Output command will automatically include this information. If you specify a self-describing input file with Oracle dates, Suprtool will automatically recognize the Oracle date attribute. Set Oracle Rows The minimum value for Set Oracle Rows has been changed from 10 to 1. Open Command [OP] This command opens an SQL database. Only one database can be opened at a time. For example, >OPEN ALLBASE DBEname Owner The Suprtool syntax for Allbase is slightly different from that for Oracle. Instead of a password, Suprtool expects the "owner" name that is associated with a specific Allbase database. Select Command [SEL] Specify a select-statement for an open SQL database. The Select command in Suprtool supports all the select-statement features of the open SQL database. Only one Select command can be specified at a time. SELECT select-statement The Select command can contain any expression or clause that is supported by the SQL database. However, there are some Suprtool commands that may perform faster than select-statements (e.g., Suprtool Sort command versus Order By). Set Allbase Rows When the input source is an Allbase database, Suprtool reads more than one row at a time. By default, Suprtool fetches 100 rows at a time. With the Set Allbase Rows command, you can change the number of rows; the minimum number of rows is one and the maximum number is 990. You must specify Set Allbase Rows before you enter the Select command. Data-Types When you specify a Select command, Suprtool figures out how to translate the Allbase internal data-types into formats that it can process. Not all Allbase data-types can be processed by Suprtool. The following table lists the Suprtool data-type that corresponds to each Allbase data-type: Allbase Data-Type Suprtool Data-Type integer double smallint integer binary Not Supported char byte varchar byte real ieee-32 float ieee-64 decimal packed numeric packed TID Not Supported date byte time byte datetime byte interval byte varbinary Not Supported long binary Not Supported long varbinary Not Supported Date and Time Types Allbase has four types of fields that are associated with dates and times. These fields are converted to byte-type data and are returned with specific lengths. The date and time fields are returned with the following byte lengths: Data-Type Length DATE 10 TIME 8 DATETIME 23 INTERVAL 8 Allbase Suprtool can now read Allbase tables and views. The Open and Select commands are used with Allbase. The Form command has been enhanced to show information about Allbase databases. Allbase Access Suprtool now supports Native Char and Native VarChar data-types. The Form command reports these fields as being Char(N) and VarChar(N) respectively. Form Command [F] The Form command now displays information about an open Allbase database. After an Open command, the Form command displays the tables available to the user, together with their owner names. After a Select command, the Form command displays the columns in the specified table. >open Allbase Partsdbe.allbase scott >form Allbase Owner Table: MANUFDB SUPPLYBATCHES MANUFDB TESTDATA PURCHDB INVENTORY PURCHDB ORDERITEMS PURCHDB ORDERS PURCHDB PARTINFO PURCHDB PARTS PURCHDB REPORTS PURCHDB SUPPLYPRICE PURCHDB VENDORS PURCHDB VENDORSTATISTICS RECDB CLUBS RECDB EVENTS RECDB MEMBERS SCOTT EMP >select * from scott.emp >form Column Name: Allbase Type: Nulls: Suprtool Type: EMPNO Decimal (4) N Packed ENAME VarChar (10) Y Byte JOB VarChar (9) Y Byte MGR Decimal (4) Y Packed HIREDATE Char (10) Y Byte SAL Decimal (7,2) Y Packed COMM Decimal (7,2) Y Packed DEPTNO Decimal (2) Y Packed Open Command [OP] This command opens an SQL database. Only one database can be opened at a time. For example, >OPEN ALLBASE DBEname Owner The Suprtool syntax for Allbase is slightly different from that for Oracle. Instead of a password, Suprtool expects the "owner" name that is associated with a specific Allbase database. Select Command [SEL] Specify a select-statement for an open SQL database. The Select command in Suprtool supports all the select-statement features of the open SQL database. Only one Select command can be specified at a time. Allbase sorts data: >select * from user.account@emp order by ename Suprtool sorts data: >select * from user.account@emp >sort ename The Select command can contain any expression or clause that is supported by the SQL database. However, there are some Suprtool commands that may perform faster than select-statements (e.g., Suprtool Sort command versus Order By). Allbase Performance Suprtool provides you with easy ways to let either Allbase or Suprtool do most of the work. Whether it is best to use Allbase or Suprtool really depends on your specific machine, database, and application. You can use the Select command to force Allbase to do most of the processing or you can use Suprtool to do the work. In our testing, Suprtool consistently sorts 1.5 times faster than Allbase. Your performance improvements may be different from ours, so we recommend that you take some common tasks and try them with both tools. Here is an example of sorting with Allbase and then with Suprtool: Allbase sorts data: >select * from user.account@emp order by ename Suprtool sorts data: >select * from user.account@emp >sort ename Restrictions Suprtool still has the following restrictions in Allbase: 1. Suprtool needs the ownername to select a specific table. For example, >select * from purchdb.orders In this example, the owner is purchdb and the tablename is orders. 2. Suprtool cannot handle the Allbase date format. However, the To_Char function in the select-statement can convert the Allbase date format into something that Suprtool can handle. For example, >select qty,TO_CHAR(date,'YYYYMMDD') from manufdb.testdata >def mydate,date[1],8 {redefine testdate} >item mydate,date,yyyymmdd {define the date format} >if mydate<=$today(-900) STExport There are four enhancements to STExport. The HTML command lets you specify output for web pages. The Delimiter Space option is now the default if you specify HTML Preformatted. The new Heading Column option makes it much easier to specify individual headings for each field in the input source. The Date Invalid option lets you decide how STExport should handle invalid dates in the input source. Invalid Dates By default, all invalid dates are formatted as asterisks. STExport treats any date that does not have a valid century, year, month, or combination (e.g., February 29, 1999) as invalid. You can specify how you want STExport to format invalid dates by using the Invalid option of the Date command. If you specify $date invalid null STExport produces a zero-length field if you specify Column Variable, and spaces if you specify Column Fixed. If you want to specify an explicit string for all invalid dates, do so after the Invalid option. For example, $date invalid "%%%%%" will cause STExport to produce a string of five percent signs for any invalid date. Delimiter Command You can now specify a space between fields, by doing Delimiter Space. If you specify HTML Preformatted, then Delimiter Space is enabled. Heading Command It is difficult to get headings right when you have to specify all the quotes and delimiters with the Heading Add option. Instead, try the Heading Column to specify individual column headings without your having to type in formatting information. STExport then uses whatever quote and delimiter settings apply to byte-type fields. For example, if you specify: Heading Column 'Account' Heading Column 'First Name' Heading Column 'Last Name' Heading Column 'City' Heading Column 'State' and Quote Double and Delimiter Comma are in effect, then STExport produces the following heading: "Account","First Name","Last Name","City","State" Notes You cannot combine the Add and Column options. You must specify one or the other. If you start with Heading Add and then later specify Heading Column, STExport erases the heading you created with Heading Add and starts over with the first column that you specify with Heading Column. Similarly, if you start with Heading Column, a Heading string or Heading Add will start over with a new heading. HTML Command [HT] Use HTML to format web pages for either Internet or Intranet applications. HTML None | Preformatted | Table | Title string | Heading string (Default: None) Web applications expect data in a special format called the Hypertext Markup Language (HTML). Use the HTML option to request that STExport format the input file into HTML format. Maximum Size of HTML Files Web browsers often cannot process large documents. The maximum size depends on the browser, the version of that browser, the operating system it's working on, and how much physical memory is present on the client machine. We suggest that you limit your web pages to less than 1,000 lines and restrict the number of columns, unless you are certain that your users can handle larger files. This advice reflects not a limitation of STExport, but a limitation of how web browsers work. Preformatted Format To preserve the columns and spacing of each output line, use the HTML Preformatted command. This command puts an HTML 
  tag
          around  all the data in the input file.  Most web browsers display
          preformatted  text  in  a  fixed-width  font  such   as   Courier.
          Therefore,  if  you  specify  HTML  Preformatted,  you should also
          select Columns Fixed.


   Table Format

          Use HTML Table to create output in HTML  table  format.   STExport
          creates  tables with a border between each column and row.  Tables
          make it easier to read tabular information, but  not  all  browers
          support tables.


   Title

          All  HTML  documents must have a title.  By default, STExport uses
          the title "This is the Title".  You should specify your own  title
          using the Title option.


   Heading

          The  heading  appears before the column headings and the data from
          your input file.  By  default,  there  is  no  heading.   Use  the
          Heading option to specify your own heading.


   Column Headings

          If  you  specify  HTML  Table,  use the Heading command to specify
          column headings for HTML output.  The  Heading  Fieldnames  option
          produces  acceptable  column  headings,  but  it  is better to use
          Heading Column to specify a string for each of the fields in  your
          input file.


   Roman-8 Characters

          HP  3000 and HP 9000 computers use the Roman-8 character set.  The
          characters in the Roman-8 set are similar to,  but  not  identical
          with,  the  ISO-8859-1  character  set.   Web  pages  must use the
          ISO-8859-1 character set.

          When formatting byte-type fields, STExport attempts to convert any
          Roman-8   input   character   into  the  corresponding  ISO-8859-1
          character.  Those characters that cannot be converted are  dropped
          from the output.  The following characters cannot be converted:


               ©  169    grave mark

               ª  170    circumflex

               ¬  172    tilde

               ¾  190    function symbol

               Þ  222    beta symbol

               ë  235    capital-S, Icelandic

               ì  236    small-S, Icelandic

               î  238    capital-Y, umlaut


   Notes

          If you specify HTML Table, STExport sets

               Quotes None

               Delimiters None

          If you specify HTML Preformatted, STExport sets

               Quotes None

               Delimiters Space

               Columns Fixed

          In  either  case, changes cause STExport to print a warning to let
          you know that these options have changed.  If you do  want  quotes
          around  byte-type  fields  or  delimiters  between fields, specify
          these options after selecting the HTML option.



   Suprtool and the Year 2000


          We are in the midst of addressing many of  the  issues  that  face
          data-processing departments at the impending turn of the century.


   Two-Digit Years

          Currently  the date format of yymmdd collates (sorts) correctly if
          the date is not beyond December 31, 1999.  For example,  say  that
          the  current date is 961210; this is numerically is less than next
          year, the date value of which is  971210.   At  the  turn  of  the
          century,  dates in the yymmdd format (or yymm) will no longer sort
          correctly because the value of December 10, 2000 (001210) is  less
          than the value of dates before January 1, 2000, (e.g., 961210).

          Consequently,  if  we  have  a  date beyond 1999, stored in yymmdd
          format, a relative operation such as

              >if date-field >= $DATE(96/12/10)

          will not find the date of December 10, 2000.


   If Command and Year 2000

          Because dates beyond 1999 will not collate properly for the yymmdd
          format, the If command now produces an error if the year specified
          in a $date or $today function  is  greater  than  1999,  the  date
          format  is  yymmdd  or  yymm,  and  you  are performing a relative
          operation, such as greater than.


   Set Date Cutoff

          Suprtool used to assume 19 for the century for any  user-specified
          $date with a two-digit year.

          For example:

              >item date-field,date,ccyymmdd
              >if date-field <= $DATE(09/12/26)

          The $date function used to convert the user-specified $date to

              1909/12/26

          in  order  for  it  to  be  compared  to  the date-field format of
          ccyymmdd.  Now with Set Date Cutoff  xx,  we  assume  20  for  the
          century  if  the year specified in the $date function is less than
          the value of Set Date Cutoff.

          For example:

              >set date cutoff 10
              >item date-field,date,ccyymmdd
              >if date-field <= $DATE(09/12/26)

          Suprtool in this case assumes that the full $date is

              2009/12/26

          Conversely, if the value of  Set  Date  Cutoff  was  5,  then  the
          assumed century would be 19.

          The default value of Set Date Cutoff is 10.


   Set Date ForceCentury

          Set Date ForceCentury On does not allow a yy date to be entered in
          the $date function.  Instead, it forces the user to enter  a  full
          ccyy date.

             >set date forcecentury on
             >item date-field,date,ccyymmdd
             >if date-field >= $DATE(96/12/10)

           Error:You must specify the century or Set Date ForceCentury off

          The default value for Set Date ForceCentury is off.


   New Date Formats

          The Item command now supports four new date formats:

            Format                 Date-Types
            ccyymmdd               X8 Z8 J2 K2 P10
            ccyymm                 X6 Z6 J2 K2 P8
            ccyy                   X4 Z4 J1 K1
            aammdd                 X6


   AAMMDD Date Format

          The  aammdd  date  format was developed by James Overman of HP for
          use in their MM3000 product.  This format is only available for X6
          data-type.

          AAMMDD is similar to yymmdd, but the year portion of the date uses
          a combination of numbers and letters of the alphabet to  represent
          years beyond 1999.

          By  substituting a letter of the alphabet in the first position of
          the year, we can extend a six-digit date and also ensure that  the
          dates collate correctly.  For example,

            YY of AAMMDD     CCYY
            A0 - A9          2000 - 2009
            B0 - B9          2010 - 2019
            C0 - C9          2020 - 2029

          Because   letters  are  greater  than  numbers  in  the  collating
          sequence, we can ensure that aammdd dates beyond 1999  will  order
          correctly.


   Invalid Dates

          The  If  command  now  has a $invalid function to find all invalid
          dates for a particular field.  An invalid date is any number of  a
          particular  date  format  whose date equivalent cannot be found on
          the calendar.  For example, a date with a  month  of  99  will  be
          considered invalid.

             >base store.demo
             Database password [;]?
             >get d-sales
             >item deliv-date,date,ccyymmdd
             >if $invalid(deliv-date)
             >out baddates,link
             >xeq


   Converting Dates

          Suprtool is capable of converting dates from one format to another
          using a variety of Suprtool features.  You will see  how  Suprtool
          can  convert  common  dates without the century to those that have
          the century included.  Although Suprtool can convert your data, it
          is  up  to you to change your programs so that they will recognize
          the reformatted dates as valid.  Adager, a third-party program for
          changing IMAGE database structures, has the ability to change date
          fields.  Suprtool can convert data in IMAGE databases, flat files,
          self-describing files, and KSAM files.


   Case 1:  Converting a J2 date from YYMMDD to CCYYMMDD

          Because  Suprtool  can  do arithmetic expressions, you can alter a
          date to put 19 in front of it.  But what if all the dates are  not
          actually dates but rather are filled with 9's as some sort of flag
          to your application?

          Say you have a dataset with two date fields, which are J2 items in
          the date format yymmdd.

          Database: STORE.DB.GREEN

              D-SALES          Detail    Set 5
                 Entry:                     Offset
                    CUST-ACCOUNT         Z8      1  (!M-CUSTOMER)
                    DELIV-DATE           J2      9
                    PRODUCT-NO           Z8     13  (M-PRODUCT)
                    PRODUCT-PRICE        J2     21
                    PURCH-DATE           J2     25
                    SALES-QTY            J1     29
                    SALES-TAX            J2     31
                    SALES-TOTAL          J2     35
              Capacity: 602 (14)  Entries: 10  Highwater: 10  Bytes: 38

          First,  you  need  to  know  and  understand your data.  Are there
          invalid dates?   If so, does the value  have  some  other  logical
          meaning?  Are they, for example, flags for your application?

            >get d-sales
            >item deliv-date,date,yymmdd
            >item purch-date,date,yymmdd
            >if $invalid(deliv-date) or $invalid(purch-date)
            >list
            >xeq
            >GET D-SALES (9) >OUT $NULL (0)
            CUST-ACCOUNT    = 10010          DELIV-DATE      = 999999
            PRODUCT-NO      = 50513001       PRODUCT-PRICE   = 19220
            PURCH-DATE      = 999999         SALES-QTY       = 2
            SALES-TAX       = 2691           SALES-TOTAL     = 21910

            >GET D-SALES (10) >OUT $NULL (1)
            CUST-ACCOUNT    = 10010          DELIV-DATE      = 125213
            PRODUCT-NO      = 50513001       PRODUCT-PRICE   = 19220
            PURCH-DATE      = 970135         SALES-QTY       = 2691
            SALES-TAX       = 21910          SALES-TOTAL     = 21910

            IN=10, OUT=2.  CPU-Sec=1.  Wall-Sec=1.

          Two records do not contain proper dates: the first record contains
          all 9's, which are probably used as some sort of  flag.   You  may
          need  to  add an additional leading "99" to dates such as these to
          match the ccyymmdd date format.  The second  record,  however,  is
          obviously  wrong.   Use Dbedit to correct this record and continue
          on to convert the other dates.  The point here is  that  you  must
          know  your  own  data: in order to correctly convert to a new date
          format, you must understand what are true invalid dates  and  what
          are flags that you want to keep.

          After fixing up the dates that are incorrect you can now get ready
          to start converting the dates.

          You can convert prefix 19 in front of all of the appropriate dates
          by  using  the  following extract statement.  Please note that you
          are updating this directly.  So that you can redo this task if  it
          is stopped halfway, you only convert those dates that have not yet
          been converted.

               >get d-sales
               >item purch-date,date,yymmdd
               >if not $invalid(purch-date) and purch-date < 999999
               >update
               >ext purch-date = purch-date + 19000000
               >xeq

          If you have records containing years between 00 and 10,  and  they
          actually represent 2000 and 2010, you should change the If command
          to update them in a separate pass.  The  first  pass  updates  all
          twentieth-century dates:

               >get d-sales
               >item purch-date,date,yymmdd
               >if not $invalid(purch-date) and purch-date / 10000 > 10
               >update
               >ext purch-date = purch-date + 19000000
               >xeq

          The second pass updates those dates in the twenty-first century:

               >get d-sales
               >item purch-date,date,yymmdd
               >if not $invalid(purch-date) and purch-date / 10000 <= 10
               >update
               >ext purch-date = purch-date + 20000000
               >xeq

          You  now have converted all the J2 yymmdd dates to ccyymmdd format
          and added the correct century to the date.


   Case 2:   X6 YYMMDD Data to X8 CCYYMMDD

          The following Suprtool task shows you how to generate a  new  file
          to put into a new database with dates in a different format.

          Consider  the  deliv-date  and  purch-date  fields  of the D-Sales
          dataset:

          Database: STORE.DBOLD.ACCOUNT

              D-SALES          Detail    Set 5
                 Entry:                     Offset
                    CUST-ACCOUNT         Z8      1  (!M-CUSTOMER)
                    DELIV-DATE           X6      9
                    PRODUCT-NO           Z8     15  (M-PRODUCT)
                    PRODUCT-PRICE        J2     23
                    PURCH-DATE           X6     27
                    SALES-QTY            J1     33
                    SALES-TAX            J2     35
                    SALES-TOTAL          J2     39
              Capacity: 611 (13)  Entries: 15  Highwater: 15  Bytes: 42

          You want to convert to a date format with room for  a  cc  at  the
          beginning of the deliv-date and purch-date:


             Database: STORE.DB.ACCOUNT

              D-SALES          Detail    Set 5
                 Entry:                     Offset
                    CUST-ACCOUNT         Z8      1  (!M-CUSTOMER)
                    DELIV-DATE           X8      9
                    PRODUCT-NO           Z8     17  (M-PRODUCT)
                    PRODUCT-PRICE        J2     25
                    PURCH-DATE           X8     29
                    SALES-QTY            J1     37
                    SALES-TAX            J2     39
                    SALES-TOTAL          J2     43
              Capacity: 608 (16)  Entries: 0  Highwater: 0  Bytes: 46

          In  order to convert these dates you must put either a 19 or 20 in
          front of the yymmdd date, depending on  the  value  of  the  year.
          Before  you  can  do either of these you must confirm, once again,
          that you have no invalid dates.

             >base store.dbold
             >get d-sales
             >item deliv-date,date,yymmdd
             >item purch-date,date,yymmdd
             >if $invalid(deliv-date) or $invalid(purch-date)
             >list
             >xeq

          Once you have confirmed that there are no invalid  dates  you  can
          start  converting  the dates that you have.  Because there are two
          date-fields in this dateset,  you  must  be  careful  to  add  the
          appropriate  century  for the proper field.  For this example, you
          assume that if a year is less that 1950 then the century should be
          20.

          You  can  easily  convert  each  date  by  processing  each  field
          separately using an intermediate self-describing file:

             >base store.dbold,1
             Database password [;]?

             >get d-sales
             >set squeeze off
             >item deliv-date,date,yymmdd
             >if deliv-date >= $DATE(1950/01/01)
             >out sales01,link
             >ext cust-account
             >ext "19"
             >ext deliv-date / sales-total
             >xeq
             IN=15, OUT=14.  CPU-Sec=1.  Wall-Sec=5.

          Now insert 20 to the century for the appropriate records:

             >base store.dbold
             >get d-sales
             >if deliv-date < $DATE(1950/01/01)
             >ext cust-account
             >ext "20"
             >ext deliv-date / sales-total
             >out sales01,link,append
             >xeq

          Now you can convert the other field from the flat file,  (Sales01)
          and add a century to the purch-date field:

             >reset
             >base                 {close the open database}
             >in sales01
             >item purch-date,date,yymmdd
             >if purch-date >= $date(1950/01/01)
             >set squeeze off
             >out sales02,link
             >ext cust-account / product-price
             >ext "19"
             >ext purch-date / sales-total
             >xeq
             IN=15, OUT=15.  CPU-Sec=1.  Wall-Sec=1.

          Because  you extracted all 15 records you know you do not have any
          records with the purch-date field that need to be updated  with  a
          20.

          You can insert the records into the new database:

             >base store.db
             >in sales02
             >put d-sales
             >xeq

          Now you have converted two dates from X6 format to an X8 format.


   Case 3:  Different Date Formats X6 MMDDYY Data to X6 YYMMDD

          The  following  Suprtool task shows you how to convert a date in a
          self-describing file from mmddyy to yymmdd format.

          Consider the following self-describing file  with  the  deliv-date
          and purch-date fields:

               File: SALES04.DATA.ACCOUNT   (SD Version B.00.00)
                  Entry:                     Offset
                     CUST-ACCOUNT         Z8      1
                     DELIV-DATE           X6      9       <>
                     PRODUCT-NO           Z8     15
                     PRODUCT-PRICE        I2     23
                     PURCH-DATE           X6     27       <>
                     SALES-QTY            I1     33
                     SALES-TAX            I2     35
                     SALES-TOTAL          I2     39
               Limit: 115  EOF: 15  Entry Length: 42  Blocking: 97

          You  want to convert these two dates to a format of yymmdd, before
          adding a century in  front  of  the  year.   This  can  be  easily
          accomplished by defining each sub part of the date, and extracting
          those parts in the new order.

             >in sales04
             >def deliv-date-mm,deliv-date[1],2
             >def deliv-date-dd,deliv-date[3],2
             >def deliv-date-yy,deliv-date[5],2
             >def purch-date-mm,purch-date[1],2
             >def purch-date-dd,purch-date[3],2
             >def purch-date-yy,purch-date[5],2
             >ext cust-account
             >ext deliv-date-yy
             >ext deliv-date-mm
             >ext deliv-date-dd
             >ext product-no / product-price
             >ext purch-date-yy
             >ext purch-date-mm
             >ext purch-date-dd
             >ext sales-qty / sales-total
             >out sales05,link
             >xeq

          You now have a file with  the  dates  in  yymmdd  order,  but  the
          self-describing information shows three separate fields.

              File: SALES05.DATE.ACCOUNT     (SD Version B.00.00)
                 Entry:                     Offset
                    CUST-ACCOUNT         Z8      1
                    DELIV-DATE-YY        X2      9
                    DELIV-DATE-MM        X2     11
                    DELIV-DATE-DD        X2     13
                    PRODUCT-NO           Z8     15
                    PRODUCT-PRICE        I2     23
                    PURCH-DATE-YY        X2     27
                    PURCH-DATE-MM        X2     29
                    PURCH-DATE-DD        X2     31
                    SALES-QTY            I1     33
                    SALES-TAX            I2     35
                    SALES-TOTAL          I2     39
              Limit: 115  EOF: 15  Entry Length: 42  Blocking: 97

          You  can  convert  these  several fields to one field with another
          extract task:

              >in sales05
              >def deliv-date,9,6,byte
              >def purch-date,27,6,byte
              >item deliv-date,date,yymmdd
              >item purch-date,date,yymmdd
              >ext cust-account
              >ext deliv-date
              >ext product-no
              >ext product-price
              >ext purch-date
              >ext sales-qty / sales-total
              >out sales06,link
              >xeq
              IN=15, OUT=15.  CPU-Sec=1.  Wall-Sec=1.

          You now end up with a file that looks like this:

              File: SALES06.DATA.ACCOUNT   (SD Version B.00.00)
                 Entry:                     Offset
                     CUST-ACCOUNT         Z8      1
                     DELIV-DATE           X6      9       <>
                     PRODUCT-NO           Z8     15
                     PRODUCT-PRICE        I2     23
                     PURCH-DATE           X6     27       <>
                     SALES-QTY            I1     33
                     SALES-TAX            I2     35
                     SALES-TOTAL          I2     39
               Limit: 115  EOF: 15  Entry Length: 42  Blocking: 97

          You then add the century to these fields as described above.


   Bugs Fixed


          Allbase.  The following  problems  in  Allbase  access  have  been
          fixed:

          1. Suprtool  now properly sets Char fields to spaces if the values
             in the Allbase column are null.

          2. Suprtool now correctly reports the length of VarChar columns.

          3. Suprtool would fail with strange errors if the input source was
             a large SQL table.

          Chain  Command.  The  Chain  command no longer requires a specific
          reset when a "Missing quote" syntax error occurs.

          Edit Command.  Dbedit no longer allows invalid numbers  in  double
          integer fields.  This was caused by a bug in the NM version of the
          DBINARY intrinsic.

          Export  Command.  STExport  now  accepts  *filename  as   a   back
          reference to a file equation.

          STExport  can  now  build  an  output file that is large enough to
          handle a combination  of  Columns  Fixed  and  Heading  Fieldnames
          options.

          STExport no longer puts a trailing comma on the Heading line.

          STExport no longer truncates large numbers when they are formatted
          with decimal places and signs.

          Extract Command.  An obscure error  message  was  printed  when  a
          conversion  or  arithmetic expression overflowed the target field.
          For example, assume that sales-qty has values  greater  than  100.
          The following commands will result in overflow:

                >get    d-sales
                >define small-field,1,2,display
                >extract  small-field = sales-qty
                >output   somefile
                >xeq

               Error:  Overflow of arithmetic expression

               Input record number: 0

          Form  Command.  The  Form  command  now  correctly  shows the Item
          attributes of a specified field when the input source is an  IMAGE
          dataset.

          Table  Command.  The  Table  command  now  prints a warning when a
          fieldname specified does not exist in the self-describing file.

          If  Command.  The  If  command  produced  an  error  if  you  were
          comparing  two  packed-decimal  fields  with a different number of
          decimal places.

          Output=Input.   Suprtool treats an Output=Input  operation  as  an
          error  if  the  input  file  is  self-describing and the extracted
          record size is not the same as the  input  file.   This  operation
          previously  would  create  files  where the data did not match the
          self-describing file.

          Progress Messages.  When selecting data from  IMAGE  datasets  the
          number of records reported with In= was incorrect.

          STExport.  The following problems in STExport have been fixed:

          1. When  STExport  created  variable-length output files, a lot of
             disc space could be wasted.  This happened whenever there was a
             big difference between the input files EOF and LIMIT.

          2. The   Default  option  of  the  Floating  command  was  spelled
             incorrectly.

          3. When converting Double Integer fields  to  ASCII,  spaces  were
             left at the end of the record.

          4. If  the  input source had compound date fields, the Xeq command
             would produce an error  message  about  an  invalid  date  type
             rather than just processing the date field correctly.

          Item  Command.  Calendar  dates  beyond 1999 are now being handled
          properly.

          Output Command.  Suprtool no longer fails  with  an  "Extent  size
          exceeds maximum" error when it attempts to build very large output
          files.

          Sort Command.  Suprtool  no  longer  fails  with  an  "Illegal  DB
          register setting" error when it sorts very large output files.

          Total Command.  The Total command no longer upshifts filenames.

          Verify  Command.  The  Verify All command no longer prints the SQL
          information twice.

          Verify Command.  The Verify command without parameters now  prints
          out Select command information.


   QLIB and Bonus Enhancements


          Suprtool  comes  with  an  array  of contributed tools in the QLIB
          library.  Your Robelle license may also entitle you to receive our
          five  Bonus  programs.   All of these tools are updated throughout
          the year, not necessarily in  sync  with  Suprtool  or  any  other
          Robelle product.

          If  you  have  the  Bonus  programs,  you  may  only  use  them on
          appropriately licensed CPUs.  The QLIB programs, however,  may  be
          used  on  any  CPUs  and  given  away  freely.   The most recently
          released Bonus and QLIB tools are as follows:

               Bonus                   Contributed
               Compare/iX 2.4          Helpcomp 2.2
               HowMessy 2.4            Printdoc 1.5
               Select 3.6              Prose 4.1
               Spell 1.5               Pscreen 89C
               Xpedit 1.5              Qcopy 4.3
                                       Qhelp 2.2


   Bonus Programs

          Since the last major release  of  Suprtool,  the  following  Bonus
          programs have been enhanced with these new features:


   Compare/iX 2.4

          The  NM Compare program compares two text files and prints out the
          differences for you.  Compare works with both  Suprtool  workfiles
          and  regular  Keep files.  The input filenames can be specified in
          the Info=  string.   See  Compare.Docchg.Robelle  for  a  detailed
          change notice for Compare.


   HowMessy 2.4

          HowMessy  provides fast reports on the internal efficiency of your
          database.  Now use HowMessy  to  report  statistics  on  IMAGE/SQL
          datasets larger than 4 gigabytes.  See HowMessy.Docchg.Robelle for
          a detailed change notice for HowMessy.


   Select 3.6

          Select is a menu front-end that works on  any  terminal,  allowing
          people to select tasks from a list of choices.  User commands (and
          UDCs) support 32  parameters  and  :Escape.   Variable-length  and
          Qedit-type  command  files are allowed.  See Select.Docchg.Robelle
          for a detailed change notice for Select.


   Spell 1.5

          Spell is a fast spelling checker for the HP 3000 that  reads  both
          Qedit  and  EDIT/3000  files.   Spell  can  read Qedit's new Jumbo
          files.


   Xpedit 1.5

          Xpedit is a simple screen editor that is handy for  editing  small
          files  using  VPLUS  block-mode.   Inverse video was added to some
          error messages to make them stand out more prominently.   See  the
          file  Xpedit.Docchg.Robelle for a complete description of Xpedit's
          changes.


   QLIB Programs

          Since the last major  release  of  Suprtool,  the  following  QLIB
          programs have been enhanced with these new features:


   Printdoc 1.5

          Printdoc  is  an  easy-to-use utility for printing Robelle manuals
          and other documentation.  Printdoc now supports LaserJet 4  and  5
          printers.


   Prose 4.1

          Prose is the text formatter that we use for all our documentation,
          from user manuals to on-line Help.  Prose can now read Jumbo  text
          files that are wider than 256 characters.  Prose can also generate
          RTF output with the .out (rtf)  command.   See  the  Prose.Docchg.
          Robelle file for a detailed change notice for Prose.


   Qcopy 4.3

          Qcopy  is a contributed program that reads and writes Qedit files.
          Its Qeditaccess routine decodes the  files  for  Qcopy.   Although
          Qeditaccess  can  read  Qedit's  new  Jumbo files, it cannot write
          them.


   Qhelp 2.2

          Qhelp is an on-line user help facility that you can tie into  your
          own software.  We use it ourselves in all our software products.


   Appendix:  Non-Robelle Installation


          Suprtool  is  usually  installed  on  your  HP 3000 in the Robelle
          account.  The installation instructions  earlier  in  this  change
          notice  make  that  assumption.   However,  you can easily install
          Suprtool  into  another  account  by   following   these   general
          guidelines:

          1. Create or upgrade the structure of your account

          2. Restore the files into your account

          3. Alter any installation jobs to log on to your account

          4. Stream the installation jobs

          For  example, to install Suprtool into an account called Tools and
          into the same groups as Suprtool would have used  in  the  Robelle
          account (Pub, Doc, Help, etc.), follow these steps.


   Step 1:  Upgrade Account Structure

          With   a  text  editor,  change  all  account  references  in  the
          Robelle.Pub.Sys job from "robelle" to "tools."  Don't worry  about
          changes  to  some  of the comments in the job.  Note that this job
          will change the capabilities, access flags, and logon password  of
          the  Tools  account.  Examine the job carefully to ensure that the
          changes do not cause problems for other software  in  the  Robelle
          account.

               :run qedit.pub.robelle
               /text robelle.pub.sys
               /change "robelle"(upshift) "tools" all


   Step 2:  Restore Files

          Restore  the  files  into  your  account,  which  is Tools in this
          example.

               :file suprtape;dev=tape
               :restore *suprtape;@.@.robelle


   Steps 3 and 4:  Alter and Stream Installation Jobs

          Now log on to the Tools account instead of the Robelle account.

               :hello mgr.tools
               :run qedit.pub.robelle

          Before streaming any jobs, you must alter them so that they log on
          to  the  Tools  account  instead of the Robelle account.  Make the
          following change to each job in the  Suprjob,  Job,  and  Purgejob
          groups:

               /text install.suprjob
               /change "robelle"(upshift smart) "tools" all
               /keep
               /exit
               :stream install.suprjob

          We  have  used  Robelle's  Qedit  text editor to make the required
          changes to the account name in this example, but you can  use  any
          editor.   Just  make  sure  that  all occurrences of "robelle" are
          changed,  regardless  of  whether  "robelle"  is   in   uppercase,
          lowercase,  or  mixed  case.   Only  change "robelle" when it is a
          separate word, not when it is embedded in a longer word.