Using ATG development operation tags.

Generating DDL's of any repository
If you have created a new repository or new item-descriptor in reposiotry in your module.
To generate ddl,just start the instance (ignore table missing errors).
Once instance is up, go to that particular repository in dynamo component browser (/dyn/admin/)
In "Run XML Operation Tags on the Repository"
use

<print-ddl/>

Which will give you DDL for that repository.

output -

-- drop table www_instock_wic;
CREATE TABLE www_instock_wic (
    primary_wic         INTEGER    NOT NULL,
    primary_sku_id         varchar2(254)    NOT NULL,
    instock_wic         INTEGER    NOT NULL,
    ship_sub_wic         INTEGER    NOT NULL,
    ship_sub_sku_id     varchar2(254)    NOT NULL,
    update_dttm         DATE    NULL,
    sub_wic_desc         varchar2(250)    NULL,
    sub_start_date         DATE    NULL,
    PRIMARY KEY(primary_wic)
);

If your repository is version repository since $class for that repository is pointing to
 atg.adapter.version.VersionRepository

DDL generated will have all columns which are needed for versioning. 

-- drop table dcs_catalog;
CREATE TABLE dcs_catalog (
    catalog_id         varchar2(254)    NOT NULL,
    asset_version         INTEGER    NOT NULL,
    version         INTEGER    NULL,
    creation_date         DATE    NULL,
    last_mod_date         DATE    NULL,
    migration_status     INTEGER    NULL,
    migration_index     INTEGER    NULL,
    item_acl         CLOB    NULL,
    display_name         varchar2(254)    NOT NULL,
    version_deleted     number(1)    NULL,
    version_editable     number(1)    NULL,
    pred_version         INTEGER    NULL,
    workspace_id         varchar2(254)    NULL,
    branch_id         varchar2(254)    NULL,
    is_head         number(1)    NULL,
    checkin_date         DATE    NULL,
    CHECK (version_deleted IN (0, 1)),
    CHECK (version_editable IN (0, 1)),
    CHECK (is_head IN (0, 1)),
    PRIMARY KEY(catalog_id, asset_version)
); 

You can also generate ddl's using startSQLRepository.

In <ATG-HOME>/bin/

bin\startSQLRepository -m <module-name> -repository /atg/commerce/catalog/ProductCatalog -outputSQLFile product_catalog.sql

Testing RQL's - query-items

<query-items item-descriptor="inventory"> catalogRefId="sku386558" </query-items>

output -

Query: catalogRefId="sku386558" returns 1 items:
<add-item item-descriptor="inventory" id="inv1000">
  <!-- export is false   <set-property name="version"><![CDATA[4]]></set-property>  -->
  <set-property name="catalogRefId"><![CDATA[sku386558]]></set-property>
  <set-property name="creationDate"><![CDATA[__NULL__]]></set-property>
  <set-property name="startDate"><![CDATA[1/14/2005 05:10:17]]></set-property>
  <set-property name="availabilityStatus"><![CDATA[OUTOFSTOCK]]></set-property>
</add-item>

--- Querying using date or timestamp by using RQL
With date

<query-items item-descriptor="order"> creationDate>date("2011-10-10") and state="SUBMITTED" </query-items>

With timestamp

<query-items item-descriptor="order"> creationDate>date("2011-10-10 10:00:00 EST") and state="SUBMITTED" </query-items>

To check what items are in cache

<dump-caches item-descriptors="skuSpecialPriceRules" dump-type="both"/>

output -  

Desc: skuSpecialPriceRules mItemCache: 2 items, 0, weak items
LRU: [skuSpecialPriceRules:9400059, skuSpecialPriceRules:5100045]
mItems: {5100045=skuSpecialPriceRules:5100045,
9400059=skuSpecialPriceRules:9400059}
mWeakItems: {}
OnDeckItemCache: null
NullItemCache: {}

*** begin pre-cache XML output
<load-items item-descriptor="skuSpecialPriceRules">5100045,9400059</load-items>
*** end pre-cache XML output

Other operation tags
<add-item>, <update-item>, <remove-item> -to add or update or remove items
<export-items>, <import-items> - to export and import items
<print-item> - to print item
<transaction> - to maintain transactions while adding or removing items.

Reference - http://download.oracle.com/docs/cd/E23095_01/Platform.93/RepositoryGuide/html/s1201sqlrepositoryreference01.html