Suprtool/Open 5.7

User Manual

Doc-To-Help Standard Manual

by 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.

Robelle Solutions Technology Inc.

Suite 372 - 7360 137 Street

Surrey, B.C. CanadaV3W 1A3

Phone: 604.501.2001

Support: 289.480.1060

E-mail: sales@robelle.com

E-mail: support@robelle.com

Web: www.robelle.com

Table of Contents 3

Welcome to Suprtool 15

Introduction......................................................................................................... 15

Suprtool Components............................................................................................ 15

STExport - Data Export Utility................................................................... 15

Suprlink - Multidataset Access.................................................................... 15

Documentation..................................................................................................... 16

Notation.............................................................................................................. 16

Suprtool Version 5.7 18

Highlights in Suprtool 5.7...................................................................................... 18

Installing Suprtool 19

Overview............................................................................................................. 19

Who Needs To Use These Instructions..................................................................... 19

Instructions for Installing........................................................................................ 19

Quick Start Guide for Suprtool 20

How to Run Suprtool............................................................................................ 20

What is a Task?................................................................................................... 20

Copying Files...................................................................................................... 20

Copying One File..................................................................................... 20

Appending to a File................................................................................... 21

Fields in Data Files............................................................................................... 21

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

Creating an SD File................................................................................... 21

Define Fields in a Data File......................................................................... 21

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

Repeating Commands............................................................................................ 22

Repeating a Command............................................................................... 22

Selecting Database Records..................................................................................... 23

Select All Records..................................................................................... 23

Look at the First Few Records..................................................................... 23

Selecting by Criteria.............................................................................................. 24

Simple Criteria......................................................................................... 24

Complex Criteria...................................................................................... 24

String of Digits......................................................................................... 24

Selecting by Date.................................................................................................. 24

Select by Today's Date............................................................................... 25

Select by Particular Date............................................................................. 25

Select by Year.......................................................................................... 25

Select Prior Month.................................................................................... 25

Selecting by Lists of Values.................................................................................... 26

Finding Data Based on a List...................................................................... 26

Finding Data Based on a File...................................................................... 26

Finding Data Based on Another Table’s Criteria............................................. 27

Finding Data in a Data File......................................................................... 27

Sorting Database Records....................................................................................... 27

Sort Records............................................................................................ 27

Sort Records in Descending Order................................................................ 28

Sort by Multiple Keys............................................................................... 28

Duplicate Records................................................................................................. 28

Report without Duplicate Records................................................................ 28

Report Only the Duplicate Records............................................................... 29

Report Only the Unique Records.................................................................. 29

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

Deleting Duplicate Data File Records............................................................ 30

Decimal Places..................................................................................................... 31

Converting Numbers.............................................................................................. 31

Counts and Subtotals............................................................................................. 32

Count and Subtotal on Sort Keys................................................................. 32

Sort by Count or Subtotal.......................................................................... 32

Total by Field.......................................................................................... 33

Running Totals........................................................................................ 33

Running SubTotals................................................................................... 33

Listing Records.................................................................................................... 34

Changing the Output Record Format........................................................................ 34

Producing a Condensed Table Listing....................................................................... 35

Simple Reports..................................................................................................... 35

Your First Report...................................................................................... 35

Column Headings..................................................................................... 36

Printing Mailing Labels............................................................................. 36

Running Suprtool 39

Running Suprtool................................................................................................. 39

Setting Up a PATH for Suprtool.............................................................................. 39

Bourne and Korn Shells............................................................................. 39

C Shell................................................................................................... 40

Dynamic Loading.................................................................................................. 40

Control Characters and stty..................................................................................... 40

Suprmgr Configuration Files................................................................................... 41

On-Line vs. Batch Access....................................................................................... 41

Command Line Options......................................................................................... 41

Initial Command Line: -ccmdstring............................................................. 41

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

Exit with Verify: -v.................................................................................. 42

Combining -c and -v.................................................................................. 42

Loader Warnings....................................................................................... 42

Unix/Linux Notes................................................................................................. 42

Shell Commands...................................................................................... 42

Hardcoded File Names and ROBELLE Variable.......................................................... 43

ROBELLE Variable................................................................................... 43

/opt/robelle/suprmgr.................................................................................. 43

Outcount File........................................................................................... 43

Differences Between MPE and Open.......................................................................... 43

Record Length.......................................................................................... 44

Line Feeds............................................................................................... 44

Duplicate Output Files............................................................................... 44

Classic Reals............................................................................................ 44

Input from Stdlist...................................................................................... 45

Missing Features....................................................................................... 45

Suprtool Issues and Solutions 47

A Suprtool Task................................................................................................... 47

Input Choices........................................................................................... 47

Processing Selections................................................................................. 47

Output Choices......................................................................................... 47

Suprtool and Oracle............................................................................................... 48

Data-Types.............................................................................................. 48

Number Data-Type.................................................................................... 48

Order By vs. Sort...................................................................................... 49

Restrictions.............................................................................................. 49

Suprtool and Self-Describing Files........................................................................... 49

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

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

SD Files as Input...................................................................................... 50

Listing SD Files....................................................................................... 50

Decimal Places and Date Formats................................................................. 50

Extended Field Names............................................................................... 50

Restrictions of SD Files............................................................................. 51

Suprtool and Sorting Files...................................................................................... 51

Suprtool and Personal Computers............................................................................ 51

Downloading to the PC.............................................................................. 51

Decimal Places......................................................................................... 51

Spreadsheets............................................................................................. 52

Two-Digit Year Solutions...................................................................................... 52

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

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

Stddate and Set Date Cutoff......................................................................... 53

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

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

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

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

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

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

aammdd Date Format................................................................................. 55

Invalid Dates............................................................................................ 56

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

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

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

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

Performance.......................................................................................................... 60

Eloquence Performance............................................................................... 60

Sort Performance....................................................................................... 60

Oracle Performance.................................................................................... 61

Analyzing Performance Data........................................................................ 61

Variable Substitution............................................................................................. 61

Suprtool Functions................................................................................................ 62

UC4/Scripting and Functions.................................................................................. 62

String/Byte Functions............................................................................................ 62

$TRIM (Works on byte type fields).......................................................................... 63

If Usage:.................................................................................................. 63

Extract Usage (target: Byte type fields).......................................................... 63

Example:................................................................................................. 63

Data Examples Before and After:.................................................................. 63

$LTRIM (Works on Byte type fields)....................................................................... 63

If Usage:.................................................................................................. 64

Extract Usage (target: Byte type fields).......................................................... 64

Example:................................................................................................. 64

Data Examples before and after:.................................................................... 64

$RTRIM (Works on Byte type fields)....................................................................... 64

If Usage:.................................................................................................. 64

Extract Usage (target: Byte type fields).......................................................... 64

Example:................................................................................................. 64

Data Examples:......................................................................................... 64

Data Result.............................................................................................. 64

$UPPER (Works on Byte-type fields)....................................................................... 65

If Usage:.................................................................................................. 65

Extract Usage (target: Byte type fields).......................................................... 65

Example:................................................................................................. 65

Data Examples:......................................................................................... 65

$LOWER (Works on Byte-type fields)...................................................................... 65

If Usage:.................................................................................................. 65

Extract Usage:.......................................................................................... 65

Example:................................................................................................. 65

Data Examples:......................................................................................... 65

$PROPER (Works on Byte-type fields)..................................................................... 66

If Usage:.................................................................................................. 66

Extract Usage:.......................................................................................... 66

Example:................................................................................................. 66

Data Examples:......................................................................................... 66

$SPLIT (Works on Byte-type fields)........................................................................ 66

If Usage:.................................................................................................. 66

Extract Usage:.......................................................................................... 67

Data Examples:......................................................................................... 67

$FINDCLEAN (Works on Byte-type fields)............................................................... 67

If Usage:.................................................................................................. 67

$CLEAN (Works on Byte-type fields)....................................................................... 67

If Usage: (Not commonly used).................................................................... 68

Extract Usage:.......................................................................................... 68

Example:................................................................................................. 68

Data Examples:......................................................................................... 68

$TRANSLATE (Works on Byte-type fields).............................................................. 68

If Usage:.................................................................................................. 68

Extract Usage:.......................................................................................... 68

Data Examples: (Using above code).............................................................. 68

$ETOA............................................................................................................... 68

Extract Usage:.......................................................................................... 69

$ATOE............................................................................................................... 69

Extract Usage:.......................................................................................... 69

String Addition.................................................................................................... 69

Extract Usage:.......................................................................................... 69

Example:................................................................................................. 69

Data Result:............................................................................................. 69

Numeric Functions................................................................................................ 69

$TRUNCATE...................................................................................................... 69

If Usage:.................................................................................................. 70

Extract Usage:.......................................................................................... 70

$ABS................................................................................................................. 70

IF Usage:................................................................................................. 70

Extract Usage:.......................................................................................... 70

$TOTAL............................................................................................................. 70

IF Usage:................................................................................................. 70

Extract Usage:.......................................................................................... 70

$SUBTOTAL...................................................................................................... 70

IF Usage:................................................................................................. 71

Extract Usage:.......................................................................................... 71

Example of $TOTAL and $SUBTOTAL...................................................... 71

$COUNTER........................................................................................................ 71

IF Usage:................................................................................................. 71

Extract Usage:.......................................................................................... 72

$SUBCOUNT...................................................................................................... 72

IF Usage:................................................................................................. 72

Extract Usage:.......................................................................................... 72

Examples for $counter and $subcount:........................................................... 72

$SIGNED............................................................................................................ 73

IF Usage:................................................................................................. 73

Extract Usage:.......................................................................................... 73

Arithmetic Operations............................................................................................ 73

+ - * / mod.............................................................................................. 73

If Usage:.................................................................................................. 73

Extract Usage:.......................................................................................... 73

Conversion/Formatting.......................................................................................... 73

$NUMBER.......................................................................................................... 73

If Usage:.................................................................................................. 74

Extract Usage:.......................................................................................... 74

Data Examples:......................................................................................... 74

$EDIT................................................................................................................ 74

If Usage:.................................................................................................. 74

Extract Usage:.......................................................................................... 74

Data Examples:......................................................................................... 74

Other Functions.................................................................................................... 75

$LOOKUP........................................................................................................... 75

If Usage:.................................................................................................. 75

Extract Usage:.......................................................................................... 75

$NULL............................................................................................................... 75

$READ............................................................................................................... 75

If Usage:.................................................................................................. 75

Date Functions..................................................................................................... 76

$TODAY............................................................................................................ 76

If Usage:.................................................................................................. 76

Extract Usage:.......................................................................................... 76

$DATE............................................................................................................... 76

If Usage:.................................................................................................. 76

Extract Usage:.......................................................................................... 76

$INVALID........................................................................................................... 76

If Usage:.................................................................................................. 76

Extract Usage:.......................................................................................... 76

$STDDATE........................................................................................................ 77

If Usage:.................................................................................................. 77

Extract Usage:.......................................................................................... 77

$DAYS............................................................................................................... 77

If Usage:.................................................................................................. 77

Extract Usage:.......................................................................................... 77

Suprtool Commands 78

General Notes....................................................................................................... 78

Abbreviating............................................................................................ 78

Uppercase or Lowercase.............................................................................. 78

Multiple Commands per Line...................................................................... 78

Continuation............................................................................................ 79

Comments on Command Lines................................................................... 79

OS Commands......................................................................................... 79

Calculator................................................................................................ 80

Control-Y Interrupt.................................................................................... 80

Add Command [Add]........................................................................................... 81

Notes...................................................................................................... 81

Examples................................................................................................. 81

Base Command [BA]............................................................................................ 83

Before Command [B]............................................................................................ 85

Chain Command [C]............................................................................................ 87

Clean Command [CL]........................................................................................... 89

Removing Bad Characters........................................................................... 89

Define Command [D]............................................................................................ 91

Delete Command [DEL]........................................................................................ 96

Do Command [DO].............................................................................................. 98

Duplicate Command [DU]..................................................................................... 99

Edit Command [ED]........................................................................................... 104

Exit Command [E]............................................................................................. 105

Export Command [EXP]...................................................................................... 107

Extract Command [EXT]..................................................................................... 108

Constants............................................................................................... 108

Dates..................................................................................................... 110

Range of Fields....................................................................................... 112

Numeric Expressions................................................................................ 113

$SubTotal Function................................................................................. 115

$Total Function...................................................................................... 115

$Counter Function................................................................................... 115

String Expressions................................................................................... 116

Splitting Variable Length Strings............................................................... 117

Cleaning your Data.................................................................................. 118

Un-printables.......................................................................................... 119

Clean Command Syntax........................................................................... 119

Setting the Clean Character....................................................................... 119

Cleaning a Field...................................................................................... 119

Cleaning your data................................................................................... 120

Extract from a Table................................................................................. 120

Data Conversion...................................................................................... 121

$Number Function................................................................................... 122

Numeric to Byte Conversion..................................................................... 123

$Edit Function........................................................................................ 124

Placeholders and Format Characters............................................................ 124

Byte-Type Formatting.............................................................................. 124

Z-placeholder for byte-fields....................................................................... 125

Overflow and limits................................................................................. 125

Numeric field edit-masks........................................................................... 126

Signs.................................................................................................... 126

Decimal Places........................................................................................ 127

Data and Edit mask:................................................................................. 127

Currency and Dollar signs......................................................................... 127

Overflow and floating dollar....................................................................... 128

Set CurrencySymbol................................................................................ 128

Overflow and limits................................................................................. 128

Restrictions............................................................................................ 128

Form Command [F]............................................................................................ 131

Get Command [G].............................................................................................. 134

Help Command [H]............................................................................................ 136

If Command [IF]................................................................................................ 137

Expressions............................................................................................ 137

Constants............................................................................................... 140

Subscripts.............................................................................................. 141

Numeric Expressions................................................................................ 142

String Expressions................................................................................... 144

Date Selection......................................................................................... 147

Long Expressions.................................................................................... 152

Input Command [I]............................................................................................. 155

Item Command [IT]............................................................................................ 157

Date Formats.......................................................................................... 157

Decimal Places........................................................................................ 160

Notes.................................................................................................... 161

Key Command [K]............................................................................................. 163

Link Command [LIN]......................................................................................... 165

List Command [L].............................................................................................. 166

Format.................................................................................................. 166

LaserJet Listings..................................................................................... 167

Headings in Listings................................................................................ 168

Simple Reports....................................................................................... 169

List Device............................................................................................. 170

List File................................................................................................ 170

Listredo Command [LISTREDO].......................................................................... 173

Numrecs Command [N]....................................................................................... 174

Open Command [OP].......................................................................................... 175

Remote Databases and Oracle Issues............................................................ 175

Output Command [O]......................................................................................... 176

Put Command [P].............................................................................................. 182

Q Command [Q]................................................................................................ 183

Redo Command [REDO]..................................................................................... 184

Reset Command [R]........................................................................................... 187

Select Command [SEL]....................................................................................... 188

Set Command [S]............................................................................................... 189

Allbase.................................................................................................. 191

Arithmetic.............................................................................................. 191

Baseclose............................................................................................... 191

Blocksize............................................................................................... 191

Buffer.................................................................................................... 191

CleanChar.............................................................................................. 191

CurrencySymbol..................................................................................... 192

Date Cutoff............................................................................................. 192

Date ForceCentury................................................................................... 193

Date IfYY2000Error................................................................................. 193

Date MapToPHDate8............................................................................... 194

DecimalSymbol...................................................................................... 194

Defer..................................................................................................... 194

DumpOnError......................................................................................... 195

EditStopError......................................................................................... 195

Eofread.................................................................................................. 195

FastRead................................................................................................ 195

Filecode................................................................................................. 195

Filename................................................................................................ 196

Firstrec.................................................................................................. 196

Hints..................................................................................................... 196

HPUXCmdErr “<string>”........................................................................ 196

Ifcheck................................................................................................... 197

Ignore.................................................................................................... 197

InitExtents............................................................................................. 197

ItemAbbreviateDate.................................................................................. 197

ItemLock............................................................................................... 197

Interactive.............................................................................................. 198

LabelledTapeRewind................................................................................ 198

Limits................................................................................................... 198

Table Size.............................................................................................. 198

Read Only.............................................................................................. 199

List....................................................................................................... 199

List Date................................................................................................ 199

List PCL............................................................................................... 199

List Time.............................................................................................. 201

List FormFeed........................................................................................ 201

Lock..................................................................................................... 201

MakeAbsent........................................................................................... 201

NLS...................................................................................................... 201

NumBug................................................................................................ 202

Openmode.............................................................................................. 202

Oracle Rows........................................................................................... 203

Oracle Integer.......................................................................................... 203

Oracle OpenFix....................................................................................... 203

Oracle PassShift...................................................................................... 204

Oracle SpaceNull..................................................................................... 204

Oracle ZeroNull....................................................................................... 204

Pattern................................................................................................... 204

Prefetch.................................................................................................. 205

Privmode............................................................................................... 205

Progress................................................................................................. 205

Prompt.................................................................................................. 206

RealMap................................................................................................ 206

Recover................................................................................................. 206

Redo..................................................................................................... 206

SDExtname............................................................................................ 207

Sortfast.................................................................................................. 208

Squeeze................................................................................................. 208

Statistics................................................................................................ 208

Subsystem............................................................................................. 208

Suspend................................................................................................. 208

ThousandSymbol.................................................................................... 208

Userlabels.............................................................................................. 208

Varsub................................................................................................... 208

VarsubCompat........................................................................................ 209

VarsubDebug.......................................................................................... 209

Warnings............................................................................................... 209

Sort Command [SO]........................................................................................... 210

Table Command [TA].......................................................................................... 212

Adding Individual Values to a Table........................................................... 212

Adding Values from a File........................................................................ 213

TRanslate Command [TR].................................................................................... 216

Total Command [T]............................................................................................ 218

Update Command [UP]....................................................................................... 220

Use Command [U].............................................................................................. 221

Userpause Command [USER]............................................................................... 223

Verify Command [V]........................................................................................... 224

Xeq Command [X]............................................................................................. 225

Calculator Command [=]...................................................................................... 226

Suprtool Errors and Warnings 229

Two Types Of Messages....................................................................................... 229

Errors................................................................................................................ 229

Warnings........................................................................................................... 230

Welcome to STExport 231

Welcome to STExport......................................................................................... 231

Installing STExport............................................................................................. 231

Accessing STExport 233

How To Run STExport........................................................................................ 233

How to Xeq an STExport Task.............................................................................. 233

Hardcoded File Names and ROBELLE Variable........................................................ 233

ROBELLE Variable................................................................................. 233

Using STExport in Batch..................................................................................... 234

Command Line Options....................................................................................... 234

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

Variable Substitution -v............................................................................ 234

Introduction to STExport 235

Importing Data.................................................................................................... 235

Input File........................................................................................................... 235

Data-Types......................................................................................................... 235

Formatting Commands......................................................................................... 236

Commands......................................................................................................... 236

Performance Considerations................................................................................... 236

STExport Commands 237

General Notes..................................................................................................... 237

Abbreviating........................................................................................... 237

Uppercase or Lowercase............................................................................ 237

Comments on Command Lines.................................................................. 237

OS Commands....................................................................................... 237

File Names............................................................................................. 238

Calculator.............................................................................................. 238

Control-Y.............................................................................................. 238

Before Command [B].......................................................................................... 239

Clean Command [CL]......................................................................................... 241

Removing Bad Characters......................................................................... 241

Columns Command [C]...................................................................................... 242

Date Command [DA].......................................................................................... 243

Decimal Command [DEC]................................................................................... 245

Delimiter Command [DE].................................................................................... 246

Do Command [DO]............................................................................................ 247

Escape Command [ES]........................................................................................ 248

Exit Command [E]............................................................................................. 249

Exit Abort [EA]...................................................................................... 249

Exit Suspend [ES]................................................................................... 249

Exit Xeq [EX]......................................................................................... 249

Floating Command [FL]...................................................................................... 251

Form Command [F]............................................................................................ 252

Heading Command [HEA]................................................................................... 253

Help Command [H]............................................................................................ 255

HTML Command [HT]....................................................................................... 256

Dynamic Web Pages................................................................................ 258

Web Server............................................................................................ 258

Shell Script............................................................................................ 259

Perl Script.............................................................................................. 261

CGI Script............................................................................................. 262

Input Command [I]............................................................................................. 264

Listredo Command [LISTREDO].......................................................................... 265

Output Command [O]......................................................................................... 266

Quote Command [Q]........................................................................................... 267

Redo Command [REDO]..................................................................................... 268

Reset Command [R]........................................................................................... 269

Set Command [S]............................................................................................... 270

CleanChar.............................................................................................. 270

Mapped................................................................................................. 270

Redo..................................................................................................... 270

Statistics................................................................................................ 271

Varsub................................................................................................... 271

VarsubCompat........................................................................................ 272

VarsubDebug.......................................................................................... 272

Warnings............................................................................................... 272

Xmltagchar............................................................................................. 273

ZonedFix............................................................................................... 273

Sign Command [SI]............................................................................................ 274

Spaces Command [SP]........................................................................................ 275

Use Command [U].............................................................................................. 276

Verify Command [V]........................................................................................... 277

Xeq Command [X]............................................................................................. 278

XML Command [XML]...................................................................................... 279

Zero Command [Z]............................................................................................. 282

Example of STExport Output 283

Example............................................................................................................ 283

Limits Within STExport 287

Maximums......................................................................................................... 287

Welcome to Suprlink 288

Welcome to Suprlink........................................................................................... 288

Terminology and HP-UX...................................................................................... 288

Notation............................................................................................................ 288

Installing Suprlink............................................................................................... 289

Hardcoded File Names and ROBELLE Variable........................................................ 289

ROBELLE Variable................................................................................. 289

Accessing Suprlink 291

How To Run Suprlink......................................................................................... 291

How to Xeq a Suprlink Task................................................................................. 291

Suprtool Link Command...................................................................................... 291

Exit with Verify.................................................................................................. 291

Using Suprlink in Batch....................................................................................... 292

Command Line Options....................................................................................... 292

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

Variable Substitution -v............................................................................ 292

Introduction to Suprlink 293

How Report Programs Work................................................................................. 293

Input Files......................................................................................................... 293

Link Files.......................................................................................................... 293

Output Files....................................................................................................... 294

Sort Keys........................................................................................................... 294

Selection Logic................................................................................................... 294

A Link Example.................................................................................................. 294

A Join Example.................................................................................................. 295

Performance Considerations................................................................................... 296

Another Example................................................................................................. 297

Illegal Digits...................................................................................................... 297

Selecting Non-Matches......................................................................................... 298

Suprlink with Quiz/QTP...................................................................................... 299

Suprlink Commands 301

General Notes..................................................................................................... 301

Abbreviating........................................................................................... 301

Uppercase or Lowercase............................................................................ 301

Continuation.......................................................................................... 301

Comments on Command Lines.................................................................. 302

OS Commands....................................................................................... 302

File Names............................................................................................. 302

Calculator.............................................................................................. 302

Control-Y.............................................................................................. 302

Before Command [B].......................................................................................... 304

Do Command [DO]............................................................................................ 306

Exit Command [E]............................................................................................. 307

Exit Abort [EA]...................................................................................... 307

Exit Suspend [ES]................................................................................... 307

Exit Xeq [EX]......................................................................................... 307

Form Command [F]............................................................................................ 308

Help Command [H]............................................................................................ 310

Input Command [I]............................................................................................. 311

Join Command [J].............................................................................................. 312

Link Command [L]............................................................................................. 314