Content Server Database Tables
HI.
I need to access a category attribute of each content server document to validate a date.
I'm using LiveReport / WebReport.
Can you tell me what table I can get the documents from the content server and also have access to the attributes of the categories of those documents?
Regards,
Felipe.
Comments
-
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.
LLATTRDATA.
Attached(hopefully) is a basic relationship Visio drawing, I’ve made back in 2013 and have since updated it. It has some of the “codes” necessary for figuring out which fields to use to extract based on the AttrID & Attrtype.
LLattrdata.id = Dtree.dataid and llattrdata.vernum = dtree.versionnum
If you need more, let me know.
Colin J
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 7, 2018 11:56 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Content Server Database TablesPosted byfelipe.fonseca@entelgy.com (Open Text, Projeto) On 06/07/2018 12:49 PM
HI.
I need to access a category attribute of each content server document to validate a date.
I'm using LiveReport / WebReport.
Can you tell me what table I can get the documents from the content server and also have access to the attributes of the categories of those documents?
Regards,
Felipe.
[To post a comment, use the normal reply function]
Forum:
Content Server:
0 -
ask your OT Account person for the NDA for the schema, be sure to list all your modules as you may need additional notes for RM etc. Then you can use this with the Schema Companion guide (https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=16448243&objAction=View) to work with the DB. The OT course is also very good.
0 -
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.
These tables are connected to each other for your purpose:
DTREE.DATAID = LLATTRDATA.ID
DTREE.VERSIONNUM = LLATTRDATA.VERNUM
LLATTRDATA.DEFID = CATREGIONMAP.CATID
Inside here, you’ll find an easier way of getting to which attribute is what kind of data item.
Categories are first DTREE Objects. But an intelligible breakdown of their parts are found in the CATREGIONMAP.
In the case of a DATE, the LLATTRDATA.ATTRTYPE = -7, but if you have more than one date attribute, you’ll need to
Know which one by the LLATTRDATA.ATTRIID = <last character/parameter of CATREGIONMAP.REGIONMAP>
Then you pull out the LLATTRDATA.VALDATE.
The NDA is the document to start with, but after you open the DB up with SQL Developer or some DB tool and look over the tables, over time you can figure out the relations on your own. Codes, a fair amount of them, can be found in the Schema Companion, as another reply gave. Others you discover here in the KC.
Colin J
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 11, 2018 5:51 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Content Server Database TablesPosted bygreg.a.griffiths@capgemini.com (Griffiths, Greg) On 06/11/2018 06:43 AM
ask your OT Account person for the NDA for the schema, be sure to list all your modules as you may need additional notes for RM etc. Then you can use this with the Schema Companion guide (https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=16448243&objAction=View) to work with the DB. The OT course is also very good.
[To post a comment, use the normal reply function]
Topic:
Forum:
Content Server:
0 -
Thanks all for the help.
Whats is the difference in subtype 144 and subtype 1 in DTREE table?
Wich subtype I can get in DTREE table to find the documents in enterprise workspace?
I've also noticed that any documents included in the content server and deleted later remains in DTREE table. Why does it happen?
If I disable Recycle Bin this wont be happening ?
Thanks.
Regards,
Felipe
0 -
I'm looking for document and category information as per the query below.
I am using the DTREE and LLATTRDATA tables to validate the date of a category.
select d.name, a.valdate from DTREE d JOIN LLATTRDATA a on a.id = d.DataID where valdate < (select ADD_YEARS(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'),-20) from DUMMY)
I search the field date in a category and check if the field date is greater than 20 years. That way I'll know if I can discard the document.
Is this information secure? Or do you think I should include in the JOIN DTREE.VERSIONNUM = LLATTRDATA.VERNUM and LLATTRDATA.DEFID = CATREGIONMAP.CATID?
Quoted Colin Schmidt on 06/11/2018 05:55 PM:These tables are connected to each other for your purpose:
DTREE.DATAID = LLATTRDATA.ID
DTREE.VERSIONNUM = LLATTRDATA.VERNUM
LLATTRDATA.DEFID = CATREGIONMAP.CATID
Inside here, you’ll find an easier way of getting to which attribute is what kind of data item.
Categories are first DTREE Objects. But an intelligible breakdown of their parts are found in the CATREGIONMAP.
In the case of a DATE, the LLATTRDATA.ATTRTYPE = -7, but if you have more than one date attribute, you’ll need to
Know which one by the LLATTRDATA.ATTRIID = <last character/parameter of CATREGIONMAP.REGIONMAP>
Then you pull out the LLATTRDATA.VALDATE.
The NDA is the document to start with, but after you open the DB up with SQL Developer or some DB tool and look over the tables, over time you can figure out the relations on your own. Codes, a fair amount of them, can be found in the Schema Companion, as another reply gave. Others you discover here in the KC.
Colin J
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 11, 2018 5:51 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Content Server Database TablesPosted by greg.a.griffiths@capgemini.com (Griffiths, Greg) On 06/11/2018 06:43 AM
ask your OT Account person for the NDA for the schema, be sure to list all your modules as you may need additional notes for RM etc. Then you can use this with the Schema Companion guide (https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=16448243&objAction=View) to work with the DB. The OT course is also very good.
[To post a comment, use the normal reply function]
Topic:
Forum:
Content Server:
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.
0 -
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.
Most definitely add those fields. Without them, you don’t know if you’re getting the Attributes for the current Version. You will also need to add the qualifying part to get the specific Attribute that contains the Date you’re after. LLATTRDATA.ATTRTYPE = -7 for DATEs and yet, if you have more than one Date Attribute, you will need to check that get the right one with LLATTRDATA.ATTRID which can be matched up somewhat to the CATREGIONMAP.REGIONMAP field(last part of value).
Colin J
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 11, 2018 5:06 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: [EXTERNAL] - RE Content Server Database Tables 2Posted byfelipe.fonseca@entelgy.com (Open Text, Projeto) On 06/11/2018 05:55 PM
I'm looking for document and category information as per the query below.
I am using the DTREE and LLATTRDATA tables to validate the date of a category.
select d.name, a.valdate from DTREE d JOIN LLATTRDATA a on a.id = d.DataID where valdate < (select ADD_YEARS(TO_DATE(CURRENT_DATE, 'YYYY-MM-DD'),-20) from DUMMY)
I search the field date in a category and check if the field date is greater than 20 years. That way I'll know if I can discard the document.
Is this information secure? Or do you think I should include in the JOIN DTREE.VERSIONNUM = LLATTRDATA.VERNUM and LLATTRDATA.DEFID = CATREGIONMAP.CATID?
Quoted Colin Schmidt on 06/11/2018 05:55 PM:
These tables are connected to each other for your purpose:
DTREE.DATAID = LLATTRDATA.ID
DTREE.VERSIONNUM = LLATTRDATA.VERNUM
LLATTRDATA.DEFID = CATREGIONMAP.CATID
Inside here, you’ll find an easier way of getting to which attribute is what kind of data item.
Categories are first DTREE Objects. But an intelligible breakdown of their parts are found in the CATREGIONMAP.
In the case of a DATE, the LLATTRDATA.ATTRTYPE = -7, but if you have more than one date attribute, you’ll need to
Know which one by the LLATTRDATA.ATTRIID = <last character/parameter of CATREGIONMAP.REGIONMAP>
Then you pull out the LLATTRDATA.VALDATE.
The NDA is the document to start with, but after you open the DB up with SQL Developer or some DB tool and look over the tables, over time you can figure out the relations on your own. Codes, a fair amount of them, can be found in the Schema Companion, as another reply gave. Others you discover here in the KC.
Colin J
From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 11, 2018 5:51 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Content Server Database TablesPosted by greg.a.griffiths@capgemini.com (Griffiths, Greg) On 06/11/2018 06:43 AM
ask your OT Account person for the NDA for the schema, be sure to list all your modules as you may need additional notes for RM etc. Then you can use this with the Schema Companion guide (https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=16448243&objAction=View) to work with the DB. The OT course is also very good.
[To post a comment, use the normal reply function]
Topic:
Forum:
Content Server:
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.
[To post a comment, use the normal reply function]
Topic:
Forum:
Content Server:
0 -
Felipe,
for a list of subtype values and what they mean, I would recommend https://www.greggriffiths.org/livelink/reference/subtypes/. The reason the content remains in DTREE is that even through the object is "deleted" it is in the Recycle Bin, which is part of the system, if you empty the Recycle Bin it should go.
0 -
Hi all,
How can we fetch the Location attribute which is located in the properties-->categories-->Personal information-->Location: for an employee workspace folder. Please suggest the attrtype0 -
Please find the above category information information for a particular employee workspace, we have to fetch only "Location" attribute which is present in Categories —> Personal Information —> Location. We already have an query to fetch the workspace name of employee workspace and folders and files inside it. But now we have to add the new lines into in the query to fetch Location of that employee workspace. Please help me with the LLAttrData table info and what is the attribute I'd ifor this Location attriute.
0 -
if you have the Category and Attribute IDs of it you can find it in the database, you can also use CatRegionMap table if you have different IDs in each environment.
1 -
?func=attributes.dump is a RH that is used,it reads the table CatRegionMap that Greg mentions
I have a query here that I used to pull Similar things the LLA1, and LLA2 are multiple joins to the same table. This is a common pattern one uses, there's an official one as well. The clue is to find the
LLA1.AttrID in (2) and 3 and so on which you get from CatRegionMap or ?func=attributes.dump.In my case all the data was defined as String hence the use of ValStr.Note that I am pulling from two categories
Enterprise Global,
Facility Global hence the differing numbers (270280 and 2331738)
select DT.DataID,DT.Name,
LLA4.ValStr "Facility Global:Discipline",
LLA5.ValStr "Facility Global:DocType",
LLA6.ValStr "Facility Global:ContentType",
LLA7.ValStr "Facility Global:Collection",
LLA8.ValStr "Facility Global:Business Function",
LLA1.ValStr "Enterprise Global:Business Function",
LLA2.ValStr "Enterprise Global:Collection",
LLA3.ValStr "Enterprise Global:Content Type",
DT.OwnerID,DT.ParentID,DT.SubType,DT.VersionNum
from DTree DT
left join LLAttrData LLA1 on DT.DataID=LLA1.ID
and LLA1.DefID=270280 and DT.VersionNum=LLA1.VerNum and LLA1.AttrID in (2)
left join LLAttrData LLA2 on DT.DataID=LLA2.ID
and LLA2.DefID=270280 and DT.VersionNum=LLA2.VerNum and LLA2.AttrID in (3)
left join LLAttrData LLA3 on DT.DataID=LLA3.ID
and LLA3.DefID=270280 and DT.VersionNum=LLA3.VerNum and LLA3.AttrID in (4)
left join LLAttrData LLA4 on DT.DataID=LLA4.ID
and LLA4.DefID=2331738 and DT.VersionNum=LLA4.VerNum and LLA4.AttrID in (2)
left join LLAttrData LLA5 on DT.DataID=LLA5.ID
and LLA5.DefID=2331738 and DT.VersionNum=LLA5.VerNum and LLA5.AttrID in (7)
left join LLAttrData LLA6 on DT.DataID=LLA6.ID
and LLA6.DefID=2331738 and DT.VersionNum=LLA6.VerNum and LLA6.AttrID in (11)
left join LLAttrData LLA7 on DT.DataID=LLA7.ID
and LLA7.DefID=2331738 and DT.VersionNum=LLA7.VerNum and LLA7.AttrID in (8)
left join LLAttrData LLA8 on DT.DataID=LLA8.ID
and LLA8.DefID=2331738 and DT.VersionNum=LLA8.VerNum and LLA8.AttrID in (12)
where DT.DataID in (select TOP 30000 DataID FROM DTreeAncestors where AncestorID=2332140);OT used to have a BI tool not sure if they still do. Its developers created these however this actually creates database objectshttps://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=12740255&objAction=ArticleView
0 -
hi @appuq,
Thankyou I have explored the list of attributes by using ?func=attributes.dump, now I am able to execute the query with appropriate DefId and attrId.
I have an other question on differences between test and production database tables, I am getting the results by executing below query in test environment, but unable to get the results for prod environment.
SELECT ws.name AS "Employee_Workspace_Name",
fol.name AS "Folder_Name",
doc.name AS "Document_Name"
FROM dtree doc
INNER JOIN dtree fol ON doc.parentid = fol.dataid
INNER JOIN dtree ws ON doc.ownerid = ws.dataid
WHERE doc.subtype = 144
AND fol.subtype = 0
AND ws.subtype = 848
AND fol.name = '200 Miscellaneous'
ORDER BY ws.name;With this query I am able to see the results in test environment, but in prod we are getting empty results, though we have same folder structure, please suggest me if any changes needed in the query for production.
0 -
The query does look OK and should not need to be changed in PROD.
What you should check if the data is as you expect in your query.
Look for a document in a folder '200 Miscellaneous'. Check if the folder is actually named like that in DTree.
Check if the ParentID and OwnerID are as expected on the document.
0 -
It does not stating any errors , but taking long time to run and at the end it showing like empty results with column names Type and Name. Any clue on how to check Dtree table on columns in content server itself , as we do not have access to databse0
-
If this is in a livereport you need to change the report type to Automatic LiveReport.
0 -
By changing the formate to auto Live report I am able to view the results now, Thankyou so much Sir. Now, with this we can generate a report right with web report help. This auto live report format did not effect in report generation right?0
-
No, the auto livereport is intended when you don't need to alias names. The standard LR format allows automatic hyperlinks in the output if certain key table columns are used DTree.dataid, KUAF.ID,webnodes, etc.
in an autolivereport, if you wrote select * from DTree it will spit out columns
in LR you would have to say the column names(aliases)
in your WR you will need to read the column returned either using positional parameters or column names.
The WR/LR should not be made by hand in production use a transport.
0
Categories
- All Categories
- 122 Developer Announcements
- 53 Articles
- 151 General Questions
- 146 Thrust Services
- 56 OpenText Hackathon
- 35 Developer Tools
- 20.6K Analytics
- 4.2K AppWorks
- 9K Extended ECM
- 917 Cloud Fax and Notifications
- 84 Digital Asset Management
- 9.4K Documentum
- 31 eDOCS
- 179 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management
- 9 XM Fax
- Follow Categories
