Created On:  11 January 2011

Problem:

With AcuODBC users had the capability to access more than one table with the same name, and this was called multi-company support. How this can be done with AcuXDBC?

Resolution:

AcuXDBC offers convenient ways to manage multiple company data sets. For example, you may have two sets of data for Company01 and Company02. The layouts of the data files are the same, but the file names have different prefixes or suffixes. For example, ACCT01 and ACCT02 may be the accounting files for Company01 and Company02. They are stored in different directories.

Rather than having to set up system catalogs for each of your companies individually, you can do one of two things:

1. You can have a different configuration file for each company. In this configuration file, you can use wildcards and substitution characters (defined in the configuration file) to identify which data files to access, allowing you to have a single system catalog that gets populated only once and yet supports more than one company.

2. You can set up a separate schema for each company, and then you can specify which table to access. For example, you could set up a schema for "acme" and a separate schema for "generic," and then qualify your SQL statements.

To set up a multi-company DSN with wildcard substitution:

1. Create a text file listing the XFD filenames for your companies. Name the file "list.txt" or something similar.

For example, if your data is: 

main.xfd 
ACCT01.dat 
ACCT02.dat 
ACCT03.dat

you might have an entry like this in "list.txt": 

main#AcctTbl#Acct$$  

where main is the XFD name, AcctTbl is the table/repository name, and Acct$$ represents the Vision file name.

2.       In the configuration file, "acuxdbc.cfg" by default, use the FILENAME_WILDCARD variable to define the wildcards and their respective substitution characters.

For example, the configuration file might contain: 

FILENAME_WILDCARD $$=01
3.       Create your system catalog using the "-f" option as shown:
xdbcutil -d c:\\data\\dict -f list.txt

When building the system catalog, xdbcutil uses the XFD "main" to create a repository table called "AcctTbl".

4.       At the company site, create a single DSN for Accounting, and point to the configuration file that contains the substitution characters for that company.

When a user accesses the table AcctTbl from your COBOL program, AcuXDBC looks in the configuration file for the FILENAME-WILDCARD variable, and, substituting "01" for "$$" accesses the data file, "Acct01". In programming terms, when "SELECT * from AcctTbl;" is issued, it functions as if "SELECT * from Acct01;" was entered.

There is no limit on the number of wildcards/replacements that you can use with AcuXDBC. You can change several characters in a filename. For example: 

ACCT>=$$**  

where the actual filename would be ACCTZ01AA and the configuration file entry would be: 

FILENAME_WILDCARD >==Z ;$$=01;**=AA

You don't have to create a list file to specify your company wildcards. If you prefer, you can specify them directly on the xdbcutil command line, like this: 

xdbcutil -d c:\\data\\dict -x c:\\data\\data  -a mainxfd#AcctTbl#Acct$$
Note: You may have to escape the wildcard characters if they are shell interpreted. For example, the sample above would not work on UNIX/Linux, because the shells interpret the "$" character. You would have to use:

xdbcutil -d /data/dict -x /data/data -a mainxfd#AcctTbl#Acct\\$\\$
or
xdbcutil -d /data/dict -x /data/data -a "mainxfd#AcctTbl#Acct$$"

To set up a multi-company with separate schemas (table ownership):

Alternatively, to support multiple companies, you could specify database table ownership using the "-o" option to xdbcutil when creating your system catalog. For example, there could be two tables named AcctTbl-one owned by Company01 and one by Company 02.

Filename
Database Name
Owner
Table name

ACCT01

dd

company01

AcctTbl

ACCT02

dd

company02

AcctTbl

On the command line, you specify: 

xdbcutil -d  -x  -o  
xfdname#tablename#filename

Or in this case: 

xdbcutil -d c:\\data\\dict -x c:\\data\\XFD -o Company01 
mainxfd#AcctTbl#ACCT01  
 
xdbcutil -d c:\\data\\dict -x c:\\data\\XFD -o Company02 
mainxfd#AcctTbl#ACCT02

Then you can access all the data files with one SQL query to one DSN, like this: 

SELECT * FROM COMPANY01.AcctTbl AND COMPANY02.AcctTbl 
WHERE .....

You can also use synonyms for AcctTbl, such as "Accts Receivable" or "Billing".

Note: If you don't want a table owner to be listed publicly in applications like Microsoft Access, specify "-o " " " when loading the table with xdbcutil and set the IGNORE_OWNER variable to "1" (on, true, yes) in your configuration file.