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