Using startSQLRepository from scratch

While you can very easily import/export data through /dyn/admin, if working on a production environment you will have to deal with very high amounts of data that your browser will just not withstand to display/ingest, in that case you can use startSQLRepository utility program from ATG installation.

Information for configuring it is out there on Oracle docs, however, there are some tricks you might be missing, I'm going to detail steps for getting it up.

First of all, make sure you read through Oracle's documentation:
http://docs.oracle.com/cd/E52191_03/Platform.11-1/ATGRepositoryGuide/html/s1207startsqlrepository01.html

Configure your datasources for database access

In order to get started with the utility you need to configure the FakeXADataSource component in order to allow the utility to connect to your database, you can see documentation from Oracle in here. But let's get into detail:

Create a new server on your ATG installation along with the necessary folders for datasource configuration:

mkdir $ATG_ROOT/home/servers/<your_server_name>
mkdir ... <your_server_name>/localconfig
mkdir ... <your_server_name>/localconfig/atg
mkdir ... <your_server_name>/localconfig/atg/dynamo
mkdir ... <your_server_name>/localconfig/atg/dynamo/service
mkdir ... <your_server_name>/localconfig/atg/dynamo/service/jdbc

The default datasource is JTDataSource, so we will create an override for it with the values as suggested by Oracle's documentation:

vi ... <your_server_name>/localconfig/atg/dynamo/service/jdbc/JTDataSource.properties

And paste these values:

$class=atg.service.jdbc.MonitoredDataSource

min=10

max=20

blocking=false

maxFree=-1

loggingSQLWarning=false

loggingSQLDebug=false

loggingSQLInfo=false

dataSource=/atg/dynamo/service/jdbc/DirectJTDataSourceFakeXA

loggingSQLError=false

As you can see in the dataSource property, it points to a FakeXA component which specifies the database connection, so create a file like:

vi ... <your_server_name>/localconfig/atg/dynamo/service/jdbc/DirectJTDataSourceFakeXA.properties

And paste these values, notice I hav ojdbc driver in this example:

$class=atg.service.jdbc.FakeXADataSource
server=localhost:1521:orcl
user=<the_core_user>
needsSeparateUserInfo=false
URL=jdbc:oracle:thin:@localhost:1521:orcl
readOnly=true
password=<the_password>
database=
driver=oracle.jdbc.xa.client.OracleXADataSource

That will setup the connection for CORE schema, since you most likely need this utility for catalog data you'll need to create the CATA/B connection components the same way as you did for CORE, just remember to use the SwitchingDataSources and create their corresponding FakeXA component:

vi ... <your_server_name>/localconfig/atg/dynamo/service/jdbc/SwitchingDataSourceA.properties
vi ... <your_server_name>/localconfig/atg/dynamo/service/jdbc/SwitchingDataSourceB.properties
vi ... <your_server_name>/localconfig/atg/dynamo/service/jdbc/SwitchingDataSourceAFakeXA.properties
vi ... <your_server_name>/localconfig/atg/dynamo/service/jdbc/SwitchingDataSourceBFakeXA.properties

You're now set with your database connection components. Look at the documentation links above for details on what each property does.

Configure a server to override components

When using startSQLRepository you will be using default settings from ATG, for example, all component's datasources point to JTDataSource by default, so you'll need to override configuration for those components that need to use the SwitchingDataSource, all you have to do is set it like this:

dataSource=/atg/dynamo/service/jdbc/SwitchingDataSource

This is a list of components you'll need by default (or what I have found so far):

/atg/commerce/catalog/ProductCatalog.properties
/atg/commerce/locations/LocationRepository.properties
/atg/commerce/pricing/priceLists/PriceLists.properties
/atg/content/ContentManagementRepository.properties
/atg/multisite/SiteRepository.properties
/atg/seo/SEORepository.properties
/atg/userprofiling/PersonalizationRepository.properties

Look at the logs, if you see errors of components not finding any tables in CORE schema then it means you have to override it for the SwitchingDataSource.

Almost finished, you now need to build your code to make sure it has the latest changes and run the startSQLRepository utility, for example:

$ATG_ROOT/home/bin/startSQLRepository -m DCS -s startSQL-import -exportRepositories /atg/commerce/catalog/ProductCatalog $ATG_ROOT/home/servers/startSQL-import/CatalogExport.xml

A very brief explanation on what the above does:
-m DCS -> This is for loading the DCS module, which contains the catalog related components
-s startSQL-import -> This is for using a server name startSQL-import for components override
The rest should be self explanatory...

Have a nice day!