========================================================================= G C O S 8 R E H O S T I N G E X P R E S S v3.1.04 V4.0.04 Windows NT/2000/XP RELEASE ========================================================================= REHOSTING EXPRESS SUPPORT NOTES =============================== TABLE OF CONTENTS ================= INTRODUCTION REQUIREMENTS INSTALLATION DATABASE REHOSTING INFORMIX ORACLE IBM DB2 MICROSOFT SQL SERVER HINTS & TIPS KNOWN LIMITATIONS/ERRORS INTRODUCTION ============ This document describes the GCOS8 Rehosting Express (RHE8) option, whihc may be installed as an optional component of GCOS8 Mainframe Express (MFE8) and GCOS8 Net Express (NE8). The RHE8 option should be used when MFE8/NE8 is used to support a RHE8 run-time environment. REQUIREMENTS ============ RHE8 use a standard Relational Database System to host the IDS-II database emulation. Following RDBMS software is supported: INFORMIX IDS.2000 Version 9.2 ORACLE 8/8i/9i IBM DB2 Version 7/8 Microsoft SQL Server 7.0/2000 INSTALLATION ============ RHE8 is an optional part of MFE8 release v2.5.04, and you need to install MFE8/NE8, ref. MFE8/NE8 documentation for details, with the RHE option enabled. The MFE8 environment may be used to test application components while running in the Re-hosting environment, i.e. using RDBMS. If you have an existing MFE8 environment, you need to move application components and configuration to the new RHE8 environment. Note: Schemas and subschemas must be validated for RHE8, however programs checked/compiled within a MFE8 need not be re-checked/ compiled for use in the RHE8 environment. Note: Existing databases MUST be unloaded with nnHDBU, either from GCOS8 or MFE8 environment. DATABASE REHOSTING ================== RHE8 use a standard Relational Database System to host the IDS-II database emulation. Following RDBMS software is supported: INFORMIX IDS.2000 Version 9.2 ORACLE 8/8i/9i IBM DB2 Version 7/8 Microsoft SQL Server 7.0/2000 Each of these RDBMS emulation are described below, however although IDS-II database access will be functionally equivalent for your applicarion programs, the following general preparations must be considered. Dbkey allocation ---------------- Dbkey allocation for GCOS is defined for an area and the dbkey allocation and optional reserve reflect the number of records that can be stored in an area. As records are deleted, dbkeys are reused. Dbkey allocation for RHE8 is defined for a record and the dbkey allocation reflect the number of records (of this type) that can be stored during the life-cycle of the database. As records are deleted, dbkeys are NOT reused. To prevent running out of dbkeys, you must consider the dbkey allocation carefully before loading your production database for use in a rehosted environment. Note: It is possible re-gain lost dbkeys using an own developed SQL script, however you must keep in mind set relations, which are using the dbkey. Hidden fields ------------- CHAR fields in an IDS-II schema can contain 'hidden' fields, and such fields MUST always be converted during unload/load if the are in packed or binary formats, otherwise data will be incorrect and for some RDBMS software considered illegal. NULL characters (low-values) are not allowed in CHAR fields for some RDBMS software. Where applicable you can use the NULL-FIX settings to force null characters to spaces (works both for load and application programs). Some RDBMS software will not allow packed or binary data in CHAR, and you then need to change the format to DISPLAY. This may affect the size of the CHAR field in schema/subschema. The PDATA-TRACE setting may be used to document presence of packed or binary data in CHAR fields (works both for load and application programs). Phantom pointers ---------------- RHE8 will automatically adapt dbkeys for records and sets, however it cannot adapt phantom pointers (data field with dbkey pointer) automatically. If phantom pointers are used, you need to amend the schema configuration rules before you generate unload/load programs, ex. ; ; ---------------------------------------------- ; Phantom pointer Configuration (Rehosting only) ; ---------------------------------------------- ; ; During rehosting to RDBMS database, the GCOS8 dbkey is changed ; and set pointers are adjusted. When phantom pointers are used ; you need to configure these, otherwise these will NOT be ; adjusted and thus be invalid. When this configuration is ; present, the load program will perform a Phase 3, where ; such pointers are adjusted. ; ; Note: The records referenced in phantom pointers MUST be ; loaded with the records where the phantom pointers are ; define (in same load run), or the referenced record ; must have been loaded in a previous load run. ; Alternatively you must split the load run into ; phases _1, _2 and _3, to ensure the referenced ; records are loaded, before the phantom pointers ; are adjusted. ; ;[PHANTOM,field,record] [PHANTOM,TRANSLINK,TRANSPOST] [PHANTOM,TRANSLINKX,TRANSPOSTX] Area use -------- For GCOS you could store records in multiple areas through area selection, using a DB parameter of DB procedure. For RHE8 areas are considered logical and an attribute of a table, however functionally area selection is performed equivalent to GCOS. You an however suppress all area selection via the AREA-SELECT setting. If supressed, all records are stored in the first realm (from WITHIN clause), no area attribute is associated with the table and DB parameters or procedures are ignored. Note: Access to other areas (non-first) are still possible, however these are all empty. Space allocation ---------------- For GCOS the physical space allocation was controlled via schema DMCL and the database could (or had to) be spread amongst multiple physical files. For RHE8 all IDS-II databases (all schemas) are rehosted in one instance of the target RDBMS software, and the physical space allocation can be left entirely to the target RDBMS software or partially controlled by you. The Location database setting for a schema controls space allocation as follows: CURRENT = use default tablespace for all tables SCHEMA = create and use a schema specific tablespace AREA = create and use an area specific tablespace INFORMIX RDBMS ============== Technqiues ---------- All records have a unique dbkey field, which is used as primary key. The dbkey field is implemented as a SERIAL datatype with a min/max range as configured for the schema. During load of database the GCOS dbkey value is remapped to the new dbkey value assigned during load. This remapping apply to set and phantom pointers. When accessing records through Calc or Index keys or sets, RHE8 will use a hint for the appropriate key, and thus force Oracle optimizer to use the correct index for access. Sets are implemented as foreign keys in the member record(s), and the dbkey of the owner is use to enforce a constraint for the set relationship. For manually sorted sets - insertion first/last/next/ prior, an additional order field is maintained. RHE8 Configuration ------------------ Following configuration entries in Database Configuration (DBMS.CFG) may be used enable and configure IDS-II via RDBMS: [SQL-SECTION] ; Enable/Disable RHE SQL-IMPLEMENTATION : INFORMIX ; INFORMIX ; ; OFF (ISAM) SERVER : ol_instance ; Name of server DATABASE : inf_ids2 ; Name of database USERID : informix ; Name of user PASSWORD : informix ; Password of user NULL-FIX : ON ; Fix null in char OFF(default)/ON NULL-TRACE : OFF ; Trace null fix OFF(default)/ON DEBUG : OFF ; SQL Debug-log OFF(default)/ON EXPLAIN : OFF ; SQL explain OFF(default)/ON WAIT-LOCK : ON ; SQL wait lock ON(default)/OFF ISOLATION : HIGH ; Isolation HIGH(default)/LOW/OFF .LOCK-SUPPORT : ON ; .LOCK ON(default)/OFF AREA-SELECT : ON ; ON(default)/OFF DECIMAL-POINT-COMMA : OFF ; OFF(default)/ON MAX-LOAD-UNIT : 10000 ; commit every n (default 500) ESQL-C : ON ; ON(default)/OFF ORDER-PROGRAM : ON ; Enforce non-sorted set ordering On(default)/OFF CASCADE-DELETE : ON ; Use constaint rules ON(Default)/OFF CHECKSUM-CHECK : OFF ; Debug OFF(default)/ON/ALWAYS SQL-IMPLEMENTATION:INFORMIX setting enables IDS-II database emulation through Informix IDS 2000. SERVER setting designate the name of the Informix IDS server instance. Default naming is ol_xxxxx. The name cannot exceed 9 characters. DATABASE setting designate the name of the Informix IDS database. It is recommended to use the default name = inf_ids2. USERID and PASSWORD setting designate which indentification to use when establishing sessions with Informix. Use the default Informix userid with appropriate password. NULL-FIX:ON setting may be used force NULL characters in CHAR type fields to SPACE. Note: NULL characters, i.e. low-values, are not allowed in CHAR type fields. NULL-TRACE:ON may be used to log information on NULL-FIX activity on the execution log (batch or TP8). Statistics show the field-name and a corresponding count for null-fix operations. DEBUG:ON may be used to log a substantial amount of information about ESQL activity on the $g8rbldir\DBESQL.LOG file. EXPLAIN:ON may be used to set explain mode for all SELECT operations performed by RHE8. The explain information is logged within RDBMS environment, ref. Informix documentation for details. WAIT-LOCK:ON is default, i.e. application will not abort until a lock-wait time-out occur. The time-out period is controlled via settings in DEFAULT-SECTION as follows: BATCH-WAIT-TIME : 180 ; Wait time in seconds, default 180 TP-WAIT-TIME : 30 ; Wait time in seconds, default 30 ISOLATION:HIGH is default, however, following this is dynamically changes as follows: For TP: The setting, i.e. HIGH, is always used For Batch: If HIGH, it is changed to LOW, except when QUERY access mode is used, then it is changed to OFF The equivalence for Informix is as follows: HIGH = REPEATABLE READ LOW = COMMITTED READ OFF = DIRTY READ .LOCK-SUPPORT:ON is default, and a .LOCK service call will cause the current record to be 'locked for update'. Alternatively .LOCK service calls can be ignored (OFF). AREA-SELECT: ON is default, OFF may be used to ignore area selection. Note: When OFF, any area selection procedures are ignored and records are always stored/retrieved from the first realm. All others realms are treated as empty. DECIMAL-POINT-COMMA: OFF is default, ON must be specified if Informix is configured to treat comma as decimal point. MAX-LOAD-UNIT: 500 is default, may be used to control how often commits are taken during load of database. ESQL-C: ON is default, and the only option for Informix. ORDER-PROGRAM: ON is default and use to ensure non-sorted sets are maintained in the appropriate FIRST/LAST/NEXT/PRIOR order. OFF may be used enable a simpler and more efficient emulation (dbkey order), which however is not compatible with GCOS. CASCADE-DELETE: ON is default and will use SQL constaint rules to enforce owner/membership relations, which is effective both for IDS-II and direct SQL database access. OFF may be used suppress use of constraint rules and emulation of ERASE ALL MEMBERS. CHECKSUM-CHECK:OFF is default, however, following settings can be used to recover from some times of memory leaks: ON - cause schema and subschema tables to be checksummed and re- loaded if corrupted, after any DB related abort ALWAYS - cause schema and subschema tables to be checksummed and reloaded if corrupted, during every DML operation Note: ALWAYS gives extensive processor overhead and should be used with care. [SCHEMA-SECTION] ; Number Location Location Dbkey ; (1-99) Schema database allocation per table ; CURRENT DEFAULT (2100000 dbkeys) ; SCHEMA nnnnnnnnn ; AREA CONFIG (separate tag) DIMS : 23 CURRENT CURRENT DEFAULT ; DIMS8 TELEX8 : 24 CURRENT CURRENT DEFAULT ; DIMS8 Schema configuration is different for RHE8, however, the schema number and location is the same as for MFE8/G8RB, likewise, the schema number should NEVER be changed. Location database may be used to control placement of the database for the schema. CURRENT (Default) will place the database within the default SBSPACE of the inf_ids2 database. SCHEMA will place the database within an SBSPACE with the same name as the schema. AREA will place the database within multiple SBSPACEs, where each SBSPACE is given the same name as an area within the schema. Note: A record can ONLY be placed in one SBSPACE, and the the first area, i.e. WITHIN clause, is always used. Area- selection is not affected by this. Note: For a production environment you should consider use of SCHEMA or AREA location, or a combination of these. DBkey allocation may be used to control the DBkey ranges within a schema. The default is 2100000, and each record within the schema is thus assigned a dbkey range of 2.1 million. CONFIG: May be used to control DBkey allocation at record level. A separate tag is used to specify DBkey allocation details, this tag must be name -INTERVAL, ex. [DIMS-INTERVAL] DEFAULT : 2100000 ; Default allocation COURSE : 40000000 ; Specific allocation SCEHDULE : 80000000 ; Sepcific allocation Note: DBkeys are NOT reused, so the range must be sufficient to support STORE/DELETE over an extended period of time, otherwise the dbkey range will be exhausted eventually. Whenever a schema (a table) is dropped and re-created, the dbkey range is reset. Note: RHE8 can ONLY work with one RDBMS Server instance. As such, all schemas and databases are loaded into the same RDBMS Server instance. Additional RHE8 environments may be setup to support multiple environments, i.e. Test and Production. Database setup & load --------------------- For RHE8, all schemas and subschemas must be re-validated. The validation environment is recorded and verified at run-time. If validation and execution environment does not match, the application will be aborted. The SQL definition of a schema must be generated through GENSQL command or function within MFE8, alternatively generation is done automatically if schema is translated and validated. The SQL definition files are placed under $g8rbddir\schema as follows: nnMTABL.SQL - create all tables and primary index nnMINDX.SQL - create all indexes (Calc, Index key) nnMCONS.SQL - create set indexes and constrains nnDTABL.SQL - create all tables and primary index nnDINDX.SQL - create all indexes (Calc, Index key) nnDCONS.SQL - create set indexes and constrains where nn = schema number Creation of an empty database can be done with the DBACCESS utility, when executed from the ol instance command prompt, ex. dbaccess inf_ids2 \schema\23MTABL.SQL dbaccess inf_ids2 \schema\23MINDX.SQL dbaccess inf_ids2 \schema\23MCONS.SQL When Database location = SCHEMA, GENSQL will generate two additional command files (BAT) as follows: nnCSPACE.BAT - Create SBSPACE for schema nnDSPACE.BAT - Drop SBSPACE for schema When Database location = AREA, GENSQL will generate similar command files (BAT) as follows: nnCSPACE.BAT - Create SBSPACE for all areas within schema nnDSPACE.BAT - Drop SBSPACE for all areas within schema Note: You need to change information and space allocation for each DBSPACE, before you execute the command file. RHE8 utilize almost the same technique for GCOS database unload, as MFE8. First you need to generate unload and load programs for each schema. This is done with DBWFL command or Generate Unload function. The GCOS8 host unload program hhHDBU.CBL and the subschema nnSCHCS.SSD is placed under your current directory. The RHE8 load program is placed within $g8rbddir\schema and automatically compiled. Transfer the nnHDBU.CBL to GCOS8, and optionally the subschema (may already be present on the host). Next compile and execute the nnHDBU program to unload the whole or parts of the host database. Next transfer the unload file(s) to RHE8 environment (UFT/FTP ascii file). Before loading the database, you need to prepare the Informix database as follows: 1) Modify the nnCSPACE.BAT and execute this to create the DBSPACE(s) for the schema. This does not apply for Database location = CURRENT. 2) Set database in 'no-logging' mode using ONDBLOG utility, ex. ONDBLOG nolog inf_ids2 3) Create tables for the schema using DBACCESS utility, ex. DBACCESS inf_ids2 \schema\23MTABL.SQL 4) Load the database using DBLOAD utility, ex. DBLOAD dims dumpdims. _nolog Repeat this step for each unload file, if multiple files were used for GCOS8 unload. 5) Create indexes and constrains for the schema using DBACCESS utility, ex. DBACCESS inf_ids2 \\schema\23MINDX.SQL DBACCESS inf_ids2 \\schema\23MCONS.SQL 6) Set database in 'logging' mode using ONDBLOG utility, ex. ONDBLOG unbuf inf_ids2 The request does not become effective until a level-0 backup is performed for the database. 7) Do a complete (level-0) backup of the database using the ONBAR utility, ex. ONBAR -b -L 0 The DBLOAD utility will execute in two phases if the unload file contains both record and set information (default). In Phase 1, only records are processed and each record is inserted in the database. A serial number (new dbkey) is assigned to each record, and GCOS8 db-key is mapped to the serial number (new dbkey) using a table snn_dbk_map. In Phase 2, only sets are processed and each set membership causes the corresponding record to be updated with the serial number (new dbkey) of the owner. If you need to re-run the DBLOAD utility, it is recommended to drop all tables or the tables involved, and re-create these, before running DBLOAD. This way serial number ranges are reset. The DBLOAD utility may be directed to perform only a single phase, ex. DBLOAD dims dumpdims. _1 DBLOAD dims dumpdims. _2 _u Logging, Backup & Restore ------------------------- Logging ------- For on-line TP8 operation, the database must be protected with logging enabled. By default, logging files are backed up automa- tically (ALARMPROGRAM setting). Should a disaster occur, the database can be fully recovered to a consistently state near the time of disaster. For batch operation, the database may or may not be protected with logging enabled, depending on operation mode and the application. Batch concurrent with on-line: Logging MUST be enabled, and the application must either take frequent checkpoints, or use the 'auto' database mode. Warning: Should the batch program apply incorrect changes to the database, you are NOT able to restore the database without poten- tial loss of changes made through on-line applications. Batch stand-alone: Logging may be enabled, however, the application must either take frequent checkpoints, or use the 'auto' database mode, otherwise the LOCKS resource may be exhausted. Batch stand-alone/heavy: Logging may be disabled, however, be aware the database MUST be backed up (level-0) whenever the logging mode is changed. The application MUST use the 'nolog' database mode, otherwise it will be aborted. For batch, use 'exclusive' database mode, if you want to prevent others (batch or TP8) from accessing the database. For batch, use 'query' database mode, if the application is only reading from the database. When running concurrent with TP8, the batch application will neither reserve locks or respect locks on records. This mode is quite similar to QUERY allocation mode on GCOS8. Backup ------ Informix IDS Server provides following levels of backup: Level-0 - A full backup of the database Level-1 - An incremental backup of changed pages (since level-0) Level-2 - An incremental backup of changed pages (since level-1) Logical Logs - A backup of all changes since last backup A level-0 backup should be performed - after initial load of database - after changing logging mode - at frequent intervals, i.e. once a week, to limit the size of level-1/2 backup and logical logs A level-1/2 backup should be performed - after completion of on-line operation day - after completion of large batch operations Note: If level-2 is requested and no level-1 exist since last level-0 backup, the backup is treated as level-1. Backup operations are requested with the ONBAR utility, ex. ONBAR -b -L 0 ONBAR -b -L 2 Note: Logical logs are backed up automatically, either when filled or when a level-n backup is requested. Restore ------- Informix IDS Server perform automatic recovery should the instance or the server machine fail. For hardware failures, please refer to Informix documentation on how to restore and recover the database to a consistent clean-point, just before the failure occurred. The need for an operational restore may arrise, if an application has logical errors and incorrect updating of the database has occurred, or you need to re-run a batch application, which have partially committed database updates (checkpoint or auto) or was run with logging disabled. By default, a restore request will perform restore and recovery to the most recent consistent clean-point. To restore to a specific operational point, i.e. just before running a batch application, a restore request must be specified with a data/time stamp equivalent to the specific operational point. Note: To ensure proper documentation of operational point and associated date/time stamp, we recommend using BATLOG command to log a meaningful message in the consolidated batch.log (ref. Batch above). Ex. BATLOG Ready to run XXXX program - backup clean-point Restore operations are requested the ONBAR utility, ex. ONBAR -r ONBAR -r -t "yyyy-mm-dd hh:mm:ss" Backup - Wrapper ---------------- A Backup Wrapper utility (DBBCK) may be used to launch database backups and record information about associated time stamps. DBBCK backup-set-name [0|1|2|W] backup-set-name = a meaningful text to identify the backup 0 = Full backup (level-0) 1 = incremental backup (changes since level-0) 2 = incremental backup (changes since level-1) W = Complete backup (level-0) Default is 2 The backup information is recorded in $g8rbddir\BACKUP.CFG. Restore - Wrapper ----------------- A Restore Wrapper utility (DBRES) may be used to launch database restores without knowledge of actual time stamps. DBRES backup-set-name|L|Last-good-backup backup-set-name = the identification used during backup L = the last-good-backup complete The associated time stamp is used to launch a restore to the point right after the backup was completed Note: All restores are cold (Informix shutdown and restarted) and the database is left in Quecient mode after the restore completes. ORACLE ====== Installation ------------ The Oracle 8/8i/9i Database may be installed either on the same NT/ 2000 as RHE8, or on any other server, including Linux and Unix platforms. When installed on another server, you must install the Oracle Client on the RHE8 server and setup appropriate connect string(s) for RHE8. Configuration ------------- Use standard Oracle tuning techniques to achieve the required performance. During load of database you should disable archive logging. Technqiues ---------- All records have a unique dbkey field, which is used as primary key. The dbkey field is implemented as a SERIAL datatype with a min/max range as configured for the schema. During load of database the GCOS dbkey value is remapped to the new dbkey value assigned during load. This remapping apply to set and phantom pointers. When accessing records through Calc or Index keys or sets, RHE8 will use a hint for the appropriate key, and thus force Oracle optimizer to use the correct index for access. Sets are implemented as foreign keys in the member record(s), and the dbkey of the owner is use to enforce a constraint for the set relationship. For manually sorted sets - insertion first/last/next/ prior, an additional order field is maintained. RHE8 always use OPTIMIZER_MODE = FIRST_ROWS. RHE8 Configuration ------------------ Following configuration entries in Database Configuration (DBMS.CFG) may be used enable and configure IDS-II via RDBMS: [SQL-SECTION] ; Enable/Disable RHE SQL-IMPLEMENTATION : ORACLE ; Oracle 8/8i/9i ; ; OFF (ISAM) DATABASE : ora_ids2 ; Database name (TNS name) USERID : scott ; Name of user PASSWORD : tiger ; Password of user NULL-FIX : ON ; Fix null in char OFF(default)/ON NULL-TRACE : OFF ; Trace null fix OFF(default)/ON DEBUG : OFF ; SQL Debug-log OFF(default)/ON ISOLATION : HIGH ; Isolation HIGH(default)/LOW .LOCK-SUPPORT : ON ; .LOCK ON(default)/OFF AREA-SELECT : ON ; ON(default)/OFF DECIMAL-POINT-COMMA : OFF ; OFF(default)/ON MAX-LOAD-UNIT : 10000 ; commit every n (default 500) EXPLAIN : OFF ; OFF(required) WAIT-LOCK : ON ; ON(required) ESQL-C : OFF ; OFF(required) ORDER-PROGRAM : ON ; Enforce non-sorted set ordering On(default)/OFF CASCADE-DELETE : ON ; Use constaint rules ON(Default)/OFF CHECKSUM-CHECK : OFF ; Debug OFF(default)/ON/ALWAYS SQL-IMPLEMENTATION:ORACLE setting enables IDS-II database emulation through Oracle 8/8i/9i. DATABASE setting designate the name of the Oracle database (TNS name). USERID and PASSWORD setting designate which indentification to use when establishing sessions with Oracle. Use the default Scott userid with appropriate password, or any other userid. NULL-FIX:ON setting may be used force NULL characters in CHAR type fields to SPACE. Note: NULL characters, i.e. low-values, are not allowed in CHAR type fields. NULL-TRACE:ON may be used to log information on NULL-FIX activity on the execution log (batch or TP8). Statistics show the field-name and a corresponding count for null-fix operations. DEBUG:ON may be used to log a substantial amount of information about ESQL activity on the $g8rbldir\DBESQL.LOG file. ISOLATION:LOW is default, however, this may changed to HIGH as required. The equivalence for Oracle is as follows: HIGH = SERIALIZABLE LOW = COMMITTED READ Note: Oracle does not support query/nolog mode of operation and will use the isolation level low/high as configured. .LOCK-SUPPORT:ON is default, and a .LOCK service call will cause the current record to be 'locked for update'. Alternatively .LOCK service calls can be ignored (OFF). AREA-SELECT: ON is default, OFF may be used to ignore area selection. Note: When OFF, any area selection procedures are ignored and records are always stored/retrieved from the first realm. All others realms are treated as empty. DECIMAL-POINT-COMMA: OFF is default, ON must be specified if Oracle is configured to treat comma as decimal point. MAX-LOAD-UNIT: 500 is default, may be used to control how often commits are taken during load of database. ESQL-C: OFF is default, and the only option for Oracle. ORDER-PROGRAM: ON is default and use to ensure non-sorted sets are maintained in the appropriate FIRST/LAST/NEXT/PRIOR order. OFF may be used enable a simpler and more efficient emulation (dbkey order), which however is not compatible with GCOS. CASCADE-DELETE: ON is default and will use SQL constaint rules to enforce owner/membership relations, which is effective both for IDS-II and direct SQL database access. OFF may be used suppress use of constraint rules and emulation of ERASE ALL MEMBERS. CHECKSUM-CHECK:OFF is default, however, following settings can be used to recover from some times of memory leaks: ON - cause schema and subschema tables to be checksummed and re- loaded if corrupted, after any DB related abort ALWAYS - cause schema and subschema tables to be checksummed and reloaded if corrupted, during every DML operation Note: ALWAYS gives extensive processor overhead and should be used with care. [SCHEMA-SECTION] ; Number Location Location Dbkey ; (1-99) Schema database allocation per table ; CURRENT DEFAULT (2100000 dbkeys) ; SCHEMA nnnnnnnnn ; AREA CONFIG (separate tag) DIMS : 23 CURRENT CURRENT DEFAULT ; DIMS8 TELEX8 : 24 CURRENT CURRENT DEFAULT ; DIMS8 Schema configuration is different for RHE8, however, the schema number and location is the same as for MFE8/G8RB, likewise, the schema number should NEVER be changed. Location database may be used to control placement of the database for the schema. CURRENT (Default) will place the database within the default SBSPACE of the inf_ids2 database. SCHEMA will place the database within an TABLESPACE with the same name as the schema. AREA will place the database within multiple TABLESPACEs, where each TABLESPACE is given the same name as an area within the schema. Note: A record can ONLY be placed in one TABLESPACE, and the the first area, i.e. WITHIN clause, is always used. Area- selection is not affected by this. Note: For a production environment you should consider use of SCHEMA or AREA location, or a combination of these. DBkey allocation may be used to control the DBkey ranges within a schema. The default is 2100000, and each record within the schema is thus assigned a dbkey range of 2.1 million. CONFIG: May be used to control DBkey allocation at record level. A separate tag is used to specify DBkey allocation details, this tag must be name -INTERVAL, ex. [DIMS-INTERVAL] DEFAULT : 2100000 ; Default allocation COURSE : 40000000 ; Specific allocation SCEHDULE : 80000000 ; Sepcific allocation Note: DBkeys are NOT reused, so the range must be sufficient to support STORE/DELETE over an extended period of time, otherwise the dbkey range will be exhausted eventually. Whenever a schema (a table) is dropped and re-created, the dbkey range is reset. Note: RHE8 can ONLY work with one RDBMS Server instance. As such, all schemas and databases are loaded into the same RDBMS Server instance. Additional RHE8 environments may be setup to support multiple environments, i.e. Test and Production. Database setup & load --------------------- For RHE8, all schemas and subschemas must be re-validated. The validation environment is recorded and verified at run-time. If validation and execution environment does not match, the application will be aborted. The SQL definition of a schema must be generated through GENSQL command or function within MFE8, alternatively generation is done automatically if schema is translated and validated. The SQL definition files are placed under $g8rbddir\schema as follows: nnMTABL.SQL - create all tables and primary index nnMINDX.SQL - create all indexes (Calc, Index key) nnMCONS.SQL - create set indexes and constrains nnDTABL.SQL - create all tables and primary index nnDINDX.SQL - create all indexes (Calc, Index key) nnDCONS.SQL - create set indexes and constrains where nn = schema number Creation of an empty database can be done with the SQL*Plus utility, ex. (first connect to appropriate database> start \schema\23MTABL.SQL start \schema\23MINDX.SQL start \schema\23MCONS.SQL When Database location = SCHEMA, GENSQL will generate two additional SQL definition files as follows: nnMSPACE.SQL - Create TABLESPACE for schema nnDSPACE.SQL - Drop TABLESPACE for schema When Database location = AREA, GENSQL will generate similar SQL definition files as follows: nnMSPACE.SQL - Create TABLESPACE for all areas within schema nnDSPACE.SQL - Drop TABLESPACE for all areas within schema Note: You need to change datafile information and space allocation for each TABLESPACE, before you execute the definition files. RHE8 utilize almost the same technique for GCOS database unload, as MFE8. First you need to generate unload and load programs for each schema. This is done with DBWFL command or Generate Unload function. The GCOS8 host unload program hhHDBU.CBL and the subschema nnSCHCS.SSD is placed under your current directory. The RHE8 load program is placed within $g8rbddir\schema and automatically compiled. Transfer the nnHDBU.CBL to GCOS8, and optionally the subschema (may already be present on the host). Next compile and execute the nnHDBU program to unload the whole or parts of the host database. Next transfer the unload file(s) to RHE8 environment (UFT/FTP ascii file). Before loading the database, you need to prepare the Oracle database as follows: 1) Modify the nnMSPACE.SQL and execute this to create the TABLESPACE(s) for the schema. This does not apply for Database location = CURRENT. 2) Set ARCHIVE LOGGING off using Oracle Console or command. 3) Create tables for the schema using SQL*Plus utility, ex. START \schema\23MTABL.SQL 4) Load the database using DBLOAD utility, ex. DBLOAD dims dumpdims. Repeat this step for each unload file, if multiple files were used for GCOS8 unload, however remember to use _update for all but the first load file, ex. DBLOAD dims dumpdims1. DBLOAD dims dumpdims2. _u DBLOAD dims dumpdims3. _u 5) Create indexes and constrains for the schema using SQL*Plus utility, ex. START \schema\23MINDX.SQL START \schema\23MCONS.SQL 6) Set ARCHIVE LOGGING on using Oracle Console or command. 7) Do a complete backup of the database using Oracle Console or command. The DBLOAD utility will execute in two phases if the unload file contains both record and set information (default). In Phase 1, only records are processed and each record is inserted in the database. A serial number (new dbkey) is assigned to each record, and GCOS8 db-key is mapped to the serial number (new dbkey) using a table snn_dbk_map. In Phase 2, only sets are processed and each set membership causes the corresponding record to be updated with the serial number (new dbkey) of the owner. An optional Phase 3 is triggered if phantom pointers are defined and involved records are detected in Phase 1 (record processing). If you need to re-run the DBLOAD utility, it is recommended to drop all tables or the tables involved, and re-create these, before running DBLOAD. This way serial number ranges are reset. The DBLOAD utility may be directed to perform only a single phase, ex. DBLOAD dims dumpdims. _1 DBLOAD dims dumpdims. _2 _u DBLOAD dims dumpdims. _3 _u (only phantom pointers) Logging ------- For on-line TP8 operation, the database must be protected with archive logging enabled. By default, logging files are backed up automatically. Should a disaster occur, the database can be fully recovered to a consistently state near the time of disaster. Batch concurrent with on-line: The application must either take frequent checkpoints, or use the 'auto' database mode to prevent TP from waiting for resources held by batch application. Batch stand-alone: The application must either take frequent checkpoints, or use the 'auto' database mode, otherwise the LOCKS resource may be exhausted. IBM DB2 ======= Installation ------------ The DB2 UDB 7/8 Database may be installed either on the same NT/ 2000 as RHE8, or on any other server, including Linux and Unix platforms. When installed on another server, you must install the DB2 Client on the RHE8 server and configure the database(s) for DB2. Configuration ------------- Use standard DB2 tuning techniques to achieve the required performance. During load of database you should use circular logging (set 'Retain log files' to NO). Technqiues ---------- All records have a unique dbkey field, which is used as primary key. The dbkey field is implemented as a SERIAL datatype with a min/max range as configured for the schema. During load of database the GCOS dbkey value is remapped to the new dbkey value assigned during load. This remapping apply to set and phantom pointers. When accessing records through Calc or Index keys or sets, RHE8 will use a hint for the appropriate key, and thus force Oracle optimizer to use the correct index for access. Sets are implemented as foreign keys in the member record(s), and the dbkey of the owner is use to enforce a constraint for the set relationship. For manually sorted sets - insertion first/last/next/ prior, an additional order field is maintained. RHE8 always use QUERY OPTIMIZATION = 0. RHE8 Configuration ------------------ Following configuration entries in Database Configuration (DBMS.CFG) may be used enable and configure IDS-II via RDBMS: [SQL-SECTION] ; Enable/Disable RHE SQL-IMPLEMENTATION : DB2 ; IBM DB2 UDB 7/8 ; ; OFF (ISAM) DATABASE : db2_ids2 ; Database name USERID : db2admin ; Name of user PASSWORD : db2admin ; Password of user NULL-FIX : OFF ; Change null to space in char OFF(default)/ON ; Null IS allowed in CHAR fields NULL-TRACE : OFF ; Trace null fix OFF(default)/ON PDATA-TRACE : OFF ; Trace 'packed' data in char OFF(default)/ON DEBUG : OFF ; SQL Debug-log OFF(default)/ON EXPLAIN : OFF ; SQL explain OFF(default)/ON ; ON = EXPLAIN MODE YES ; DB2 explain tables MUST be created first ISOLATION : HIGH ; HIGH = READ COMMITTED (not configurable) .LOCK-SUPPORT : ON ; .LOCK ON(default)/OFF AREA-SELECT : ON ; ON(default)/OFF DECIMAL-POINT-COMMA : OFF ; OFF(default)/ON MAX-LOAD-UNIT : 10000 ; commit every n (default 500) WAIT-LOCK : ON ; ON(required) ESQL-C : OFF ; OFF(required) ORDER-PROGRAM : ON ; Enforce non-sorted set ordering On(default)/OFF CASCADE-DELETE : ON ; Use constaint rules ON(Default)/OFF CHECKSUM-CHECK : OFF ; Debug OFF(default)/ON/ALWAYS SQL-IMPLEMENTATION:ORACLE setting enables IDS-II database emulation through DB2 UDB 7/8. DATABASE setting designate the name of the Oracle database (TNS name). USERID and PASSWORD setting designate which indentification to use when establishing sessions with DB2. Use the default db2admin userid with appropriate password, or any other userid. NULL-FIX:ON setting may be used force NULL characters in CHAR type fields to SPACE. Note: NULL characters, i.e. low-values, are allowed in CHAR type fields. NULL-TRACE:ON may be used to log information on NULL detections on the execution log (batch or TP8). Statistics show the field-name and a corresponding count for NULL detections. PDATA-TRACE:ON may be used to log information on 'packed' data detections on the execution log (batch or TP8). Statistics show the field-name and a corresponding count for 'packed' data detections. DEBUG:ON may be used to log a substantial amount of information about ESQL activity on the $g8rbldir\DBESQL.LOG file. EXPLAIN:ON may be used to set explain mode for dynamic SQL operations performed by RHE8. The explain information is logged in the DB2 explain tables. Note: The DB2 explain tables MUST be created before you enable this feature. Refer to DB2 documentatuion for further details. ISOLATION:HIGH is default, and equivalent to READ COMMITTED. This is not configurable. Note: DB2 does not support query/nolog mode of operation and will always use the isolation level high. .LOCK-SUPPORT:ON is default, and a .LOCK service call will cause the current record to be 'locked for update'. Alternatively .LOCK service calls can be ignored (OFF). AREA-SELECT: ON is default, OFF may be used to ignore area selection. Note: When OFF, any area selection procedures are ignored and records are always stored/retrieved from the first realm. All others realms are treated as empty. MAX-LOAD-UNIT: 500 is default, may be used to control how often commits are taken during load of database. ESQL-C: OFF is default, and the only option for Oracle. ORDER-PROGRAM: ON is default and use to ensure non-sorted sets are maintained in the appropriate FIRST/LAST/NEXT/PRIOR order. OFF may be used enable a simpler and more efficient emulation (dbkey order), which however is not compatible with GCOS. CASCADE-DELETE: ON is default and will use SQL constaint rules to enforce owner/membership relations, which is effective both for IDS-II and direct SQL database access. OFF may be used suppress use of constraint rules and emulation of ERASE ALL MEMBERS. CHECKSUM-CHECK:OFF is default, however, following settings can be used to recover from some times of memory leaks: ON - cause schema and subschema tables to be checksummed and re- loaded if corrupted, after any DB related abort ALWAYS - cause schema and subschema tables to be checksummed and reloaded if corrupted, during every DML operation Note: ALWAYS gives extensive processor overhead and should be used with care. [SCHEMA-SECTION] ; Number Location Location Dbkey ; (1-99) Schema database allocation per table ; CURRENT DEFAULT (2100000 dbkeys) ; SCHEMA nnnnnnnnn ; AREA CONFIG (separate tag) DIMS : 23 CURRENT CURRENT DEFAULT ; DIMS8 TELEX8 : 24 CURRENT CURRENT DEFAULT ; DIMS8 Schema configuration is different for RHE8, however, the schema number and location is the same as for MFE8/G8RB, likewise, the schema number should NEVER be changed. Location database may be used to control placement of the database for the schema. CURRENT (Default) will place the database within the default SBSPACE of the inf_ids2 database. SCHEMA will place the database within an TABLESPACE with the same name as the schema. AREA will place the database within multiple TABLESPACEs, where each TABLESPACE is given the same name as an area within the schema. Note: A record can ONLY be placed in one TABLESPACE, and the the first area, i.e. WITHIN clause, is always used. Area- selection is not affected by this. Note: For a production environment you should consider use of SCHEMA or AREA location, or a combination of these. DBkey allocation may be used to control the DBkey ranges within a schema. The default is 2100000, and each record within the schema is thus assigned a dbkey range of 2.1 million. CONFIG: May be used to control DBkey allocation at record level. A separate tag is used to specify DBkey allocation details, this tag must be name -INTERVAL, ex. [DIMS-INTERVAL] DEFAULT : 2100000 ; Default allocation COURSE : 40000000 ; Specific allocation SCEHDULE : 80000000 ; Sepcific allocation Note: DBkeys are NOT reused, so the range must be sufficient to support STORE/DELETE over an extended period of time, otherwise the dbkey range will be exhausted eventually. Whenever a schema (a table) is dropped and re-created, the dbkey range is reset. Note: RHE8 can ONLY work with one RDBMS Server instance. As such, all schemas and databases are loaded into the same RDBMS Server instance. Additional RHE8 environments may be setup to support multiple environments, i.e. Test and Production. Database setup & load --------------------- For RHE8, all schemas and subschemas must be re-validated. The validation environment is recorded and verified at run-time. If validation and execution environment does not match, the application will be aborted. The SQL definition of a schema must be generated through GENSQL command or function within MFE8, alternatively generation is done automatically if schema is translated and validated. The SQL definition files are placed under $g8rbddir\schema as follows: nnMTABL.SQL - create all tables and primary index nnMINDX.SQL - create all indexes (Calc, Index key) nnMCONS.SQL - create set indexes and constrains nnDTABL.SQL - create all tables and primary index nnDINDX.SQL - create all indexes (Calc, Index key) nnDCONS.SQL - create set indexes and constrains where nn = schema number A corresponding set of command files (BAT) are generated, which may be launched from a DB2 command window. Each command file contains a CONNECT, execution of SQL file and a DISCONNECT. Creation of an empty database can be done from a DB2 command window, ex. \schema\23MTABL.BAT \schema\23MINDX.BAT \schema\23MCONS.BAT When Database location = SCHEMA, GENSQL will generate two additional SQL definition files as follows: nnMSPACE.SQL - Create TABLESPACE for schema nnDSPACE.SQL - Drop TABLESPACE for schema When Database location = AREA, GENSQL will generate similar SQL definition files as follows: nnMSPACE.SQL - Create TABLESPACE for all areas within schema nnDSPACE.SQL - Drop TABLESPACE for all areas within schema Note: You need to change datafile information and space allocation for each TABLESPACE, before you execute the definition files. RHE8 utilize almost the same technique for GCOS database unload, as MFE8. First you need to generate unload and load programs for each schema. This is done with DBWFL command or Generate Unload function. The GCOS8 host unload program hhHDBU.CBL and the subschema nnSCHCS.SSD is placed under your current directory. The RHE8 load program is placed within $g8rbddir\schema and automatically compiled. Transfer the nnHDBU.CBL to GCOS8, and optionally the subschema (may already be present on the host). Next compile and execute the nnHDBU program to unload the whole or parts of the host database. Next transfer the unload file(s) to RHE8 environment (UFT/FTP ascii file). Before loading the database, you need to prepare the DB2 database as follows: 1) First bind the RHE8 interface to the DB2 database using the command: RUN WB INITBND 2) Modify the nnMSPACE.SQL and execute this to create the TABLESPACE(s) for the schema. This does not apply for Database location = CURRENT. 3) Set 'Retain log files' to NO using DB2 Control Center or DB2 command. 4) Create tables for the schema using DB2 Command window, ex. \schema\23MTABL.BAT 5) Load the database using DBLOAD utility, ex. DBLOAD dims dumpdims. Repeat this step for each unload file, if multiple files were used for GCOS8 unload, however, remember to use _update for all but the first load file, ex. DBLOAD dims dumpdims1. DBLOAD dims dumpdims2. _u DBLOAD dims dumpdims3. _u 6) Create indexes and constrains for the schema using DB2 Command window, ex. \schema\23MINDX.BAT \schema\23MCONS.BAT 7) Set 'Retain log files' to RECOVERY or CAPTURE using DB2 Control Center or DB2 command. 8) Do a complete backup of the database using DB2 Control Center or DB2 command. The DBLOAD utility will execute in two phases if the unload file contains both record and set information (default). In Phase 1, only records are processed and each record is inserted in the database. A serial number (new dbkey) is assigned to each record, and GCOS8 db-key is mapped to the serial number (new dbkey) using a table snn_dbk_map. In Phase 2, only sets are processed and each set membership causes the corresponding record to be updated with the serial number (new dbkey) of the owner. An optional Phase 3 is triggered if phantom pointers are defined and involved records are detected in Phase 1 (record processing). If you need to re-run the DBLOAD utility, it is recommended to drop all tables or the tables involved, and re-create these, before running DBLOAD. This way serial number ranges are reset. The DBLOAD utility may be directed to perform only a single phase, ex. DBLOAD dims dumpdims. _1 DBLOAD dims dumpdims. _2 _u DBLOAD dims dumpdims. _3 _u (only phantom pointers) Logging ------- For on-line TP8 operation, the database should be protected with proper 'Retain log files' setting. Should a disaster occur, the database can be fully recovered to a consistently state near the time of disaster. Batch concurrent with on-line: The application must either take frequent checkpoints, or use the 'auto' database mode to prevent TP from waiting for resources held by batch application. Batch stand-alone: The application must either take frequent checkpoints, or use the 'auto' database mode, otherwise the LOCKS resource may be exhausted. MICROSOFT SQL SERVER ==================== Currently not documented. HINTS & TIPS ============ Please refer to text above. KNOWN LIMITATIONS/ERRORS ======================== Please refer to text above. ========================================================================= GCOS8 Rehosting Express, GCOS8 Run-time Express, GCOS8 Mainframe Express and GCOS8 Net Express are trademarks of B & C Solution. All other trademarks are trade names belong to their respective companies. ========================================================================= Copyright (C) 1998-2003 B & C Solution. All Rights Reserved.