Feeds:
Posts
Comments

Posts Tagged ‘DQL’

There are a lot of discussions on various forums/threads regarding the Content Server High Availability Environment. But I have not come across any documentation providing the precise steps to implement it. This is an attempt to list the steps that I have been using for the implementation. It’s basically an integration of bits and pieces from various sources combined along with my experience in order to put a clear picture. These steps may not be exactly as suggested and supported by EMC.
The procedure listed below is specific to the Content Server Linux Oracle 6.5 SP2 version.

Prerequisites:

  • As it’s a HA environment, the content files should be present in a File Store that is shared across the Content Servers.
  • The Installation Owner and the Installation Path should be same on each Content Server.
  • Availability of a Database Server and its connectivity through each Content Server Host using Oracle Client.
  • Update the /etc/hosts file of the Content Server Hosts so that they can resolve their IP addresses and hostnames.

Once the above prerequisites are satisfied, the below steps can be used to establish the HA environment.

  • Install the Primary Content Server as per the standard procedure mentioned in Installation Guide.
  • Install the docbroker on the Secondary CS host.
  • Create a Secondary Server Config object using Documentum Administrator.
  • Copy server.ini, aek.key, dbpasswd.txt, dm_start_docbase and dm_shutdown_docbase from Primary CS Host to Secondary CS Host.
  • Update the server.ini on both the Hosts so that the docbrokers project to each other.
    server.ini on the Primary CS:

    [DOCBROKER_PROJECTION_TARGET]
    host = <primary docbroker>
    port = 1489
    [DOCBROKER_PROJECTION_TARGET_1]
    host = <secondary docbroker>
    port = 1489

    server.ini on the Secondary CS:

    [DOCBROKER_PROJECTION_TARGET]
    host = <secondary docbroker>
    port = 1489
    [DOCBROKER_PROJECTION_TARGET_1]
    host = <primary docbroker>
    port = 1489
  • Update the dm_shutdown_docbase as follows: 
      The line preceding to “shutdown,c,T,T” should be updated as follows:

    • Original:
      ./iapi <docbase> -U$DM_DMADMIN_USER -P -e << EOF 
    • Updated:
      ./iapi <docbase>.<secondary server config object name> -U$DM_DMADMIN_USER  -P -e << EOF
  • Update the dfc.properties of the Web Application as well as both the Content Server Hosts so that they point to both the docbrokers.
  • Create an ACS Config object using the below command:
      dmbasic -f dm_acs_install.ebs -e Install -- <docbase name> <installation owner> <password> <new acs config name> <secondary server config name> <JMS Port> <JMS protocol> <output log location>
  • Update the acs.properties accordingly.
  • Once the above steps are complete, shutdown the Primary CS and test the Secondary CS for expected functionality. There may be minor issues which may need few basic fixes. Such fixes include creation of sysadmin directory at $DOCUMENTUM/dba/logs/<repository id>/ in order to fix issues relating to jobs running on Secondary CS.

Start the Primary CS and now both the CS should be in HA.

Advertisements

Read Full Post »

I came up with this junk when I was trying to clean my repository by finding a DQL that I could use to delete more that 150,000 junk user objects from my repository. Ignore that statement as it is again a junk. What I came up is not very new. It’s just the weirdness in the behavior of the DQLs. Is this weirdness only with the repeating attributes? I would like the DQLs to do much of the talking as I feel they can explain their pain much better. So here it goes:

A1)SELECT COUNT(DISTINCT user_name) FROM dm_user >> 168241

A2)SELECT COUNT(DISTINCT users_names) FROM dm_group >> 2916

A3)SELECT COUNT(DISTINCT user_name) FROM dm_user
WHERE user_name NOT IN
(SELECT DISTINCT users_names from dm_group) >> 0

Isn’t that weird?

B1)SELECT COUNT(DISTINCT user_name) FROM dm_user, dm_group
WHERE ANY dm_group.users_names = dm_user.user_name >> 2915

A2 VS B1: again weird!

C1)SELECT COUNT(DISTINCT user_name) FROM dm_user
WHERE user_name NOT IN
(SELECT DISTINCT user_name FROM dm_user, dm_group
WHERE ANY dm_group.users_names = dm_user.user_name) >> 165326

(A2, A3) VS (B1, C1): any explanation?
But that’s a relief indeed. That’s the result I needed. A1 – B1 = C1. Under the current circumstances, that’s encouraging enough to get into some more meddling.

D1)SELECT COUNT(users_names) FROM dm_group
WHERE ANY users_names NOT IN
(SELECT user_name FROM dm_user) >> 5

What the hell!!!

D2)SELECT users_names FROM dm_group
WHERE ANY users_names NOT IN
(SELECT user_name FROM dm_user)
>> TestUser1 test3 test2 orts test1x

hmmmm…

D3)SELECT COUNT(*) FROM dm_user
WHERE user_name IN
(SELECT users_names FROM dm_group
WHERE ANY users_names NOT IN
(SELECT user_name FROM dm_user))>> 4

This is insane. Isn’t D3 a contradiction in itself? Can I challenge EMC to explain that? Can someone come to my rescue?

D4)SELECT user_name FROM dm_user
WHERE user_name IN
(SELECT users_names FROM dm_group
WHERE ANY users_names NOT IN
(SELECT user_name FROM dm_user))
>> TestUser1 test3 test2 orts

Here is something that helps; but it doesn’t explain the insanity though.

ConsistencyChecker Report:

Checking for users belonging to groups not in dm_user
WARNING CC-0002: User ‘test1x’ is referenced in dm_group with id ‘12000d808004a500’ but does not have a valid dm_user object
Rows Returned: 1

Summery:
Weirdness No. 1:     (A1, A2, A3)
Weirdness No. 2:     A2 VS B1: Explained by the ConsistencyChecker Report.
Weirdness No. 3:     (A2, A3) VS (B1, C1)
Weirdness No. 4:     D3: The Contradiction in itself.

I could get an explanation only for Weirdness No. 2 which I guess is not a weirdness at all. I hope someone reading this post would try explaining the other three. 1 & 2 I guess are contributed by repeating attributes but remains unexplained anyway. 4, the D3 is an absolute marvel. Is there a bug in the way DQL works?
C1 is the undisputed winner as it provides the expected result.

Read Full Post »

“Which are the repeating attribures in my_document?” That question was put by Gaurav as he needed to validate them with Captiva. He was finding it too tedious to validate the attributes by checking the definition of each type in the repository. He was interested in a single DQL query which could make his job easy.

My first reaction to his query was: “You are expecting too much!”. Life can’t be that easy. You can not escape from every complexity. But we can indeed try to make them simple. Can’t we?
So I looked into dmi_dd_type_info, dmi_dd_attr_info and finally in dm_type I found the attributes that I could use. After a few iterations I came up with the following DQL Query:

SELECT name, attr_name, attr_repeating
FROM dm_type
WHERE name = 'my_document' AND attr_repeating = 1
ENABLE(ROW_BASED)

This query is again a good example of the DQL Hint ROW_BASED. With this Hint we get only those results where attr_repeating = 1. That statement may have sounded dumb had attr_repeating been a single valued attribute. But it is repeating and there are some weirdness with the result if we are checking for the value of a repeating attribute in the WHERE clause without using that hint.

SELECT name, attr_name, attr_repeating
FROM dm_type
WHERE name = 'my_document' AND ANY attr_repeating = 1

The result of the first query brought a smile to Gaurav’s face; but his expectations had increased. He was interested in only those repeating attributes that were not inherited from the parent type. We found a simple way to do that.

SELECT name, attr_name, attr_repeating
FROM dm_type
WHERE name = 'my_document' AND attr_repeating = 1 AND attr_name
NOT IN (SELECT attr_name FROM dm_type WHERE name = 'dm_document')
ENABLE(ROW_BASED)

We came up with one more modification and our final query looked like:

SELECT name, attr_name, attr_repeating
FROM dm_type
WHERE name = 'my_document' AND attr_repeating = 1 AND attr_name
NOT IN (SELECT attr_name
	FROM dm_type
	WHERE name =
		(SELECT super_name
		FROM dm_type
		WHERE name = 'my_document')
	)
ENABLE(ROW_BASED)

Now that has brought a smile to my face and I hope that Gaurav doesn’t come up with more expectations.

Read Full Post »

This post is nothing but few observations that I made in my Documentum repository. Some of you may find it obvious but some others may find it interesting. The observations are specific to a particular repository. These results had messed up my mind and I expect the same for at least a few of you. Please don’t make any conclusion out of this post. Any comments from the readers are welcome.

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

SELECT COUNT(*) FROM dm_user
Result  >>  435820

SELECT COUNT(*) FROM dm_group
Result  >>  267375

SELECT COUNT(*) FROM dm_user WHERE r_is_group = true
Result  >>  263559

SELECT COUNT(*) FROM dm_user WHERE r_is_group = false
Result  >>  172261

SELECT COUNT(*) FROM dm_group
WHERE group_name
IN (SELECT user_name FROM dm_user WHERE r_is_group = true)

Result  >>  263559

SELECT COUNT(*) FROM dm_group
WHERE group_name
NOT IN (SELECT user_name FROM dm_user WHERE r_is_group = true)

Result  >>  3816

SELECT COUNT(*) FROM dm_group
WHERE r_object_id
IN (SELECT r_object_id FROM dm_user WHERE r_is_group = true)

Result  >>  0

SELECT COUNT(*) FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name

Result  >>  465587

SELECT dm_user.user_name,dm_group.group_name
FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name

Total Results  >>  465587

SELECT DISTINCT dm_user.user_name, dm_group.group_name
FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name

Total Results  >>  198518

SELECT COUNT(*), dm_group.group_name
FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name
GROUP BY dm_group.group_name
HAVING count(*) > 1

Total Results  >>  38608

SELECT group_name, COUNT(*) FROM dm_group
GROUP BY group_name HAVING COUNT(*) > 1

Total Results  >>  38608

SELECT user_name, COUNT(*) FROM dm_user
GROUP BY user_name HAVING COUNT(*) > 1

Total Results  >>  39468

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

SELECT COUNT(DISTINCT user_name) FROM dm_user
Result  >>  366103

SELECT COUNT(DISTINCT group_name) FROM dm_group
Result  >>  198518

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

SELECT COUNT(DISTINCT user_name) FROM dm_user WHERE r_is_group = true
Result  >>  195107

SELECT COUNT(DISTINCT user_name) FROM dm_user WHERE r_is_group = false
Result  >>  170996

SELECT COUNT(DISTINCT group_name)
FROM dm_group
WHERE group_name
IN (SELECT user_name FROM dm_user WHERE r_is_group = true)

Result  >>  195107

SELECT COUNT(DISTINCT group_name)
FROM dm_group
WHERE group_name
NOT IN (SELECT user_name FROM dm_user WHERE r_is_group = true)

Result  >>  3411

SELECT DISTINCT dm_user.user_name, dm_group.group_name
FROM dm_group, dm_user
WHERE dm_group.group_name = dm_user.user_name

Total Results  >>  198518

SELECT DISTINCT dm_user.user_name, dm_group.group_name
FROM dm_group, dm_user
WHERE dm_user.r_is_group = true and dm_group.group_name = dm_user.user_name

Total Results  >>  195107

SELECT DISTINCT dm_user.user_name, dm_group.group_name
FROM dm_group, dm_user
WHERE dm_user.r_is_group = false and dm_group.group_name = dm_user.user_name

Total Results  >>  3411

SELECT COUNT(DISTINCT group_name)
FROM dm_group
WHERE group_name
IN (SELECT user_name FROM dm_user WHERE r_is_group = false)

Result  >>  3411

SELECT COUNT(DISTINCT group_name)
FROM dm_group
WHERE group_name
IN (SELECT user_name FROM dm_user)

Result  >>  198518

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

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

It’s just an observation and I don’t have much to comment.

Read Full Post »

Workflow represents a Business Process.

A Workflow Defination consists of multiple Activities which can be Manual or Automatic. Manual Activities are performed by a Performer or a User whereas an Auto-Activity is generally performed by a program on behalf of a user (using a User’s session). The program used for Auto-Activity has to follow certain guidelines. The class should implement IDmMethod or WorkflowMethod and accordingly it should implement execute or doTask from the corresponding interface. This program is configured as a workflow method, an instance of dm_method with its a_special_app property set to ‘Workflow’. It can be created using DAB or DA + DQL. The Manual activities are made available to the Performers in their inbox as task.

The activities are linked through flows. Flows have Packages associated with them. It’s mandatory for Flows to have at least one associated Package. The ending flow is an exception which doesn’t have any associated package. A Package specifies an object type whose objects can be attached in the package as attachments. A flow can be a Normal Flow or a Reject Flow. A Reject flow is represented by a red line in the Workflow Template. Using a Reject Flow in a Workflow Template automatically generates a Reject button in an inbox task.

Object Types:
The workflow template/definition                        : dm_process
The constituting activities                                    : dm_activity
The running instance of workflow                        : dm_workflow
The running instance of activity                           : dmi_workitem
The package associated with a workflow            : dmi_package
The representation of manual activity in inbox    : dmi_queue_item

Object Relationship:
WorkflowObjectModel1

A document attached in a Workflow:
A document is actually attached to a package which in turn is associated with a flow in a workflow. As seen in the Object Relation diagram above:

 

dm_workflow dmi_queue_item dmi_package dm_sysobject
(workflow) (inbox) (package) (document)
r_object_id = router_id = r_workflow_id
r_component_id(Rep) = r_object_id

Keeping in mind the above relation, the below mentioned DQL query can be used to find out the documents present as workflow attachment in a user’s inbox.

SELECT DOC.r_object_id, DOC.object_name
FROM dm_document DOC, dmi_package PACKAGE, dmi_queue_item INBOX
WHERE INBOX.name = 'Uttkarsh'
AND INBOX.router_id = PACKAGE.r_workflow_id
AND any PACKAGE.r_component_id = DOC.r_object_id
AND INBOX.delete_flag = 0

Vice-Versa if the document is present as a workflow attachment and its properties are known, the following DQL query can be used to find the User in whose inbox it is present.

SELECT name, task_name
FROM dmi_queue_item INBOX, dmi_package PACKAGE, dm_document DOC
WHERE DOC.object_name = 'queries.txt'
AND any PACKAGE.r_component_id = DOC.r_object_id
AND INBOX.router_id = PACKAGE.r_workflow_id
AND INBOX.delete_flag = 0

In addition to above, an Administrative Method GET_INBOX can also be used to get the details of task in a user’s inbox.

EXECUTE GET_INBOX with name = 'Uttkarsh'

Guess that’s enough for this post. Hope you enjoyed reading it. 🙂

Read Full Post »

Few days back I had read the chapter on Aliases. Since then I was eager to play with them. Ultimately I got my game plan ready. It was not a full-fledged plan which could give me a result but it was good enough for an initiative. I like flexible plans so that I can make changes as and when I feel, according to my comfort. Okay, lets stop the gossip and let the game start.

Setting up the field:

I will be using ACL Template to play with Aliases. As I am using ACL Template, I will also be requiring a set of users and objects.

As a first step I use the following DQL query to create a user.

create dm_user object
set home_docbase = 'ASSAPArchive',
set user_os_domain = 'infch02088',
set user_name = 'TestUser1',
set user_os_nam e= 'TestUser1',
set user_source = 'inline password',
set user_password = 'TestUser1'

Similar DQL queries were used to create four more users TestUser2, TestUser3, TestUser4 and TestUser5.

I like users to be part of a group.

CREATE GROUP TestUserGroup
WITH MEMBERS
(SELECT user_name
FROM dm_user
WHERE user_name like 'TestUser%')

No need to worry; there are no additional users with similar name in my repository. Now I have a group named TestUserGroup and it has five members named TestUser1,…..TestUser5.

Till here it was pretty easy. Isn’t it? But the game has not started yet. I am just arranging the players at their respective positions.

Before I create the ACL Template, I will need the Alias Set with the Alias that has to be used in the ACL Template.

CREATE dm_alias_set OBJECT
SET object_name = 'TestUserAlias';
UPDATE dm_alias_set OBJECTS
APPEND alias_name = 'TestUser',
APPEND alias_value = 'TestUser1',
APPEND alias_category = 1,
APPEND alias_usr_category = 1,
APPEND alias_description = 'Testing alias in ACL Template'
WHERE object_name = 'TestUserAlias';

Now I can go ahead with the creation of ACL Template. ACL Template is not same as ACL. It is actually a kind of ACL where aliases are used in place of users/groups. When such an ACL is applied to an object at runtime, the content server resolves the alias and creates a custom ACL with the resolved users/groups and the permissions in the ACL Template. ACL Template can be recognized as objects of dm_acl with acl_class = 1.

Below is the screen shot for the ACL Template created through Application Builder.newacltemplate_1

The field is set now.

Users : TestUser1, TestUser2, TestUser3, TestUser4, TestUser5.

Group : TestUserGroup

Alias Set Name : TestUserAlias

Alias Name : TestUser

ACL Template : TestACLTemplate

The Game Begins:

Let me go ahead with objects (of type dm_document) creation and apply the Template ACL to them.

CREATE dm_document object
SET object_name = 'TestObject1',
SET acl_name = 'TestACLTemplate'
SET acl_domain = 'ASSAPArchive'
LINK '/dmadmin'

FOUL!!!!!!!!!!!

[DM_QUERY_F_UP_SAVE]fatal:  “UPDATE:  An error has occurred during a save operation.”

[DM_POLICY_E_AS_NO_ALIAS_SET_USER]error:  “No default alias set found for user dmadmin. The following alias sets were searched: sessionconfig, user, user’s default group, server config, and policy (if applicable)”

Let me associate the Alias Set with the user, else there is no way the Alias can be resolved.

UPDATE dm_user OBJECT
SET alias_set_id =
(SELECT r_object_id
FROM dm_alias_set
WHERE object_name = 'TestUserAlias')
WHERE user_name = 'dmadmin'

The Alias Set is associated with the user. Once again I use the previously used DQL query for creating a dm_document object and applying the ACL Template to it. This time I was successful.

Lets check the permissions in the property page of the newly created object. A new custom ACL named dm_450003f080001517_80001100 is created and assigned to the object. It should be noted that the r_object_id of the ACL Template is 450003f080001517. Below is the screen shot of the property page. The new ACL has the resolved user name from the Alias Set.

testuser1permission_11

Second half:

The first part of the game is over. Now I need to change the user in the Alias set and check whether the change is reflected in the permissions of the new object.

UPDATE dm_alias_set OBJECT
SET alias_value[0] = 'TestUser2'
WHERE object_name = 'TestUserAlias'

The change in Alias Set is reflected in the object’s permission. So, a successful implementation of Alias Set is achieved. But a single Alias Set is not of much help in a practical scenario. Let me create one more Alias Set.

CREATE dm_alias_set OBJECT
SET object_name = 'TestUserAlias1'
APPEND alias_name = 'TestUser',
APPEND alias_value = 'TestUser1',
APPEND alias_category = 1,
APPEND alias_usr_category = 1,
APPEND alias_description = 'Testing alias in ACL Template'

The newly created Alias Set is now applied to the user dmadmin. Next time I created an object named TestObject2 and applied the ACL Template to it; the resolved user was as per the new Alias Set. Now I have two objects on which I had applied the same ACL Template and both have different users in their custom permission sets.

Analysis:

This situation is achieved because the Alias Set associated with dmadmin was changed before applying the ACL Template to the new object. Am I going to change the Alias set of dmadmin every time I apply the ACL Template to an object? That doesn’t appear to be a good idea. Further If due to some reason I need to reassign the ACL Template to the older object I may be into serious trouble.

Result:

The Alias Set was created and implemented along with the ACL Template. But the approach was not satisfactory for the practical purposes. A new plan has to be devised. Let me take a break now. I will be back with the new plan soon.

Related Articles:

Read Full Post »

Where are the documents (contents) stored in repository? Is it possible to find the documents by navigating to them in the file system? The question has been bothering me for quiet some time. I have heard lot many answers regarding this but I was never convinced. Till few weeks ago, in my earlier organization I was loaded with so much of work that I could never explore it myself.

Couple of weeks ago in an interview one of my colleagues asked the candidate about dmr_content. Later when I asked him he told me that the contents of renditions are stored as dmr_content. In an another incident that happened yesterday another colleague asked me about dmr_content while I was making a presentation on Object and Types. I recalled that a friend had told me once that the content of a deleted document can be retrieved using dmr_content. She was not satisfied with the response.

These incidents had developed enough curiosity in me about dmr_content. It was now my turn to explore it myself.

I checked the Object Relational Model. The i_contents_id of dm_sysobject (or it’s subtypes such as dm_document) is linked to the r_object_id of dmr_content. dmr_content also has a repeating attribute called parent_id which is linked to the r_object_id of dm_sysobject.

I was able to establish the relation between dm_document and dmr_content. The actual document or the content is stored as dmr_content and it is linked to dm_document which stores the metadata. In fact all the contents are stored as objects of dmr_content. That is the reason for creation of a new dmr_content object when we create a rendition.

But why is the parent_id a repeating attribute? It should mean that many dm_document objects can be linked to a single dmr_content object. Does that make sense? I performed the following operations on a document in order to check that.

  1. Check out a document and check it back in as a new version without making any changes to it.

  2. Create a copy of a document.

In both the cases a new object of dmr_content was created and the new document/version got linked to it. Interestingly even when I checked in the document as the same version, a new dmr_content object was created. The deletion of dm_document did not delete the associated dmr_content object either. Both of the above mentioned operations had resulted in the creation of orphan dmr_content objects. I read on a forum that the dm_clean job removes all such orphan objects. So, until the dm_clean job runs, it is possible to retrieve the content of a deleted document. Further the content files on the host file system which doesn’t have a referring dmr_content object are deleted by dm_filescan job.

Here is the most interesting part and the answer to the introductory question: I found this DQL query in the discussion forum of powerlink.
EXECUTE GET_PATH FOR ‘06XXXXXXXXXXXXXX’

This was the thing I was looking for. In order to verify it I imported an image file named object_type.jpg in my repository using DA. I used the following query to find the r_object_id of the dmr_content it was associated with.

SELECT r_object_id
FROM dmr_content
WHERE any parent_id in
(SELECT r_object_id
FROM dm_document
WHERE object_name = 'object_type.jpg')

Alternatively, the following query can also be used.

SELECT i_contents_id AS DMR_CONTENT_ID
FROM dm_document
WHERE object_name = 'object_type.jpg'

The return value of the query was ‘060003f08000c93e’. This value was used as input for the second query.

EXECUTE GET_PATH
FOR '060003f08000c93e'

Any guesses? The query returned me the following content path.

C:/Documentum/data/ASSAPArchive/content_storage_01/000003f0/80/00/59/e2.jpg

The e2.jpg was same as the object_type.jpg which I had imported. In other words, the file was stored in the host file system but it was renamed. GET_PATH is an Administration method and can be found under Administration >> Job Management >> Administration Method node in Documentum Administrator.

I am sorry but I don’t want to indulge in any more puzzles now. You can get some help in Robin’s Post if you want to understand the logic of resolving the content path of dmr_content. As per Object Relational Diagram, the storage_id and data_ticket attributes are used to refer to the content stored in the host file system.

In the end I got the answer for the long standing question. But one question remained unanswered. Why is parent_id a repeating attribute? Any thoughts on that?

Related Articles:

Read Full Post »

Older Posts »