One of the many little bundles of joy when dealing with back end SQL code on ‘living’* application such as IBM Connections, is that SQL that may have worked for one version or service pack might not work for another
To cater for this you might have to write for or at least be able to handle the diferent versions of a database, when doing this you tend not to use the actual product version ie “Connections 4.5.0.0.” but the db schema number for each database, thankfully IBM store the db schema for each database in Connections (though not with a great deal of consistency), the following is the SQL to get this for each of the databases that make up connections.
Connections App: Files
BD: FILES
Table: PRODUCT
Connection v4.5 Base Schema Number: 82
SQL to get Schema
SELECT SCHEMA_VERSION FROM "FILES"."PRODUCT";
***
#####Connections App: Activities
_BD:_ OPNACT
_Table:_ OA_SCHEMA
_Connection v4.5 Base Schema Number:_ 56
SQL to get Schema
SELECT DBSCHEMAVER FROM "ACTIVITIES"."OA_SCHEMA";
***
#####Connections App: Blogs
_BD:_ BLOGS
_Table:_ ROLLER_PROPERTIES
_Connection v4.5 Base Schema Number:_ 51
SQL to get Schema
SELECT DBMS_LOB.substr(VALUE, 3000) FROM "BLOGS"."ROLLER_PROPERTIES" where NAME = 'database.schema.version';
_Notes:_ The rotten sods have stored it as a “Clob” data format (basically Binary) so the SQL is a bit more complex.
***
#####Connections App: Communities
_BD:_ SNCOMM
_Table:_ SNCOMM_SCHEMA
_Connection v4.5 Base Schema Number:_ 77
SQL to get Schema
SELECT DBSCHEMAVER FROM "SNCOMM"."SNCOMM_SCHEMA";
***
#####Connections App: Community Events
_BD:_ SNCOMM
_Table:_ CA_SCHEMA
_Connection v4.5 Base Schema Number:_ 15
SQL to get Schema
SELECT DBSCHEMAVER FROM "CALENDAR"."CA_SCHEMA";
***
#####Connections App: BookMarks
_BD:_ DOGEAR
_Table:_ DOGEAR_SCHEMA
_Connection v4.5 Base Schema Number:_ 15
SQL to get Schema
SELECT DBSCHEMAVER FROM "DOGEAR"."DOGEAR_SCHEMA";
***
#####Connections App: Forums
_BD:_ FORUM
_Table:_ DF_SCHEMA
_Connection v4.5 Base Schema Number:_ 27
SQL to get Schema
SELECT DBSCHEMAVER FROM "FORUM"."DF_SCHEMA";
***
#####Connections App: HomePage , News, Search
_BD:_ HOMEPAGE
_Table:_ HOMEPAGE_SCHEMA
_Connection v4.5 Base Schema Number:_ 210
SQL to get Schema
SELECT DBSCHEMAVER FROM "HOMEPAGE"."HOMEPAGE_SCHEMA";
***
#####Connections App: Metrics
_BD:_ METRICS
_Table:_ PRODUCT
_Connection v4.5 Base Schema Number:_ 34
SQL to get Schema
SELECT SCHEMA_VERSION FROM "METRICS"."PRODUCT";
***
#####Connections App: Mobile
_BD:_ MOBILE
_Table:_ ROLLER_PROPERTIES
_Connection v4.5 Base Schema Number:_ 1
SQL to get Schema
SELECT VALUE FROM "MOBILE"."ROLLER_PROPERTIES" where NAME = 'database.schema.version';
***
#####Connections App: Profiles
_BD:_ PEOPLEDB
_Table:_ SNPROF_SCHEMA
_Connection v4.5 Base Schema Number:_ 36
SQL to get Schema
SELECT DBSCHEMAVER FROM "EMPINST"."SNPROF_SCHEMA";
***
#####Connections App: Wiki
_BD:_ WIKIS
_Table:_ PRODUCT
_Connection v4.5 Base Schema Number:_ 82
SQL to get Schema
SELECT SCHEMA_VERSION FROM "WIKIS"."PRODUCT";
***
*By living I mean ‘the feckers keep changing them’
_NOTE: All these tips are not officaliy sanctioned by IBM as they do not release DB schema info to the public, so you use them at your own risk, Also if IBM are reading this and I either have somthing wrong or you have documented this for the public, then please tell me and I will correct_ 🙂
_NOTE: I’m not a proper Connections / Web Sphere admin, If you want proper Web Sphere and Connections admin you want [the Turtle Partnership](http://www.turtleweb.com/)_