Avaya PeriRDB User Manual
PeriRDB User’s Guide 2.1
Avaya Business Communications Manager
Document Status: Standard
Document Number: P0602814
Document Version: 1.92
Date: June 2010
© 2010 Avaya Inc.
All Rights Reserved.
Notices
While reasonable efforts have been made to ensure that the information in this document is complete and accurate at the time of printing,
Avaya assumes no liability for any errors. Avaya reserves the right to make changes and corrections to the information in this document
without the obligation to notify any person or organization of such changes.
Documentation disclaimer
Avaya shall not be responsible for any modifications, additions, or deletions to the original published version of this documentation
unless such modifications, additions, or deletions were performed by Avaya. End User agree to indemnify and hold harmless Avaya,
Avaya’s agents, servants and employees against all claims, lawsuits, demands and judgments arising out of, or in connection with,
subsequent modifications, additions or deletions to this documentation, to the extent made by End User.
Link disclaimer
Avaya is not responsible for the contents or reliability of any linked Web sites referenced within this site or documentation(s) provided by
Avaya. Avaya is not responsible for the accuracy of any information, statement or content provided on these sites and does not
necessarily endorse the products, services, or information described or offered within them. Avaya does not guarantee that these links will
work all the time and has no control over the availability of the linked pages.
Warranty
Avaya provides a limited warranty on this product. Refer to your sales agreement to establish the terms of the limited warranty. In
addition, Avaya’s standard warranty language, as well as information regarding support for this product, while under warranty, is
available to Avaya customers and other parties through the Avaya Support Web site: http://www.avaya.com/support
Please note that if you acquired the product from an authorized reseller, the warranty is provided to you by said reseller and not by Avaya.
Licenses
THE SOFTWARE LICENSE TERMS AVAILABLE ON THE AVAYA WEBSITE, HTTP://SUPPORT.AVAYA.COM/LICENSEINFO/
ARE APPLICABLE TO ANYONE WHO DOWNLOADS, USES AND/OR INSTALLS AVAYA SOFTWARE, PURCHASED FROM
AVAYA INC., ANY AVAYA AFFILIATE, OR AN AUTHORIZED AVAYA RESELLER (AS APPLICABLE) UNDER A
COMMERCIAL AGREEMENT WITH AVAYA OR AN AUTHORIZED AVAYA RESELLER. UNLESS OTHERWISE AGREED TO
BY AVAYA IN WRITING, AVAYA DOES NOT EXTEND THIS LICENSE IF THE SOFTWARE WAS OBTAINED FROM ANYONE
OTHER THAN AVAYA, AN AVAYA AFFILIATE OR AN AVAYA AUTHORIZED RESELLER, AND AVAYA RESERVES THE
RIGHT TO TAKE LEGAL ACTION AGAINST YOU AND ANYONE ELSE USING OR SELLING THE SOFTWARE WITHOUT A
LICENSE. BY INSTALLING, DOWNLOADING OR USING THE SOFTWARE, OR AUTHORIZING OTHERS TO DO SO, YOU,
ON BEHALF OF YOURSELF AND THE ENTITY FOR WHOM YOU ARE INSTALLING, DOWNLOADING OR USING THE
SOFTWARE (HEREINAFTER REFERRED TO INTERCHANGEABLY AS "YOU" AND "END USER"), AGREE TO THESE
TERMS AND CONDITIONS AND CREATE A BINDING CONTRACT BETWEEN YOU AND AVAYA INC. OR THE
APPLICABLE AVAYA AFFILIATE ("AVAYA").
Copyright
Except where expressly stated otherwise, no use should be made of the Documentation(s) and Product(s) provided by Avaya. All content
in this documentation(s) and the product(s) provided by Avaya including the selection, arrangement and design of the content is owned
either by Avaya or its licensors and is protected by copyright and other intellectual property laws including the sui generis rights relating
to the protection of databases. You may not modify, copy, reproduce, republish, upload, post, transmit or distribute in any way
content, in whole or in part, including any code and software. Unauthorized reproduction, transmission, dissemination, storage, and or
use without the express written consent of Avaya can be a criminal, as well as a civil offense under the applicable law.
Third Party Components
Certain software programs or portions thereof included in the Product may contain software distributed under third party agreements
("Third Party Components"), which may contain terms that expand or limit rights to use certain portions of the Product ("Third Party
Terms"). Information regarding distributed Linux OS source code (for those Products that have distributed the Linux OS source code),
and identifying the copyright holders of the Third Party Components and the Third Party Terms that apply to them is available on the
Avaya Support Web site: http://support.avaya.com/Copyright.
Trademarks
The trademarks, logos and service marks ("Marks") displayed in this site, the documentation(s) and product(s) provided by Avaya are the
registered or unregistered Marks of Avaya, its affiliates, or other third parties. Users are not permitted to use such Marks without prior
written consent from Avaya or such third party which may own the Mark. Nothing contained in this site, the documentation(s) and
product(s) should be construed as granting, by implication, estoppel, or otherwise, any license or right in and to the Marks without the
express written permission of Avaya or the applicable third party. Avaya is a registered trademark of Avaya Inc. All non-Avaya
trademarks are the property of their respective owners.
Downloading documents
For the most current versions of documentation, see the Avaya Support. Web site: http://www.avaya.com/support
Contact Avaya Support
Avaya provides a telephone number for you to use to report problems or to ask questions about your product. The support telephone
number is 1-800-242-2121 in the United States. For additional support telephone numbers, see the Avaya Web site: http://
www.avaya.com/support
any
Table of Contents
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Intended Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
How to Use This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Organization of This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Conventions Used in This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Solaris and Windows 2000 Conventions . . . . . . . . . . . . . . . . . . . . . . . . 10
Trademark Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Introduction to RDB Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Using the RDB package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Supported RDBM Systems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
PeriSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Automatically Generating SQL Commands (PeriSQL) . . . . . . . . . . . . 16
Accessing PeriSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Setting the Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . 17
Accessing PeriSQL From the Command Line. . . . . . . . . . . . . . . . . 18
Accessing PeriSQL From PeriProducer. . . . . . . . . . . . . . . . . . . . . . 19
PeriSQL Display. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
PeriSQL User Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
PeriSQL Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
PeriSQL Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Column Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Using PeriSQL to Query an RDBMS. . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Creating and Customizing the SQL Command in PeriSQL. . . . . . . 25
Selecting Multiple Columns (Join) . . . . . . . . . . . . . . . . . . . . . . 26
Performing a Query With PeriSQL . . . . . . . . . . . . . . . . . . . . . . . . . 27
Debugging the SQL Command in PeriSQL. . . . . . . . . . . . . . . . . . . 28
Reported Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Using Stored Procedures, Functions, and Triggers. . . . . . . . . . . . . . . . . 28
Displaying Predefined Procedures, Functions, and Triggers. . . . . . 29
Implementing Procedures and Functions . . . . . . . . . . . . . . . . . . . . . 31
Writing Procedures and Functions to an Application . . . . . . . . 31
Supplying Arguments to Functions and Procedures . . . . . . . . . 33
Sybase Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Using PeriSQL With the SQL Block . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Writing the PeriSQL SELECT to an SQL Block. . . . . . . . . . . . . . . 36
SQLClnt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Accelerating RDBMS Access With SQLClnt . . . . . . . . . . . . . . . . . . . . 38
Limitations For SQLClnt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Configuring For SQLClnt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Using the SQL Request Accelerator . . . . . . . . . . . . . . . . . . . . . . . . 38
Using the SQLClnt Backup Server (-s option). . . . . . . . . . . . . . . . . 39
Page 3
PeriRDB User’s Guide
CORBAClnt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Timing, Measuring, and Logging RDBMS Server Performance . . . 40
Cryptutil Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Using the Cryptutil Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40
Using the Encrypted Connect String with SQLClnt . . 41
Using encrypted Connect Strings with gen.cfg . . . 42
Accessing Databases using SQLClnt on an Intermediate Node . . . . . . .43
SQLClnt on the Same Node as the IVR Application . . . . . . . . . . . .43
SQLClnt on an Intermediate Node . . . . . . . . . . . . . . . . . . . . . . . . . .44
SQLClnt and vmst on an Intermediate Node . . . . . . . . . . . . . . . . . . 45
SQLClnt and periq on an Intermediate Node . . . . . . . . . . . . . . . . . . 46
Working with CORBA Server Objects . . . . . . . . . . . . . . . . . . . . . . . . . 50
CORBAClnt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
corbaclnt Usage Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Installation/Startup of corbaclnt on Solaris . . . . . . . . . . . . . . . . . . . 51
CORBAClnt Server Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Reported Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
The CORBA Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
Invoking Operations With the CORBA Block . . . . . . . . . . . . . . . . .54
Specifying Return Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55
Specifying Return Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
PeriRDB Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
PeriRDB Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Page 4
Preface
PeriRDB User’s Guide
Scope
The PeriRDB User’s Guide provides a feature description and reference for PeriSQL
and SQLClnt software functions as well as SQL database libraries. This document
also includes installation, configuration, and operation information.
This document uses PeriProducer as the application development tool. For
information about using PeriProducer, see the PeriProducer User’s Guide.
Intended Audience
This document is intended for application developers implementing RDBMS
applications in an Avaya Media Processing Server (MPS) environment.
To use this document, you should be familiar with relational databases, and structured
query languages.
Avaya recommends that application developers attend an on-site PeriProducer class as
well as a system familiarization training program before attempting to create
interactive applications.
Page 6
How to Use This Manual
This manual uses many standard terms relating to computer system and software
application functions. However, it contains some terminology that can only be
explained in the context of the MPS Series system. Refer to the Glossary of MPS
Terminology for definitions of MPS Series specific terms.
Initially, you should read this manual at least once, from start to finish. Later, you can
use the Table of Contents to locate topics of interest for reference and review.
If you are reading this document online, use the cross-reference links (shown in blue)
to quickly locate related topics. <L
with your cursor over the cross-reference link. Click on any point in a Table of
Contents entry to move to that topic. Click on the page number of any Index entry to
access that topic page.
To familiarize yourself with various specialized textual references within the manual, ,
see Conventions Used in This Manual on page 8.
Preface
EFT> click once with your mouse while positioned
Periphonics is part of Avaya. The name Periphonics, and variations thereof, appear in
this manual only where it is referred to in a product. (For examples, a PeriProducer
application, the PERImps package, the perirev command, etc.)
Organization of This Manual
• Chapter 1: PeriSQL
Describes Periphonics’ SQL generation and display tool, PeriSQL
• Chapter 2: SQLClnt
Describes Periphonics’ RDBMS server request accelerator, SQLClnt
• Chapter 3: CORBAClnt
Describes Periphonics’ CORBA server tool, CORBAClnt
• Chapter 4: PeriRDB Libraries
Describes PeriRDB’s database libraries
Page 7
PeriRDB User’s Guide
Conventions Used in This Manual
This manual uses different fonts and symbols to differentiate between document
elements and types of information. These conventions are summarized in the
following table.
Conventions Used in This Manual Sheet 1 of 2
Notation Description
Normal text
important term
system
command
command,
condition
and alarm
file name /
directory
on-screen field
<KEY NAME>
Book Reference
Normal text font is used for most of the document.
The Italics font is used to introduce new terms, to highlight
meaningful words or phrases, or to distinguish specific terms from
nearby text.
This font indicates a system command and/or its arguments. Such
keywords are to be entered exactly as shown (i.e., users are not to
fill in their own values).
Command, Condition and Alarm references appear on the screen
in magenta text and reference the Command Reference Manual,
the PeriProducer User’s Guide, or the Alarm Reference Manual,
respectively. Refer to these documents for detailed information
Commands, Conditions, and Alarms.
about
This font is used for highlighting the names of disk directories, files,
and extensions for file names. It is also used to show displays on
text-based screens (e.g., to show the contents of a file.)
This font is used for field labels, on-screen menu buttons, and
action buttons.
A term that appears within angled brackets denotes a terminal
keyboard key, a telephone keypad button, or a system mouse
button.
This font indicates the names of other publications referenced
within the document.
cross reference
!
A cross reference appears on the screen in blue text. Click on the
cross reference to access the referenced location. A cross
reference that refers to a section name accesses the first page of
that section.
The Note icon identifies notes, important facts, and other keys to
understanding.
The Caution icon identifies procedures or events that require
special attention. The icon indicates a warning that serious
problems may arise if the stated instructions are improperly
followed.
Page 8
Conventions Used in This Manual Sheet 2 of 2
Notation Description
Preface
The flying Window icon identifies procedures or events that apply
to the Windows 2000 operating system only.
The Solaris icon identifies procedures or events that apply to the
Solaris operating system only.
1. Windows 2000 and the flying Window logo are either trademarks or registered
trademarks of the Microsoft Corporation.
2. Solaris is a trademark or registered trademark of Sun Microsystems, Inc. in the
United States and other countries.
2
1
Page 9
PeriRDB User’s Guide
Solaris and Windows 2000 Conventions
This manual depicts examples (command line syntax, configuration files, and screen
shots) in Solaris format. In certain instances Windows 2000 specific commands,
procedures, or screen shots are shown where required. The following table lists
examples of general operating system conventions to keep in mind when using this
manual with either the Solaris or 2000 operating system.
Solaris Windows 2000
Environment $MPSHOME %MPSHOME%
Paths $MPSHOME/common/etc %MPSHOME%\common\etc
Command <command> & start /b <command>
Trademark Conventions
The following trademark information is presented here and applies throughout for
third party products discussed within this manual. Trademarking information is not
repeated hereafter.
Solaris is a trademark or registered trademark of Sun Microsystems, Inc. in the United
States and other countries.
Microsoft, Windows, Windows 2000, Internet Explorer, and the Flying Windows logo
are either trademarks or registered trademarks of Microsoft Corporation.
Netscape® and Netscape Navigator® are registered trademarks of Netscape
Communications Corporation in the United States and other countries. Netscape's
logos and Netscape product and service names are also trademarks of Netscape
Communications Corporation, which may be registered in other countries.
Page 10
Introduction to RDB
Access
This chapter covers:
1. Overview
2. Using the RDB package
3. Supported RDBM Systems
PeriRDB User’s Guide
Overview
Relational Database Management Systems (RDBMS) Database access is enabled
through the MPS Relational Database (RDB) collection of programs and libraries.
Program type Description
MPS RDB Programs
MPS SQL
GUI utility to access an RDBMS, build and perform Structured
Query Language (SQL) commands and display results. It can be
launched as a stand-alone application or from within the SQL block
in MPS Developer for application development.
SQLClnt
Cryptutil
CORBAClnt
Program to accelerate access to an RDBMS. Used in conjunction
with PERIQ.
Encryption program used to encrypt access strings. These access
strings are used by SQLClnt during logon.
Program to access Common Object Request Broker Architecture
(CORBA) servers
There are three types of MPS RDB Libraries:
« MPS RDB
« SQL Client
« MPS SQL
MPS RDB Libraries are used by three types of applications based on the kind of
connection to the database:
1. applications making direct (native) connections to relational
databases
2. applications using SQL Client (SQLClnt)
3. applications using CORBA Client (CORBAClnt)
Page 12
Introduction to RDB Access
Using the RDB package
1. Write an Interactive Voice Response (IVR) application using MPS Devel-
oper.
2. Include SQL blocks in the IVR application.
a. Type SQL commands into the block or use MPS SQL.
b. Specify native, SQL Client, or CORBA Client connections in each
block.
3. When you run the IVR application, each SQL block formulates SQL
commands.
4. If the application uses a client connection, the SQL commands are sent to
either the SQL Client or CORBA Client. (This step is omitted if the application uses a native connection.)
5. The SQL commands are sent to a program/library that is part of MPS
RDB.
6. The RDB program/library forwards the SQL commands to the RDBMS
Client.
7. If the application uses native or SQL Client connections, the RDBMS Cli-
ent sends the SQL commands to the RDBMS database. If the application
uses CORBA Client connections, the RDBMS Client sends the SQL commands to the CORBA Server.
8. The results from the RDBMS Database (or CORBA Server) are sent back
to the IVR application in reverse order.
Supported RDBM Systems
The following tables show supported connection types for third-party relational
database systems.
Connection
type
Native Oracle 8i (1), 9i,
Oracle Sybase Informix MS SQL DB2
10g (2)
Sybase 10.x,
11.x, 12.1
IDS
7.31,
9.21,
9.30,
9.40
DB2 7.x,
8.1
ODBC
Connect ODBC
4.2. For MS SQL
Server 7.0,
2000, use
Connect 5.0
Page 13
PeriRDB User’s Guide
Connection
type
Native Oracle 8i(1).
ODBC
Oracle Sybase Informix MS SQL DB2
Up to patch
rdb2.0.8; use
ODBC for
higher patch
levels
MS ODBC or
Oracle ODBC
9i, 10g. From
patch rdb2.0.9
or higher
Sybase 10.x,
11.x, 12.1
IDS 7.31,
9.21,
9.30, 9.40
MS SQL
Server 6.5, 7.0,
2000
1. Oracle 8i is not supported by the vendor since December 2004.
2. Access to Oracle 10g requires Oracle 9i client.
DB2
7.x, 8.1
Page 14
PeriSQL
This chapter covers:
1. Automatically Generating
SQL Commands (PeriSQL)
2. Accessing PeriSQL
3. PeriSQL Display
4. Using PeriSQL to Query an
RDBMS
5. Using Stored Procedures,
Functions, and Triggers
6. Using PeriSQL With the SQL
Block
PeriRDB User’s Guide
Automatically Generating SQL Commands (PeriSQL)
The Periphonics SQL Generate and Display Tool (PeriSQL) allows you to create,
modify, and execute Structured Query Language (SQL) SELECT commands through
a graphical interface.
PeriSQL uses the relational database management system (RDBMS) server’s master
data dictionary to query, retrieve, and display information about the various database
objects.
You can also use PeriSQL to access stored Procedures, Functions, and Triggers (see
Using Stored Procedures, Functions, and Triggers on page 28).
You can use PeriSQL as a stand-alone utility (see Accessing PeriSQL From the
Command Line on page 18) or with the PeriProducer SQL block (see Accessing
PeriSQL From PeriProducer on page 19).
References to PeriProducer’s SQL block are used throughout this section. For detailed
information about the SQL block, refer to the PeriProducer User’s Guide.
Page 16
Accessing PeriSQL
You can access PeriSQL as a stand-alone utility or through the PeriProducer SQL
block.
Setting the Environment Variables
Before PeriSQL can access the RDBMS, you must set the required environment
variables for the particular RDBMS.
Normally, the required environment variables are established during the installation of
the PeriRDB package.
To access Oracle, set the following Oracle-specific environment variables:
PeriSQL
variable name defines
ORACLE_HOME the base directory for the Oracle installation
ORACLE_SID an alias for the Oracle SID (server name)
For example:
This alias must be properly defined in the
$ORACLE_HOME/network/admin/tnsnames.ora file to
refer to a valid Oracle server instance (SID)
Page 17
PeriRDB User’s Guide
To access Sybase, set the following Sybase-specific environment variables:
variable name defines
SYBASE the base directory for the Sybase installation
DSQUERY the name of the Sybase server
For example:
Accessing PeriSQL From the Command Line
DSQUERY must be properly defined in the
$SYBASE/interfaces file for a valid Sybase server.
Access PeriSQL from the command line by entering the following command:
perisql &
PeriSQL displays the Logon window:
Select the RDBMS
Type the user
name and
to access
RDBMS server name
password
Exit without
Select
logging on
Logon
Page 18
Loading...