*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
| [Home] | [Table of Contents] | [Previous Section] | [Next Section] |
Breck Carter
Last modified: February 23, 1998
mail to: bcarter@bcarter.com
Section Two initializes the database on a remote computer via DBXTRACT, DBINITW and ISQLW, and starts the subscriptions on both the remote and consolidated databases.
Tip: Finish the database and publication design before running DBXTRACT.
Make sure you're ready to start using your application before running DBXTRACT. That's because it's a lot easier to make changes to the database design before replication begins than afterwards.
Step 5 - Run DBXTRACT on the consolidated database (required).
The DBXTRACT utility makes it easy to initialize remote databases by creating files tailored for each one.
The following commands run DBXTRACT via the XTRACT.BAT file shown in Figure 5a:
c:\test\xtract.bat 0001
c:\test\xtract.bat 0002
rem ...etc., for other remote sites
Figure 5a - XTRACT.BAT - Run DBXTRACT On The Consolidated Database
(This file is contained in rep.zip.)
if %1z == z goto error1 if %2z == z goto ok1 goto error :ok1 pause To run DBXTRACT for SITE%1 SET SQLREMOTE=c:\test SET cparm="DBN=consol;UID=DBA;PWD=SQL" SET oparm=c:\test\site%1\dbxtract.txt SET rparm=c:\test\site%1\reload.sql DBXTRACT -c %cparm% -l 3 -o %oparm% -r %rparm% -u -v -y c:\test\site%1 site%1 SET cparm= SET oparm= SET rparm= goto end :error1 pause Specify the site number; e.g.: xtract 0001 goto end :end pause |
The following DBXTRACT parameters are used in XTRACT.BAT:
The batch file parameter %1 specifies the site number; e.g., 0001 for site0001. Three local environment variables %cparm%, %oparm% and %rparm% are used just to make the batch file easier to read.
XTRACT.BAT places the DBXTRACT files in the C:\TEST\SITEnnnn subdirectories on the central server. These files aren't "replication messages" in the strictest sense but they're being treated in a similar manner by this demonstration.
Figure 5b shows the log of all the messages displayed by the first run of DBXTRACT. This log is written to the DBXTRACT.TXT file in the C:\TEST\SITE0001 subdirectory.
Figure 5b - DBXTRACT.TXT - Message Log For SITE0001 On The Central Server
Unloading user "SYS" Unloading user "DBA" Unloading user "PUBLIC" Unloading user "dbo" Unloading user "consol" Unloading user "site0001" Unloading "DBA"."replication1" Unloading "DBA"."replication1" into c:\test\site0001\161.dat SELECT "unique_id1", "subscription_id1", "numeric_data1", "string_data1" |
Figure 5c shows the C:\TEST\SITE0001\RELOAD.SQL file created by DBXTRACT. It contains all the commands necessary to create the tables, user ids, publications and subscriptions for the first remote database.
Tip: RELOAD.SQL is well worth studying in detail, at least the first time you create it.
The whole file is reproduced here because it's so important to understand what it contains and how it works.
Figure 5c - RELOAD.SQL - The DBXTRACT Command File To Load The Remote Database
% Usage:isql read c:\test\site0001\reload.sql % % This command file reloads a database that was unloaded using "dbxtract". % % (version: 5.5.03 Build #1666) % SET OPTION Statistics = 3 go SET OPTION Date_order = 'YMD' go %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Create userids and grant user permissions %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% GRANT CONNECT TO "DBA" IDENTIFIED BY "SQL" go GRANT RESOURCE, DBA, SCHEDULE TO "DBA" go GRANT CONNECT TO "dbo" go GRANT GROUP TO "dbo" go GRANT RESOURCE, DBA TO "dbo" go GRANT CONNECT TO "consol" IDENTIFIED BY ENCRYPTED '\x8C\x1F\x1A\x72\x6C\x02\xA7 |
The LOAD TABLE commands in Figure 5c refer to three data files called 161.DAT, 162.DAT and 163.DAT. These text files are also created by DBXTRACT and placed in the C:\TEST\SITE0001 subdirectory.
Figure 5d shows what these 3 files look like.
Figure 5d - DBXTRACT Data Files Used By RELOAD.SQL
161.DAT - File for the replication1 table '1','Main','0','initial test' 162.DAT - File for the replication2 table '1','consol','Sub1','0','initial test' 163.DAT - File for the replication3 table '1','consol','Sub1','0','initial test' |
The FROM 'c:\test\site0001\xxx.dat' clauses in the LOAD TABLE commands are one of the reasons that the same directory structure is used on both the central server and remote computers. That's because in this demonstration the RELOAD.SQL file is created on the central server and then executed on the remote computer. The path specified in a LOAD TABLE command is always relative to the engine that's running the command, so c:\test\site0001 must exist on the remote computer as well as the central server.
In Figure 5c the CREATE REMOTE TYPE statement does two things: it specifies "FILE" as the messaging system to be used, and it specifies "site0001" as the return address for messages. Here's how you can interpret the ADDRESS 'site0001' clause: Use C:\TEST\SITE0001 as the subdirectory on the remote computer to hold messages that are being sent to the remote database from the consolidated database.
If that sounds familiar, it should. And so should the next statement:
The GRANT CONSOLIDATE TO consol command also has two purposes: to (again!) specify "FILE" as the messaging system, and to define C:\TEST\CONSOL as the subdirectory on the remote computer to hold messages that are being sent to the consolidated database from the remote database.
It should really sound familiar now, just like the description of CREUSERS.SQL in Step 2, except that everything's backward. Or reversed, if "backward" sounds too much like a value judgement.
The magic of DBXTRACT is that it creates RELOAD.SQL to do so much of the grunt work for you, to create the mirror-image user ids and privileges that are required to match up each remote database with its consolidated master. It does half your work if you only have one remote database, much more than half if you have a thousand remotes.
Figure 5e shows these mirror-image user ids and subdirectory names rearranged to appear side-by-side.
Figure 5e - Mirror-Image User Ids And Addresses In CREUSERS.SQL And RELOAD.SQL
How CREUSERS.SQL Does It For How RELOAD.SQL Does It For
The Consolidated Database The Remote Database
(See Figure 2b) (See Figure 5c)
---------------------------- --------------------------
Return Address: CREATE REMOTE MESSAGE CREATE REMOTE
TYPE FILE TYPE FILE
ADDRESS 'consol'; ADDRESS 'site0001'
This Database: GRANT CONNECT TO consol GRANT CONNECT TO site0001
GRANT REMOTE DBA TO consol GRANT REMOTE DBA TO site0001
GRANT PUBLISH TO consol GRANT PUBLISH TO site0001
Other Database: GRANT CONNECT TO site0001 GRANT CONNECT TO consol
GRANT REMOTE DBA TO site0001 GRANT REMOTE DBA TO consol
GRANT REMOTE TO site0001 GRANT CONSOLIDATE TO consol
TYPE FILE TYPE FILE
ADDRESS 'site0001' ADDRESS 'consol'
|
Figure 5e also shows how CREUSERS.SQL and RELOAD.SQL answer these three questions:
For another view of the naming conventions have a look at Figure 1b. It's important to note that the decision to use the same values for user ids and subdirectory names is a conscious one. So is the decision to use DBXTRACT instead of coding your own RELOAD.SQL or using some other technique.
The advantage to all of this is simplicity: DBXTRACT takes care of us. There is a disadvantage, however. Each remote computer needs a different subdirectory name SITEnnnn even though they all share the same database file name REMOTE.DB. Yes, different subdirectory names like SITEnnnn are always needed on the central computer, but they're not absolutely necessary on the remote computers themselves. And if a common name was used then some batch files and file handling logic would be easier to write. In other words, some administrative tasks would be easier.
A bit easier. But not in this demonstration, because we're taking what DBXTRACT gives us.
Tip: Don't run DBXTRACT until you're ready to start replicating.
You should probably be ready to set up and use the remote database when you run DBXTRACT. That's because DBXTRACT doesn't just put START SUBSCRIPTION commands in the RELOAD.SQL file for later execution on the remote database. It also runs START SUBSCRIPTION commands on the consolidated database right away, for the data to be sent to this particular remote database.
Here's a summary of what DBXTRACT does for you:
Step 6 - Make file replication subdirectories on each remote computer (required).
Each remote computer needs two subdirectories for replication files, CONSOL and SITEnnnn (see Figure 1b for an explanation of the naming convention).
The following DOS command creates these subdirectories on SITE0001 by running the batch file shown in Figure 6a:
c:\test\makerdir 0001
Figure 6a - MAKERDIR.BAT - Make Replication Subdirectories On A Remote Computer
(This file is contained in rep.zip.)
if %1z == z goto error1 if %2z == z goto ok1 goto error1 :ok1 pause To create replication subdirectories on SITE%1 mkdir c:\test\consol mkdir c:\test\site%1 pause goto end :error1 pause Specify the site number; e.g.: makerdir 0001 goto end :end |
Step 7 - Set the SQLREMOTE environment variable as required (suggested).
The following command is included in the AUTOEXEC.BAT file for each remote computer:
SET SQLREMOTE=C:\TEST
On the central server this command is included in each batch file so it doesn't need to be defined as a permanent environment variable.
Step 8 - Send the DBXTRACT files to the remote computers (required).
There are two different approaches to initializing the remote databases:
The following commands on the central server copy the 4 required DBXTRACT files to the first remote computer SITE0001. They assume the remote C: drive is shared with the central server using F: as the drive letter:
rem These commands only show one remote computer site0001 copy c:\test\site0001\*.dat f:\test\site0001 copy c:\test\site0001\reload.sql f:\test\site0001 |
Step 9 - Run DBINITW and ISQLW to initialize the remote databases (required).
The following commands run on the remote computer SITE0001 to create the REMOTE.DB and REMOTE.LOG files and then load the database via RELOAD.SQL. The program names end in "W" because different executables are required for Windows for Workgroups 3.11:
rem These commands only show one remote computer site0001
DBINITW -p 4096 c:\test\remote.db
ISQLW -c "DBF=c:\test\remote.db;UID=DBA;PWD=SQL" READ c:\test\site0001\reload.sql
Another difference between the two Windows platforms is that DOS batch files can be used on Windows NT to launch Windows-based programs. On Windows for Workgroups 3.11 this generates the error message "this is a Windows 16-bit executable" and you're limited to launching the programs directly via Program Manager icons or the File - Run menu item.
The DBINITW command uses the following parameters:
Step 10 - Make a backup: MAKECBAK 10 and MAKERBAK 10 (optional).
Step 11 - Show the replication status after the remote database is set up (optional).
At this point you can run SELDATA.SQL and SYSREMO.SQL via ISQL and ISQLW, but it's easier to run REPSTAT.EXE. Figure 11a shows that the rows marked 'initial test' have indeed been loaded on the remote database, and that the consolidated and remote databases are in sync.
Figure 11a - Replication Status After The Remote Database Is Set Up
The sysremoteusers data shows that each database knows about the other one but that no replication traffic has been sent yet (the time_sent columns are empty).
Step 12 - Delete the old DBXTRACT files from the remote computer (suggested).
These commands are run on the remote computer to delete the old DBXTRACT files that have already been processed:
rem These commands only show one remote computer site0001
erase c:\test\site0001\*.dat
erase c:\test\site0001\reload.sql
If you don't delete these files the first run of DBREMOTW will display warning messages that say "Deleting corrupt message".
Step 13 - Delete the old DBXTRACT files from the central server (suggested).
These commands are run on the central server to delete the old DBXTRACT files that have already been processed:
erase c:\test\site0001\*.dat
erase c:\test\site0001\reload.sql
erase c:\test\site0001\dbxtract.txt
erase c:\test\site0002\*.dat
erase c:\test\site0002\reload.sql
erase c:\test\site0002\dbxtract.txt
rem ...etc., for other remote sites
This is the end of Section Two. The remote database has been set up, the subscriptions have been started and the system is ready for replication to begin.
| [Home] | [Table of Contents] | [Previous Section] | [Next Section] |