WARNING: Use these techniques and sample scripts at your own risk.
Always make sure you have a current and reliable BACKUP of the Intralink Commonspace before making any changes at SQL level.
'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;
