HP 3000 MPE/iX Computer Systems IMAGE/SQL Administration Guide ABCDE HP Part No. 36385-90001 Printed in U.S.A.
ODBCLink/SETM is a registered trademark of M. B. Foster Software Labs, Inc. Microsoft R WindowsTM, Visual BasicTM, and Microsoft AccessTM are U.S. registered trademarks of Microsoft Corporation. Acknowledgements The information contained in this document is subject to change without notice. Hewlett-Packard makes no warranty of any kind with regard to this material, including, but not limited to, the implied warranties of merchantability or tness for a particular purpose.
Printing History The following table lists the printings of this document, together with the respective release dates for each edition. The software version indicates the version of the software product at the time this document was issued. Many product releases do not require changes to the document. Therefore, do not expect a one-to-one correspondence between product releases and document editions.
About this Manual This manual explains how to use IMAGE/SQL for relational access to your TurboIMAGE/XL database. You should be familiar with TurboIMAGE/XL and have a general knowledge of relational databases to use this guide e ectively. This manual assumes you are familiar with IMAGE/SQL or have read Getting Started with HP IMAGE/SQL. The terms Turbo CONNECT and ALLBASE/Turbo CONNECT (ATC) are names used for previous versions of IMAGE/SQL. ATC still appears in some error messages and warnings.
Appendix D SQL Exceptions Lists SQL statements that have restrictions when used on a TurboIMAGE/XL data set. Appendix E SQL Views for Indices Describes the new views for TurboIMAGE/XL and third-party indices. Appendix F DATE/TIME API Describes the new externally callable procedures used to convert data to and from the internal format of ALLBASE/SQL date/time format. These new procedures are called the Date/Time Application Programming Interface or API.
What's New in this Edition Migrating a DBEnvironment This edition of the HP IMAGE/SQL Administration Guide contains the following changes: Enhancements added in releases B.G1 through B.G2.03 are documented. Edits based on suggestions by customers and HP Support Engineers are included throughout the manual. Changed security for users to allow CLASS as well as PASS. Information on migrating a DBEnvironment previously in Appendix E is removed.
Additional Documentation Manuals Bundled with IMAGE/SQL HP IMAGE/SQL Administration Guide Getting Started with HP IMAGE/SQL TurboIMAGE/XL Database Management System Reference Customer Order Number 36385-90001 36385-90008 30391-90001 Manual ALLBASE/SQL Reference Manual ALLBASE/SQL Database Administration Guide ALLBASE/SQL Message Manual ALLBASE/SQL Performance and Monitoring Guidelines ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL ODBCLINK/SE Reference Manual Additional ALLBASE Manuals ALLBASE/SQL
Conventions UPPERCASE In a syntax statement, commands and keywords are shown in uppercase characters. The characters must be entered in the order shown; however, you can enter the characters in either uppercase or lowercase. For example: COMMAND can be entered as any of the following: command italics Command COMMAND In a syntax statement or an example, a word in italics represents a parameter or argument that you must replace with the actual value.
Conventions [ . .. ] (continued) In a syntax statement, horizontal ellipses enclosed in brackets indicate that you can repeatedly select the element(s) that appear within the immediately preceding pair of brackets or braces. In the example below, you can select Parameter zero or more times. Each instance of Parameter must be preceded by a comma: [,Parameter][...
Contents 1. 2. Introduction What is IMAGE/SQL? . . . . . . . . . . . . Basic Terms for Getting Started . . . . . . . . Getting Acquainted with the IMAGE/SQL Utility Invoking the IMAGE/SQL Utility . . . . . . Attaching to a DBEnvironment . . . . . . . Selecting TurboIMAGE/XL Data from Mapped Tables . . . . . . . . . . . . . . . . . . . . . . 1-1 1-2 1-4 1-4 1-5 . 1-6 Administering IMAGE/SQL: An Overview . . . . Setting Up the IMAGE/SQL Environment . . . . Maintaining the IMAGE/SQL Environment . . .
Contents-2 3. Understanding IMAGE/SQL 4. IMAGE/SQL Utility Commands 5. IMAGE/SQL Locking How IMAGE/SQL Works . . . . . . IMAGE/SQL Files . . . . . . . . What Takes Place During an Attach? What Takes Place During a Detach? About IMAGE/SQL Security . . . . Controlling IMAGE/SQL User Access IMAGE/SQL Data Type Mapping . At Run Time . . . . . . . . . . Accessing Mapped Tables . . . . . . Performance Considerations . . . . Up-to-Date Information . . . . . Items in Queries . . . . . . . . . . . . . . . .
6. IMAGE/SQL Transactions De nition . . . . . . . . . . . Repeatable Read . . . . . . . . Restrictions . . . . . . . . . . IMAGE/SQL Aborted Transaction A. IMAGE/SQL Error Messages B. SALES Database Schema C. IMAGE/SQL and Database Utilities D. SQL Exceptions IMAGE/SQL Warning Messages File System Error Messages . . DBCORE Errors . . . . . . . Preprocessor Errors . . . . . . General Errors . . . . . . . . DBUTIL . . . . . . . . . . . Displaying Information . . . . Purging an Attached Database .
TRANSFER OWNERSHIP . . . UPDATE . . . . . . . . . . . UPDATE WHERE CURRENT . SQL Statements without Exceptions E. F. . . . . . . . . . . . . . . . . . . . . . . . . SQL Views for Indices Executing SQLINSTL and ODBCVIEW . . . . . . Views for TurboIMAGE/XL . . . . . . . . . . . SYSTEM.IMAGEKEY and CATALOG.IMAGEKEY Columns in SYSTEM.IMAGEKEY and CATALOG.IMAGEKEY . . . . . . . . . SYSTEM.TPINDEX and CATALOG.TPINDEX . Columns in SYSTEM.TPINDEX and CATALOG.TPINDEX . . . . . . . . . . SYSTEM.
Figures 2-1. IMAGE/SQL Utility Command Prerequisites 3-1. Files Created by IMAGE/SQL . . . . . . . 3-2. A TurboIMAGE/XL Database and a DBEnvironment Before the Attach . . . 3-3. An Attached TurboIMAGE/XL Database . . 3-4. IMAGE/SQL Security Mapping . . . . . . 3-5. IMAGE/SQL at Run Time . . . . . . . . . . . . 2-5 3-2 . . . . 3-3 3-5 3-8 3-9 . . . . Tables 2-1. Checklist for Setting Up the IMAGE/SQL Environment . . . . . . . . . . . . . . . 2-2. Checklist for Maintaining the IMAGE/SQL Environment .
1 Introduction This chapter introduces some basic IMAGE/SQL concepts and shows how to begin using IMAGE/SQL. For additional introductory information on IMAGE/SQL, refer to Getting Started with HP IMAGE/SQL. What is IMAGE/SQL? IMAGE/SQL provides relational access to your TurboIMAGE data using the industry-standard Structured Query Language (SQL). This access method includes full read and write capability using ANSI standard functionality.
Basic Terms for Getting Started 1-2 Introduction The following de nitions will get you started. A complete alphabetical listing of all terms and their de nitions can be found in the glossary at the end of this guide. ATC|the terms Turbo CONNECT and ALLBASE/Turbo CONNECT (ATC) were used for earlier versions of IMAGE/SQL. The acronym, ATC, still appears in error messages for IMAGE/SQL (for example, ATCERR or ATCWARN). ATCUtil |is the previous name for the IMAGE/SQL utility program, now called IMAGESQL.
DBECreator|the DBEnvironment. individual who originally con gured the DBA (Database Administrator)|a database administrator of the DBEnvironment. You must be a DBA of the DBEnvironment to which the TurboIMAGE/XL database is attached to issue most IMAGE/SQL utility commands. The creator of the DBEnvironment is automatically a DBA. Other ALLBASE/SQL users can be granted DBA authority by a DBA.
Getting Acquainted with the IMAGE/SQL The following example shows how to use the IMAGE/SQL utility to attach a TurboIMAGE/XL database to a DBEnvironment. Utility Invoking the IMAGE/SQL Utility To initiate an IMAGE/SQL utility session, log on to a group and account containing a TurboIMAGE/XL database and a DBEnvironment. Then at the MPE/iX system prompt, type RUN IMAGESQL.PUB.SYS. For example: :HELLO NANCY.ATC :RUN IMAGESQL.PUB.SYS HP36385B B.G0.
Attaching to a DBEnvironment Three IMAGE/SQL commands are needed to attach a TurboIMAGE/XL database to the DBEnvironment: SET TURBODB identi es the TurboIMAGE/XL database to be attached to a DBEnvironment. To issue this command you must be the DBC or supply the TurboIMAGE/XL database maintenance word. SET SQLDBE identi es the DBEnvironment to which the TurboIMAGE/XL database will be attached. To issue this command you must be the DBECreator or supply the DBEnvironment maintenance word.
Selecting TurboIMAGE/XL Data from Mapped Tables The attached TurboIMAGE/XL database is now a part of the DBEnvironment and can be queried with SQL SELECT statements by the DBC. (Other users must be explicitly added by the DBC. Refer to Chapter 2, \Using the IMAGE/SQL Utility," for more details.) In the example below, the ALLBASE/SQL utility, ISQL, is used to connect to PartsDBE. A SELECT statement is then used to display the data in the mapped table SALES.VENDOR. :RUN ISQL.PUB.
2 Using the IMAGE/SQL Utility This chapter assumes you have already read Chapter 1, \Introduction." This chapter provides step-by-step directions for performing IMAGE/SQL administrative tasks using IMAGE/SQL utility commands. The following information is in this chapter: An overview of the role of the IMAGE/SQL administrator. Checklists showing which tasks to perform when setting up and maintaining the IMAGE/SQL environment. An explanation of how each task description is organized.
As an administrator, you are responsible for the IMAGE/SQL environment. This chapter shows how to use IMAGE/SQL commands to perform tasks necessary for setting up and maintaining this environment. Administering IMAGE/SQL: An Overview The initial IMAGE/SQL setup involves the following steps: Establishing the connection between the DBEnvironment and the TurboIMAGE/XL database. Adding users. Customizing the default mapping to meet your needs.
p Opt/ Task# Req Table 2-2. Checklist for Maintaining the IMAGE/SQL Environment Task Description Notes Opt 4 Updating data type mapping. Perform to select alternative data type mapping. Opt 5 Splitting mapped columns. Perform to divide a mapped column into two or more mapped columns so that you can use the split column names in SQL statements. Opt 6 Updating information about users. Perform to update a user's class, password, or access mode. Opt 7 Deleting users.
About the Tasks 2-4 Using the IMAGE/SQL Utility Each task is divided into three parts: Getting Ready describes preparatory steps and gives information you need to know before performing the task. Performing the Task describes how to perform the task. Task Reference lists information useful when performing the task and provides cross-references to related tasks. To perform most tasks you must be attached to the DBEnvironment.
Figure 2-1.
Tasks Covered in this Chapter For your convenience Table 2-4 lists the tasks and the commands needed to perform each task. Most of the IMAGE/SQL commands require using SET SQLDBE and/or SET TURBODB before using the commands. Table 2-4.
Task 1: Configuring a DBEnvironment Task 1: Configuring a DBEnvironment This task describes how to con gure a DBEnvironment so you can access your TurboIMAGE/XL database(s) with ALLBASE/SQL. This is an optional task because the SET SQLDBE command can also create the DBEnvironment using defaults when the DBEnvironment does not exist. Instead of using the commands as shown in the examples, you can use the IMAGE/SQL utility to create your DBEnvironment.
Task 1: Configuring a DBEnvironment Performing the Task Log on in the same group and account as the TurboIMAGE/XL database(s) and run ISQL to enter the statements interactively. At the ISQL prompt, enter the START DBE NEW statement. For example, to con gure a DBEnvironment named PartsDBE, enter the following: :RUN ISQL.PUB.SYS isql=> START DBE 'PartsDBE' MULTI NEW > MAXIMUM TIMEOUT = 2 MINUTES > DEFAULT TIMEOUT = MAXIMUM > DBEFILE0 DBEFILE DBEFILE0 =DBEFile0De nition > WITH PAGES = 500, = . .
Task 1: Configuring a DBEnvironment To set a maintenance word for the newly con gured DBEnvironment, use the SETDBEMAINT command of SQLUtil, as in the following example: :RUN SQLUTIL.PUB.SYS >>SETDBEMAINT DBEnvironment Name: PartsDBE Current Maintenance Word: Carriage Return New Maintenance Word: usr Retype New Maintenance Word: usr >>EXIT Once you have con gured the DBEnvironment, set a maintenance word for it, and set the timeout time, you are ready to attach TurboIMAGE/XL databases to it.
Task 1: Configuring a DBEnvironment Task Reference No information is given here about considerations that may be necessary when con guring a DBEnvironment that contains ALLBASE/SQL databases. Consult the ALLBASE/SQL Database Administration Guide for more guidance in this area. The following summaries of SQL statements will get you started. You can use ISQL to invoke these statements interactively.
Task 1: Configuring a DBEnvironment Use SQLUtil to set a DBEnvironment maintenance word. First access SQLUtil, then use the SQLUtil SETDBEMAINT command to set a maintenance word. The syntax for this command is as follows: :RUN SQLUTIL.PUB.
Task 2: Attaching a TurboIMAGE/XL Database This task describes how to attach a TurboIMAGE/XL database. Task 2: Attaching a TurboIMAGE/XL Database Note If you are attaching a database with the same name but in a di erent group and/or account as a database already attached to the DBEnvironment, you must specify an alternative owner name at attach time. This is because in a mapped table, by default, the owner name is the database name. Duplicate table names are not allowed within the same database.
Task 2: Attaching a TurboIMAGE/XL Database The ATTACH command also requires that the appropriate SET SQLDBE and SET TURBODB commands have been issued. To check the status of these commands, run the IMAGE/SQL utility and use the DISPLAY OPTIONS command: :RUN IMAGESQL.PUB.SYS >>DISPLAY OPTIONS Current base : Current SQLDBE : Echo : ON Command Logging : ON Current Log File : ATCLOG.SERED.ATC If necessary, issue the SET commands in IMAGE/SQL utility. For example: >>SET SQLDBE PARTSDBE.SERED.
Task 2: Attaching a TurboIMAGE/XL Database Performing the Task When you are sure the appropriate SET commands have been speci ed and that the correct MPE/iX security is in place, issue the ATTACH command. :RUN IMAGESQL.PUB.SYS >>ATTACH Split 1 compound source fields (ATCWARN 32063). Mapped 15 source table/source field names (ATCWARN 32062). Mapped 1 incompatible source types (ATCWARN 32061). >> Messages issued at attach time inform you if any mapping has been done.
Task 2: Attaching a TurboIMAGE/XL Database Table 2-5. IMAGE/SQL Default Data Types Group Default Type IMAGE/SQL Data Type Alphanumeric CHAR Blanks Date/Time DATE CURRENT DATE DATETIME CURRENT DATETIME INTERVAL 0 00:00:00.000 TIME CURRENT TIME FLOAT 0.0 DECIMAL 0 INTEGER 0 SMALLINT 0 Numeric If all columns in a table are not speci ed, the missing columns will be de ned using the default values.
Task 2: Attaching a TurboIMAGE/XL Database Once the database is attached, the DBC must add any additional IMAGE/SQL users. It is desirable to update data types (Task 4) and split mapped columns (Task 5) before IMAGE/SQL users access the attached database. This is because whenever a mapped column is split or the data type of a mapped column is updated, any user-created views containing these mapped columns are dropped.
Task 3: Adding IMAGE/SQL Users Task 3: This task describes how to add IMAGE/SQL users. Adding IMAGE/SQL Getting Ready Users The DBC is the only IMAGE/SQL user de ned when a database is attached. All other IMAGE/SQL users must be explicitly added. To perform this task, you must be the TurboIMAGE/XL DBC and the ALLBASE/SQL DBA. If you want to check the current users before adding new IMAGE/SQL users, issue the DISPLAY USER command. For example: >>DISPLAY USER TurboIMAGE/XL DB : SALES.SERED.
Task 3: Adding IMAGE/SQL Users >>DISPLAY USER TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.ATC USER LOGON ---------- DBOPEN MODE ----------- USER PASSWORD USER CLASS ------------- ---------- NANCY.ATC SMITH.ATC 5 6 ; CLERK 64 14 >> Task Reference At attach time, the TurboIMAGE/XL database creator (DBC) is the only IMAGE/SQL user de ned in the ATCINFO le (DBEnvironmentName CR). The ALLBASE/SQL syntax User@Account is used to add an IMAGE/SQL user.
Task 4: Updating IMAGE/SQL Utility Data Type Mapping Information Updating This task describes how to select alternative IMAGE/SQL utility data type mapping. IMAGE/SQL Utility Getting Ready Task 4: Data Type Mapping Information To select alternative data type mapping, you may rst want to examine the default mapping done by the IMAGE/SQL utility.
Task 4: Updating IMAGE/SQL Utility Data Type Mapping Information Performing the Task To change default data type mapping, use the UPDATE TYPE command. For example: >>UPDATE TYPE IN CUSTOMER.CREDIT_RATING TO CHAR(4) Updated information in table CUSTOMER. >> In this example, CHAR(4) is speci ed as the data type mapping for CUSTOMER.CREDIT RATING. The DISPLAY MAP command now re ects this change: >>DISPLAY MAP CUSTOMER TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.
Task 4: Updating IMAGE/SQL Utility Data Type Mapping Information Task Reference When the data type of a mapped column is updated, all user-created views based on IMAGE/SQL utility views containing the updated mapped column are dropped. Therefore, it is desirable to update data types before IMAGE/SQL users access the attached database. Table 2-6 summarizes IMAGE/SQL data type mapping defaults and alternatives. The following abbreviations and variables are used in Table 2-6: MSB most signi cant bit.
Task 4: Updating IMAGE/SQL Utility Data Type Mapping Information Table 2-6.
Task 5: Splitting Mapped Columns Task 5: Splitting Mapped Columns One data set eld is sometimes used in TurboIMAGE/XL databases to hold several related units of data. This task describes how to easily access these individual data units by dividing them into separate mapped columns. Getting Ready Before splitting a mapped column, some preparation is necessary: Con rm that the TurboIMAGE/XL database containing the source eld is attached to a DBEnvironment.
Task 5: Splitting Mapped Columns Decide what to name the new mapped columns. For example, the new mapped columns in the mapped table INVENTORY are named: PART ID CODE PART VERSION NO PART NOTES Determine what SQL data type(s) can be mapped to each TurboIMAGE/XL data type identi ed in step 2; refer to Table 2-6. When alternative data type mapping possibilities exist, decide which mapping best re ects the format of the data.
Task 5: Splitting Mapped Columns The DISPLAY MAP command shows the newly split columns: >>DISPLAY MAP FOR SALES INVENTORY TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.ATC Owner Name : SALES MAPPED SOURCE MAPPED SOURCE MAPPED TABLE FIELD COLUMN TYPE TYPE NOTES ------------ --------------- ------------------ ------- ----------- ----INVENTORY new new new =) =) =) PRODUCT# . . . PRODUCT# . . . U8 . . . CHAR(8) . . .
Task 6: Updating Information about IMAGE/SQL Users Task 6: This task describes how to update IMAGE/SQL user information. Updating Getting Ready Information about To perform this task, you must be both the TurboIMAGE/XL DBC and a DBA of the attached DBEnvironment. IMAGE/SQL Users If you want to check the current users before adding new IMAGE/SQL users, issue the DISPLAY USER command. For example: >>DISPLAY USER TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.
Task 6: Updating Information about IMAGE/SQL Users The DISPLAY USER command now re ects the updated information: >>DISPLAY USER TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.ATC USER LOGON ---------- DBOPEN MODE ----------- USER PASSWORD USER CLASS ------------- ---------- NANCY.ATC SMITH.ATC 5 5 ; CLERK 64 14 >> Task Reference The SQL syntax User@Account (or User.Account ) is used to add an IMAGE/SQL user.
Task 7: Deleting IMAGE/SQL Users Task 7: This task describes how to delete IMAGE/SQL users. Deleting IMAGE/SQL Getting Ready Users To perform this task, you must be both the TurboIMAGE/XL DBC and an SQL DBA. If you want to check the current users before adding new IMAGE/SQL users, issue the DISPLAY USER command. For example: >>DISPLAY USER TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.
Task 7: Deleting IMAGE/SQL Users The DISPLAY USER command now re ects the deletion. >>DISPLAY USER TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.ATC USER LOGON ---------- DBOPEN MODE ----------- USER PASSWORD USER CLASS ------------- ---------- NANCY.ATC 5 ; 64 >> Note that SMITH.ATC is still con gured as an SQL user because ALLBASE/SQL tables and views may be owned by this User.Logon .
Task 8: Adding a Log File Task 8: This task describes how to add a new log le to the DBEnvironment. Adding a Log File Performing the Task Run SQLUtil, issue the ADDLOG command, and add a new log le as in the following example: :RUN SQLUTIL.PUB.SYS >>ADDLOG DBEnvironment Name: PARTSDBE Maintenance Word: 4Return5 Enter Log File Name(s) Separated by a Blank? LGN1 New Log File Size? 300 Add Log File (y/n)? y Log file 'LGN1' was Added.
Task 9: Detaching a TurboIMAGE/XL Database Detaching a This task describes how to detach a TurboIMAGE/XL database from a DBEnvironment. TurboIMAGE/XL Getting Ready Task 9: Database Before detaching a TurboIMAGE/XL database, do the following: Obtain exclusive access to the DBEnvironment before detaching a TurboIMAGE/XL database. Make sure the appropriate SET SQLDBE and SET TURBODB commands have been issued.
Task 9: Detaching a TurboIMAGE/XL Database Task Reference There are several reasons to detach a TurboIMAGE/XL database from a DBEnvironment: If the database is to be restructured. (This includes restructuring with DBChange and other third-party restructuring tools.) If you want to reset all mapping information to default values. All mapped tables, all IMAGE/SQL views based on these tables, and all user-created views based on IMAGE/SQL views and tables are dropped when the database is detached.
Task 10: Displaying IMAGE/SQL Utility Information Task 10: Displaying IMAGE/SQL Utility Information This task describes how to display IMAGE/SQL information using several IMAGE/SQL utility commands. These commands include: DISPLAY OPTIONS Displays the options in e ect for your current IMAGE/SQL utility session. DISPLAY TURBODB Displays all the TurboIMAGE/XL databases associated with a speci c DBEnvironment. DISPLAY SQLDBE Displays all the DBEnvironments associated with a speci c TurboIMAGE/XL database.
Task 10: Displaying IMAGE/SQL Utility Information Performing the Task Two examples using the DISPLAY commands are shown below and on the next page. Example 1: Displaying Database Information To see all the TurboIMAGE/XL databases and mapped tables currently associated with PartsDBE, use the DISPLAY TURBODB command. :RUN IMAGESQL.PUB.SYS >>DISPLAY TURBODB TABLES FOR PARTSDBE.SERED.ATC DBEnvironment : PARTSDBE.SERED.
Task 10: Displaying IMAGE/SQL Utility Information Example 2: Displaying Database Mapping Information The DISPLAY MAP command shows how TurboIMAGE/XL data sets are mapped to SQL tables. In the following example, information about the mapped table INVENTORY is displayed. >>DISPLAY MAP INVENTORY TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.
Task 11: Issuing MPE/iX Commands from the IMAGE/SQL Utility Issuing MPE/iX This task describes how to issue MPE/iX commands from the IMAGE/SQL utility. Commands from the Performing the Task Task 11: IMAGE/SQL Utility To issue an MPE/iX command from the IMAGE/SQL utility, enter a colon (:) and the name of the MPE/iX command you want to issue.
Task 12: Setting IMAGE/SQL Utility File Equations Setting IMAGE/SQL This task describes how to set IMAGE/SQL utility le equations for two les: ATCINFO and ATCLOG. File Equations Performing the Task Task 12: Setting a File Equation for ATCINFO An ATCINFO le equation can only be set before the le is created (before any TurboIMAGE/XL databases are attached to the DBEnvironment). Only the le name can be speci ed. Other parameters of the FILE command will not be valid at attach time.
Task 12: Setting IMAGE/SQL Utility File Equations Task Reference ATCINFO Reference The ATCINFO le is a permanent privileged le containing mapping information about data set and eld names, data types, and user security. Its formal le designator is ATCINFO. The default name of the ATCINFO le is DBEnvironmentName CR. One ATCINFO le exists for each DBEnvironment. It is created in the same group and account as the DBEnvironment and is considered part of the DBEnvironment.
Task 13: Logging IMAGE/SQL Utility Commands Task 13: Logging IMAGE/SQL Utility Commands This task describes how to log IMAGE/SQL utility commands and how to save frequently used command sequences in di erent log les so they can easily be reissued in batch or interactive mode. Performing the Task By default, IMAGE/SQL utility commands are logged to the ASCII le ATCLOG, which you can read and edit. You can change log les within the IMAGE/SQL utility by issuing a le equation for ATCLOG.
Task 13: Logging IMAGE/SQL Utility Commands To close this log le and write to a new one, issue another MPE/iX FILE command from within the IMAGE/SQL utility. For example: >>:FILE ATCLOG=UPDATYPE;SAVE >> The DISPLAY OPTIONS command con rms that commands are now being logged to UPDATYPE: >>DISPLAY OPTIONS Set Turbodb : SALES.SERED.ATC Set Sqldbe : PARTSDBE.SERED.
Task 13: Logging IMAGE/SQL Utility Commands Task Reference Task 12, \Setting IMAGE/SQL Utility File Equations," contains more information about issuing IMAGE/SQL utility le equations. Task 14, \Using IMAGE/SQL Utility Command Files," contains more information about using the IMAGE/SQL utility XEQ command interactively or in batch to execute IMAGE/SQL utility commands. When an IMAGE/SQL utility session begins, logging is on.
Task 14: Using IMAGE/SQL Utility Command Files Using IMAGE/SQL This task describes how to execute an IMAGE/SQL utility command le in interactive or batch mode. Utility Command Getting Ready Task 14: Files An IMAGE/SQL utility command le can be created with a text editor or can be produced as part of the logging process. If you create or edit this le with an editor, it must be kept unnumbered. In the following examples, the le UPDATYPE contains the following commands: SET TURBODB SALES.SERED.
Task 14: Using IMAGE/SQL Utility Command Files You can also issue XEQ commands in batch mode. The following job stream le contains XEQ commands that execute the commands in the UPDATYPE le. !job JIMSQL,NANCY/KEVIN.ATC/MGR,SERED/ALL !comment************************************************************** !comment* This job executes an IMAGESQL command file. !comment************************************************************** ! !tell NANCY.ATC; /-->Start JIMSQL for SALES ! !run IMAGESQL.PUB.
Task 15: Selecting TurboIMAGE/XL Data with SQL Selecting This task explains how IMAGE/SQL users select TurboIMAGE/XL data with SQL. TurboIMAGE/XL Getting Ready Task 15: Data with SQL To successfully select TurboIMAGE/XL data with IMAGE/SQL, users need to know the following: How to use their available SQL interface. The examples in this manual use ISQL, which also requires familiarity with the SQL SELECT statement. The names of the mapped tables and/or views to which they have access.
Task 15: Selecting TurboIMAGE/XL Data with SQL Users can also join data from more than one view. The query in the following example retrieves data from two views, both with a column containing product numbers. The product number, the quantity, and the name of an alternative vendor is selected when the product number appears in both views. Speci cally, the query selects the following columns: OTHER VENDORS 1 from view SALES.INVENTORY 14 QUANTITY from view SALES.SALES V14 SALES.SALES.
Task 15: Selecting TurboIMAGE/XL Data with SQL In WHERE clause, specifying columns that map to TurboIMAGE/XL search items, key items (with or without B-Tree indices), or items that have third-party indices will improve performance under the following conditions: The WHERE clause compares a mapped column and a value for equality: isql=> SELECT * FROM SALES.SALES_V11 > WHERE PRODUCT# = '235' > ...
Task 16: Maintaining the ATCINFO File Task 16: This task describes how to maintain the ATCINFO le. Maintaining the Getting Ready ATCINFO File Maintenance for the ATCINFO le (DBEnvironmentName CR) may be necessary in either of the following situations: If a crash occurs while the ATCINFO le is being modi ed. This may be the case if a crash takes place when the IMAGE/SQL administrator is in the midst of an IMAGE/SQL utility command that updates the ATCINFO le.
3 Understanding IMAGE/SQL This chapter explains how IMAGE/SQL works and outlines the SQL functionality available to IMAGE/SQL users. This chapter describes: Files created by IMAGE/SQL Events that take place during an attach Events that take place during a detach IMAGE/SQL security IMAGE/SQL data type mapping Run-time events Mapped table access with SQL How IMAGE/SQL Works IMAGE/SQL users can select data in mapped tables in the same way that data in ALLBASE/SQL tables is selected.
IMAGE/SQL creates two les: the ATCINFO le with a default name of DBEnvironmentName CR in the same group and account as the DBEnvironment and DBName TC in the same group and account as the TurboIMAGE/XL database. This is shown in Figure 3-1. IMAGE/SQL Files Figure 3-1. Files Created by IMAGE/SQL These les contain information about the relationships between all attached TurboIMAGE/XL databases and their DBEnvironments.
DBNameTC What Takes Place During an Attach? It is named DBEnvironmentName CR where DBEnvironmentName is up to six characters of the actual DBEnvironmentName unless a le equation has been set before the attach. The ATCINFO lename is placed in the DBECon le of the DBEnvironment so that it can be located whenever IMAGE/SQL needs to use or update the information in this le. is a permanent privileged le in the same group and account as the TurboIMAGE/XL database.
When the TurboIMAGE/XL database is attached to the DBEnvironment, several events take place: 1. The ATCINFO (DBEnvironmentName CR) and DBName TC les are created. 2. De nitions of the TurboIMAGE/XL mapped tables are placed in the system catalog of the DBEnvironment. These entries in the system catalog identify the tables as mapped tables. The naming convention for SQL tables is OwnerName.TableName .
Figure 3-3.
What Takes Place During a Detach? When a database is detached, you can no longer use SQL to access TurboIMAGE/XL data. All views based on mapped tables are dropped. This includes views created by users, as well as IMAGE/SQL-created views. Mapped table de nitions are removed from the system catalog and all mapping information about the detached TurboIMAGE/XL database is removed from the ATCINFO le. This results in the removal of de nitions of hash, B-Tree, and third-party indices from the system catalog.
About IMAGE/SQL Security IMAGE/SQL enforces TurboIMAGE/XL database security. That is, SQL users can access only the data de ned for them in the TurboIMAGE/XL database schema. To accomplish this, during an attach, only the DBC is de ned as an SQL user. This user has access to all the mapped tables in the database. The DBC must explicitly add all other IMAGE/SQL users by associating each user class or password with a DBEUser ID. A view is then created for each mapped table to which the DBEUser ID has access.
Figure 3-4. IMAGE/SQL Security Mapping IMAGE/SQL Data Type Mapping IMAGE/SQL maps all TurboIMAGE/XL data types to the closest equivalent SQL data types. Sometimes completely compatible choices are not available, or more than one viable alternative exists. In these cases, IMAGE/SQL chooses default data types for you, but also provides alternative data type mapping that you can select if it more closely meets your particular needs.
At Run Time At run time, SQL turns all mapped table queries over to IMAGE/SQL. Using the mapping information in the ATCINFO le, IMAGE/SQL makes the appropriate TurboIMAGE/XL calls, retrieves the data from the TurboIMAGE/XL database, and returns the data to SQL in the correct SQL format (see Figure 3-5.) Figure 3-5. IMAGE/SQL at Run Time Note that the data is retrieved from the TurboIMAGE/XL database. Only the mapped table de nitions actually reside in the DBEnvironment.
Accessing Mapped Tables Performance IMAGE/SQL provides transparent access to TurboIMAGE/XL data. However, IMAGE/SQL users should keep in mind the considerations described in this section when accessing TurboIMAGE/XL data. The following factors can a ect IMAGE/SQL performance: Considerations Up-to-Date Information For better performance, it is important to have accurate statistics on the tables. Use the SQL UPDATE STATISTICS statement to ensure that this information is up-to-date.
4 IMAGE/SQL Utility Commands This chapter contains detailed information about the commands for the IMAGE/SQL utility, IMAGESQL. The following is included for each command: Command syntax Explanation of the command's parameters Prerequisites needed to execute the command Discussion of the command's functionality Example(s) In all examples, the TurboIMAGE/XL database shown is SALES and the DBEnvironment is PartsDBE.
Adds an IMAGE/SQL user. ADD USER Parameters PASS=Password CLASS=Classnum User@Account is the name used to identify the new user to IMAGE/SQL. This name, referred to in SQL as the DBEUserID, is made up of an MPE/iX user and account name, connected with the period or @ symbol. It must contain valid logon syntax. If the account is omitted, it will default to the logon account. Password is a password in the TurboIMAGE/XL schema.
ADD USER To enforce TurboIMAGE/XL security, one view is created for each data set the user class is allowed to access. The view is named according to the following conventions: OwnerName.MappedTableName_VUserClassNumber For example, for a database owned by MKTG, the view created for the mapped table ACCOUNTS is MKTG.ACCOUNTS V11 for user class 11. Each view contains only those mapped columns that correspond to the elds in the data set the user class can read.
Attaches a TurboIMAGE/XL database to an SQL DBEnvironment. ATTACH 2 AT[TACH] WITH OWNER=OwnerName Syntax Parameters OwnerName 3 speci es an owner for all SQL objects that need to be created for the attached TurboIMAGE/XL database. This name can be up to 17 bytes in length and can be made up of any combination of letters (A to Z), decimal digits (0 to 9), $, #, @, or (underscore). However, the rst character cannot be a decimal digit or an underscore.
ATTACH you want to reattach it, you must perform a DETACH rst and then ATTACH it. To attach databases with the same database name but in di erent groups and accounts to the same DBEnvironment, you must use the OwnerName parameter to specify a di erent owner name for all but the rst such database you attach.
ATTACH DBEnvironment. For an example, the SQL statement, Select * from SYSTEM.IMAGEKEY, after connecting to the DBEnvironment will display all hash indices. If B-Tree indices are created on the key items of the selected master data sets using DBUTIL or DBSCHEMA, IMAGE/SQL enters de nitions for the B-Tree indices in the system catalog of the DBEnvironment.
ATTACH index and third-party index be not created on the same item as it will have an unnecessary impact on the performance (Optimizer calculates cost for each index). The Optimizer decides which index to use and the proper order of operations to ensure that the most e cient path is used. Example In the following example, SALES is attached to PARTSDBE. The accompanying message summarizes the mapping that took place during the attach.
ATTACH >>DISPLAY MAP TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.
DELETE USER Deletes an IMAGE/SQL user. DELETE USER Syntax Parameters Prerequisites Description Example DEL[ETE] USER User@Account User.Account User@Account is the name used to identify the IMAGE/SQL user to SQL. This name, referred to as the DBEUserID, is made up of an MPE/iX user and account name, connected with the period or @ symbol. This user and account must be a valid existing logon. SET SQLDBE issued. SET TURBODB issued. DBC status. DBA authority. Database attached.
Detaches a TurboIMAGE/XL database from a DBEnvironment. DETACH DET[ACH] Syntax Command Prerequisites Description SET SQLDBE issued. SET TURBODB issued. DBA authority. Database attached. Use the DETACH command to detach a TurboIMAGE/XL database from a DBEnvironment. Obtain exclusive access to the DBEnvironment before using the DETACH command. Use the ISQL SELECT command to nd out if it is being accessed. (See the ATTACH command for an example of SELECT.
DETACH Example In this example, SALES is detached from PartsDBE. When this occurs, all mapping information about SALES is removed from the ATCINFO le. If SALES is the only database attached to PartsDBE, the ATCINFO le itself is purged. If SALES is no longer attached to any DBEnvironment, the SALESTC le is also purged. :RUN IMAGESQL.PUB.
Displays the current data type mapping information for a speci ed TurboIMAGE/XL database. DISPLAY MAP DI[SPLAY] MAP [FOR TurboDB [,MAINT=TurboMaintWord] ] [MappedTable[.MappedCol]] [,...] Syntax Parameters TurboDB TurboMaintWord MappedTable MappedCol Prerequisites Description is the name of the TurboIMAGE/XL database whose data type mapping information you want to display. This name can be a fully quali ed group and account name.
DISPLAY MAP command may also have been used to explicitly split a mapped column into smaller mapped columns. In this case, a U also appears in the NOTES section. A U in the NOTES section next to a particular mapping indicates that the default data type mapping has been updated with the UPDATE TYPE command. Refer to the SPLIT and UPDATE TYPE commands for information about how to select alternative data type mapping.
DISPLAY OPTIONS Displays the options in e ect for the current IMAGE/SQL utility session. DI[SPLAY] OPTIONS Syntax Prerequisites Description Example None. Use the DISPLAY OPTIONS command to display the options in e ect for the current IMAGE/SQL utility session. The following information is displayed: The database named in the last SET TURBODB command. The DBEnvironment named in the last SET SQLDBE command. The condition speci ed in the last LOG command (on or o ).
DISPLAY SQLDBES DISPLAY SQLDBES Syntax Parameters Displays all the DBEnvironments currently attached to a TurboIMAGE/XL database. DI[SPLAY] SQLDBE[S] [FOR TurboDB [,MAINT=TurboMaintWord]] TurboDB TurboMaintWord Prerequisites Description Example is the name of the TurboIMAGE/XL database whose associated DBEnvironments you want to display. If this parameter is omitted, the database must be previously speci ed with the SET TURBODB command or else an error message is issued.
Displays all TurboIMAGE/XL databases currently attached to a DBEnvironment and, optionally, their associated mapped tables. DISPLAY TURBODBS DI[SPLAY] TURBODB[S] [TABLES] [FOR DBEnvironment [,MAINT=DBE MaintWord]] Syntax Parameters TABLES DBEnvironment DBE MaintWord Prerequisites Description is an option that displays all mapped tables in each TurboIMAGE/XL database attached to the DBEnvironment. If this parameter is omitted, no table information is displayed.
DISPLAY USERS DISPLAY USERS Syntax Displays current user information for a speci c TurboIMAGE/XL database. DI[SPLAY] USER[S] [FOR TurboDB] [USER= [,.. .]] Parameters Prerequisites Description User@Account User.Account TurboDB is the name of the TurboIMAGE/XL database whose user information you want to display. If this parameter is omitted, the name of the database de ned in the most recent SET TURBODB command is used.
DISPLAY USERS >>DISPLAY USER USER=NANCY@ATC TurboIMAGE/XL DB : SALES.SERED.ATC DBEnvironment : PARTSDBE.SERED.ATC Owner Name : SALES USER LOGON ---------- DBOPEN MODE ----------- USER PASSWORD USER CLASS -------------------- NANCY.
ECHO Determines if commands and comments are echoed during the execution of a command le. ECHO EC[HO] Syntax Parameters Prerequisites Description ON OFF turns echo on. This is the default. OFF turns echo o . ON None. Use the ECHO command to turn the IMAGE/SQL utility echo on or o . In session mode, when echo is on, all commands issued from a command le and any accompanying comments are displayed at the terminal.
Stops execution of the IMAGE/SQL utility program. EXIT EX[IT] Syntax Prerequisites Description Example None. Use the EXIT command to leave the IMAGE/SQL utility program. This command provides the same functionality as the QUIT command. In the following example, the IMAGE/SQL utility stops execution and returns you to the MPE/iX prompt.
HELP Provides information about IMAGE/SQL utility commands. HELP Syntax Parameters H[ELP] ? CommandName KeyWord Prerequisites Description Example 1 [CommandName [KeyWord]] is the name of the IMAGE/SQL utility command about which you want information. The abbreviated form of the command can be used. If this parameter is omitted, syntax information about all IMAGE/SQL utility commands is displayed. is a word that further de nes some commands.
HELP >>HELP DISPLAY SQLDBES DI[SPLAY] SQLDBE[S] ---------------Displays all the DBEnvironments associated with a specific TurboIMAGE/XL database.
LOG Turns IMAGE/SQL command logging on or o . LOG L[OG] Syntax Parameters Prerequisites Description ON OFF turns command logging on. This is the default. OFF turns command logging o . ON None. Use the LOG command to turn IMAGE/SQL utility command logging on or o . When logging is on, all commands entered are logged to the ATCLOG le. When the IMAGE/SQL utility is run, logging is initially on.
Stops the execution of the IMAGE/SQL utility program. QUIT Q[UIT] Syntax Prerequisites Description Example None. Use the QUIT command to leave the IMAGE/SQL utility program. This command provides the same functionality as the EXIT command. In the following example, the IMAGE/SQL utility stops execution and returns you to the MPE/iX prompt.
RECOVER Performs maintenance on the ATCINFO le. RECOVER Syntax Prerequisites Description Example RECOVER None. Use the RECOVER command to perform maintenance on the ATCINFO le. The default name of the ATCINFO le is DBEnvironmentName CR with up to six characters of the DBEnvironmentName . Maintenance for the ATCINFO le may be necessary in either of the following situations: If a crash occurs while the ATCINFO le is being modi ed.
Allows a user to correct or modify the last IMAGE/SQL utility command. REDO REDO Syntax Prerequisites Description Subcommands None. Use this command to display the last IMAGE/SQL utility command entered. Use the associated subcommands to correct or change the displayed command. The REDO command applies only to the last command entered and is available only in interactive mode. It is ignored with a warning in batch mode. D I R U deletes the character above the cursor.
SET SQLDBE De nes the SQL DBEnvironment to be used by other IMAGE/SQL utility commands. SET SQLDBE Syntax Parameters SE[T] SQLDBE DBEnvironment [,MAINT=DBE MaintWord] DBEnvironment DBE MaintWord Prerequisites Description is the name of the DBEnvironment with which you want to work. is the maintenance word for the DBEnvironment. This parameter can be omitted if you are an SQL database administrator (DBA). DBECreator status or DBEnvironment maintenance word speci ed as part of the command.
SET SQLDBE The size of MYDBEFL is 5000 pages, and that of MYDBELG is 10000 pages. Example In the following example, the PARTSDBE DBEnvironment is speci ed and is in e ect until you exit the IMAGE/SQL utility or issue another SET SQLDBE command.
SET TURBODB De nes the TurboIMAGE/XL database to be used by other IMAGE/SQL utility commands. SET TURBODB Syntax Parameters SE[T] TURBODB TurboDB [,MAINT=TurboMaintWord] TurboDB TurboMaintWord Prerequisites Description is the name of the TurboIMAGE/XL database with which you want to work. is the maintenance word for the TurboIMAGE/XL database. This parameter can be omitted if you are the TurboIMAGE/XL database creator (DBC).
Divides a mapped column into two or more smaller mapped columns. SPLIT SP[LIT] MapTable.MapCol INTO NewColSpec [,...] Syntax Parameters MapTable MapCol NewColSpec is the name of the mapped table containing the mapped column. is the name of the mapped column to be split into smaller units. is the speci cation of the new mapped column. Repeat this parameter for each new mapped column.
SPLIT Description Use the SPLIT command to divide mapped columns into smaller units. This is sometimes necessary because one data item can be used to contain information about several logically discrete units. When a TurboIMAGE/XL database becomes part of a DBEnvironment, it is no longer necessary to keep logically separate information in one mapped column. The SPLIT command can be used to divide mapped columns of this kind into several smaller more logically discrete mapped columns.
SPLIT Example The mapped column INVENTORY.PART INFO is of type CHAR(60) and contains the following units of information about parts in the order shown: A code identifying the part. The version number of the part. Brief listing of any special considerations regarding the part. To split this mapped column into its logical units, issue the following command: >>SPLIT INVENTORY.PART_INFO INTO PART_ID_CODE:X4,& PART_VERSION_NO:I1,& PART_NOTES:X54 Updated information in table INVENTORY.
UPDATE TYPE Updates data type mapping information. UPDATE TYPE Syntax Parameters SourceType IN * MappedTable IN MappedTable.Col [TO NewMappedType] U[PDATE] TYPE SourceType 3 (asterisk) MappedTable MappedTable.Col NewMappedType Prerequisites 8 < : 9 = ; is a TurboIMAGE/XL data item type whose data type mapping information you want to update. Use either this parameter or the MappedTable.Col parameter.
UPDATE TYPE Description Use the UPDATE TYPE command to update the data type mapping information in one of the following situations: For all occurrences of a speci ed source data type in the database. For all occurrences of the source data type in a speci ed mapped table. For one occurrence of the source data type in a particular column of a speci ed table. To return the TurboIMAGE/XL data type or mapped column to default IMAGE/SQL utility data type mapping values, omit the TO NewMappedType parameter.
UPDATE TYPE >>UPDATE TYPE J2 IN tablename TO DECIMAL(10,2) Use this command to remap one particular eld in a data set to 1 decimal place: >>UPDATE TYPE IN table.column TO DECIMAL(10,1) In this second example, the UPDATE TYPE command is used to specify the alternative mapping ALLBASE/SQL CHAR(4). This is a byte-by-byte transfer (R2 is 4 bytes long). No data conversion is performed. >>UPDATE TYPE R2 IN CUSTOMER TO CHAR(4) Updated information in table CUSTOMER.
Updates an IMAGE/SQL user's class number, password, and/or DBOPEN mode in a DBEnvironment. UPDATE USER Syntax U[PDATE] USER |,...| Parameters User@Acct Password ModeNumber ClassNumber Prerequisites Description 8 < PASS=Password User@Acct TO MODE=ModeNumber User.Acct : CLASS=ClassNumber 9 = ; is the name used to identify the user in the DBEnvironment. This name, referred to as the DBEUserID, is made up of the MPE/XL user and account name, connected with the period or @ symbol.
UPDATE USER Example In the following example, user RYAN.ATC is updated to DBOPEN mode 1 with user class 13. UPDATE USER RYAN.ATC TO MODE=1, CLASS=13 ALLBASE/SQL group SALES_13 created. View SALES.DATE_MASTER_V13 created. View SALES.DATE_PRODUCT_V13 created. View SALES.DATE_VENDOR_V13 created. View SALES.DATE_INVENTORY_V13 created.
Executes IMAGE/SQL utility commands from a command le instead of the standard input device. XEQ X[EQ] FileName Syntax Parameters Prerequisites Description FileName is the name of an ASCII le containing commands and parameters. None, but for successful execution of the command le, you must meet prerequisites of all commands in the command le. Use the XEQ command to execute IMAGE/SQL utility commands from a le instead of the standard input device.
XEQ Example In the following example, the contents of the le DOATTACH are listed using the MPE/iX PRINT command. The XEQ command then executes the commands stored in the DOATTACH le. Because commands are not displayed as they are executed, it can be assumed that the ECHO command option is o . >>:PRINT DOATTACH SET TURBODB ORDER4 = contents of XEQ le SET SQLDBE PARTSDBE = " " ATTACH = " " QUIT = " " >> >>XEQ DOATTACH = XEQ command issued Split 1 compound source field(s) (ATCWARN 32063).
5 IMAGE/SQL Locking This chapter describes how IMAGE/SQL assigns locks on IMAGE/SQL tables and how IMAGE/SQL handles deadlocks. Note Lock Assignment For detailed information on TurboIMAGE/XL and ALLBASE/SQL locking concepts, refer to the TurboIMAGE/XL Database Management System Reference Manual and the ALLBASE/SQL Reference Manual . Locks are assigned to IMAGE/SQL tables in two ways. First, you can explicitly set a lock with the LOCK TABLE statement.
The SQL statements that can modify TurboIMAGE/XL tables are INSERT, UPDATE, or DELETE. The DBOPEN mode is assigned at ATTACH time. To see what mode is assigned, use the IMAGESQL DISPLAY USERS command. If you have DBA authority, you can change the mode with the UPDATE USER command. The following are the isolation levels supported in ALLBASE/SQL and IMAGE/SQL. RR Repeatable Read. The transaction uses locking strategies to guarantee repeatable reads, the default isolation level. CS Cursor Stability.
6 IMAGE/SQL Transactions This chapter describes IMAGE/SQL transactions, repeatable reads, and IMAGE/SQL aborted transactions. Note Definition For detailed information on ALLBASE/SQL transactions, refer to the ALLBASE/SQL Reference Manual . A transaction is a unit of work consisting of one or more SQL statements referencing one or more databases in a DBEnvironment. Work done within a transaction can be committed (made permanent) or undone (rolled back).
You can only modify one type of data in a transaction. That is, you can do the following within the same transaction: Read a TurboIMAGE/XL and/or ALLBASE/SQL database and modify a TurboIMAGE/XL database. Read a TurboIMAGE/XL and/or ALLBASE/SQL database and modify an ALLBASE/SQL database. Restrictions You cannot update both an ALLBASE/SQL database and a TurboIMAGE/XL database within the same transaction. Such an attempt will produce an error.
A IMAGE/SQL Error Messages 13501 MESSAGE Invalid TurboIMAGE database access. TurboIMAGE intrinsic intrinsic number . (DBERR 13501) CAUSE The TurboIMAGE/XL intrinsic named in the message has returned an error code (-21) indicating a bad password or maintenance word. 1. Make sure the user logon is a valid IMAGE/SQL user for this TurboIMAGE/XL database and the password for the user logon is correct. (Use the DISPLAY USERS command and DBUTIL's SHOW dbname PASSWORDS command.) 2.
13503 MESSAGE TurboIMAGE database subsystem access has been disabled. (DBERR 13503) CAUSE Subsystem access of the TurboIMAGE/XL database has been disabled. Contact your DBA to enable the subsystem access to the TurboIMAGE/XL database. Refer to the discussion of the DBUTIL SET command in the TurboIMAGE/XL ACTION Database Management System Reference Manual 13504 MESSAGE TurboIMAGE database has been disabled for access. (DBERR 13504) CAUSE Access to the TurboIMAGE/XL database has been disabled.
13505 MESSAGE IMAGE/SQL data conversion error: error ATC internal error number , set data set number , item data item number , column column number . (DBERR 13505) CAUSE Data conversion between TurboIMAGE/XL format and ALLBASE/SQL format caused the error. Either the source or the target length is not correct, or the internal conversion code stored in ATCINFO le is corrupted.
13506 MESSAGE IMAGE/SQL conversion overflow: set data set number , item data item number , column column number , record turboIMAGE record number . (DBERR 13506) CAUSE Data conversion from TurboIMAGE/XL format to ALLBASE/SQL format resulted in an over ow. Identify the TurboIMAGE/XL record and the data item that caused the conversion over ow and use the IMAGE/SQL utility DISPLAY MAP command to nd out the current mapping.
13507 MESSAGE Invalid IMAGE/SQL data format: set data set number , item data item number , column column number , record turbo record number . (DBERR 13507) CAUSE Data conversion from TurboIMAGE/XL format to ALLBASE/SQL format for ZONED data type found an illegal overpunch in the data. Identify the TurboIMAGE/XL record and the data items that contains the illegal overpunch, and use IMAGE/SQL utility's DISPLAY MAP command to nd out the current mapping.
13508 MESSAGE IMAGE/SQL conversion truncated; set data set number , item data item number , column column number , record turboIMAGE record number . (DBERR 13508) CAUSE Data conversion between TurboIMAGE/XL format and ALLBASE/SQL format resulted in error. The source length is longer then target length, so the remaining byte(s) will be truncated.
13511 MESSAGE Update Statistics failed. IMAGE/SQL error error number ; DBINFO error error number . (DBERR 13511) CAUSE A call to the TurboIMAGE/XL procedure DBINFO on the data set failed. The most likely reason is that the user does not have any access to the data set. Check the TurboIMAGE/XL Database ACTION 13551 Management System Reference Manual for the DBINFO error number and make sure the user has read access to the TurboIMAGE/XL data set.
13552 MESSAGE IMAGE/SQL error error number ; TurboIMAGE error error number ; TurboIMAGE intrinsic intrinsic number , Auxiliary error error number . (DBERR 13552) CAUSE The TurboIMAGE/XL intrinsic indicated by the intrinsic number failed. Check the TurboIMAGE/XL Database ACTION Management System Reference Manual and correct the problem. For example: ATC error 129; TurboIMAGE error -1; TurboIMAGE intrinsic 401, Auxiliary error 93.
401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 418 419 420 421 422 DBOPEN DBINFO DBCLOSE DBFIND DBGET DBUPDATE DBPUT DBDELETE DBLOCK DBUNLOCK DBCONTROL DBBEGIN DBEND DBMEMO DBMAINT DBEXPLAIN DBERROR DBXBEGIN DBXEND DBXUNDO The auxiliary error number is the lower level subsystem error number, such as the MPE/iX le system error number. 13553 MESSAGE IMAGE/SQL internal error error number , error number , error number , error number .
13554 MESSAGE IMAGE/SQL internal error error number , error number , error number , error number . (DBERR 13554) CAUSE At connection time, the procedure call to open the ATCINFO le has failed. The third error number contains the le system info number, the fourth error number contains the subsys number. Use those to gure out the reason why open of the ATCINFO le failed. ACTION 13555 MESSAGE IMAGE/SQL internal error error number , error number , error number , error number .
IMAGE/SQL Warning Messages 32051 MESSAGE Database already attached (ATCWARN 32051). CAUSE The database was already attached prior to executing the latest ATTACH. No action is necessary. This is an advisory message only. ACTION 32052 MESSAGE Database not attached (ATCWARN 32052). CAUSE You attempted to execute a command that requires the database to be attached. Attach the database and re-execute the command that gave this warning. ACTION 32059 MESSAGE Exceeded 3996 bytes.
32060 MESSAGE Exceeded 255 columns. Columns omitted in mapped table name (ATCWARN 32060). CAUSE The maximum number of columns in an ALLBASE/SQL table is 255. This limit was exceeded while creating mapped tables, so one or more columns had to be omitted in the de nition of the speci ed mapped table. Restructure the database so that the total number of elds in the data set does not exceed 255, or group one or more elds into a compound eld.
32063 MESSAGE Split number compound source field(s) (ATCWARN 32063). CAUSE The speci ed number of compound source elds have been split into constituent components. No remedial action is required. Use the DISPLAY MAP command to review all the mappings. ACTION 32066 MESSAGE Duplicate user name (ATCWARN 32066). CAUSE The user name you speci ed already exists in the ATCINFO le. No action is necessary. This is an advisory message only.
32069 MESSAGE Warning: command containing a password has been logged (ATCWARN 32069). CAUSE A command containing a TurboIMAGE/XL database password was logged in ATCLOG. Secure the ATCLOG le if security is a concern. ACTION File System Error Messages 32203 MESSAGE Privileged File Error (ATCERR 32203, FSERR error number ). CAUSE The speci ed le did not have the expected le code. Specify the correct le. For example, in the SET TURBODB command, only a TurboIMAGE/XL database name is valid.
32206 MESSAGE lename - Duplicate Temporary File (ATCERR 32206, FSERR error number ). CAUSE The speci ed le already exists in the temporary le domain. Specify an unique le name. ACTION 32207 MESSAGE File System Error on lename (ATCERR 32207, FSERR error number ). CAUSE A le system error related to the speci ed le has occurred. Look up the le system error number in the MPE/iX Intrinsics Reference Manual . ACTION 32208 MESSAGE lename - Security Violation (ATCERR 32208, FSERR error number ).
32210 MESSAGE lename - End-of-File encountered (ATCERR 32210). CAUSE The physical end-of- le has been found in the speci ed le. If the ATCLOG le has been edited, this error can occur when the IMAGE/SQL utility attempts to write log records because some editors automatically insert end-of- le markers. If this happens with the ATCLOG le, le equate ATCLOG to a new le and turn the logging on. If this happens with another le, contact the HP Response Center. ACTION 32211 MESSAGE lename - FREAD Error.
32214 MESSAGE lename - File being used by IMAGE/SQL Utility (ATCERR 32214). CAUSE The speci ed le is being used by the utility. This occurs if you try to XEQ a le that is already opened by the IMAGE/SQL utility for logging. File equate ATCLOG to a di erent le. ACTION 32215 MESSAGE Cannot close lename (ATCERR 32215, FSERR error number ). CAUSE The speci ed le could not be closed. Examine the le to determine the cause of the error, then rerun the program. ACTION 32216 MESSAGE Error in CATREAD.
32218 MESSAGE user name - User name identifier > 8 CAUSE The user name speci ed is more than eight characters long. Specify a user name less than or equal to eight characters. ACTION 32219 MESSAGE lename - Group name identifier > 8 chars (ATCERR 32219). CAUSE The group name in the speci ed le name is more than eight characters long. Specify a group name less than or equal to eight characters. ACTION 32220 MESSAGE lename - Account name identifier > 8 chars (ATCERR 32220).
DBCORE Errors 32301 MESSAGE Unexpected DBCORE error (ATCERR 32301, DBCORE error number , error number , error number , error number ). CAUSE A DBCORE subsystem error has occurred. May be caused by using the ATTACH or DETACH commands when other processes are accessing the DBEnvironment. Obtain exclusive access to the DBEnvironment before using the ATTACH or DETACH commands. Use the ISQL SELECT command to check if other users are accessing the DBEnvironment: ACTION isql=> SELECT * FROM SYSTEM.
Preprocessor Errors 32351 MESSAGE ALLBASE/SQL CONNECT command failed (ATCERR 32351). CAUSE A preprocessed CONNECT failed. This message is followed by an ALLBASE/SQL error message. Refer to the ALLBASE/SQL Message Manual for more information. ACTION 32352 MESSAGE ALLBASE/SQL RELEASE command failed (ATCERR 32352). CAUSE A preprocessed RELEASE failed. This message is followed by an ALLBASE/SQL error message. Refer to the ALLBASE/SQL Message Manual for more information.
32355 MESSAGE ALLBASE/SQL DROP TABLE command failed (ATCERR 32355). CAUSE A preprocessed DROP TABLE failed. This message is followed by an ALLBASE/SQL error message. Refer to the ALLBASE/SQL Message Manual for more information. ACTION 32356 MESSAGE ALLBASE/SQL COMMIT WORK command failed (ATCERR 32356). CAUSE A preprocessed COMMIT WORK failed. This message is followed by an ALLBASE/SQL error message. Refer to the ALLBASE/SQL Message Manual for more information.
32359 MESSAGE ALLBASE/SQL ADD user TO GROUP command failed (ATCERR 32359). CAUSE A preprocessed ADD user failed. This message is followed by an ALLBASE/SQL error message. Refer to the ALLBASE/SQL Message Manual for more information. ACTION 32360 MESSAGE ALLBASE/SQL REMOVE user FROM GROUP command failed (ATCERR 32360). CAUSE A preprocessed REMOVE user failed. This message is followed by an ALLBASE/SQL error message. Refer to the ALLBASE/SQL Message Manual for more information.
32363 MESSAGE ALLBASE/SQL GRANT CONNECT TO user command failed (ATCERR 32363). CAUSE A preprocessed GRANT CONNECT failed. This message is followed by an ALLBASE/SQL error message. Refer to the ALLBASE/SQL Message Manual for more information. ACTION 32364 MESSAGE ALLBASE/SQL REVOKE SELECT FROM table command failed (ATCERR 32364). CAUSE A preprocessed REVOKE SELECT failed. This message is followed by an ALLBASE/SQL error message. Refer to the ALLBASE/SQL Message Manual for more information.
32402 MESSAGE Bad Maintenance Word (ATCERR 32402). CAUSE An incorrect maintenance word was supplied. Supply the correct maintenance word. ACTION 32403 MESSAGE Maintenance word not specified (ATCERR 32403). CAUSE A maintenance word is required, but was not speci ed. Specify the maintenance word. ACTION 32404 MESSAGE Non creator access. Command disallowed (ATCERR 32404). CAUSE The command cannot be executed by anyone other than the TurboIMAGE/XL database creator.
32408 MESSAGE Invalid DBOPEN mode (ATCERR 32408). CAUSE The DPOPEN mode speci ed in ADD/UPDATE USER command is not valid. Specify a DBOPEN mode greater than or equal to ve and less than or equal to eight. ACTION 32409 MESSAGE Invalid user name (ATCERR 32409). CAUSE The user name is not in the correct format. Specify the correct user name format username@accountname . ACTION 32410 MESSAGE Cannot create ATCINFO file (ATCERR 32410, FSERR error number ). CAUSE Failed to create the ATCINFO le.
32412 MESSAGE Cannot purge ATCINFO file (ATCERR 32412, FSERR error number ). CAUSE Failed to purge the ATCINFO le due to the speci ed le system error. Look up the le system error number in the MPE/iX Intrinsics Reference Manual . ACTION 32413 MESSAGE Cannot close ATCINFO file (ATCERR 32413, FSERR error number ). CAUSE Failed to close the ATCINFO le due to the speci ed le system error. Look up the le system error number in the MPE/iX Intrinsics Reference Manual .
32419 MESSAGE Region is busy (ATCERR 32419). CAUSE The current TurboIMAGE/XL database is in use by some other process. Wait a while and retry the command. ACTION 32421 MESSAGE ATCINFO procedure error (ATCERR 32421, ATCSTAT error number , FSERR error number ). CAUSE An internal error has occurred while accessing the ATCINFO le. Try RECOVER command in IMAGESQL utility. If the error still occurs, call the HP Response Center.
32423 MESSAGE Region disabled. Detach before proceeding (ATCERR 32423). CAUSE A previous command or a system crash caused the information in the ATCINFO le to become unstable. Detach and then attach the database. Then, retry the command. Caution: the detaching of a disabled region unmaps all customized mapping. The detach is required to \clean up" the environment. ACTION 32424 MESSAGE Owner name > 17 chars (ATCERR 32424). CAUSE An owner name longer than 17 characters was speci ed.
32427 MESSAGE Maximum number of columns (255) exceeded (ATCERR 32427). CAUSE The maximum number of columns in an ALLBASE/SQL table is 255. This limit was exceeded while trying to split an item. Reduce the number of split elds. ACTION 32428 MESSAGE Maximum length of columns (3996 bytes) exceeded (ARCERR 32428). CAUSE The maximum limit of column width in an ALLBASE/SQL table is 3996 bytes. This limit was exceeded while trying to do SPLIT/UPDATE TYPE.
32431 32432 MESSAGE name - Invalid ALLBASE/SQL name CAUSE ACTION The name speci ed contains illegal characters or has an illegal format. Specify a legal ALLBASE/SQL name. For further reference see the ALLBASE/SQL Reference Manual . MESSAGE name - Duplicate name (ATCERR CAUSE The speci ed name has already been de ned. Specify an unique name. ACTION 32434 32435 MPE status error (ATCERR 32434, MPESTAT error number , error number ).
32437 MESSAGE SQLDBE not set (ATCERR 32437). CAUSE The SET SQLDBE dbenvironment command has not been issued. Issue a SET SQLDBE dbenvironment command. ACTION 32438 MESSAGE ALLBASE/SQL DBE language does not match the TurboIMAGE language (ATCERR 32438). CAUSE The native language of the DBEnvironment and the TurboIMAGE/XL database do not match. Contact your DBA to change the native language of either one of the databases so they match. ACTION 32439 MESSAGE Cannot map TurboIMAGE type P1.
32441 32442 MESSAGE User name not found (ATCERR 32441). CAUSE ACTION The user name speci ed in the DELETE/UPDATE USER command does not exist. Specify the correct user name. MESSAGE TurboIMAGE/XL database - Access is CAUSE Access to the TurboIMAGE/XL database has been disabled. Contact your DBA to enable access to the TurboIMAGE/XL database. Refer to the discussion of the DBUTIL ENABLE command in the ACTION disabled (ATCERR 32442). TurboIMAGE/XL Database Management System Reference Manual .
B SALES Database Schema The SALES database is used in the examples throughout this guide.
NAME: ENTRY: CUSTOMER CUSTOMER# LAST-NAME FIRST-NAME INITIAL STREET CITY STATE ZIP CREDIT-RATING CAPACITY: 201(7); ,M(14/11,18); (1) , , , , , , , , ; NAME: ENTRY: PRODUCT ,M(13,14/12,18); PRODUCT# (2) PRODUCT-DESCRIPT CAPACITY: 300(16); NAME: ENTRY: VENDOR VENDOR STREET CITY STATE ZIP CAPACITY: 201(12); , ; ,M(13/12,18); (1) , , , , ; NAME: ENTRY: INVENTORY ,D(12,14/13,18); PRODUCT# ( PRODUCT ON-HAND-QTY VENDOR (!VENDOR OTHER-VENDORS UNIT-COST LAST-SHIP-DATE ( DATE-MASTER LOCATION-BIN PART-INFO
C IMAGE/SQL and Database Utilities DBUTIL contains several enhancements to support IMAGE/SQL. DBUTIL Displaying Information If the TurboIMAGE/XL database which is also enabled for third-party indexing (TPI), is attached to any DBEnvironments, the ALL parameter of the SHOW command displays this information, as the following example illustrates: :DBUTIL HP30391C.05.09 TurboIMAGE/XL:DBUTIL (C)COPYRIGHT HEWLETT-PACKARD COMPANY >>SHOW SALES ALL For database SALES Maintenance word is not present.
DBUTIL Enhancements for TPI DBUTIL is enhanced for TPIs. The ENABLE, DISABLE, and SHOW statements have been modi ed for TPI registration as follows: ENABLE database FOR INDEXING If your database is already attached to at least one DBE and you enable it for indexing (TPIs must be con gured before you use the ENABLE command), an attempt is made to enter de nitions for the TPIs in all DBEs to which the database is attached.
SQLUtil SQLUtil contains two enhancements to support IMAGE/SQL: The ALL option of the SHOWDBE command displays the name of the ATCINFO le if any TurboIMAGE/XL databases are attached. If any TurboIMAGE/XL databases are attached and you purge the DBEnvironment, the PURGEDBE command automatically detaches them. The following example illustrates these enhancements: :SQLUTIL SAT, MAY 12, 1990, 2:59 PM HP36216-02A.21.05 DBE Utility/3000 HP SQL/XL (C)COPYRIGHT HEWLETT-PACKARD CO.
SQLGEN is a utility program for database administrators that generates the SQL commands necessary to re-create all or part of a DBEnvironment. The output from SQLGEN is a command le (sometimes called a schema) that can be used as input to ISQL in re-creating database objects. SQLGEN SQLGEN also contains some enhancements for IMAGE/SQL. The following commands include mapped table de nitions if any TurboIMAGE/XL databases are attached to the DBEnvironment.
D SQL Exceptions When SQL is used to access a TurboIMAGE/XL database, certain statements and options are unavailable or do not behave exactly as described in the ALLBASE/SQL Reference Manual . This appendix lists the known exceptions and also lists the SQL statements without exceptions. SQL Statements with Exceptions ALTER TABLE ALTER TABLE cannot be used to add a column to an IMAGE/SQL table. For example, the command isql=> alter table sales.
BEGIN ARCHIVE SQL archive logging does not capture modi cations made to IMAGE/SQL databases. TURBOSTORE Online Backup requires special procedures when TurboIMAGE/XL databases (and therefore IMAGE/SQL databases) are in use during the store operation. BEGIN WORK IMAGE/SQL transactions are managed by using the TurboIMAGE/XL intrinsics DBXBEGIN and DBXEND. The BEGIN WORK statement does not invoke DBXBEGIN until the rst modi cation or repeatable read is requested for an IMAGE/SQL table.
CREATE INDEX CREATE INDEX cannot be used to create an index on an IMAGE/SQL table. The command isql => create index sales.newindex on sales.customer (last_name asc); invokes this error: Operation not allowed on non-sql table. CREATE SCHEMA CREATE TABLE DECLARE CURSOR DELETE (DBERR 2454) CREATE SCHEMA cannot be used to de ne an IMAGE/SQL database, but can be used to de ne a view. CREATE TABLE is not supported.
DROP VIEW INSERT Cannot be used to drop a view created by IMAGESQL. A view created by IMAGE/SQL can be dropped only by detaching the database with the IMAGE/SQL DETACH command. If SQL detects an error during an INSERT statement that references an IMAGE/SQL table, the current transaction is aborted regardless of the setting of the SET DML ATOMICITY and SET CONSTRAINTS statements.
SET CONSTRAINTS SET DML ATOMICITY START DBE NEW TRANSFER OWNERSHIP UPDATE If a modi cation error occurs within an IMAGE/SQL transaction, the current transaction is aborted regardless of the setting of SET CONSTRAINTS. If a modi cation error occurs within an IMAGE/SQL transaction, the current transaction is aborted regardless of the setting of SET DML AUTOMICITY. The default DBEFile size of 150 pages used by START DBE NEW may not be su cient to allow a database to be attached with IMAGESQL.
SQL Statements without Exceptions D-6 SQL Exceptions The following SQL statements behave as documented in the ALLBASE/SQL Reference Manual : ADD DBEFILE ADD TO GROUP ALTER DBEFILE ASSIGNMENT (=) BEGIN BEGIN DECLARE SECTION CLOSE CONNECT CREATE DBEFILE CREATE DBEFILESET CREATE GROUP CREATE PROCEDURE CREATE RULE CREATE TEMPSPACE CREATE VIEW DECLARE DESCRIBE DISABLE RULES DISCONNECT DROP DBEFILESET DROP GROUP DROP INDEX DROP MODULE DROP PROCEDURE DROP RULE DROP TEMPSPACE DROP VIEW ENABLE RULES END DECLARE S
RAISE ERROR REFETCH RELEASE REMOVE DBEFILE REMOVE FROM GROUP RESET RETURN SET CONNECTION SET MULTITRANSACTION SET PRINTRULES SET USER TIMEOUT SQLEXPLAIN START DBE START DBE NEWLOG STOP DBE TERMINATE USER UPDATE STATISTICS VALIDATE WHENEVER WHILE SQL Exceptions D-7
E SQL Views for Indices This appendix is new with the G.2 version of this manual. It includes information on executing SQLINSTL and ODBCVIEW, as well as information on these four views that are related to indices: SYSTEM.IMAGEKEY CATALOG.IMAGEKEY SYSTEM.TPINDEX CATALOG.TPINDEX Information related to these views for B-Tree indices is also included: SYSTEM.INDEX CATALOG.
:ISQL isql=> connect to 'testdbe'; isql=> start odbcview.pub.sys; ... isql=> commit work; isql=> connect to 'rtdbe'; isql=> start odbcview.pub.sys; isql=> commit work; isql=> exit; You may see errors if you are executing this le for the DBEnvironment for the rst time. These errors may be ignored. You can execute this command for all DBEnvironments in one session. The B-Tree indices can be observed in the existing views, SYSTEM.INDEX by the DBA and CATALOG.INDEX by others.
There are four SQL views added for TurboIMAGE/XL per DBEnvironment for the G.1 version. These are the SQL views for TurboIMAGE/XL hash indices on key items and search items and for TPIs: SYSTEM.IMAGEKEY CATALOG.IMAGEKEY SYSTEM.TPINDEX CATALOG.TPINDEX Views for TurboIMAGE/XL SYSTEM.IMAGEKEY and CATALOG.IMAGEKEY The DBA can view all TurboIMAGE/XL hash indices associated with a database by examining SYSTEM.IMAGEKEY.
OWNER UNIQUE Name of the TurboIMAGE/XL database (or the owner name used during the ATTACH) on which the key is de ned. Uniqueness indicator: 0 if duplicates are allowed, that is, the index is not unique. 1 if duplicates are not allowed, that is, the key is unique. NUMC COLNUMS NDISTINCT PRIMARIES SCCCOUNT DCCCOUNT SYSTEM.TPINDEX and CATALOG.TPINDEX Hash index on the key item of a master data set (both automatic and manual) is always unique, except when de ned on P and Z (decimal) data types.
Columns in SYSTEM.TPINDEX and CATALOG.TPINDEX The following columns exist in both SYSTEM.TPINDEX and CATALOG.TPINDEX (NPAGES, NLEVELS, NLEAVES, NDISTINCT, NFIRST, NPERKEY, and CCOUNT are not calculated or used in G.2): INDEXNAME Name of the TPI. The following su x is used by IMAGESQL when registering TPIs: TABLENAME OWNER UNIQUE Tn (Where n can be 1 to 400 depending on the TPIs that exist on the database.) Name of the data set on which the TPI is de ned.
CTIME COLDIRS Time of creation: yyyymmddhhsstt. A vector of 16 direction entries, which indicates the direction of the corresponding column in the index de nition. In ISQL, each column number is displayed as a eld of 4 hexadecimal digits. 5 Ascending. 6 Descending. SYSTEM.INDEX and CATALOG.
Columns in SYSTEM.INDEX and CATALOG.INDEX The following columns are in both SYSTEM.INDEX and CATALOG.INDEX: INDEXNAME Name of the TurboIMAGE/XL item, plus a su x. The following su xes are used by IMAGE/SQL: B1 is used when entering the de nition for B-Tree index on the master key item. Vn is used when entering the de nition for B-Tree index on the related search item of detail data set, where n is the path number from 1 to 16. All other column names are the same as for SYSTEM.TPINDEX and CATALOG.TPINDEX.
F Date/Time API Date/Time API Description The collection of conversion routines called the \Date/Time Application Programming Interface (API)" allows conversion of data to and from internal format of ALLBASE/SQL date/time values programmatically. You can enter dates, times, and intervals through the SQL interface in the format of the ALLBASE/SQL date/time data types and store it in the same format in your TurboIMAGE/XL database.
UPDATE TYPE K8 IN * TO DATE After you update your K8 data type to one of the SQL date/time data types for SQL interface, you may want to read from and write to these K8 data type elds in your TurboIMAGE/XL database using TurboIMAGE/XL applications. The eld is still K8 for TurboIMAGE/XL applications; it is updated to one of ALLBASE/SQL date/time types for the SQL interface.
DBTODATE Syntax DBTODATE (charval, stringlen, format, fmtlen, dateval, error) Parameters charval stringlen format fmtlen dateval error 4-byte address of an array of characters holding the character representation of a date. 4-byte integer length of charval in bytes. 4-byte address of an array of characters holding the format speci cation of the input character string, charval . Only valid format speci cations for the TO DATE function are allowed. 4-byte integer length of format in bytes.
DBTODTTM Syntax DBTODTTM (charval, stringlen, format, fmtlen, dttmal, error) Parameters charval stringlen format fmtlen dttmal error 4-byte address of an array of characters holding the character representation of date-time value. 4-byte integer length of charval in bytes. 4-byte address of an array of characters holding the format speci cation of the input character string, charval . Only valid format speci cations for the TO DATETIME function are allowed. 4-byte integer length of format in bytes.
DBTOCHAR Syntax DBTOCHAR(dateval,datatype,format,fmtlen,charval,bu en,error) Parameters dateval datatype format fmtlen charval bu en error 4-byte address of the 16-byte date, time, datetime, or interval value stored in the ALLBASE/SQL date/time format to be converted. 4-byte integer representing the data type of the input, DATEVAL.
DBTOINT Syntax DBTOINT(dateval, datatype, format, fmtlen, intval, error) Parameters dateval datatype format fmtlen intval error F-6 Date/Time API 4-byte address of the 16-byte date, time, datetime, or interval value stored in the ALLBASE/SQL date/time format to be converted. 4-byte integer representing the data type of the input, DATEVAL.
Glossary ATC The acronym, ATC, stands for ALLBASE/Turbo CONNECT. The terms Turbo CONNECT and ALLBASE/Turbo CONNECT are synonymous with IMAGE/SQL. The acronym, ATC, appears in error messages for IMAGE/SQL (for example, ATCERR or ATCWARN). ATCINFO A permanent privileged le containing mapping information about data types and user security. By default, it is named DBEnvironmentName CR. If you want to set a le equation for this le, you must do so before attaching any TurboIMAGE/XL databases.
Column The vertical component of a table. DBA (Database Administrator) A database administrator of the DBEnvironment. You must be a DBA of the DBEnvironment to which the TurboIMAGE/XL database is attached to issue most IMAGE/SQL utility commands. The creator of the DBEnvironment is automatically a DBA. Other ALLBASE/SQL users can be granted DBA authority by a DBA. DBC (Database Creator) The creator of the TurboIMAGE/XL database.
Detached Database A TurboIMAGE/XL database whose data cannot be accessed through a DBEnvironment. No information about the TurboIMAGE/XL database is stored in the DBEnvironment. A TurboIMAGE/XL database must be detached from a DBEnvironment before it is restructured. Explicit B-Tree Index A B-Tree index created using DBUTIL or DBSCHEMA on the master data set key item. See \B-Tree Index." IMAGE/SQL View A view created by IMAGE/SQL based on mapped tables. IMAGESQL A utility program for IMAGE/SQL.
Mapping The process IMAGE/SQL uses to allow a TurboIMAGE/XL database to emulate a DBEnvironment database. Mapping takes place for TurboIMAGE/XL names, data sets, data items, data item types, and data security. Native SQL Column A column belonging to an ALLBASE/SQL table. Native SQL Table A table created by ALLBASE/SQL. Owner A term used to de ne ownership of ALLBASE/SQL tables, views, and other ALLBASE/SQL objects.
SQLMON A utility program to help you monitor DBEnvironment performance. SQLUtil A utility program that assists the database administrator with DBEnvironment maintenance, backup, and recovery. SQLUtil also lets you modify the startup parameters for a DBEnvironment. It is a di erent utility from the IMAGE/SQL utility. SQLVer A utility to check the version strings of the ALLBASE/SQL les. System Catalog An ALLBASE/SQL database of information about the DBEnvironment. It is owned by SYSTEM.
Index A aborted transaction, 6-2 accessing mapped tables, 3-10 accounts attaching from di erent, 2-7 adding a log le, 2-30 adding IMAGE/SQL users, 2-17{18 example of, 2-17 getting ready, 2-17 reference information for, 2-18 related events, 3-7 ADD USER, 4-2 administering IMAGE/SQL, 2-2 ALLBASE/Turbo CONNECT, 1-2 ALTER TABLE, D-1 ATC, 1-2 ATCINFO le characteristics of, 3-2 DBEnvironmentName CR, 2-47 reference information for, 2-38 setting a le equation for, 2-37 when purged, 2-32 ATCLOG le reference informa
B C D Index-2 basic terms, 1-2 batch using IMAGE/SQL utility command les in, 2-43 BEGIN ARCHIVE, D-2 beginning an IMAGE/SQL utility session, 1-4 BEGIN WORK, D-2 B-Tree indices viewing, E-2 with ATTACH, 4-5 CATALOG.IMAGEKEY, E-3 CATALOG.INDEX, E-6 CATALOG.
de ned, 1-3 DBECreator de ned, 1-2 DBEFile maximum size, 2-10 DBEFILE0 size considerations, 2-7 DBELOG1 size considerations, 2-7 DBEnvironment con guring, 2-7{11 de ned, 1-2 setting a maintenance word, 2-11 DBEnvironmentName CR le, 2-47 characteristics of, 3-2 DBEnvironmentName TC le characteristics of, 3-3 DBEUserID, 2-18 DBNameTC , 3-3 DBOPEN mode, 2-27, 5-1, 5-2 DBTOCHAR, F-5 DBTODATE, F-3 DBTODTTM, F-4 DBTOINT, F-6 DBTOITVL, F-4 DBTOTIME, F-3 DBUTIL enhancements for IMAGE/SQL, C-1 deadlocks, 5-2 avoidin
DISPLAY OPTIONS, 4-14 DISPLAY SQLDBES, 4-15 DISPLAY TURBODBS, 4-16 DISPLAY USERS, 4-17 DROP DBEFILE, D-3 DROP TABLE, D-3 DROP VIEW, D-4 E F G Index-4 ECHO, 4-19 error messages DBCORE errors, A-19 le system errors, A-14 general, A-23 IMAGE/SQL, A-1 preprocessor, A-20 warning messages, A-11 example of connecting to a DBEnvironment , 1-6 deleting IMAGE/SQL users, 2-28 detaching a TurboIMAGE/XL database, 2-31 displaying TurboIMAGE/XL database information, 2-34 issuing MPE/iX commands from the IMAGE/SQL util
H I HELP, 4-21 ID DBEUserID, 2-18 IMAGE/SQL and database utilities, C-1 at run time, 3-9 data access, controlling, 3-7 data type mapping, about, 3-8 DBOPEN modes for, 2-27 environment, maintaining, 2-2 environment, setting up, 2-2 error messages, A-1 introduction to, 1-2{6 performance considerations, 3-10 security, about, 3-7 security, to control IMAGE/SQL user access, 3-7 terms, introduction to basic, 1-2 users, adding, 2-17{18 users, reason for explicitly adding, 3-7 users, related events when adding, 3
XEQ , 4-38 IMAGE/SQL utility tasks, 2-4 checklists of, 2-2 numbered list of, 2-6 summary of commands needed to perform, 2-6 In data type mapping, 2-21 indices B-Tree views, E-2 third-party, C-2 views for third-party, E-1 INSERT, D-4 installing ALLBASE/SQL, vii INTERVAL, 2-21 invoking the IMAGE/SQL utility, 1-4 isolation level, 5-1 ISQL, 1-2 example, 1-6, 2-8, 2-44 issuing IMAGE/SQL utility commands prerequisites for, 2-4 issuing MPE/iX commands from the IMAGE/SQL utility, 2-36 J K L M Index-6 Jn data t
controlling access to, 3-7 de ned, 1-2 naming conventions for, 3-4 mapping, 1-2 mapping data types table of defaults and alternatives, 2-21 maximum pages, 2-10 MAXIMUM TIMEOUT, 2-8 messages.
Q R S Index-8 queries, 3-10 QUIT, 4-24 RECOVER, 4-25 REDO, 4-26 reference information, C-1{4 ATCINFO, 2-38 ATCLOG, 2-38 repeatable read, 6-1 resetting ATCLOG le equations from within the IMAGE/SQL utility, 2-37 restrictions on transactions, 6-2 restructuring in the IMAGE/SQL environment checklist for, 2-2 REVOKE, D-4 Rn data type mapping, 2-22 ROLLBACK WORK, 5-1, 6-1, D-4 run time IMAGE/SQL at, 3-9 SALES database schema, B-1 sample database schema, B-1 SAVEPOINTS, D-4 search items in queries, 3-10 secur
reference information for, 2-25 when to perform, 2-25 SQLGEN, C-4 SQLINSTL, vii, E-1 SQLMigrate, vii SQL naming conventions, 2-25 SQLUtil, 1-2 enhancements for IMAGE/SQL, C-3 using to set DBEnvironment maintenance word, 2-11 SQL views for indices, E-1 START DBE NEW, 2-10, D-5 SYSTEM.IMAGEKEY, E-3 SYSTEM.INDEX, E-6 SYSTEM.
restrictions, 6-2 TRANSFER OWNERSHIP, D-5 TurboIMAGE/XL database passwords, protecting, 3-6 purging an attached, C-1 security, protecting passwords, 3-6 U V W Index-10 Un data type mapping, 2-21 UPDATE, D-5 UPDATE TYPE, 4-33, F-1 UPDATE USER, 4-36 UPDATE WHERE CURRENT, D-5 updating IMAGE/SQL data type mapping, 2-19{22 example of, 2-20 getting ready, 2-19 reference information for, 2-21 updating IMAGE/SQL user information, 2-26{27 example of, 2-26 getting ready, 2-26 reference information, 2-27 user-crea
