Transoft U/SQL User Guide - Test Page for Apache Installation

466 Pages·2007·3.58 MB·English

Transoft U/SQL
User Guide
Copyright © 2004-2007 Computer Software Group
This document is copyright and all rights are reserved. It may not, in whole or in
part, be copied, photocopied, reproduced, translated or reduced to any electronic
medium or machine readable form without prior permission in writing from
Transoft.
Transoft, part of Computer Software Group, reserves the right to make
changes in specifications and other information contained in this
document without prior notice.
Whilst Transoft endeavours to ensure the accuracy of the contents of this
document, no liability is accepted for any error or omission.
Transoft and e-volutionary solutions are either registered trademarks and/or
trademarks of Transoft in various countries throughout the world. All other
marks, names and logos may be the trademarks of their respective owners.
Transoft (Atlanta office)
1165 Northchase, Suite 375, Marietta, GA 30067, USA
Phone: [phone]
Fax: [phone]
Transoft (Dayton office)
7333 Paragon, Suite 250, Dayton, OH 45459, USA
Phone: [phone]
Fax: [phone]
Transoft (UK office)
Transoft House, 5J Langley Business Centre, Station Road, Langley, Slough, SL3
8DS, England
Phone: [phone]
Fax: [phone]
Please use e-mail to contact us wherever possible:
Americas: [email protected]
Rest of the World: [email protected]

Web site: http://www.transoft.com
http://www.computersoftware.com/
Table Of Contents
Introduction................................................................................................1
Installation and Licensing.............................................................................3
Installation and Licensing..........................................................................3
Installing the Multiple-Tier U/SQL Server.....................................................4
Server platforms supported.....................................................................4
Installing the UNIX U/SQL Server Software...............................................4
Installing the Windows NT U/SQL Server Software.....................................6
Uninstalling U/SQL Server on Windows NT Server......................................9
The Next Steps......................................................................................9
Installing the Single or Multiple-tier U/SQL client........................................10
System Requirements...........................................................................10
Contents of U/SQL Client Software.........................................................10
Installing the Client Software...................................................................12
De-installing the U/SQL Client Software..................................................18
The Next Step.....................................................................................19
Installing and Licensing Vista Business edition............................................20
Installing HTTP Server on Windows...........................................................24
U/SQL Client Browser on Windows.........................................................24
Getting started with the HTTP Server .....................................................26
Limitations..........................................................................................28
Installing HTTP Server on UNIX................................................................30
U/SQL HTTP Client Browser on UNIX......................................................30
Getting started with the HTTP Server .....................................................31
Limitations..........................................................................................33
Licensing...............................................................................................35
Licensing............................................................................................35
Installing the U/SQL Client License.........................................................36
Installing the U/SQL Server License........................................................41
Installing the Server License on UNIX Platforms.......................................42
Installing the Server License on Windows NT Server.................................47
Starting U/SQL with Licensing ...............................................................51
Unattended Client installation .....................................................................55
Unattended Client installation...................................................................55
Introduction........................................................................................55
How to perform Unattended (Silent) installations.....................................55
InstallShield - Common error codes..........................................................59
Configure and Use.....................................................................................61
ODBC Overview......................................................................................61
ODBC Overview...................................................................................61
i
Table Of Contents
ODBC Compliance................................................................................65
System and User Data Sources..............................................................71
File Data Sources.................................................................................72
Single-tier Administration........................................................................77
Installed Files......................................................................................77
U/SQL Administrator............................................................................78
Entering the ODBC.INI Directives...........................................................79
Multiple-tier Administration (Windows)......................................................82
U/SQL Server Installation on Windows....................................................82
U/SQL Service Manager........................................................................84
U/SQL Server Directives.......................................................................89
U/SQL Administrator Facilities ...............................................................91
Multiple-tier Administration (UNIX)...........................................................95
U/SQL Server Installation on UNIX.........................................................95
Starting and Stopping the UNIX Server...................................................98
Setting up the UNIX usqlsd.ini File.........................................................99
Micro Focus COBOL Specific Issues.......................................................103
Additional Information........................................................................104
Client Configuration..............................................................................105
U/SQL Client Installation Files..............................................................105
Interactive U/SQL Utilities...................................................................106
Win U/SQLi.......................................................................................107
usqli on UNIX Servers.........................................................................123
JDBC Client.......................................................................................133
INI Directives.......................................................................................134
ODBC.INI Directives...........................................................................134
UNIX Client-Side Directives.................................................................136
Configuration Section.........................................................................137
Data Source Defaults Section..............................................................140
Data Source Section...........................................................................145
Foreign Character Set Support.............................................................146
Advanced Directives...........................................................................147
Validation Rules for Security Directives.................................................155
COGNOS Impromptu Outer Join Directives............................................157
The USQLCS.LOG File and Error Reporting...............................................158
The USQLCS.LOG File and Log Levels...................................................158
Error Messages..................................................................................163
SQL Support........................................................................................167
SQL Syntax Supported........................................................................167
Sample Queries.................................................................................168
ii
Table Of Contents
READ_ONLY Views.............................................................................176
Limitations........................................................................................178
Transaction Processing.......................................................................179
Locking.............................................................................................182
Security...............................................................................................183
Security............................................................................................183
Multiple-tier Security - User Connection................................................184
GRANT and REVOKE Security...............................................................188
Query Planning.....................................................................................194
Query Planner...................................................................................194
Tuning the Query Planner ...................................................................199
Query Planner Hinting ........................................................................209
Sample Applications..............................................................................211
Demonstration - Books Wholesaler.......................................................211
Writing Applications Using U/SQL To Access Your Data...........................218
General UDD Information ......................................................................223
Overview..........................................................................................223
Creating a UDD..................................................................................225
Expression Handling...........................................................................229
Handling of Data Arrays......................................................................232
Limitations........................................................................................247
Specific UDD Information.......................................................................248
Setting Up a COBOL Data Dictionary ....................................................249
Setting Up a C-ISAM Data Dictionary....................................................352
Setting Up a Business BASIC Data Dictionary........................................370
Setting Up a U/FOS Data Dictionary.....................................................391
Enterprise Join Engine...........................................................................411
Enterprise Join Engine........................................................................411
Using the Enterprise Join Engine..........................................................413
Configuring the EJE on UNIX...............................................................420
Configuring Multi-Datasource U/SQL EJE on UNIX..................................422
Configuring U/SQL EJE Thin Client to Connect to the SQL Server..............426
Configuring U/SQL EJE for MS Access, MS Query or Crystal Reports.........430
Advanced Use......................................................................................432
Advanced Use of U/SQL Adapters.........................................................432
Multi-company Support.......................................................................433
Issues and Limits...............................................................................434
Accessing the UDD System Tables........................................................436
Hints and Tips...................................................................................440
Troubleshooting....................................................................................443
iii
Table Of Contents
Troubleshooting.................................................................................443
Appendices.............................................................................................445
Appendix A - Support Information...........................................................445
Appendix B - Sample License Form.........................................................446
Appendix C - U/FOS data type enhancements..........................................447
Appendix D - ACUCobol Configurable Variables.........................................448
Appendix E - MFOCUS Configurable Variables...........................................449
Printed Documentation.............................................................................451
Index.....................................................................................................453
iv
Introduction
Transoft U/SQL Adapters (U/SQL) provides direct Microsoft ODBC (open
Database Connectivity) access to your COBOL, C-ISAM, BASIC and other non-
relational data.
This enables you to use the widest possible variety of PC Windows products
without changing your code or exporting your data.
(cid:1)(cid:2) Office Systems
Create direct links from your data into spreadsheets or WP documents.
(cid:1)(cid:2) Report Writers and Business Intelligence Tools
Use the latest GUI-based reporting and decision support products, such as
Crystal Reports, Cognos Impromptu and IQ/Objects.
(cid:1)(cid:2) Windows-based Development Tools
Modernise your applications by selectively developing and integrating
modules in Microsoft Visual Basic, Delphi, PowerBuilder and so on.
The major elements of U/SQL are:
(cid:1)(cid:2) The ODBC driver whose settings are defined by the U/SQL Administrator.
(cid:1)(cid:2) The U/SQL Server, including optimized query planner.
(cid:1)(cid:2) Data source drivers that access non-relational data, including specific data
source drivers for ACUCOBOL Vision, Micro Focus Callable File Handler
(EXTFH), C-ISAM, Business Basic ISAM, U/FOS and so forth.
Using two-level data dictionary technology, U/SQL provides a 'relational view' of
your non-relational data. The Universal Data Dictionary (UDD) provides a
relational description of your data and the Universal File Dictionary (UFD)
describes the physical structure of the files being accessed.

The two-level data dictionary technology

1

Installation and Licensing
Installation and Licensing
This section describes how to install and license both Single-tier and Multiple-
tier U/SQL.
(cid:1)(cid:2) In the Single-tier model of U/SQL, both the Client and Server elements
reside on the same platform, for example, a Windows 2000 based PC.
(cid:1)(cid:2) In the Multiple-tier model of U/SQL, the U/SQL ODBC driver resides on the
client platform, which is connected via a LAN to the server platform, where
the U/SQL Server and Data Source Driver reside. For example, your client
platform could be a Windows 95 PC and your server platform a UNIX or
Windows NT Server machine.
The following sections describe:
(cid:1)(cid:2) Installing the Multiple-tier U/SQL server
(cid:1)(cid:2) Installing the Single or Multiple-tier U/SQL client.

3
Transoft U/SQL User Guide

Installing the Multiple-Tier U/SQL Server
Note: You must install the Multiple-tier U/SQL Server software first in order to
obtain a free TCP/IP socket port number. You need to enter this when you
subsequently install the U/SQL Client software.
The U/SQL Server software is available on many UNIX platforms for all data
sources and on Windows NT Server and Windows 2000 Server for selected data
sources including ACUCOBOL, Micro Focus COBOL and BBASIC ISAM.
This section covers:
(cid:1)(cid:2) Server platforms supported
(cid:1)(cid:2) Installing the U/SQL Server Software on UNIX
(cid:1)(cid:2) Installing the U/SQL Server Software on Windows NT.

Server platforms supported
The U/SQL Server software is available on various UNIX platforms for all Data
Source Drivers and on Windows NT Server 4 for selected data sources, including
ACUCOBOL, BBASIC ISAM, Micro Focus COBOL.
Contact your Transoft Account Manager for a complete list of platforms and data
sources supported.

Installing the UNIX U/SQL Server Software
Note:
(cid:1)(cid:2) If you are installing a new version of the U/SQL Server software into the
same directory as a previous version, ensure that the U/SQL Server
executable, usqlsd, is NOT running, otherwise it will not be replaced by
the new version.
(cid:1)(cid:2) If you are installing a release that has U/SQL HTTP Server (U/SQL 421
onwards) then include extra steps from the following document
Installing_HTTP_Server_on_UNIX
To install the U/SQL Server software, perform the following steps:
1. Create a base directory for the U/SQL Server software and use tar to copy
the files from the media. For example, to install the software in the
directory /usr/usqls, enter the following:
su : become superuser
cd /usr
mkdir usqls : make the usqls directory
cd usqls
2. Next, insert the first diskette or tape, in a suitable peripheral drive, and
then issue the tar copy command as either:
4