Intralink SQL Hacks : get your armour ready...

'SQL Hacks' is a suitable term for the commands we will discuss here - they are not generally supported by PTC, and may sometimes produce undesirable results.  Remember they're powerful, and work directly on the database.  I recommend you always work on a test server, and ALWAYS take a backup before applying to a production server. Also - I will not be held responsible for any ill effects of anything you try - though I'd be happy to take the credit for any good things that turn out... 

'SQL' here is shorthand for SQL*Plus, the language of Intralink + Oracle at this level. You'll find great resources elsewhere on the web (eg: orafaq), but here I'll specifically aim at Intralink 3.x.  Most Intralink admin can be accomplished by the standard interface, though Pro/Admin and DSMU are pretty clumsy. I continue to stick with these as much as possible, but in cases of automation, repetition and downright unavailability I'll delve into command lines and text files.

I've worked with several dataservers with 'checkered history', eg: clusters on dead servers, out-of-date replication, non-standard attribute names, and other issues...  SQL is useful for tweaking storage and attributes.  Following posts will look at these 2 topics in more detail, but these few tips will get you started:

First, type this in a Command Prompt on the Dataserver:

sqlplus system/manager

Next, take a quick look at the fileservers and clusters (copy+paste these at SQL> prompt):

set linesize 1000
set pagesize 1000
col FSVHOST format a30
col FSNAME format a30
col POOLNAME format a20
col POOLPATH format a40
col POOLHOST format a20
select FSVHOST,FSVID,CREATEDON from pdm.pdm_fileserver;
select FSNAME,FSID,CREATEDON,MODIFIEDON from pdm.pdm_filespace;
select POOLNAME,POOLPATH,POOLHOST from pdm.pdm_pool ORDER BY POOLID;

You should see your servers and clusters displayed with their relevant codes and dates...

And here's some SQL to get specific attribute codes (put in your own attribute names):

set pagesize 1000
column CLANAME format a20
column DBAATTRCOL format a30
select c.CLANAME,DBAATTRCOL from pdm.pdm_dbattrdef d, pdm.pdm_classattr c
where d.claid = c.claid and (
c.CLANAME = 'Title_1'
or c.CLANAME = 'Description1'
or c.CLANAME = 'Matl'
or c.CLANAME = 'Material'
) order by CLANAME;

Next time we'll look at using this information to wreak havoc...