In order to bring you the best possible user experience, this site uses Javascript. If you are seeing this message, it is likely that the Javascript option in your browser is disabled. For optimal viewing of this site, please ensure that Javascript is enabled for your browser.
Login  |   On Demand  |   Home  |   qad.com




Setting Up the Database Server
QAD CRM is a multi-database application that requires a minimum of three databases connected concurrently to a single session. Each QAD CRM session connects to, at minimum, a main database (bisgen), an administration database (bisgmenu) and a data synchronization database (dataexch). If you plan to integrate with QAD Enterprise Applications, you need to connect to other databases.
Working QAD CRM databases are created in two stages. You first create empty databases, then use them as a template to build test, production, training, development and other databases.
To set up databases in a UNIX/Linux server, you must have experience creating databases and loading data definition files in Progress. You must also know how to modify system files such as service and startup scripts for databases, and have the appropriate write permission to make the required file and folder updates.
QAD CRM supports the following code pages:
Unicode (UTF-8)
ISO (ISO8859-1)
For non-iso languages, CRM databases should be configured to run in Unicode.
Important: Languages requiring Unicode character sets must be able to access Unicode components in the DLC/prolang/utf directory on the server.
Before creating the database, determine the following:
1
The directory where you want to create the QAD CRM databases, referred to as QADCRMInstallDirDatabaseServer /db
2
The Progress directory
3
The host name of the database server
QAD recommends that you deploy QAD CRM and QAD Enterprise Applications databases on the same database server.
Set the Progress DLC variable correctly and ensure that the $DLC\bin directory is included in the PATH environment variable. DLC is the directory where Progress is installed, and bin is a subdirectory of DLC where the most often used Progress executables reside.
Setting Up Databases in a UNIX/Linux Server
Creating Non-Unicode Databases
To create empty databases in a UNIX/Linux server:
1
Go to QADCRMInstallDirDatabaseServer /db.
2
Transfer all the data definition (.df) and structure (.st) files from QADCRMInstallDirDatabaseServer /db/df to QADCRMInstallDirDatabaseServer/db using an FTP or WinSCP program.
3
Enter the following:
prostrct create bisgen bisgen.st –blocksize 8192
prostrct create bisgmenu bisgmenu.st –blocksize 8192
prostrct create dataexch dataexch.st –blocksize 8192
procopy $DLC/empty8 bisgen
procopy $DLC/empty8 bisgmenu
procopy $DLC/empty8 dataexch
4
Start the Progress data dictionary using the command:
_progres –p _admin.p
5
Connect bisgen, bisgmenu, and dataexch databases and load data definitions into it that define each table, field, and index in the database.
6
The database schema (.df file) consists of the sequences, tables, fields and indexes in the database. In this task, you load the schema into the empty bisgen, bisgmenu, and dataexch databases using data definition files bisgen.df, bisgmenu.df and dataexch.df. Load the data definition files using the Progress data dictionary tool.
7
The resulting empty databases are used to build all your main and support databases such as bisgenprod, bisgentest, and bisgentrain. Create copies of the databases for backup and testing purposes.
Creating Unicode Databases
1
Go to QADCRMInstallDirDatabaseServer /db.
2
Transfer all the data definition (.df) and structure (.st) files from QADCRMInstallDirDatabaseServer/db/df to QADCRMInstallDirDatabaseServer /db using an FTP or WinSCP program.
3
Type the following:
prostrct create bisgen bisgen.st -blocksize 8192
prostrct create bisgmenu bisgmenu.st -blocksize 8192
prostrct create dataexch dataexch.st -blocksize 8192
procopy $DLC/prolang/utf/empty8 bisgen
procopy $DLC/prolang/utf/empty8 bisgmenu
procopy $DLC/prolang/utf/empty8 dataexch
4
Load the ICU-UCA collation table.
a
Start the Progress data dictionary using the following command:
_progres -p _admin.p
b
Connect to the database you want to load the ICU-UCA collation table into by choosing Database|Connect from the menu.
c
Choose Admin|Load Data and Definitions|Data Definitions (.df file). Locate the file ICU-UCA.df in $DLC /prolang/utf/ and load it into the database.
Ignore the following error and warning messages:
Error: Collation tables for database DBName have been changed. You must rebuild all the indexes in order to reconnect to this database. (2609)

Warning Message
d
Disconnect from the database by choosing Database|Disconnect from the menu.
Note: Repeat steps b through d for each of the three CRM databases—bisgen, bisgmenu, and dataexch.
Note: Whenever you use Progress tools such as Data Dictionary or Data Administration for setting up Unicode databases, make sure that the .pf file used by the Progress session specifies the following parameters:
-cpinternal UTF-8
-cpstream UTF-8
-cpcoll ICU-UCA
-cpcase Basic
5
Rebuild indexes. Execute the following command to rebuild index for the new ICU-UCA collation:
proutil DBPath/bisgen -C idxbuild all -cpinternal UTF-8
proutil DBPath/bisgmenu -C idxbuild all -cpinternal UTF-8
proutil DBPath/dataexch -C idxbuild all -cpinternal UTF-8
6
Load your .df files.
Start the Progress data dictionary using the following command:
_progres -p _admin.p
Connect to the bisgen, bisgmenu, and dataexch databases and load into them data definitions that define tables, fields, and indexes in the databases.
Note: For the bisgen Unicode database, you should skip loading bisgen.df and load bisgen_utf.df instead.
7
Use the empty databases to build your main and support databases such as bisgenprod, bisgentest, and bisgentrain. Create copies of the databases for backup and testing purposes.
Setting Up Databases on a Windows Server
The QAD CRM installation uses or creates several directories. If you use the default directories and they do not exist at the time of installation, they are created automatically. This applies the ownership and appropriate permissions. However if you create or use the other directories outside installation, make sure that permissions are set properly.
The QAD CRM Installation environment has a db folder. QAD recommends that you keep the databases in this folder. For example, you can keep test databases by creating a new QADCRMInstallDir \db\test folder.
Before creating the database, determine the following:
1
The directory where you want to create the QAD CRM databases
2
The Progress directory
3
The host name of the database server
Set the Progress DLC variable correctly and ensure that the %DLC%\bin directory is included in the PATH environment variable. DLC is the directory where Progress is installed, and bin is a subdirectory of DLC where the most often used Progress executables reside.
To add DLC and PATH to user environment variables:
1
From the Desktop, choose Start|Settings|Control Panel.
2
Double-click the System icon and select the Advanced tab.
3
Click the Environment Variable button.
4
Click New under the User variables for <user name> listbox. The New User Variable dialog box appears.
5
In the Variable name field, type DLC.
6
In the Variable value field, type the Progress installation path. Then click OK.
7
Repeat Step 4 to create a PATH environment variable. In the Variable value field, type the following:
%PATH%;%DLC%;%DLC%\bin
When you have added the DLC and PATH environment variables, use the DOS command prompt to run all the Progress commands.
Creating Non-Unicode Databases
1
Create two folders test and prod to hold the test and production databases under QADCRMInstallDir \db respectively.
2
Using Windows Explorer, copy all the database structure (.st) and data definition (.df) files from QADCRMInstallDir \db\df to QADCRMInstallDir \db\test assuming that you wish to place your newly created databases in QADCRMInstallDir \db\test folder.
3
From the Desktop, choose Start|Run. The Run dialog box appears. Type cmd in the Open field. Then click OK.
4
Change to the QADCRMInstallDir \db\test directory.
5
Type the following:
prostrct create bisgen bisgen.st –blocksize 8192
prostrct create bisgmenu bisgmenu.st –blocksize 8192
prostrct create dataexch dataexch.st –blocksize 8192
procopy %DLC%/empty8 bisgen
procopy %DLC%/empty8 bisgmenu
procopy %DLC%/empty8 dataexch
6
Start the Progress data dictionary using the command:
_progres –p _admin.p
7
Connect the bisgen, bisgmenu, and dataexch databases and load data definitions that define each table, field, and index in the database.
8
The database schema (.df file) consists of the sequences, tables, fields, and indexes in the database. You load the schema into the empty bisgen, bisgmenu, and dataexch databases using the data definition files bisgen.df, bisgmenu.df and dataexch.df. Load the data definition files using Progress data dictionary tool.
The resulting empty databases are used to build all your main and support databases such as bisgenprod and bisgentrain. Create copies of the databases for backup and testing purposes.
Creating Unicode Databases
To create Unicode databases on the Windows Server:
1
Create two directories—test and prod—for the test and production databases under QADCRMInstallDir \db respectively.
2
Copy all the database structure (.st) and data definition (.df) files from QADCRMInstallDir \db\df to QADCRMInstallDir \db\test.
3
From the Windows Start menu, choose Run; then enter cmd and click OK to open the command window.
4
Change to the QADCRMInstallDir \db\test directory.
5
Enter the following:
prostrct create bisgen bisgen.st -blocksize 8192
prostrct create bisgmenu bisgmenu.st -blocksize 8192
prostrct create dataexch dataexch.st -blocksize 8192
procopy %DLC%\prolang\utf\empty8 bisgen
procopy %DLC%\prolang\utf\empty8 bisgmenu
procopy %DLC%\prolang\utf\empty8 dataexch
6
Load the ICU-UCA collation table.
a
Start the Progress data dictionary using the following command:
_progres -p _admin.p
b
Connect to the database you want to load ICU-UCA collation table into by choosing Database|Connect from the menu.
c
Choose Admin|Load Data and Definitions|Data Definitions (.df file). Locate the file ICU-UCA.df in %DLC%\prolang\utf\ and load it into the selected database.
Ignore the following error and warning messages:
Error: Collation tables for database DBName have been changed. You must rebuild all the indexes in order to reconnect to this database. (2609)

Warning Message
d
Disconnect from the database by choosing Database|Disconnect from the menu.
Note: Repeat steps b through d for each of the three CRM databases—bisgen, bisgmenu, and dataexch. Whenever you use Progress tools such as Data Dictionary or Data Administration for setting up Unicode databases, make sure that the .pf file used by the Progress session specifies the following parameters:
-cpinternal UTF-8
-cpstream UTF-8
-cpcoll ICU-UCA
-cpcase Basic
7
Rebuild indexes. Execute the following command to rebuild indexes for the new ICU-UCA collation:
proutil DBPath/bisgen -C idxbuild all -cpinternal UTF-8
proutil DBPath/bisgmenu -C idxbuild all -cpinternal UTF-8
proutil DBPath/dataexch -C idxbuild all -cpinternal UTF-8
8
Load your .df files.
Connect to the bisgen, bisgmenu, and dataexch databases and load into them data definitions that define tables, fields, and indexes in the databases.
Note: For the bisgen Unicode database, you should load bisgen_utf.df instead of bisgen.df.
9
Use the Progress Data Dictionary tool to load the data definition files bisgen.df, bisgmenu.df, and dataexch.df into the empty bisgen, bisgmenu, and dataexch databases.
10
Use the empty databases to build your main and support databases such as bisgenprod, bisgentest, and bisgentrain. Create copies of the databases for backup and testing purposes.
Starting Databases in Multi-User Mode
To enable clients to communicate with the databases, you need to start databases in multi-user mode.
1
On the database server machine, tailor your services file. All services names, host names, and port numbers must match in the services files of each client and each database server on the network. The location of the UNIX/Linux services file on the server is typically the /etc directory. On Windows, it is located in C:\windows\system32\drivers\etc.
Note: The more users you expect to have, the more numbers you should leave open between port numbers.
2
Add the names of your database services to your services file. Add one for each main database and any other databases you plan to connect to. Limit the service name to 16 characters. You can use any unused port numbers.
3
The database service names and suggested port numbers are described below. If these port numbers conflict with your existing setup, you must specify appropriate numbers.
 
Service Name
Port #/Protocol
Comment
bisgentest
2030/tcp
# test bisgen
bisgmenutest
2040/tcp
# test bisgmenu
dataexchtest
2050/tcp
# test dataexch
bisgenprod
2060/tcp
# Production bisgen
bisgmenuprod
2070/tcp
# Production bisgmenu
dataexchprod
2080/tcp
# Production dataexch
4
Start the databases in multi-user mode using one of the following:
Progress Explorer (Windows only)
Batch files
The installation technician is responsible for supplying and customizing the batch files.
If you plan to integrate with QAD Enterprise Applications, QAD recommends that you use QAD Enterprise Applications startup and shutdown scripts.
5
Make sure that the databases are running properly. In case of errors, check Progress help and database startup parameters.