Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Web CMS (TeamSite)
DAS: making initial inserts non-transactional
marcmundt
When DAS runs an initial deployment for STAGING or a workarea, all of the inserts belong to a single transaction which is committed or rolled-back at the end.
This means that a single file with a bad EA (text which exceeds the column size, for example) will cause the entire deployment to fail. We have a large number of files, so this is costly in terms of time.
For DAS, is there a way to make each insert independent? I'd prefer that the good files be inserted, and the bad files skipped. I can then check the log for bad files that need to be fixed.
I tried setting 'commit-batch-size="1"' to the database section, but this doesn't seem to have any effect.
Marc Mundt
Senior Analyst
Blue Cross Blue Shield Association
Find more posts tagged with
Comments
Migrateduser
Marc, What happens exactly? Do you see no records getting commited at all and the entire transaction getting rolled back? So say you have 5 records to deploy, the first 2 and last 2 are ok but the third is bad. With the commit size set to 1, do you see the first 2 records getting in? Or do none of the records get in.?
It might help to post your log files, config files and some sample records.
Mariam
marcmundt
Mariam-
Exactly. Record #3 (in your example) is bad, causing all 5 records to be rolled-back.
The logs are huge (300 mb), so I'll pick out the important parts and post them later today. In the meantime, I've added the configs below -- let me know if any other configs would be helpful.
-Marc
--------------------------------------------------------
mdc_ddcfg.template
--------------------------------------------------------
<!-- -->
<!-- DD cfg for WIDE tables -->
<!-- -->
<!-- DataDeploy configuration file for TeamSite MetaData -->
<!-- -->
<!-- CUT OUT EXTRANEOUS STANDARD COMMENTS -->
<!-- -->
<data-deploy-configuration>
<data-deploy-elements filepath="e:/interwoven/opendeployng/etc/database.xml" />
<filter name="DASFilter">
<keep>
<or>
<field name="path" match="^.*attachments.*" />
<field name="path" match="^.*templatedata.*data.*" />
</or>
</keep>
</filter>
<client>
<!--###########################################################-->
<!-- This deployment dumps DCR data from a TeamSite -->
<!-- area to a rotated base table -->
<!-- This area can be either a Staging area or Edition area -->
<!--###########################################################-->
<!-- 1. Generating Full Base Area Table(s) -->
<!-- -->
<!-- iwodcmd start <deployment-name> -->
<!-- -k iwdd=basearea -->
<!-- -k mybasearea=<vpath-to-my-area> -->
<!-- -k mybasetable=<base-table-name> -->
<!-- -k mysubpath=<area-relative-path> -->
<deployment name="basearea">
<source>
<!-- Pull data tuples from TeamSite Extended Attributes -->
<teamsite-extended-attributes
options = "wide"
area = "\$mybasearea">
<path name = "\$mysubpath"
visit-directory = "deep" />
</teamsite-extended-attributes>
</source>
<filter use="DASFilter" />
<destinations>
<database use = "teamsitedb"
table = "\$mybasetable">
<select>
<column name="Path" value-from-field="path" />
</select>
<update type="base"
state-field="state">
<column name="iw_state" value-from-field="state" />
<!-- ################# following automatically generated by ddgen.ipl ################# -->
$fieldswap
<!-- ################# preceding automatically generated by ddgen.ipl ################# -->
</update>
<!-- The parameter token in the next line is -->
<!-- subject to parameter substitution. -->
<sql action="create">
CREATE TABLE \$mybasetable (
Path VARCHAR(255) NOT NULL,
iw_state VARCHAR(30),
<!-- ################# following automatically generated by ddgen.ipl ################# -->
$tabledef
<!-- ################# preceding automatically generated by ddgen.ipl ################# -->
CONSTRAINT \$mybasetable^_KEY PRIMARY KEY (Path)
)
</sql>
</database>
</destinations>
</deployment>
<!--###########################################################-->
<!-- This deployment performs a generation of a delta table -->
<!--###########################################################-->
<!-- 2. Generate Incremental Workarea Delta Table(s) -->
<!-- -->
<!-- iwodcmd start <deployment-name> -->
<!-- -k iwdd=deltagen -->
<!-- -k myarea=<vpath-to-my-workarea> -->
<!-- -k mybasearea=<vpath-to-my-staging-area> -->
<!-- -k mytable=<delta-table-name> -->
<!-- -k mybasetable=<base-table-name> -->
<!-- -k mysubpath=<area-relative-path> -->
<deployment name="deltagen">
<source>
<!-- Pull data tuples from TeamSite Extended Attributes -->
<teamsite-extended-attributes
options = "differential,wide"
area = "\$myarea"
base-area = "\$mybasearea"
>
<path name = "\$mysubpath"
visit-directory = "deep" />
</teamsite-extended-attributes>
</source>
<filter use="DASFilter" />
<destinations>
<database use = "teamsitedb"
table = "\$mytable"
clear-table = "yes"
table-view = "no">
<select>
<column name="Path" value-from-field="path" />
</select>
<update type = "delta"
base-table = "\$mybasetable"
state-field = "state">
<column name="iw_state" value-from-field="state" />
<!-- ################# automatically generated by ddgen.ipl ################# -->
$fieldswap
<!-- ################# automatically generated by ddgen.ipl ################# -->
</update>
<!-- The parameter token in the next line is -->
<!-- subject to parameter substitution. -->
<sql action="create">
CREATE TABLE \$mytable (
Path VARCHAR(255) NOT NULL,
iw_state VARCHAR(30),
<!-- ################# following automatically generated by ddgen.ipl ################# -->
$tabledef
<!-- ################# preceding automatically generated by ddgen.ipl ################# -->
CONSTRAINT \$mytable^_KEY PRIMARY KEY (Path)
)
</sql>
</database>
</destinations>
</deployment>
<!--###########################################################-->
<!-- This deployment performs an update of a delta table -->
<!--###########################################################-->
<!-- 3. Updating Incremental Workarea Delta Table(s) -->
<!-- -->
<!-- iwodcmd start <deployment-name> -->
<!-- -k iwdd=deltaupdate -->
<!-- -k myarea=<vpath-to-my-workarea> -->
<!-- -k mybasearea=<vpath-to-my-staging-area> -->
<!-- -k mytable=<delta-table-name> -->
<!-- -k mybasetable=<base-table-name> -->
<!-- -k myfilelist=<filelist-filename> -->
<deployment name="deltaupdate">
<source>
<!-- Pull data tuples from TeamSite Extended Attributes -->
<teamsite-extended-attributes
options = "full,wide"
area = "\$myarea"
>
<path filelist = "\$myfilelist"
visit-directory = "deep"
delete-after-use= "yes" />
</teamsite-extended-attributes>
</source>
<filter use="DASFilter" />
<destinations>
<database use = "teamsitedb"
table = "\$mytable"
clear-table = "no">
<select>
<column name="Path" value-from-field="path" />
</select>
<update type = "delta"
base-table = "\$mybasetable"
state-field = "state">
<column name="iw_state" value-from-field="state" />
<!-- ################# automatically generated by ddgen.ipl ################# -->
$fieldswap
<!-- ################# automatically generated by ddgen.ipl ################# -->
</update>
<!-- The parameter token in the next line is -->
<!-- subject to parameter substitution. -->
<sql action="create">
CREATE TABLE \$mytable (
Path VARCHAR(255) NOT NULL,
iw_state VARCHAR(30),
<!-- ################# following automatically generated by ddgen.ipl ################# -->
$tabledef
<!-- ################# preceding automatically generated by ddgen.ipl ################# -->
CONSTRAINT \$mytable^_KEY PRIMARY KEY (Path)
)
</sql>
</database>
</destinations>
</deployment>
<!--############################################################-->
<!-- This deployment synchronizes the DCR tuple differences -->
<!-- from submit-generated filelist into a database _after_ -->
<!-- the submit event has been executed -->
<!--############################################################-->
<!-- 4. Post-Submitting DCR tuples of successfully submitted -->
<!-- files specified in a submit-generated filelist -->
<!-- -->
<!-- iwodcmd start <deployment-name> -->
<!-- -k iwdd=postsubmit -->
<!-- -k myarea=<vpath-to-my-workarea> -->
<!-- -k mytable=<delta-table-name> -->
<!-- -k mybasetable=<base-table-name> -->
<!-- -k mysubmitlist=<input-file-name> -->
<deployment name="submitlist">
<source>
<!-- Pull data tuples from TeamSite Extended Attributes -->
<teamsite-extended-attributes
options = "full,wide"
area = "\$myarea"
>
<path filelist = "\$mysubmitlist"
format = "submit-status"
visit-directory = "deep"
delete-after-use= "yes" />
</teamsite-extended-attributes>
</source>
<filter use="DASFilter" />
<destinations>
<database use = "teamsitedb"
table = "\$mytable">
<!-- This is a rotated table specification -->
<select>
<column name="Path" value-from-field="path" />
</select>
<update type = "base"
base-table = "\$mybasetable"
state-field = "state">
<!-- ################# following automatically generated by ddgen.ipl ################# -->
$fieldswap
<!-- ################# preceding automatically generated by ddgen.ipl ################# -->
<column name="iw_state" value-from-field="state" />
</update>
<!-- The parameter token in the next line is -->
<!-- subject to parameter substitution. -->
<sql action="create">
CREATE TABLE \$mytable (
Path VARCHAR(255) NOT NULL,
iw_state VARCHAR(30),
<!-- ################# following automatically generated by ddgen.ipl ################# -->
$tabledef
<!-- ################# preceding automatically generated by ddgen.ipl ################# -->
CONSTRAINT \$mytable^_KEY PRIMARY KEY (Path)
)
</sql>
</database>
</destinations>
</deployment>
<!--############################################################-->
<!-- This deployment performs various SQL commands -->
<!-- on the specified database -->
<!--############################################################-->
<!-- 5. Various SQL stuff (optional) -->
<!-- -->
<!-- Syntax depends on action desired -->
<!-- Eg. For user-action 'showview', -->
<!-- -->
<!-- iwodcmd start <deployment-name> -->
<!-- -k iwdd=dosql -->
<!-- -k mybasetable=<base-table-name> -->
<!-- -k mytable=<delta-table-name> -->
<!-- -k iwdd-op=do-sql user-op=showview -->
<!-- -->
<deployment name="dosql">
<filter use="DASFilter" />
<destinations>
<database use = "teamsitedb"
table = "not-used-by-this-dosql-deployment">
<!-- -->
<!-- Show the combined view of delta and -->
<!-- base tables -->
<!-- -->
<sql user-action="showview" type="query">
SELECT *
FROM \$mybasetable
WHERE NOT EXISTS (SELECT * FROM \$mytable
WHERE \$mytable.Path = \$mybasetable.Path )
UNION ALL
SELECT *
FROM \$mytable
WHERE \$mytable.State != 'NotPresent'
</sql>
<!-- -->
<!-- Show the files having DCR's -->
<!-- -->
<sql user-action="showpaths" type="query">
SELECT Path FROM \$mytable
</sql>
<sql user-action="drop" type="update">
DROP TABLE \$mytable
</sql>
</database>
</destinations>
</deployment>
</client>
</data-deploy-configuration>
--------------------------------------------------------
database.xml
--------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!--
This is a sample file which defines all the database related
attributes. It is included, by default, in all the example
DataDeploy configuration template (*.template.example) files
under the $odbasehome/ddtemplate directory. This file is included in the
DD config template files using the <data-deploy-elements>
tag.
The user can set up all database elements in this file and simply
refer them in the DD configuration files using the "use"
attribute. Attributes specified for the database element in this
file can be overridden by specifying the same attribute value
in the DD config file. See the DataDeploy Administration manual for more
information.
-->
<data-deploy-elements>
<database name="teamsitedb"
db="chgsqldev5.bcbsa.com?database=tsuat"
user="tsuser"
password="tsuser"
vendor = "microsoft-inetuna"
commit-batch-size="1"
max-id-length = "128" />
</data-deploy-elements>
Marc Mundt
Senior Analyst
Blue Cross Blue Shield Association
