Purpose:
- Provide a way to resubmit a large number of documents.
- The sample script provided here, will query the Revisions table for content matching a specific criteria, and generate a command file that can be used by IdcCommand to resubmit content for conversion.
- The SQL script will not change any data in the database. It will only be used to query a table, and generate a command file that will be passed to IdcCommand.
- This is an alternate approach from “How to Build IdcCommand Files and Batch Files Using SQL Developer” (Document 1399562.1)
Requirements:
- SQL Plus and access to the WebCenter Content schema
- IdcCommand
Configuration:
Properly update the “WHERE” clause of the query to match the content desired to be resubmitted.
In the sample code below, the script will generate a command file that will resubmit all files where dMessage is set to ‘!csIndexerFailure’ and dProcessingState is set to ‘M’.
WHERE dMessage=’!csIndexerFailure’
AND dProcessingState=’M’
Also change the value of ROWNUM, to limit the content that will be resubmitted.
Instructions:
1. Create a new file GenDocList.sql, and copy and paste the SQL script into the file. The SQL script is also attached to this note.
2. Connect to the database as the WCC schema owner.
Make sure to use sqlplus to connect, and no other tool. The script contains sqlplus specific commands to format the results, which would not work in other tools
3. Run the script:
SQL> @GenDocList.sql
4. Exit from sqlplus
5. The script has generated a new file “resubmit.hda” in the current directory. Check the generated file and verify that it contains valid IdcCommand commands.
6. Pass the “resubmit.hda” command file to the IdcCommand tool:
IdcCommand -f resubmit.hda -u sysadmin -l idccommand.log -c auto
Note: The IdcCommand tool is located in ${domain.home}/ucm/cs/bin
Important Items to Note:
- Test the script on a test system first, before running it on the production system. Directly accessing the database schema is not officially supported, so make sure to thoroughly test this procedure on a test system first.
- Before running on production for the first time, change ROWNUM < 1000 to ROWNUM < 3 in GenDocList.sql. This will resubmit 2 documents during the first run.
- If the first run on production went fine, change the query back to ROWNUM < 1000 to resubmit a batch of 1000 documents.
- Before resubmitting the next 1000 documents, wait for the initial documents to have been processed successfully.
Sample Code:
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET TRIMSPOOL ON
SET ECHO OFF
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET RECSEP OFF
COLUMN “col1” FORMAT A80
COLUMN “col2” FORMAT A80
COLUMN “col3” FORMAT A80
COLUMN “col4” FORMAT A80
COLUMN “col5” FORMAT A80
COLUMN “col6” FORMAT A80
SPOOL resubmit.hda
PROMPT <?hda version=”5.1.1 (build011203)” jcharset=Cp1252 encoding=iso-8859-1?>
SELECT ‘@Properties LocalData’ “col1”,
‘IdcService=RESUBMIT_FOR_CONVERSION’ “col2”,
‘dID=’||dId “col3”,
‘AlwaysResubmit=1’ “col4”,
‘@end’ “col5”,
‘<<EOD>>’ “col6”
FROM Revisions
WHERE dMessage=’!csIndexerFailure’
AND dProcessingState=’M’
AND ROWNUM < 1000;
SPOOL OFF
Sample Output:
Sample output calling GenDocList.sql in sqlplus:
oracle:ora112:~$ sqlplus dev_ocs/manager1
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 3 15:39:50 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @GenDocList.sql
<?hda version=”5.1.1 (build011203)” jcharset=Cp1252 encoding=iso-8859-1?>
@Properties LocalData
IdcService=RESUBMIT_FOR_CONVERSION
dID=1
AlwaysResubmit=1
@end
<<EOD>>
@Properties LocalData
IdcService=RESUBMIT_FOR_CONVERSION
dID=4
AlwaysResubmit=1
@end
<<EOD>>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Sample content of resubmit.hda which gets generated by the sqlplus script:
oracle:ora112:~$ cat resubmit.hda
<?hda version=”5.1.1 (build011203)” jcharset=Cp1252 encoding=iso-8859-1?>
@Properties LocalData
IdcService=RESUBMIT_FOR_CONVERSION
dID=1
AlwaysResubmit=1
@end
<<EOD>>
@Properties LocalData
IdcService=RESUBMIT_FOR_CONVERSION
dID=4
AlwaysResubmit=1
@end
<<EOD>>
Sample output calling resubmit.hda with IdcCommand:
oracle:ucm1115:~$ IdcCommand -f resubmit.hda -u sysadmin -l idccommand.log -c auto
FileSystemFileStore:1.0.0.0 started.
Content Management System Server. Version 11gR1-11.1.1.5.0-idcprod1-110413T184243
Copyright (C) 1996-2011, Oracle and/or its affiliates. All rights reserved.
8/3/12: Success executing service RESUBMIT_FOR_CONVERSION.
8/3/12: Success executing service RESUBMIT_FOR_CONVERSION.
Is your organization struggling to manage your current WebCenter environments? Contact Inspired ECM and inquire about our Managed Services offerings.
