Skip to main content

Galaxy Server SQL Tips

Galaxy Information

Get total number of active users on a repository
SELECT * FROM SKVirtualActor WHERE IsActiveUser = 1;

Troubleshooting

Cleaning up the change log
DELETE FROM gobject_change_log
WHERE change_date <= DATEADD(dd, -15, GetDate())
Truncate the database log file
DECLARE @DBNAME nvarchar(max)
SET @DBNAME= 'Test' /* Set the correct galaxy name here*/

DECLARE @RTp nvarchar(max)
SELECT @RTp = recovery_model_desc
FROM sys.databases
WHERE name = @DBNAME;

IF (@RTp is not null)
  BEGIN
    DECLARE @LOGNAME varchar(100)
    DECLARE @sql nvarchar(max)
    SET @sql = 'ALTER DATABASE '+ @DBNAME +' SET RECOVERY SIMPLE'
    IF (upper(@RTp)<> 'SIMPLE')
       EXEC (@sql)
    IF (coalesce(object_id(N'tempdb..##tt'),0)>0)
       DROP TABLE ##tt;
    SET @sql = 'select name into ##tt from sys.database_files where type =0'
      EXEC sp_executesql @sql

    DECLARE users_cursor
    CURSOR FOR SELECT name FROM ##tt
    OPEN users_cursor
    FETCH NEXT FROM users_cursor
    INTO @LOGNAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
      --Print @LOGNAME
      DBCC SHRINKFILE (@LOGNAME , 1)
      FETCH NEXT FROM users_cursor --have to fetch again within loop
      INTO @LOGNAME
    END
    CLOSE users_cursor
    DEALLOCATE users_cursor

    SET @sql = 'DBCC SHRINKDATABASE (''' + @DBNAME +''' , TRUNCATEONLY)'
      EXEC(@sql)
    SET @sql ='ALTER DATABASE '+ @DBNAME +' SET RECOVERY FULL;'
    IF (upper(@RecoveryModel)<> 'SIMPLE')
       EXEC(@sql)
  END
Testing database integrity
EXEC internal_validate_integrity

Templates

Find template gobject ID
SELECT gobject_ID FROM GalaxyName.gobject WHERE tag_name LIKE 'TemplateName';
Unprotect template
DELETE FROM GalaxyName.gobject_protected WHERE gobject_id = #
Protect template
INSERT INTO GalaxyName.gobject_protected (gobject_id) VALUES (#);
Get list of protected templates
SELECT g.gobject_id, g.tag_name
FROM gobject as g
INNER JOIN gobject_protected as p on p.gobject_id = g.gobject_id
ORDER BY g.gobject_id
Get list of all objects and their areas derived from a specific template
DECLARE @tempName VARCHAR(50);
SET @tempName = '$TemplateName';

SELECT g.tag_name as TagName,
		(SELECT tag_name FROM gobject WHERE (gobject_id = g.hosted_by_gobject_id)) AS Area
FROM gobject AS g
WHERE g.is_template = 0
	AND g.derived_from_gobject_id = (
	SELECT gobject_id FROM gobject WHERE tag_name = @tempName
	)
ORDER BY Area, g.tag_name

Get count of objects derived from a specific template
DECLARE @tempName VARCHAR(50);
SET @tempName = '$TemplateName';

SELECT COUNT(gobject_id)
FROM gobject 
WHERE is_template = 0
	AND derived_from_gobject_id = (
	SELECT gobject_id FROM gobject WHERE tag_name = @tempName
	)

Alarms

Get list of all Alarmed attributes
SELECT g.tag_name + '.' + p.primitive_name AS name, p.gobject_id, p.package_id, p.mx_primitive_id, g.hosted_by_gobject_id,
(SELECT tag_name FROM gobject WHERE (gobject_id = g.hosted_by_gobject_id)) AS Area
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 19 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 0)
ORDER BY name
Get list of all alarmed attributes defined in templates
SELECT g.tag_name AS Template, p.primitive_name AS Attribute
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 19 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Attribute
Get alarms from wwALMDB
SELECT EventStamp, AlarmState, TagName, Description, Operator, Priority, UserFullName
FROM v_AlarmHistory
where EventStamp > '1/1/2013' and EventStamp < '1/2/2013';

Historization

Get list of all historized attributes defined in templates
SELECT g.tag_name AS Template, p.primitive_name AS Attribute
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 18 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Attribute

Scripts

Get list of all scripts defined in templates
SELECT g.tag_name AS Template, p.primitive_name AS Script
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 16 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Script

Log Change Events

Get list of all log change attributes defined in templates
SELECT g.tag_name AS Template, p.primitive_name AS Attribute
FROM gobject AS g INNER JOIN
primitive_instance AS p ON p.gobject_id = g.gobject_id AND p.execution_group = 6 INNER JOIN
primitive_definition AS pd ON p.primitive_definition_id = pd.primitive_definition_id AND pd.primitive_name <> '' INNER JOIN
package AS pkg ON p.gobject_id = pkg.gobject_id AND p.package_id = pkg.package_id AND pkg.package_id = g.checked_in_package_id
WHERE (g.namespace_id = 1) AND (g.is_template = 1)
ORDER BY Template, Attribute


Back to top