Suprtool/Open 5.6

User Manual

Doc-To-Help Standard Manual

 

 

 

 

 

 

 

by Robelle Solutions Technology Inc.

 

 


 

Program and manual copyright 1981-2013 Robelle Solutions Technology Inc.

 

Permission is granted to reprint this document (but not for profit), provided that copyright notice is given.

 

Qedit and Suprtool are trademarks of Robelle Solutions Technology Inc. Oracle is a trademark of Oracle Corporation, Redwood City, California, USA. Other product and company names mentioned herein may be the trademarks of their respective owners.

 

 

 

 

 

 

 

I

 

Robelle Solutions Technology Inc.

Suite 372 - 7360 137 Street

Surrey, B.C. CanadaV3W 1A3

 

Phone:   604.501.2001

Fax:      604.501.2003

Support: 289.480.1060

 

E-mail:  sales@robelle.com

E-mail:  support@robelle.com

Web:     www.robelle.com


Table of Contents

Table of Contents                                                                                                       3

Welcome to Suprtool                                                                                              13

Introduction......................................................................................................... 13

Suprtool Components............................................................................................ 13

STExport - Data Export Utility................................................................... 13

Suprlink - Multidataset Access.................................................................... 13

Documentation..................................................................................................... 14

Notation.............................................................................................................. 14

Suprtool Version 5.6                                                                                               16

Highlights in Version 5.6....................................................................................... 16

Installing Suprtool                                                                                                   17

Overview............................................................................................................. 17

Who Needs To Use These Instructions..................................................................... 17

Instructions for Installing........................................................................................ 17

Quick Start Guide for Suprtool                                                                             18

How to Run Suprtool............................................................................................ 18

What is a Task?................................................................................................... 18

Copying Files...................................................................................................... 18

Copying One File..................................................................................... 18

Appending to a File................................................................................... 19

Fields in Data Files............................................................................................... 19

What is a Self-Describing File?................................................................... 19

Creating an SD File................................................................................... 19

Define Fields in a Data File......................................................................... 19

Create an SD File from a Data File............................................................... 20

Repeating Commands............................................................................................ 20

Repeating a Command............................................................................... 20

Selecting Database Records..................................................................................... 21

Select All Records..................................................................................... 21

Look at the First Few Records..................................................................... 21

Selecting by Criteria.............................................................................................. 22

Simple Criteria......................................................................................... 22

Complex Criteria...................................................................................... 22

String of Digits......................................................................................... 22

Selecting by Date.................................................................................................. 22

Select by Today's Date............................................................................... 23

Select by Particular Date............................................................................. 23

Select by Year.......................................................................................... 23

Select Prior Month.................................................................................... 23

Selecting by Lists of Values.................................................................................... 24

Finding Data Based on a List...................................................................... 24

Finding Data Based on a File...................................................................... 24

Finding Data Based on Another Tables Criteria............................................. 25

Finding Data in a Data File......................................................................... 25

Sorting Database Records....................................................................................... 25

Sort Records............................................................................................ 25

Sort Records in Descending Order................................................................ 26

Sort by Multiple Keys............................................................................... 26

Duplicate Records................................................................................................. 26

Report without Duplicate Records................................................................ 26

Report Only the Duplicate Records............................................................... 27

Report Only the Unique Records.................................................................. 27

Report Only the Duplicates and Their Originals.............................................. 28

Deleting Duplicate Data File Records............................................................ 28

Decimal Places..................................................................................................... 29

Converting Numbers.............................................................................................. 29

Counts and Subtotals............................................................................................. 30

Count and Subtotal on Sort Keys................................................................. 30

Sort by Count or Subtotal.......................................................................... 30

Total by Field.......................................................................................... 31

Running Totals........................................................................................ 31

Running SubTotals................................................................................... 31

Listing Records.................................................................................................... 32

Changing the Output Record Format........................................................................ 32

Producing a Condensed Table Listing....................................................................... 33

Simple Reports..................................................................................................... 33

Your First Report...................................................................................... 33

Column Headings..................................................................................... 34

Printing Mailing Labels............................................................................. 34

Running Suprtool                                                                                                     37

Running Suprtool................................................................................................. 37

Setting Up a PATH for Suprtool.............................................................................. 37

Bourne and Korn Shells............................................................................. 37

C Shell................................................................................................... 38

Dynamic Loading.................................................................................................. 38

Control Characters and stty..................................................................................... 38

Suprmgr Configuration Files................................................................................... 39

On-Line vs. Batch Access....................................................................................... 39

Command Line Options......................................................................................... 39

Initial Command Line:  -ccmdstring............................................................. 39

Default Outcount File Name:  -oc................................................................. 39

Exit with Verify:  -v.................................................................................. 40

Combining -c and -v.................................................................................. 40

Loader Warnings....................................................................................... 40

UNix/Linux Notes................................................................................................. 40

Shell Commands...................................................................................... 40

Hardcoded File Names and ROBELLE Variable.......................................................... 41

ROBELLE Variable................................................................................... 41

/opt/robelle/suprmgr.................................................................................. 41

Outcount File........................................................................................... 41

Differences Between MPE and Open.......................................................................... 41

Record Length.......................................................................................... 42

Line Feeds............................................................................................... 42

Duplicate Output Files............................................................................... 42

Classic Reals............................................................................................ 42

Input from Stdlist...................................................................................... 43

Missing Features....................................................................................... 43

Suprtool Issues and Solutions                                                                             45

A Suprtool Task................................................................................................... 45

Input Choices........................................................................................... 45

Processing Selections................................................................................. 45

Output Choices......................................................................................... 45

Suprtool and Oracle............................................................................................... 46

Data-Types.............................................................................................. 46

Number Data-Type.................................................................................... 46

Order By vs. Sort...................................................................................... 47

Restrictions.............................................................................................. 47

Suprtool and Self-Describing Files........................................................................... 47

Create an SD File from a Table.................................................................... 47

Create an SD File from a Data File............................................................... 47

SD Files as Input...................................................................................... 48

Listing SD Files....................................................................................... 48

Decimal Places and Date Formats................................................................. 48

Extended Field Names............................................................................... 48

Restrictions of SD Files............................................................................. 49

Suprtool and Sorting Files...................................................................................... 49

Suprtool and Personal Computers............................................................................ 49

Downloading to the PC.............................................................................. 49

Decimal Places......................................................................................... 49

Spreadsheets............................................................................................. 50

Two-Digit Year Solutions...................................................................................... 50

What If I Have Four-Digit Years?................................................................. 50

What does Set Date Cutoff do?.................................................................... 50

Stddate and Set Date Cutoff......................................................................... 51

What does Set Date ForceCentury do?.......................................................... 51

What If I Have Two-Digit Years?................................................................. 51

What Is Wrong with Two-Digit Years?........................................................ 52

How Do $Today and $Date Work?.............................................................. 52

Will Suprtool Generate an Error for Two-Digit Year Dates?.............................. 53

How Do I Use $Today and $Date with yymmdd Dates?................................... 53

aammdd Date Format................................................................................. 53

Invalid Dates............................................................................................ 54

Can Suprtool Convert Two-Digit Years to Four Digits?.................................. 54

Case 1:  Converting a J2 Date from yymmdd to ccyymmdd.............................. 54

Case 2:  X6 yymmdd Data to X8 ccyymmdd................................................. 55

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

Performance.......................................................................................................... 58

Eloquence Performance............................................................................... 58

Sort Performance....................................................................................... 58

Oracle Performance.................................................................................... 59

Analyzing Performance Data........................................................................ 59

Variable Substitution............................................................................................. 59

Suprtool Commands                                                                                               61

General Notes....................................................................................................... 61

Abbreviating............................................................................................ 61

Uppercase or Lowercase.............................................................................. 61

Multiple Commands per Line...................................................................... 61

Continuation............................................................................................ 62

Comments on Command Lines................................................................... 62

OS Commands......................................................................................... 63

Calculator................................................................................................ 63

Control-Y Interrupt.................................................................................... 63

Add Command  [Add]........................................................................................... 64

Notes...................................................................................................... 64

Examples................................................................................................. 64

Base Command  [BA]............................................................................................ 66

Before Command  [B]............................................................................................ 68

Chain Command  [C]............................................................................................ 70

Clean Command  [CL]........................................................................................... 72

Removing Bad Characters........................................................................... 72

Define Command  [D]............................................................................................ 74

Delete Command  [DEL]........................................................................................ 79

Do Command  [DO].............................................................................................. 81

Duplicate Command  [DU]..................................................................................... 82

Edit Command  [ED]............................................................................................. 87

Exit Command  [E]............................................................................................... 88

Export Command  [EXP]....................................................................................... 90

Extract Command  [EXT]....................................................................................... 91

Constants................................................................................................ 91

Dates...................................................................................................... 93

Range of Fields......................................................................................... 95

Numeric Expressions................................................................................. 96

$SubTotal Function.................................................................................. 98

$Total Function........................................................................................ 98

$Counter Function.................................................................................... 98

String Expressions.................................................................................... 99

Splitting Variable Length Strings............................................................... 100

Cleaning your Data.................................................................................. 101

Un-printables.......................................................................................... 102

Clean Command Syntax........................................................................... 102

Setting the Clean Character....................................................................... 102

Cleaning a Field...................................................................................... 102

Cleaning your data................................................................................... 103

Extract from a Table................................................................................. 103

Data Conversion...................................................................................... 104

$Number Function................................................................................... 105

Numeric to Byte Conversion..................................................................... 106

$Edit Function........................................................................................ 107

Placeholders and Format Characters............................................................ 107

Byte-Type Formatting.............................................................................. 107

Z-placeholder for byte-fields....................................................................... 108

Overflow and limits................................................................................. 108

Numeric field edit-masks........................................................................... 109

Signs.................................................................................................... 109

Decimal Places........................................................................................ 110

Data and Edit mask:................................................................................. 110

Currency and Dollar signs......................................................................... 110

Overflow and floating dollar....................................................................... 111

Set CurrencySymbol................................................................................ 111

Overflow and limits................................................................................. 111

Restrictions............................................................................................ 111

Form Command  [F]............................................................................................ 114

Get Command  [G].............................................................................................. 117

Help Command  [H]............................................................................................ 119

If Command  [IF]................................................................................................ 120

Expressions............................................................................................ 120

Constants............................................................................................... 123

Subscripts.............................................................................................. 124

Numeric Expressions................................................................................ 125

String Expressions................................................................................... 127

Date Selection......................................................................................... 130

Long Expressions.................................................................................... 135

Input Command  [I]............................................................................................. 138

Item Command  [IT]............................................................................................ 140

Date Formats.......................................................................................... 140

Decimal Places........................................................................................ 143

Notes.................................................................................................... 144

Key Command  [K]............................................................................................. 146

Link Command  [LIN]......................................................................................... 148

List Command  [L].............................................................................................. 149

Format.................................................................................................. 149

LaserJet Listings..................................................................................... 150

Headings in Listings................................................................................ 151

Simple Reports....................................................................................... 152

List Device............................................................................................. 153

List File................................................................................................ 153

Listredo Command  [LISTREDO].......................................................................... 156

Numrecs Command  [N]....................................................................................... 157

Open Command  [OP].......................................................................................... 158

Remote Databases and Oracle Issues............................................................ 158

Output Command  [O]......................................................................................... 159

Put Command  [P].............................................................................................. 165

Q Command  [Q]................................................................................................ 166

Redo Command  [REDO]..................................................................................... 167

Reset Command  [R]........................................................................................... 170

Select Command  [SEL]....................................................................................... 171

Set Command  [S]............................................................................................... 172

Allbase.................................................................................................. 174

Arithmetic.............................................................................................. 174

Baseclose............................................................................................... 174

Blocksize............................................................................................... 174

Buffer.................................................................................................... 174

CleanChar.............................................................................................. 174

CurrencySymbol..................................................................................... 175

Date Cutoff............................................................................................. 175

Date ForceCentury................................................................................... 176

Date IfYY2000Error................................................................................. 176

Date MapToPHDate8............................................................................... 177

DecimalSymbol...................................................................................... 177

Defer..................................................................................................... 177

DumpOnError......................................................................................... 178

EditStopError......................................................................................... 178

Eofread.................................................................................................. 178

FastRead................................................................................................ 178

Filecode................................................................................................. 178

Filename................................................................................................ 179

Firstrec.................................................................................................. 179

Hints..................................................................................................... 179

HPUXCmdErr <string>........................................................................ 179

Ifcheck................................................................................................... 180

Ignore.................................................................................................... 180

InitExtents............................................................................................. 180

ItemAbbreviateDate.................................................................................. 180

ItemLock............................................................................................... 180

Interactive.............................................................................................. 181

LabelledTapeRewind................................................................................ 181

Limits................................................................................................... 181

Table Size.............................................................................................. 181

Read Only.............................................................................................. 182

List....................................................................................................... 182

List Date................................................................................................ 182

List PCL............................................................................................... 182

List Time.............................................................................................. 184

List FormFeed........................................................................................ 184

Lock..................................................................................................... 184

MakeAbsent........................................................................................... 184

NLS...................................................................................................... 184

NumBug................................................................................................ 185

Openmode.............................................................................................. 185

Oracle Rows........................................................................................... 186

Oracle Integer.......................................................................................... 186

Oracle OpenFix....................................................................................... 186

Oracle PassShift...................................................................................... 187

Oracle SpaceNull..................................................................................... 187

Oracle ZeroNull....................................................................................... 187

Pattern................................................................................................... 187

Prefetch.................................................................................................. 188

Privmode............................................................................................... 188

Progress................................................................................................. 188

Prompt.................................................................................................. 189

RealMap................................................................................................ 189

Recover................................................................................................. 189

Redo..................................................................................................... 189

SDExtname............................................................................................ 190

Sortfast.................................................................................................. 191

Squeeze................................................................................................. 191

Statistics................................................................................................ 191

Subsystem............................................................................................. 191

Suspend................................................................................................. 191

ThousandSymbol.................................................................................... 191

Userlabels.............................................................................................. 191

Varsub................................................................................................... 191

VarsubCompat........................................................................................ 192

VarsubDebug.......................................................................................... 192

Warnings............................................................................................... 192

Sort Command  [SO]........................................................................................... 193

Table Command [TA].......................................................................................... 195

Adding Individual Values to a Table........................................................... 195

Adding Values from a File........................................................................ 196

Total Command [T]............................................................................................ 199

Update Command  [UP]....................................................................................... 201

Use Command  [U].............................................................................................. 202

Userpause Command  [USER]............................................................................... 204

Verify Command  [V]........................................................................................... 205

Xeq Command  [X]............................................................................................. 206

Calculator Command [=]...................................................................................... 207

Suprtool Errors and Warnings                                                                           210

Two Types Of Messages....................................................................................... 210

Errors................................................................................................................ 210

Warnings........................................................................................................... 211

Welcome to STExport                                                                                          212

Welcome to STExport......................................................................................... 212

Installing STExport............................................................................................. 212

Accessing STExport                                                                                             213

How To Run STExport........................................................................................ 213

How to Xeq an STExport Task.............................................................................. 213

Hardcoded File Names and ROBELLE Variable........................................................ 213

ROBELLE Variable................................................................................. 213

Using STExport in Batch..................................................................................... 214

Command Line Options....................................................................................... 214

Default Outcount File Name:  -oc............................................................... 214

Variable Substitution -v............................................................................ 214

Introduction to STExport                                                                                     215

Importing Data.................................................................................................... 215

Input File........................................................................................................... 215

Data-Types......................................................................................................... 215

Formatting Commands......................................................................................... 216

Commands......................................................................................................... 216

Performance Considerations................................................................................... 216

STExport Commands                                                                                           217

General Notes..................................................................................................... 217

Abbreviating........................................................................................... 217

Uppercase or Lowercase............................................................................ 217

Comments on Command Lines.................................................................. 217

OS Commands....................................................................................... 217

File Names............................................................................................. 218

Calculator.............................................................................................. 218

Control-Y.............................................................................................. 218

Before Command  [B].......................................................................................... 219

Clean Command  [CL]......................................................................................... 221

Removing Bad Characters......................................................................... 221

Columns Command  [C]...................................................................................... 222

Date Command  [DA].......................................................................................... 223

Decimal Command  [DEC]................................................................................... 225

Delimiter Command  [DE].................................................................................... 226

Do Command  [DO]............................................................................................ 227

Escape Command  [ES]........................................................................................ 228

Exit Command  [E]............................................................................................. 229

Exit Abort [EA]...................................................................................... 229

Exit Suspend [ES]................................................................................... 229

Exit Xeq [EX]......................................................................................... 229

Floating Command  [FL]...................................................................................... 231

Form Command  [F]............................................................................................ 232

Heading Command  [HEA]................................................................................... 233

Help Command  [H]............................................................................................ 235

HTML Command  [HT]....................................................................................... 236

Dynamic Web Pages................................................................................ 238

Web Server............................................................................................ 238

Shell Script............................................................................................ 239

Perl Script.............................................................................................. 241

CGI Script............................................................................................. 242

Input Command  [I]............................................................................................. 244

Listredo Command  [LISTREDO].......................................................................... 245

Output Command  [O]......................................................................................... 246

Quote Command  [Q]........................................................................................... 247

Redo Command  [REDO]..................................................................................... 248

Reset Command  [R]........................................................................................... 249

Set Command  [S]............................................................................................... 250

CleanChar.............................................................................................. 250

Mapped................................................................................................. 250

Redo..................................................................................................... 250

Statistics................................................................................................ 251

Varsub................................................................................................... 251

VarsubCompat........................................................................................ 252

VarsubDebug.......................................................................................... 252

Warnings............................................................................................... 252

Xmltagchar............................................................................................. 253

ZonedFix............................................................................................... 253

Sign Command  [SI]............................................................................................ 254

Spaces Command  [SP]........................................................................................ 255

Use Command  [U].............................................................................................. 256

Verify Command  [V]........................................................................................... 257

Xeq Command  [X]............................................................................................. 258

XML Command  [XML]...................................................................................... 259

Zero Command  [Z]............................................................................................. 262

Example of STExport Output                                                                              263

Example............................................................................................................ 263

Limits Within STExport                                                                                        267

Maximums......................................................................................................... 267

Welcome to Suprlink                                                                                            268

Welcome to Suprlink........................................................................................... 268

Terminology and HP-UX...................................................................................... 268

Notation............................................................................................................ 268

Installing Suprlink............................................................................................... 269

Hardcoded File Names and ROBELLE Variable........................................................ 269

ROBELLE Variable................................................................................. 269

Accessing Suprlink                                                                                              271

How To Run Suprlink......................................................................................... 271

How to Xeq a Suprlink Task................................................................................. 271

Suprtool Link Command...................................................................................... 271

Exit with Verify.................................................................................................. 271

Using Suprlink in Batch....................................................................................... 272

Command Line Options....................................................................................... 272

Default Outcount File Name:  -oc............................................................... 272

Variable Substitution -v............................................................................ 272

Introduction to Suprlink                                                                                       273

How Report Programs Work................................................................................. 273

Input Files......................................................................................................... 273

Link Files.......................................................................................................... 273

Output Files....................................................................................................... 274

Sort Keys........................................................................................................... 274

Selection Logic................................................................................................... 274

A Link Example.................................................................................................. 274

A Join Example.................................................................................................. 275

Performance Considerations................................................................................... 276

Another Example................................................................................................. 277

Illegal Digits...................................................................................................... 277

Selecting Non-Matches......................................................................................... 278

Suprlink with Quiz/QTP...................................................................................... 279

Suprlink Commands                                                                                             281

General Notes..................................................................................................... 281

Abbreviating........................................................................................... 281

Uppercase or Lowercase............................................................................ 281

Continuation.......................................................................................... 281

Comments on Command Lines.................................................................. 282

OS Commands....................................................................................... 282

File Names............................................................................................. 282

Calculator.............................................................................................. 282

Control-Y.............................................................................................. 282

Before Command  [B].......................................................................................... 284

Do Command  [DO]............................................................................................ 286

Exit Command  [E]............................................................................................. 287

Exit Abort [EA]...................................................................................... 287

Exit Suspend [ES]................................................................................... 287

Exit Xeq [EX]......................................................................................... 287

Form Command  [F]............................................................................................ 288

Help Command  [H]............................................................................................ 290

Input Command  [I]............................................................................................. 291

Join Command  [J].............................................................................................. 292

Link Command  [L]............................................................................................. 294

Listredo Command  [LISTREDO].......................................................................... 296

Output Command  [O]......................................................................................... 297

Redo Command  [REDO]..................................................................................... 298

Reset Command  [R]........................................................................................... 299

Set Command  [S]............................................................................................... 300

Mapped................................................................................................. 300

Redo..................................................................................................... 300

Statistics................................................................................................ 301

Varsub................................................................................................... 301

VarsubCompat........................................................................................ 301

VarsubDebug.......................................................................................... 302

Use Command  [U].............................................................................................. 303

Verify Command  [V]........................................................................................... 304

Xeq Command  [X]............................................................................................. 305

Example Suprlink Output                                                                                    307

Example............................................................................................................ 307

Limits Within Suprlink                                                                                          309

Maximums......................................................................................................... 309

Welcome to Calling Suprtool                                                                              311

Calling Suprtool................................................................................................. 311

Suprtool2 Routine............................................................................................... 311

Importance of the Exit Command........................................................................... 311

Environment Variables......................................................................................... 312

Control Record................................................................................................... 312

Examples of Calling Suprtool                                                                             315

Copying the Examples......................................................................................... 315

COBOL Example.................................................................................... 315

C Sample............................................................................................... 318

Installing the Suprtool2 Interface                                                                      320

Installing........................................................................................................... 320

Suprtool2 Error Messages                                                                                 321

Error Numbers.................................................................................................... 321

Glossary of Terms                                                                                                322

Commonly-used Terms........................................................................................ 322

Special Characters................................................................................................ 325

Index                                                                                                                         330


Welcome to Suprtool

Introduction

Welcome to version 5.6 of Suprtool Open-- the data manipulation handyman for Eloquence, Oracle databases and fixed-length data files. Use Suprtool to quickly select and sort data records. Combine multiple data files using Suprlink. Use STExport to convert fields in a self-describing input file into an output file that can be imported into other applications.

The Suprtool commands are:

Add

EDit

ITem

Q

Use

BAse

Exit

Key

REDO

USERpause

Before

EXPort

LINk

Reset

Verify

Chain

EXTract

List

SELect

Xeq

Clean

Form

LISTREDO

Set

:OS command

Define

Get

Numrecs

SOrt

=expression

DELete

Help

OPen

TAble

 

DO

IF

Output

Total

 

Duplicate

Input

Put

UPdate

 

The minimum abbreviation of each command is shown in capital letters.

Suprtool Components

The Suprtool package consists not only of Suprtool, but also of other programs that perform useful database functions. These other programs are STExport and Suprlink.

STExport - Data Export Utility

STExport converts fields in a self-describing input file into an output file that can be imported into different applications.

Use STExport to produce a formatted output file that can be used to import data into databases and applications.

Other databases have different requirements for the format of input data. You will have to experiment with the various STExport formatting options to find a format that your particular database tool accepts.

Suprlink - Multidataset Access

Suprlink is a program that works with Suprtool to add "multidataset" capability to Suprtool. Suprlink is not a set of callable routines. To run Suprlink you can run as a separate program as /opt/robelle/bin/suprlink or suprlink, provided you have the correct PATH settings.

Rather than take the regular path to multiple datasets -- random retrieval via Eloquence keys -- we have chosen to follow a different path: fast serial extracts plus a very efficient merge.

To understand what Suprlink does, think of the process of writing a report. Your report program (written in COBOL, RPG, PowerHouse, or some other language) hunts all over the database with DBFIND and DBGET to collect your data.

It would be faster if the report program could just read a sorted disc file with a big record containing all the data necessary for the report, and this is Suprlink's function. Suprtool can extract the desired fields from the desired records of the sales detail dataset and put them in a disc file. Then Suprtool can extract the desired fields from the customer master dataset and write them to a second disc file. If Suprtool sorts both files by customer, Suprlink can "link" them together, producing a third file whose composite record consists of the related fields from both files. This file is just what we need to feed into the report program. For example, a sales report program might read a disc file whose records consist of sales transactions plus customer information. This file has been sorted by customer number and date. If there are several sales for the same customer, the customer information is just repeated in each record. The report program reads the records, checks for level breaks, and formats and prints the records.

Documentation

The user manual contains the full description of all the Suprtool suite of products including Suprlink, STExport, and Suprtool2, as well as usage tips and commands for each. The manuals are up-to-date with all the latest changes. To see only the changes in the latest version, see the "What's New" section of the manual, or see the change notice.

You can download our manuals and Change Notices in various formats and even order printed (hardcopy) manuals from our web site at:

 http://www.robelle.com/library/manuals/.

 

Notation

The Suprtool documentation uses a common notation in describing all commands. Here is a sample command definition:

            EXTRACT field (subscript)] [=value] [,...]

UPPERcase letters - literal symbols to be used in the command as they appear (e.g., EXTRACT).

Lowercase, underlined or italic - "variables" to be filled in by the user (example: field). Each such "variable" is defined elsewhere in terms of literal symbols (consult the index). In the help file, underlining and italics are not available and variables appear simply in lowercase.

Brackets - enclose optional fields (example: [(subscript)]).

Braces - enclose comments in examples. For example, >INPUT ACTREC {input from a data file}. Braces can be used for comments in actual Suprtool commands.

Up lines - separate alternatives from which you select (example: Set Ignore [On|Off]); sometimes, the alternatives are shown listed on several lines.

Dot-dot-dot (...) - indicates that the variable may be repeated many times in the command.

Other special characters - literal symbols that must appear in the command as they are shown in the format (example: the comma above). Some commas in Suprtool are optional.

In examples, there is an implied carriage return at the end of each line.


 

Suprtool Version 5.6

Highlights in Version 5.6

       First version of Suprtool/Open has been released on x86, Linux.


Installing Suprtool

Overview

Suprtool and associated tools can be downloaded from our web site using simple instructions found underneath the download.

Who Needs To Use These Instructions

Since the install requires root access the system manager should use the following installation instructions to install Suprtool/Open. No one can be using Suprtool/Open during the installation. The installation should only take a few minutes.

Instructions for Installing

If you received a production release tape from Robelle, then you can install using the instructions on this web page: 

 http://www.robelle.com/downloads/install-soprod.html

 


Quick Start Guide for Suprtool

How to Run Suprtool

Use the following command to access Suprtool:

/opt/robelle/bin/suprtool

 

SUPRTOOL/Open Copyright Robelle Solutions Technology Inc. 1981-2013.

(Version 5.6) 

> 

Suprtool prints its version number and the current date and time right after a banner.  Suprtool then prompts with ">". Press Return after typing each command.

To exit Suprtool, type "Exit" at the Suprtool prompt.

>exit

What is a Task?

Tasks are the building blocks with which Suprtool helps you to solve data processing problems. In a task, Suprtool reads information from a file or database, selects and processes some information, and writes out the result. You can visualize a Suprtool task like this:

The examples that follow all consist of Suprtool tasks. Simple solutions require only one task. Complex solutions consist of several tasks, often with the output of one task becoming the input for the next task.

Copying Files

Copying One File

Use the Input command to specify a data file.

>input  file1,reclen 80, nolf

>output result

>xeq

The Output command creates the file called "result", which is a copy of the input file.

Appending to a File

To append to an existing file, use the Append option in the Output command.

>input  file2, reclen 80, nolf

>output result,append

>xeq

Fields in Data Files

What is a Self-Describing File?

A self-describing (SD) file is actually a pair of files, one with data and the other with field information. These files have the advantage of behaving like data files, which can provide field information to Suprtool without you having to define all the fields. The Input command is also simpler because there is no need for either the Reclen or the LF parameters.

Creating an SD File

To create an SD file, use the Link option in the Output command.

>select * from sales

>output result,link

>xeq

Now the data file "result" has the same field names as the Sales table. Suprtool can read this data file and know about the fields automatically.

>input  result

>if     sales_total>20000 and product_price<5000

>output custlist

>xeq

Define Fields in a Data File

A regular data file does not have any field information associated with it. If you need to work with the fields in a data file, you need to tell Suprtool about the fields using the Define command. For example, say you have a data file with lines that look like this:

12345678John        Rutherford     <32>

98765432Catherine   Smith          <29>

|       |           |               |

Account First name  Last name       Age

8-byte  12-byte     16-byte         2-byte integer

Use these Define commands to tell Suprtool about the fields:

>input  datafile, reclen 38, lf

>define account,    1,  8, byte

>define first_name, 9, 12, byte

>define last_name, 21, 16, byte

>define age,       37,  2, int

         |         |    |    |

     field name    |  Length |

                 Start       Data-type

                 position

Now you can use the field names "account", "first_name", "last_name", and "age" to refer to the corresponding parts of the line, just as if this were a database record.

>input  datafile

>define ...

>if     age>65

>ext    account, last_name

>output result

>xeq

Create an SD File from a Data File

To create an SD file from a data file, follow these steps:

1.     Define the fields that you want to include in the SD file.

2.     Extract the fields in the order you want.

3.     Use the Link option in the Output command to create the SD file.

>input   datafile, reclen 38, lf

>define  account,    1,  1, byte

>define  first_name, 9, 12, byte

>define  last_name, 21, 16, byte

>define  age,       37,  2, int

 

>extract account, age, first_name, last_name

 

>output  result,link

>xeq

Repeating Commands

Repeating a Command

Use the Listredo command to see a list of your most recent commands. Use the Do command to repeat a command, or use the Redo command to modify a command before repeating it.

>listredo                              {20 most recent commands}

>listredo input                        {most recent Input commands}

 

>input result

>...

>xeq

>do  input                             {repeat previous Input command}

 

>input result

>if quantity > 10000

>...

>redo if                               {modify previous If command, then repeat}

If you have used two commands that begin with the same letter, you have to be careful when repeating those commands. Make sure you use enough letters to identify each command distinctly from the other. In the following example, if you wish to repeat the Input command instead of the If command, you need to use "do i s" instead of just "do i".

>i somefile

>if <expression>

>...

>xeq

>do i s                                {repeats previous Input command}

Selecting Database Records

These examples show you how to get records from an Oracle table. It assumes you have opened the database with the Open command. The results are written to a data file called "result", which can be read either by a program or by a report writer.

/opt/robelle/bin/suprtool

>open oracle demo reader

Select All Records

This example extracts all the records from the table. Note that we didn't specify any selection criteria, so Suprtool selects all the records.

>select * from sales                   {input table}

>output result                         {output file}

>xeq                                   {Xeq command performs the task}

Look at the First Few Records

If you want to look at the first few records of a dataset, use the Numrecs command. This command tells Suprtool to extract at most the number of records specified. Then, instead of sending the result to a file, send it to the screen with "output *,ascii". The example shows you how to look at the first 10 records in your dataset.

>select * from sales

>numrecs 10                            {first 10 records}

>output  *,ascii                       {output to screen, format numbers}

>xeq

Selecting by Criteria

Simple Criteria

To tell Suprtool to choose records based on certain criteria, you can either use any valid SQL command (e.g., select, where), or you can select all the records and use an If command. In the following example Suprtool extracts all records with a sales_total value greater than 20000 from the Sales table. Both tasks produce identical results, but one way may be faster than the other.

>select * from sales

>if     sales_total > 20000

>output result

>xeq

Complex Criteria

To choose records using more complex criteria, combine several simple criteria using AND, OR, NOT, and parentheses. In this example Suprtool extracts all records that have a sales_total value greater than 20000 and a product_price value less than 5000 from the sales table.

>select * from sales

>if     sales_total>20000 and product_price<5000

>output result

>xeq

String of Digits

If you have a byte-type field that consists entirely of digits, and you want to use this field as a numeric field, you need to define a new display field on top of the existing field. For example, suppose your data looks like the following, where the customer account number is stored in the 8-digit byte-type field at the start of the record:

20476789...rest of customer record...

To find all customers with an account greater than 20470000, you would do the following:

>select * from table

>define accountnum, account, 8, display

>if     accountnum > 20470000

>output result

>xeq

Selecting by Date

The following section on dates does not apply to SQL columns, only to defined fields and SD fields. Disc files usually store dates as numeric or character fields; you can use the Define command to isolate the field.

Before Suprtool can use a date field, it has to know the format of a particular date field. Use the Item command to specify the format. For example, to tell Suprtool that the item purch_date is a date field with a format of yyyymmdd (e.g., 20040319), you would use:

>define purch_date, 11,8               {8 bytes, starts in byte 11}

>item   purch_date, date, yyyymmdd     {date format}

In the following date examples, we show the Define and Item commands in each example. In practice, however, you only need to use these commands once per date field, not once per task.

Select by Today's Date

For this example, select all the sales records whose purchase date is today. Note the use of $today in the If command to indicate today's date.

>input  saledata,reclen 70,nolf

>define purch_date, 11,8

>item   purch_date,date,yyyymmdd

>if     purch_date = $today            {select today's date}

>output result

>xeq

Other tricks with $today

>if     purch_date = $today(-1)        {yesterday}

>if     purch_date = $today(+1)        {tomorrow}

Select by Particular Date

To specify a particular date, use the $date function in the If command. The $date function has the form $date(year /month /day). This example selects all the sales transactions for August 12, 2000.

>input  saledata,reclen 70,nolf

>define purch_date, 11,8

>item   purch_date,date,yyyymmdd

>if     purch_date = $date(2000/08/12)

>output result

>xeq

Select by Year

Suppose we want to select all the sales transactions for 2000. Suprtool does not have a shorthand for specifying "everything in that year". To specify an entire year, use a date range from January 1st to December 31st.

>input  saledata,reclen 70,nolf

>define purch_date, 11,8

>item   purch_date,date,yyyymmdd

>if     purch_date >= $date(2000/01/01) and &

        purch_date <= $date(2000/12/31)

>output result

>xeq

Select Prior Month

In the $date function, use a * to indicate the current year, month, or day. Similarly, a *-1 means the previous year, month, or day. For this example, select all the sales transactions for the prior month. Note the use of the special keywords "first" and "last" to indicate the first and last day of the month.

>input  saledata,reclen 70,nolf

>define purch_date, 11,8

>select * from sales

>item   purch_date,date,yyyymmdd

>if     purch_date >= $date(*/*-1/first) and &

        purch_date <= $date(*/*-1/last)

>output result

>xeq

Selecting by Lists of Values

Sometimes you want to find records based on criteria contained in another file or table.

Finding Data Based on a List

Suppose we want to find all orders for the customers "1234", "9876", and "5555." We simply use a list of values after the equal sign in the If command. A match is made if a customer matches any one of the values in the list.

>select * from order_details

>if     cust_no = "1234", "9876", "5555"

>output orders

>xeq

If we wanted to find orders for all customers except "1234", "9876", and "5555", we would simply change the equal sign in the If command to a not-equal sign. A match is made if a customer does not match any values in the list.

>select * from order_details

>if     cust_no <> "1234", "9876", "5555"

>output orders

>xeq

Finding Data Based on a File

If you have a large list of values in a file, you can load them into Suprtool and select data based on this list. First use the Table command to load values from an external file into a table, then use the $lookup function of the If command to match data to the table.

Suppose our list is in a self-describing file called Custlist. We create a program-temporary table called cust_table. Note that this is not the same as an Oracle table.

>select * from order_details

>table  cust_table, cust_no, file, custlist

>if     $lookup(cust_table, cust_no)

>output orders

>xeq

If you want to find all customers not on the list, just negate the If condition by simply putting "not" in front of the $lookup.

>if     not $lookup(cust_table, cust_no)

Finding Data Based on Another Tables Criteria

Sometimes you need to find data from one table based on conditions from another table. This is a typical example: you want to find all of the pending orders for those customers whose accounts receivable balance is 0.

First we find the customers with an AR balance of 0, and extract their customer numbers to a file.

>select * from receivables

>if     ar_balance = 0

>ext    cust_no

>output custlist,link

>xeq

Now we can find information by loading a file of customer numbers into a table and then applying the $lookup feature.

>select * from order_details

>table  cust_table, cust_no, file, custlist

>if     status="PENDING" and $lookup(cust_table,cust_no)

>output orders

>xeq

Finding Data in a Data File

So far, the examples have looked up data from a table. If you want to look up information in a data file, you need to tell Suprtool about the fields. Use the Define command to do this.

The following example gives you some idea of the byte-size of one kind of record in a data file.

John        Smith           12345678

Anna-May    Richardson      98765432

12-bytes    16-bytes        8-bytes

If you want to look up customers based on a list of customer numbers in the self-describing file Custlist, use the following task. Notice how the start position and number of bytes is entered into the Define command. This defines the position within the input file, not the table file.

>input  flatfile, reclen 36, nolf

>define cust_no, 29, 8, byte

>table  cust_table, cust_no, file, custlist

>if     $lookup(cust_table, cust_no)

>output result

>xeq

Sorting Database Records

Sort Records

To tell Suprtool to sort table records, you can either use any valid SQL command (e.g., select, order by), or you can select the records and use a Sort command. Here are two examples where Suprtool extracts all records from the Sales table into a data file called "result". The records are sorted by the field cust_account. Both tasks produce identical results, but one way may be faster than the other.

Sorting in the Select command:

>select * from sales  order by cust_account

>output result

>xeq

Sorting in the Sort command:

>select * from sales

>sort   cust_account

>output result

>xeq

Sort Records in Descending Order

This example extracts all records from the Sales table into a data file called "result".  The records are sorted by the field sales_total in descending order (i.e., show highest totals first). Use the Desc option in the Sort command to do this.

>select * from sales

>sort   sales_total desc               {descending order}

>output result

>xeq

Sort by Multiple Keys

This example extracts all records from the Sales table into a data file called "result". The records are sorted by the field cust_account, then by sales_total in descending order. Use two Sort commands to do this because the Sort command only accepts one field at a time.

>select * from sales                   {input}

>sort   cust_account                   {first sort key}

>sort   sales_total desc               {second sort key}

>output result                         {output}

>xeq

Duplicate Records

In the following examples, the key field is in the first four bytes of the record. "Duplicate-ness" is based on records having the same key value. In any group of records with the same key value, the first record is considered to be the "original", and the rest are considered to be the "duplicates".

Report without Duplicate Records

This is an example of filtering out duplicated records (the original remains). This is done by using the None option of the Duplicate command.

Input      Result

1111 a     1111 a

2222 b     2222 b

2222 c     3333 e

2222 d

3333 e

>select * from table

>sort   keyfield

>dup    none keys

>output result

>xeq

Report Only the Duplicate Records

This is an example of keeping only the duplicated records (the original is not kept). This example is the opposite of the previous example. Use the Only option of the Duplicate command to do this.

Input      Result

1111 a     2222 c

2222 b     2222 d

2222 c

2222 d

3333 e

>select * from table

>sort   keyfield

>dup    only keys

>output result

>xeq

Report Only the Unique Records

This example shows how to report only those records without duplicates. That is, if the records have duplicates, both the originals and their duplicates are omitted from the report.

Input       Result

1111  a     1111  a

2222  b     3333  e

2222  c

2222  d

3333  e

You have to use two Suprtool tasks to accomplish this. The first task creates an intermediate file Dupfile that contains the keys of the duplicate records. The second task creates the desired output file Result that contains only the unique records.

>select  * from table

>sort    keyfield

>extract keyfield

>dup     only keys

>output  dupfile

>xeq

 

>get     dataset

>table   dup-table, keyfield, sorted, dupfile

>if      not $lookup(dup-table, keyfield)

>output  result

>xeq

Report Only the Duplicates and Their Originals

This performs the opposite function to the one outlined above. It keeps only the duplicates and their originals.

Input       Result

1111  a     2222  b

2222  b     2222  c

2222  c     2222  d

2222  d

3333  e

Once again, you have to use two Suprtool tasks to accomplish this. The first task creates an intermediate file Dupfile that contains the keys of the duplicate records. The second task creates the output file Result that contains only duplicate files and their originals.

>select  * from table

>sort    keyfield

>extract keyfield

>dup     only keys

>output  dupfile, link

>xeq

 

>select  * from table

>table   dup_table, keyfield, sorted, dupfile

>if      $lookup(dup_table, keyfield)

>output  result

>xeq

Deleting Duplicate Data File Records

The following tasks read the file datafile and create two new files. The file named "result" does not have duplicate records. The other file named "archive" has only the duplicate records.

Task 1: Identify which records to delete.

>input  datafile,reclen 38, nolf

>define key1,1,8

>define key2,13,12

 

>define rec,1,38                       {length of the record}

>ext    rec

>sort   key1

>sort   key2

>dup    only keys

>out    dupfile, link

>xeq

Task 2: Write records to archive.

>input  datafile, reclen 38, nolf

>table  duptab, rec, sorted, dupfile, hold

>if     $lookup(duptab, rec)

>output archive

>xeq

Task 3: Delete the records.

>input  datafile

>if     not $lookup(duptab, rec)

>output result

>xeq

Decimal Places

Data in disc files often has an implied number of decimal places. For example, dollar amounts usually have two implied decimal places for the cents. In this case, the number stored is scaled by a factor of one hundred (e.g., you would enter 10000 to represent $100.00).

>input  saledata, reclen 70, nolf

>def  total_sales, 40,4, integer

>if   total_sales > 99900              {find sales > $999}

>out  result

>xeq

You can use Suprtool's Item command to identify defined fields that have an implied number of decimal places. Once you do this, you can then enter regular, unscaled numbers. For example, to enter five cents, use 0.05; to enter $100.00, use 100. If a field is a dollar and cents amount scaled by 100, use the following to tell Suprtool about the decimal place:

>item total_sales, DECIMAL, 2

With the Item command, the previous example becomes more understandable:

>input  saledata, reclen 70, nolf

>def    total_sales, 40, integer

>item   total_sales, decimal, 2

>if     total_sales > 999              {find sales > $999}

>out    result

>xeq

Converting Numbers

There are several ways to convert binary numbers (e.g. I2, P8) into human-readable ASCII form. You can use STExport's Output,ASCII or Output,DISPLAY if you want to convert all numbers.

If you want to convert only some of your numeric fields, you can use Suprtool's numeric conversion feature to convert the binary fields to display fields.

define mynumber,1,6,display

get dataset

ext some-fields...

ext mynumber = binary-number

output filename

xeq

Note that this technique also works for converting a number from one numeric type to another numeric type.

You can also convert from binary numbers to a formatted byte field using the $edit function:

  >in mysdfile

  >def a,1,10,byte

  >def b,1,10,byte

  >ext a=$edit(int-field,"$$,$$$.99-")

  >ext b=$edit(int-field,"999999999-")

  >list

  >xeq

  >IN FILE1SD.NEIL.GREEN (0) >OUT $NULL (0)

  A      =      $11.11-     B   = 000001111-

Counts and Subtotals

Count and Subtotal on Sort Keys

This example counts the number of sales transactions for each customer and produces the total sales for each customer. We use the Count and Total options of the Duplicate command. Note that we made the output file self-describing so we can easily work with it later.

>input transactions                    {self-describing file}

>ext   cust_account

>sort  cust_account                    {need to sort by key}

>dup   none keys count total sales_total

>list  standard

>out   result, link

>xeq

 

The output file contains three fields. The first field is the cust_account that we extracted. Suprtool created two new fields at the end of each output record: st-count and st-total-1. St-count contains the number of times each cust_account occurred, while st-total-1 contains the total sales for each cust_account.

Sort by Count or Subtotal

When Suprtool counts or subtotals, the output is sorted according to the key fields. If you want your output file to be sorted by the count or by a total, you must process the output file with a second task. The following example sorts the previous file of totals by ST-COUNT. We choose a descending sort sequence, so that we can see first the customers with the largest number of orders.

>input result                          {input from previous task}

>sort  st-count, desc                  {highest counts appear first}

>list  standard                        {produce a simple report}

>xeq

Total by Field

If you want to get a single total for a field, without caring about subtotals on sort breaks, you can use the Total command. Total prints out the result on $stdlist. For example, to compute the total sales value for 2000 transactions, use these commands:

>select * from sales

>if     purch_date>=000101 and purch_date<=001231

>total  sales_total

>output $null

>xeq

Running Totals

You can get a running total on a field using the $total function.. The target data must be a packed field with 28 digits, in order to help avoid overflow issues. A sample use of the total function could be:

  >def mytotal,1,14,packed

  >get orders

  >ext mytotal = $total(sales-amount)

  >xeq

Running SubTotals

Suprtool has the ability to keep a running subtotal for any numeric field based on a given sort key. The target data must be a packed field with 28 digits, in order to help avoid overflow issues.

A sample use of the $subtotal function could be:

  >def mytotal,1,14,packed

  >get orders

  >sort order-number

  >ext order-number

  >ext part-number

  >ext description

  >ext sales-amount

  >ext mytotal = $subtotal(sales-amount,order-number)

  >out sales,link

  >xeq

This would result in a file containing a running subtotal in the field mytotal for a given order-number. You could then generate a simple report with the simple Suprtool commands:

  >in sales

  >list standard

  >xeq

The basic syntax for the $subtotal function in the extract command is:

  extract targetfield = $subtotal(field,sort-field)

You must specify the sort command before referencing the sort-field in the $subtotal function. You can subtotal up to ten fields per task.

Listing Records

You can print selected input records either formatted or with the Octal, Hex, Decimal, or Character representations. To dump all sales records with a negative amount, use these commands:

>select * from sales

>if sales_total < 0

>list lp

>xeq

This finds the entries that meet the selection criteria and prints them to the default line printer, showing column names and column values converted to ASCII. If you suspect that your data is bad, you can dump the records in Octal/Char format instead:

>select * from sales

>if sales_total < 0

>list octal,char

>xeq

If you want the listing in column format, use List Standard:

>select * from sales

>if sales_total < 0

>list standard lp

>xeq

You can send the list output go to a file and to also append to an existing file using the file option in the list command. The File option takes the next parameter as being the filename:

  >in test/file1sd

  >list stan file myslist

  >xeq

If the file myslist exists it will be over-written, unless you specify the Append option. If you specify the append option the new report will be added to the file.

So if you want to incorporate multiple reports you just need to do the following:

  >in test/file1sd

  >list stan file myslist

  >xeq

  >in test/file2sd

  >list stan file myslist append

  >xeq

Changing the Output Record Format

You can change the output file record format by using the Extract command. The Extract command causes Suprtool to assemble Output records by stringing together fields extracted from Input records. You would use the following to extract two of the nine fields from the customer records:

>select * from customer                {input from a table}

>extract cust_account                  {extract the key value and}

>extract credit_rating                 {   one other field}

>output out1                           {output file will have two fields}

>xeq

You can easily insert data into the middle of a record, again using the Extract command. Define the first and second halves of the record as two big chunks. Now Extract the first part, note the constant you wish to insert, then Extract the second part.

>input myfile, reclen 95, nolf         {95 bytes wide}

>define part1,1,60,byte                {first 60 bytes}

>define part2,61,35,byte               {remaining 35}

>extract part1, "constant", part2      {extract an 8-byte constant}

>output newfile                        {103 bytes now}

>xeq

Producing a Condensed Table Listing

When debugging test databases, it is often desirable to produce a condensed listing of a table on $stdlist. The following example combines the Extract command with the ASCII output option (i.e., all binary and packed-decimal data is converted into readable ASCII characters). For readability, each data value is prefixed with an abbreviated column name. This listing is more compact than the one produced by the List command.

>select * from customer

>extract "Account=",cust_account," "

>extract "C/R=",credit_rating

>output *,ascii                        {* implies $stdlist}

>xeq

The output would look like:

Account=04598921 C/R=    500000

Account=44657844 C/R=   2000000

Account=98753198 C/R=    300000

Simple Reports

You can produce simple reports with Suprtool's List command. You select the records for the report with the If command and the fields for the report with the Extract command. Reports can include running headings with the date, title, and page number and an optional line of column headings. Suprtool can produce default titles and headings.

>select  * from customer

>extract cust-account

>extract credit-rating

>list    standard

>xeq

The output would look like this:

Jan 17, 2000 11:59                                    Page 1

 

CUST_ACCO CREDIT_RATING

 

 4598921       5000.00

44657844      20000.00

98753198       3000.00

Your First Report

Our report selects all customers in California, sorts the records by city, and reports on the city, account number, and name of each California customer:

>select  * from customer               {input table}

>if      state = "CA"                  {California customers}

>sort    city                          {sort by city name}

>extract city                          {city first on each line}

>extract cust_account                  {followed by account#}

>extract name_first                    {and first name}

>extract name_last                     {and finally last name}

>list    standard                      {produce a quick report}

>xeq

These commands produce a report with four columns. The title consists of the date and page number. The column headings are the name of each column that we extracted.

Column Headings

Column headings default to uppercase field names. The names are truncated if they are longer than the field itself. One space is inserted between fields.

Suprtool does not automatically align user-specified headings with the data columns. We suggest specifying heading strings with the same length as the fields they represent, while taking into account the space between the data columns.

In our example, we enter one column heading per line (using Suprtool's continuation character "&"):

>list standard,heading  &

      {----+----1----+----2}

      "City         " &                {field is X12}

      "Account   "    &                {field is Z8}

      "First Name "   &                {field is X10}

      "Last Name"                      {field is X16}

We included one space between fields. Note that an extra space was needed for the

Account

heading (it is an 8-digit field, but we used 10 characters). Because cust-account is a zoned-decimal field, an extra space is required for the sign.

Printing Mailing Labels

You can print mailing labels by combining the Extract command with the List Oneperline command. We assume that each mailing label starts with two blank lines, followed by the customer name and address, followed by another blank line. The Suprtool commands to produce the labels are as follows:

>select  * from customer               {input customers}

>extract " "                           {first field}

>extract " "                           {second field}

>extract customer_name                 {name first}

>extract street_address(1)             {three lines of address}

>extract street_address(2)

>extract street_address(3)

>extract " "                           {last blank field}

>list    oneperline, noname, noskip, norec

>xeq

The line

extract " "

creates a single field that consists of a blank space. Each of these blank fields results in a blank line on our mailing labels, since the List command puts one field on each output line.

If you want to combine two fields on one line, you would first have to create an output file with the combined fields and use this file as input to List Oneperline.


Running Suprtool

Running Suprtool

To run Suprtool, type this command:

/opt/robelle/bin/suprtool

 

Suprtool. Copyright Robelle Solutions Technology Inc. 1981-2013.

(Version 5.6) Type ? for help.

> 

Suprtool prints its version number and prompts with ">". Press Return after each command you type. For example, if you type

>define a,1,4,double

Suprtool add the defined field a to it's internal table and makes it a double integer.

Setting Up a PATH for Suprtool

You can invoke Suprtool with the command:

/opt/robelle/bin/suprtool

If you wish to just type

suprtool

in order to invoke Suprtool/Open, you must either add /opt/robelle/bin to your PATH or copy /opt/robelle/bin/suprtool to a directory that is currently on your PATH. Similarly, the man pages for Suprtool are in /opt/robelle/man/man1/suprtool.1. To make the man pages available to everyone, you can either add /opt/robelle/man to your MANPATH or you can copy the man pages to a directory that is currently on your MANPATH.

Bourne and Korn Shells

See Configuring Different Shells (above) for a discussion on the files that are automatically executed by the Bourne and Korn shells. The easiest way to change the two PATHs for all the users on your machine is to logon as root, and add these two lines to the file /etc/profile after any existing PATH or MANPATH statements:

PATH=$PATH:/opt/robelle/bin

MANPATH=$MANPATH:/opt/robelle/man

Remember to delete any PATH or MANPATH settings in /etc/d.profile so that new users do not override your changes. You also have to warn existing Bourne and Korn shell users to change the .profile file in their home directories.

C Shell

See Configuring Different Shells (above) for a discussion on the files that are automatically executed by the C shell. The easiest way to change the two PATHs for all the users on your machine is to logon as root, and add these two lines to the file /etc/csh.login after any existing PATH or MANPATH statements:

set path ($path /opt/robelle/bin)

setenv MANPATH "$MANPATH":/opt/robelle/man

Remember to delete any PATH or MANPATH settings in both /etc/d.login and /etc/d.cshrc so that new users do not override your changes. You also have to warn existing C shell users to change their .login and .cshrc files in their home directories.

 

Dynamic Loading

Suprtool dynamically loads the required Eloquence and Oracle routines on startup.  Suprtool requires two Eloquence libraries, namely: libimage3k.sl and libeqdb.sl. and one from Oracle, typically libclntsh.sl. This library will have other dependencies, and this varies by version of Oracle. The Oracle library is only loaded if you have the Oracle option enabled. In this version if any of the dynamic loading of Eloquence or Oracle fails, Suprtool will continue.

Printing Loader Warnings

When loading Oracle libraries Suprtool would report warnings on startup if it failed to load the Oracle libraries. Suprtool by default no longer prints these warnings. To check if your libraries were loaded you can run Suprtool with the –lw option:

 suprtool -lw

Control Characters and stty

Most Linux/Unix users have Control-D configured as the end-of-file character, and Control-C as the interrupt character. If you use Robelle-style modify, you must reassign Control-D to a different control character. If you are familiar with MPE, you may want to assign Control-Y as your interrupt character. A standard shell configuration file (.profile for Bourne and Korn shells, and .login for the C shell) usually contains a line like:

stty erase "^H" kill "^U" intr "^C" eof "^D" swtch "^Z"

To change both the end-of-file and interrupt characters, you should change the "intr" and "eof" control keys as follows:

stty erase "^H" kill "^U" intr "^Y" eof "^E" swtch "^Z"

Note that many programs require an end-of-file signal. Many introductory books on UNIX assume that Control-D signifies the end-of-file. Once you have changed the control character, remember to use Control-E for the end-of-file (at least Control-E is easy to remember since end-of-file starts with the letter "E").

Suprmgr Configuration Files

When you run Suprtool, it automatically "uses" this configuration files if it exists:

/opt/robelle/suprmgr

The system manager usually creates /opt/robelle/suprmgr, and puts Suprtool commands in it to set Suprtool options. To check the options for your site, examine this configuration file.

On-Line vs. Batch Access

You normally run Suprtool as an on-line session. You type Suprtool commands on your terminal, and Suprtool prints responses on your terminal. If you redirect stdin or stdout, Suprtool assumes that it is in batch.

Suprtool in batch is almost identical to Suprtool on-line, except for answering questions. When Suprtool asks a question in batch, it does not expect an answer from stdin because no one is there to answer. Suprtool assumes that you want your batch task to complete, so it always selects the option that completes the command successfully. This is normally a "yes" answer, as in "yes, purge the file". Suprtool prints the question on stdout, as well as the answer that it has selected for you.

Command Line Options

You can invoke Suprtool/Open with various options. The syntax for invoking Suprtool/Open is

suprtool [-cv]

Initial Command Line:  -ccmdstring

You can specify commands by using the -c option followed by the actual commands. There must be no space between the -c and the command list.

If there is a space within the command, the whole command must be enclosed in single or double quotation marks; otherwise, the quotation marks are optional. Here are some examples:

suprtool -c"use usefile"

suprtool -c"set prompt $"

Default Outcount File Name:  -oc

If you want to know how many records Suprtool has processed, use the -oc option.  This option sets the file name for outcount to ".stoutcount".  After a successful task, Suprtool writes the number of output records to the .stoutcount file.  You can then use this file in shell scripts to check for specific record counts.

For example, suppose that you want to check for at least ten records from an Oracle database.  You would write a shell script like:

#!/bin/sh

#

#  Select records from an Oracle table and check that there

#  are at least ten.

 

suprtool -oc << !EOD

open   oracle scott tiger

select * from emp

if     sal > 1000.00

output /dev/null

exit

!EOD

if [ `cat .stoutcount` -ge 10 ]; then

   echo "More than 10 records found"

fi

Exit with Verify:  -v

Some users inadvertently Exit from Suprtool by entering the Exit command instead of Xeq. To prompt for Exit approval, use the -v option.

suprtool -v

>e

Okay to exit [no]:

> 

Combining -c and -v

You can combine both the -c and -v options with the following command:

suprtool -c"use usefile" -v

Loader Warnings

Suprtool by default does not print loader warnings. These are errors or problems when Suprtool starts up. Suprtool will attempt to find the Eloquence libraries and if configured, the Oracle libraries. You may use both, none or one of these libraries, so these warning/errors may be perfectly valid for your site. If you want to see the loader warnings you can run Suprtool with the –lw option:

suprtool -lw

Unix/Linux Notes

This section describes Suprtool/Open features that interact with the outside OS environment.

Shell Commands

You can execute shell commands by typing them anywhere you type a Suprtool command. If a command is both a shell and Suprtool command, you must precede the shell command by an exclamation mark (!) or a colon (:). Shell commands are executed by your default shell (the one configured in /etc/passwd or /etc/shadow for your user name).

$suprtool

>whoami                                {these 3 commands are identical}

>!whoami

>:whoami

 

>set  ...                              {does Suprtool's Set command}

>!set ...                              {does Linux/Unix's Set command}

The shell commands are executed by a child copy of your shell, child shells cannot change environment variables in the parent's environment. To change the value of an environment variable, you must first exit Suprtool.

Hardcoded File Names and ROBELLE Variable

Some file names are hardcoded into Suprtool. This section describes the hardcoded file names that Suprtool/Open may need. Suprtool will normally look for files in the /opt/robelle directory unless you set the ROBELLE variable.

ROBELLE Variable

Normally Suprtool looks files in the /opt/robelle directory. If you move Suprtool you must set the ROBELLE variable.

export ROBELLE="/users/robelle"

/opt/robelle/suprmgr

This is an optional file that is designed to contain configuration commands. You cannot change this file name. If you move Suprtool/Open to a different directory you must set the ROBELLE variable so Suprtool may find this file.

For example, if you move Suprtool to the /users/robelle directory you must set the ROBELLE variable in the following manner:

export ROBELLE="/users/robelle"

You can then put your suprmgr file in the /users/robelle directory.

Outcount File

If you want to automatically check the number of output records that Suprtool produced, you must produce an outcount file. This file contains a string with the number of output records that Suprtool processed.

By default, no outcount file is produced.  If you invoke Suprtool/Open with the "-oc" option, Suprtool writes the number of output records to a file called ".stoutcount." Use Set Filename Outcount to specify your own file name for the output count.

If you add Set Filename Outcount to the Suprmgr file, every successful invocation of Suprtool/Open produces a file. While these files are small, they may clutter up a busy system so that is why the default file name is none.

Differences Between MPE and Open

We have tried to make the MPE and Open versions of Suprtool as compatible as possible. This section describes how Suprtool/Open is different from Suprtool/MPE.

Record Length

On MPE, Suprtool can obtain the record length of a file.  There is no concept of record length on many Linux and Unix based platforms because a file consists of a string of bytes.  In Suprtool/Open, there are two ways to determine the record length.

1)       Specify the record length with the Rec parameter of the Input command.

2)       Use self-describing files.

If the specified record size is incorrect, Suprtool/Open cannot verify it. The most common symptom of an incorrect specification in size is an offset of one or more characters in each field.

Line Feeds

In MPE, there is no separator between records in a file. In Linux and Unix based systems there may not be a separator, or there may be a line feed between each record. For Suprtool to correctly read a data file, it must know whether the line feeds are present. You can specify whether or not a file has line feeds via the LF or NOLF options in the Input command.

Suprtool and STExport allow control over whether or not line feeds will be written to the output file or not. For details please see the Output Commands for both Suprtool and STExport.

Duplicate Output Files

If the output file already exists (and you haven't requested the Erase or Append option), Suprtool has to decide what to do. This is how Suprtool/Open handles duplicate output files:

        In Suprtool/Open, the duplicate output file processing takes place at the beginning of a task (in Suprtool/MPE it occurs at the end).

        If Suprtool/Open is in batch, it purges any existing file with the same file name (Suprtool/MPE chooses a new output file name of the form OutputNN). If the Suprtool/Open task is on-line, it prompts the user to purge the file.

        When Suprtool/Open purges a data file, it always deletes any associated .sd file, even if the output option is not Query or Link.

Classic Reals

Suprtool/Open does not support Classic real numbers (real or long). If you are porting data files from MPE to Linux or similar OS, you should first convert any Classic floating point numbers to their IEEE floating point equivalents. You can do this by using the Extract command on Suprtool/MPE.

$suprtool

>base     sample

>get      customer

>define   ieee-credit-rating,1,4,ieee

>extract  cust-no

>extract  name-first

>extract  name-last

>extract  ieee-credit-rating = credit-rating

>out      mpefile

>xeq

The Classic and IEEE floating point formats are not identical. Be sure to check the IEEE values after converting them from Classic floating point.

Eloquence still allows for a schema to use R type items, but internally they are stored as IEEE.  Suprtool and STExport will just map the real and long data types to their respective IEEE data types. Suprlink  does not need to map these data items as it does not support Real, Long or IEEE key values. You can turn this mapping off with Set RealMap Off, but you should not need this option.

Define Command

Suprtool will not allow you to define a real type item. Although Suprtool can map R items in SD files and in Eloquence datasets, we do not allow the creation of real or longs using the define command.

Input from Stdlist

In Suprtool/MPE, "input *" means read the input data from the stdin input device. This is usually a job stream, and data is terminated by an !EOD symbol.

Suprtool/Open does not support reading data from stdin (via Input * or any other method). If you need to create temporary data in the middle of a script, it is easy to use a temporary file. For example, the following script creates a temporary file, writes three lines of data to it, then uses this file as input to Suprtool/Open. At the end of the script we make sure that we remove the temporary file that we created.

#!/bin/sh

 

datafile=`mktemp`

 

echo "1234567 Line 1" >> $datafile

echo "2345678 Line 2" >> $datafile

echo "3456789 Line 3" >> $datafile

 

suprtool << !EOD

input    $datafile,rec 14,lf

define   key    ,1,7

define   line   ,8,7

extract  key

extract  line

list     standard

exit

!EOD

 

rm $datafile

Missing Features

The following Suprtool features on MPE are currently not available in Suprtool/Open:

Extracting a range of fields from an SQL database

Export command (STExport exists as a separate program)

Link command (Suprlink exists as a separate program)

Table Command with the File option requires that the file being loaded is self-describing.

Out= option of the Listredo command

Output Ask, Num,Key, and Num,Query

Output Temp (There are no temporary files in Linux/Unix)


Suprtool Issues and Solutions

A Suprtool Task

Suprtool's primary function is to extract data quickly; its focus is batch extraction. The key principle is: the bigger the input data source and the smaller the subset of data selected, the more performance improves.

Your aim is to replace serial reads and selection with Suprtool. To do this, break your task into components: an input choice, some processing selections, and the output choice.

Input Choices

Suprtool reads fixed-length data files. You can create self-describing files with Suprtool's Query or Link output options. It is easier to work with self-describing files because they have information about the fields in each input record.

Often you select a subset of the input records using the If command. Only selected records are passed to the processing stage and the output choice.

Processing Selections

If you do not specify any processing, the input records are quickly copied to the output choice. Some of your processing choices are

1.   Sort the records into ascending or descending sequence (Sort or Key). No records are output until all of the selected input records have been sorted.

2.   Total one or more input record fields (Total).

3.   Remove or select duplicate records (Duplicate).

Output Choices

Usually you wish to extract a subset of your records to feed into a report program, so the default output file is a data file. The default output file format matches the input file format, unless you use the Extract command. You can specify different formats for the output file by qualifying the Output command. To have readable ASCII output, use "output xxx,ascii". To produce "self-describing" files, use Output xxx,Link.

By default, every output record is identical to the corresponding input record. The Extract command assembles output records by stringing together fields extracted from the input records. With the Extract command you can insert constant values into the output record.

Each output record is written to the output choice. You can also see a formatted listing of each record with the List command.

Suprtool and Oracle

You specify an Oracle database with the Open command. You can open any Oracle database to which you normally have access. If you cannot open your Oracle database, check with your system or database administrator so that your environment can be set up properly. Once Suprtool has opened the database, use the Form command to obtain information about the tables in the database. Use the Select command to choose what data to read from your Oracle databases.

Oracle access is available as a separate add-on module to Suprtool.

Data-Types

When you specify a Select command, Suprtool figures out how to translate the Oracle internal data-types so that Suprtool can process them. Not all Oracle data-types can be processed by Suprtool. The following table lists the Oracle data-type and the corresponding Suprtool data-type:

Oracle Data-Type

Suprtool Data-Type

Varchar2

Byte

Number

Varies, see below

Long

Not supported

Rowid

Not supported

Date

Oracle Date

Raw

Not supported

Long raw

Not supported

Char

Byte

Mislabel

Not supported

Number Data-Type

Oracle numbers are translated into a variety of Suprtool data-types. The translation depends on the precision of the number and the number of decimal places. 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

In packed-decimal translations Suprtool uses the precision of the number to determine the size of a packed-decimal number.

Order By vs. Sort

Oracle's Order By statement on the Select command does not always generate the same results. Specifically, sorted fields with null field values appear at the beginning when they are sorted by Suprtool's Sort command.

Restrictions

Suprtool/Open cannot handle all Oracle database concepts. The current restrictions are:

Suprtool/Open can handle varchar2, char, date, and number data-types. It cannot handle any other data-type.

Because any Oracle Select command can be used, it is possible to generate column names that are not compatible with Suprtool/Open. For example,

>select sal + comm from bonus

This example produces a column called "sal + comm".  In some cases Suprtool/Open correctly uses this as the column name (e.g., the List command). You cannot refer to this column by name in any Suprtool/Open command that accepts field names as a parameter.

Suprtool and Self-Describing Files

A problem with data files is that there is no field information. Self-describing files solve this problem by providing field information about the file. Suprtool reads and writes SD files; Suprlink requires SD files as input and creates an SD file as output.

Create an SD File from a Table

You request an SD file using the Link option of the Output command. If you extract columns from the table, only the extracted columns appear in the SD file.

>select * from sales                   {input from a table}

>output salefile,link                  {salefile has all of the columns from sales}

>xeq

Create an SD File from a Data File

You must Define and Extract the fields you want to have in the SD file. Use the Link option of the Output command to create the file as a self-describing file. Although Suprtool itself allows longer field names, SD files only store the first 16 characters of a field name, unless SDExtName is on.

>input sales.data,reclen 16,nolf       {input from a data file}

>define cust_no,1,6,byte

>define invoice_date,7,6,integer

>define sales_qty,13,4,packed

>extract cust_no,invoice_date,sales_qty

>output salefile,link                  {salefile has all of the extracted columns}

>xeq

SD Files as Input

When you specify an SD file as input to Suprtool, all the field information becomes available. You can select, extract, and total fields without the Define command. In addition, the Input command no longer needs any Reclen or LF parameters.

>input salefile                        {self_describing file}

>form                                  {display the fields in the file}

>if sales_total > 10000                {select based on a field}

>extract cust_account                  {only extract a few fields}

>extract sales_qty

>extract sales_tax

>extract sales_total

>total sales_total                     {total a field from the file}

>output newfile,link                   {create a new SD file}

>xeq

Listing SD Files

Suprtool normally lists data files in an Octal/Char format. When listing an SD file, Suprtool produces a formatted listing with field names and field values converted into ASCII:

>input salefile                        {self-describing file}