Multiple Remote Desktop Connections

It's free. It helps with multiple RDC's and makes life simpler. I knew about it but never thought about finding the original download source. I kept getting it off of other server admins' thumbdrives...

http://terminals.codeplex.com/

Very basic Oracle query to get parent hierarchy

Here's a simple query that uses the "connect by" functionality in Oracle to get the entire parental hierarchy of a given nodeid in DTREE.

select *
from dtree
connect by prior parentid=dataid
start with dataid = 12345 --starting nodeID

This will give you all the parent nodeid's starting from the Enterprise top level down to the dataid you specified.

Pivoting the Livelink LLATTRDATA Table

For those who have tried to pull attribute data, you know that each attribute value is on a separate row and may even be in a different column. Here's a query (sorry MSSQL guys) that will put all the attributes into one row when you run it. This can be used in web reports or external apps to grab category/attribute data and display it nicely. Good luck.

select lla.id
, max(decode(lla.attrid, 2, lla.valstr, null)) "Attribute Name 1"
, max(decode(lla.attrid, 3, lla.valstr, null)) "Attribute Name 2"
, max(decode(lla.attrid, 4, lla.valstr, null)) "Attribute Name 3"
, max(decode(lla.attrid, 5, lla.valstr, null)) "Attribute Name 4" 

, max(decode(lla.attrid, 6, lla.valstr, null)) "Attribute Name 5"

, max(decode(lla2.attrid, 5, lla2.valstr, null)) "Attribute Name 6"

from llattrdata lla, llattrdata lla2
where lla.id = lla2.id
and lla.defid = 47881102 --category id 1
and lla2.defid = 40278096 --category id 2
and lla.id = 48212327 --can replace with a subselect to get a group of dataIDs in dtree (ex: *where defid in (select dataid…))
and lla2.id = 48212327
group by lla.id

Please note that the defids and ids will not be the same in your instance. You need to replace them with your own values. 

Here's the explanation:

• select lla.id - Pulls the Livelink object ID from the LLATTRDATA table
• , max(decode(lla.attrid, 2, lla.valstr, null)) "Attribute Name 1" - This gets only one row, one column for the specific attribute. What you need to change: The numeric value inside the decode, right after the "lla.attrid". The "2" in this case represents the attribute ID. This number can be found on the CATREGIONMAP table as the number found suffixed to the value in column REGIONNAME (Attr_1234567_###). This number gets used in the decode statement to filter against the ATTRID column in table LLATTRDATA. 
• , max(decode(lla.attrid, 3, lla.valstr, null)) "Attribute Name 2" - SAME AS ABOVE
• , max(decode(lla.attrid, 4, lla.valstr, null)) "Attribute Name 3" - SAME AS ABOVE
  , max(decode(lla.attrid, 5, lla.valstr, null)) "Attribute Name 4" - SAME AS ABOVE
• , max(decode(lla.attrid, 6, lla.valstr, null)) "Attribute Name 5" - SAME AS ABOVE
• , max(decode(lla2.attrid, 5, lla2.valstr, null)) "Attribute Name 6" - In this line, I am referencing an attribute ID off another category, hence the lla2.attrid.
• from llattrdata lla, llattrdata lla2 - Datasource for pulling the attribute values. You will need to call the LLATTRDATA table for each different category you are trying to grab data from. In this case, there are only two that are associated with the Livelink objects in question.
• where lla.id = lla2.id - The inner join to connect the two LLATTRDATA tables. If you had three LLATTRDATA calls, then the second JOIN would be "and lla2.id = lla3.id"
• and lla.defid = 47881102 --category id 1 - The filter for the first LLATTRDATA table call, i.e. the DEFID or the category ID, which can be pulled from the CATREGIONMAP table.
• and lla2.defid = 40278096 --category id 2 - Same as above except this is calling the defid of the second category
• and lla.id = 48212327 - Filter to get a specific Livelink object in the LLATTRDATA table. This value is the object ID. You can actually implement a subselect statement in place of the hardcoded dataid. This will allow you to get multiple objects with their category/attribute values associated.
• and lla2.id = 48212327 - Same as above. You have to apply the same filter to all LLATTRDATA table calls because of the table joins and pivots.
• group by lla.id - Forces the results to show up in one row per Livelink object

Usages of this query: 

• Faster than joining multiple select statements to get LiveLink attribute data.
• Web Reporting... I've actually used this as a data source for a web report that returns 30K+ records. If you implement the SQL as a materialized view in the LiveLink schema and  reference it in your LiveReport SQL, then the results should be quick. 
• Quick go-to for data pulling that clients need.

New Bug in Livelink 9.7.1 - Destroy Your Search Index

That's right.... this one will destroy your search index. It's a new bug that we discovered unfortunately the hard way. Opentext confirmed it and should be working on a patch.

Basically, when you create an extended attribute table for a category to reference, Livelink will build out a new table in its schema. The issue is that certain attribute names or the column name that Livelink will use when building out the table are in fact reserved words.

The reserved word in question this time around is ATTR_VALUE. Of course, this famous word is seen everywhere in Livelink, especially in the catregionmap table and llattrdata table. OUCH.

Words of warning here: Don't name an attribute or actually anything in Livelink "ATTR_VALUE". Just stay away from using it as a prefix or suffix. What happens is that the search index becomes corrupt and you have to rebuild it from the ground up. That is MASSIVE.

Good luck =]

How to update a boolean, yes/no, true/false attribute with LAPI

Below is sample code in C# on updating a physical item attribute (custom system attributes in the Specifics tab of a physical object). You can use the same logic to update a regular category attribute that is boolean based.

The most important line is the  request.add("mtField_PrintNewLabel", "on");

Basically, use "on" for true, "off" for false. The "mtField_PrintNewLabel" is the name of the custom physical object attribute. It's mtField_Name-of-the-field.

For a category-attribute, you would use the update category functions and call the attribute name with the "on"/"off" value being passed.  


***I will post more examples of updating category/attributes, physical items (records management), and easy Livelink session establishment.

void doIt(int nodeID)

        {

            LLSession session = null;

            session = new LLSession("opentext.server.blah.com",

                                    2099,

                                    "",

                                    "adminloginacct",

                                    "adminpw");

            session.ImpersonateUser("yomama");

            LAPI_DOCUMENTS doc = new LAPI_DOCUMENTS(session);

            LLValue request = (new LLValue()).setAssocNotSet();

            request.add("objAction", "info2");

            request.add("selectedMedia",1235465 );

            request.add("poLocation", "CRC - ABC");

            request.add("mtField_Volume", "Vol10");

            request.add("mtField_Temporary", "No");

            request.add("mtField_FileType", "");           

            request.add("mtField_PrintNewLabel", "on");

            LLValue myEntValues = new LLValue();

            int volID = 0;

            if (doc.AccessEnterpriseWS(myEntValues) == 0)

            {

                volID = myEntValues.toInteger("VolumeID");

            }

            int status = 0;

            status = doc.UpdateObjectInfo(volID, nodeID, request);

            if (status != 0)

            {

                string errMsg = string.Format("Problem: {0} | {1} | {2} | {3}", session.getApiError(), session.getErrMsg(), session.getStatus(), session.getStatusMessage());

                Response.Write(errMsg);

            }

            else

            {

                Response.Write("Something got updated");

            }

        }

The Business Analyst Algorithm

This has nothing to do with Livelink, but after doing development work for a while, I have learned that developers do A LOT more than actual coding. I was inspired by the Friend Algorithm by Sheldon Cooper in the TV sitcom "Big Bang Theory". I gotta say, this is pretty accurate. So accurate, that even my colleagues follow it. (SOL stand for **it Out of Luck).

How To Kill Your Livelink Server.... With Web Reports

Yes... this is true and very possible:

• Run a long processing web report under the livelink.exe path and you'll get a CGI Timeout. 
• Keep doing it and the server will not respond anymore. You'll have to restart services.
• Send a null value to a search parameter based on an attribute field (if you are using search as your datasource)
• This will cause an infinite loop. This is guaranteed when the attribute you are searching on is a required attribute. So much for depending on search as your datasource vs. old-fashioned LLATTRDATA convoluted-inner-join queries....

To avoid it: Use the llisapi.dll to run the reports. 

I brought down our production servers today. =]