NetBase SQL Shadowing Handbook

ManualsBrandsHP ManualsSoftwareMPE/iX 6.5 Operating System

NetBase SQL

Shadowing Handbook

610 Newport Center Drive, Suite 1400, Newport Beach, CA 92660

Summary of content (78 pages)

PAGE 1
NetBase SQL Shadowing Handbook 610 Newport Center Drive, Suite 1400, Newport Beach, CA 92660
PAGE 2
This manual contains proprietary information which is protected by copyright. The information in this manual is subject to change without notice and does not represent a commitment on the part of Quest Software. The software described in this manual is furnished under a license or nondisclosure agreement. This software may be used or copied only in accordance with the terms of this agreement.
PAGE 3
NetBase SQL Shadowing Table of Contents Chapter 1 - Introduction Who should use this handbook? .....................................................................................1-1 NetBase SQL Shadowing ...............................................................................................1-1 Chapter 2 - Installation Installation Summary......................................................................................................2-1 1. Restore NetBase from the Installation Tape.........
PAGE 4
PAGE 5
Chapter 1 Introduction Who should use this handbook? The purpose of this handbook is to address the issues of customers who are interested in SQL shadowing without using any of the other NetBase features. For customers who are adding SQL shadowing to their other NetBase services, such as NFA, shadowing, spooling, or statistics, the NetBase reference manual is the appropriate text. NetBase SQL shadowing uses a small fraction of the NetBase product.
PAGE 6
Chapter 2 Installation Installation Summary Typically, installation of the software and configuration of each system can be accomplished in less than 20 minutes per system. NOTE: This installation process assumes that the part of WSL which is a set of routines provided by HP has been installed into an XL and that the databases to be shadowed via SQL exist. In general, the following steps comprise the installation process: 1. 2. 3. 4. 5. 6. 7. 8. Note: Restore the NetBase software from tape.
PAGE 7
Installation NetBase SQL NetBase SQL Installation 1. Restore NetBase from the Installation Tape To create NetBase's environment and install the software, you will restore a job into PUB.SYS. This job is then streamed to create and restore the NetBase account. Log on to MANAGER.SYS, and restore the job stream file: : HELLO MANAGER.SYS,PUB : RESTORE ;NETACCT.JOB.
PAGE 8
NetBase SQL Installation 3. Configure NetBase Operational Parameters Minimally, NetBase must be instructed to perform SQL shadowing. This is accomplished using the CONFIG subsystem in the NBCTRL program. All configuration must be performed from the NETBASE account. : HELLO MGR.NETBASE : RUN NBCTRL NetBase Control Program [v.u.f] Update n (C) QUEST Software 1987 *> CONFIG C> MODIFY SQL SQL - Enable SQL Shadowing.........
PAGE 9
Installation NetBase SQL 4. Configure Network Nodes For NetBase to communicate to the other systems on the network, all systems must be defined to each other. This is accomplished using the CONFIG subsystem in the NBCTRL program. All configuration must be performed from the NETBASE account. Now, each computer in the network, including the local computer being configured, must be entered into the configuration.
PAGE 10
NetBase SQL Installation C> ADD 2 System Name........................ BUNNY Startup Access (S/I/O/A/W)......... [A] Line Type (N/H).................... [N] Node Name.......................... BUNNY.QUEST.SOFTWARE Node Connection Timeout............ [180] This procedure must be repeated for each system in the network to be accessed by NetBase. The LIST command can be used to display the configured nodes.
PAGE 11
Installation NetBase SQL 5. Make Directory Entries Instruct NetBase as to which DBEs are to be shadowed. This is accomplished using the NBDIR program. The SQLOUT command is used to define which DBEs are exporting updates to which nodes. For each DBE, a separate SQLIN record must be entered for each node importing updates. These entries take effect with the next SQL shadowing session (started with either START NETBASE or START SQL within NBCTRL).
PAGE 12
NetBase SQL Installation SQLIN Defines DBEs for inbound SQL Shadowing SYNTAX: D> SQLIN dbename[=remname],node [;PRI=transaction-priority] [;EXECPRI={BS|CS|DS|ES}] [;USEREXIT] [;RESET] The node, in this case, is the node from which data is coming. The PRI option is used to set the priority on the BEGIN WORK for the transaction being applied. This is important in the case of deadlocks.
PAGE 13
Installation NetBase SQL EXAMPLES: The simplest form of SQL shadowing is to shadow a DBE to another machine. Let's say we are shadowing DBE PRODDBE from node SYSA to SYSB. On SYSA we run NBDIR, and enter the following record: : RUN NBDIR.PUB.NETBASE NetBase Directory Program [v.u.f] Update n (C) QUEST Software 1987 D> SQLOUT PRODDBE.DATA.PROD,SYSB;HOME=1 On SYSB we enter into NBDIR: D> SQLIN PRODDBE.DATA.PROD,SYSA SINGLE DIRECTION SQL SHADOWING SYSA SYSB D> SQLOUT DBE.DATA.
PAGE 14
NetBase SQL Installation Renaming DBEs It should be noted that the link between the SQLOUT and SQLIN matching pair is formed by the name of the DBE on the shadow (or import) side combined with the export node and the import node. If the name of the DBE on the import side is not the same as the name of the DBE on the export side, the SQLOUT record needs to specify the remote name.
PAGE 15
Installation NetBase SQL Distributed SQL Shadowing Another way a network may be set up is to shadow a DBE from one node to a number of other nodes. If we are shadowing PRODDBE from MASTER to SHAD1, SHAD2, and SHAD3, then on MASTER, we enter: D> SQLOUT PRODDBE.DATA.PROD,SHAD1;HOME=1 D> SQLOUT PRODDBE.DATA.PROD,SHAD2;HOME=1 D> SQLOUT PRODDBE.DATA.PROD,SHAD3;HOME=1 And on each of the other systems, we enter: D> SQLIN PRODDBE.DATA.PROD,MASTER MASTER D> SQLOUT PRODDBE.DATA.
PAGE 16
NetBase SQL Installation Consolidated SQL Shadowing Or, you may want to export updates from DBEs on a number of nodes to a master (consolidated) copy on one machine. Notice that in this case our terminology involving the word "shadow" is reversed. In this set up, the DBEs on each of the export machines is unique, and the master copy is a combination of them all. For example, let's export updates to DBE from SYSA, SYSB, and SYSC to SYSD. On SYSA we enter: D> SQLOUT DBE.DATA.
PAGE 17
Installation NetBase SQL Multi-Directional SQL Shadowing The most complex use of SQL shadowing is when you want to export updates from all machines in a network to all other machines in the network. To reduce the chances of synchronization problems, you should consider some form of partitioning. The idea behind partitioning is that master copies of different sections of a DBE are kept on different systems. This may be accomplished using user exits or by using TABLE entries in NBDIR.
PAGE 18
NetBase SQL Installation SYSA D> SQLOUT DBE.DATA.PROD,SYSB;HOME=1;USEREXIT D> SQLOUT DBE.DATA.PROD,SYSC;HOME=1;USEREXIT D> SQLIN DBE.DATA.PROD,SYSB;USEREXIT D> SQLIN DBE.DATA.PROD,SYSC;USEREXIT SYSB D> SQLOUT DBE.DATA.PROD,SYSA;HOME=2;USEREXIT D> SQLOUT DBE.DATA.PROD,SYSC;HOME=2;USEREXIT D> SQLIN DBE.DATA.PROD,SYSA;USEREXIT D> SQLIN DBE.DATA.PROD,SYSC;USEREXIT SYSC D> SQLOUT DBE.DATA.PROD,SYSA;HOME=3;USEREXIT D> SQLOUT DBE.DATA.PROD,SYSB;HOME=3;USEREXIT D> SQLIN DBE.DATA.PROD,SYSA;USEREXIT D> SQLIN DBE.
PAGE 19
Installation NetBase SQL Pseudo-Node Shadowing If we want to have a pseudo-node process running on each of the machines, on each of the machines we would also enter the following record: D> SQLOUT PRODDBE.DATA.PROD,@;PART=1,2,3 The user exit interprets the @ as node zero (0), and may perform whatever is defined for that node. A common application is to log all update transactions (without logging any of the rest). SYSA D> SQLOUT DBE.DATA.PROD,SYSB;HOME=1;USEREXIT D> SQLOUT DBE.DATA.
PAGE 20
NetBase SQL Installation Excluding Tables The previous examples have assumed complete shadowing of DBEs. Thanks to the INCLUDE and EXCLUDE options on the SQLOUT commands, you may limit the shadowing of tables or set up vertical partitioning. If you specify INCLUDE on an SQLOUT command, the corresponding TABLE records for that DBE reflect the only tables to be included in shadowing for that DBE.
PAGE 21
Installation NetBase SQL EXAMPLES: For our example, let's assume DBE includes five tables, and we are shadowing it from SYSA to SYSB. To exclude table5 (shadowing the rest), you could enter: On SYSA: D> SQLOUT DBE.DATA.PROD,SYSB;HOME=1;EXCLUDE D> TABLE DBE.DATA.PROD,SYSB,TABLE5 On SYSB: D> SQLIN DBE.DATA.PROD,SYSA SYSA DBE TABLE 1, TABLE 2, TABLE 3, TABLE 4 D> SQLOUT DBE.DATA.PROD,SYSB;HOME=1;EXCLUDE D> TABLE DBE.DATA.PROD,SYSB,TABLE5 TABLE 5 SYSB D> SQLIN DBE.DATA.
PAGE 22
NetBase SQL Installation If DBE2 includes 7 tables, and you want to shadow only TABLE1 and TABLE 5, the INCLUDE option is your best choice: On SYSA: D> SQLOUT DBE2.DATA.PROD,SYSB;HOME=1;INCLUDE D> TABLE DBE2.DATA.PROD,SYSB,TABLE1 D> TABLE DBE2.DATA.PROD,SYSB,TABLE5 On SYSB: :NBDIR.PUB.NETBASE D> SQLIN DBE2.DATA.PROD,SYSA SYSA DBE2 TABLE 2, TABLE 3, TABLE 4, TABLE 6, TABLE 7 D> SQLOUT DBE2.DATA.PROD,SYSB;HOME=1;INCLUDE D> TABLE DBE2.DATA.PROD,SYSB,TABLE1 D> TABLE DBE2.DATA.
PAGE 23
Installation NetBase SQL 6. Enable Warm Standby Logging Prior to enabling WSL, each DBE should be checked to verify that it has a valid WSL ID and Home Partition ID number, and the Maximum Number of Partitions is set. When an SQL database is created (via the START DBE NEW command in ISQL), by default these elements are initialized to zero. (Actually, WSL ID is initialized to blanks.) Each of these items must have bona fide values for standby logging to work. 1.
PAGE 24
NetBase SQL Installation Below are some tips on setting these values: WSL ID This name must be unique across the network. Once this is set, it should never be changed, unless a master DBE is being restored on a shadow machine. (Maximum 8 characters.) Home partition ID This must be a number between 1 and 32767 (inclusive), and each copy of the DBE must have a unique home partition number. Once this is set, it should never be changed.
PAGE 25
Installation NetBase SQL For example: Explanation: : ISQL isql=> START DBE 'ourdbe.data' NEWLOG > HOMEPARTITION = 2, > MAXPARTITIONS = 3, > WSLID = 'SQLACE', > STANDBY LOG > LOG DBEFILE sqltemp > WITH PAGES = 5000, > NAME = 'sqlalog.log'; For the DBE named "ourdbe.data" (in the login account) use home partition ID "2", maximum number of partitions of "3", and WSL ID "SQLACE". Enable standby logging.
PAGE 26
NetBase SQL Installation 7. Add Additional Required Log Files Once WSL is enabled, you have created a single log file. WSL requires a minimum of two log files. To add additional log files, you should run SQLUtil. 1. Run SQLUtil.
PAGE 27
Installation NetBase SQL To calculate the size of archive log files, use the following formula: [(Max size of a transaction) x (Max Transactions per period)] + 38 pages = Archive Logsize You may build several log files to handle your data storage requirements (between backups), rather than two large ones. (Remember: Replicate requires a minimum of two log files.) For more information on sizing log files, please consult your AllBase/SQL Database Administration Guide from Hewlett Packard.
PAGE 28
NetBase SQL Installation 8. Start SQL Shadowing Once WSL is enabled, you may start and stop it within the NetBase control utility, NBCTRL. If NetBase is running, to start SQL shadowing import and export processes for all configured nodes: : NBCTRL.PUB.NETBASE *> START SQL *> EXIT If NetBase is not running, you may start it which, in turn, will start SQL shadowing. : NBCTRL.PUB.NETBASE *> START NETBASE *> EXIT To stop SQL shadowing: : NBCTRL.PUB.
PAGE 29
Installation NetBase SQL To review SQL shadowing: *> SHOW SQL Exporting Status: # Node DBE Name State PIN 7 CLIENT1 BRN1DBE.PUB.QDBEBRN1 ACTIVE 73 8 DBSERVER CCDBE.ROBIN.NETBASE ACTIVE 39 8 DBSERVER XXDBE.ROBIN.NETBASE INACT 47 Importing Status: # Node DBE Name State PIN 7 CLIENT1 BRN1DBE.PUB.QDBEBRN1 ACTIVE 119 7 CLIENT1 FOO.PUB.NETBASE ACTIVE 133 7 CLIENT1 HRMSDB.HRMSDTA.REP01 INACT 8 DBSERVER CCDBE.SHADOW.NETBASE DOWN 8 DBSERVER SHDBE.SHADOW.
PAGE 30
NetBase SQL Installation Sample Startup and Backup Jobs Sample jobs for starting SQL and backing it up may be found in the JOB group of the NetBase account. Please note that the jobs reflect a simple shadowing scenario from master to shadow, building archive log files on the master, and non-archive (small) log files on the shadow. The naming convention is as follows: SQLINITM.JOB.NETBASE SQLINITS.JOB.NETBASE SQLBACKM.JOB.NETBASE SQLBACKS.JOB.
PAGE 31
Chapter 3 User Exits Overview Four different types of user exits may be used with SQL shadowing; two are considered export user exits, and two are post user exits. If the USEREXIT option is specified on the SQLOUT command in NBDIR, the export process, NBSQLEXP.NB, will call the userexit (export_sql_exit) before sending the transaction to the import process on the other machine. : NBDIR D> SQLOUT dbe,node;PART=part1;...
PAGE 32
User Exits NetBase SQL The names of the user exits must be as stated above, and they should reside in one of the following libraries: XL.NB.NETBASE XL.PUB.NETBASE XL.PUB.SYS (not recommended) The default library can be overridden by setting variables in NETBASE.JOB.NETBASE. You may override the location for export_sql_exit by setting EXPORTSQLPROC to the name of the library where the exit procedure resides.
PAGE 33
NetBase SQL User Exits procedure must have the logic necessary to process all tables simultaneously. Typically, this requires simple switching logic to other procedures. WSL Flags Three flags in the WSL routines may be set by one of the NetBase processes or by a user exit. Normally, WSL performs several tests to validate an update before the update is applied on the shadow copy of the DBE. One check is to verify that the actual transaction length matches the transaction length in the transaction.
PAGE 34
User Exits NetBase SQL Notes on User Exit Structure All of the user exits (both export user exits and post user exits) have the same set of parameters. The sqlca parameter is the same as the sqlca as defined in the SQL manual for the language of your choice. The comarea parameter has flags that are returned by the user exit. These flags control the processing of that record. The action field of the comarea should always be set by the user exit, since it is not initialized by the calling program.
PAGE 35
NetBase SQL User Exits Export Exit Procedure Calling Conventions REC REC REC REC REC export_sql_exit(sqlca,comarea,header,data,upd_data) sqlca HP SQL communication area for the DBE. This field can be used by the user exit to perform dynamic SQL commands on the DBE. See the SQL manual for the language in which the user exit is written for the definition of this field. comarea An array of various information used to control the exporting of shadow information.
PAGE 36
User Exits header NetBase SQL status Set by user exit procedure to send a message to the console. If a positive value is set, this message in the catalog file NBEXPSQL.CAT, set 5, will be sent to the console. error1 Set by user exit procedure. If status is non-zero, these values will be error2 inserted in the message sent to the console. export_node Set by calling procedure to indicate the node number from which transactions are being exported. This is the local node.
PAGE 37
NetBase SQL User Exits The individual fields within header are defined as follows: DBEname Name of the DBE (fully-qualified). owner_name Owner of the table. table_name Name of the table. user_name User name of the entity that committed the transaction. Only valid if record_type = 1. commit_time Time the record was committed. Only valid if record_type = 1. This field is in the same format as the SQL DATETIME data type. record_type Type of record.
PAGE 38
User Exits Note: NetBase SQL dat A buffer containing all columns of the record concatinated together. The description of the columns is found in the "column_array". For UPDATE records, the original data is stored here. upd_data A buffer containing all updated columns. Unlike the "data" buffer, this field contains only the updated columns. Note that if a column was not updated, it will not appear in this buffer. This is why the column number is specified in the "update_array" field.
PAGE 39
NetBase SQL User Exits Post Exit Procedure Calling Conventions REC REC REC REC REC post_sql_exit_before(sqlca,comarea,header,data,upd_data); REC REC REC REC REC post_sql_exit_after (sqlca,comarea,header,data,upd_data); Parameters are defined as follows: sqlca HP SQL communication area for the DBE. This field can be used by the user exit procedure to perform dynamic SQL commands on the DBE.
PAGE 40
User Exits NetBase SQL The individual fields are defined as follows: action Set by user exit procedure to cause various actions by the exporting process. The valid values are: 0 Process record normally. Post the record as normal (before only) or continue with next record (after). 1 Ignore record completely. The record will be thrown away. This can easily cause synchronization errors. Used by "before" only. 2 Disable all subsequent calls to the user exit facility. 3 Roll back transaction.
PAGE 41
NetBase SQL User Exits first_time This flag is set by the calling process. It is TRUE only the first time that the user exit is called. key_col_search This flag is set by the user exit. It tells the import process to set the KEY_COL_SRCH_OK flag when the transaction is applied. user_area This is a comarea provided to the USEREXIT for its own use. header Contains all non-data information about the original update.
PAGE 42
User Exits NetBase SQL num_columns Number of columns in "data" buffer. upd_columns Number of columns in "upd_data" buffer. This will be 0 unless "record_type" is 26. column_array Describes all columns in the "data" buffer. The first entry describes the first column, the second describes column #2, etc.
PAGE 43
NetBase SQL User Exits Sample User Exit The following sample user exit performs three functions: 1. It disables shadowing for the table named "LOCAL_TABLE". 2. Sets the WSL flag "KEY_COL_SEARCH_OK" on each update applied. 3. Prints a message to the console when the "CUST_NUM" column in the "CUSTOMERS" table is updated. Your user exit could write a record to the DBE. If it does, that table should be excluded from shadowing.
PAGE 44
User Exits NetBase SQL typedef struct { } char DBEname [26]; char owner_name [20]; char table_name [20]; char user_name [20]; char commit_time [24]; short record_type; short num_columns; short upd_columns; col_rec column_array [256]; upd_rec update_array [256]; header_rec; #pragma list on void export_sql_exit (sqlca_type sqlca, comarea_rec *comarea, header_rec *header, char *data, char *upd_data) { int rec,upd = 0; int upd_offset = 0; int rec_offset = 0; char msg[130]; comar
PAGE 45
NetBase SQL User Exits } /* DISABLE SHADOWING FOR THE TABLE "LOCAL_TABLE" if (header->record_type != 15 && */ /* not BEGIN */ header->record_type != 1 && /* not COMMIT */ strncmp (header->table_name, "LOCAL_TABLE ", 12) == 0) comarea->action = 4; /* disable table */ /* TABLE "GLOBAL_TABLE" IS SHADOWED BOTH WAYS AND IS PARTITIONED */ /* VERTICALLY.
PAGE 46
User Exits NetBase SQL EXEC SQL BEGIN DECLARE SECTION; char SQLMsg[130]; EXEC SQL END DECLARE SECTION; comarea->action = 0; /* IF IT'S NOT AN ERROR, RETURN */ if (sqlca.
PAGE 47
NetBase SQL User Exits Sample User Exit Below is a sample PASCAL user exit which performs the same functions as the previous example. INPUT-OUTPUT SECTION. DATA DIVISION. WORKING-STORAGE SECTION. 01 REC PIC S9(09) COMP VALUE 1. 01 UPD PIC S9(09) COMP VALUE 1. 01 UPD-OFFSET PIC S9(09) COMP VALUE 1. 01 REC-OFFSET PIC S9(09) COMP VALUE 1. 01 MESSAGE-BUFFER PIC X(130). 01 HOLD-AREA PIC X(6). EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 SQLMESSAGE PIC X(132). EXEC SQL END DECLARE SECTION END-EXEC.
PAGE 48
User Exits NetBase SQL ******************************************************************* * * * PROCEDURE DIVISION * * * ******************************************************************* PROCEDURE DIVISION. 000-MAIN-LOGIC SECTION 01. 000-EXIT. EXIT. $PAGE A000-EXPORT-SQL-EXIT SECTION 01. ENTRY "export_sql_exit" USING SQLCA-ARRAY, COMAREA, HEADER, DATA-ARRAY, UPDATE-ARRAY. MOVE ZERO TO ACTION IN COMAREA. IF SHADNODE IN COMAREA = 0 THEN * Display when customers dataset is modified.
PAGE 49
NetBase SQL User Exits A00A-EXIT. EXIT. $PAGE A100-COUNT-UPDATES SECTION 01. COMPUTE UPD-OFFSET = UPD-OFFSET + UPD-COL-LENGTH IN HDR-UPDATE-ARRAY(UPD). ADD 1 TO UPD. A100-EXIT. EXIT. $PAGE A200-INCREMENT-REC-COUNT SECTION 01. COMPUTE REC-OFFSET = REC-OFFSET + COLUMN-LENGTH IN HDR-COLUMN-ARRAY(REC). ADD 1 TO REC. A200-EXIT. EXIT. $PAGE A300-DISPLAY-DATA SECTION 01. DISPLAY "USEREXIT CUSTDBE : CUST_NUM.CUSTOMERS modified at ", COMMIT-TIME IN HEADER. MOVE DATA-ARRAY(REC-OFFSET:6) TO HOLD-AREA.
PAGE 50
User Exits NetBase SQL IF DATA-ARRAY(REC-OFFSET:2) = "NW" THEN DISPLAY "USEREXIT CUSTDBE:LOCAL CUST_NUM MODIFIED ", ORIGNODE MOVE 1 IN COMAREA TO ACTION IN COMAREA ELSE NEXT SENTENCE ELSE NEXT SENTENCE ELSE NEXT SENTENCE. GOBACK. B00A-EXIT. EXIT. $PAGE C000-POST-SQL-EXIT-AFTER SECTION 01. ENTRY "post_sql_exit_after" USING SQLCA, COMAREA, HEADER, DATA-ARRAY, UPDATE-ARRAY. MOVE ZERO TO ACTION IN COMAREA. * Return if not an error IF SQLCODE IN SQLCA = 0 THEN GOBACK.
PAGE 51
NetBase SQL User Exits DISPLAY "DELETE" ELSE IF RECORD-TYPE IN HEADER = 26 THEN DISPLAY "UPDATE". C010-EXIT. EXIT. ____________________________________________________________ ver A0195 QUEST Software, Inc.
PAGE 52
User Exits NetBase SQL A sample user exit for batch update shadowing which alerts the shadow system that "all" transactions have been processed by creating a "STOP" MPE file. In addition, the user exit explains with which transaction it struggled to update the shadow copy (if any). #pragma list off #define _MPEXL_SOURCE #pragma intrinsic FOPEN, FCHECK, FCLOSE, PRINT #include #include #include #include
PAGE 53
NetBase SQL } User Exits short upd_col_type; short upd_col_length; upd_rec; typedef struct { } char DBEname [26]; char owner_name [20]; char table_name [20]; char user_name [20]; char commit_time [24]; short record_type; short num_columns; short upd_columns; col_rec column_array [256]; upd_rec update_array [256]; header_rec; #pragma list on EXEC SQL BEGIN DECLARE SECTION; char SQLMsg[130]; /* Used by SQLExplain */ char tableName[20]; /* Used by various SQL commands */ EX
PAGE 54
User Exits NetBase SQL case DATE: memcpy (&dt, data, sizeof(dt)); sprintf (result, "%4.4d-%2.2d-%2.2d", dt.year, dt.month, dt.day); break; case TIME: memcpy (&tm, data+3, sizeof(tm)); sprintf (result, "%2.2d:%2.2d:%2.2d", tm.hour, tm.minute, tm.second); break; case DATETIME: memcpy (&dt, data, sizeof(dt)); memcpy (&tm, data+3, sizeof(tm)); memcpy (&mi, data+5, sizeof(mi)); sprintf (result, "%4.4d-%2.2d-%2.2d %2.2d:%2.2d:%2.2d.%3.3d", dt.year, dt.month, dt.day, tm.hour, tm.minute, tm.second, mi.
PAGE 55
NetBase SQL User Exits DataStart = DataEnd - precision + 1; SignPos = DataEnd + 1; memset (result, 0, 80); if ((DecimalPlace = precision - scale) == 0) outStr[PutPos++] = '.'; for (i = DataStart; i <= DataEnd; i++) { outStr[PutPos] = ASCIIZero + btod(data,i); if (PutPos == DecimalPlace-1) outStr[++PutPos] = '.'; PutPos++; } for (;*outStr && *outStr == '0'; outStr++); if (*outStr == 0) *outStr = '0'; else { if (*outStr == '.
PAGE 56
User Exits NetBase SQL unsigned filler :2; } mi; memcpy (msg, cname, 20); memcpy (msg+20, " switch (ctype) ", 2); { case 0: if (clen == 2) { memcpy (&sdata, data, 2); sprintf (msg+22, "%d", sdata); } else if (clen == 4) { memcpy (&idata, data, 4); sprintf (msg+22, "%d", idata); } else sprintf (msg+22, "CANNOT DISPLAY INTEGER DATA, LEN %d", clen); PRINT (msg, -strlen(msg), 0); break; case 2: memcpy (msg+22, data, clen); PRINT (msg, -(22+clen), 0); break; case 3: if (clen > 0) memcpy (msg+22, data,
PAGE 57
NetBase SQL User Exits default: sprintf (msg+22, "CANNOT DISPLAY DATATYPE %d", ctype); PRINT (msg, -strlen(msg), 0); break; } } /* end uexit_print_data */ /* uexit_process_data */ /* */ /* Get the column information for the column number passed and */ /* print the data. */ The function returns FALSE if processing /* should stop.
PAGE 58
User Exits NetBase SQL *haveVAR = TRUE; } else if (*haveVAR && lengthUsed == 0) { strcpy (msg, "CANNOT DECODE DATA, NO COLUMN INFO AFTER VARCHAR"); PRINT (msg, -(strlen(msg)), 0); return FALSE; } else if (lengthUsed > 0 && lengthUsed != clen) { strcpy (msg, "CANNOT DECODE DATA, COLUMN LENGTHS DO NOT MATCH"); PRINT (msg, -(strlen(msg)), 0); return FALSE; } uexit_print_data (data+*offset, columnName, ctype, clen, precision, scale); *offset += clen; return TRUE; } /* end of uexit_process_data */ /* US
PAGE 59
NetBase SQL User Exits char fname[40]; short err; char char msg[80]; *logp = (char *)&log_data; comarea->action = 0; /* /* Default to no action */ PROCESS ONLY IF STATUS TABLE AND INSERT OR DELETE RECORD */ if (memcmp (header->table_name, "STATUS ", 8) != 0 || (header->record_type != INSERT && /* Check the status record */ header->record_type != UPDATE)) return; /* GET THE NEW STATUS RECORD */ memcpy (logp, data, sizeof(log_data)); if (header->record_type == UPDATE) { /* Move in the updated
PAGE 60
User Exits NetBase SQL /* CREATE THE MPE FILE STOP.PUB.NETBASE */ strcpy (fname, "STOP"); for (i = 0; i < 4 && log_source[i] != ' '; i++); memcpy (fname+4, log_source, i); fname[i+4] = CHR0; strcat (fname, ".PUB.
PAGE 61
NetBase SQL /* User Exits IF IT'S NOT AN ERROR, RETURN */ if (sqlca.
PAGE 62
User Exits NetBase SQL /* READ THE NUMBER OF COLUMNS FOR THIS TABLE */ memcpy (tableName, header->table_name, 20); EXEC SQL SELECT NUMC INTO :numColumns FROM SYSTEM.TABLE WHERE NAME = :tableName; if (sqlca.sqlcode == 100) { strcpy (msg, "NO ROWS QUALIFIED - SELECT OF SYSTEM.TABLE "); memcpy (msg+(mlen=strlen(msg)), tableName, 20); PRINT (msg, -(mlen+20), 0); return; } else if (sqlca.
PAGE 63
NetBase SQL User Exits strcpy (msg, "UPDATE INFORMATION NOT PROVIDED"); PRINT (msg, -strlen(msg), 0); PRINT (msg, 0, 0); return; } col = header->update_array[l].upd_col_num; if (col > numColumns) { sprintf (msg, "COLUMN %d NOT DEFINED", col); PRINT (msg, -strlen(msg), 0); continue; } uexit_process_data (sqlca, upd_data, &offset, col, header->update_array[l].
PAGE 64
Chapter 4 Troubleshooting This chapter contains notes and tips concerning running NetBase SQL Shadowing, as well as error messages which appear when the software has encountered a problem. If NetBase SQL is not performing as you expect, review the notes in this section to determine what may need to be changed. At the end of this section is a listing of the error messages associated with NetBase SQL. Listed with each message is a description and a solution.
PAGE 65
Troubleshooting NetBase SQL • Network Transport Tables (within NMMGR) - If many remote sessions or other Network Services are being used, some NS tables may need increasing, especially connection and socket tables. • Retransmission Interval Lower Bound (within NMMGR) - It may be necessary to increase this interval if excessive network time-outs are occurring. • The DBE was created on a much older version of ALLBASE.
PAGE 66
NetBase SQL Troubleshooting Termination Conditions Conditions that result in termination of an SQL export or import process are listed below. 1. A serious error is encountered during initialization. For instance, cannot connect to DBE, error open log scan, etc. 2. The control process says to stop as a result of the user issuing a STOP command. 3. Transmit_log finds that somebody has called an WSL routine on the log file using hard resync mode. The scan is closed. 4.
PAGE 67
Troubleshooting NetBase SQL NBSQLEXP may abort if it encounters: - Cannot find the directory entry for this DBE - DBE name is not a valid MPE file name - The export userexit returns an invalid action code - A hard resync point is encountered in the log scan NBSQLEXP, pseudo node may abort if: - USEREXIT option not specified on directory entry - Userexit not found, must be there for pseudo node - SCR file is invalid for this DBE - Error opening, creating or accessing SCR file - No partition numbers specifi
PAGE 68
NetBase SQL Troubleshooting Error Messages The error messages are listed in alphabetical order. Some error messages require assistance from Quest Technical Support if they are encountered. The following list contains error messages whose solutions you may perform. If you encounter a message that is not listed below, please contact Quest Technical Support. Make a note of the error message, any details included in the error message, and the version of NetBase you are running.
PAGE 69
Troubleshooting NetBase SQL Attempting to apply a log record while WSL is not enabled. (DBERR 2819) **Causes Abort** The architecture of Apply_Log requires WSL to be active at the slave (target). Use SQLUTIL (SHOWDBE) to check the startup parameters, and activate WSL (via START DBE NEWLOG) if necessary. Could not allocate heap space needed for procedure. (DBERR 10056) Duplicate HOMEPARTITION clause defined.
PAGE 70
NetBase SQL Troubleshooting Insufficient space in log buffer to return next log record. (DBWARN 10077) Transmit log was unable to transmit the log record into the user's log buffer because the buffer did not have enough space. This may occur only if the first record to transmit will not fit. Allocate a larger log buffer space, and call transmit_log again. Invalid buffer offset passed.
PAGE 71
Troubleshooting NetBase SQL Invalid offset for last record passed. (DBERROR 10074) Invalid partition id encountered. (DBERR 10052) Audit_Log returns this message Last_Rec_Offset contains an invalid value. Invalid specification for maximum number of log files. (DBERR 10055) The user specified an invalid number of "max_logfiles" for the backward scan during Open_Log_Scan. This parameter can be either -1 (the default) or a non-zero positive number. Invalid specification for number of SCR slots used.
PAGE 72
NetBase SQL Troubleshooting More than one slot found for the same partition (DBERR 2813) Open_Log_Scan has received an array of SCR slots where some partition has multiple COMMIT records. To correct, retrieve a set of slots from the node performing a soft resync, by issuing a Get_SCR call with the LATEST option. Then retry the Open_Log_Scan call. Multiple database environments updated in the same transaction.
PAGE 73
Troubleshooting NetBase SQL No more WSL records to transmit. (DBWARN 10040) A Transmit_Log reader has encountered the end of log. No more records can be transmitted beyond this point until some more are written. The application should pause for a short period, and then retry. Open_Log_Scan could not reserve log for partitions found. (DBERR 2816) The starting point for one or more partitions was overwritten by a direct update transaction.
PAGE 74
NetBase SQL Warm standby enabled (DBWARN 2061) Troubleshooting logging already A previous ENABLE STANDBY LOGGING command is still in effect. Check the sequence of operations. Warm standby logging not set for DBE (DBERR 2810) An ENABLE STANDBY LOGGING command was issued for a session, but standby logging is turned off for the DBEnvironment. Either a START DBE NEWLOG needs to be issued to start standby logging for the DBE, or the ENABLE STANDBY LOGGING is invalid.
PAGE 75
Troubleshooting NetBase SQL SQL ERRORS The export and the import processes may incur errors when reading records from the log file or when updating the shadow copy of the DBE. These errors are printed to the console using SQLEXPLAIN. A number of errors that may occur on the shadow machine are not recoverable. In general, these are errors that indicate that the DBEs are out of sync. If the process receives one of these errors, it prints a message and terminates.
PAGE 76
NetBase SQL Troubleshooting Resync’ing As an example, let's start with the simplest case. A shadow copy of a DBE is out of sync with the master. There is only one shadow copy and one master copy. To get back into sync the following steps should be taken: 1. Connect to the DBE on the master system. 2. Unload the DBE. This should be done while the DBE is completely quiesced. Do not proceed until all activity with the DBE has ceased. 3. Get the checkpoint record from the log file.
PAGE 77
NetBase SQL Shadowing Index C Concurrent updates.................................3-3 CONFIG...........................................2-3, 2-4 commands ADD ...........................................2-4 LIST ...........................................2-5 MODIFY .....................................2-4 fields Node Name ................................2-4 System Name.............................2-4 E Enable SQL Shadowing ..........................2-3 Enable WSL..........................................
PAGE 78
Index NetBase SQL Binary data............................... 3-7, 3-12 Character data.......................... 3-7, 3-12 column_array field.................... 3-7, 3-12 column_length field .................. 3-7, 3-12 column_num field..................... 3-7, 3-12 column_type field ..................... 3-7, 3-12 comarea .............................. 3-4, 3-5, 3-9 commit_time field..................... 3-7, 3-11 data buffer................................ 3-4, 3-12 DBEname field ......................