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-005 NonStop SQL DDL Replicator AAE (Upd 5) March 2011 545799-006 NonStop SQL DDL Replicator AAH (TCF) August 2012
Legal Notices Copyright 2012 Hewlett-Packard Development Company L.P. Legal Notice 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.
HP NonStop SQL DDL Replicator User’s Guide—545799-006 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-16.
General Syntax Notation About This Manual { } 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.
Change Bar Notation About This Manual 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.
Change Bar Notation About This Manual HP NonStop SQL DDL Replicator User’s Guide—545799-006 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-5 Create Catalog 4-5 CREATE Collation 4-5 User Transactions 4-6 Distributed Tables and Indexes 4-7 Unaudited Tables and Indexes 4-7 SQLCI DUP Utility 4-7 DDL Replication 4-7 SDR Updater Processes 4-8 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 ALTER MONITOR 6-10 CANCEL 6-10 CREATE SYSDB 6-11 EXECUTE 6-12 HOLD 6-12 INFO GLOBALS 6-12 INFO MONITOR 6-12 INFO RUNTIME 6-13 INSTALL RUNTIME 6-13 INSTALL SDR 6-13 READLOG 6-14 RELEASE 6-15 RESET 6-15 RESTART UPDATE 6-16 RETRY 6-16 START MONITOR 6-16 START UPDATE 6-18 STATUS * 6-18 STATUS RDF 6-19 STATU
ENV 6-29 EXIT 6-29 FC AND! 6-29 FILEINFO 6-30 FILES 6-30 HELP 6-31 HISTORY 6-32 LOG 6-32 MONITOR 6-33 OBEY 6-33 OUT 6-33 RESET 6-33 RUN[D] 6-33 SEMICOLON 6-34 STATUS MONITOR 6-34 TIME 6-35 VOLUME 6-35 Monitor Commands 6-35 EMSLEVEL 6-35 LOG 6-36 STATUS 6-36 BACKUPCPU 6-37 SWITCH 6-37 SQL DDL Statements A-1 DDL Statements A-1 The RESTORE Utility A-3 SQL object in Statement A-3 Catalog References in Statements A-3 PHYSVOL References in Statements A-4 WITH SHARED ACCESS Clauses A-4 SDR EMS Messages B-1 Infor
Testing C-2 Moving to production C-2 Testing automated operations on the primary C-2 HP NonStop SQL DDL Replicator User’s Guide—545799-006 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.
Product Overview Introducing SDR 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.
Data Files Introducing SDR 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 Insta This section describes the installation of SDR. It covers the following topics: Prerequisites Initial Installation Updating SDR Software Updating NonStop SQL Software or Operating System Software Disabling DDL Capture Removing SDR Software Changes to RDF Operations 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.
Move Files from the Product Media to the Installation Subvolume Installing SDR 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.
Install Product Files Installing SDR 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.
Start an EMS distributor Installing SDR 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.
Create the SDR System Database Installing SDR 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.
Install the SDR License Installing SDR 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.
Enable SQL for DDL Replication Installing SDR 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.
Install EMS templates Installing SDR 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.
Install Product Files for SDR Update Installing SDR 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.
Disabling DDL Capture Installing SDR 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).
ZRDF-EVT-Msg-736 Installing SDR 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 Capture Configuring SDR 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 Capture Configuring SDR 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 Replication Configuring SDR 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 Replication Configuring SDR 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 Replication Configuring SDR 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-006 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.
DDL Capture SDR Operations 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 already be captured, along with the other TMF audit, to replicate everything.
DDL Capture Artifacts SDR Operations 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 file name is always SDRDEPOT.
DDL Capture Operation SDR Operations $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 re-initialize 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.
Special cases SDR Operations SQL eventually commits or aborts the transaction used to perform the DDL change. If the transaction commits, SDR writes a description of the DDL to a depot file, which is replicated by RDF to the RDF backup system(s). SDR also creates the marker file to prevent the RDF Extractor from aborting. Later, when RDF is replicating the catalog audit for the DDL, it will replicate the SDR depot records and then encounter the SRU audit record and shut down the RDF updaters.
Special cases SDR Operations Because there are additional files involved, there are potential issues in replicating CREATE COLLATION relating to the use of these files. The collation compiler input file Since edit files are not replicated by RDF, you must ensure that the same character processing rules are present on both systems before executing the CREATE COLLATION on the primary system.
DDL Replication SDR Operations See paragraph Handling DDL Operations performed under a User Transactions on page 3-4 for a discussion on the options available to deal with this situation. Note. If you have a serious need to execute DDL in a user transaction, please contact HP product support.
SDR Updater Processes SDR Operations SDR Updater Processes The SDR monitor is responsible for monitoring all RDF activity. It does so by monitoring RDF events sent to collector $0. The SDR monitor also makes a periodic check of RDF and eventually makes the proper adjustments, even if the RDF events are not available. When the SDR monitor detects an active RDF subsystem, it starts an SDR updater process on the RDF backup system.
Replication Operations SDR Operations Restarting RDF Update Creating the SDR Depot Files on the Backup The SDR depot files $vol.ZASDRnnn.SDRDEPOT contain the DDL statements captured on the primary system. The creation of the depot files is not replicated to the backup by RDF. When RDF first attempts to insert updates into the depot file before it exists on the backup, it issues event 736. The SDR updater detects the event signaling that RDF is looking for the file and creates the depot file.
Replication Operations SDR Operations replicated. It simply monitors RDF and performs the associated DDL operation when RDF enters the “Update NSA Stopped” state. When RDF enters the Update NSA Stopped state, the SDR updater accesses the RDF backup copy of the depot file where the associated original DDL statement was stored. Note that the SRU file name will have a subvolume beginning with ZASDR.
SDR and RDF Takeover SDR Operations When executing replicated DDL, SDR reports SQL errors in the EMS log and waits for the operator to take action. See Controlling Replication in SDR Monitoring and Control. If the DDL statement alters or drops a table that does not exist on the backup, the operation is discarded and the RDF updaters are restarted. When executing the DDL for a CREATE operation, SDR assumes the user identity that executed the primary system DDL.
SDR Operations Distributed Table Replication name contains a primary volume on the remote system, but the ZASDRnnn.AAmmmmmm part of the file name will be the same.) The SDR depot record contains a list of all systems that received an SRU audit record, including the volume name used in each remote SRU audit record. SDR must wait until all such systems have entered that state for the correct file name.
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.
STATUS Commands SDR Monitoring and Control 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).
Controlling Replication SDR Monitoring and Control 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).
Monitor-Level Commands SDR Monitoring and Control 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.
Updater-level Command SDR Monitoring and Control 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.
Updater-level Command SDR Monitoring and Control 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).
Updater-level Command SDR Monitoring and Control 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.
Updater-level Command SDR Monitoring and Control 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.
Updater-level Command SDR Monitoring and Control 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-006 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.
Command Syntax SDR Commands 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.
Command Description Overview SDR Commands Table 6-1.
Command Description Overview SDR Commands Table 6-2.
SDR Configuration and Management Commands SDR Commands SDR Configuration and Management Commands ALTER Sets or changes global configuration values for SDR. Global parameters are the default configuration settings for SDR.
ALTER SDR Commands Setting the global ACCESSID to USER[ID] instructs the SDR updater to use the same user ID to perform the DDL operation on the backup as was used to perform the DDL operation on the primary system. 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.
ALTER SDR Commands the capture of SQL DDL operations is enabled and, if for any reason the DDL operations cannot be captured by SDR, the DDL operations are not executed on the primary system. If SDR aborts a DDL operation on the primary system, a message is logged to EMS to notify the user that DDL capture is REQUIRED, but an environmental problem is preventing SDR from capturing the DDL operation. Note.
ALTER SDR Commands used for the replication of DDL operations performed on distributed tables or indexes, to provide SDR with the description of the RDF network nodes. RDF-control-subvol is an RDF configuration in the format node[suffixcharacter].
ALTER SDR Commands RETENTION n { HOURS | DAYS | WEEKS } sets the retention period for SDR Depot records that store captured DDL operations. The minimum retention period is 24 hours. If the user sets it to a smaller value, SDR rounds it up to 1 day. The maximum retention time is 60 days. The default is 1 week. UNAUDITEDDDL { ON | OFF } specifies how SDR will process DDL statements for unaudited tables.
ALTER MONITOR SDR Commands The default is OFF. ALTER MONITOR Alters monitor attributes process priority, home terminal, primary and backup CPU. Changes to the attributes take effect the next time the monitor is restarted. ALTER MONITOR attributes attributes is { { { { { { { BACKUP cpu-num } } HOMETERM file-name } } PRIMARY cpu-num } } PRIORITY process-priority } BACKUP cpu-number a cpu number (in the range of 0 to 15) for the backup monitor process.
CREATE SYSDB SDR Commands Certain SDR configuration or processing errors make it impossible to determine the information necessary to restart the RDF updaters. For example, an incomplete RDF or SDR network specification can make it impossible to restart updaters on all participating network systems. In this case, CANCEL has no effect and you must use RDFCOM to restart the updaters.
EXECUTE SDR Commands SECURE “reap” specifies the READ, WRITE, EXECUTE and PURGE security attributes of SysDB tables. After you create SysDB, comply with the product licensing instructions. 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.
INFO RUNTIME SDR Commands INFO RUNTIME Lists the SQL/MP components currently executing that are using the SDR runtime.
READLOG SDR Commands Once SDR has been enabled, you must not remove SDR objects from your system without first reversing the installation by issuing the UNINSTALL SDR command. INSTALL SDR READLOG Displays the entries in the SDR log file SDRLOG. The display is similar to the output of STATUS UPDATE. 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.
RELEASE SDR Commands the name of the system from which the DDL operation was captured. This option is used if multiple RDF primary systems use the same RDF backup system, to select the name of a specific primary system. RDF[CONFIG] RDF-control-subvol displays only entries generated by an SDR updater that manages the specified RDF configuration. RDF-control-subvol is in the format node[suffix-character]. STOP stop-time time of the latest entry to display from the SDR log file.
RESTART UPDATE SDR Commands For a list of global parameters, see command description for ALTER on page 6-5. RESTART UPDATE Instructs the monitor to stop and then restart the SDR updater that manages the specified RDF configuration (or stop and restart all SDR updaters). RESTART UPDATE [ RDF-control-subvol ][, HOLD ] RDF-control-subvol 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.
START MONITOR SDR Commands You must be a SUPER user with update access to the REGISTRY table to start the monitor. The monitor process executes as the user who issues the START MONITOR command. START MONITOR [ process ] [, option...
START UPDATE SDR Commands the SDR monitor process. Subsequently, all messages are sent to the EMS collector. PRIMARY cpu-number a cpu-number (in the range of 0 to 15) of the primary monitor processes. PRIORITY process-priority the priority (in the range of 1 to 199) for the monitor process. UPDATE { HOLD | OFF } instructs the monitor to start (or not start) the SDR updaters. If UPDATE HOLD is specified, the updaters are started in the HOLD state.
STATUS RDF SDR Commands STATUS RDF Display the status of RDF. The command is not a substitute for its RDFCOM namesake. It displays RDF information that is pertinent to SDR operations. STATUS RDF [ RDF-control-subvol ][, DETAIL ] RDF-control-subvol an RDF configuration in the format node[suffix-character]. DETAIL adds the RDF volume information to the display. STATUS SDR Displays the state of DDL replication.
STOP MONITOR SDR Commands STOP MONITOR Performs an orderly shutdown of the monitor process.. STOP MONITOR STOP UPDATE Stops an SDR updater. STOP UPDATE [ RDF-control-subvol ] 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. If RDF-control-subvol is omitted, all SDR updaters are stopped.
ALTER FILE SDR Commands 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.
ALTER FILE SDR Commands 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.
COPY SDR Commands COPY Similar to the FUP COPY command with enhancements. Copies records from an input file to an output file.
COPY SDR Commands 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.
COPY SDR Commands 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.
COPY SDR Commands 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.
COPY SDR Commands 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.
COPY SDR Commands 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.
COMMENT SDR Commands 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.
FILEINFO SDR Commands 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.
HELP SDR Commands HELP Lists the help options for SDR commands, MONITOR commands and the SDR defines. HELP { ALL } { } { SDR-COMMAND } { } { command [ DETAIL | EXAMPLE ]} { } { 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. Multi-word commands are entered with hyphens.
HISTORY SDR Commands 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. HISTORY Lists the saved commands in the history buffer. These commands can be run using the FC or bang (!) commands.
MONITOR SDR Commands MONITOR The MONITOR command sends a command to the monitor process. Refer to Monitor Commands on page 6-35 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. An OBEY file may not contain an OBEY command.
SEMICOLON SDR Commands 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. The RUN[D] command is not a multiline command and is not terminated with a semicolon. Any semicolon is passed to the process as part of the startup command.
TIME SDR Commands 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. VOLUME can be abbreviated to V. V[OLUME] [ volume | subvolume | volume.
LOG SDR Commands 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. CLEAR empties the file before logging starts. If file already exists, information is appended to the end of the file unless CLEAR is specified.
BACKUPCPU SDR Commands 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.
SWITCH SDR Commands HP NonStop SQL DDL Replicator User’s Guide—545799-006 6-38
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. The HP RESTORE utility, references to catalogs, physical volumes, and WITH SHARED ACCESS are also discussed.
DDL Statements SQL 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
The RESTORE Utility SQL DDL Statements 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, depending on the REPLICATEPURGE setting. See REPLICATEPURGE discussion in Section 3, Configuring SDR.
PHYSVOL References in Statements SQL DDL Statements SDR will translate catalog references even if catalog subvolumes are excluded in the RDF configuration. They will be translated to the same subvolume name on the backup volume. Note that the volume must be protected by RDF. This does not apply to CREATE CATALOG and DROP CATALOG. Also, AUTOCREATECATALOG will not create a catalog when the catalog subvolume is excluded in the RDF configuration.
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.
Informational Messages SDR EMS 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.
Informational Messages SDR EMS 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.
Informational Messages SDR EMS 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.
Informational Messages SDR EMS 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.
Informational Messages SDR EMS 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.
Informational Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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.
Critical Messages SDR EMS 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 Testing SDR 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-006 C-4
Index Symbols E $ZSDR SDR monitor 4-1 EMS Collector 5-1 SDRFLTR filter 5-1 ZSDRTMPL template 5-1 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-5 DDL Replication enabling 2-6 DDL statement translation DDL replications 4-10 Distributed 4-7 Distributed DDL operation DDL capture 4-7 Replication 4-11 D
INCLUDE and EXCLUDE 3-2 LOGFILE 3-2 RDF network 3-1 REPLICATEPURGE 3-1 SMF virtual volumes 3-2 UPDATERNSASUSPEND 4-9 RDF Takeover 4-11 Removing SDR 2-10 REPLICATEPURGE Difference between RDF and SDR 3-1 RDF configuration 3-1 RESTORE Utility A-3 RETENTION SDR configuration 3-4 S SDR components Data Files 1-3 Program Files 1-2 SDR depot files creation 4-9 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-8 EMS logging 4-8 RDF control
