Postgres useful features

To copy values from one db to another db

pg_dump -h <SRC_HOSTNAME> -U <user> -t table_to_copy source_db | psql -h <DST_HOSTNAME> -U <user> -d target_db

My experience in DBMS structure creation PostgreSQL+Liquibase (constantly updated)

1. Create auto-incremental id field. It is suitable for inserting a new values.

 <addAutoIncrement tableName="TABLENAME" columnName="ID"/>

2. Сonsider to create field as NOT NULL if applicable

Usage of external DB objects- table/query (not Documentum object)

External DB objects - table/query (not Documentum object) is accessable only for Super User permission user. In general you get it, when query to DB by docbase owner alias dm_dbo (select * from  dm_dmo.table_name).

For using an object in external DB - this object should be registered.

1) Execute query "register"

REGISTER TABLE DOCUM.OBJECT_NAME (ANYFIELD CHAR(8))

/* where DOCUM - docbase owner
              OBJECT_NAME - table/query
*/

2) Grant permission for users. Execute query "update"

update dm_registered object 

set owner_table_permit=15
set group_table_permit=15
set world_table_permit=15

where object_name ='OBJECT_NAME '

A great artice about Registered Tables you may find by the link
http://www.bluefishgroup.com/1999/documentum-registered-tables/

The most disappointing things in DCTM 6.7 in my way (Some point may be my fault due to incomplete experience)

FormsBuilder -

-  Child forms (like Query Simulation, adaptor designer) are not expandable. It is very unconvenient to design long query in small window.

-  I can't populate valeus from one control to another. F.e. from some tables to summary table
-  I can't calculate cells data of table - like sum, avg.

(100% bug) - adaptor stop executes (data source for Tables) from time to time - have to set to another adaptor in DataSource field and then turn back to hanged one

Useful DQL / API

How to define a names of TaskSpace main and child applications

select * from dmc_taskspace_app

Clear repository cache

flush,c,querycache
flush,c,persistentcache
flush,c,persistentobjcache
flush,c,ddcache,<PARTICULAR_OBJECT_TYPE>
flushcache,c

HowTo reset a Repository owner system user

1. Stop the Documentum Content Server (Repository) and DocBrocker
2. Go to $DM_HOME/dba/config/<repository name>/ 
3. Backup dbpasswd.txt 
4. Run SQL command in DBMS (change the password of Repository Owner)
 ALTER USER <Repository Owner Username> IDENTIFIED BY <New Password>; 
5. Replace the new password as plain text (<New Password>) in dbpasswd.txt  and save it
6. Go to $DM_HOME/product/6.7/bin/ and run the following: 
dm_encrypt_password -docbase <docbase name> -rdbms -encrypt <database password> 
7. Run Repository and DocBrocker start scripts. 

Troubleshooting. Checklist.

Troubleshooting

1)     Analize all DCTM log files (DocBrocker / Docbase / WebApp)

2)     Check RAM memory (command: topas) and storage free space, free inode on server (commands df, du)

3)     Restart App (Save all log and stop app / clear cache / start app )

4)     Check system jobs log and ensure that jobs are active and there are no critical warnings in logs (especially DMDbWarning – assess a free space and extents of DB / consider index creating)

1. DMClean job -  removes deleted and orphaned objects from the Docbase.

2. DMFileScan job removes deleted and orphaned content files from the file system.

3. Log Purge job removes server and session logs from the Docbase and file system.

4. Consistency Checker job runs 77 referential integrity checks on the Docbase.

5 Update Stats job updates database table statistics and repairs fragmented tables

6. QueueMgt job deletes dequeued Inbox items from the Docbase.

7. State of the Docbase job produces a report of the repository environment and statistics about object types.

5)     Check quantity of dm_audittrails (DCTM user activity log records), dmi_queue_item (BP tasks records) – delete unneeded records if too many quantity

*********************************************************************************

ACTIONS AFTER UPDATES and RECONFIGURATION

If you updated something objects in repository, please perform cleaning cache and reinitilisation of Repository

publish_dd,c (flush entire cache)

publish_dd,c,,dm_document (flush cache for dm_document type, there is extra comma after c for the locale)

reinit,c

If services DCTM hasn’t been started – check conf env

/etc/services  - docbrocker and repository ports shout be defined

/ect/hosts – host and IP address resolution shout be defined