DQL: Hidden DQL Tricks and Behaviors

Here are a few DQL tricks and behaviors that are not always obvious.

  1. Using r_object_id in the select statement changes the resultset.

The following DQL for any r_folder_path will return multiple rows for the same object.

select object_name, r_folder_path from dm_folder where any r_folder_path is not nullstring

By adding r_object_id to the select statement, the resultset will contain only distinct r_object_id’s. In other words, will only return a single row for each object.

select object_name, r_folder_path from dm_folder where any r_folder_path is not nullstring

2. When using joins with recurring attributes, you need to use the DQL hint “enable (ROW_BASED)”

 

Advertisements

AWS: How to setup a simple website

  1. In order to setup a simple website on Amazon AWS, you will need to first acquire a domain. Although not technically necessarily in order to view it, it looks better than the default url that you get from amazon. I used Namecheap to register my domain, which was a little cheaper than GoDaddy, one of the major domain name registrars out there.
  2. The next step is to register for an AWS account, if you haven’t done that already. Go to AWS to register/create an account with AWS.
  3. Create a storage bucket using Amazon S3, using your registered domain name as the bucket name.
  4. Upload the files related to your website to your newly created bucket. Make sure to include a file called index.html at the root of your bucket, because by default that is where AWS expects it to be.
  5. Use Amazon Route 53 to change the DNS Name Servers from Namecheap or GoDaddy to AWS.
  6. Create a “Hosted Zone” for your domain.
  7. Click on the new “Hosted Zone” you just created, and then click on “Create a Record Set”
  8. Use the following values to create a hosted zone (These are not the only possibilities)

Name: <your domain name>

Type: A-IPv4 Address

Alias: Yes

Alias Target: <choose the target for your domain from the drop down>

Routing Policy: Simple

Click on Save Record Set button at the bottom.

9. Copy the NameServers from Amazon to your NameCheap or GoDaddy domain configuration.

The Name servers that I was given by Amazon were:

ns-286.awsdns-35.com
ns-771.awsdns-32.net
ns-1311.awsdns-35.org
ns-1781.awsdns-30.co.uk

 

AWS: A Quick Guide to some main AWS Services (Not Comprehensive)

I would like to record here the list of AWS Services. I will attempt to get this list as comprehensive as I can, but you have to start somewhere ūüôā

Service Name Link to Service Console Service Description
AWS https://aws.amazon.com/
S3 https://console.aws.amazon.com/s3  Simple object storageDurability, Scalability
Route 53 https://console.aws.amazon.com/route53
EC2  https://console.aws.amazon.com/ec2/v2
RDS  https://console.aws.amazon.com/rds
CodeCommit  https://console.aws.amazon.com/codecommit
CodeDeploy  https://console.aws.amazon.com/codedeploy
Elastic Beanstalk  https://console.aws.amazon.com/elasticbeanstalk

DQL: How to find Documentum folders with 0 (zero) documents in any subfolders

I was recently asked to find all custom folders in a cabinet which may have other nested folders, but no documents. In other words, folders can have other folders, but not documents.

In order to find empty folders, with no documents or sub-folders, is straightforward:

select * from dm_folder where i_reference_cnt=0

But what we are looking for are folders which may contain other folders, but none of the sub-folders should have a document.

I came up with the following query. Keep in mind that this query can be improved upon, for example by using joins. I only need to run it once so I have no need for performance based improvements at this time.

select  

   r_object_id, object_name, r_folder_path, r_creation_date, r_modify_date

from dm_folder //or custom dm_folder subtype

where r_object_id not in   //Returns all folders which are not in the list of ancestor folders (folder hierarchy) as returned below

(

    select distinct (i_ancestor_id) from dm_folder where r_object_id in   //Returns all ancestor folders in the hierarchy of the parent folders below

               (

        select distinct(i_folder_id) from dm_document WHERE <your custom where clause>   //Returns all parent folders which contain documents 

               )

)

and any r_folder_path is not nullstring

and folder('/Background Investigation', descend)

enable (ROW_BASED)  //Needed when querying repeating attributes such as r_folder_path above

 

 

Documentum: Software to Download Documents and Metadata

I have written a software in Java using DFC. This software performs the following tasks:

  1. Performs export or import of documentum data
  2. Download all documents given a folder/cabinet in Documentum
  3. Download all document metadata, including custom metadata without having to specify the specific fields
  4. All the metadata is saved in an xml file
  5. The field metadata, such as the attribute data type, attribute name and value are all saved in the xml to make importing into any other content management system easy.
  6. All the permissions levels for each document are saved to the xml
  7. The complete folder hierarchy under the given root hierarchy is exported
  8. Any custom folder metadata is also saved in the xml

DQL: Listing documents with their Folderpaths

Here’s a DQL to get a listing of Documents along with their folderpaths in the repository:

select fld.r_folder_path, 
doc.r_object_id, 
doc.object_name, doc.r_content_size, doc.acl_name, doc.r_creation_date, doc.r_modify_date, doc.r_creator_name, doc.title, doc.subject, doc.keywords 
from dm_document doc, dm_folder fld
where 
folder('/<CABINET_NAME>', descend) and 
fld.r_object_id = doc.i_folder_id 
and any r_folder_path is not nullstring 
enable (ROW_BASED)

DQL: Total Document Content Size By Cabinet

Here is a DQL statement to find the total document size by cabinet. You may be aware that dm_document has two different attributes for content size:

  • r_content_size,
  • r_full_content_size

The first one, r_content_size, can only store sizes upto 2 GB. r_full_content_size stores the correct size if the content is larger than 2 GB. Weird. I know.

select cab.object_name, sum(doc.r_full_content_size) 
from dm_document doc, dm_cabinet cab
where doc.i_cabinet_id = cab.r_object_id
group by cab.object_name