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...

Danger: Thin Ice (The value of backups)

Two days ago my laptop hard disk failed - there was no warning, everything just froze up and suddenly the primary disk was unavailable.  Many thoughts flashed past, thankfully they included memories of recent backups to a network server and an external hard disk.  In spite of these backups, I've still lost some work (including some notes for another article here ;)

Anyway - it brought the backup thing into focus, and linked with another experience I've had recently: discovering two Intralink servers with no active backup system in place!  I've put scripts in place, but previously had no knowledge of them - one had its last metadata dump file dated 11th Dec 2006 - that's 11 months ago... The most astonishing thing: the scheduled task had failed to start and NO-ONE NOTICED.

I've now added a couple of lines to all our backup scripts to log date/time and publish to a central location... by checking this occasionally any issues can be spotted and investigated.  This goes hand in hand with my weekly server report script, that tells you a bunch of info about your servers - items, users, folder, attributes, etc...  I find these scripts extremely valuable working with 13 different data servers, and will post on website if there's interest.

For now, I'm off to try and remember the SQL tips I'd writted before the crash...

Celebrate: Intralink 3.x will support Wildfire 4

Yes, it's public knowledge now: PTC will support Wildfire 4 with Intralink 3.4 next summer... I've seen the announcement mentioned on a public news group, so I'm not the first out with the news (although I heard it a couple of weeks ago on PTC/USER group, another good reason for getting involved).

Here's how I understand it:

• A new build of Intralink 3.4 will be released to support a future build of Wildfire 4
• PTC have listened to widespread customer opinions + petitions - thankfully
• The unicode issues will be resolved by new coding (3.4 upgrade will be more than a simple patch)
• 3.x support officially ends in June 08 - but you can purchase an extra year at 110% if you're keen
• The compatible build will be released before the support freeze (though the extra year gives the impression there'll be bugs to fix...)

In reality, all this does is buy you an extra year to sort your Pro/E data management for the future, but it will be a valuable year.  As I've suggested here before, I still don't think PDMLink is yet at a level to replace Intralink 3.x for direct Pro/E file management.  9.1 may be the tipping point, but that remains to be seen.  Any more I hear will appear here...

Pro/E FAQ must-read plus more

I was just getting ready to write here + then I come across this great post on Pro/E FAQ:  What does it take to be a good Pro/E operator?

Proegeek has a list of things every Pro/E user should know - and it sounds like they're based on firsthand experience, they certainly ring true with me...

How often do I find myself telling Pro/E folks to think further than just their CAD program...?  Design knowledge and drafting (or draughting) standards are so important, and yet we often get embroiled in the workings of a particular feature or a dimensioning nuance.

Anyway - the dead horse of Intralink 3.x upgrades is all but flogged for me!  I have a couple more on the horizon, but there won't be any more about them here unless requested (which is unlikely, since this blog's readers are remarkably quiet these days...)

My next general topic on the blog is SQL hacking... not exactly CAD-specific I know, but it interests me, and lies in the same vein as automation, mapkeys, UI scripting, macros, etc... ie: simple programming to achieve great results.  I'll put together some useful findings I've made in my quest for standard attributes across multiple servers, hoping these will be useful to others out there.

Meanwhile, I'll make a note to forward the Pro/E FAQ article to my fellow Pro/E-nthusiasts.