DDML Document Example
The following example presents a possible DDML document.
<tables-definition product="CD"><!-- CD for customer data -->
<!-- example for a timeslice statistics level table for the Customer's J2EE data -->
<table-definition name="PW_JECD_CUSTOMER_DATA_T" type="STATISTICS" filterable="TRUE" oracle-storage-clause="NEXT 4M PCTINCREASE 0">
<column-definition name="JECD_TIMESTAMP" data-type="TIMESTAMP" null="FALSE" type="DATE" /><!-- mandatory column -->
<column-definition name="JECD_PWHG_ID" data-type="INTEGER" data-length="4" null="FALSE" type="IDENTIFIER" default="0"/><!-- mandatory column -->
<column-definition name="JECD_MINUTES_COUNT_SUM" data-type="INTEGER" data-length="9" null="FALSE" type="SUM" default="1"/><!-- mandatory column -->
<column-definition name="JECD_PWII_INSTANCE_ID" data-type="INTEGER" data-length="9" null="FALSE" type="IDENTIFIER"/><!-- mandatory column -->
<column-definition name="JECD_RECIEVED_TIMESTAMP" data-type="TIMESTAMP" null="FALSE" default="CURRENT TIMESTAMP" type="DATE"/><!-- mandatory column -->
<column-definition name="JECD_IS_SERVICE_REQUEST" data-type="BOOLEAN" null="FALSE" type="IDENTIFIER" default="FALSE" baseline-value="F"/><!-- identifer definition -->
<column-definition name="JECD_THRESHOLD_BREACH" data-type="BOOLEAN" null="TRUE" type="IDENTIFIER"/><!-- identifer definition -->
<column-definition name="JECD_TYPE" data-type="VARCHAR" data-length="254" null="FALSE" type="IDENTIFIER" /><!-- identifer definition -->
<column-definition name="JECD_RESPONSE_TIME_SUM" data-type="FLOAT" data-length="15" null="FALSE" type="SUM" default="0"/><!-- counter definition -->
<column-definition name="JECD_HIT_COUNT_SUM" data-type="INTEGER" data-length="19" null="FALSE" type="SUM" default="0"/><!-- counter definition -->
<column-definition name="JECD_INTERNAL_TIME_SUM" data-type="FLOAT" data-length="15" null="FALSE" type="SUM" default="0"/><!-- counter definition -->
<column-definition name="JECD_JDBC_TIME_AVG" data-type="FLOAT" data-length="15" null="FALSE" type="AVG" default="0"/><!-- counter definition -->
<column-definition name="JECD_ACTIVE_THREADS_AVG" data-type="FLOAT" data-length="15" null="TRUE" type="AVG"/><!-- counter definition -->
<index-definition name="IW_JECD_01_T" unique="FALSE" primary="FALSE" clustered="FALSE" columns-order="JECD_PWII_INSTANCE_ID ASC JECD_RECIEVED_TIMESTAMP ASC JECD_TIMESTAMP ASC" oracle-storage-clause="NEXT 4M PCTINCREASE 0"/><!-- index definition -->
<summary-hour name="PW_JECD_CUSTOMER_DATA_H"/><!-- indication to summarize the data at an hourly level -->
<summary-day name="PW_JECD_CUSTOMER_DATA_D"/><!-- indication to summarize the data at a dayly level -->
<summary-week name="PW_JECD_CUSTOMER_DATA_W"/><!-- indication to summarize the data at a weekly level -->
<summary-month name="PW_JECD_CUSTOMER_DATA_M"/><!-- indication to summarize the data at a monthly level -->
<baseline name="PW_JECD_CUSTOMER_DATA_B" oracle-additional-clause="NOLOGGING"/><!-- indication to create a baseline for that table -->
</table-definition>
<!-- example for a non-statistcs table that should be part of the PMDB schema -->
<table-definition name="PW_ORSR_STRUCTURE_CONTROL" type="PWOTHER" oracle-storage-clause="initial 1M next 10M minextents 1 maxextents unlimited pctincrease 0">
<column-definition name="ORSR_DATABASE_ID" data-type="VARCHAR" data-length="255" null="TRUE"/>
<column-definition name="ORSR_UPDATE_START" data-type="TIMESTAMP" null="TRUE"/>
<column-definition name="ORSR_STATUS" data-type="VARCHAR" data-length="30" null="TRUE"/>
<column-definition name="ORSR_ROW_TYPE" data-type="VARCHAR" data-length="2" null="TRUE"/>
<column-definition name="ORSR_PURGE_DATE" data-type="TIMESTAMP" null="TRUE"/>
<column-definition name="ORSR_LAST_PURGED_TABLE" data-type="VARCHAR" data-length="30" null="TRUE"/>
</table-definition>
<!-- example for a view creation -->
<view-definition name="ACTIVE_STATUS_VIEW" view-columns="DATABASE_ID DATABASE_STATUS" as-query="SELECT ORSR_DATABASE_ID,ORSR_STATUS FROM PW_ORSR_STRUCTURE_CONTROL WHERE ORSR_STATUS='ACTIVE'"/>
<!-- example for DDL/DML commands that should be performed during installation -->
<ddl-definition dbms="ORACLE" version="" event="INSTALL" statement="create sequence PRECISE_STATEMENT_ID_SEQ"/>
<!-- example for DDL/DML commands that should be performed during uninstallation -->
<ddl-definition dbms="ORACLE" version="" event="UNINSTALL" statement="drop sequence PRECISE_STATEMENT_ID_SEQ"/>
</tables-definition>
Retrieve Request Example
The following example sends one query to retrieve data.
<queries connection-pool="pw" single-connection="true">
<free-query id="501">
<!-- meta-data is optional -->
<meta-data>
<fetch-rows value="10000" /> <!-- number of rows to retrieve -->
<skip-rows value="0" /> <!-- number of rows to skip -->
<timeout value="30" /> <!-- maximum time to wait for database execution -->
</meta-data>
<query-text value="SELECT * FROM PS_PWTR_TABLE_RANGE WHERE PWTR_TABLE_NAME = ?" />
<bind-variable id="1" value="PW_NTAC_ACTIONS_M" type="string" />
</free-query>
</queries>
The following example sends several queries to retrieve data.
<queries connection-pool="pw" single-connection="false"> <!-- with single-connection set to false, queries are performed in parallel -->
<free-query id="601">
<!-- meta-data is optional -->
<meta-data>
<fetch-rows value="1" /> <!-- number of rows to retrieve (fetching only one row) -->
<skip-rows value="0" /> <!-- number of rows to skip -->
<timeout value="30" /> <!-- Maximum time to wait for database execution -->
</meta-data>
<!-- bind variables are optional -->
<query-text value="SELECT * FROM PW_PWII_INSTANCE_ID" />
</free-query>
<free-query id= "602">
<!-- meta-data is optional -->
<meta-data>
<timeout value="45" /> <!-- Maximum time to wait for database execution -->
</meta-data>
<query-text value="UPDATE PS_PWSE_SCHEDULED_EVENTS SET PWSE_START_TIMESTAMP = SYSDATE+4/1440, PWSE_STATUS = 'UPDATE' WHERE PWSE_ID = 6" />
</free-query>
<free-query id="603">
<!-- meta-data is optional -->
<meta-data>
<timeout value="45" /> <!-- Maximum time to wait for database execution -->
</meta-data>
<query-text value="SELECT * FROM PS_PWTR_T" />
</free-query>
.
.
.
</queries>
Retrieve Response Examples
The following example shows a retrieve response to one query executed without errors.
<rowsets>
<rowset id="501">
<row rownum="1">
<PWTR_PWII_ID>1005</PWTR_PWII_ID>
<PWTR_TABLE_NAME>PW_NTAC_ACTIONS_M</PWTR_TABLE_NAME>
<PWTR_START_TIMESTAMP></PWTR_START_TIMESTAMP> <!-- indication of null -->
<PWTR_END_TIMESTAMP></PWTR_END_TIMESTAMP>
<PWTR_LAST_PARTITION_TIMESTAMP>2008-08-01 00:00:00.0</PWTR_LAST_PARTITION_TIMESTAMP>
</row>
<row rownum="2">
<PWTR_PWII_ID>1011</PWTR_PWII_ID>
<PWTR_TABLE_NAME>PW_NTAC_ACTIONS_M</PWTR_TABLE_NAME>
<PWTR_START_TIMESTAMP>2003-08-02 12:00:00.0</PWTR_START_TIMESTAMP>
<PWTR_END_TIMESTAMP>2003-08-23 23:00:00.0</PWTR_END_TIMESTAMP>
<PWTR_LAST_PARTITION_TIMESTAMP>2008-08-01 00:00:00.0</PWTR_LAST_PARTITION_TIMESTAMP>
</row>
<rowset-info>
<affected-rows>2</affected-rows>
<more-rows>false</more-rows> <!-- indication that all the rows where fetched -->
</rowset-info>
<error>
<code>0</code> <!-- rowset level error indication that there ware no errors with the query -->
</error>
</rowset>
<error> <!-- rowsets level error, indication that there were no problems with the database connection -->
<code>0/code>
</error>
</rowsets>
The following example shows a retrieve response to several queries. Note that some queries have encountered an error during execution.
<rowsets>
<rowset id="601">
<row rownum="1">
<PWII_ID>1005</PWII_ID>
<PWII_INSTANCE_NAME>network_instance</PWII_INSTANCE_NAME>
<PWII_TECHNOLOGY>NT</PWII_TECHNOLOGY>
<PWII_SERVER>my_server</PWII_SERVER>
</row>
<rowset-info>
<affected-rows>1</affected-rows>
<more-rows>true</more-rows> <!-- indication that there are more rows to fetch -->
</rowset-info>
<error>
<code>0</code> <!-- rowset level error indication that there ware no errors with the query -->
</error>
</rowset>
<rowset id="602">
<rowset-info>
<affected-rows>1</affected-rows>
<more-rows>false</more-rows>
</rowset-info>
<error>
<code>0</code> <!-- rowset level error indication that there ware no errors with the query -->
</error>
</rowset>
<rowset id="603">
<error>
<code>8200</code> <!-- indication that there was an error performing the query -->
<description>com.precise.shared.retriever.RetrieverJDBCException: ERROR could not execute the sql command in query id : 502 for sql text : SELECT * FROM PS_PWTR_T</description>
<external-code>942</external-code>
<external-description>ORA-00942: table or view does not exist</external-description>
</error>
</rowset>
<error>
<code>0</code>
</error>
</rowsets>
The following example presents an error connecting to the database.
<rowsets>
<error>
<code>9100</code>
<description>ERROR at statement 72000</description>
<external-code>1034</external-code>
<external-description>ORA-01034: ORACLE not available</external-description>
</error>
</rowsets>
Load Request Example
Do NOT enter blanks, nor new lines between the tags.
The following example presents a request to load data.
<rowsets connection-pool="pw"><!-- the connection pool names should always be "pw" -->
<rowset object="PW_NTAC_ACTIONS_T" single-commit="true"> <!--table name and indication to commit only after all rows are loaded into PW -->
<columns> <!-- the list of columns should be kept in the rows creation -->
<column>NTAC_TIMESTAMP</column>
<column>NTAC_ID1</column>
<column>NTAC_CONSUMER_IP</column>
<column>NTAC_REQUESTS_SUM</column>
<column>NTAC_NETWORK_TIME_SUM</column>
<column>NTAC_TIME1_SUM</column>
<column>NTAC_STAT1_SUM</column>
<column>NTAC_STAT2_SUM</column>
<column>NTAC_SOURCE_ID</column>
<column>NTAC_PWII_INSTANCE_ID</column>
<column>NTAC_INSTANCE</column>
<column>NTAC_SERVER</column>
<column>NTAC_INSTANCE_GID</column>
<column>NTAC_SERVER_GID</column>
<column>NTAC_CONSUMER_IP_GID</column>
<column>NTAC_RED_SUM</column>
<column>NTAC_YELLOW_SUM</column>
<column>NTAC_GREEN_SUM</column>
</columns>
<row rownum="1"><![CDATA[2212003-08-2811:30:00.0^21010.1.0.218^1910.1.1.94^17175.000^1621.600^150.000^1514963^1820227220^12SQ^141009^210SAPSQLSRV2^210sapsqlsrv2^0^0^0^130.0^130.0^130.0^]]></row>
<row rownum="2"><![CDATA[2212003-08-2811:30:00.0^21010.1.0.218^1910.1.1.94^17175.000^1621.600^150.000^1514963^1820227220^12SQ^141009^210SAPSQLSRV2^210sapsqlsrv2^0^0^0^130.0^130.0^130.0^]]></row>
</rowset>
</rowsets>
Load Response Examples
The following example presents a response to a successful load request.
<?xml version="1.0" encoding="UTF-8"?>
<rowsets>
<error>
<code>0</code> <!-- indication that there was no error connecting to the database -->
</error>
<rowset object="PW_NTAC_ACTIONS_T">
<error>
<code>0</code> <!-- indication that there was no error loading the data into the table -->
</error>
</rowset>
</rowsets>
The following example presents an error connecting to the database.
<?xml version="1.0" encoding="UTF-8"?>
<rowsets>
<error>
<code>9200</code>
<description>Error connecting to the PW DB</description>
<external-code>12154</external-code>
<external-description>TNS:could not resolve service name</external-description>
</error>
</rowsets>
The following example presents the response to a load request that has encountered an error during execution.
<?xml version="1.0" encoding="UTF-8"?>
<rowsets>
<rowset object="PW_NTAC_ACTIONS_T">
<error>
<code>10200</code>
<description>Error loading table PW_NTAC_ACTIONS_T</description>
<external-code>1</external-code>
<external-description>Unique index violation</external-description>
</error>
</rowset>
<error>
<code>0</code>
</error>
</rowsets>