Differences between revisions 6 and 16 (spanning 10 versions)
⇤ ← Revision 6 as of 2008-05-16 05:59:28 →
Size: 1899
Editor: KabindraPradhan
Comment:
← Revision 16 as of 2013-09-18 06:09:33 → ⇥
Size: 3819
Editor: localhost
Comment: converted to 1.6 markup
Deletions are marked like this. Additions are marked like this.
Line 1: Line 1:
This instruction will help you understand how CONTROL and SQL database works together. Also this instruction will be helpful to troubleshoot or implementing sql database for the client. ## page was renamed from sql and control
= SQL Mirroring =
== How it works ==
 1. A record in one of the .dat files is altered. This includes changes to quantities, balances. This includes some changes that polling would not normally send/receive.
 1. The type of record and the record number is recorded in dblog.dat
 1. {{{log2odbc}}} takes unprocessed dblog.dat entries, generates SQL query for the whole record and sends it via ODBC. It still needs access to the control .dat files as dblog.dat just contains pointers to records in the original .dat file.
 1. dblog.dat gets updated so that newly processed entries are not re-processed. This is the only file that gets written to by log2odbc and it will dump significantly faster if this file was copied to the local hard drive.
Line 3: Line 9:
All control data files are store in .dat files and .idx is a header which works like index page for .dat file. == Installation ==
This instruction will help you understand how CONTROL and SQL database works together. Also this instruction will be helpful to troubleshoot or implementing SQL database for the client.
Line 5: Line 12:
Steps to configure sql database and control All control data files are stored in .dat files and .idx files are indexes for the .dat files.
Line 7: Line 14:
 * Install SQL database. This could be either on same server that control reside or different.
 * Also download and install odbc driver for your database. This will put the file in /etc/odbc.ini which you will need to configure:
  * [control] - this is a connection name that you use for database
  * Driver: - this is driver that is used
  * Databasename: dabasename that you create
  * login:
  * password:
 * Create database now using following command
  * #createdb databasename
 * Create table command
  * #createtb --help ( to see option)
  * #createtb -d databasename
 * kill all control users
 * Set flag in cooad. i.e Enable update of odbc data file =y . Now this flag will start loging in dblog.dat.
 * If the sql database is not used from the begining of control, all files will not be logged in dblog.dat to update in sql database. So simple enabling flag will not logged old files of control to dblog.dat, only the new files will be logged. To update old and as well as old , use this command.
  * #fdb2log
 * use #dblogdump command to check contents of dblog.dat
 * Now to start dumping control data to sql database. use this command
  * #log2odbc --help
  * #log2odbc -d
 * To test to see if the data dumping is successful.
  * #psql dannydb postgresql
  * #select * from stock; ( if you see data you are looking for then is working)
 * Now after everything is done, you have to put this in cronjob to do automatic update. See the example in braun server cronjob how incremental_dumb script is used in crontab		 Steps to configure SQL database and control

 1. Install SQL database. This could be either on same server that Control resides on or a different server.
 {{{
# yum install postgresql}}}

 1. Download and install odbc driver for your database.
 {{{
# yum install unixODBC}}}
 1. Configure /etc/odbc.ini which you will need to configure:
  * [xxxxxx]: This is a ODBC connection name
  * Driver: This is ODBC driver that is used to connect to database (different for different SQL vedors)
  * Database: Name of database that you created
  * ServerName: The IP of the SQL server (possibly this machine)
  * Port: Port SQL server listens on (different for different SQL vendors)
  * UserName: -> SQL login name
  * Password: -> SQL password
 {{{
[control]
Driver = /usr/local/lib/psqlodbcw.so
Port = 5432
ServerName = localhost
Database = control
UserName = ccc
Password = support}}}

 1. Create database now using following command
 {{{
# createdb databasename
}}}
 at this point, if you run into problems using postgres, check the [[Postgresql_problems]] page.

 1. Create table command
 {{{
# createtb --help ( to see option)
# createtb -d <databasename>}}}

 1. Log off all Control users

 1. Set flag in {{{cooad}}}.
 {{{
Enable update of odbc data file = Y}}}
 Now this flag will start loging in dblog.dat.

 1. If the sql database is not used from the begining of control, all files will not be logged in dblog.dat to update in sql database. So simple enabling flag will not logged old files of control to dblog.dat, only the new files will be logged. To update old and as well as old , use this command.
 {{{
# fdb2log}}}

 1. Use
 {{{
# dblogdump}}}
 command to check contents of dblog.dat

 1. Now to start dumping control data to sql database. use this command
  * {{{
# log2odbc --help}}}
  * {{{
# log2odbc -d <ODBC_Connection_Name>}}}

 1. To test to see if the data dumping is successful.
 {{{
# psql dannydb postgresql
  => select * from stock;
}}}
 If you see the data you are looking for, then it is working.

 1. {{{log2odbc}}} defaults has the following default values:
 {{{
ODBC Connection Name = control
Username = ccc
Password = support }}}
 You will need to alter the {{{/u/cc/binl/incremental_dump}}} script so that {{{log2odbc}}} accesses the correct DB

 1. Now after everything is done, place a job in ccc's {{{crontab}}} to do perform the automatic update.
 {{{
* * * * * /u/cc/binl/incremental_dump >> /u/cc/LOG/incremental_dump.log 2>&1}}}

 1. dblog.dat does not get re-initialised. You will need to run {{{rolldblog}}} to initialise dblog.dat
Maybe add to backup script

----
 . CategorySql

SQL Mirroring

How it works

  1. A record in one of the .dat files is altered. This includes changes to quantities, balances. This includes some changes that polling would not normally send/receive.
  2. The type of record and the record number is recorded in dblog.dat

  3. log2odbc takes unprocessed dblog.dat entries, generates SQL query for the whole record and sends it via ODBC. It still needs access to the control .dat files as dblog.dat just contains pointers to records in the original .dat file.

  4. dblog.dat gets updated so that newly processed entries are not re-processed. This is the only file that gets written to by log2odbc and it will dump significantly faster if this file was copied to the local hard drive.

Installation

This instruction will help you understand how CONTROL and SQL database works together. Also this instruction will be helpful to troubleshoot or implementing SQL database for the client.

All control data files are stored in .dat files and .idx files are indexes for the .dat files.

Steps to configure SQL database and control

  1. Install SQL database. This could be either on same server that Control resides on or a different server. 
    # yum install postgresql
  2. Download and install odbc driver for your database. 
    # yum install unixODBC
  3. Configure /etc/odbc.ini which you will need to configure:
    • [xxxxxx]: This is a ODBC connection name
    • Driver: This is ODBC driver that is used to connect to database (different for different SQL vedors)
    • Database: Name of database that you created

    • ServerName: The IP of the SQL server (possibly this machine)

    • Port: Port SQL server listens on (different for different SQL vendors)

    • UserName: -> SQL login name

    • Password: -> SQL password 

    [control]
Driver     = /usr/local/lib/psqlodbcw.so
Port       = 5432
ServerName = localhost
Database   = control
UserName   = ccc
Password   = support
  4. Create database now using following command 
    # createdb databasename

    at this point, if you run into problems using postgres, check the Postgresql_problems page.

  5. Create table command 
    # createtb --help ( to see option)
# createtb -d <databasename>
  6. Log off all Control users

  7. Set flag in cooad. 

    Enable update of odbc data file = Y
    Now this flag will start loging in dblog.dat.
  8. If the sql database is not used from the begining of control, all files will not be logged in dblog.dat to update in sql database. So simple enabling flag will not logged old files of control to dblog.dat, only the new files will be logged. To update old and as well as old , use this command. 
    # fdb2log
  9. Use 
    # dblogdump
    command to check contents of dblog.dat
  10. Now to start dumping control data to sql database. use this command 
    • # log2odbc --help
    • # log2odbc -d <ODBC_Connection_Name>
  11. To test to see if the data dumping is successful. 
    # psql dannydb postgresql
  => select * from stock;
    If you see the data you are looking for, then it is working.

  12. log2odbc defaults has the following default values: 

    ODBC Connection Name = control
Username = ccc
Password = support

    You will need to alter the /u/cc/binl/incremental_dump script so that log2odbc accesses the correct DB

  13. Now after everything is done, place a job in ccc's crontab to do perform the automatic update. 

    * * * * * /u/cc/binl/incremental_dump >> /u/cc/LOG/incremental_dump.log 2>&1

  14. dblog.dat does not get re-initialised. You will need to run rolldblog to initialise dblog.dat

Maybe add to backup script

SQL_Mirroring (last edited 2013-09-18 06:09:33 by localhost)