HP NonStop SQL DDL Replicator User’s Guide Abstract HP NonStop™ SQL DDL Replicator Software replicates NonStop SQL DDL operations to one or more backup systems.
Document History Part Number Product Version Published 545799-001 NonStop SQL DDL Replicator 1.
Legal Notices © Copyright 2008 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
What’s New in This Manual Manual Information HP NonStop SQL DDL Replicator User’s Guide Abstract HP NonStop™ SQL DDL Replicator Software replicates NonStop SQL DDL operations to one or more backup systems. Product Version NonStop SQL DDL Replicator AAE Supported Release Version Updates (RVUs) N/A Part Number Published 545799-005 March 2011 Document History Part Number Product Version Published 545799-001 NonStop SQL DDL Replicator 1.
HP NonStop SQL DDL Replicator User’s Guide—545799-005 ii
About This Manual This manual describes the uses, installation, and all other operational aspects of the HP NonStop SDR software. This software product replicates SQL/MP DDL operations to one or more backup systems. Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: To start the SDR monitor, see START MONITOR on page 6-15.
About This Manual General Syntax Notation { } Braces. A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list may be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example: LISTOPENS PROCESS { $appl-mgr-name } { $process-name } ALLOWSU { ON | OFF } | Vertical Line.
About This Manual Change Bar Notation a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: ALTER [ / OUT file-spec / ] LINE [ , attribute-spec ]... Change Bar Notation Change bars are used to indicate substantive differences between this edition of the manual and the preceding edition. Change bars are vertical rules placed in the right margin of changed portions of text, figures, tables, examples, and so on.
About This Manual Change Bar Notation HP NonStop SQL DDL Replicator User’s Guide—545799-005 vi
HP NonStop SQL DDL Replicator User’s Guide Legal Notices Introducing SDR 1-11-1 Introduction 1-1 Product Overview 1-2 SDR Components 1-2 Program Files 1-2 SDR Command Interpreter 1-2 SDR Monitor ($ZSDR) 1-3 SDR Runtime 1-3 SDR Updater 1-3 Data Files 1-3 SDRERROR 1-3 SDRHELP 1-3 ZSDRTMPL 1-3 SDRFLTR 1-3 ZCOMTMPL and COMFLTR 1-3 NonStop Kernel and RDF Compatibility 1-4 Installing SDR 2-1 Prerequisites 2-1 Initial Installation 2-1 Move Files from the Product Media to the Installation Subvolume 2-2 Install Pro
Disabling DDL Capture 2-10 Removing SDR Software 2-10 Changes to RDF Operations 2-10 ZRDF-EVT-Msg-736 2-11 ZRDF-EVT-Msg-908 2-11 ZRDF-EVT-Msg-700, ZRDF-EVT-Msg-705 2-11 Configuring SDR 3-1 RDF Considerations 3-1 RDF Topologies 3-1 RDF Configuration 3-1 REPLICATEPURGE 3-1 NETWORK and NETWORKMASTER 3-1 Replicating Physical VOLUMEs in an SMF Environment 3-2 INCLUDE and EXCLUDE 3-2 RDF EMS Events and the LOGFILE 3-2 Configuring SDR 3-2 Configuring SDR Capture 3-3 Enabling and Disabling SDR 3-3 Requiring DDL Ca
Special cases 4-4 Create Catalog 4-4 CREATE Collation 4-5 User Transactions 4-6 Distributed Tables and Indexes 4-6 Unaudited Tables and Indexes 4-7 SQLCI DUP Utility 4-7 DDL Replication 4-7 SDR Updater Processes 4-7 Normal vs.
CANCEL RDF-control 5-9 RETRY RDF-control 5-10 SDR Commands 6-1 Running the Command Interpreter 6-1 Command Syntax 6-2 Command Description Overview 6-2 SDR Configuration and Management Commands 6-5 ALTER 6-5 CANCEL 6-9 CREATE SYSDB 6-10 EXECUTE 6-11 HOLD 6-11 INFO GLOBALS 6-11 INFO RUNTIME 6-11 INSTALL RUNTIME 6-11 INSTALL SDR 6-12 READLOG 6-12 RELEASE 6-14 RESET 6-14 RESTART UPDATE 6-14 RETRY 6-15 START MONITOR 6-15 START UPDATE 6-16 STATUS * 6-17 STATUS RDF 6-17 STATUS SDR 6-17 STATUS UPDATE 6-18 STOP MON
FC AND! 6-28 FILEINFO 6-29 FILES 6-29 HELP 6-30 HISTORY 6-31 LOG 6-31 MONITOR 6-32 OBEY 6-32 OUT 6-32 RESET 6-32 RUN[D] 6-33 SEMICOLON 6-33 STATUS MONITOR 6-34 TIME 6-34 VOLUME 6-34 Monitor Commands 6-34 EMSLEVEL 6-34 LOG 6-35 STATUS 6-36 BACKUPCPU 6-36 SWITCH 6-36 SQL DDL Statements A-1 DDL Statements A-1 SQL object in Statement A-3 Catalog References in Statements A-3 PHYSVOL References in Statements A-3 WITH SHARED ACCESS Clauses A-4 SDR EMS Messages B-1 Informational Messages B-1 101 B-1 Critical Mess
HP NonStop SQL DDL Replicator User’s Guide—545799-005 vi
1 Introducing SDR This manual describes HP NonStop SQL DDL Replicator (SDR). This section is an introduction to SDR. The information is organized as follows: Introduction Product Overview SDR Components NonStop Kernel and RDF Compatibility Introduction HP NonStop SQL DDL Replicator (SDR) is a companion to the NonStop Remote Database Facility (RDF) subsystem.
Introducing SDR Product Overview Product Overview SDR installs quickly. Once the product files are in place, you use the SDR command interpreter, SDRCOM, to issue a few commands that set up a small configuration database and to install SDR on your system. SDR is privileged, so the installation of the product files must be performed by SUPER.SUPER. It does not require a cold load, application outage, or stopping RDF or TMF. All other operations can be performed by a member of the SUPER group.
Introducing SDR Data Files SDR Monitor ($ZSDR) The SDR monitor (SDRMON) is a NonStop process pair with the reserved name $ZSDR. It should be started at cold load time and left running at all times, regardless of the state of RDF or TMF. If the SDR monitor is executing and properly licensed, then SDR is active. SDR Runtime The SDR runtime (SDRRUNTM) captures DDL operations and stores them in appropriate locations for replication at a later time.
Introducing SDR NonStop Kernel and RDF Compatibility NonStop Kernel and RDF Compatibility SDR is a release-independent product. There is only one version of SDR and it runs on all supported NonStop servers supported by HP: NonStop S-series, NonStop H-series and NonStop J-series. SDR is also compatible with all versions of RDF (IMP, IMPX and ZLT) starting with SPR T0346AAJ (released summer 2000), with a few exceptions. SDR will not work with T0346A07 SPRs ABJ or ABO, or with base release T0346H08.
2 Installing SDR This section describes the installation of SDR. It covers the following topics: Prerequisites Initial Installation Updating SDR Software Enabling DDL Capture when Updating NonStop SQL Software or the Operating System Disabling DDL Capture Removing SDR Software Prerequisites SDR works closely with RDF and must be installed on the RDF primary and backup systems. Versions of SDR software must be the same on all systems. Read the Software Release Document before you install SDR.
Installing SDR Move Files from the Product Media to the Installation Subvolume 5. Install the SDR License 6. Enable SQL for DDL Replication 7. Install EMS templates 8. Update System Coldload Procedures Repeat the installation on the all the systems in the RDF network. Move Files from the Product Media to the Installation Subvolume The SDR product media contains the files listed in Table 2-1. Instructions for placing the SDR software in the ISV subvolume ZISDR are delivered on the distribution media.
Installing SDR Install Product Files Install Product Files The INSTALL macro is used for the initial SDR installation and for subsequent SDR product updates. When installing SDR for the first time, INSTALL performs the following steps: 1. Restores the product files from ZISDR to the operational subvolume 2. Licenses the SDR object files. You must be logged on as SUPER.SUPER to run the INSTALL macro. Because local SUPER.
Installing SDR Start an EMS distributor Running INSTALL: > INSTALL HP Nonstop(tm) SQL DDL Replicator(tm) Software File Installer *** SDR software will be installed in subvolume $system.sdr UNPAK - File decompression program - T1255G06 - (2008-06-03) Archive version: 1 File Mode RESTORE Program - T9074G08 (21JUL2008) (AFO) (C)2000 Compaq (C)2006 Hewlett Packard Development Company, L.P. Drives: (\SIERRA.
Installing SDR Create the SDR System Database Create the SDR System Database The SDR System Database (SysDB) is created using the SDR command interpreter SDRCOM. When starting SDRCOM during the initial installation, a warning message appears directing you to create a SysDB and license the SDR product. You must be a member of the SUPER group in order to create the SysDB. If you are not logged on as a SUPER user, SDRCOM displays an error message and does not create the SysDB.
Installing SDR Install the SDR License 1. Creates the REGISTRY table in subvolume $SYSTEM.SDRSYSDB. If creating tables on $SYSTEM is difficult due to system security or if $SYSTEM is not audited, specify an alternative volume using the ON volume option. SysDB may be created on any audited volume. The REGISTRY table is owned by the SUPER user that issues the CREATE SYSDB command and has a default security of “NOOO”. The REGISTRY table owner is the SDR owner who is authorized to manage SDR.
Installing SDR Enable SQL for DDL Replication After you have successfully installed your SDR license, SDRCOM notifies you that SQL is not yet configured to perform DDL capture and replication: *Warning* $SYSTEM.SYSTEM.SQLCAT is not properly configured for use with SDR. * 1669 * This file must be the NonStop SQL/MP product program and it * * must be activated for use with SDR with the following command: * * * * INSTALL SDR * * To enable DDL replication, enter the INSTALL SDR command. Note.
Installing SDR Install EMS templates Install EMS templates The file ZSDRTMPL contains EMS templates for all events generated by SDR. The file ZCOMTMPL contains templates for SDR and RDF events. Please refer to the DSM Template Services Manual for instructions about installing system templates. You can use the EMS filter file SDRFLTR with an EMS distributor to display SDR events, as described above in Start an EMS distributor on page 2-4.
Installing SDR Install Product Files for SDR Update 1. Replace the SDR software components in the product subvolume using the INSTALL macro as described below in Install Product Files for SDR Update. The INSTALL macro restarts the monitor with UPDATE. 2. Install the EMS templates. See Install EMS templates on page 2-8. 3. RESET the DDLCAPTURE global parameter if it was set only for the duration of the installation: RESET GLOBAL DDLCAPTURE. You do not need to re-issue the INSTALL SDR command.
Installing SDR Disabling DDL Capture Disabling DDL Capture To disable SDR DDL capture, set the DDLCAPTURE global parameter to DISABLED using SDRCOM command ALTER: SDRCOM 1? alter global ddlcapture disabled; --- SDR Global SDRDDLCAPTURE updated. --- Monitor $ZSDR globals refreshed. SDR displays a message in the EMS log and STATUS SDR shows the following: SDRCOM 2? status sdr * Warning 1810 * SQL DDL Capture is disabled. SDR monitor $ZSDR (0,817) is monitoring 1 RDF environment(s).
Installing SDR ZRDF-EVT-Msg-736 for some changes in the way operators respond to a few specific RDF events that are reported on the backup system, mostly by ignoring them. ZRDF-EVT-Msg-736 Waiting to obtain FILEINFO on file $vol.ZASDRnnn.SDRDEPOT, error 11 This message from the RDF Updater will appear after the initial installation of SDR the the first time a SQL DDL operation is performed on a table residing on $vol.
Installing SDR ZRDF-EVT-Msg-700, ZRDF-EVT-Msg-705 In such RDF configurations, the operator can safely ignore the file system errors 10 and 71 and the open error 12 reported on the $vol.ZASDRnnn.SDRDEPOT file by the RDF Updaters.
3 Configuring SDR This section discusses the SDR configuration options and, because many functions of SDR depend on proper configuration of RDF, a few pertinent RDF considerations. The chapter is organized as follows: RDF Considerations • • • • • RDF Topologies RDF Configuration Configuring SDR Configuring SDR Capture Configuring SDR Replication RDF Considerations RDF Topologies SDR supports all RDF topologies (as described in the RDF - 1 – Features).
Configuring SDR Configuring SDR table partition. See the discussion below in Configuring the SDR Network for further details. Replicating Physical VOLUMEs in an SMF Environment The RDF VOLUME configuration determines how SDR translates the DDL for execution on the backup systems. When configuring RDF to replicate databases on SMF virtual volumes, it is usually not necessary to specify the physical volumes.
Configuring SDR Configuring SDR Capture The global settings are changed using the ALTER command and reset to their default with the RESET command. ALTER [GLOBAL] parameter-and-value To issue the ALTER and RESET commands you must be a SUPER user and have update access to the REGISTRY table in the SDRSYSDB subvolume.
Configuring SDR Configuring SDR Capture Retaining Captured DDL At the time you perform either primary database updates or DDL operations, you might not have configured RDF yet, or even have a backup database. You can decide, weeks after an application has started updating a database on the primary system, to initialize RDF and replicate all the updates that were performed in past weeks to a backup database. All you need to do is save the TMF audit trails.
Configuring SDR • Configuring SDR Replication HOLD: allow the operator to manually review each DDL operation executed under a user transaction, and decide if it should be executed or canceled. SDR and RDF updating is suspended until the operation is either executed or canceled. HOLD is the default.
Configuring SDR Configuring SDR Replication In cases where the table is created on the backup system by SUPER.SUPER, the ownership or the table can be set to the userid that owns the table on the primary system by specifying the GIVETOUSER option of CREATEID.
Configuring SDR Configuring SDR Replication Replicating Unaudited Tables SDR is designed to operate in conjunction with RDF, to perform DDL replication automatically and at exactly the right time, relative to RDF audited data replication. Thus, it is designed primarily for audited tables. SDR can be configured to replicate DDL for unaudited SQL operations, by setting the global parameter UNAUDITEDDDL.
Configuring SDR Configuring SDR Replication HP NonStop SQL DDL Replicator User’s Guide—545799-005 3-8
4 SDR Operations This section describes how SDR captures, replicates and executes DDL operations and various special processing cases. The topics covered are the following: SDR Monitor DDL Capture DDL Replication SDR and RDF Takeover Distributed Table Replication SDR Monitor The SDR monitor process has a reserved process name, $ZSDR. It is a NonStop process pair to ensure that a CPU or network failure does not interrupt SDR processing. The SDR monitor has three basic functions: 1.
SDR Operations DDL Capture Rule 1 - DDL Capture The “capture” or extraction of SQL DDL on any system is, and must be, completely independent of any RDF configuration. RDF can be configured to replicate database audit (and now SQL DDL) that was generated in the past, so SDR does not know whether or not you will configure and start RDF on a system at some future time. The SQL DDL must be already captured, along with the other TMF audit, to replicate everything.
SDR Operations • DDL Capture Artifacts Marker Files SDR Depot Files SDR depot files are automatically created by SDR as needed. A depot file is an audited Enscribe key-sequenced file. A depot file is created on each primary database volume that has a SQL object (table, index, so on) that is the target of a captured DDL statement. The name of the subvolume where a depot file is stored is a combination of the reserved name ZASDR and the Expand node number. The depot filename is always SDRDEPOT.
SDR Operations DDL Capture Operation $DATA.ZASDR004.AA000123. Marker files are managed automatically by SDR. They occupy no disk space. SDR purges marker files when they are no longer needed. WARNING. Purging Marker files manually can cause RDF to abort with no possibility of restart. You would need to reinitialize RDF to an audit location after the SRU record that references the marker file, and likely lose database audit that keeps the backup database synchronized.
SDR Operations Special cases not know that they apply to a catalog and cannot discard them. More specifically, RDF events 736 appear in the EMS log, when the RDF updater detects the SQL updates to the catalog table VERSIONS: Waiting to obtain FILEINFO on file $vol.catsubvol.VERSIONS, error 11 SDR detects this situation and updates the depot file with a CREATE CATALOG before the audited catalog inserts. The CREATE CATALOG will be replicated before the RDF updater would stall at the catalog inserts.
SDR Operations Special cases Without SDR, the operator has to create a collation manually to allow RDF to continue processing. With SDR, the operator no longer needs to intervene. SDR detects the absence of the audited file and inserts a CREATE COLLATION into the depot file, before the collation compiler has a chance to generate its audited updates. Thus, CREATE COLLATION is replicated before RDF encounters the compiler generated audited updates. Caution.
SDR Operations DDL Replication Unaudited Tables and Indexes SDR writes every DDL operation to a depot file, but it writes the SRU audit record only if the target SQL object is audited. Since the SRU audit initiates SDR replication, DDL for unaudited objects is not replicated. See Replicating Unaudited Tables on page 3-7 for options available to process DDL replication on unaudited tables.
SDR Operations Replication Operations mode, it is assumed that the primary system is unavailable; the SDR monitor on the backup system manages the SDR updater. EMS Messages In contrast to RDF, each SDR component, including the SDR updater, logs all EMS events to the system that it was started from. When RDF is in normal mode, all SDR EMS events are sent to the primary system EMS collector. When RDF is in takeover mode, all SDR events are sent to the backup system EMS collector.
SDR Operations Replication Operations backup, so there is no increased exposure to lost transactions on the backup in case of a takeover. In RDFCOM, a STATUS RDF command will show an “Update NSA Stopped” state. Prior to SDR, this is the point where system operators would manually execute the DDL operation and restart the RDF updaters. If SDR is installed, it is critical to notify the operators that they should not be issuing an RDFCOM START UPDATE command when RDF is in this state.
SDR Operations SDR and RDF Takeover the target name, but does not replicate some secondary reference, SDR signals an error and waits for operator intervention. Note. Many RDF configurations have excluded SQL catalog subvolumes. If your DDL references a catalog on an excluded subvolume of a replicated volume, SDR will still attempt to replicate the operation. It will use the same subvolume on the RDF backup volume.
SDR Operations Distributed Table Replication An RDF takeover can occur only if the primary system has failed (or is unavailable on the Expand network) or if RDF has been stopped on the primary system. Either of these events will cause the SDR updaters started from the primary system to shut down. When you issue the RDF TAKEOVER command, the SDR monitor on the backup system immediately starts an SDR updater for that RDF configuration.
SDR Operations Distributed Table Replication HP NonStop SQL DDL Replicator User’s Guide—545799-005 4-12
5 SDR Monitoring and Control This section describes the commands available to query the status of SDR and control its operations: Checking Status • • • EMS Log The SDR Log STATUS Commands Controlling Replication • • Monitor-Level Commands Updater-level Command Checking Status EMS Log SDR sends numerous event messages to the EMS collector. The default collector is $0, but you can specify a different collector by setting the EMSCOLLECTOR global parameter to an alternate collector.
SDR Monitoring and Control STATUS Commands SDRLOG is created when the first DDL operation is replicated successfully. All replicated DDL operations are logged, even if they do not succeed. The SDR updater detects if the log fills up and deletes the oldest records, making room for new records. The SDR update also detects retries resulting in the same error and does not write a new log entry that would be a copy of the previous one. The format of the SDRLOG entries is proprietary.
SDR Monitoring and Control STATUS Commands STATUS SDR STATUS SDR shows a combined summary of RDF and SDR information. It provides a simple way to look for abnormal conditions. In the example below, STATUS SDR shows that the SDR updater has been stopped: SDRCOM 25? STATUS SDR SDR monitor $ZSDR (5,627) is monitoring 1 RDF environment(s).
SDR Monitoring and Control Controlling Replication STATUS UPDATE The STATUS UPDATE command shows the state of SDR updaters that are controlled from a selected system. The DETAIL option adds the DEFINES and the text of the last replicated DDL statement to the display. In the example below, the last statement was a CREATE TABLE LIKE. SDRCOM 35? STATUS UPDATE ,DETAIL SDR monitor $ZSDR (5,627) is monitoring 1 RDF environment(s).
SDR Monitoring and Control Monitor-Level Commands Monitor-Level Commands The SDR monitor starts and stops the SDR updaters on the backup system. The following commands are related to monitor functions: START UPDATE STOP UPDATE RESTART UPDATE START UPDATE START UPDATE starts all SDR updaters or the updater for a specific RDF configuration. SDRCOM 39? START UPDATE SDR updater ATOMJ will be started.
SDR Monitoring and Control Updater-level Command In the following example, STATUS UPDATE shows the SDR updater is in a HOLD state, RESTART UPDATE is used to stop it and restart it and the subsequent STATUS UPDATE shows the updater executing: SDRCOM 60? STATUS UPDATE SDR monitor $ZSDR (5,627) is monitoring 1 RDF environment(s). Current SDRUPDT processes: 1 Total SDRUPDT processes: 8 RDF Control Subvol ATOMJ SDR Updater Process \SIERRA.
SDR Monitoring and Control Updater-level Command RETRY RDF-control You must be a SUPER user to execute SDR updater commands. HOLD RDF-control HOLD places the specified updater in the hold state. When in the hold state, an updater will fetch and translate a pending DDL operation, but will not execute it.
SDR Monitoring and Control Updater-level Command In the following example, the SDR updater is on hold and there is a pending DDL statement as shown by STATUS SDR. The subsequent RELEASE causes the updater to execute the pending statement: SDRCOM 65? STATUS SDR SDR monitor $ZSDR (5,627) is monitoring 1 RDF environment(s).
SDR Monitoring and Control Updater-level Command In the example below, the updater is in a hold state and holding a DROP TABLE statement as shown by STATUS UPDATE. The subsequent EXECUTE causes the updater to execute the DROP statement SDRCOM 6? STATUS UPDATE SDR monitor $ZSDR (5,627) is monitoring 1 RDF environment(s). Current SDRUPDT processes: 1 Total SDRUPDT processes: 9 RDF Control Subvol ATOMJ SDR Updater Process \SIERRA.
SDR Monitoring and Control Updater-level Command SDRCOM 10? STATUS UPDATE SDR monitor $ZSDR (5,627) is monitoring 1 RDF environment(s). Current SDRUPDT processes: 1 Total SDRUPDT processes: 9 RDF Control Subvol ATOMJ SDR Updater Process \SIERRA.$X8MZ (4,379) RDF Primary Sys \ATOM RDF Backup Sys \SIERRA State Updater executing *** This updater is in HOLD state.
SDR Monitoring and Control Updater-level Command The operator purges the table on the backup system and issues a RETRY. The second STATUS UPDATE shows that the table was created successfully: SDRCOM 8?STATUS UPDATER SDR monitor $ZSDR (3,978) is monitoring 1 RDF environment(s). Current SDRUPDT processes: 1 Total SDRUPDT processes: 7 RDF Control Subvol ATOMJ SDR Updater Process \SIERRA.
SDR Monitoring and Control HP NonStop SQL DDL Replicator User’s Guide—545799-005 5-12 Updater-level Command
6 SDR Commands The command interpreter for SDR, SDRCOM, is used to install, configure, control and monitor SDR. This section describes the command interpreter and is organized as follows: Running the Command Interpreter Command Syntax Command Description Overview SDR Configuration and Management Commands Utility Commands Monitor Commands Running the Command Interpreter Even if you can run SDRCOM as any user, most configuration and management commands must be performed by a member of the SUPER group.
SDR Commands Command Syntax command also restores all parameters displayed by the ENV command to their default values. Command Syntax SDRCOM is a conversational-mode program. Most commands are single-line commands and are terminated by the end of line; such commands can be continued by placing an ampersand (&) at the end of the line.
SDR Commands Command Description Overview Table 6-1.
SDR Commands Command Description Overview Table 6-2. SDR Utility Commands (continued) Command Name Description RUN[D] Runs an external process during a SDRCOM session SEMICOLON Toggles the use of a semicolon (;) as a command delimiter STATUS MONITOR Displays information about the monitor process TIME Displays the current time and date VOLUME Sets the SDRCOM session volume and subvolume Table 6-3.
SDR Commands SDR Configuration and Management Commands SDR Configuration and Management Commands ALTER Sets or changes global configuration values for SDR. Global parameters are the default configuration settings for SDR.
SDR Commands ALTER The default is SUPER[ID], which causes all DDL operations, except CREATE, to be performed under SUPER,SUPER. AUTOCREATECATALOG { ON | OFF } specifies that SDR should automatically create catalogs on the backup system, whenever RDF indicates that a catalog is required. For automatic catalog creation, RDF must be configured to replicate data for the files in the catalog subvolume. Automatic catalog creation is independent of any other SDR facility.
SDR Commands ALTER environmental problem is preventing SDR from capturing the DDL operation. Note. As a usage example, you are advised to set DDLCAPTURE to REQUIRED when you install an update to the SDR software, at least until the installation is completed on all the systems in the RDF network. See “Updating SDR Software” on page 2-8 for further details.
SDR Commands ALTER used to support parallel RDF configurations (most commonly production and test) that may share RDF protected volumes, to avoid disrupting the specified RDF configuration (most commonly production). RDF-control-subvol is an RDF configuration in the format node[suffixcharacter]. DEFAULT applies to all RDF configurations that do not have an explicit RDFCONFIG setting.
SDR Commands CANCEL specifies how SDR will process DDL statements executed under a user transaction. ABORT causes SDR to disallow the execution of a DDL statement within a user transaction. NonStop SQL already disallows the execution of many DDL statement within a user transaction; this further extends the prohibition to include all DDL statements.
SDR Commands CREATE SYSDB RDF-control-subvol the RDF configuration in the format node[suffix-character] that replicates the SQL object that is the target of the DDL operation. CREATE SYSDB Creates the SDR Configuration Database. SysDB consist of the SQL REGISTRY that resides in a subvolume named SDRSYSDB. REGISTRY is updated with registration information and global parameters. SysDB must be created on an audited volume.
SDR Commands EXECUTE EXECUTE Directs the specified SDR updater to execute the current DDL statement. EXECUTE is typically used when the SDR updater is in a hold state or when the operation requires user intervention.t. EXECUTE RDF-control-subvol RDF-control-subvol an RDF configuration in the format node[suffix-character] that replicates the SQL object that is the target of the DDL operation.
SDR Commands • INSTALL SDR Duplicates and renames SQL utilities so that new instances of SQL processes will use the new SDRRUNTM INSTALL RUNTIME is executed by the INSTALL macro when a new version of SDR is installed on the system. INSTALL RUNTIME new-runtime new-runtime the new SDR runtime that replaces the current SDRRUNTM. Note that • • Unlike INSTALL SDR, the INSTALL RUNTIME command does not need the SDR monitor to be executing nor does it require the product to be licensed.
SDR Commands READLOG The SDR log file SDRLOG is an Enscribe key-sequenced file, residing on the backup system in the SysDB subvolume (SDRSYSDB). Its record format is proprietary and the file can only be displayed with READLOG. READLOG backup-system [, options ] options: { { { { { { { COUNT n DETAIL LAST n PRIMARY primary system RDF[CONFIG] RDF-control-subvol STOP stop-time TIME start-time } } } } } } } backup-system the name of the RDF backup system where the DDL operation was executed.
SDR Commands RELEASE STOP stop-time time of the latest entry to display from the SDR log file. stop-time is [[ date ] time ] or [ [ time ] date ] where: time is hh:mm[:ss] and date is dd mmm yyyy or mmm dd yyyy (example: 11:15:00 30 May 2008) TIME start-time time of the earliest entry to display from the SDR log file.
SDR Commands RETRY the RDF configuration in the format node[suffix-character] that replicates the SQL objects that are the target of the DDL operations managed by the SDR updater. If RDF-control-subvol is omitted, all SDR updaters are stopped and restarted. HOLD instructs the monitor to restart the SDR updater(s) in the HOLD state. RETRY Instructs the SDR updater that manages the specified RDF configuration to retry executing a DDL operation that was previously attempted and did not succeed.
SDR Commands START UPDATE cpu (in the range of 0 to 15) of the backup monitor processes. Used when the monitor process is started. EMSLEVEL { CRITICAL | DETAIL | NORMAL | TRACE } sets the granularity of EMS event reporting: CRITICAL displays only messages that are critical or that require operator intervention. DETAIL displays all EMS messages generated by the SDR monitor and SDR updaters. NORMAL displays essential informational messages, action messages and critical messages. NORMAL is the default.
SDR Commands STATUS * an RDF configuration in the format node[suffix-character] that replicate the SQL objects that are the target of DDL operations managed by the SDR updater. If RDF-control-subvol is omitted, all SDR updaters are started. HOLD instructs the monitor to start the updater(s) in the HOLD state. STATUS * Combined form for STATUS SDR, STATUS RDF, and STATUS UPDATER.
SDR Commands STATUS UPDATE STATUS UPDATE Displays information about SDR updaters that manage the specified RDF configuration and for the specified system. STATUS UPDATE [ \node ] [ RDF-control-subvol ] [, DETAIL ] node node name of the system for which the SDR updater information is requested. The default is the local node. RDF-control-subvol an RDF configuration in the format node[suffix-character] that replicates the SQL objects that are the target of DDL operations managed by the SDR updater.
SDR Commands Utility Commands You must UNSINSTALL SDR before removing the SDR product files from your system. UNINSTALL SDR Utility Commands ABEND MONITOR Causes the monitor to stop and produce a saveabend file. Use this command only when you are required to supply information about a monitor problem and are instructed to do so by product support. ABEND MONITOR [ * | process-name ] * (asterisk) stops all configured monitor processes.
SDR Commands ALTER FILE ALTER FILE Utility command similar to the FUP ALTER command. Changes attributes of Enscribe files. Unlike the FUP ALTER command, the SDRCOM ALTER FILE command can alter a collection of files specified as a file set.
SDR Commands ALTER FILE specifies whether buffered writes are performed. If NO BUFFERED is specified, writes are not buffered. The default is buffered mode for audited files and not buffered for non audited files. CLEARONPURGE | NO CLEARONPURGE erases disk free space when files are purged. CODE file-code numeric file code of the file. file-code is an integer between 0 and 65535. Codes 100 to 999 are reserved for use by HP. LOCKLENGTH key-length the byte count of the record key for generic locks.
SDR Commands COPY COPY Similar to the FUP COPY command with enhancements. Copies records from an input file to an output file.
SDR Commands COPY out-options is: { { { { { { { { { { { { { { { { { { { BLOCKOUT n EBCDICOUT FOLD PAD pad-character RECOUT n REWINDOUT | NO REWINDOUT UNLOADOUT | NO UNLOADOUT UNSTROUT UPDATE VAROUT } } } } } } } } } } } } } } } } } } } display-options { { { { { { { { { { { [O]CTAL [D]ECIMAL [H]EX [A]SCII BYTE NO HEAD } } } } } } } } } } } in-file file containing data to be copied. in-file can be a process, tape, terminal, or disk file.
SDR Commands COPY COUNT num-records the number of records or rows to be copied. If omitted, all records are copied. FIRST ordinal-record-num the starting record of the input file to copy. If omitted, the copy starts at the first record or row in the input file. ordinal-record-num the number of records or rows from the beginning of the file that are to be skipped. The first record in a file is record zero.
SDR Commands COPY number of bytes between 1 and 32767 in an input block that is requested in a single physical read operation. When BLOCKIN is not specified, the RECIN value is used. The default is device dependent: 80 bytes for terminal, 132 bytes for process and unstructured files. COMPACT | NO COMPACT zero length records should (or should not) be skipped when copied to the output file. The default is COMPACT.
SDR Commands COPY This option applies to key sequenced files only. TRIM trim-character delete any trailing character matching the trim-character. The character is specified in ASCII using quotation marks or as an integer in the range 0 to 255. UNLOADIN | NO UNLOADIN (magnetic tapes only) input tape is unloaded (or not unloaded) after the tape has been rewound. The default is UNLOADIN. This also applies to labeled tapes. UNSTRUCTURED open and access the input file using the unstructured option.
SDR Commands COPY output tape is rewound (or not rewound) after the copy operation has completed. If NO REWINDOUT is specified, the tape remains positioned without rewinding. The default is REWINDOUT. This option also applies to labeled tapes. UNLOADOUT | NO UNLOADOUT (magnetic tapes only) output tape is unloaded (or not unloaded) after rewinding. The default is UNLOADOUT. This option also applies to labeled tapes. UNSTROUT opens and writes the output file using the unstructured access option.
SDR Commands COMMENT NO HEAD omit the heading preceding each record when one of the display-options is specified. COMMENT Causes the command interpreter to ignore the remainder of the current line. COMMENT is not a multiline command or terminated with a semicolon and may not appear within the lines of a multiline command. COMMENT any-text In addition to the explicit COMMENT command, a pair of dashes (– –) causes the interpreter to ignore all remaining text on the current line.
SDR Commands • • FILEINFO The default is the immediately previous command. integer (positive) specifies the ordinal number of a command in the history buffer (See HISTORY). • -integer (negative) specifies a relative command in the history buffer, with the most recent command having the value –1. • text selects the most recent command that starts with the specified text. FILEINFO Displays information about Enscribe files.
SDR Commands HELP HELP Lists the help options for SDR commands, MONITOR commands and the SDR defines. HELP { ALL { { SDR-COMMAND { { command [ DETAIL | EXAMPLE ] { { SDR-DEFINES { { define [ USAGE ] { { GLOBALS { { MONITOR [ monitor-command ] } } } } } } } } } } } } } ALL displays the list of all commands. SDR-COMMANDS requests the list of all SDR commands available. command [ DETAIL | EXAMPLES ] a SDR command. Help displays the syntax and description of the command.
SDR Commands HISTORY requests the display of the list of DEFINEs for which HELP is available. define-name [ USAGE ] is the name of the DEFINE. HELP displays the syntax and a description of the DEFINEs. If USAGE is specified, Help displays information about the usage of the DEFINE, otherwise Help displays USAGE as a possible subtopic. GLOBALS requests a display of the list of all the possible global parameters that can be set for SDR using the ALTER command.
SDR Commands MONITOR closes the current log file and stops logging. MONITOR The MONITOR command sends a command to the monitor process. Refer to Monitor Commands on page 6-34 for a list of valid monitor commands MONITOR command-text command-text is all text up to the end of the current command line. A MONITOR command cannot be continued on multiple lines. OBEY Reads and runs a sequence of commands from another device. The commands are run serially until end-of-file is detected.
SDR Commands RUN[D] RUN[D] Runs a program during a SDRCOM session. When the program terminates, the session resumes. RUN[D] filename[/run-options/] [command] filename specifies a program file to be run. run-options specifies standard TACL process options, including the following: CPU EXTSWAP LIB NOWAIT PRI DEBUG IN MEM OUT SWAP DEFMODE INSPECT NAME PFS TERM command specifies any command line to be passed to the process in the startup message.
SDR Commands STATUS MONITOR STATUS MONITOR Determines the status of the monitor process by sending the monitor inquiries. STATUS MONITOR TIME Displays the current local time of day as obtained from the local Guardian timekeeping function. You may also request the local time as seen by another system on your Expand network. TIME [ systemname ] systemname identifies an Expand system for which the local system time is desired. VOLUME Changes the default volume and/or subvolume for filename expansion.
SDR Commands LOG displays all EMS messages generated by the SDR monitor and SDR updaters. NORMAL displays essential informational messages, action messages and critical messages. NORMAL is the default. TRACE displays low level activity for problem analysis and should be used only when advised by HP support. LOG Initiates or terminates a monitor activity log. MONITOR LOG { TO file [CLEAR] | STOP } TO file [CLEAR] specifies the name of a log file and starts logging.
SDR Commands STATUS STATUS Displays information about various objects. MONITOR STATUS [ LICENSE ] LICENSE requests information about the SDR license for the current system. BACKUPCPU The BACKUPCPU command specifies the cpu for the monitor backup process. MONITOR BACKUPCPU [ cpu ] cpu specifies the cpu number where the monitor backup process should be started. If omitted, the backup process is terminated. SWITCH The SWITCH command causes the monitor primary and backup processes to exchange roles.
A SQL DDL Statements This appendix describes the SQL DDL statements that are replicated and, in some cases, specific information about how SDR translates them. References to catalogs, physical volumes, and WITH SHARED ACCESS are also discussed. DDL Statements Catalog References in Statements PHYSVOL References in Statements WITH SHARED ACCESS Clauses DDL Statements Table A-1 lists the DDL statements processed by SDR with notes on special processing, where applicable. Table A-1.
SQL DDL Statements DDL Statements DDL Operation Considerations and Processing Notes CREATE INDEX If the CREATE INDEX statement contains the option PARALLEL EXECUTION ON CONFIG config-file, note that: • • • CREATE TABLE SDR does not move the config-file to the backup system so it must be moved to the backup by other means SDR translates the primary config-file name to the backup config-file name The content of the config-file must specify volume and file names on the backup system CREATE TABLE LIKE r
SQL DDL Statements SQL object in Statement DDL Operation Considerations and Processing Notes PURGE PURGE is an SQLCI utility that is converted to a DROP statement for each SQL object that is referenced by the file set. The individual DROP statements are replicated separately by SDR. References to programs and Enscribe files are not processed by SDR SECURE SECURE is an SQLCI utility that is converted by SQLCI to an ALTER statement for each SQL object that is referenced by the file set.
SQL DDL Statements WITH SHARED ACCESS Clauses and physical volume assignments on the backup system must mirror the primary system. Otherwise, the translated SQL statement will fail. If you do not require the replication of physical volume assignments on the backup system, set the SDR global option KEEPPHYSVOL to OFF. WITH SHARED ACCESS Clauses Some DDL statements support, or even require, a WITH SHARED ACCESS clause.
B SDR EMS Messages In each message description, the following information appears: • • • • • Message number Message text Cause—the condition or error that produced the message Effect—the effect of the condition or error on the system Recovery—the steps required to recover from a reported error Informational Messages 100 Monitor initializing Cause. The SDR monitor process is initializing itself. Effect. The monitor process is not ready to service requests until initialization is completed. Recovery.
SDR EMS Messages Informational Messages 103. Monitor switched to backup Cause. The SDR monitor primary process or its CPU has failed. The monitor backup process has taken over. Effect. The monitor is ready to service requests. Recovery. Informational message. No action is required. 104 Monitor backup created Cause. The SDR monitor primary process has created a backup process. Effect. The monitor process is now fault-tolerant. Recovery. Informational message. No action is required.
SDR EMS Messages Informational Messages Effect. Status information is placed in the EMS log. Recovery. Informational message only; no corrective action is needed. 151 SDR updater process starting Cause. The monitor process has started an SDRUPDT process to perform replication. The message contains the remote node and the process name. Effect. SDR replication processing for the specified node is active. Recovery. Informational message only; no corrective action is needed.
SDR EMS Messages Informational Messages 157 A utility process used by SDR has died Cause. A utility process started by SDR has unexpectedly terminated. Effect. Replication of SQL DDL is interrupted. Recovery. SDR should restart the process. If the problem persists, contact product support. 158 SDR Updater draining Cause. The SDR monitor is draining an SDR updater process. This normally occurs when an RDF configuration is shut down. The updater may be processing a final SQL DDL operation. Effect.
SDR EMS Messages Informational Messages 177 SDR updater stopped Cause. An SDR updater has stopped, usually due to a shut down of an RDF configuration. Effect. The SDR updater will no longer replicate SQL DDL for the RDF configuration. Recovery. Informational message only; no corrective action is needed. 178 SDR started a process Cause. SDR has started a utility process. Effect. The utility process will perform services required by SDR. Recovery. Informational message only; no corrective action is needed.
SDR EMS Messages Informational Messages 181 RDF updater restarted Cause. After SDR has replicated SQL DDL, it tells the RDF monitor to restart the RDF updaters. Effect. RDF updating is restarted. Recovery. Informational message only; no corrective action is needed. 182 DDL statement executed Cause. SDR executed a SQL DDL statement on the backup system. Effect. SQL DDL on a primary system is replicated on a backup system. Recovery. Informational message only; no corrective action is needed.
SDR EMS Messages Informational Messages Effect. The SDR updater will await further instructions to cancel or execute the SQL DDL. Recovery. Use the SDRCOM to issue either the CANCEL or EXECUTE command. 186 SDR Depot file created Cause. An SDR depot file has been created. Effect. SDR will use the depot file to capture SQL DDL statements. Recovery. Informational message only; no corrective action is needed. 187 SQL catalog created Cause.
SDR EMS Messages Critical Messages Critical Messages 402 SDR monitor intentionally abended Cause. The monitor process has been intentionally abended to produce a saveabend file for problem diagnosis. Effect. The primary monitor process abends. The backup process takes over and continues processing. Recovery. Send the saveabend file to product support. 403 Invalid SDR license Cause. An operation was attempted, but no license for that service has been installed. Effect. The operation is rejected. Recovery.
SDR EMS Messages Critical Messages 411 Internal error detected (assertion failure) Cause. A logic error has been detected by the process. Effect. The process abends. Recovery. Report the error information to product support. 412 Segment resize error Cause. An attempt to resize the extended segment failed. Effect. The process abends. Recovery. The error number is provided in the message. Determine the reason for the failure and correct it. 413 Memory pool allocation error Cause.
SDR EMS Messages Critical Messages Effect. The process abends. Recovery. Either correct the TMF environmental problem or report the error information to product support. 419 Utility program process trap Cause. A utility program encountered a program trap. Effect. The process abends. Recovery. Report the error information to product support. 420 Native utility program process trap Cause. A native mode utility program encountered a program trap. Cause. The process abends. Recovery.
SDR EMS Messages Critical Messages 451 SDR monitor on the backup system is not running Cause. An SDRUPDT process on a remote system was not able to communicate with the SDR monitor process on the remote system. Effect. The SDRUPDT process stops. No replication is performed. Recovery. Start the monitor on the remote system. 452 Unable to send NSA UPDATE message Cause. SDR could not insert a NSA STOP UPDATE message in the TMF audit trail. The error code is displayed in the message. Effect.
SDR EMS Messages Critical Messages 455 Unable to access a file label Cause. SDR was unable to obtain the label of a SQL table. The error code is displayed in the message. Effect. SDR will not be able to replicate a SQL DDL statement. SDR may have caused a SQL DDL statement to fail. Recovery. Correct the file label access problem. 456 SDR updater or SDR runtime unable to authenticate a user Cause. The SDR runtime or updater was unable to authenticate a user ID.
SDR EMS Messages Critical Messages 459 IOEdit error Cause. SDR encountered an error when using the IOEDIT routines to communicate with EMSDIST. Effect. SDR cannot monitor system events and will fail to replicate SQL DDL. Recovery. This is an internal error. Contact product support. 460 SDR monitor down Cause. The SDR monitor is down. Effect. SDR cannot perform critical functions when the monitor is down. Recovery. Use SDRCOM to start the SDR monitor.
SDR EMS Messages Critical Messages Effect. SDR updating is temporarily interrupted. Recovery. No corrective action is needed. SDR will restart the updater when the backup node is available. 464 SDR monitor unable to authenticate user Cause. The SDR monitor was unable to authenticate a user ID. SDR must perform certain operations using a different user id and invokes USER_AUTHENTICATE_. Effect. A required operation fails. Recovery. Correct the authentication problem. SDR should retry the operation.
SDR EMS Messages Critical Messages 468 Invalid NSA STOP UPDATE record Cause. The file name in a NSA STOP UPDATE record is invalid. Effect. The SQL DDL will not be replicated. Recovery. Internal error. Contact product support. 469 Unable to access SDR depot file Cause. The SDR depot file could not be accessed for the indicated file system error. Effect. The SQL DDL will not be replicated. Recovery. If the indicated error cannot be corrected, contact product support.
SDR EMS Messages Critical Messages Recovery. Correct the cause of the failure and the process will be restarted. 473 Error communicating with a utility program Cause. SDR was unable to communicate with the indicated utility program. Effect. The effect depends on the utility program and the operation being attempted. Recovery. If the error cannot be corrected, contact product support. 474 Unable to stop SDR updater Cause. The SDR monitor is unable to STOP an SDR updater process. Effect.
SDR EMS Messages Critical Messages Effect. The SQL DDL statement fails. Recovery. Either change the application or change the SDR configuration to deal with user transactions in another way. 478 SDR holding on user transaction Cause. SQL DDL was executed under a user transaction; SDR is holding the replication of the SQL DDL. Effect. SDR and RDF updating are suspended awaiting user input. Recovery. Review the SQL DDL and either EXECUTE or CANCEL it. 479 SDR holding on DROP TABLE Cause.
SDR EMS Messages Critical Messages Recovery. If the operation is not successful after many retries, investigate the status of the indicated SDR monitor. 482 SDR waiting for RDF SRU Cause. A distributed SQL DDL statement is being replicated and SDR is waiting for a remote RDF configuration to process the NSA STOP UPDATE record and stop the updaters. Effect. SDR and RDF updating are suspended awaiting remote RDF configurations to stop updating. Recovery. No corrective action is needed.
SDR EMS Messages Critical Messages Recovery. Either correct the RDF NETWORK configuration or CANCEL the SQL DDL statement. Note that you may also configure a NETWORK inside SDR. 486 Target of SQL DDL operation missing Cause. A DROP operation referenced SQL object that did not exist. Effect. The DROP operation is ignored and SDR proceeds. Recovery. No corrective action is needed. 487 SDR updater abend Cause. An SDRUPDT process has abended. Effect. The SDR monitor will restart the process.
SDR EMS Messages Critical Messages 490 SDR required but impaired by environmental condition Cause. SDR DDL capture is configured as REQUIRED, but some error is preventing SDR replication. Effect. The SQL DDL statement fails. Recovery. Correct the cause of the error or change the SDR configuration to make DDL capture enabled but not required. 491 SDR updater in hold state Cause. A SQL DDL statement is ready to be replicated, but the SDR updater is in the hold state. Effect.
C Testing SDR Overview Testing of SDR is dependent on the resources available. If you have a separate testing environment with RDF configured between two development and or testing nodes, then there is less concern that SDR testing might impact production activities. But, many installations have only two systems: the production database and backup database nodes, with the backup used for testing and development.
Testing SDR Testing 5. To prevent accidental updates, turn off replication by setting the RDFCONFIG global to NOREPLICATE for all RDF configurations on the primary and backup systems. In SDRCOM ALTER RDFCONFIG DEFAULT NOREPLICATE Testing To execute testing scenarios: 1.
Testing SDR Testing automated operations on the primary To prevent the capture of DDL operations, you can set the =_SDR_CAPTURE_DISABLE define as follows: ADD DEFINE =_SDR_CAPTURE_DISABLE, CLASS MAP, FILE DUMMY As long as that define is present in the TACL session, SDR will not capture the SQL DDL in the audit file. The define can be used until the scripts are fully debugged. Once they are debugged, you can DELETE the define to test out the SDR replication of the DDL operations.
Testing SDR Testing automated operations on the primary HP NonStop SQL DDL Replicator User’s Guide—545799-005 C-4
Index Symbols Collector 5-1 SDRFLTR filter 5-1 ZSDRTMPL template 5-1 $ZSDR SDR monitor 4-1 I A ACCESSID SDR configuration 3-6 AUTOCREATECATALOG SDR configuration 3-6 C CREATE CATALOG Special case 4-5 CREATE COLLATION Special case 4-5 CREATEID SDR configuration 3-5 CREATESECURITY SDR configuration 3-6 D DDL Capture disabling 2-10 Special cases 4-4 DDL Replication enabling 2-6 DDL statement translation DDL replications 4-9 Distributed 4-6 Distributed DDL operation DDL capture 4-6 Replication 4-11 E EMS
REPLICATEPURGE 3-1 SMF virtual volumes 3-2 RDF Takeover 4-10 Removing SDR 2-10 REPLICATEPURGE Difference between RDF and SDR 3-1 RDF configuration 3-1 RETENTION SDR configuration 3-4 S SDR components Data Files 1-3 Program Files 1-2 SDR depot files creation 4-8 DDL capture 4-3 RDF SPRs and automatic replication 32 SDR monitor $ZSDR 2-6, 4-1 Functions 4-1 SDR updater DDL replication 4-7 EMS logging 4-8 RDF control subvolume 4-7 RDF LOGFILE 4-7 SDRLOG SDR logging 5-1 SMF RDF Configuration 3-2 SRU audit recor
