unisys Distributed Processing Middleware Enterprise Database SQL Query Processor for ClearPath MCP Installation and Operations Guide imagine it. done.

(1)

imagine it. done.

Distributed Processing Middleware

Enterprise Database

SQL Query Processor for ClearPath MCP

Installation and Operations Guide

ClearPath MCP 13.0

(2)

You should be very careful to ensure that the use of this information and/or software material complies with the laws, rules, and regulations of the jurisdictions with respect to which it is used.

The information contained herein is subject to change without notice. Revisions may be issued to advise of such changes and/or additions.

Notice to U.S. Government End Users: This is commercial computer software or hardware documentation developed at private expense. Use, reproduction, or disclosure by the Government is subject to the terms of Unisys standard commercial license for the products, and where applicable, the restricted/limited rights provisions of the contract data rights clauses.

(3)

Section 1. Overview

Documentation Updates ... 1–1 What’s New? ... 1–2 What Is SQL Query Processor? ... 1–3

Section 2. Installing SQL Query Processor

Hardware and Software Requirements ... 2–2 Hardware Requirements ... 2–2 MCP Environment Software Requirements ... 2–2 Windows Environment Software Requirements ... 2–3 Installing the SQL Query Processor Software in the MCP

Environment ... 2–3 Manually Assigning the SQL Query Processor

Library Function Names to the Server Library

Code File Titles ... 2–4 Creating the SQL Query Processor Configuration

File (DMSQL/CONFIG) ... 2–5 Changing the Name and Location of the SQL Query

Processor Configuration File ... 2–6 Creating the Server Configuration File

(DMSQL/SERVER/CONFIG) ... 2–6 Configuring the TCP/IP Distributed Systems

Services (DSS) ... 2–8 Optional Installation Tasks ... 2–9 Installing the SQL Query Processor Utilities in the Windows

Environment ... 2–11 Migrating from an Earlier Release... 2–12 Updating an SQL Database Definition ... 2–12 Updating an Enterprise Database Server Database ... 2–12 Removing the SQL Query Processor Software ... 2–13 Reinstalling the SQL Query Processor Software ... 2–15 Troubleshooting ... 2–16 Updating Enterprise Database Server Databases with SQL

Queries ... 2–22 Updating Enterprise Database Server Data Sets with SQL

Queries ... 2–22

(4)

Setting the Look and Feel of the Interface... 3–5 Determining Connection Status ... 3–5 Catalog Window ... 3–6 Opening Tables ... 3–9 Viewing Table Results ... 3–9 Viewing Column Results ... 3–10 Analyze Query Window ... 3–11 Analyze Query Window Commands ... 3–13 Preparing and Executing a Query ... 3–14 Saving a Query ... 3–14 Saving Query Results ... 3–15 Opening a Query ... 3–15 Enabling Manual Commit Mode ... 3–15 Setting Transaction Isolation Levels ... 3–16 Creating a Savepoint ... 3–17 Rolling Back to a Savepoint ... 3–17 Setting the Row Limit ... 3–17 Generating a QGraph... 3–18 Generating a QDump ... 3–19 Design Query Window ... 3–20 Designing a Query ... 3–22 Customizing the Query Design Center ... 3–24

Section 4. Using the Relational Design Center Utility

Starting the Relational Design Center ... 4–2 Understanding the Relational Design Center Interface ... 4–2 Using the Menu Bar ... 4–7 Understanding Relational Mappings for SQL ... 4–8 Default SQL Names ... 4–9 Global Data Defaults ... 4–10 Disjoint Data Set Defaults ... 4–10 Embedded Data Set and Occurring Item Defaults ... 4–14 Initial Values ... 4–22 Set and Access Defaults as Indexes ... 4–23 Set and Subset Defaults as Views ... 4–23 Data Item Defaults ... 4–23 Link Defaults ... 4–25 Group Defaults ... 4–25 Physical Option Defaults ... 4–25 Restart Data Set Defaults... 4–25 SQL Referential Integrity Constraints ... 4–25 Date Construct ... 4–26 Security ... 4–28 View Construct ... 4–29 Grant Construct ... 4–29 Working with Servers ... 4–30 Adding a Server ... 4–30 Removing a Server ... 4–30 Importing a Relational Schema ... 4–31 Creating an SQL Database ... 4–32

(5)

Working with Databases ... 4–34 Opening a Database ... 4–34 Removing a Database ... 4–35 Refreshing a Database ... 4–35 Restoring a Database ... 4–36 Getting the SQL Schema ... 4–36 Saving the Schema ... 4–36 Loading a Schema ... 4–37 Applying Schema Modifications ... 4–37 Modifying an SQL Database ... 4–37 Working with Database Schemas ... 4–39 Modifying Data Sets ... 4–39 Modifying Occurring Items ... 4–40 Modifying Variable-Format Items ... 4–40 Modifying Indexes (Sets and Subsets) ... 4–41 Modifying Data Items (Columns and Primary Keys) ... 4–41 Assigning Access Rights to Database Structures ... 4–42 Modifying Access Rights for Database Structures... 4–43 Creating SQL Dates ... 4–44 Modifying SQL Dates ... 4–45 Creating Integrity Constraints ... 4–45 Modifying Integrity Constraints ... 4–46 Creating SQL Views ... 4–47 Modifying SQL Views ... 4–47

Section 5. SQL Query Processor Server Administration

Configuring SQL Query Processor Server Resources ... 5–2 Preparing a Database to Be a Resource ... 5–4 Controlling the SQL Query Processor Server ... 5–5

Starting and Stopping the SQL Query Processor

Server ... 5–5 Obtaining Help Information ... 5–6 Automating SQL Query Processor Server Initialization ... 5–7 Creating the *DMSQL/SERVER/CONFIG File ... 5–7 Setting the SQL Query Processor Server Timeout Intervals ... 5–9 Obtaining SQL Query Processor Server Status ... 5–10 Viewing Server Log Information in the MCP Environment ... 5–11 Identifying the Server Log ... 5–11 Understanding Server Log Contents ... 5–12 Viewing Trace Information on the Server ... 5–13 Diagnostic Trace ... 5–13 Identifying the Trace File ... 5–13 Enabling the Trace Facility ... 5–13 Closing Trace Files Automatically ... 5–14 Retrieving Dump Files ... 5–15 Identifying the Dump File ... 5–15 Displaying the Dump File ... 5–15

(6)

Section 6. SQL Query Processor Configuration Control Facility

Using the SQL Query Processor Configuration Control Facility ... 6–2 Understanding the SQL Query Processor Configuration File ... 6–3 Creating the SQL Query Processor Configuration File ... 6–4 Release Specification ... 6–5 User Specification ... 6–7 Program Specification ... 6–8 Default Release Specification ... 6–10 Resource Specification ... 6–11 Using the CCS Clause... 6–13 Using the Limit Clause... 6–13 Setting a Default Release Limit ... 6–13 Limit Clause Enforcement ... 6–14 Sample SQL Query Processor Configuration Files ... 6–16

Section 7. Using the Schema Administrator

Ensuring Description File and Control File Accessibility ... 7–2 Reviewing the Results File of a SQLVIEW Command

Generation ... 7–3 Viewing the Schema ... 7–3 Using the Schema Administrator Input File ... 7–4 Manually Enabling SQL Access to an Enterprise Database

Server Database ... 7–5 Generating the SQL Descriptions ... 7–6 Listing the Description ... 7–8

Section 8. Using the DMQUERY Utility

What Is the DMQUERY Utility? ... 8–1 Using DMQUERY Utility Commands ... 8–2 BYE ... 8–2 CANCEL ... 8–2 CLOSE ... 8–2 COMMIT WORK ... 8–2 DEFINE ... 8–3 DIAGNOSE ... 8–3 DO ... 8–3 OPEN ... 8–3 PARSE ... 8–3 QD ... 8–4 REPEAT ... 8–4 ROLLBACK WORK ... 8–4 SAVEPOINT ... 8–4 SET ... 8–4 SHOW ... 8–6 Running the DMQUERY Utility Interactively Through CANDE ... 8–7 Running the DMQUERY Utility in Batch Mode ... 8–7 DMQUERY Utility Files ... 8–9

(7)

Appendix A. Setting Up Kerberos Authentication in the Windows Environment in the Windows Environment

Appendix B. Troubleshooting

(8)

(9)

Purpose

This guide explains how to install and administer the SQL Query Processor for ClearPath MCP, including the Windows-based utilities Query Design Center and Relational Design Center.

Audience

This guide is intended for the experienced database administrator (DBA) who understands the Unisys Enterprise Database Server for ClearPath MCP, Windows operating systems, and the SQL specification.

Terminology Conventions

In this guide, the term ClearPath MCP servers refers to ClearPath LX and CS servers, and FS and Libra Series servers.

For simplicity, the name of the SQL Query Processor for ClearPath MCP product is shortened to SQL Query Processor and is referred to as such throughout this guide.

Documentation Updates

This document contains all the information that was available at the time of publication. Changes identified after release of this document are included in problem list entry (PLE) 18716747. To obtain a copy of the PLE, contact your Unisys representative or access the current PLE from the Unisys Product Support Web site:

http://www.support.unisys.com/all/ple/18716747

(10)

What’s New?

The following table identifies new and revised information for this release.

New or Revised Information Location in Guide The database types with which you can work

in the Relational Design Center is updated.

Section 4 “Using the Relational Design Center”

The procedure for opening a database in the Relational Design Center is updated.

The effect of using the refresh database operation in the Relational Design Center is updated.

The procedure for restoring a database in the Relational Design Center is updated.

The procedure for assigning access rights to database structures in the Relational Design Center is updated.

The procedure for modifying access rights to database structures in the Relational Design Center is updated.

The procedure for creating SQL views in the Relational Design Center is updated.

The procedure for modifying SQL views in the Relational Design Center is updated.

(11)

What Is SQL Query Processor?

The SQL Query Processor is a Structured Query Language (SQL) parser, optimizer, and query execution engine. The SQL Query Processoraccepts and executes SQL

commands according to a chosen plan and interacts with the Enterprise Database Server storage engine to return the expected results.

The optimizer chooses the fastest execution plan. In the optimization phase, the SQL Query Processor chooses

• Any indexes to use

• The order in which joins are executed

• The order in which constraints (such as WHERE clauses) are applied • The algorithms that lead to the best performance

The query execution engine executes the query plan chosen during query optimization. The execution engine also determines the algorithms available to the query optimizer. For example, SQL Query Processor implements an indexed-loops join algorithm. The SQL Query Processor enables you to

• Access business-critical data stored in the highly robust and scalable Enterprise Database Server using standard SQL commands from JDBC, COBOL, or ALGOL applications.

• Create SQL databases that can be queried using the SQL data manipulation language (DML)

• Build, deploy, and manage applications on your ClearPath MCP server that are secure, scalable, and reliable.

The SQL Query Processor is compliant with the SQL-92, entry level with extensions, data manipulation language (DML) as defined by the American National Standards Institute (ANSI) document X3.135-1992, “Database Language SQL.” The SQL Query Processor provides an ALGOL and a COBOL call-level application program interface (API) that conforms to a subset of the SQL-99 call-level interface (CLI). Administration tools are provided to

• Create a relational view of an existing Enterprise Database Server database. • Create an SQL database based on an SQL data definition language (DDL).

(12)

Benefits of Using the SQL Query Processor

The SQL Query Processor provides the following benefits: • Reduced network traffic

The SQL Query Processor runs entirely on the ClearPath MCP server. This feature eliminates any network traffic that would result from switching between the ClearPath MCP server and a Windows or other server.

• Scalability

The SQL Query Processor is scalable and supports multiple user sessions at the same time.

• Simplified enterprise development

Using the COBOL or ALGOL API or the JDBC interface, you can easily develop applications to interface to the Enterprise Database Server.

• Comprehensive software localization

International customers are able to interact with the SQL Query Processor and its tools on all levels, in their own language, and using their own cultural conventions. Localization entails translation of all dialog boxes, on-line help, error messages, and other user interface text.

SQL Query Processor supports various character sets and collation based on the Unisys CCSVERSION defined for the Enterprise Database Server database. Using the SQL Query Processor administration tools, you can specify the coded character set (CCS) to be used when connecting to the database and when translating database data and identifiers to and from Unicode.

Components and Capabilities

The SQL Query Processor provides the following components and capabilities: • Query Design Center

The Query Design Center is an interactive query tool that developers and system administrators can use to issue queries against the Enterprise Database Server. This tool makes it easy to develop and design queries against Enterprise Database Server databases and test them outside of user applications.

• Relational Design Center

The Relational Design Center is an interactive schema administration tool that enables SQL access to Enterprise Database Server databases. Database administrators can import, define, and save schema modifications. Schema administration functions include

− Generating an SQL schema for Enterprise Database Server databases

− Defining and modifying schema attributes to be used for mapping an Enterprise Database Server description file

(13)

• Call-Level Interface (CLI)

The CLI is a single, open SQL API that enables a client application on a ClearPath MCP system to access Enterprise Database Server databases. CLI interfaces include JDBC, COBOL, and ALGOL.

• Module language

The module language supports the SQL module language interfaces. The module language enables you to partition and centralize code and develop routines that can be called by other applications.

(14)

(15)

This section provides information on the following topics: • Hardware and software requirements

• Installing SQL Query Processor software in the MCP environment • Installing SQL Query Processor utilities in the Windows environment • Migrating from an earlier release

• Removing the SQL Query Processor software • Reinstalling the SQL Query Processor software • Troubleshooting

• Requirements for updating Enterprise Database Server databases with SQL queries • Requirements for updating Enterprise Database Server data sets with SQL queries

(16)

Hardware and Software Requirements

As you consider the hardware and software requirements, note the following factors: • Processor size and the amount of available memory affect performance.

• The available memory and disk space must be sufficient for the demands of your operating environment, the SQL Query Processor software, and the client applications using the services of the SQL Query Processor.

Hardware Requirements

Platform Hardware Requirements

MCP environment One of the following MCP systems:

• ClearPath LX server

• ClearPath NX server

• ClearPath CS server

• FS Series servers

• Libra Series servers

Windows environment • Intel Pentium processor, 2.0 GHz or greater

• 256 MB of RAM or greater

• 4 MB of hard disk space or greater

MCP Environment Software Requirements

Software Requirements

MCP ClearPath MCP release 11.0 or later

Refer to the compatibility matrix on the Unisys Product Support Web site:

http://www.support.unisys.com/common/ascompat.cfm? pla=AS&nav=AS

Enterprise Database Server

Enterprise Database Server release 10.1 or later Refer to the compatibility matrix on the Unisys Product Support Web site:

http://www.support.unisys.com/common/ascompat.cfm? pla=AS&nav=AS

TCP/IP Network Services release 10.0 or later

DALICENSESUPPORT The same version as the SQL Query Processor software or later

(17)

Windows Environment Software Requirements

Software Requirements

Windows operating system

One of the following operating systems:

• Windows Server 2003

• Windows 2000 or XP Professional SQL Query Processor • Relational Design Center

• Query Design Center

Other software Java Runtime Environment (JRE) 1.4 or greater

Installing the SQL Query Processor Software in the

MCP Environment

Notes:

• On new ClearPath MCP servers, the MCP host software, including the SQL Query Processor product, comes preloaded. Unless you are installing an Interim Correction (IC) update to the SQL Query Processor, you do not need to perform an installation in the MCP environment.

• For this installation process, the MCP environment is considered to be a ClearPath MCP server, unless otherwise stated.

For the MCP environment, the SQL Query Processor software, including the utilities, is packaged on CD-ROM media.

Run the Simple Installation (SI) program or use the Installation Center to install the SQL Query Processor software package. The following files are installed on the MCP environment:

SYSTEM/SQL/ADMIN

SYSTEM/SQL/ADMIN/DIAGNOSTICS SYSTEM/SQL/ETINQ

SYSTEM/SQL/ETINQ/DIAGNOSTICS SYSTEM/SQL/SCODE

SYSTEM/SQL/SCODE/DIAGNOSTICS SYSTEM/SQL/CONFIG

SYSTEM/SQL/CONFIG/DIAGNOSTICS SYSTEM/SQL/DRIVER

SYSTEM/SQL/DRIVER/DIAGNOSTICS SYSTEM/SQL/PARSER

SYSTEM/SQL/PARSER/DIAGNOSTICS SYSTEM/SQL/SERVER

SYSTEM/SQL/SERVER/DIAGNOSTICS SYSTEM/SQL/WORKER

(18)

SYSTEM/SQL/DMQUERY/DIAGNOSTICS SYSTEM/SQL/MODLANG

SYSTEM/SQL/MODLANG/DIAGNOSTICS SYSTEM/SQL/SUPPORT

SYSTEM/SQL/SUPPORT/DIAGNOSTICS SYSTEM/SQL/COBOLDESC

SYSTEM/SQL/COBOLDESC/DIAGNOSTICS SYSTEM/SQL/INTERFACE

SYSTEM/SQL/INTERFACE/DIAGNOSTICS SYSTEM/SQL/DMSIIMAPPER

SYSTEM/SQL/DMSIIMAPPER/DIAGNOSTICS SYSTEM/SQL/FILESUPPORT

SYSTEM/SQL/FILESUPPORT/DIAGNOSTICS SYSTEM/SQL/SCODESUPPORT

SYSTEM/SQL/SCODESUPPORT/DIAGNOSTICS SYMBOL/SQL/CLI/PROPERTIES/ALGOL SYMBOL/SQL/CLI/PROPERTIES/COBOL

SYSTEM/INSTALLS/DMSQLUTILITIES/"README.TXT" SYSTEM/INSTALLS/DMSQLUTILITIES/"DMSQLUTILS.MSI" EXAMPLE/SQL/CLI/ALGOL

EXAMPLE/SQL/CLI/COBOL EXAMPLE/DMSQL/CONFIG

DESCRIPTION/SQLDIR/DMSQL-CATALOG

For information about the SI program, refer to the Simple Installation Operations Guide. For information about Installation Center, refer to the Installation Center Operations Guide.

Manually Assigning the SQL Query Processor Library Function

Names to the Server Library Code File Titles

When you use the SI program to install the SQL Query Processor in the MCP environment, four SL assignments are automatically performed. The SQL Query Processor requires these assignments to run in the MCP environment.

To perform the SL assignments manually, ensure that you have system user privileges and enter the following SL (Support Library) system commands:

SL DMSQLSERVER = *SYSTEM/SQL/SERVER ON <pack name>: LINKCLASS = 1, TRUSTED SL DMSQLCONFIG = *SYSTEM/SQL/CONFIG ON <pack name>

SL DMSQLSCODE = *SYSTEM/SQL/SCODE ON <pack name>

SL DMSQLCLI= *SYSTEM/SQL/INTERFACE ON <pack name>: TRUSTED

The <pack name> construct is the name of the pack to which you copied the code file. The : LINKCLASS = 1, TRUSTED and the TRUSTED clause are required.

(19)

Creating the SQL Query Processor Configuration File

(DMSQL/CONFIG)

During installation of the SQL Query Processor on the ClearPath MCP host, you use CANDE to create the SQL Query Processor configuration file called DMSQL/CONFIG under the same usercode and on the same pack as the *SYSTEM/SQL/CONFIG code file. The information in the configuration file is used by the system to determine the version of the SQL Query Processor software to which a particular user has access. Perform the following steps to create the configuration file:

1. Use the EXAMPLE/DMSQL/CONFIG file as a template. This file is included with the SQL Query Processor software.

2. Copy the file EXAMPLE/DMSQL/CONFIG as DMSQL/CONFIG under your usercode. 3. Update the following sections in the DMSQL/CONFIG file to reflect your

environment: RELEASE

RELEASE DIAGNOSTICS USER

DEFAULT RESOURCE

Refer to Section 6, “SQL Query Processor Configuration Control Facility,” for details on configuring these items to fit your environment.

4. Save the updated example file as *DMSQL/CONFIG ON DISK.

Note: _{If you want to save the configuration file under another name and location,}

refer to “Changing the Name and Location of the SQL Query Processor Configuration File” later in this section.

5. From CANDE, issue the following statement to verify the accuracy of the syntax in the DMSQL/CONFIG file:

RUN *SYSTEM/SQL/CONFIG ON <pack name>

If the contents of the file are valid, a message similar to the following is displayed: #RUNNING 8864

> The DMSQL/CONFIG file just validated is: > *DMSQL/CONFIG ON HL511D0 10/11/2005 16:03:05 > No errors.

#ET=0.1 PT=0.0 IO=0.1@@@@@

If errors are returned, refer to Section 6, “SQL Query Processor Configuration Control Facility,” to determine how to correct the errors.

(20)

Changing the Name and Location of the SQL Query Processor

Configuration File

You can change the name and disk location of the SQL Query Processor configuration file. By default, the name and location of the configuration file is DMSQL/CONFIG ON <pack name>. The variable <pack name> identifies the pack where the SQL Query Processor is installed.

The default usercode and family are the same as those in the SYSTEM/SQL/CONFIG file title. That is, if the SQL Query Processor is installed under the asterisk (*) usercode on the family named PACK, the default configuration file title is *DMSQL/CONFIG ON PACK.

Perform the following steps to modify the name and location of the SQL Query Processor configuration file:

1. From CANDE, enter the following statement:

WFL MODIFY *SYSTEM/SQL/CONFIG ON <pack name>;FILE CONFIG=<new configurati on file name> ON <pack name>

2. Enter the following SL (Support Library) system command: SL DMSQLCONFIG = *SYSTEM/SQL/CONFIG ON <pack name>

Creating the Server Configuration File

(DMSQL/SERVER/CONFIG)

During installation of the SQL Query Processor server on the ClearPath MCP host, you use CANDE to create the SQL Query Processor server configuration file called

DMSQL/SERVER/CONFIG under the same usercode and on the same pack as the *SYSTEM/SQL/SERVER code file.

The server configuration file specifies TCP/IP attributes and the distributed systems services (DSS) provider name. The file is optional, and if it is not installed or is not found, all attributes take on their default values as defined in the following text.

The syntax for defining these attributes and the SQL Query Processor name follows: DD TCPIP DEDDDDDDDDDDDDDDDEDEDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDEDDDDDDDDDDDF

GD<port number>DI GD : DD<timeout specification>DI

FDEDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDEDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDF GD , DD DSSPROVIDER DD<provider name>DI

FDEDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDEDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDJ GD , DD MAXBUFFSIZE DD<integer>DI

<timeout specification>

DD<BLOCKEDTIMEOUT value>DEDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDEDDDDDDDDDDJ GD : DD<DIALOGCHECKINTERVAL value>DI

(21)

The following table explains the elements of the syntax diagrams.

Element Definition

<port number> Identifies the port number of the SQL Query Processor server. The default port number is 1897. The port number must also be defined in the DSS DMSQL_TCPIP endpoint and in either the connection attributes file or the connection defaults file.

<BLOCKEDTIMEOUT value> Specifies the length of time, in minutes, that the connection between the workstation and the server stays open after the workstation has stopped responding to inactivity handshaking procedures. This value must be an integer. The default BLOCKEDTIMEOUT value is 10 minutes.

<DIALOGCHECKINTERVAL value> Specifies the length of time, in minutes, that the server waits without a response from an attached system before it automatically invokes inactivity handshaking procedures. This value must be an integer.

The default DIALOGCHECKINTERVAL value is 5. A value of 0 (zero) prevents inactivity

handshaking procedures from being invoked. <provider name> Specifies the name of the DSS provider that

provides the SQL Query Processor DSS. The default DSS provider name is DMSQLPROV. <integer> Identifies the buffer size in bytes. The default

(22)

Configuring the TCP/IP Distributed Systems Services (DSS)

The SQL Query Processor Windows-based utilities use a TCP/IP connection to the ClearPath MCP server. This connection is managed as a distributed systems service (DSS).

You can manually configure the DSS connection using the following ClearPath MCP commands. However, if you use the Simple Installation (SI) program, the DSS connection is automatically configured, and it is not necessary to enter the following commands.

Note: You must be a system user to run the following commands. NA REG ADD PROVIDER DMSQLPROV

DSS = DMSQL,

SUPPORTLIBRARY = DMSQLSERVER,

TASK TYPE = NONMONITOREDSUPPORTLIBRARY, INTERFACE = MESSAGE;

NA REG ADD DSS DMSQL ENDPOINT = DMSQL_TCPIP, CLASS = OTHER,

INITIALIZE = TRUE, RECOVER = FALSE; NA REG ADD EP DMSQL_TCPIP FILENAME = DMSQL_TCPIP, MYNAME = "1897",

PUBLIC = TRUE;

You can also copy the preceding commands to a ClearPath MCP sequential data file and load them using the following command:

(23)

Optional Installation Tasks

As part of the SQL Query Processor product installation, you can optionally direct the MCP host to use a pack other than the pack on which the SQL Query Processor is installed for

• The logging and tracing capabilities of the SQL Query Processor server

By default, the system writes requested log and trace files to the pack on which the *SYSTEM/SQL/SERVER code file resides.

• The location of the *DMSQL/SERVER/CONFIG file

By default, the system looks for the server configuration file

(*DMSQL/SERVER/CONFIG) on the pack on which the *SYSTEM/SQL/SERVER code file resides.

Perform the following steps to change the location of the log or trace files, or the *DMSQL/SERVER/CONFIG file:

1. Stop the SQL Query Processor server by entering the following system command: NA DMSQL QUIT NOW

2. Remove the function name specification for DMSQLSERVER by entering the following system command:

SL - DMSQLSERVER

3. Execute the following commands:

WFL MODIFY *SYSTEM/SQL/SERVER ON <pack name>; FILE LOG_FILE (FAMILYNAME = <pack name>); FILE TRACE_FILE (FAMILYNAME = <pack name>);

FILE DMSQL_SERVER_CONFIG (FAMILYNAME = <pack name>);

4. Map the function name DMSQLSERVER to the SQL Query Processor server code file by entering the following SL (Support Library) system command:

SL DMSQLSERVER = *SYSTEM/SQL/SERVER ON <pack name>:LINKCLASS = 1, TRUSTED The pack name represents the name of the pack to which you copied the code file *SYSTEM/SQL/SERVER.

The :LINKCLASS = 1, TRUSTED clause of the preceding command is required. 5. Type the following command to start the SQL Query Processor server software:

(24)

6. Type the following command to display the status of the SQL Query Processor connection:

NA DMSQL STATUS

The status report includes the following information. • DSS name is DMSQL.

• DSS provider name is DMSQLPROV. • TCP/IP options are as follows:

− Using port is 1897.

− BlockedTimeout value is 10 min. − DialogCheckInterval value is 5 min.

(25)

Installing the SQL Query Processor Utilities in the

Windows Environment

After the release media is installed on the ClearPath MCP server, the SQL Query

Processor installation package is accessible from the Windows environment on the MCP share called INSTALLS.

Note: _{If you do not have the INSTALLS share on your ClearPath MCP server, you must}

set up the share using the Administration Center. (Refer to the Administration Center Help.)

The preferred installation method is through MCP Installation Assistant. However, you can install the SQL Query Processor directly from the \DMSQLUtilities folder on the INSTALLS share.

Installing by Using MCP Installation Assistant

Perform the following steps to install the SQL Query Processor using MCP Installation Assistant:

1. From the Windows environment, map a network drive to the INSTALLS share on your MCP server.

2. Double-click the file setup.exe. MCP Installation Assistant appears.

3. Expand the Database Query and Reporting group.

4. Select the SQL Query Processor Utilities for ClearPath MCP check box.

Note: _{By default, Installation Assistant installs only the Query Design Center. To}

install both utilities, right-click the product name and click Install Options, select the Relational Design Center check box, and click Apply. You can also right-click the product name to view the readme file.

5. Click Next. 6. Click Install.

The SQL Query Processor software is installed in the following path on the Windows environment:

Program Files\Unisys\MCP\DMSQLUtilities

Note: _{The Installation Assistant automatically installs the Java Runtime}

Environment (JRE) if you do not have the JRE version 1.4 or later installed. Refer to the MCP Implementation Guide for additional details on MCP Installation Assistant.

(26)

Installing by Using the SQL Query Processor .msi File

Perform the following steps to install the SQL Query Processor directly from the .msi file in the \DMSQLUtilities folder on the INSTALLS share:

1. Ensure that the Java Runtime Environment (JRE) version 1.4 or later is installed. If not, install it from the INSTALLS share under the Redist\SUN folder.

2. From the Windows environment, map a network drive to the INSTALLS share on your ClearPath MCP server.

3. Double-click the INSTALLS folder. 4. Double-click the DMSQLUtilities folder. 5. Double-click the DMSQLUtils.msi file.

An installation wizard is initiated.

6. Follow the instructions on the screen. Be sure to select the appropriate options for your type of installation.

Note: When installing using the .msi file, you can choose to perform a complete or a custom installation. The complete setup installs both utilities. To install only one utility, select Custom. On the Custom Setup page, select Query Design Center (the default in a custom setup) or Relational Design Center, and then click Next.

Migrating from an Earlier Release

The format of the catalog file has changed to accommodate new functionality. Therefore, if you have an earlier release of the SQL Query Processor software installed, you must update your SQL database definition or Enterprise Database Server database on which the SQLVIEW command was processed.

Updating an SQL Database Definition

To update your SQL database definition to the current release of SQL Query Processor, run the Schema Administrator with the UPDATE command. You can also use the UPDATE command to update an SQL database to the current level of Enterprise Database Server software.

Updating an Enterprise Database Server Database

Perform the following steps to update an Enterprise Database Server database that was previously generated with the SQLVIEW command:

1. On the ClearPath MCP server, remove the file named DESCRIPTION/<database name>/DMSQL-CATALOG.

(27)

2. Run the following command if you made changes to the SQL schema (such as renaming a column or table) and want to preserve those changes:

RUN *SYSTEM/SQL/ADMIN ("SQLVIEW DATABASE <database name> :ACCESSCONTROL=UPDATEOK");

FILE CARD=DDLRESULTS/<database name>/SEMANTIC

Note: _{If the database must be mapped for read-only access, use the}

ACCESSCONTROL = INQUIRYONLY clause or omit the ACCESSCONTROL declaration. For further details, refer to Section 7, “Using the Schema Administrator.”

3. Start the Relational Design Center, remove the database name from the tree view, and reimport the database.

Removing the SQL Query Processor Software

MCP Environment

Removing the SQL Query Processor software from the MCP environment involves deleting the server library, worker code files, DMSQL/CONFIG file, and the DSS entities. Perform the following steps to remove the SQL Query Processor software from the MCP environment:

1. Stop the SQL Query Processor server by entering the following system command: NA DMSQL QUIT NOW

2. Remove the function name specification for DMSQLSERVER by entering the following system commands:

SL - DMSQLSERVER SL - DMSQLCONFIG SL - DMSQLSCODE SL - DMSQLCLI

3. Delete the DSS entities by entering the following system commands: NA REG DEL PROV DMSQLPROV

NA REG DEL DSS DMSQL NA REG DEL EP DMSQL_TCPIP

(28)

4. Remove the SQL Query Processor server files by entering the following system commands:

REM *SYSTEM/SQL/ADMIN ON <pack name>

*SYSTEM/SQL/ADMIN/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/ETINQ ON <pack name>

*SYSTEM/SQL/ETINQ/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/SCODE ON <pack name>

*SYSTEM/SQL/SCODE/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/CONFIG ON <pack name>

*SYSTEM/SQL/CONFIG/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/DRIVER ON <pack name>

*SYSTEM/SQL/DRIVER/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/PARSER ON <pack name>

*SYSTEM/SQL/PARSER/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/SERVER ON <pack name>

*SYSTEM/SQL/SERVER/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/WORKER ON <pack name>

*SYSTEM/SQL/WORKER/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/DMQUERY ON <pack name>

*SYSTEM/SQL/DMQUERY/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/MODLANG ON <pack name>

*SYSTEM/SQL/MODLANG/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/SUPPORT ON <pack name>

*SYSTEM/SQL/SUPPORT/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/COBOLDESC ON <pack name>

*SYSTEM/SQL/COBOLDESC/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/INTERFACE ON <pack name>

*SYSTEM/SQL/INTERFACE/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/DMSIIMAPPER ON <pack name>

*SYSTEM/SQL/DMSIIMAPPER/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/FILESUPPORT ON <pack name>

*SYSTEM/SQL/FILESUPPORT/DIAGNOSTICS ON <pack name> *SYSTEM/SQL/SCODESUPPORT ON <pack name>

*SYSTEM/SQL/SCODESUPPORT/DIAGNOSTICS ON <pack name> *SYMBOL/SQL/CLI/PROPERTIES/ALBOL ON <pack name> *SYMBOL/SQL/CLI/PROPERTIES/COBOL ON <pack name> *EXAMPLE/SQL/CLI/ALGOL ON <pack name>

*EXAMPLE/SQL/CLI/COBOL ON <pack name> *EXAMPLE/DMSQL/CONFIG ON <pack name>

*DESCRIPTION/SQLDIR/DMSQL-CATALOG ON <pack name> *DMSQL/SERVER/CONFIG ON <pack name>

*DMSQL/LOG/= ON <pack name> *DMSQL/TRACE/= ON <pack name> *DMSQL/CONFIG ON <pack name>

(29)

Windows Environment

The Windows uninstall program removes the SQL Query Processor software only. The program does not remove the log, trace, and schema files, which you can remove manually.

Perform the following steps to uninstall the SQL Query Processor software from your Windows environment:

1. Double-click the Add or Remove Programs icon on Control Panel. The Add or Remove Programs Properties dialog box is displayed. 2. Select Unisys SQL Query Processor Utilities for ClearPath MCP. 3. Click OK.

The uninstall program begins and removes the SQL Query Processor software.

Reinstalling the SQL Query Processor Software

MCP Environment

To reinstall the SQL Query Processor software in the MCP environment, follow the directions explained earlier in this section under the heading “Installing the SQL Query Processor Software in the MCP Environment.”

Windows Environment

You can reinstall the SQL Query Processor software by repeating the installation procedure. However, it is recommended that you uninstall the product first using the following procedure to ensure trouble-free operation after you have reinstalled the product:

1. Uninstall the SQL Query Processor software. Refer to the topic “Removing the SQL Query Processor Software” earlier in this section.

2. Install the SQL Query Processor software as outlined under “Installing the SQL Query Processor Utilities in the Windows Environment” earlier in this section.

Note: _{The server maintains your previous configuration when you reinstall the SQL}

(30)

Troubleshooting

The following procedures can help you determine if the SQL Query Processor server installation completed correctly. These procedures are not required. If you had any problems during the installation process, or if you want to confirm that all components of the SQL Query Processor server were installed, you can run some or all of the

troubleshooting procedures.

Verifying Release Identifiers

Action

To verify the installation of the most recent support libraries, enter the following CANDE commands:

FAM

LFILES *SYSTEM/SQL ON SYSNEW :RELEASEID

LFILES *DESCRIPTION/SQLDIR/DMSQL-CATALOG :RELEASEID

Result

The following number of files are returned.

File Type Number Returned

*SYSTEM/SQL 32

*DESCRIPTION/SQLDIR/DMSQL-CATALOG 1

Solution

If an incorrect number of files are returned or if the RELEASEID attribute of the files is not correct, perform the following procedures.

If . . . Then . . .

You are installing files from an Interim Correction (IC) tape

Recopy the appropriate files from the IC tape.

You are installing the base release Complete the following steps: 1. Run the Simple Installation (SI)

program.

2. Ensure the product identifier is SQL. 3. Specify the appropriate release

identifier.

Verifying the SQL Query Processor Configuration System Library

Action

(31)

SL *DMSQLCONFIG

Result

The following statement is displayed:

SL DMSQLCONFIG = *SYSTEM/SQL/CONFIG ON SYSNEW

Solution

If the DMSQLCONFIG statement points to another file or if the SQL Query Processor configuration system library function does not exist, enter the following ODT command:

SL DMSQLCONFIG = *SYSTEM/SQL/CONFIG ON SYSNEW

Verifying the SCode Compiler System Library

Action

Enter the following ODT command: SL * DMSQLSCODE

Result

SL DMSQLSCODE = *SYSTEM/SQL/SCODE ON SYSNEW

Solution

If the DMSQLCODE statement points to an incorrect file or if the Scode compiler system library function does not exist, enter the following ODT command:

(32)

Verifying the SQL Driver System Library

Action

Enter the following ODT command: SL *DMSQLCLI

Result

SL DMSQLCLI = *SYSTEM/SQL/INTERFACE/ ON SYSNEW TRUSTED

Solution

If the DMSQLCLI statement points to another file, if the TRUSTED parameter is missing, or if the SQL driver system library function does not exist, enter the following ODT command:

SL DMSQLCLI = *SYSTEM/SQL/INTERFACE ON SYSNEW :TRUSTED

Verifying the Existence and Location of the SQL Query Processor

Configuration File

Action

Enter the following CANDE command: RUN $SYSTEM/SQL/CONFIG ON SYSNEW

Result

The following text is displayed: #RUNNING 8864

> The DMSQL/CONFIG file just validated is: > *DMSQL/CONFIG ON HL511D0 10/11/2005 16:03:05 > No errors.

#ET=0.1 PT=0.0 IO=0.1@@@@@

Solution

If the SQL Query Processor configuration file does not exist, refer to “Creating the SQL Query Processor Configuration File (DMSQL/CONFIG)” earlier in this section for

instructions on creating an SQL Query Processor configuration file.

If the SQL Query Processor configuration file does exists, but syntax errors occur, refer to Section 6, “SQL Query Processor Configuration Control Facility,” for instructions about creating an SQL Query Processor configuration file.

(33)

Verifying the Contents of the SQL Query Processor Configuration

File

Action

After verifying the existence and location of the SQL Query Processor configuration file, enter the following command to display the contents of the SQL Query Processor configuration file:

LIST *DMSQL/CONFIG ON SYSNEW

Result

The exact text of your SQL Query Processor configuration file might differ, but ensure the following information is contained in your file:

RELEASE MYSQL (PACK = SYSNEW)

Solution

If the RELEASE or VIEWCONNECTION option is missing from your SQL Query Processor configuration file, modify your SQL Query Processor configuration file to include these options. For detailed information about the SQL Query Processor configuration file, refer to Section 6, “SQL Query Processor Configuration Control Facility.”

Verifying the SQL Query Processor Server Code File

Action

Enter the following ODT command: SL *DMSQLSERVER

Result

The following text is displayed:

SL DMSQLSERVER = *SYSTEM/SQL/SERVER ON SYSNEW :TRUSTED, LINKCLASS=1

Solution

If the DMSQLSERVER statement points to an incorrect file, if either the TRUSTED parameter or the LINKCLASS parameter is missing, or if the code file does not exist, enter the following ODT command:

(34)

Verifying the SQL Query Processor Server Provider

Action

Enter the following ODT command: NA REG SHOW PROV DMSQLPROV

Result

Provider name: DMSQLPROV, Task type: NonMonitoredSupportLibrary Interface: Message

SL: DMSQLSERVER DSSes provided: DMSQL

Solution

If the displayed information differs from the preceding result, enter the following ODT command:

NA REG ADD PROV DMSQLPROV SL=DMSQLSERVER, TASK TYPE=NMSL, INTERFACE=MESSAGE, DSS=DMSQL

Verifying SQL Query Processor Distributed Systems Services (DSS)

Information

Action

Enter the following ODT command: NA REG SHOW DSS DMSQL

Result

The following text is displayed: DSSName: DMSQL, Class: OTHER

Recovery after Halt/Load: FALSE, Initialize DSS: TRUE Endpoints: DMSQL_TCPIP

Note: Your settings for Recovery after Halt/Load and Initialize DSS might differ.

Solution

(35)

Verifying the TCP/IP Endpoint

Action

Enter the following ODT command: NA REG SHOW EP DMSQL_TCPIP

Result

Endpoint Name: DMSQL_TCPIP, Public Endpoint: TRUE Filename: DMSQL_TCPIP, Applicationgroup:

Myname: "1897"

Solution

NA REG ADD EP DMSQL_TCPIP FILENAME=DMSQL_TCPIP, MYNAME="1897", PUBLIC=TRUE

Note:_{MYNAME must be a TCP/IP port number unique to the system. The number}

1897 is the default service number used for the SQL Query Processor. You can specify a different port number by registering this endpoint with a different port number for MYNAME, and by entering this number in the SQL Query Processor configuration file. Failure to choose a unique number could cause conflicts between different TCP/IP-based services.

Listing the Status of the SQL Query Processor Server

Action

Enter the following ODT commands: NA DMSQL -

NA DMSQL + NA DMSQL STATUS

Note: The NA DMSQL – command closes all SQL Query Processor connections and stops the SQL Query Processor server.

Result

DMSQL Server Status Report for job 4318/4318. Monday, October 11, 2005 11:50:26.6582. DSS Name is DMSQL. DSS Provider Name is DMSQLPROV. DMSQL Server [51.190.8001].

(36)

Trace Information not available. 0 DMSQL connections TCPIP Options : BlockedTimeout: 10 mins DialogCheckInterval: 5 mins Subfiles offered: 3 Using port: 1897

Solution

If the required information is missing from your SQL Query Processor server configuration file (DMSQL/SERVER/CONFIG), modify your server configuration file.

Updating Enterprise Database Server Databases

with SQL Queries

If you want an Enterprise Database Server database to be updated by SQL queries, the database must meet the following requirements:

• The database must be audited and include the REAPPLYCOMPLETED and

INDEPENDENTTRANS options. Refer to the DASDL Reference Manual for details on these options.

• The database must be imported through the Relational Design Center utility with the Read-only access control option reset. See “Importing a Relational Schema” in Section 4 for more information.

Or, the clause ACCESSCONTROL = UPDATEOK must be included as part of the Schema Administrator RUN statement. Refer to “Manually Enabling SQL Access to an Enterprise Database Server Database” in Section 7 for more information.

• The database resource must specify MODE UPDATE. Refer to the DMQUERY utility OPEN command in Section 8 for more information.

• Although updates to an Enterprise Application Environment (EAE) database can be performed using the SQL Query Processor, such direct updates are not

recommended as they might violate EAE application business rules and corrupt the application integrity of an EAE system.

Updating Enterprise Database Server Data Sets

with SQL Queries

If you want an Enterprise Database Server data set to be updated by SQL queries, the data set must meet the following requirements.

• The data set must not contain any LINK items.

• For some applications, the data set must have at least one set with no duplicates.

(37)

The Query Design Center is an interactive tool that administrators and developers can use to design and execute ad-hoc SQL requests against the SQL Query Processor for ClearPath MCP. This tool makes it easy to design and execute queries against Enterprise Database Server and test these queries outside of user applications. Using the Query Design Center, you specify your solution in terms of joins, ordering, columns to display, and so on. You do not use SQL syntax. The Query Design Center automatically builds the syntax of your queries based on your input. However, if you are fluent in SQL, you can manually enter and execute SQL syntax.

Starting the Query Design Center

Perform the following steps to start the Query Design Center:

1. On the Start menu, point to Programs, Unisys MCP, and then SQL Query Processor for ClearPath MCP Utilities, and click Query Design Center.

(38)

2. Perform one of the following actions:

• Type your log-in credentials without a resource name, and click OK.

Refer to “Usercode and Password Conventions” later in this section for details on specifying your usercode and password.

The Catalog window is displayed.

• Type your log-in credentials including a resource name, and click OK.

Refer to “Usercode and Password Conventions” later in this section for details on specifying your usercode and password.

The Analyze Query window is displayed.

Notes:

• To authenticate log-in credentials using your ClearPath MCP usercode and password, select the MCP option in the Authentication list and type your log-in credentials.

• To authenticate log-in credentials using Kerberos credentials, select the Kerberos option in the Authentication list.

To encrypt your Kerberos credentials, select the Encryption option in the Encryption list.

• If only read access is allowed on the database, select the Read Only check box. The ReadOnly property in the qdc.properties file controls the default setting of this option. The value 1 indicates read-only access.

3. Proceed with your work as desired.

Usercode and Password Conventions

The usercode can consist of usercode, accesscode, and chargecode combinations as shown in the following syntax. Accesscode and chargecode requirements are defined by your system administrator.

/<chargecode> /<accesscode>/<chargecode>

The password can consist of a password and an optional accesscode password as shown in the following syntax:

(39)

Understanding the Query Design Center Interface

The Query Design Center consists of a main window with a menu bar and the following internal windows:

• Catalog

The Catalog window enables you to view the available resources, the tables and columns of a given resource, and their respective properties. You can also access the Analyze Query window and the Design Query window.

• Analyze Query

The Analyze Query window enables you to enter and execute SQL statements, and view the results. To use this window, it is recommended that you have experience in SQL syntax and semantics.

• Design Query

The Design Query window enables you to interactively build queries and automatically generate an SQL statement.

The internal window that appears in the main window of the Query Design Center depends on the task you are performing.

(40)

Using the Menu Bar

The following table describes the menu bar located at the top of the Query Design Center window.

Menu Item Description

File Enables you to connect to a resource for a server, disconnect from a server, open an existing query, save a query, or save a query result. You can also exit the Query Design Center.

Query The following commands are available:

• Prepare

Validates the syntax of an SQL statement and prepares the SQL statement for execution.

• Execute

Executes the SQL statement.

• Commit

Commits the current transaction when the Manual Commit Mode command is set.

• Abort

Aborts the current transaction when the Manual Commit Mode command is set.

• Savepoint

Creates a named savepoint when the Manual Commit Mode command is set.

• Rollback to Savepoint

Rolls back the current transaction to a selected savepoint when the Manual Commit Mode command is set.

View Enables you to view the Catalog window. Options The following commands are available:

• QGraphs

Generates a QGraph which contains information that is useful when you are manually optimizing queries.

• QDumps

Generates a QDump which contains diagnostic information used by Unisys support personnel to analyze and troubleshoot problems you report to the Unisys Support Center.

• Manual Commit Mode

Commits the results of each SQL statement to the database. Manual commit mode is disabled by default. You can enable Manual Commit Mode and then use the Commit and Abort commands to manually manage transactions.

• Isolation level

(41)

Menu Item Description Options

(cont.)

• Change Row Limit

Sets the number of rows returned for queries.

• Look and feel

Sets the look and feel of the Query Design Center interface to the Windows, Metal (Java), or Motif (UNIX) environment.

Window Enables you to switch between currently opened windows.

Setting the Look and Feel of the Interface

You can set the Query Design Center interface to have the look and feel of a Java, UNIX, or Windows environment. The default look and feel is the Windows environment. Perform the following steps to set the look and feel of the Query Design Center interface:

1. On the Options menu, point to Look and Feel. 2. Click one of the following options:

• Metal • Motif • Windows

The Query Design Center adopts the selected look and feel for the current session.

Determining Connection Status

Perform the following steps to determine the connection status of a database: 1. Right-click in any window and click Connection Status.

The Connection Status dialog box appears. 2. Review the details of the resource connection. 3. Click OK to dismiss the dialog box.

(42)

Catalog Window

The Catalog window provides a view of resources against which you can design and execute queries.

The Catalog window has two views: a tree view and a properties view.

Tree View

The left pane of the Catalog window displays a tree view of databases that you can access, assuming you have the proper privileges to access a particular resource. The resources are defined in the SQL Query Processor configuration file named

DMSQL/CONFIG file that is used by the DMSQLCONFIG library.

Note: _{If the number of resources exceeds the number specified for the rowlimit}

property, the list of available resources in the tree view is truncated. Refer to “Default Properties” later in this section for more information about the rowlimit property.

Icons

The Catalog window uses the following icons to represent the different objects in the tree view.

Icon Description Identifies a database. Identifies a table. Identifies a column.

(43)

Icon

Identifies a primary key. Identifies a view.

Properties View

The right pane of the Catalog window displays the tree view. The properties and their

Resource Properties

The following properties appear in the properties view when a resource is selected in the tree view.

The tree view presents the database schema in a hierarchical fashion. For a given server, the root node is the database. The properties view displays the following information for a database.

Property Resource Name Database Title

Table Properties

The following properties appear in the properties view when a table is selected tree view. Property Resource Name Table Name Owner Name Type Primary Key Description Identifies a primary key. Identifies a view.

The right pane of the Catalog window displays the properties of the object

properties and their and values vary depending on the selected object.

Resource Properties

The following properties appear in the properties view when a resource is selected in the

The tree view presents the database schema in a hierarchical fashion. For a given server, the root node is the database. The properties view displays the following information for

Property Description

Identifies the logical name of the data source. Identifies the file title of the physical database.

Table Properties

The following properties appear in the properties view when a table is selected

Identifies the logical name of the data source. A resource maps a data source identifier to one or more physical databases.

Identifies the name of the table or view of the resource.

Identifies the name of the owner of the table or view. Identifies the item as a table or view.

Identifies the column names of the primary key for the table or view.

of the object selected in depending on the selected object.

The following properties appear in the properties view when a resource is selected in the

The tree view presents the database schema in a hierarchical fashion. For a given server, the root node is the database. The properties view displays the following information for

data source. Identifies the file title of the physical database.

The following properties appear in the properties view when a table is selected in the

data source. A resource maps a data source identifier to one or

table or view of the

Identifies the name of the owner of the table or view. Identifies the item as a table or view.

(44)

Column Properties

The following properties appear in the properties view when a column is selected in the tree view.

Resource Name Identifies the logical name of the data source. A resource maps a data source identifier to one or more physical databases.

Table Name Identifies the name of the table or view of the resource.

Column Name Identifies the column name.

Type Identifies the data type of the column.

Length For a character type, identifies the number of bytes for a fixed length type and the maximum number of bytes for a variable length type.

Precision For a fixed point number (numeric or decimal type), identifies the maximum number of digits

representing the value.

Scale For a fixed point number (numeric or decimal type), identifies the maximum number of digits

representing the value to the right of the decimal point.

Required Indicates whether the column can contain NULL values.

Primary Key Properties

The following properties appear in the properties view when a primary key is selected in the tree view.

Resource Name Identifies the logical name of the data source. A resource maps a data source identifier to one or more physical databases.

Table Name Identifies the name of the table or view of the resource.

Column Name Identifies the column name.

Type Identifies the data type of the column.

Length For a character type, identifies the number of bytes for a fixed length type and the maximum number of bytes for a variable length type.

(45)

Precision For a fixed point number (numeric or decimal type), identifies the maximum number of digits

representing the value.

Scale For a fixed point number (numeric or decimal type), identifies the maximum number of digits

representing the value to the right of the decimal point.

Required Indicates whether the column can contain NULL values.

Primary Key Sequence Identifies the ordinal number for each column of that is part of the primary key. There can be more than one column that is part of the primary key. Index Identifies the name of the index if an index exists

over this column.

This property could apply to any column.

Opening Tables

You can open tables contained in a resource. The Query Design Center automatically executes the appropriate SQL query on the table to enable you to see the rows of the table.

Perform the following steps to open a table:

1. From the Catalog window, expand a resource to expose the underlying tables. 2. Right-click the desired table and click Open.

The Analyze Query window appears with the SELECT statement in the text pane and the rows of the table in the results pane.

Viewing Table Results

Perform the following steps to display the tables of a resource and related information: 1. From the Catalog window, right-click a resource, point to View, and click SQLTables.

The separate window appears listing tables contained in the resource in the results pane.

3. To save the results as a text file, right-click the results pane and click Save Result Set As.

The Save dialog box appears.

4. Navigate to the desired folder, type a name in the File name box, and click Save. The file is saved.

(46)

Viewing Column Results

Perform the following steps to display the information related to columns:

1. From the Catalog window, expand a resource, right-click the desired table, and point to View.

2. Click one of the following commands on the shortcut menu: • SQLColumns

Displays the columns of the selected table. • SQLPrimaryKeys

Displays the columns that make up the primary key for the selected table. • SQLForeignKeys, related

Displays the list of foreign keys in the selected table and their related primary keys.

• SQLForeignKeys, in this

Displays the list of foreign keys in the selected table and their related primary keys.

• SQLStatistics

Displays a list of statistics about a single table and the indexes associated with the table.

A separate window appears with the results in the results pane.

(47)

Analyze Query Window

The Analyze Query window enables you to

• Interrogate and update data in a database using SQL syntax and semantics.

• Experiment with the SQL Query Processor call-level interface (CLI) without having to write a COBOL or ALGOL program.

• Obtain diagnostic information about queries for purposes of optimization and to send to Unisys support for analysis.

• View result sets translated using the coded character set of the database. Through the Analyze Query window, you can enter and execute the following SQL statements, and view their results:

• SELECT • INSERT • UPDATE • DELETE

The Analyze Query window has two panes: a text pane and a results pane.

Text Pane

The top pane of the Analyze Query window enables you to type the text of an SQL query statement and perform a number of operations such as statement execution and syntax checking. You can also load a query from a text fie into the text pane.

In the following example, a SELECT statement was typed in the text pane. A shortcut menu appears when you right-click the text pane enabling you to perform actions against the statement.

(48)

Results Pane

The bottom pane of the Analyze Query window displays the results of syntax checking (along with item descriptions) and the results of the executed query statement. You can also save results of a query to a text file.

In the following example, the syntax of the statement SELECT * FROM sqlexample.car in the text pane is validated and the item descriptions displayed in the results pane.

The following example shows the results of the executed query statement.

Note: From the shortcut menu for a table or view, you can access the Analyze Query window to display the contents of the selected table (Open) or access the Design Query window to design a query involving the selected table.

(49)

Analyze Query Window Commands

The following commands are available on the shortcut menu when you right-click in the Analyze Query window.

Command Description

Copy Result Set Copies the table of results to the clipboard. You can only execute this command from the results pane.

Clear Removes the contents of the text pane or results pane, depending on the pane from which you execute the command.

Execute Executes the SQL statement in the text area, or executes the SQL statement that the user has selected in the text area.

Prepare Parses the SQL statement in the text pane.

The Query Design Center validates the syntax without attempting to execute the query. The results of a successful validation appear in the results pane along with item

descriptions. If the syntax is not valid, an error message provides a description of the error.

Commit Commits the current transaction when in manual commit mode.

Abort Aborts the current transaction when in manual commit mode.

Savepoint Saves a named savepoint when in manual commit mode. Rollback to savepoint Rolls back the current transaction to a selected savepoint

when in manual commit mode.

Open Query Enables you to place the contents of a saved query into the text pane.

Save Query As Saves the contents of the text pane to a file. Save Result Set As Saves the results of an executed query to a text file. View Catalog Displays the Catalog window.

(50)

Preparing and Executing a Query

Perform the following steps to prepare and execute a query.

Note: Queries are limited to a maximum number of 5000 rows by default. See the section on Customizations later in this document.)

1. From the Catalog window, right-click the desired resource and click Analyze Query. The Analyze Query window appears.

2. In the text pane (top pane), type your query statement. 3. Right-click and choose Prepare.

The Query Design Center checks the syntax of your statement.

• If the syntax is valid, the outcome along with item descriptions is displayed in the results pane (bottom pane). The following message also appears:

Statement was prepared successfully. See item descriptions above.

• If the SQL syntax is not valid, an error message appears containing a description of the error. You must correct the syntax before proceeding.

4. Right-click and choose Execute.

If your SQL statement contains parameters, the Enter Parameters dialog box appears. Continue with step 5. Otherwise, the Query Design Center executes the query and the outcome appears in the results pane.

5. Type the desired parameters in the Parameter 1 box, and click OK. The outcome of the query appears in the results pane.

Note: The Enter Parameters dialog box continues to appear depending on the number of parameters declared in your SQL statement.

Saving a Query

You can save a query statement that you enter in the text pane and use it later. Perform the following steps to save a query:

1. Right-click the text pane and click Save Query As. The Save dialog box appears.

2. Navigate to the desired folder, type a name in the File name box, and click Save. The file is saved with the .sql extension, for example, mystatement.sql.