StarQuest Technical Documents

How to Distribute StarSQL Data Source(s) to Your Users

Last Update: 16 September 2010
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ003

Abstract

StarSQL ships with an administrative tool (dsimport) that allows you to distribute data sources to your users. It eliminates the painstaking chore of manually reproducing data sources on each desktop in an enterprise environment. This technical document explains how to use dsimport.

Solution

There are two main ways to distribute data sources using dsimport. Both methods require that you install StarSQL on a test system, create the desired data sources, and export the registry entries for the given data sources. Once you have the exported data source registry file, you have two options:

1. Copy the resulting registry file to the client machines and import the data sources by running dsimport manually.
2. Include the data source file in a silent installation image, which will result in the data sources being automatically imported during the installation. For information on creating an installable image, refer to the technical document Creating a Custom StarSQL Installable Image.

Step 1: Install StarSQL and create the data sources.

You need to create and test the StarSQL data source(s) on the same platform that is used by the target clients. In other words, if the target clients run Windows XP, you must use a create the data sources to be distributed on a Windows XP system.

1. Install StarSQL on the test system.
2. Using the ODBC Data Source Administrator (typically found in Control Panel, Administrative Tools), create one or more StarSQL User or System data sources that you wish to distribute to your client desktops.

Step 2: Export the data source registry entries.

1. Click the Start button and select Run.

2. In the command field, enter "regedit".

3. In the Registry Editor, locate the ODBC.INI key that contains the data sources.

   For User data sources: HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
   For System data sources: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
   For 32-bit System data sources on a 64-bit system: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI

4. Highlight the appropriate ODBC.INI and, from the File menu, choose Export.

5. Select 'Win9x/NT 4 Registration Files' as the Save as type. It is important to choose the correct Save as type.

6. Save the file as DSIMPORT.TXT.

Step 3: Modify registry file to remove nonessential data sources (optional).

If you only want to import a particular data source and not all of the data sources in the file, you can edit the DSIMPORT.TXT at this time.

1. Open the DSIMPORT.TXT file in a text editor, such as Notepad.
2. The contents of the file may look something like this:

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\DB2PROD]
"AutoTypDefNam"="QTDSQL400"
"AutoTypDefOvr"="37,0,0"
"UID"="DB2USER"
"Driver"="C:\\Program Files\\StarQuest\\StarSQL\\Programs\\swodbc32.dll"
"Server"="DB2PROD"
"CatQual"="QSYS2"
"PkgColId"="STARSQL"
"Netlib"="SWTCP32.DLL"
"ReadOnly"="No"
"UseStaticMatch"="N"
"AutoBind"="N"
"Capture"="N"
"CaptureDDL"="N"
"ParameterMarkersOnly"="N"
"CaptureQryOnly"="N"
"SpecialColumns"="Y"
"CmdBufSiz"="32767"
"QryBufSiz"="32767"
"HostSec"="NO"
"StrictParsing"="Y"
"EE"="N"
"TwoPC"="N"
"WaitSess"="No"
"HostName"="PRODSYS"
"Port"="446"
"LiteralConversion"="Y"
"AllowSynonyms"="Y"
"TimeStampAsChar"="?"
"IncludeSynonyms"="Y"
"FetchAhead"="Y"
"KeepDynamic"="Y"
"SupDynRul"="N"
"LongStrParams"="N"
"AccessibleTables"="N"
"AutoSqlSet"="N"
"AccessFix1"="N"
"CacheUID"="Y"
"FoldUIDPWD"="N"
"NMFix"="N"
"UseEncryption"="N"
"CharacterSubstitution"="W"
"UseJumboPackages"="N"
"CustomizePrdid"="N"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"DB2PROD"="StarSQL 32"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC File DSN]
"DefaultDSNDir"="C:\\Program Files\\Common Files\\ODBC\\Data Sources"

• The items in bold are necessary for dsimport to work. The area in between the bolded items are unique to each data source.
• You may remove any StarSQL data source configurations that you do not wish to deploy. Note that dsimport will ignore any non-StarSQL entries therefore you do not need to remove them from the file.
• If any StarSQL data sources are removed, be sure to also remove the data source names from the list under the [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources] heading.

Step 4: Import the StarSQL data sources.

1. If you already have StarSQL installed on the client computer, copy DSIMPORT.TXT into the \Program Files\StarQuest\StarSQL\Programs directory and run dsimport.exe.
2. Otherwise, you can include DSIMPORT.TXT in the silent installation image as instructed in the technical document Creating a Custom StarSQL Installable Image. During installation, the data sources you defined in DSIMPORT.TXT will be created.

Command Line Parameters

dsimport [/c] [/v] [/f <filename]

If no parameters are supplied, dismport will create system data sources using a file named "dsimport.txt" located in the same directory as the dsimport application.

Optional parameters:

/v (or -v or /d or -d) - verbose (debug) mode: display progress information in pop-up dialogs

/c (or -c) - create user data sources rather than system data sources

/f or -f <filename> - filename to use in place of dsimport.txt

64-bit Considerations

• dsimport can be used to freely copy data sources between 32 and 64-bit systems; it will ignore information in DSIMPORT.TXT that differs between 32 and 64-bit systems, such as the name of the driver - "StarSQL 32" vs "StarSQL (64-bit)". So a DSIMPORT.TXT file created from 64-bit data sources can be used with dsimport from StarSQL 32-bit to create 32-bit data sources, and vice versa.
• Similarly, dsimport can be used to copy data sources between StarSQL (32-bit) and StarSQL (64-bit) installed on the same 64-bit system.
• On a 64-bit system, StarSQL (32-bit) is typically installed in C:\Program Files (x86)\StarQuest\StarSQL.
• On a 64-bit system, 32-bit system data sources are stored in the registry in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI and viewed with the 32-bit version of ODBC Administrator (C:\Windows\SYSWOW64\odbcad32.exe). You can also access the 32-bit version of ODBC Administrator using the "ODBC Administrator" shortcut of the "StarSQL (32-bit)" program group.
• On a 64-bit system, both 32-bit and 64-bit user data sources are stored in the same area of the registry (HKEY_CURRENT_USER\Software\ODBC\ODBC.INI) and are visible to both 32-bit and 64-bit applications (including the 32-bit & 64-bit versions of ODBC Administrator), though the data source is usable only by the appropriate version of the application. So you should be careful when naming user data sources to avoid confusion or accidentally overwriting an existing data source with dsimport.

Windows 7/Windows Server 2008/Vista Considerations

• dsimport runs in elevated mode (as Administrator) so that it has authority to create system data sources; a UAC elevation prompt is displayed when you run dsimport.
• When invoked from within the StarSQL installer (triggered by the presence of "DSIMPORT.TXT" in the root directory of the installer - i.e. the same level as setup.exe), there is no additional elevation prompt, as the installer is already running elevated.
• Copying DSIMPORT.TXT into the \Program Files\StarQuest\StarSQL\Programs directory will display a confirmation prompt because the Program Files area is protected; alternatively, you can copy both dsimport.exe and DSIMPORT.TXT into a non-protected directory elsewhere on your system.

Known Limitations

• The registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources contains a list of all data sources, both StarSQL and non-StarSQL. This list is not used by dsimport. However, if this list is extremely long, it can prevent correct operation of dsimport; the symptom of the problem is that the data sources that appear before it in DSIMPORT.TXT (i.e. those with names that come before "ODBC.." alphabetically) will be created, while those that appear appear after it do not. If you encounter this problem, edit DSIMPORT.TXT and remove the ODBC Data Sources section.