suprtool-s

 

SUPRTOOL™ 5.8.10

Database Handyman
for HP e3000 and HP-UX and Linux

Quick Reference Guide

 

Suprtool Commands by Function

In/Out

Process

Select

Control

Add

Clean

Define

Edit

Base

Delete

Duplicate

Export

Chain

Extract

If

Link

Get

Key

Item

Q

Input

List

Table

Reset

Numrecs

Put

 

Userpause

Open

Sort

 

Xeq

Output

Total

 

=calc

Select

Translate

Update

 

:o/s cmd

 

Dbedit

Suprlink

STExport

Add

Input

Columns

Input

Change

Join

Clean

Output

Delete

Link

Date

Quote

File

Output

Decimal

Reset

List

Reset

Delimeter

Sign

Modify

Xeq

Escape

Spaces

Q

 

Floating

Xeq

 

 

Heading

Xml

 

 

HTML

JSon

Zero

 

Commands Common to All Modules

Before

Form

Redo

Use

Do

Help

Set

Verify

Exit

Listredo

 

 

 

 

Robelle Solutions Technology Inc.

Suite 372, 7360 137 Street
Surrey, BC Canada
V3W 1A3

Phone:  (604) 501-2001

Support:  (289) 480-1060

E-mail:  support@robelle.com

Web:  www.robelle.com

Copyright 1985-2016 Robelle Solutions Technology Inc.


Suprtool Commands

The following list describes the commands that Suprtool understands at the ">" prompt. Continue command lines with "&" and combine commands on one line with ";". Shorten command names to the substring printed in capitals (e.g., BA for Base). You can use CI !variables in commands on MPE and $environment variables on HP-UX.

Add   tablename

Add   ownername.tablename

 

The Add command inserts records into an Oracle table. Before records can be added, an Oracle database must be open. You can specify just the tablename or the ownername.tablename combination. The specified table name must be a valid table, not a view. For adding records to an Eloquence database please see the Put command.

>add customer

BAse   [[host][:service]/]database

Open an Eloquence database (close current base). Base command also follows value of Eloquence variable EQ_DBSERVER.

  base sample,5

  base :eloqdb/sample,5

  base hostname.robelle.com:eloqdb/sample,5

BAse   [system#] base [ mode ] [ pass ]

Open an IMAGE database (close current base). Specify remote base with system# prefix. Use "?" for pass to prompt with no echo in batch. (Default: logon = system, mode = 1, pass = ";")

>base actrec.dat      {mode = 1, pass = ";"}

>base actrec.dat,5,reader

>base sys2#act.db,5   {remote on sys2}

Before   [ start [ /stop ]] | string | [ ALL | @ ]

Redo commands with chance to modify. (Default: previous)

Clean             [ SPECIAL | <string> <range> ]

Specifies what characters to clean when using the $Clean function. The SPECIAL keyword means characters  from Decimal 0 to Decimal 31. Characters that match those defined with the Clean command, will be replaced by the value defined by Set CleanChar, which defaults to space. Clean characters can be defined with decimal notation "^" and/or the actual character as a string. To find records with specific characters see the $findclean function in the IF command.

  >in mysdfile

  >clean special,"^128:^190","^255","}"

  >extract byte-field = $clean(byte-field)

Chain   setname search-field = [ key-values | table ]

Read an IMAGE or Eloquence dataset by search path, TPI, or B-tree and select specific records (masters) or chains of records (details) for explicit key values; requires previous Base command. Specify the key values either by an explicit list of values separated by commas or by a table name (see Table command on page 9.)

>chain d-sales,customer="12345"

>table slist,customer,file,slist.db

>chain d-sales,customer=slist

>chain d-sales,part-no="3YJ "

Define   field  byteposition  sublen [ type ][ subcount ]

Define a new data field (name <33 characters) by specifying an absolute location and format. First byte of input record is 1. See Data-Types on page 12. (Default: type = byte, subcount = 1)

>define city,11,10     {byte type}

>define TransType,1,2,int  {1 = first}

>def amt,11,2,int,12   {amt repeats 12 times}

Define   field  fieldname  [ qualifier ]

Define a new data field that is relative to the position of an existing database or Defined field. The qualifier equals

[(subscript)] [ [offset] ] [sublen] [type] [subcount]

The (subscript) specifies one sub-item of a compound item such as 5J2; 1 is the first and default sub-item. The offset specifies a byte offset from the existing location of fieldname; 1 is the default. The sublen and type override the existing size and data-type. See Data-Types on page 12. When you specify a subcount, the sublen is the byte-length of each subfield.

>define itemcount,status,2,int

>define costtotal,status[2],4,ieee

>define middle,name(2)

DELete

Delete selected records from IMAGE or Eloquence input dataset using DBDELETE.

DO   [ start [ /stop ]] | string | [ ALL | @ ]

Repeat previous commands without modifying them.

>do 5/9   {repeat several commands}

>do if    {repeat last If command}

DUplicate   ONLY | NONE  KEYS [num] | RECORD
                   [ COUNT ] [ TOTAL field ... ]

Include or exclude duplicate records for the output file. There are options to count the duplicate output records, and to total up to fifteen fields.

>duplicate none keys         {remove dups}

>duplicate only keys         {keep only dups}

>duplicate none keys 1       {first sort level}

>duplicate none keys count   {count dups}

>duplicate none keys total sales-amt, units-sold

EDit

Edit a database (see Dbedit Subsystem on page 18). Use the Base command before using the Edit command.

Exit   [ ABORT | SUSPEND | XEQ ]

Perform task and return to parent process. Exit Abort abandons task. Suspend lets you return to task later.

>exit          {default = Xeq}

>exit suspend  {stop without executing}

EXPort   [ stexport-command ]

Invoke STExport/MPE as a son process or pass a command to STExport.

>export input sdfile

You cannot use Suprtool/UX's Export command to invoke STExport/UX, but you can run STExport/UX by itself.

>/opt/robelle/bin/stexport

EXTract   field [ (subscript)]
                [ = value | = field2 | = expression ]
                [ ,..  ]

Create output records by stringing together fields from the input record and constant values. Extracts are cumulative; you can use multiple extract commands. Extract can use dates (see Item command on page 6) and expressions (one per command, must be at the end of the command).

>ext custnum,transtable(3)

>ext CustTotal=15000

>ext CustName," ",CustAddr

>ext SalePrice = (SalePrice * 1.10)

>ext Day = (date-field mod 100)

>ext ByteField = ^7   {= ^G (bell)}

>ext c = (6000 - cost)

>ext yesterdate = $today(-1)

>ext date-field = $date(*/*/*-1)

>ext ccyymmdd-i2 = $stddate(date-mmddyy)

>ext date-field = $stddate($days(date-field) +7)

>ext num-days = ($days(date1) - $days(date2))

>ext positive = $abs(num-expression)

>ext packed-field = $signed(integer-field)

>ext int-field = $truncate(real-expression)

>ext full-name = ($trim(first-name) + " " + &

                 $trim(last-name))

>ext lowercase = $lower(city)

>ext uppercase = $upper(city)

>ext $ETOA(ByteField) {EBCDIC to ASCII}

>ext field = $lookup(table,key,tabledata)

>ext new-price=$number(new-price-ascii)

>ext dbl-count=$counter

>ext count=$subcount(order-number)

>ext first-name=$split(full-name,first,"/")

>ext address(1)=$clean(address(1))

>ext mytotal=$total(sales-dollars)

>ext mysub=$subtotal(sales-dollars,order-number)

>ext int-field=$number(byte-number)

>ext edit-amount=$edit(sales-amount,"$$,$$$.99-")

>ext mycbcount=$subcount(sort-break)

>ext full-name=$proper(full-name)

>ext name=$translate(name)

EXTract   field1 [ (subscript1) ] \ field2 [ (subscript2) ]

Specify an inclusive range of fields to extract.

>ext ProductNo \ SalesQty

>ext SalesAmt(4) \ SalesAmt(6)

Form   [ SETS | ITEMS | PATHS |
           dataset | data-item | filename ]

Show information about IMAGE or Eloquence database, datasets, items, or self-describing files. On HP-UX output goes to $stdlist. On MPE output goes to Formout file, defaulting to $stdlist.

>form sets        {list the datasets}

>form d-cust      {details on d-cust set}

>form sdfile      {fields in self-describing file}

Get   setname  [ subset ]

Select an IMAGE or Eloquence dataset as input source; requires previous Base command. Subset options select only part of dataset. (Default: read the entire dataset.)

[ (startrec/endrec) ]          {range of record numbers}

[ (#n) ]                          {selects every nth record}

>get dtrans    

>get dtrans(100/200)    {first record is 1}

>get dtrans(#4)         {every 4th record}

Help   [ command-name | keyword [ ,section ]]

Provide access to the on-line user manual. Type a keyword (e.g., Help Access) or a command-name. HQ for brief help.

>help        {default is browse}

>hq list     {quick summary of List command}

IF   expression

Select a logical subset of the input source through an expression that tests one or more fields. See Record Selection on page 13 for details.

>if TransCode="01" and amount>1000

>if $upper(City)="VANC","SEAT","PORT"

>if StatWord.(4:1)=1 and addr=="@BC@"

>if a = alpha

>if $lookup(part-no-table,partno)

>if date=$today

>if TotalPrice <> (SalesTotal + Taxes)

>if $null(salestotal)

>if not $null(salestotal)

>if $invalid(date-field)

>if $stddate(dt-mmyydd) < $stddate(dt-aammdd)

>if (date-field mod 100) = 01

>if InDate <= $date(*/*-6/*)         {6 months ago}

>if ($days(ship-date) - $days(order-date)) > 14

>if $abs(oldprice - newprice) > 10

>if $truncate(total / months) > 10

>if $lower(city) = "niagara falls"

>if $ltrim(last-name) = "Armstrong"

>if $findclean(last-name)

>if $lookup(mytable,char-field,id-field)=id-field

Input   file  [ = dataset ] [ subset ]

Select an MPE or KSAM file as input source. The file can be the name of an existing disc file or a reference to a :File equation (*name). The =set option requires an open base because the file is assigned the same record format as the IMAGE set. The first record is 0, except for some KSAM files where it is 1. Subset options select only part of the file.

[ (startrec/endrec) ]          {range of record numbers}

[ ($first+10/$last-10) ]     {range of record numbers}

[ (#n) ]                          {selects every nth record}

>input log23      {disc file}

>input *          {$stdinx}

>input trans(#5)  {every 5th record}

Input   file  [ fileinfo ] [ subset ]

The file is the name of an HP-UX file. The fileinfo options are Reclen, LF, and NOLF. The Reclen option must be followed by the record length (in bytes) of each data record. Subset options select only part of the file.

[ (startrec/endrec) ]          {range of record numbers}

[ ($first+10/$last-5) ]       {range of record numbers}

[ (#n) ]                          {selects every nth record}

>input uxfile,reclen 80,nolf

>input sdfile           {requires ".sd" file}

ITem   itemname  DATE | DECIMAL  attribute

Define the date format or implied decimal places for an item. For compound items, all sub-items are assigned the same date type or number of decimal places. The attribute is a string for Date and the number of decimal places for Decimal. The Date attributes describe the format of the date.

yymmdd

X6 Z6 J2 K2 P8

ddmmyy

X6 Z6 J2 K2 P8

mmddyy

X6 Z6 J2 K2 P8

yyyymmdd

X8 Z8 J2 K2 P10

ddmmyyyy

X8 Z8 J2 K2 P10

mmddyyyy

X8 Z8 J2 K2 P10

ASK

J1

PHdate

J1 K1 J2 K2

calendar

J1 K1

yymm

X4 Z4 J1 K1

yyymmdd

J2 P8

Oracle

X7

ccyymmdd

X8 Z8 J2 K2 P10

ccyymm

X6 Z6 J2 K2 P8

yyyymm

X6 Z6 J2 K2 P8

aamm

X4

aammdd

X6

mmddaa

X6

ddmmaa

X6

ccyy

X4 Z4 J1 K1

SRNChronos

X6

mmyyyy

X6 Z6 J2 K2 P8

yyddd

X5 Z5 J2 K2 P8

ccyyddd

X7 Z7 J2 K2 P10

HPCalendar

J2 K2

EDSDate

J2 P8

JulianDay

J2

PHdate8

J1 K1 J2 K2

>item UnitCost,decimal,2

>item InDate,date,ddmmyy

>extract InDate=$today(-1) {yesterday}

>if InDate<=$date(*/*-6/*) {6 months ago}

[ Key ]   byteposition  bytelen [ type ] [ DESC ]

Define an arbitrary sort field anywhere within the input record. See Data-Types on page 12. (Default: type = byte, ascending)

>key 1,10          {first ten bytes}

>key 21,4,double   {double integer}

>key 25,2,int,desc {descending order}

>25,2,int,desc     {omitted the Key command name}

LINk   [ suprlink-command ]

Invoke Suprlink as a son process or pass a command to Suprlink .

>link input sdfile

You cannot use Suprtool/UX's Link command to invoke Suprlink/UX, but you can run Suprlink/UX by itself.

/opt/robelle/bin/suprlink

List      [ STANDARD ]
[ RECORD ]
[ DUPLEX ]
[ TITLE  "string" [ DATE format ] ]
[ HEADING  "string" [ "string"...  ]  ]
[ ONEPERLINE ]
[ LABEL ]
[ PCL format ]
[ NOREC ]
[ NONAME ]
[ NOSKIP ]
[ OCTAL | HEX | DECIMAL ]
[ CHAR ]
[ LEFTJUSTNUM ]
[ RIGHTJUSTNUM ]
[ TIME format ]                                                [FILE name APPEND RECLEN size]
[ LP ]
[ DEVICE name ]                                                     

Report selected records to Suprlist file, defaulting to $stdlist. Report selected records to $stdlist  to an LP device or to a file using the FILE option and control the size with the RECLEN option. Standard produces a columnar report, but the default is either a formatted listing or Octal/Char if the file is unstructured. Use Title and Heading to customize the listing. Record prints on attached printer, and Duplex prints two sided.

>list standard record {to attached printer}

>list stand title "Overdue Accounts"

LISTREDO   [ start[ /stop ] | [ ALL | @ ] string ]
            [ ;ABS | ;REL | ;UNN ] [ ;OUT file ]

Display previous commands; ",," is shortcut. (Default: last 20 commands)

>listredo 10/40

>listredo input {all Input commands}

Numrecs   number  |  percentage%

Specify size of input, output and Sortscr files as an absolute number of entries or as a percent of input size. Required for input tape files.

>numrecs 100000

>numrecs 5%      {5% of the input}

OPen   ALLBASE  dbename  owner
            ORACLE  username  password                      

Open an SQL database. Allbase is supported on MPE and HP-UX. Oracle is supported on HP-UX only.

>open allbase inventory anne

>open oracle scott tiger

>open oracle scott/tiger@machine

Output   file [ format ] [ APPEND | ERASE | TEMP ]

Define the name of the output file as one of the following: a new disc file (default), an existing disc or KSAM file (Append or Erase option), "*" for $stdlist, a reference to a :File equation (*name), or =Input to sort a file into itself. If you use List, Put, or Total, output defaults to $null. Output produces the same record format as the input source (adjusted by Extract commands), unless you override with format keywords:

DATA

default

ELSE

Records not qualified by if condition

KEY

sort keys only

PRN

PC file format

QUERY

self-describing

LINK

better self-describing format

ASCII

convert from numeric

DISPLAY

convert to display

NUM

J2 input record number only

NUM,KEY

J2 input record number & sort keys

NUM,DATA

J2 input record number & data record

NUM,QUERY

"numbers" format

NOLF

Do not write out line feeds to end of record

LF

Write out line feeds to end of record

Converted ASCII fields have a trailing sign (usually blank for positive values). Size depends on the binary field's format:

E2 R2

12 bytes

E4 R4

23 bytes

I1 J1

06 bytes

I2 J2

11 bytes

I3 J3

16 bytes

I4 J4

20 bytes

K1

05 bytes

K2

10 bytes

Zn

n+1 bytes

Pn

n bytes

>output newfile       {build newfile}

>output newfile,temp  {build temp file}

>output *,ascii       {$stdlist display}

>output =input        {sort into itself}

>output cleanfl,erase {existing file}

>output ksamfl,erase  {load KSAM file}

>output trans1,append {append to file}

>output sdfile,link   {self-describing}

>output lotus,prn     {ready to download}

Put   set [,[host][:service]/]database]

Put the output records to an Eloquence dataset by using DBPUT.

Put trans

Put dtrans,sample,5

Put dataset,:eloqdb/sample,5

put dataset,hostname.robelle.com:eloqdb/sample,5

Put   set [ ,base [ ,mode [ ,password ] ] ]

Put the output records to an IMAGE dataset by using DBPUT.

>put dtrans,actrec,4      {another base}

>get curmonth;put history {same base}

Q   [ "string" ]

Print a string on the CRT. (Default: print blank line)

>q "Sorting customer records" {quoted}

REDO   [ start[ /stop ]] | string | [ ALL | @ ]

Redo command lines; modify using MPE control codes.

>redo input    {repeat previous Input command}

Reset   [[ ALL | @ ] |  command [ ...  ]]

Reset aspects of the current task. Reset All resets all input and output commands, but not Define and Set options. (Default: Reset Delete, Sort, Key, If, List)

>reset if   {reset If command}

>reset all

SELect   statement

Specify an input source with an SQL select statement. Some processing options can be specified on the Select statement or with other Suprtool commands.

Using SQL to sort:

>select * from user.acct@emp order by name

Using Suprtool to sort:

>select * from user.acct@emp

>sort name

Set   option-name  value

Enable/disable options. See Configuration Options on page 14. Verify Set shows current values.

>set statistics off

SOrt   field  [ (subscript) ] [ DESCending]

Specify a field or a Defined field as a sort field. For compound items, the first sub-item is the default.

>sort CustNumber   {default ascending sort}

>sort total desc   {descending order}

>sort StreetAddress(2)

TAble   tablename  itemname  keyword  qualifier

Build a table of values for testing in the If and Chain commands, or for extracting from the table with the Extract command. The tablename (up to 16 characters) must be unique. Itemname is a database item or a Defined field, or a field in a self-describing file. For compound items, Table uses the first sub-item. The keyword is one of ITEM, FILE, or SORTED. The format of qualifier depends on keyword: for ITEM, a list of explicit data values; for FILE and SORTED, a filename whose data is unsorted or sorted by itemname, followed by an optional HOLD keyword and an optional Data keyword indicating a list of data fields to be loaded from the self-describing file.

Keyword

Qualifier

ITEM

value [ value ... ]

FILE or SORTED

filename [ HOLD ] [ DATA (fieldlist) ]

>table select,ccode,item,"ABCD","FILM"

>chain m-customer,ccode=select

>table select,ccode,file,extract.file,hold

>if $lookup(select,ccode)

>table t,ccode,file,sdfile,data(updatefield)

>update

>extract dbfield=$lookup(t,ccode,updatefield)

>table t,ccode,file,sdfile,data(updatefield)

>if dbfield=$lookup(t,ccode,updatefield)

 

TRanslate     TOUNREAD | TOREAD | “^from:^to”

Translate command allows you to specify a From and To-character in decimal notation, that can be used in the $translate function. Translate also has two keywords TOUNREAD, which makes a translate table to help make things unreadable, and TOREAD, which makes a translate table to make byte fields readable again.

>translate tounread

>ext name=$translate(name)

Total   field  [ (subscript) ] [ decplaces ]
$FILE file [ APPEND | ERASE | TEMP ]
$FILE $LIST

Compute sum total of a numeric field value in all selected records and print the result with a specified number of decplaces (default is 0 unless specified in Item command). The $file option prints the totals to a file or to the List device ($list). (Default: subscript = 1)

>total cust-balance,2

>total $file tothist,append

UPdate   [ CIUPDATE ]

Update IMAGE or Eloquence fields specified by subsequent Extract commands.

>update

>extract status-code="OLD"

Use[Q]   filename

Execute commands from a Text file or a Qedit file.

>use define6.usefile

USERpause   "string"

Print prompt string; wait for any key.

>userpause "Press Return"

Verify   [ ALL | @ | VERSION | command [ ... ]  ]

Show current specifications.

>verify input   {print name of input file}

Xeq

Perform the specified task, then wait for more commands.

More Suprtool Functions

O/S commands  

>purge abc

>run myprog

>!rm abc

>!chmod 777 myfile

Calculations  

>=312/4      {try "=?" for help}

Sort keys     

>10,2,integer

Comments      

>{this is a comment in braces}

Modify Functions In The Before Command

Printing characters replace; control keys edit. Move with the spacebar. Press the Return key after each function. ^ means Control.

^A

Append to end of line (also ^L).

^B

Insert before a column (^^ on console).

^D

Delete (default = end of line).

^G

Restart with original line.

^O

Overwrite characters (default).

^T

Stop current editing mode.

^A,^D

Delete from end of line, then add.

Calling Suprtool2

User programs can call Suprtool via the interface routine Suprtool2 (see St2usl.Pub.Robelle, St2xl.Pub.Robelle,/opt/robelle/lib). The user program calls Suprtool2 repeatedly, passing in Suprtool commands via a control record (COBOL definition in Cobol.Qlibsrc.Robelle). The last command passed to a user program must be Exit. Normally, Suprtool creates an output file which the user program then reads. To avoid typing mistakes, copy the control record from the file. Be careful to :Prep or :Link program with Cap=PH.

Suprtool Fields

Suprtool recognizes fields in databases and self-describing files. The Define command can define new fields or redefine existing fields into new sizes or data-types so that you can name and use important parts of existing fields. Refer to the Defined field in other commands (e.g., Extract, If, etc.). Select one element of a compound field by specifying a subscript in parentheses. The first element is (1).

field-name  (subscript)

The default subscript is the first sub-item for Total, Define, Sort, and If, but the entire compound item for Extract. Table does not allow subscripts; it always uses one (1). The If command's syntax uses up to 3 subscripts, so you can refer to subfields without using Define (see user manual).

>define two,address(2),20,byte

>extract two    {20 bytes of 2nd line}

>define trans,partnum[7],2

>if trans="XT"  {last 2 bytes of X8}

Data-Types

The data-types for the Define and Key commands:

Byte

ASCII characters

packed

packed-decimal

Integer

two's complement

packed*

last nibble unused

Double

two's complement

display

zoned-decimal

Real

floating-point

logical

unsigned integer

Long

floating-point

character

for NLS

Ieee

IEEE floating-point

 

 

The Suprtool definitions for the IMAGE data-types:

IMAGE Type

Bytes

COBOL Declaration

SUPRTOOL Definition

I1

2

S9(4)comp

>def a,1,2,integer

I2

4

S9(9)comp

>def a,1,4,double

I4

8

S9(18)comp

>def a,1,8,integer

J1

2

S9(4)comp

>def a,1,2,integer

J2

4

S9(9)comp

>def a,1,4,double

J4

8

S9(18)comp

>def a,1,8,integer

Un

n

A(n)

>def a,1,n,byte

Xn

n

X(n)

>def a,1,n,byte

Zn

n

9(n)

>def a,1,n,display

Pn

n/2

S9(n-1) comp-3

>def a,1,n/2,packed

K1

2

 

>def a,1,2,logical

K2

4

 

>def a,1,4,logical

R2

4

 

>def a,1,4,real

R4

8

 

>def a,1,8,real

E2

4

 

>def a,1,4,ieee

E4

8

 

>def a,1,8,ieee

The Suprtool definitions for PowerHouse data types:

IMAGE

PowerHouse

Suprtool

U or X

Character

Byte (Character for NLS)

R

Float (non-IEEE)*

Real or Long

E

Float (IEEE)*

IEEE

K

Integer Unsigned

Logical

J

Integer Signed

Integer or Double

P

Packed

Packed

Z

Zoned

Display

·       * IEEE or Non-IEEE depends on whether "float IEEE" or "float NONIEEE" was declared under "System Options" in your System Dictionary

·       Use QSHOW to view the attributes of a subfile.

The Suprtool definitions for the Allbase data types:

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

The Allbase date and time fields are returned as byte-type fields with the following lengths:

Data-Type

Length

Date

10

Time

8

Datetime

23

Interval

8

The Suprtool definitions for the Oracle data types:

Oracle Data-Type

Suprtool Data-Type

Varchar2

Byte

Number

Varies, see Precision below*

Long

Not supported

Rowid

Not supported

Date

Oracle Date

Raw

Not supported

Long raw

Not supported

Char

Byte

Mislabel

Not supported

The following table describes the translation for each case:

*Precision

Decimal Places

Suprtool Data Type

None

Any

8-byte IEEE

1-4

Zero

2-byte Integer

5-9

Zero

4-byte Integer

1-9

Non-zero

Packed-decimal

10-27

Any

Packed-decimal

28-38

Any

8-byte IEEE

You can change the Suprtool format for certain fields using the command Set Oracle Integer On, which when extracted will give the following Suprtool Data types:

Precision

Decimal Places

Suprtool Data-Type

1-4

Any

2-byte Integer

5-9

Any

4-byte Integer

10-27

Any

8-byte Integer

$Clean Function and Clean Command

Record Selection

The If command lets you select a subset of the input source based on field values in the entries, and it allows you to combine tests. Operator precedence is parentheses, NOT, AND, OR, unary minus, multiply and divide, add and subtract.

field relop value                     {relop:  =  <  >  <=  >=  <>}

field == "pattern"           {see Pattern Matching on page 16}

byte-field = NUMERIC | ALPHA | SPECIAL

field = value-list                                            {If A=1,2,3}

field = field

(expression)                                                 {parentheses}

NOT expression                                           {reverse test}

expression AND expression

expression OR expression

$INVALID(date-field)

$STDDATE(date-field)

$LOOKUP(tablename,field)

$LOOKUP(tablename,field,data)

date-field = $TODAY           {see Item command on page 6}

date-field = $DATE(yy/mm/dd) {see Item command on page 6}

word-field.(bit:count)                                    {bit extracts}

                        {bits go from 0 to 15, left to right}

byte-field = ^7                                               {Control-G}

$READ

$NULL(fieldname)

Configuration Options

Override Suprtool's default options with Set commands.

Set Option Name

Sub-Option

Value

Default

ALLBASE

ROWS

number

100

ARITHMETIC

 

CLASSIC | IEEE

see manual

AMXW

PERFWRITE

ON|OFF

OFF *

AMXW

EXTERNALSD

ON| OFF

OFF *

BASECLOSE

 

ON | OFF

OFF

BLOCKSIZE

 

size

<none>

BUFFER

 

size

14,336 or 24,576

CLEANCHAR

 

<string>

“<null>”

<space>

for no string

CURRENCYSYMBOL

 

<string>

$

DATE

CUTOFF

size

10

DATE

FORCECENTURY

ON | OFF

OFF

DATE

IFYY2000ERROR

ON | OFF

ON

DATE

MAPTOPHDATE8

ON | OFF

OFF

DECIMALSYMBOL

 

<string>

.

DEFER

 

ON | OFF

OFF

DUMPONERROR

 

ON | OFF

ON

EOFREAD

 

ON | OFF

OFF

EDITSTOPERROR

 

ON | OFF

OFF

FASTREAD

 

ON | OFF

ON (OFF)*

FILECODE

 

number

0

FILENAME

Help | Link | Edit | Hint | Export | Outcount

filename

 

FIRSTREC

 

0 | 1

see manual

HINTS

 

ON | OFF

ON

HPUXCMDERR

 

<string>

<none>

IFCHECK

 

ON | OFF

ON

IGNORE

 

ON | OFF

OFF

INITEXTENTS

 

ON | OFF

OFF

INTERACTIVE

 

ON | OFF

depends

ITEMABBREVIATEDATE

ON | OFF

ON

ITEMLOCK

<string>

<none>

LABELLEDTAPEREWIND

ON | OFF

ON

LIMITS

MPE

ON | OFF

ON

LIMITS

READONLY

ON | OFF

OFF

LIMITS

TABLE

size

1(CM)

LIMITS

TABLE

size

2,047(NM)

500(UX)

LOCK

 

0 | 1 | number

1

MAKEABSENT

 

ON|OFF

OFF

NLS

 

number

0, Nldatalang

NUMBUG

 

ON | OFF

OFF

OPENMODE

 

number

1

ORACLE

ROWS

number

100

ORACLE

INTEGER

ON | OFF

OFF

ORACLE

OPENFIX

ON | OFF

OFF

ORACLE

PASSSHIFT

ON | OFF

ON

ORACLE

SPACENULL

ON|OFF

OFF

ORACLE

ZERONULL

ON | OFF

OFF

PATTERN

 

NEW | OLD

NEW

PREFETCH

 

number

0 (MPE/iX)

PRIVMODE

 

ON | OFF

ON

PROGRESS

PERCENT

number

5

PROGRESS

MINIMUM

records

10,000 (MPE V); 50,000 (MPE/iX)

PROMPT

 

character

> 

REALMAP

 

ON | OFF

ON

RECOVER

 

ON | OFF

OFF

REDO

 

filename

<temp>

SDEXTNAME

 

ON | OFF

OFF

SQUEEZE

 

ON | OFF | <none>

<none>

SORTFAST

 

ON | OFF

ON

STATISTICS

 

ON | OFF

OFF

SUBSYSTEM

 

ON | OFF

OFF

SUSPEND

 

ON | OFF

varies

THOUSANDSYMBOL

 

<string>

,

USERLABELS

 

ON | OFF

ON

VARSUB

 

ON | OFF

OFF

VARSUBCOMPAT

 

ON | OFF

OFF

VARSUBDEBUG

 

ON | OFF

OFF

WARNINGS

 

ON | OFF

ON

XLTRIM

 

ON | OFF

OFF

LIST

TIME

format

1

LIST

DATE

0-4
0 = Sep 20, 1994
1 = 94/09/20
2 = 09/20/94
3 = 20/09/94
4 = 20 Sep 94

0

FORMFEED

 

ON | OFF

OFF

LIST

PCL

number (see below)

0

·       Suprtool for HP-UX

·       Fastread default is On for regular Suprtool for HP-UX and Off for the Dynamic Image loading version. (uses ROBELLELIB_IMAGE variable)

List PCL Number (+1,000 = ASCII, +2,000 = A4)

Letter-size (cols/lines)

A4 paper (cols/lines)

0 default font

80 x 60

 

1 Landscape-tiny

175 x 60

188 x 58

2 Landscape-Courier

100 x 45

110 x 43

3 Standard Courier

80 x 60

77 x 64

4 Portrait-tiny

132 x 80

128 x 85

5 Courier A4 "tight"

80 x 60

80 x 64

6 Landscape-legal-tiny

223 x 60

223 x 60

Ways to Run Suprtool/MPE

:Run Suprtool.Pub.Robelle [;parm=value] [;info="string"]

Parm=

Meaning

1,2

Not used

4

Execute Info = string

8

Repeat Info = string on every activate

16

Copy file input to output

32

Default Set Suspend to Off

64

Verify Exit with user

Job Control Words (JCW)

Suprtool sets the system JCW to a fatal state when Suprtool fails in a batch job. Suprtool sets a JCW named SuprtoolOutCount to the number of output records. SuprtoolOutCount has a maximum number of 65,535. On MPE/iX, Suprtool sets a variable named SuprtoolFullCount to the number of output records.

Suprmgr files

This usefile is executed every time Suprtool runs:

Suprmgr.Pub.Sys

/opt/robelle/suprmgr

Ways to Run Suprtool/UX

/opt/robelle/bin/suprtool [option]

Option

Meaning

-c

Execute specified command string.

-lw

Execute and print any loader warnings

-v

Exit with verify.

-oc

Write count to specified file.

Pattern Matching

The If command can search a byte field for a pattern of characters.

if bytefield == "pattern"  {match}

if bytefield >< "pattern"  {does not match}

@   Zero or more characters (any)               &   Escape character

~    Zero or more blanks                           !     Reserved for future use

?     One alphanumeric character                  ^    Reserved for future use

#    One numeric character

>if item-desc >< "@bolt@"

>if address=="&#101@"   {look for # sign}

Suprlink

Suprlink can access multiple files, produce a sorted disc file with a composite record of related fields from up to 7 files, and feed the merged file into your report program. Suprlink accepts only one command per line, but commands can be continued on the next line with an ampersand (&). The prompt character is "+", not ">."

:run Suprlink.Pub.Robelle

 

/opt/robelle/bin/suprlink

Before

See Suprtool section, page 2.

DO

See Suprtool section, page 3.

Exit   [ ABORT | SUSPEND | XEQ ]

Perform task specified, and return to O/S or parent process. (:Run Suprlink/MPE with Parm=64 to verify on exit.)

+exit          {default = Xeq}

+exit suspend  {stop without executing}

Form   [ filename ]

Display the fields in a self-describing file.

+form      {default = show fields in the input file}

+form customer

Help   [ command-name | keyword [ ,section ]]

Access the on-line user manual.

+help          {default is browse all}

+help input    {explain Input command}

Input   filename [ BY key-field ]

Select the primary input source. There is an option to specify the key-field by which the file is sorted.

+input invoices by cust-no

Join    filename [ BY link-keys [ FROM input-keys ] ]
[ REQUIRED | OPTIONAL ]

Join two files that have multiple keys in both the input file and the Join file.

+join custfile

Link    filename [ BY link-keys [ FROM input-keys ] ]
[ REQUIRED | OPTIONAL ]

Link the input file to another link file, maximum of seven.

+link custfile

+link sales by customer-no

LISTREDO

See Suprtool section, page 7.

Output   filename [ TEMP ] [ ERASE ]
              [ DATA ] [LINK]

Specify output file name and whether it is temporary.

+output custsale  {default file name = Output}

REDO

See Suprtool section, page 9.

Reset

Cancel the current task.

+reset         {reset everything}

Set   option-name value

Set configurable options.

+set mapped on

+set statistics on

+set varsub on    

+set warnings off  {batch only}

Use[Q]   filename

Execute commands from a Text or Qedit file.

+use cap2.infile

Verify

Show current specifications.

+verify        {show everything}

Xeq

Perform the current Suprlink task.

+xeq

Ways to Run Suprlink/UX

/opt/robelle/bin/suprlink [option]

Option

Meaning

-v

Turn on Variable Substitution

-oc

Write count to specified file.

-cv

Turn on VarsubCompat

Dbedit Subsystem

Edit records in an IMAGE, Eloquence or Oracle (with IMAX library) database. Most Dbedit commands prompt for a key value, then process matching entries. Dbedit accepts one command per line. Command abbreviations are shown in capitals. The Dbedit prompt character is "#". The generic format of a Dbedit command is

command [file] [;option [;option]]

The file parameter consists of dataset:fieldlist. See page 20 for details.

Add   [ file ]  [ ;options ]

Add new entries to a dataset manually.

#add m-customer         {add to master}

#add d-address;limit=1  {only 1 record}

Before

See Suprtool section, page 2.

Change   [ file ]  [ ;options ]

Replace a key value in a master and all related details.

#change m-customer  {must be master}

Delete  [ file ]  [ ;options ]

Remove selected entries from a dataset.

#delete m-cust    {prompts for key}

#d d-address;key=cust-no

DO

See Suprtool section, page 3.

Exit

Return control to the main part of Suprtool.

#exit       {leave Dbedit subsystem}

File  [ file ]  [ ;options ]

Establish the current file and field list as a default for other commands. (Default: none)

#file d-address       {entire entry}

#file m-cust:cur-bal  {only 1 field}

Form   [ SETS | ITEMS | PATHS |
dataset | data-item | filename ]

Show information on items and sets of database. (Default: datasets in database or fields in current dataset)

#form sets        {list datasets}

#fo d-cust        {details on d-cust set}

Help   [ command-name | keyword [ ,section ]]

Access the help file.

#help list  {explain List command}

#hq form    {quick summary of Form}

List   [ file ]  [ ;options ]

Display entries from a dataset or a group of related datasets.

#list d-address    {prompts for key}

#l m-cust;related  {linked details too}

#l d-control;all   {serial dump}

LISTREDO

See Suprtool section, page 7.

Modify   [ file ]  [ ;options ]

Change field values in a dataset.

#modify d-address

#m m-cust;updatekey   {critical fields!}

#m d-address;key=city {alternate key}

Q   [ string ]

Print a literal message on the screen. (Default: none)

#q Add a new customer to the files:

REDO

See Suprtool section, page 9

Set   option-name  value

Configure Dbedit options such as the prompt character or underlining input fields. See Dbedit Configuration Options on page 21.

#set reset off {retain Key = options}

Use[Q]   filename

Do commands from a Keep or Qedit file. (Default: none)

#use selfile

Verify   [ ALL | BASE | DSET | SET | CIUPDATE ]

Show the Dbedit specifications and options. (Default: Base, Dset, CIUpdate, changed Set values)

#verify all

Dbedit File Parameter

The file parameter consists of a dataset name with an optional list of fields. (Default: same as previous command)

dataset [ : field, ...  ]

You can use the :File command to set up the current values.

#delete m-customer

#list m-customer:name,address

Dbedit Command Options

The options come after the file parameter and can be combined with semicolons:

n

Limit prompts to n times.

ALL

Process an entire dataset.

KEY=field

Change search field prompt order.

LIMIT=count

Limit entries per key value.

RELATED

List related entries in linked datasets.

UNDER

Prompt with underlines.

UPDATEKEY

Modify search and sort fields.

Dbedit Configuration Options

Set Option-Name

Value

Default

LP

ON | OFF

OFF

PROMPT

character

#

QUIET

ON | OFF

OFF

RESET

ON | OFF

ON

UNDERLINE

ON | OFF

ON

VERIFY

ON | OFF

ON

>edit            {enter Dbedit first}

#set quiet on    {reduce screen output}

#set verify off  {don't list new records}

Dbedit Subcommands

Enter subcommands when Dbedit prompts for field values.

//          Stop the current command immediately (same as \\)

?           Describe the current field

<          Go back one field in the list ( <3 for 3 fields)

<<        Return to the first field in the list

>          Go forward one field in the list ( >3 for 3 fields)

>>        Skip the rest of the fields in the list

'           Use blanks for the field (useful in batch)

*          Use the last value for this field

[           Force what follows "[" to be a value, not a subcommand

@F       Search for field F (e.g., @bin-no)

=          Calculator

STExport

STExport reads a self-describing input file and applies formatting rules to create an output file, which is suitable for importing into other applications. STExport accepts only one command per line, but commands can be continued on the next line with an ampersand (&). The STExport prompt character is "$", not ">."

:run STExport.Pub.Robelle

 

/opt/robelle/bin/stexport

Before

See Suprtool section, page 2.

Columns   FIXED | NONE

Specify whether fields are formatted into variable- or fixed-length columns.

Clean             [ SPECIAL | <string> <range> ]

Specifies what characters to clean from byte type fields. Characters that match those defined with the Clean command, will be replaced by the value defined by Set CleanChar, which defaults to space.

  $in mysdfile

  $clean special,"^128:^190","^255","}"

  $out prnfile

Date   NONE | date-format [ "separator" ]
| INVALID [ ASTERISKS | NULL | "string" ]

Specify a date format for all dates and how to process invalid dates.

DECimal   PERIOD | COMMA

Specify which symbol will be used to indicate the decimal place in numeric fields.

DElimiter   NONE | COMMA | TAB | "string"

Specify which delimiter character, if any, is to appear between each formatted field.

EScape delimeter quote eol "string"

The Escape command will take the defined delimeter, quote and Eol and escape with a "/", if found  in any byte type field. This is for easier importing for some SQL Import programs.

  escape delimeter quote eol "/"

DO

See Suprtool section, page 3.

Exit     [ ABORT | SUSPEND | XEQ ]

Perform specified task, and return to O/S or parent process. (:Run STExport/MPE with Parm=64 to verify on exit.)

$exit          {default = Xeq}

$exit suspend  {stop without executing}

FLoating        DEFAULT | FIXED decimal-places |
SCIENTIFIC decimal-places

Specify the format and the number of decimal places for floating-point fields.

Form   [ filename ]

Display the fields in a self-describing file.

$form      {default = show fields in the input file}

$form customer

HEAding        NONE | FIELDNAMES | "string" |
ADD "string"| COLUMN "string"|

Specify which heading, if any, is to appear as the first record of the output file.

HElp   [ command-name | keyword [ ,section ]]

Access the on-line user manual.

$help          {default is browse all}

$help input    {explain Input command}

HTML NONE | PREFORMATTED | TABLE
[ TITLE "string" ]
[ HEADING "string" ]

Use HTML to format Web pages for either Internet or intranet applications.

$html table title "Product Listing"

Input   filename

Select a file to read and format.

$input invoices

LISTREDO

See Suprtool section, page 7.

Output   * | filename [ TEMP ] [ LF | NOLF ] [ ERASE ]

Specify output file name and whether it is temporary.

$output custsale  {default file name = Output}

Quote   NONE | DOUBLE | SINGLE

Specify which quote character, if any, is to be used around byte-type fields.

REDO

See Suprtool section, page 9.

Reset

Resets the Input and Output files, cancels the task in progress. Does not reset formatting commands such as Delimiter, Head, Spaces, etc.

$reset

Set      option-name  value

Set configurable options.

$set cleanchar " "

$set mapped on

$set statistics on

$set varsub on    

$set warnings off  {batch only}

$set xmltagchar "_"

$set zonedfix on

SIgn    NONE | FLOATING | LEADING | TRAILING

Specify what should be done with the sign character for numeric fields.

SPaces   NONE | TRAILING

Specify whether byte-type fields have trailing spaces.

Use[Q]   filename

Execute commands from a Text or Qedit file.

$use cap2.infile

Verify

Show current specifications.

$verify        {show everything}

Xeq

Perform the current STExport task.

$xeq

XML    VERSION "string"
DOCTYPE "string" | filename
FILE "string"
RECORD "string"

Generate output consisting of well-formed XML output.

$xml

$xml doctype myfile.xml

Zero    NONE | LEADING

Specify whether numeric fields have leading zeros.

Ways to Run STExport/UX

/opt/robelle/bin/stexport [option]

Option

Meaning

-v

Turn on Variable Substitution

-oc

Write count to specified file.

-cv

Turn on VarsubCompat

 

Speed Demon

For large datasets, replace DBGET mode-2 calls in your programs with Speed Demon calls (native or compatibility mode), which should be 2-5 times faster than DBGET. Restrictions: database cannot be open in modes-3 or -7, requires full-read access, cannot be remote, and cannot update or delete after Get. See Cobol2.Qlibsrc.Robelle for sample code.

SPDEEXPLAIN  status

Print a three-line message based on the status array.

SPDEDBINIT  base, set, mode, status, control, list

Initialize Speed Demon and specify what dataset to read. For partial field lists, specify mode-2. If your dataset may change during the read, specify mode-3 to read to end-of-file.

SPDEDBSCAN  base, status, buffer, dummy

Read the next entry in the set specified in SPDEDBINIT, returning it in the buffer.SPDEDBSHUT  base, set, mode, status, dummy

Mode-1: Close the Speed Demon environment (this must be done before reading another dataset). Mode-2: Close the environment and print statistics about the read operation.

SUPRTOOL TIPS AND TECHNIQUES

Converting Numeric Data Types

Suprtool does not change the data type in the input file, but it can change the data type as it copies numeric data to the output file.

To convert a field from Integer to Packed:

define my-packed-field,1,size,packed

extract my-packed-field = integer-field

To convert a numeric field from ASCII to binary:

define ascii-field,start,length,display

define binary-field,1,size,integer

extract binary-field = ascii-field

To convert a numeric field from ASCII to binary with sign and decimal place :

define ascii-field,start,length,display

define binary-field,1,size,integer

extract binary-field = $number(ascii-field)

 

Update Fields in A Dataset

Update a field in a dataset, using replacement values stored in a self-describing file. (Create the self-describing file using the Link option of Suprtool or Suprlink.) Load the self-describing file into a Suprtool table, then update the dataset field with the values loaded in the table. E.g., to update credit-balance with new values found in myfile:

get dataset

table t,acct-no,file,myfile,data(new-balance)

if $lookup(t,acct-no)

update

extract credit-balance = &

    $lookup(t,acct-no,new-balance)

xeq

Counting Values Found in A File

Use the Duplicate command to list all the different values in a field, and to count how many times each value occurs. E.g., to list how many shipments we have made, by state:

get ship-dataset

sort state-code

extract state-code

duplicate none keys count

list standard

xeq

Subtotaling A Field

Use the Duplicate command to total a field based on a sort key. E.g., to report the sales figures by date:

get invoices

sort invoice-date

extract invoice-date

duplicate none keys total sales-amount

list standard

xeq

Searching For Records in One File Based on Selection Criteria in Another File

E.g., to select all invoices for customers in California, when state-code is not in the invoices file:

get customer-master

if state-code = "CA"

extract acct-no

output myfile,link

xeq

get invoices

table t,acct-no,file,myfile

if $lookup(t,acct-no)

Finding the Intersection of Two Files

If a Suprlink input record does not have a matching link record, that input record does not appear in the output file. Use this to find the "intersection" of two files. E.g., list all the invoices over $1,000 for customers in California:

get invoices

item sales-total,decimal,2

if sales-total > 1000.00

sort cust-no

output inv1000,link

xeq

 

get customer-master

if state-code = "CA"

sort cust-no

output custca,link

xeq

 

link   input inv1000

link   link custca

link   output cagt1000

link   xeq

Suprlink Tips and Rules-of-Thumb

·       You can have only one Input file, but up to seven Link files.

·       All the files must be self-describing, created using the Link or Query option.

·       All the files must be sorted by a common linking field.

·       The common linking field must have the same size, data type, and content in all the files.

·       Input records without matching link records will be dropped from the output file, unless the Optional keyword is used on the Link command.

·       In general, the input file is made up of "detail-like" records, and the link file is made up of "master-like" records, even though they may not actually come from masters and details.

·       Specifically, the input file will be the file that has multiple records with the same key value (the common linking field), and the link file will be the file that has only one record with a given key value.

·       To improve speed, try to reduce the files before feeding them to Suprlink. When creating the files with Suprtool, select only the desired records using the If command, and select only the needed fields using the Extract command.

·       If both the Input file and the Link file have multiple key values then you should use the Join command instead of the Link command.

Dynamically Loading Libraries

Suprtool for HP-UX and AMXW dynamically loads the required Eloquence and Oracle routines on startup. Suprtool will no longer give a warning on startup unless you run Suprtool with the –lw option.  Suprtool requires two Eloquence libraries, namely: libimage3k.sl and libeqdb.sl. and one from Oracle, typically libclntsh.sl. The optional version that dynamically a third-party Image Replacement library needs to have the ROBELLELIB_IMAGE variable set to the fully qualified path name of the replacement variable.

Suprtool for AMXW and Suprtool for HP-UX use two methods for dynamic loading. By default, Suprtool will use shl_load to load the necessary routines which will look in the directories defined in the SHLIB_PATH. If you have the ROBELLE_DYN_LOAD variable set to Y, then Suprtool will use dlopen, which will look at LD_LIBRARY_PATH first when deciding where to look for the library files that it requires.

For example to insure that Suprtool resolves the library loads you can set the SHLIB_PATH (or LD_LIBRARY_PATH) system wide in your /etc/profile file in the following manner:

 export SHLIB_PATH=/opt/eloquence6/lib/pa11_32:/opt/oracle/lib

If you do not have the SHLIB_PATH or (LD_LIBRARY_PATH) variable set to a value where the libraries can be found, Suprtool will then try to load from their default names locations. The Oracle interface will first try to load libclntsh.so and then attempt to load libclntsh.sl from $ORACLE_HOME/lib32/libclntsh.sl.