home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 8 Other
/
08-Other.zip
/
spufi116.zip
/
README
next >
Wrap
Text File
|
1993-12-30
|
28KB
|
1,215 lines
SPUFI/2 From ICA
A DBA SQL Front-end for OS/2 DB2/2 and Database Manager
V1.1.6
@ Copyright ICE Computer Associates Limited 1993
ICE Computer Associates Limited
31A Theydon Grove
Epping
Essex
CM16 4PU
England
Tel: (UK) 0992 560788
December 1993 SPUFI/2 V1.1.6
CONTENTS
1 What is SPUFI/2?
2 SPUFI/2 Keyboard Mapping
3 The SPUFI/2 Dialogue
4 SPUFI/2 Panels
5 SQL
6 Command Line Parameters
7 Installation
8 SPUFI2.INI
9 Automatic Binding
10 Support
11 Changes in Version 1.1.6
Copyright ICE Computer Associates 1993 Page 2
December 1993 SPUFI/2 V1.1.6
1 WHAT IS SPUFI/2?
You must first ask what is SPUFI? SPUFI is an IBM product
which works in the MVS TSO/ISPF environment as a
DBA/Programmer SQL front-end to DB2. SPUFI is short for SQL
Processing Using File Input. SPUFI/2 is an OS/2 version of
that product for use with Database Manager and DB2/2.
SPUFI/2 was developed to fill a gap in the functionality
provided with OS/2 Database Manager. The purpose of the
program is to enable a stream of SQL statements to be
executed, rather than executing SQL statements one at a time
as is required by Query Manager and the DBM or DB2/2 Command
Line Processor. SPUFI/2 has the look and feel of the
mainframe product, whilst adding enhancements of its own,
including timing of queries.
SPUFI/2 Version 1.1.6 is a 16 bit program compiled with
Microsoft C6.0, which will operate equally well with OS/2
Extended Edition Version 1.2 or 1.3, with OS/2 Version 2.0
with Extended Services and with OS/2 Version 2.1 with DB2/2.
SPUFI/2 is Shareware. The Shareware concept allows you to
evaluate software before purchasing it. Shareware is not
free software. Should you decide to use this program after
you have evaluated it, you are asked to register your use of
it with the author. Thank you for supporting this concept.
Copyright ICE Computer Associates 1993 Page 3
December 1993 SPUFI/2 V1.1.6
DISCLAIMER - AGREEMENT
Users of SPUFI/2 must accept this disclaimer of warranty:
"SPUFI/2 is supplied as is. ICE Computer Associates
Limited and David Joice disclaim all warranties, expressed
or implied, including, without limitation, the warranties of
merchantability and of fitness for any purpose. Neither ICE
Computer Associates Limited nor David Joice assume any
liability for damages, direct or consequential, which may
result from the use of SPUFI/2."
SPUFI/2 is a "shareware program" and is provided at no
charge to the user for evaluation. Feel free to share it
with your colleagues, but please do not give it away altered
or as part of another system. The essence of "user-
supported" software is to provide personal computer users
with quality software without high prices, and yet to
provide an incentive for programmers to continue to develop
new products. If you find this program useful and find that
you are using SPUFI/2 and continue to use SPUFI/2 after a
reasonable trial period, you must make a registration
payment of 45 Pounds Sterling for UK orders or 60 US Dollars
for overseas orders to ICE Computer Associates Limited. UK
purchasers must add VAT at the prevailing rate.
The 45 Pounds Sterling or 60 US Dollars registration fee
will license one copy for use on any one computer at any one
time. Registered users will receive a copy of the latest
version of SPUFI/2.
Commercial users of SPUFI/2 must register and pay for their
copies of SPUFI/2 within 30 days of first use or their
license is withdrawn. Site-License arrangements may be made
by contacting ICE Computer Associates Limited.
Anyone distributing SPUFI/2 for any kind of remuneration
must first contact ICE Computer Associates Limited for
authorization.
You are encouraged to pass a copy of SPUFI/2 along to your
colleagues for evaluation. Please encourage them to
register their copy if they find that they can use it. All
registered users will receive a copy of the latest version
of SPUFI/2.
Copyright ICE Computer Associates 1993 Page 4
December 1993 SPUFI/2 V1.1.6
2 SPUFI/2 KEYBOARD MAPPING
SPUFI/2 uses IBM 3270 keyboard mapping and TSO Function Key
conventions.
ENTER SPUFI/2 uses the right hand control key to mean
ENTER. Some portable machines do not have a right
hand control key. For this reason both right and
left hand control keys may be used as the ENTER
key.
HELP Either enter the word HELP at the command line and
press ENTER or press F1.
END Either enter the word END at the command line and
press ENTER or press F3.
Tab The AT keyboard Tab key moves the cursor to the
first position of the next input field. Shift/Tab
moves the cursor to the first position of the
previous input field.
Return The AT keyboard Return key moves the cursor to the
first position of the first input field on the
next line down.
Insert The AT keyboard Insert key puts the panel into
insert mode. Pressing Insert again ends insert
mode.
Escape The AT Escape key performs in the same way as F3.
Copyright ICE Computer Associates 1993 Page 5
December 1993 SPUFI/2 V1.1.6
3 THE SPUFI/2 DIALOGUE
Use of SPUFI/2 proceeds according to a pre-determined
pattern. A simple dialogue is described below:
The following data is entered on the SPUFI/2 main panel:
o the name of an input file - this file may be empty or
may be an existing file
containing previously
written SQL statements.
o the name of a Database - this database must
Manager Database contain the tables
referenced in the input
SQL file.
o the name of an output file - SPUFI/2 will place the
results of processing the
input SQL file into this
file.
o a set of YES/NO processing - CHANGE DEFAULTS
options EDIT INPUT
EXECUTE
AUTOCOMMIT
BROWSE OUTPUT
The dialogue proceeds as follows:
1 Complete the panel as indicated above. Press ENTER
2 If required Database Manager will be
started. SPUFI/2 will connect to
the Database specified.
If CHANGE DEFAULTS has been set to YES,
the SPUFI/2 DEFAULTS panel will be
displayed. Modify the defaults as
required (See Section 4). Press ENTER
3 If EDIT INPUT has been set to YES, the
editor specified on the SPUFI/2 DEFAULTS
panel will be invoked and the input file
specified will be opened. Edit the SQL
as required. END the Edit Session
4 The SPUFI/2 main panel will be displayed.
The processing options for the previously
executed steps in the dialogue will have
been set to *. Press ENTER
Copyright ICE Computer Associates 1993 Page 6
December 1993 SPUFI/2 V1.1.6
5 If EXECUTE has been set to YES, the input
SQL file will be processed against the
Database specified. The results will be
placed in the output file specified.
If AUTOCOMMIT has been set to YES, and the
SQL was processed successfully a COMMIT
will be executed. A ROLLBACK will be
executed if the processing was unsuccessful.
If BROWSE OUTPUT has been set to YES, the
editor specified on the SPUFI/2 DEFAULTS
panel will be invoked and the output file
specified will be opened. Browse the results
of the SQL processing as required.
END the Edit Session
6 The SPUFI/2 main panel will be re-displayed,
with the processing options reset to the
value previously specified. To re-edit
the input file and repeat the dialogue. Press ENTER
Copyright ICE Computer Associates 1993 Page 7
December 1993 SPUFI/2 V1.1.6
4 SPUFI/2 PANELS
The SPUFI/2 Main Panel
======================
Line 2 the Command Line
Enter the Commands ENTER, END or HELP and Press
the ENTER key.
e.g.
===>END
This will end this SPUFI/2 session.
Enter OS/2 input file specification:
Enter the name of the input SQL file. If a file
of that name does not exist one will be created.
This is the file into which the SQL statements,
which are to be processed by SPUFI/2 are placed.
See Section 5 for rules about the SQL used.
The full path may be specified. If no path is
specified the file is assumed to be in the Working
Directory. (See Section 7)
e.g.
DRIVE/PATH NAME ===> SAMPLE.SQL
SPUFI/2 will look for a file named SAMPLE.SQL in
the current working directory; if found this will
be the target of the edit session. If not found,
the file will be created.
Enter Database name:
Enter the name of a Database Manager Database
which is catalogued on this Workstation and to
which you have access.
e.g.
DATABASE NAME ... ===> SAMPLE
This specifies a database called SAMPLE. If you
have installed database Manager the sample
database is created by typing SQLSAMPL at the
command line.
Copyright ICE Computer Associates 1993 Page 8
December 1993 SPUFI/2 V1.1.6
The SPUFI/2 Main Panel ... /Continued
============================================
Enter OS/2 output file specification:
Enter the name of an output file. If a file of
that name does not exist one will be created.
This is where the results of the SQL processing
will be placed. See also the APPEND TO OUTPUT
setting on the SPUFI/2 Defaults panel.
The full path may be specified. If no path is
specified the file is assumed to be in the Working
Directory. (See Section 7)
e.g.
DRIVE/PATH NAME ===> SAMPLE.LST
Specify processing options:
CHANGE DEFAULTS ===>
Enter YES if you wish to have the SPUFI/2 Defaults
panel displayed prior to the edit session.
EDIT INPUT ...... ===>
Enter YES if you wish to edit the input file.
EXECUTE ......... ===>
Enter YES if you wish to process the SQL
statements in the input file.
AUTOCOMMIT ...... ===>
Enter YES if you wish SPUFI/2 to issue a COMMIT
statement if, after processing the SQL, the final
SQL return code is zero. In the event of a non-
zero return code AUTOCOMMIT = YES will cause
SPUFI/2 to issue a ROLLBACK statement.
Enter NO if you wish to delay the decision until
after you have browsed the results file. If NO is
entered the SPUFI/2 COMMIT OR ROLLBACK PANEL will
be displayed after the browse session.
See also the STOP AFTER ERROR setting on the
SPUFI/2 Defaults panel.
BROWSE OUTPUT ... ===>
Enter YES if you wish to browse the results file.
Copyright ICE Computer Associates 1993 Page 9
December 1993 SPUFI/2 V1.1.6
The SPUFI/2 Defaults Panel
==========================
ISOLATION LEVEL ===>
Select the isolation level for the session:
RR Repeatable Read
CS Cursor Stability
UR Uncommitted Read
Default RR
MAX SELECT LINES ===>
Select the maximum number of lines to be returned as
the result of a select statement. If you wish the
number of lines to be unlimited enter zero.
Default 250
EDITOR TO BE USED ===>
Enter the name of the editor you wish to use in edit
and browse sessions. The default editor for use with
SPUFI/2 is the OS/2 System Editor E.EXE. Before
running SPUFI/2 it is worth executing the system editor
to set it up correctly for use with SPUFI/2.
Select the OPTIONS Pull-down menu:
Turn Word wrap OFF.
Set font to a fixed pitch font e.g. courier 8
Bold.
Alternative editors
SPUFI/2 May be used with any editor which can be
initiated by a command of the following form:
<editorname> <filename>
where editorname is the name of the editor and
filename is the name of the file to be edited.
The editor program must lie within the scope of the
PATH environment variable.
Copyright ICE Computer Associates 1993 Page 10
December 1993 SPUFI/2 V1.1.6
The SPUFI/2 Defaults Panel ... /Continued
=================================================
Warning about Editors
Select statements can return data which may, with some
editors, cause certain display peculiarities.
For example, SELECT * FROM SYSIBM.SYSTABLES results in
the LONG VARCHAR column PACKED_DESC being displayed in
the result. The data in this column will probably
contain 1 or more bytes containing X'0D', which some
editors including the system editor E will interpret as
carriage return. This in itself is not invalid, but it
will make your screen display look messy.
SPF/2
SPUFI/2 will operate well with the editor/browser
included in the CTC product SPF/2. If the editor name
on the SPUFI/2 defaults panel is set to SPF2, then
SPUFI/2 will use the SPF/2 editor for the edit session
and the SPF/2 browser for the browse session.
SPF/2 is a trademark of Command Technology Corporation.
STOP AFTER ERROR ===>
Enter the error count which, when reached, will cause
processing of the SQL file to terminate. If the value
entered is greater than 1, it is advisable to set
AUTOCOMMIT = NO. If AUTOCOMMIT = YES and STOP AFTER
ERROR is greater than 1, SPUFI/2 will ROLLBACK if any
errors occur, even if the last SQL return code is zero.
Using AUTOCOMMIT = NO allows you to choose whether to
ROLLBACK or COMMIT.
Default 1
APPEND TO OUTPUT ===>
Enter YES if you wish each successive set of results to
be appended to the previous set. Enter NO if you wish
the latest set of results to overwrite the previous
set.
Default NO
Copyright ICE Computer Associates 1993 Page 11
December 1993 SPUFI/2 V1.1.6
The SPUFI/2 Defaults Panel ... /Continued
=================================================
MAX NUMERIC FIELD ===>
Enter the maximum column width for displaying numeric
fields. If the value returned is greater then the size
specified the value will be replaced by a row of
asterisks **********.
Default 20
MAX CHAR FIELD .. ===>
Enter the maximum column width for displaying character
fields. If the size of the string returned is greater
then the size specified the string will be truncated on
the right.
Default 80
COLUMN HEADING .. ===>
Not used at this time.
Default NAMES
The SPUFI/2 COMMIT or ROLLBACK Panel
====================================
The COMMIT or ROLLBACK panel is displayed when an SQL file
has been processed and the AUTOCOMMIT option has been set to
NO.
COMMIT will save your database changes
ROLLBACK will erase your database changes
DEFER will defer the decision, which will allow you
to continue processing another input SQL
file. If you select DEFER, it is possible
that you will tie up system resources that
others need.
Default COMMIT
Copyright ICE Computer Associates 1993 Page 12
December 1993 SPUFI/2 V1.1.6
5 SQL
A sample SQL file appears below:
-- Anything which follows double minus is treated as a
-- comment.
-- SQL statements are separated using semicolons;
-- Within SPUFI/2 V1.1.5 the length of SQL statements is
-- limited to 24000 bytes.
-- The SQL below demonstrates updating the SAMPLE database
--
select * -- print before
from staff
where dept = 10;
update staff -- update
set salary = salary * 1.05
where dept = 10;
select * -- print after
from staff
where dept = 10;
rollback; -- rollback
SPUIFI/2 will "Uppercase" all input, except where it is
between 'quotes' or where it is part of a comment.
Copyright ICE Computer Associates 1993 Page 13
December 1993 SPUFI/2 V1.1.6
6 COMMAND LINE PARAMETERS
/B Bright
When using screens with low contrast such as Gas Plasma or
LCD screens, the parameter /B may be used. The /B parameter
causes all text to be displayed in high intensity mode.
/T Timing
Version 1.1.0 of SPUFI/2 introduced timed queries. If the
/T runtime parameter is present then SPUFI/2 will time each
SQL query.
The results of the timing will be presented at the end of
the query in the form:
---------+---------+---------+---------+---------+
NUMBER OF ROWS DISPLAYED IS 250
STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
TIME TO FETCH FIRST ROW 7840 MSEC
TIME TO FETCH ALL ROWS 10280 MSEC
AVERAGE TIME TO FETCH EACH ROW 41 MSEC
Note: There will probably be a difference between the
times returned for the first and subsequent
consecutive executions of the same query. The
timings returned from a standalone machine are
reliable, repeatable and may be used for
performance comparisons. Care must be taken when
timing queries on a remote database. Such timings
will be effected by other concurrent queries and
other activities taking place on the remote
database server.
Note: If the /T parameter and the /B parameter are used
at the same time the two parameters must be
separated by a blank character.
e.g. /T /B
Copyright ICE Computer Associates 1993 Page 14
December 1993 SPUFI/2 V1.1.6
7 INSTALLATION
SPUFI/2 is supplied on a single 720K diskette or in a single
ZIP file SPUFI2.ZIP. The following files should be present
on the diskette or in the ZIP file.
SPUFI2.EXE
SPUFI2.BND
SPUFI_RR.BND
SPUFI_CS.BND
SPUFI_UR.BND
SPUFI2.ICO
README
Make a directory on your hard disk.
for example:
[C:\]MD SPUFI2
[C:\]CD SPUFI2
[C:\SPUFI2]
Copy the files from the diskette in drive A: to the new
directory.
[C:\SPUFI2]COPY A:\*.* C:
Or, if working from a ZIP file which you have placed
temporarily on a floppy in drive A:.
[C:\SPUFI2]PKUNZIP A:\SPUFI2.ZIP
Now add the program to an appropriate Desktop Manager group
or if using OS/2 Version 2.0 create a program object with
the following settings.
Program Title: SPUFI/2 from ICA
Path and File Name: C:\SPUFI2\SPUFI2.EXE
Parameters: See Section 6
Working Directory: C:\SPUFI2
Copyright ICE Computer Associates 1993 Page 15
December 1993 SPUFI/2 V1.1.6
8 SPUFI2.INI
When SPUFI2 is executed it attempts to read a file called
SPUFI2.INI from the Working Directory (see Section 7). If
no such file is found SPUFI2 will create it. SPUFI2.INI is
the file which holds the latest settings on the SPUFI2
panels. All panel settings are stored in SPUFI2.INI each
time you end SPUFI2.
The settings which are used when SPUFI2.INI is created are
as follows:
Input File "SAMPLE.SQL"
DataBase "SAMPLE"
Output File "SAMPLE.LST"
Change Defaults "NO"
Edit Input "YES"
Execute "YES"
AutoCommit "YES"
Browse Output "YES"
Isolation Level "RR"
Max Select Lines "250"
Editor "E"
Stop After Errors "1"
Append To Output "NO"
Max Numeric Field "20"
Max Character Field "80"
Column Heading "NAMES"
Commit / Rollback "COMMIT"
Copyright ICE Computer Associates 1993 Page 16
December 1993 SPUFI/2 V1.1.6
9 AUTOMATIC BINDING
Like any other program accessing a Database Manager
database, SPUFI/2 requires an access plan. An access plan
is created by the action of binding a program to a database.
Unlike most programs, which access only one database and
therefore require only one access plan, SPUFI/2 is required
to access as many different databases as the user has access
to. In order to enable access to these databases SPUFI/2
will automatically bind itself to any database which it is
required to access and for which no access plan exists.
Automatic binding will take place if the following
conditions are true:
The bind files: SPUFI2.BND, SPUFI_RR.BND, SPUFI_CS.BND,
SPUFI_UR.BND are present in the working directory.
AND
No access plan exists (SQL Code -805) and the user has
CONNECT and BINDADD authority on the database.
OR
You have installed an updated version of SPUFI/2 and
the existing access plan does not match the new version
of SPUFI/2 (SQL Code -818) and the user has CONNECT and
BIND authority on the database.
If you created the database yourself you will no doubt have
the required access, however, regardless of who created the
database, Database Manager databases are, by default,
created with CONNECT and BINDADD authority granted to
PUBLIC.
Copyright ICE Computer Associates 1993 Page 17
December 1993 SPUFI/2 V1.1.6
10 SUPPORT
SPUFI/2 is supported on CompuServe, please address messages
to David Joice [100025,350].
Copyright ICE Computer Associates 1993 Page 18
December 1993 SPUFI/2 V1.1.6
11 CHANGES IN VERSION 1.1.6
The fault which caused the name of the database to be
rejected with SQLCODE -1001, which showed up in V1.1.5 when
used with DB2/2 has been cured.
A "STOP USING DATABASE" command is issued at the end of each
query.
Copyright ICE Computer Associates 1993 Page 19