hp structured data manager...hp structured data managerarchive query server client guide 7 document...

HP Structured Data ManagerSoftware version: 7.2

Archive Query Server Client Guide

Document release date: August 2014Software release date: August 2014

Legal notices

Warranty

The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein.

The information contained herein is subject to change without notice.

Restricted rights legend

Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license.

Licensing

The use of HP products is governed by the terms and conditions of the applicable End User License Agreement (EULA).

Copyright notices

© Copyright 2014 Hewlett-Packard Development Company, L.P.

Trademark notices

Microsoft® and Windows® are registered trademarks of Microsoft Corporation.

Oracle® and Java™ are registered trademarks of Oracle Corporation and/or its affiliates.

UNIX® is a registered trademark of The Open Group.

2 HP Structured Data Manager Archive Query Server Client Guide

Contents

About this document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5Intended audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5

Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5

Related documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5

Document conventions and symbols . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7

Documentation updates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7

Subscription service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8

Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8

1 Accessing archived files with SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

Installing the AQS client. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

Installing and configuring the ODBC driver . . . . . . . . . . . . . . . . . . . . . . . . . . . .10

Connecting to the archive data source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14

Viewing archive collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19

2 Integrating the AQS client with databases . . . . . . . . . . . . . . . . . . . . . . . . .27

Viewing your archived data from PostgreSQL. . . . . . . . . . . . . . . . . . . . . . . . . . .27

Viewing your archived data from Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30

Viewing your archived data from SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . .37

Searching by schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39

Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49

HP Structured Data Manager Archive Query Server Client Guide 3

4 Contents

About this document

HP Structured Data Manager provides powerful tools to build an archive solution that copies or moves data out of your online transaction processing database and into less expensive storage.

This guide provides information about:

• configuring HP Structured Data Manager archive query server

• accessing archived data

Intended audience

This guide is intended for end users who need to install and run a client driver to access archived data

Prerequisites

Prerequisites for installing this product include:

• configuration information for the server to connect to

• configuration information for the client being used

Related documentation

In addition to this guide, please refer to other documents for this product:

• HP Structured Data Manager Installation Guide

Explains how to use the Installer to install the product.

• HP Structured Data Manager Concepts Guide

Explains the major concepts of database archiving in general and HP Structured Data Manager in particular.

• HP Structured Data Manager Tutorial

Provides step-by-step instructions to build a sample archiving module, deploy it, run it, and troubleshoot errors.

• HP Structured Data Manager Developer’s Guide


Explains how to use the Designer component to design, build, test, and deploy your archiving projects.

• HP Structured Data Manager Runtime Guide

Explains how to use the Web Console component to run, monitor, and administer business flows that move data to and from the database.

• HP Structured Data Manager Troubleshooting Guide

Explains how to diagnose and resolve errors, and provides a list of common errors and solutions.

• HP Structured Data Manager API Reference Guide

Provides a reference for the available programming interfaces.

• HP Structured Data Manager Upgrade Guide

Explains how to upgrade the product and upgrade the archive schema generated by earlier versions of the product.

• HP Structured Data Manager Release Notes

Lists any items of importance that were not captured in the regular documentation.

• HP Structured Data Manager Installation Guide for PeopleSoft

Explains how to install the PeopleSoft integration kit.

• HP Structured Data Manager PeopleSoft Release Notes

Lists any items of importance that were not captured in the regular PeopleSoft documentation.

• HP Structured Data Manager Installation Guide for Oracle E-Business Suite

Explains how to install the Oracle E-Business Suite integration kit.

• HP Structured Data Manager Oracle E-Business Suite Release Notes

Lists any items of importance that were not captured in the regular Oracle E-Business Suite documentation.

The latest documentation for HP Structured Data Manager can be found on HP Software Support Online:

http://support.openview.hp.com/selfsolve/manuals

6 About this document


Document conventions and symbols

NOTE Provides additional information.

TIP Provides helpful hints and shortcuts.

RECOMMENDATION Provides guidance from HP for a best practice or for optimum performance.

Documentation updates

The latest documentation for HP Structured Data Manager can be found on HP Software Support Online:


NOTE This documentation is written to the latest patch version. If you have not installed the latest patch, there may be items in this documentation that do not apply to your environment.

Convention Element

[ ] Indicates that the enclosed element is optional and may be left out.

{ } Indicates that you must specify one of the listed options.

| Separates alternatives.

<parameter_name> You must supply a value for a variable parameter.

Medium blue text: Figure 1 Cross-reference links and e-mail addresses

Medium blue, underlined text (http://www.hp.com)

Web site addresses

Monospace • File and directory names

• Text displayed on the screen, such as system output and application messages

• Code syntax



Subscription service

HP strongly recommends that customers sign up online using the Subscriber's choice web site:

http://www.hp.com/go/e-updates

• Subscribing to this service provides you with e-mail updates on the latest product enhancements, newest versions of drivers, and firmware documentation updates as well as instant access to numerous other product resources.

• After signing up, you can quickly locate your products under Product Category.

Support

You can visit the HP Software Support web site at:

http://www.hp.com/go/hpsoftwaresupport

HP Software Support Online provides an efficient way to access interactive technical support tools. As a valued support customer, you can benefit by using the support site to:

• Search for knowledge documents of interest

• Submit and track support cases and enhancement requests

• Download software patches

• Manage support contracts

• Look up HP support contacts

• Review information about available services

• Enter into discussions with other software customers

• Research and register for software training

Most of the support areas require that you register as an HP Passport user and sign in. Many also require a support contract.

To find more information about access levels, go to:

http://support.openview.hp.com/new_access_levels.jsp

8 About this document

http://www.hp.com/go/e-updates

http://www.hp.com/go/hpsoftwaresupport

1 Accessing archived files with SQL

After your data has been archived to XML or CSV files, you can use the Archive Query Server (AQS) client to directly access your data without reloading the data into a relational database. You can access your data using reporting and development tools on Windows and UNIX, and join the data with existing data in a database.

The archive query server client uses standard JDBC and ODBC clients and servers to connect to your archived data. The JDBC driver is installed during product installation, and requires no configuration. The ODBC driver is installed separately and must be configured before use.

In this chapter • Installing the AQS client (page 9)

• Installing and configuring the ODBC driver (page 10)

• Connecting to the archive data source (page 14)

• Viewing archive collections (page 19)

Installing the AQS client

To install the AQS client:

1 Download the client package.

2 Extract the files in the package to your installation directory.

Platform Package name

Linux 32-bit TF707-15001-08.tar.gz

Linux 64-bit TF707-15001-09.tar.gz

Windows 32-bit TF707-15001-11.zip

Windows 64-bita TF707-15001-12.zip

a. The 64-bit Windows package contains the drivers for both 32-bit and 64-bit applications. When you install the driver, the menu gives the option to install either the 32-bit or 64-bit driver and you can choose the appropriate one for your application.


The AQS client files are extracted to the directory

<install_directory>/obt/bin

where <install_directory> is the location to which you extracted the files. (In the following sections <install_directory> refers to the directory where you installed the files.)

After you have extracted the files from the package, you need to install any database drivers needed by your applications. See Installing and configuring the ODBC driver (page 10) for information about how to install the database drivers.

Installing and configuring the ODBC driver

If you want to access data through AQS using an ODBC client on Windows or UNIX, you need to install and configure the appropriate driver.

• Installing and configuring the ODBC database driver on Windows (page 10)

• Installing and configuring the ODBC database driver on UNIX (page 12)

Installing and configuring the ODBC database driver on Windows

When you install the ODBC database driver on Windows, an ODBC data source is automatically created and configured to communicate with the server that is already installed. If you want to use a different data source name or connect to a different database or host, you need to configure the ODBC driver. You can configure the ODBC database driver before or after you start the AQS process, but you can only test the connection if the AQS process is running.

After you install the ODBC driver on Windows, you need to configure the driver.

NOTE If you are not sure of the required connection values, check with your administrator.

Installing the ODBC driver

1 Ensure that you are logged in as user who is a member of the administrator group. You need administrative privileges to install the driver.

2 Navigate to the directory that contains the ODBC driver installation file, oadriver.bat.

cd <install_directory>/obt/bin

3 Double-click the installation batch file, oadriver.bat.

NOTE 64-bit Windows platforms can have two ODBC drivers, one for 32-bit and one for 64-bit applications. You must install the proper driver to work with your 32-bit or 64-bit application.

10 Chapter 1: Accessing archived files with SQL

4 Select Option 1 to install the driver. On 64-bit platforms, you have two options for ODBC, 64-bit or 32-bit driver.

5 Select the driver you want to install.

The ODBC driver is installed, and a default system data source and service data source are automatically created and configured to communicate with a local AQS:

If you are only installing a client, you need to reconfigure the ODBC data source by setting the Host and Port and Service Data Source as described below.

If you want to create additional ODBC system data sources, see Creating additional ODBC data sources on Windows (page 11).

Creating additional ODBC data sources on Windows

Perform the following steps to configure 32-bit ODBC on 32-bit Windows or 64-bit ODBC on 64-bit Windows. .

To re-configure an existing ODBC data source, follow the steps below but skip steps 4 through 6.

To configure a new ODBC database driver:

1 In the Windows Start menu, select the appropriate program folder item for AQS:

— Archive Query Server32 > ODBC Administrator

— Archive Query Server64 > 64-bit ODBC Administrator.

TIP If you access the ODBC Administrator dialog through the Control Panel, you need to ensure that you open the correct dialog for your driver (32-bit or 64-bit). Using the Start menu options above, makes it much easier to find the correct ODBC Data Source Administrator dialog for your driver.

The ODBC Data Source Administrator displays.

2 Click the System DSN tab.

3 Click Add.

The Create New Data Source window opens.

4 Select the driver.

Name Description

AQS64 The system data source name for the 64-bit ODBC driver.

AQS32 The system data source name for the 32-bit ODBC driver.

AQS The system data source name for the 32-bit or 64-bit UNIX ODBC driver.

xmlArchive The default service data source name.


5 Click Finish.

The HP Structured Data Manager Query Server ODBC Driver Setup window opens.

6 Enter the following values:

7 If your administrator has started the AQS process, click Test Connect to test the connection.

The Login to Data Source window opens.

8 Enter the following information:

9 Click OK to test the connection.

10 Click OK to close the confirmation window.

11 Click OK to save the configuration.

Installing and configuring the ODBC database driver on UNIX

After you install the ODBC database driver on UNIX, you need to configure the odbc.ini file.

Field Description

Data Source Name Enter the name for the ODBC data source.

Service Host Enter the name of the host machine or IP address on which the data source is running. If AQS is running on your local machine, enter localhost.

Service Port Enter 19988 or the port number provided by your server administrator.

Service Data Source Enter the name of the data source. The default data source name is xmlArchive. If a new data source has been added, enter the name of the new data source.

Field Expected value

Data Source User Name The login that has been assigned for you.

Data Source Password The password that has been assigned for you.

Custom Properties Leave blank.


When you install the ODBC driver on UNIX, a default ODBC data source named AQS is automatically configured to communicate with the server that is already installed. If you want to use a different data source name, connect to a different server, or if the server is not installed, you need to configure the driver by editing the odbc.ini file.

1 Navigate to the directory that contains the ODBC driver installation file, oadriver.sh.

Example cd <install_directory>/obt/bin

where <install_directory> is the location where you installed the software.

2 Start the installation file.

Example ./oadriver.sh

3 Select Option 1 to install the driver.

The ODBC driver is installed.

Creating additional ODBC data sources on UNIX

If you create additional server data sources (databases), you will need to create a corresponding data source for each server that you want to access through ODBC. Perform the following steps to create additional ODBC system data sources.

1 Navigate to the directory that contains the odbc.ini file.

Example cd <install_directory>/obt/sqlxml/client/


2 Open the odbc.ini file with a text editor, and locate the [ODBC Data Sources] and [ODBC Data Source] sections.

Example of odbc.ini file [ODBC Data Sources]ODBC Data Source=DataDirect OpenAccess SDK 6.0[ODBC Data Source]Driver=/home/myhome/sqlxml/client/lib64/ivoa22.soDescription=DataDirect OpenAccess SDK 6.0Host=Port=ServerDataSource=ODBC Data SourceUseLDAP=0DistinguishedName=Encrypted=0LoadBalancing=0AlternateServers=ConnectionRetryCount=0ConnectionRetryDelay=3CustomProperties=

3 Replace the values in bold:

[ODBC Data Sources]ODBC_datasource_name=DataDirect OpenAccess SDK 6.0[ODBC_datasource_name]Driver=/home/myhome/sqlxml/client/lib64/ivoa22.soDescription=DataDirect OpenAccess SDK 6.0


Host=<host_name>Port=<port_name>ServerDataSource=<server_datasource_name>

4 Save the odbc.ini file.

Uninstalling the ODBC database driver

You can use the oadriver script to uninstall the ODBC database drivers.

1 Navigate to the directory that contains the ODBC driver installation file.

Example cd <install_directory>/obt/bin

where <install_directory> is the location where the software was installed.

2 Run the appropriate script:

3 Select Option 2 to uninstall the driver.

The ODBC database driver is uninstalled.

Connecting to the archive data source

You can connect to the archive data source using ODBC, ADO OLE-DB, or JDBC, clients. The JDBC database driver is included in the AQS installation, so the JDBC driver does not need to be configured. The connection string syntax

Value Description

ODBC_datasource_name The name for the ODBC data source. The default data source name is AQS32 or AQS64 on Windows, and AQS on UNIX.

host_name The name of the host machine or IP address on which AQS is running.

port_name The port number used by the remote AQS you are connecting to. The default value is 19988.

server_datasource_name Enter the name of the data source (database). The default data source name is xmlArchive. If a new data source has been added, enter the name of the new data source.

Platform Syntax

DOS oadriver.bat

UNIX ./oadriver.sh


includes all of the connection information required to connect to any data source. In a client-only installation, the oaisql client asks for connection specific information each time you run the program.

If you want to use an ODBC or OLE DB client, you need to install and configure the driver first.

See also Installing and configuring the ODBC driver (page 10)

By default, the data source (database) for AQS is called AQS64 or AQS32. If you create a different data source for AQS, substitute the correct name.

This section assumes that AQS is installed and running.

In this section Connecting using the Interactive SQL (JDBC) client (page 15)

Connecting using the Interactive JDBC client (ij) (page 16)

Connecting using the Interactive SQL (ODBC) client (page 18)

Changing your password (page 19)

Connecting using the Interactive SQL (JDBC) client

1 Navigate to the directory that contains the oaisql program.

Example <install_directory>/obt/bin/


2 Start the Interactive SQL(JDBC) program from the command line. If you are prompted for the server port number or server host name or both, enter the value and name provided by the server administrator.

3 Connect to the server on Windows using the following syntax:

connect <username>*<password>@<host>:<port>; ServerDataSource=<datasource_name>;

On UNIX, you enter a URL.

Platform Use

UNIX oaisql.sh

Windows oaisql.bat


NOTE If you want to connect to the default data source, you can omit ServerDataSource=<datasource_name>; from the connect command.

Example connect install*OA@localhost:19988; ServerDataSource=xmlArchive;

Connecting using the Interactive JDBC client (ij)

1 Start Interactive JDBC (ij).


2 Connect to the driver using the following syntax:

driver 'com.hp.jdbc.openaccess.OpenAccessDriver';

3 Connect to the server using the following syntax:

connect 'jdbc:aqs://<host>:<port>;serverDataSource=<datasource_name>;user=<username>;password=<password>';

Parameter Description

username The name of the user with permissions to connect to the data source. The default value is install.

password The password for the user. The default value is OA. Passwords for oaisql cannot contain the @ character.

host The name of the host machine or IP address on which the data source is running. If you are running on your local machine, enter localhost.

port The port number used by AQS on the host machine. The default value is 19988.

datasource_name The name of the server data source to which you are connecting to. The default value is xmlArchive.

Platform Command

UNIX ./<install_directory>/obt/bin/dbarch_sql.sh

Windows <install_directory>/obt/bin/dbarch_sql.bat


Example connect 'jdbc:aqs://localhost:19988;serverDataSource=xmlArchive for 64 bit;user=install;password=OA';

Connecting using a user-supplied JDBC client

To connect to the data source using a different JDBC client, you need to know the following information:


host The name of the host machine or IP address on which the data source is running. If you are running on your local machine, enter localhost.


datasource_name The name of the data source you are connecting to. The default value is xmlArchive for 64 bit or AQS32 for 32 bit.

username The name of the user with permissions to connect to the data source. The default value is install.

password The password for the user. The default value is OA.

Connection information Expected value

driver location <install_directory>/obt/lib/HPjc.jar


driver name com.hp.jdbc.openaccess.OpenAccessDriver

JDBC URL jdbc:aqs:<host>:<port>;serverDataSource= <datasource_name>;user=<username>;password= <password>Example: jdbc:aqs://localhost:19988;serverDataSource=xmlArchive for 64 bit;user=sample;password=samplePassword


host The name of the host machine or IP address on which the data source is running.



Connecting using the Interactive SQL (ODBC) client

This section assumes that AQS is installed and running, and that the ODBC driver has been configured.


— Archive Query Server32 > Interactive SQL (ODBC)

— Archive Query Server64 > Interactive SQL (64-bit ODBC)

Alternatively, you can use the following commands to start the client.

In a client-only installation, you may be prompted for a server port and server host. Enter values as supplied by the server administrator.

NOTE With Interactive SQL, the create collection, drop collection, and drop schema statements require administrator privileges and a prefix of 'exec' or '!'. This is not required for Interactive SQL (JDBC) nor with other ODBC clients.

2 Connect to the xmlArchive for 64 bit or AQS32 for 32 bit data source.

Example connect install*OA@<ODBC_DataSource_Name>

where <ODBC_DataSource_Name> is xmlArchive for 64 bit or AQS32 for 32 bit, the default ODBC data source, or the name you chose for an ODBC data source that you created and configured yourself.

The install user account is created by default and must be used the first time you connect.

See also Viewing your collection from the Interactive SQL or ij clients (page 19).

datasource_name The name of the data source you are connecting to. The default value is xmlArchive.

username Optional. The name of the user with permissions to connect to the data source.

password Optional. The password for the user.


Platform Use

UNIX oaisql.sh -odbc

Windows oaisql.bat -odbc

oaisql.bat -odbc64

You can also find these in the Start menu.


Changing your password

You can change the password that was assigned by the administrator.

1 Start Interactive SQL or your JDBC client.

2 Type the appropriate command:

where <new_password> is the new password you want to use.

Viewing archive collections

You can view collections using these methods.

• Viewing your collection from the Interactive SQL or ij clients (page 19)

• Viewing your collection from Microsoft Office Excel (page 21)

• Viewing your collection from OpenOffice.org Base (page 23)

Viewing your collection from the Interactive SQL or ij clients

After connecting using Interactive SQL or ij, you can use different SQL queries to view the data in your collection.

To find out what schemas contain your data in, use one of the following queries:

select distinct schema from oa_collections;

or

select distinct table_owner from oa_collections;

You should see results similar to the following:

schemaMySchemaorders_d2f

To find out what tables have been created from your collections, use a query similar to the following:

select table_qualifier, table_owner, table_name fromoa_tables;

You should see results similar to the following, assuming that the data source name is xmlArchive:

Example table_qualifier table_owner table_nameSCHEMA SYSTEM OA_TABLES

Client Syntax

Interactive ODBC SQL exec password <new_password>;

JDBC client password <new_password>;


SCHEMA SYSTEM OA_COLUMNSSCHEMA SYSTEM OA_TYPESSCHEMA SYSTEM OA_INFOSCHEMA SYSTEM OA_STATISTICSSCHEMA SYSTEM OA_FKEYSSCHEMA SYSTEM OA_PROCSCHEMA SYSTEM OA_PROCCOLUMNSxmlArchive MySchema arcsum_MySchemaxmlArchive MySchema COMMPLANxmlArchive MySchema CUSTOMERxmlArchive MySchema ORDER_ATTACHMENTxmlArchive MySchema ORDER_HEADERxmlArchive MySchema ORDER_LINExmlArchive MySchema ORDER_LINE_DISTxmlArchive MySchema ORDER_PAYMENTxmlArchive MySchema ORDER_PAYMENT_LINExmlArchive MySchema ORDER_STARxmlArchive MySchema ORDER_TAXxmlArchive MySchema PRODUCTxmlArchive MySchema SALESREPxmlArchive MySchema STATUSxmlArchive MySchema STORExmlArchive OASYSTEM OA_COLLECTIONSxmlArchive OASYSTEM OA_CONNECTIONSxmlArchive OASYSTEM OA_FILESxmlArchive OASYSTEM OA_INDEXESxmlArchive OASYSTEM OA_OPTIONSxmlArchive OASYSTEM OA_PRIVILEGESxmlArchive OASYSTEM OA_RANGESxmlArchive OASYSTEM OA_ROLESxmlArchive OASYSTEM OA_TABLE_INFOxmlArchive OASYSTEM OA_USERSxmlArchive ORDERS_D2F arcsum_ORDERS_D2FxmlArchive ORDERS_D2F CUSTOMERxmlArchive ORDERS_D2F ORDER_ATTACHMENTxmlArchive ORDERS_D2F ORDER_HEADERxmlArchive ORDERS_D2F ORDER_LINExmlArchive ORDERS_D2F ORDER_TAXxmlArchive ORDERS_D2F PRODUCTxmlArchive ORDERS_D2F SALESREPxmlArchive ORDERS_D2F STATUS

• The table qualifier column is the name of the data source.

• The table owner column is the name of the cartridge used to archive the data, the schema name, or the user if you created a view.

• The SYSTEM and OASYSTEM tables are system tables that contain information on the contents of the searchable archive.

• The arcsum_<schema_name> contains all of the configuration and runtime parameter information for all collections within that particular schema.


Viewing your collection from Microsoft Office Excel

You can view your collection in Microsoft Office Excel using the ODBC driver. You can use the Query Wizard to select what data is displayed, or you can use Microsoft Query to write your own queries.

NOTE Most versions of Excel are 32-bit applications. If you are using a 64-bit version of Windows and a 32-bit version of Excel, you need to use a 32-bit ODBC driver or the 32-bit OLE DB driver. For more information, see Installing and configuring the ODBC database driver on Windows (page 10) and Connecting to the archive data source (page 14).

NOTE Microsoft Office Excel does not support binary data nor some Unicode text. If your data file contains either of these types of data, you will not be able to import that data into Excel.

TIP If an object name contains special characters or punctuation marks, use quotation marks around the object names and end the query with a semicolon.For example, the following code:

SELECT ORDER_HEADER.ORDER&ID, ORDER_HEADER.DEPTNO+1 FROM XMLARCHIVE.ORDER_OA.ORDER_HEADER

should be written as:

SELECT “ORDER_HEADER”.“ORDER&ID”, “ORDER_HEADER”.“DEPTNO+1” FROM “XMLARCHIVE”.“ORDER_OA”.“ORDER_HEADER” “ORDER_HEADER”;

In this section: • Viewing your collection using the query wizard (page 21)

• Viewing your collection using Microsoft Query (page 22)

Viewing your collection using the Query Wizard

1 Ensure the ODBC database driver has been installed and configured.


2 Start Microsoft Office Excel.

3 Navigate to the Choose Data Source window.

Example Data > From Other Sources > From Microsoft Query

4 Select the name of your ODBC source from the list of values.

5 Select the Use the Query Wizard to create/edit queries checkbox.

6 Click OK.

The OpenAccess Login window opens.

7 Enter the user name and password for your user account.


The Query Wizard - Choose Columns window opens.

8 Select the tables or columns you want to include.

9 Click Next. The Query Wizard - Filter Data window opens.

10 Optionally, specify a filter to restrict the data.

11 Click Next. The Query Wizard - Sort Order window opens.

12 Optionally, specify the columns you want to use to sort the data.

13 Click Next. The Query Wizard - Finish window opens.

14 Specify how the data should be returned into Excel.

15 Click Finish. The Import Data window opens.

16 Specify where you want the data returned.

17 Click OK. The XML archive data is loaded into the spreadsheet.

Viewing your collection using Microsoft Query

1 Ensure the ODBC database driver has been installed and configured.


2 Start Microsoft Office Excel.

3 Navigate to the Choose Data Source window.

Example Data > Import External Data > New Database Query

4 Select the name of your ODBC source from the list of values.

5 Make sure the Use the Query Wizard to create/edit queries checkbox is not selected.

6 Click OK.

The OpenAccess Login window opens.

7 Enter the user name and password for your user account.


Data Source User Name install

Data Source Password OA



Data Source User Name Your user name

Data Source Password Your password



The Microsoft Query window opens.

8 Enter a SQL query into Microsoft Query.

Example select * from oa_users

The results of the query are displayed in Excel.

Viewing your collection from OpenOffice.org Base

You can view your connection in OpenOffice.org Base using either the ODBC or JDBC driver. The JDBC driver is generic to 32-bit and 64-bit Windows. If you are running on 64-bit Windows and using ODBC, you must use the appropriate driver to match the bit size of the application that is using the driver. For example, you must use a 32-bit driver with 32-bit applications and a 64-bit driver with 64-bit applications.

In this section • Connecting to OpenOffice.org Base using ODBC (page 23)

• Connecting to Open Office.org Base using JDBC (page 24)

Connecting to OpenOffice.org Base using ODBC

1 If you plan on using the ODBC driver, ensure that the ODBC database driver has been installed and configured.


2 Make sure the server process is running.

3 Start the OpenOffice.org Base program.

The Database Wizard window opens.

4 Connect to the database.

a Click the Connect to an existing database radio button.

b Select ODBC from the list of values.

c Click Next.

d On the Set up a connection to an ODBC database page, click Browse. The Data Source window opens.

e Select the data source and click OK to close the window.

f Click Next to select the data source.

g Enter the user name and select the Password required checkbox. The default user name is install, and the password is OA.

h Click Test Connection.

The login window opens.

i Enter the password and click OK to test the connection.

j Click OK on the confirmation dialog.

k Click Finish.


5 Save the data source as an ODBC database.

The Base program is connected to the server and ready to use.

Connecting to Open Office.org Base using JDBC

1 Ensure the server process is running.

2 Start the OpenOffice.org Calc program.

3 Configure the Java options.

a Select Tools > Options.

The Options window opens.

b Select Java from the navigation tree.

c Click Class Path. The Class Path window opens.

d Click Add Archive.

e Navigate to the following directory:

<install_directory>/obt/lib

f Select oajc.jar and click Open.

g Click OK on the Class Path window.

h Click OK to return to the Calc program.

4 Exit all OpenOffice.org programs.

5 Start the OpenOffice.org Base program. The Database Wizard window opens.

a Click the Connect to an existing database radio button.

b Select JDBC from the list of values, then click Next.

c Enter the following JDBC database connection values:

See also Connecting using a user-supplied JDBC client (page 17)

d Click Next.

e Enter the user name and select the Password required checkbox. The default user name is install, and the password is OA.

f Enter the password and click OK to test the connection.

g Click OK on the confirmation dialog.

h Click Finish.


Datasource URL jdbc:aqs://<host>:<port>;serverDataSource=<datasource_name>;

JDBC driver class com.hp.jdbc.openaccess.OpenAccessDriver


6 Save the data source as a JDBC database. The Base program is connected to the server and ready to use.


2 Integrating the AQS client with databases

Integrating the AQS client allows you to view and query data that has been archived using database to XML archiving from within your databases.

• Viewing your archived data from Oracle (page 30)

• Viewing your archived data from SQL Server (page 37)

• Searching by schema (page 39)

Viewing your archived data from PostgreSQL

On UNIX, with PostgreSQL foreign data wrappers, you can access any data that AQS can access as PostgreSQL tables. You can thus use advanced SQL functions on the data and even join it with the source data for advanced querying.

• Installing the JDBC foreign data wrappers (page 27)

• Creating postgreSQL tables (page 28)

• Querying your data from postgreSQL (page 29)

Installing the JDBC foreign data wrappers

You can find the installation instructions for the JDBC foreign data wrappers here:

https://github.com/atris/JDBC_FDW/wiki/Installation-instructions

The main steps are:

1 Install PostgreSQL using a source installation.

2 Put the JDBC foreign data wrappers source in the PostgreSQL contrib directory.

3 Perform a make clean.

4 Create a symbolic link of libjvm.so to /usr/lib. You can use the HP Structured Data Manager version of libjvm for this purpose:

<install directory>/jre/lib/<platform>/server/libjvm.so

5 For 64-bit Linux, se LD_LIBRARY_PATH to a 7.0_03 or later JRE, such as/usr/java/jdk1.7.0_03/jre/lib/amd64/server.

6 As root, perform a make install.


Creating postgreSQL tables

As a one-time setup for your PostgreSQL server, run these commands as a PostgreSQL super user, for example, postgres:

1 Install the JDBC foreign data wrappers with the following command:

CREATE EXTENSION jdbc_fdw;

1 Create a server that uses the JDBC FDW:

CREATE SERVER jdbc_server FOREIGN DATA WRAPPER jdbc_fdw OPTIONS(drivername 'com.hp.jdbc.openaccess.OpenAccessDriver',url 'jdbc:aqs://host:19988;serverDataSource=xmlArchive',querytimeout '90', jarfile 'sdm-install-dir/lib/HPjc.jar', maxheapsize '640')

where:

jdbc_server is the name you want to give the server. You can define multiple servers to access multiple AQS servers on multiple hosts.

URL host, port number and server data source name must be changed to match your installation.

querytimeout is in seconds and you can modify its value as desired.

jarfile is located in the HP Structured Data Manager <install directory>/lib/HPjc.jar. Supply the full path name to the file.

maxheapsize is in megabytes and is optional.

2 Create a user mapping for any user who will use the foreign data wrappers and supplies valid credentials for that user. Note that when passwords are changed on AQS you must re-run the user mapping to change the password on the PostgreSQL side. For example, to connect user postgres as the DBA install role on AQS, you would use:

CREATE USER MAPPING FOR postgres SERVER jdbc_server OPTIONS (USERNAME 'install', PASSWORD 'OA');

3 You can verify installation of the commands by using some psql commands:

— \dx shows installed extensions and jdbc_fdw should display.

— \des shows foreign servers.

— \dew shows all foreign data wrappers.

— \deu shows user mappings (does not show remote user or password).

Creating PosgreSQL table access to the foreign data wrappers

PostgreSQL foreign data wrappers provide the information to make a connection between servers, but, in order to access the data in AQS tables or views, you need to define a PostgreSQL foreign table to select the AQS data. You enter the CREATE FOREIGN TABLE statement with an appropriate PostgreSQL data type

28 Chapter 2: Integrating the AQS client with databases

for each field returned. You can start with text and then change to more specific data types as needed. Remember that AQS can change data types for you as well, using any combination of set option type or CAST() or CONVERT().

For example:

create foreign table order_header(ORDERID int4,CUSTOMERID int4,ORDERDATE text,SHIPDATE text,COMMPLANID int4,SALESREPID int4,TOTAL int4,STATUSID int4)server jdbc_serveroptions (query 'select * from order_header');

You must use a create foreign table statement for every table that you want to access. You can look at PostgreSQL metadata about foreign tables using the following commands:

— \d lists relations in the database, including foreign tables.

— \dE lists domains.

— \det lists foreign tables.

— \d <table name> looks at foreign table’s structure.

Querying your data from postgreSQL

After the foreign table is defined you can query data from it just as you would query any other table. However, bear in mind the following:

— The (remote) AQS server must be running.

— A local AQS JDBC driver must be installed. This driver is installed automatically by either installer, the AQS server or AQS client.

— The query sent to AQS is the query in the foreign table definition rather than the query used to query the foreign table.

Since the query sent to AQS is the query as defined in the foreign table, not a specific query that uses the foreign table, the cost can be very high for querying large tables. For example, if the order_header table has an index on the ORDERID column but the foreign table query is defined as select * from order_header, then the index will never be used. The query sent to AQS is always select * from order_header, even if the foreign table is queried with a statement like select * from order_header where orderid = 93218. All rows of the order_header table will be returned to postgreSQL for processing.


Viewing your archived data from Oracle

With Oracle’s Heterogeneous Services, you can access XML data from within an Oracle database. This enables you to use advanced SQL functions against the data, and even join the data with your source data for advanced querying.

TIP Oracle treats all columns and table names as upper-case. If your collection contains columns or tables that are in mixed-case or lower-case, you need to use double-quotes in your query. For example, select column_name from table_name returns a column called COLUMN_NAME, whereas select “column_name” from table_name returns a column called column_name.

In this section • Configuring Heterogeneous Services (page 30)

• Configuring Heterogeneous Services for releases prior to Oracle 11g on 64-bit operating systems (page 32)

• Creating an ODBC database link (page 36)

• Querying your data from Oracle (page 36)

Configuring Heterogeneous Services

To configure the Heterogeneous Services agent process, you need to perform the following tasks:

• Creating an initialization file (page 30)

• Modifying the listener and tnsnames files (page 31)

NOTE If you are running a version of Oracle prior to 11g on a 64-bit operating system, follow the instructions in Configuring Heterogeneous Services for releases prior to Oracle 11g on 64-bit operating systems (page 32).

Creating an initialization file

To create the initialization file:

1 Navigate to the following directory:

$ORACLE_HOME/hs/admin

2 Create a new text file, initxmlArchive.ora, that contains the following text:


where <AQSODBC_install> is the directory where AQS ODBC drivers are installed.

NOTE For Oracle 11g, change the value of the HS_DB_NAME entry to dg4odbc.

If you use a different name than xmlArchive for 64 bit for the data source, edit the file and the file name accordingly. For example, if you used the name MyArchive, the file should be named initMyArchive.ora, and the value for HS_FDS_CONNECT_INFO should be MyArchive.

You can use the path variable appropriate to your environment to point to the necessary library for your operating system.

Modifying the listener and tnsnames files

To set up the listener on the agent to listen for incoming requests from the Oracle Database server:

1 Navigate to the directory that contains the listener.ora file.

Mode Use

UNIX HS_FDS_CONNECT_INFO = xmlArchive for 64 bit

HS_AUTOREGISTER = TRUE

HS_DB_NAME = hsodbc

HS_FDS_SHAREABLE_NAME = <AQSODBC_install>/obt/sqlxml/client/lib/libodbc.so

set ODBCINI=/<AQSODBC_install>/client/odbc.ini

set LD_LIBRARY_PATH=/<AQSODBC_install>/client/lib:/<oracle-Home>/10.2.0.4/lib32

set OASDK_ODBC_HOME=/<AQSODBC_install>/client/lib

Windows set HS_FDS_CONNECT_INFO = xmlArchive for 64 bit

set HS_AUTOREGISTER = TRUE

set HS_DB_NAME = hsodbc

Operating System Path variable

IBM AIX LIBPATH

HP-UX SHLIB_PATH

HP-UX on Intel Itanium LD_LIBRARY_PATH

Linux LD_LIBRARY_PATH

Solaris LD_LIBRARY_PATH

MS Windows PATH


cd $ORACLE_HOME/network/admin

2 Edit the listener.ora file to add the entries for the data source, for example:

Example on MS Windows SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = xmlArchive for 64 bit) (ORACLE_HOME = C:\oracle\product\10.2.0\db_1) (PROGRAM = hsodbc) )

If you have a different Oracle home directory, edit the path accordingly.

NOTE For Oracle 11g, change the value of the PROGRAM entry to dg4odbc.

3 Ensure that the initialization parameter GLOBAL_NAMES is set to FALSE in the database initialization parameter file.

4 Edit the tnsnames.ora file to add the appropriate entry, for example:

TIP You do not need to add anything to tnsnames.ora on Windows.

Example on Unix HSALIAS = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<oracle_host>)(PORT=1521))(CONNECT_DATA=(SID=xmlArchive for 64 bit))(HS=OK))

5 Restart the TNS Listener. When a request is received, the agent now spawns a Heterogeneous Services agent.

You are now ready to create a database link as per the instructions in Creating an ODBC database link (page 36).

Configuring Heterogeneous Services for releases prior to Oracle 11g on 64-bit operating systems

When configuring AQS with Heterogeneous Services in releases prior to Oracle 11g on a 64-bit operating system, you must obtain and specify the 32-bit library:

• Obtaining the 32-bit ODBC driver (page 32)

• Specifying the path to the 32-bit library (page 33)

• Validating the 32-bit ODBC configuration (page 35)

Obtaining the 32-bit ODBC driver

You can have both the 32-bit and 64-bit ODBC drivers installed and use them at the same time. 32-bit applications (like pre-Oracle 11g Heterogeneous Services) require the 32-bit driver. The 64-bit application requires the 64-bit driver.


To get the 32-bit driver for your 64-bit platform, download and install the 32-bit client package for your platform. Be sure to install it in a different directory than the one where you keep the 64-bit driver. Make sure the environment settings for Heterogeneous Services point to the 32-bit driver directory, not the 64-bit driver directory.

If you cannot locate the 32-bit driver appropriate for your platform, contact HP Support.

Specifying the path to the 32-bit library

After you obtain and install the 32-bit ODBC driver, you must specify the 32-bit library using the path variable appropriate to your environment. The examples that follow illustrate how to specify the library path in different environments.

Example on Solaris On Solaris with Oracle 10.2.0.4, your initXMLArchive.ora in $ORACLE_HOME/hs/admin would look similar to the following:

HS_FDS_CONNECT_INFO = AQS32 for 32 bitHS_FDS_SHAREABLE_NAME = /<AQSODBC_install>/client/lib/libodbc.soset ODBCINI=/<AQSODBC_install>/client/odbc.iniset LD_LIBRARY_PATH=/<AQSODBC_install>/client/lib:/<oracle_home>/10.2.0.4/lib32set OASDK_ODBC_HOME=/<AQSODBC_install>/client/lib

where <AQSODBC_install> is the directory where the 32-bit AQS ODBC drivers for Solaris are installed.

Your listener.ora would look similar to the following:

SID_LIST_LISTENER = (SID_DESC = (SID_NAME = AQS32 for 32 bit) (ORACLE_HOME = /remote/app/oracle/ra11510o/10.2.0.4) (PROGRAM = hsodbc) (ENVS=LD_LIBRARY_PATH=/<AQSODBC_install>/client/lib: /<Oracle_Home>/10.2.0.4/lib32) )

Where <AQSODBC_install> is the directory where the 32-bit AQS ODBC drivers for Solaris are installed.

Your tnsnames.ora would look similar to the following:

HSALIAS = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=stingray)(PORT=1527)) (CONNECT_DATA=(SID=AQS32 for 32 bit)) (HS=OK) )

Your odbc.ini would look similar to the following:

[AQS32 for 32 bit]Driver=/<AQSODBC_install>/client/lib/ivoa22.soDescription=DataDirect OpenAccess SDK 6.0Host=stingrayPort=19988ServerDataSource=AQS


UseLDAP=0DistinguishedName=Encrypted=0LoadBalancing=0AlternateServers=ConnectionRetryCount=0ConnectionRetryDelay=3CustomProperties=[ODBC]Trace=0IANAAppCodePage=4TraceFile=odbctrace.outTraceDll=/<AQSODBC_install>/client/lib/odbctrac.soInstallDir=/<AQSODBC_install>/client

Where <AQSODBC_install> is the directory where the 32-bit AQS ODBC drivers for Solaris are installed.

Example on AIX On AIX with Oracle 10.2.0.4, your initXMLArchive.ora would look similar to the following:

HS_FDS_CONNECT_INFO=AQS32 for 32 bitHS_FDS_SHAREABLE_NAME=/home/verducci/TIGER/AQS_CLIENTS/obt/sqlxml/client/lib/libodbc.soset ODBCINI=/home/verducci/TIGER/AQS_CLIENTS/obt/sqlxml/client/odbc.iniset OASDK_ODBC_HOME=/home/verducci/TIGER/AQS_CLIENTS/obt/sqlxml/client/libset LIBPATH=/home/verducci/TIGER/AQS_CLIENTS/obt/sqlxml/client/lib:/remote/app/oracle/product/10.2.0.4/lib32


(SID_DESC = (SID_NAME = AQS32 for 32 bit) (ORACLE_HOME = /remote/app/oracle/product/10.2.0.4) (PROGRAM = hsodbc) (ENVS=LIBPATH =/home/verducci/TIGER/AQS_CLIENTS /obt/sqlxml/client/lib:/remote/app/oracle/product /10.2.0.4/lib32) )

Your tnsnames.ora would look similar to the following:

HSALIAS = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=tiger)(PORT=1523)) (CONNECT_DATA=(SID=AQS32 for 32 bit)) (HS=ok) )

Your odbc.ini would look similar to the following:

[ODBC Data Sources]AQS=DataDirect OpenAccess SDK 6.0[AQS32 for 32 bit]QEWSD=40049Driver=/home/verducci/TIGER/AQS_CLIENTS/obt/sqlxml /client/lib/ivoa22.soDescription=DataDirect OpenAccess SDK 6.0


Host=tigerPort=19989ServerDataSource=AQSUseLDAP=0DistinguishedName=Encrypted=0LoadBalancing=0AlternateServers=ConnectionRetryCount=0ConnectionRetryDelay=3CustomProperties=[ODBC]Trace=0IANAAppCodePage=4TraceFile=odbctrace.outTraceDll=/home/verducci/TIGER/AQS_CLIENTS/obt/sqlxml /client/lib/odbctrac.soInstallDir=/home/verducci/TIGER/AQS_CLIENTS/obt/sqlxml /client

Example on MS Windows On Microsoft Windows with Oracle 10.2.0.4, your initXMLArchive.ora would look similar to the following:

HS_FDS_CONNECT_INFO = AQS32 for 32 bitHS_AUTOREGISTER = TRUEHS_DB_NAME = hsodbc


(SID_DESC = (SID_NAME = AQS32 for 32 bit) (ORACLE_HOME = C:\oracle\product\10.2.0\db_2) (PROGRAM = hsodbc) )

TIP You do not need to add anything to tnsnames.ora on MS Windows.

Validating the 32-bit ODBC configuration

To ensure that your 32-bit ODBC drive is installed and working properly in your environment, perform the following checks:

• From the directory where oaisql is installed, run oaisql.[sh|bat] -odbc, then connect to the ODBC data source you configured.

For example, if your odbc.ini specifies the name of the ODBC data source as AQS, you issue the connect command:

connect user*password@AQS32 for 32 bit

Run a query to verify operation, such as select * from oa_options. If ODBC is working, then Oracle Heterogenous Services should be able to read from the AQS ODBC driver.

• If Heterogeneous Services has never been configured on your Oracle instance, use the server manager or SQL*Plus to log in as sys and run the caths.sql script, which is located in ORACLE_HOME/tdbms/admin. This script should configure the objects necessary to support running Heterogeneous Services.


• Ensure that your Oracle listener is configured properly:

NOTE Configuring and testing Heterogeneous Services setup with TNS and Oracle listeners does not require Heterogeneous Services to be configured for a specific connection.

After proper configuration of tnsnames.ora and listeners.ora, and restarting the listeners, you can run "lsnrctl services [name]" to verify that the listeners have a service handler configured for Oracle HS. You should see output similar to:

LSNRCTL> servicesConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXPPROC)))Services Summary...Service "AQSxml" has 1 instance(s).Instance "AQSxml", status UNKNOWN, has 1 handler(s) for this service…Handler(s):"DEDICATED" established:1 refused:0LOCAL SERVERThe command completed successfully

• If the listener is up and running, and ODBC is working, then the only other pieces that might be an issue are the Heterogeneous Services configuration and database link creation.

Creating an ODBC database link

1 Invoke SQL*Plus and log in as a user with privileges to create a database link. For example, SYSTEM.

2 Create a database link using the following syntax:

create public database link xmlArchive connect to "install" identified by "OA" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOST>) (PORT=<PORT>))(CONNECT_DATA=(SERVICE_NAME=xmlArchive for 64 bit)) (HS=OK))';

Related information See your Oracle documentation.

Querying your data from Oracle

1 Invoke SQL*Plus and log in as a user with privileges to access the xmlArchive data.

Parameters Description

HOST The name of the machine where Oracle is installed.

PORT The port number for your Oracle database that the TSN Listener is connected to. For example, 1521.


2 Query your data using the database link.

Example SELECT * FROM order_header@xmlArchive for 64 bit;

TIP In Oracle10g and earlier, Unicode data types are not supported by Oracle Heterogeneous Services. To get support for Unicode data types, you must upgrade to Oracle Gateway 11g (DG4ODBC).

Viewing your archived data from SQL Server

Using a linked server configuration allows you to access archived data from within SQL Server. This enables you to use advanced SQL functions against the data, and even join the data with your source data for advanced querying.

In this section • Testing system level ODBC connectivity (page 37)

• Creating the ODBC connection (page 37)

• Querying your data from SQL Server (page 38)

Testing system level ODBC connectivity

Before creating your ODBC connection, you should validate that you have ODBC connectivity at the system level:

1 Install the ODBC driver as per the instructions in Installing and configuring the ODBC database driver on Windows (page 10).


— Archive Query Server32 > ODBC Administrator

— Archive Query Server64 > 64-bit ODBC Administrator.

TIP If you access the ODBC Administrator dialog through the Control Panel, you need to ensure that you open the correct dialog for your driver (32-bit or 64-bit). Using the Start menu options above, makes it much easier to find the correct ODBC Data Source Administrator dialog for your driver.

The ODBC Data Source Administrator displays.

3 Click the System DSN tab.

4 Select the AQS driver and click Configure.

5 Click Test Connection. If the connection succeeds, you can proceed with Creating the ODBC connection (page 37). Otherwise, review your ODBC driver installation and configuration to check for errors.

Creating the ODBC connection

1 Start the SQL Server Management Studio.


2 Expand the Server Objects node.

3 Right-click Linked Servers and select New Linked Server. The New Linked Server window opens.

4 On the General panel, enter or select the following information:

5 On the Security panel, click Add.

6 Enter or select the following information for the local server login, but leave the Impersonate check box unchecked.

7 Click OK.

Querying your data from SQL Server

1 Start the SQL Server Management Studio.

2 Expand the Databases node.

3 Right-click the database you configured and select New Query.

4 Query your data using the ODBC connection.

Example select * from <remote_link_name>.<xml_server_name>. <cartridge_name>.<table_name>


Linked server XML_ARCHIVE

Server type Other data source

Provider Microsoft OLE DB Provider for ODBC Drivers

Product name Choose a name for the product. For example, sqlXML.

Data source xmlArchive for 64 bit

Provider string leave blank

Location leave blank

Catalog leave blank


Local Login Enter the login for the server you want to access the XML server. For example, sa.

Remote User Enter the user name of the server. For example, install.

Remote Password Enter the password for the user you selected. For example, OA.


Example select * from XML_ARCHIVE.xmlArchive.SalesOrderOA.Customer

Searching by schema

The searchSchema option allows you to specify which schema you want to search. Any user can use the searchSchema option, and it is only valid for that individual user:

set searchSchema <your_chosen_default_schema_name>

By setting this option, you can use fully qualified schema names in your queries against the archive data file.


remote_link_name The name of the linked server specified in Creating the ODBC connection (page 37). For example, XML_ARCHIVE.

xml_server_name The name of the XML server. For example, xmlArchive.

cartridge_name The name of the database to file archiving cartridge.

table_name The name of the table you want to query. For example, Customer.


Glossary

active database The database from which you plan to move or copy data. Typically, this database is your online transaction processing (OLTP) or production database. In a two- or three-tiered configuration, the active database resides on tier one and is the source for data movement operations.

active environment The Web Console views and acts upon only one environment at a time, the active environment. To switch the active environment, you use the Change Active option in the Web Console.

activity In Designer, a component of a business flow, which is added by using the toolbar. For example, you can add archive and reload activities to your business flow. Note, activities in a business flow are different from what you see at runtime and therefore do not necessarily map directly to what you see in the Web Console.

advanced selection A method of data selection that discovers all of the interrelated rows from multiple tables and conceptually places them in the same application partition for archiving.

annotation In Designer, a comment associated with the project, or one of its objects or components. These comments are collected and published in a PDF file when you right click a project or business flow and select Generate Documentation.

application partitioning The concept of partitioning related rows together during data selection, regardless of whether they are in one or more tables. Application partitioning is unique to HP Structured Data Manager and contrasts with the more common table partitioning offered by the database management software, which only groups related rows from one table.

application retirement At the end of an application’s lifecycle, identifying and copying application data from a production, relational database to CSV or XML files in a target location for long term retention.

archive data store The location where the data is to be archived. Can be a separate database, separate space on the same database, or an XML file. In a two-tiered configuration, the archive data store resides on tier two and can be a database or XML. In a three-tiered configuration, the archive data store is a database on tier two and XML on tier three, and is the target for data movement operations.

archive query server The component that provides SQL access to XML database archives.

Autonomy Consolidated Archive (ACA)

A managed, scalable repository that consolidates electronic communications, attachments, and files, and provides complete control over corporate information

HP Structured Data Manager 41

assets, facilitating compliance with internal corporate governance policies and procedures as well as externally mandated laws and regulations.

bulk data movement A method of data movement that is optimized for moving large amounts of data into an archive data store. With bulk, all of the eligible data is removed from the active database during a production outage (the application is offline and not accessible to users).

business flow A series of activities, such as archive operations and scripts, that run in sequence. You build business flows in Designer.

business flow status The Web Console shows the last run of each business flow. The states are Complete/Error/Running.

cartridge An instance of model- or schema-based eligibility criteria used to move or copy data from one location to another. Cartridges capture the application and business rules to ensure referential integrity of the data. For any one model in your project, you may have many cartridges that use it.

chaining table The lower level table in a many-to-one or a many-to-many relationship between higher level and lower level tables in the model hierarchy.

classification The HP Records Manager classification to be applied to the data moved by HP Structured Data Manager. This classification specifies where to place the data when it is ingested by HP Records Manager. For more information, refer to the HP Records Manager documentation.

collection The configuration of a directory location and file pattern to match a set of archived XML files, thus allowing SQL access to the archived data.

comma separated values (CSV)

A database-to-file output format that stores the data as values separated by commas and a metadata file. Each line in the CSV file corresponds to a row in a table. Within a line, fields are separated by commas, each field belonging to one table column. CSV files provide a simple format that many applications can import.

command Command files or JavaScript files launched by the Web Console on your behalf with status displays.

condition In Designer, the way you branch your business flow to run or skip an activity based on some criteria.

configuration parameter A type of parameter that has its values set by an administrator (someone who has repository privileges from the Web Console) through the administrator interface. Typically, this type of parameter represents values that should be changed very infrequently, perhaps only at deployment time.

console user The Web Console identifies individual users, who are distinct from database users. The properties for a user are User Name, Full Name, Password, Enabled, Description, Email, Phone, and Privileges.

42 Glossary

console user name The login name associated with a Web Console user.

constraint A column or a list of columns that enables you to identify rows in the database and relate them to one another.

custom properties User-created name/value pairs in cartridges and business flows. These values are exposed in the runtime as parameters.

customization A change that an administrator or DBA makes to a project provided by a third party, typically for a packaged application like Oracle PeopleSoft or Oracle E-Business Suite. As long as the customization is allowable by the project, the user can merge the customization into newer revisions of the third party project.

customization mode A Designer mode that provides visual cues to indicate customizations in the model. In a project with locked files, customization mode is on by default, but you can toggle it on and off from the toolbar in the model editor.

data access cartridge A cartridge that provides lightweight query access to retired or archived data. Data access cartridges are designed by the archive developer but can be run by business users with no technical expertise.

data masking The process of replacing private or confidential data during movement with a specified mask. You can choose from pre-defined masks that are part of HP Structured Data Manager or create your own mask. A mask may or may not be reversible upon reload from the archive data store.

data movement The method used by HP Structured Data Manager to actually move data (transactional, bulk or partitioned for database to database, and copy or archive for database to file).

data transparency The ability to access archived data through your standard application interfaces for data access. Data transparency enables users to access archived data as though it were still in the active database.

database constraint A constraint that exists in the database and can be discovered and referenced from Designer.

database to database A movement in which data goes from an active database to an archive database, or separate tablespaces inside the active database. Typically, the archive database is located on cheaper storage devices.

database to file A movement in which data goes from an active database to a file (XML or CSV format), which is offline but still accessible through SQL using the archive query server and a client tool of your choice.

deployment assistant The user interface component within Designer used to deploy or generate business flows.

description A technical description created by the developer for her own reference. These descriptions do not appear in the generated PDF file for the cartridge or business flow.


Designer The user interface component used to develop, test, and deploy your archiving solution. Designer is a powerful graphical development environment for archive solutions.

distributed instance A configuration option for database-to-database archiving where the data you archive is stored on a separate database from the source or active database.

DRE See Dynamic Reasoning Engine (DRE).

driving table A driving object is a root of a model hierarchy. Its relationship to the child tables drives the selection of transactions.

dynamic list of values A list of values for a parameter that obtains its members from a SELECT statement that returns identifiers and labels.

dynamic parameter A type of parameter that has its value set by a Groovy script that runs at deployment time to obtain a value. For example, this type of parameter can supply the type or version of a database or application, which can be obtained programmatically at deployment time.

Dynamic Reasoning Engine (DRE)

A platform technology that uses high performance pattern-matching algorithms to search for content stored in Autonomy repositories. Performs core information operations for contextual analysis and concept extraction, enabling solutions for the categorization, summarization, personalization, hyperlinking, and retrieval of all forms of information.

environment The source and (optional) target credentials against which you plan to run commands. You can define multiple environments within your installation to identify various source and target databases.

error One of the ways in which you can interrupt a business flow. Error indicates that the business flow failed for some reason.

exclusive rules One of the ways in which HP Structured Data Manager determines whether to include or exclude rows from the archive operation. Exclusive rules require all rows in the constraint table to match for inclusion. Exclusive rules exclude the instance if the condition on any child is false, like STATUS=’CLOSED’.

exit One of the ways in which you can interrupt a business flow. You can exit successfully or with a warning.

export The way that you save an HP Structured Data Manager project to an exchange format (.hdp) from the File menu. See also import.

export data The way that a user can send data to CSV format from Preview using the toolbar item.

generate documentation The process of collecting and grouping all annotations into a PDF file that also describes the business flow or cartridge structure.

44 Glossary

history schema For database-to-database archiving, the schema in the target database where the archived data is stored.

IDOL See Intelligent Data Operating Layer (IDOL) (page 45).

import The way that you transfer projects from exchange format (.hdp) into the Project Navigator. You can also use import to migrate cartridges created in 5.1 to 6.x. See also export.

inclusive rules One of the ways in which HP Structured Data Manager determines whether to include or exclude rows from the archive operation. Inclusive rules require only one row in the constraint table to match the rule and be included. Inclusive rules include the instance if the condition on any child is true, like PRODUCT_RECALLED=’Y’.

indexing cartridge A cartridge that indexes your data for better searching. For example, you might associate an indexing cartridge with a database-to-file archiving cartridge to improve performance when querying the archive data files.

Intelligent Data Operating Layer (IDOL)

An information processing layer that collects indexed data from connectors and stores it in a structure optimized for fast processing and retrieval, integrating unstructured, semi-structured, and structured information from multiple repositories.

interrupt The way to stop or pause a business flow (pause, error, exit with warning, exit successfully).

local deployment The generation and deployment of your cartridge or business flow to an environment on your local, Designer client. Deployment files are generated locally and then deployed to the designated, local environment.

lookup table A table that contains helpful non-transactional information. For example, non-transactional information could be status definitions, or the name of the sales representative.

managed table A table in the model that is copied and then purged from the active database by a cartridge. Transactional, chaining, and driving tables in a model are all typically managed tables.

model A model identifies the tables and table relationships representing a business entity or related business entities. A project can have multiple models. Each model contains a driving table and all of its child and descendent tables.

model compatibility Each model in your project can have one or more dynamic parameters associated with it to verify the compatibility with the target environment. If the compatibility parameter returns false, then the cartridge referencing the model will not deploy or run and throw an error. For example, the script could return false for Oracle 10.2 and true for Oracle 11.1 to indicate that a cartridge referencing the model can only deploy and run against Oracle 11.1.


model-based cartridge A cartridge that moves data based upon a defined data model with relationships. This type of cartridge is typically used for ongoing archive operations.

non-intrusive environment In a non-intrusive environment, data is archived without an interface schema and a generic JDBC driver is used. A non-intrusive environment enables you to copy or archive data from read-only sources, which is especially helpful in cases where the data is associated with older technologies that might not support basic SQL statements or when the database administrator or company policy prohibits write access to the production environment.

OLTP database The online transaction processing database that typically is your active or source database.

partitioned data movement A method of data movement that, whenever possible, will leverage Oracle database partitioning to move data more efficiently than transactional data movement.

pause One of the ways in which you can interrupt a business flow. Pausing suspends the business flow while awaiting operator intervention.

Records Manager Enterprise document and records management software designed to simplify the capture, management, security, and access to information. Records Manager enables organizations to more easily comply with regulations and corporate policies, and it helps secure information from inappropriate access and misuse.

reload The act of taking data from an archive data store and loading it into the active database.

remote deployment The generation and deployment of your cartridge or business flow to an environment on a system that is remote from your Designer client. Deployment files are generated locally and then deployed to the designated, remote environment.

repository The location that holds business flow metadata, product configuration data, and data collected during runtime. The repository can be located on your active database or another logical database.

rule Qualifications added to the model in order to include or exclude data based on certain criteria. For example, you might add a rule to exclude from archiving any orders that are not yet closed.

runtime parameter A type of parameter that has its values set by the operator executing the job in Console or on the command line. Typically, this type of parameter represents operational values that tend to change frequently and therefore need to be set each time the job is run.

schema-based cartridge A cartridge that moves data based upon the database schema rather than a defined data model with relationships. This type of cartridge is typically used for database retirement or the cleanup of orphan tables.

46 Glossary

selection The form of data selection to use (standard or advanced) for choosing data. When deploying a cartridge or adding it to a business flow, you must specify the selection method.

single instance A configuration option for database-to-database archiving where the data you archive is stored on the same database (Oracle) or the same server (SQL Server) as the source or active database.

source The location (database) from which you are copying or moving data.

SQL access server See archive query server.

standard selection A method of data selection that restricts itself to the rows identified by the model. Unlike advanced selection, it does not attempt to traverse related rows across multiple tables.

structured records management

A type of solution that extracts structured data from a source application and moves it into XML format. The XML is then ingested into the corporate records management system for long term management and eventual disposal according to corporate policy.

table use A database table, view, or synonym that is referenced in Designer, for example, in the model. The same table can be used multiple times in a model. For example, a table could be appear as a transactional table and a lookup table in the same model.

target The location (database or XML) to which you are copying or moving data.

tier A level in your database archiving configuration. You can have two- or three-tiered configurations. In a two-tiered configuration, tier one contains your active database and tier two your archive data store, which can be a database or XML. In a three-tiered configuration, tier one contains your active database, tier two an archive database, and tier three XML.

transactional data movement

Transactional movement uses set-based data movement and is the default method of movement.

transactional table A table that contains information about the business transaction. For example, a transactional table might contain detailed tax or payment information related to each business transaction.

unique identifiers (UIDs) A 16 hexadecimal identifier calculated based on the content of a Designer file. This value is used to determine if the user has customized key pieces of a project.

unmanaged table A table in a model that is copied but not purged from the active database by a cartridge. Lookup tables in a model are typically unmanaged tables.

Vertica Column-oriented SQL database management software for storing and analyzing structured data. Used to manage large, fast-growing volumes of data and provide fast query performance for data warehouses and other query-intensive applications.


virtual constraint A constraint that you define in Designer that only exists within HP Structured Data Manager.

Web Console A browser-based interface where you can create and manage your deployment environments, and deploy, run, administer, and monitor your business flows.

48 Glossary

Index

Aaccessing

Oracle queries, 36

archive query serveroverview, 9

audience, intended, 5

Cclient

JDBC, 15, 17

collectionsconnecting from SQL Server, 37Oracle Heterogeneous Services, 30querying from SQL Server, 38SQL Server, 37viewing from Excel, 21viewing from OpenOffice.org Base, 23

configureODBC on UNIX, 12ODBC on Windows, 10

configuringODBC on UNIX, 12, 13

conventions, document, 7

Ddatabases

link, Oracle, 36

documentationconventions, 7HP web site, 6related, 5updates, 7

EExcel

query wizard, 21, 22special characters, 21viewing collections from, 21

HHeterogeneous Services

agent process, 30database link, 36initialization file, 30listener, 31Oracle, 30prior to Oracle 11, 64 bit OS, 32tnsnames, 32

HPSubscriber’s choice web site, 8

Iinstall

ODBC on UNIX, 12ODBC on Windows, 10

installingODBC on UNIX, 12, 13

Interactive SQL, 15, 18

JJDBC

client, 17connection information, 17OpenOffice.org Base, 24

Llicensing, HP

end user license agreement, 2

linked server, 39

OODBC

database link, 36driver for UNIX, 12, 13OpenOffice.org Base, 23

ODBC client, 18

ODBC configuration file, 13


ODBC driverconfigure UNIX, 12configure Windows, 10uninstall, 14UNIX, 12Windows, 10

ODBC driver installation file, 10, 13

OpenOffice.org BaseJDBC, 24ODBC, 23viewing collections from, 23

Oracledatabase link, 36Heterogeneous Services, 30

Ppassword

changing, 19JDBC, 19ODBC, 19

prerequisitesproduct, 5

Qqueries

Excel, 21OpenOffice.org Base, 23Oracle, 36wizard, 21, 22

Ssoftware

version, 1

special charactersExcel, 21

SQL Serverconnecting for collections, 37

Subscriber’s choice, HP, 8

subscription serviceSubscriber’s choice, 8

supportweb site, 8

Vviewing

collections from Oracle Heterogeneous Services, 30

collections from SQL Server, 37

Wweb sites

HP documentation, 6HP Subscriber’s choice, 8support, 8

50 Index

hp structured data manager...hp structured data managerarchive query server client guide 7 document...

Documents