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())

Clean up unused galaxy objects

DECLARE @return_value int, @all_finished int
EXEC internal_delete_unused_packages @all_finished = @all_finished OUTPUT
SELECT @all_finished as N'@all_finished'
SELECT 'Return Value' = @return_value

Rebuild/Reorganize Indexes (Long Version - Based on Fragmentation)

DECLARE @reIndexRequest VARCHAR(1000)

DECLARE reIndexList CURSOR
FOR
SELECT INDEX_PROCESS
FROM (
    SELECT CASE 
            WHEN avg_fragmentation_in_percent BETWEEN 5
                    AND 30
                THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REORGANIZE;'
            WHEN avg_fragmentation_in_percent > 30
                THEN 'ALTER INDEX [' + i.NAME + '] ON [' + t.NAME + '] REBUILD WITH (FILLFACTOR=80, SORT_IN_TEMPDB=ON, ONLINE=ON);'
            END AS INDEX_PROCESS
        ,avg_fragmentation_in_percent
        ,t.NAME
    FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS a
    INNER JOIN sys.indexes AS i ON a.object_id = i.object_id
        AND a.index_id = i.index_id
    INNER JOIN sys.tables t ON t.object_id = i.object_id
    WHERE i.NAME IS NOT NULL
    ) PROCESS
WHERE PROCESS.INDEX_PROCESS IS NOT NULL
ORDER BY avg_fragmentation_in_percent DESC

OPEN reIndexList

FETCH NEXT
FROM reIndexList
INTO @reIndexRequest

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY

        PRINT @reIndexRequest;

        EXEC (@reIndexRequest);

    END TRY

    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = 'UNABLE TO CLEAN UP INDEX WITH: ' + @reIndexRequest + ': MESSAGE GIVEN: ' + ERROR_MESSAGE()
            ,@ErrorSeverity = 9 
            ,@ErrorState = ERROR_STATE();

    END CATCH;

    FETCH NEXT
    FROM reIndexList
    INTO @reIndexRequest
END

CLOSE reIndexList;

DEALLOCATE reIndexList;

Rebuild Indexes (Short Version - Rebuild All)

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=80, SORT_IN_TEMPDB=ON, ONLINE=ON)'

Reorganize Indexes (Short Version - Reorganize All)

This isn't necessary if you do the rebuild as a REBUILD is more thorough than a REORGANIZE operation.

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REORGANIZE'

Truncate the database log file

DECLARE @DBNAME nvarchar(max)
SET @DBNAME= 'Test' /* Set the correct galaxy name here*/

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

IF (@RecoveryModel is not null)
  BEGIN
    DECLARE @LOGNAME varchar(100)
    DECLARE @sql nvarchar(max)
    SET @sql = 'ALTER DATABASE '+ @DBNAME +' SET RECOVERY SIMPLE'
    IF (upper(@RecoveryModel)<> '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 gobject WHERE tag_name LIKE 'TemplateName';

Find all user defined templates (derived from $UserDefined) and instance counts

SELECT g.gobject_id, g.tag_name, COALESCE(c.object_count, 0) AS instances
FROM gobject g
LEFT JOIN (
	SELECT derived_from_gobject_id AS parent_id, COUNT(gobject_id) AS object_count
	FROM gobject
	WHERE is_template = 0
	GROUP BY derived_from_gobject_id
) c ON c.parent_id = g.gobject_id
WHERE is_template = 1 AND derived_from_gobject_id > 0 AND template_definition_id = 108
ORDER BY tag_name

Unprotect template

DELETE FROM gobject_protected WHERE gobject_id = ?

Protect template

INSERT INTO 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
	)

Get user attributes of a template (excluding inherited)

SELECT g.tag_name
      ,[attribute_name]
      ,da.[mx_data_type]
	  ,dt.description AS data_type_desc
      ,[is_array]
      ,[security_classification]
		, (CASE
			WHEN da.security_classification = 0 THEN 'FreeAccess'
			WHEN da.security_classification = 1 THEN 'Operate'
			WHEN da.security_classification = 2 THEN 'SecuredWrite'
			WHEN da.security_classification = 3 THEN 'VerifiedWrite'
			WHEN da.security_classification = 4 THEN 'Tune'
			WHEN da.security_classification = 5 THEN 'Configure'
			WHEN da.security_classification = 1 THEN 'ViewOnly'
			ELSE CONCAT('Unknown',da.security_classification)
		END) AS security_class_desc
	  , COALESCE(p.IsInput, 0) AS is_input
	  , COALESCE(p.IsOutput, 0) AS is_output
	  , COALESCE(p.LogChange, 0) AS log_change
	  , COALESCE(p.Historized, 0) AS historized
	  , COALESCE(p.Alarmed, 0) AS alarmed
  FROM dynamic_attribute da
  INNER JOIN gobject g ON g.gobject_id = da.gobject_id
  LEFT JOIN (
	SELECT p2.gobject_id, p2.primitive_name
		, MAX(CASE WHEN p2.execution_group = 1 THEN 1 ELSE 0 END) AS IsInput
		, MAX(CASE WHEN p2.execution_group = 17 THEN 1 ELSE 0 END) AS IsOutput
		, MAX(CASE WHEN p2.execution_group = 6 THEN 1 ELSE 0 END) AS LogChange
		, MAX(CASE WHEN p2.execution_group = 18 THEN 1 ELSE 0 END) AS Historized
		, MAX(CASE WHEN p2.execution_group = 19 THEN 1 ELSE 0 END) AS Alarmed
	FROM primitive_instance p2
	WHERE p2.execution_group NOT IN (4, 5, 16)
	GROUP BY p2.gobject_id, p2.primitive_name) AS p ON p.gobject_id = g.gobject_id AND p.primitive_name = da.attribute_name
  INNER JOIN data_type dt ON dt.mx_data_type = da.mx_data_type
  WHERE g.tag_name = '$mMotor'
  AND da.package_id = g.checked_in_package_id
  AND da.mx_primitive_id = 2				-- Remove built-in primitives
  AND da.dynamic_attribute_type = 1		-- Remove extended properties of attributes (EngUnits, Description, etc)
 ORDER BY attribute_name

Various Features

Get list of all attributes with specific features enabled on them

SELECT g.tag_name + '.' + p.primitive_name AS name, p.gobject_id, p.package_id, p.mx_primitive_id, g.hosted_by_gobject_id, p.execution_group,
CASE 
	WHEN p.execution_group=1 THEN 'I/O'
	WHEN p.execution_group=4 THEN 'Symbol'
	WHEN p.execution_group=6 THEN 'Log Change'
	WHEN p.execution_group=16 THEN 'Script'
	WHEN p.execution_group=18 THEN 'Historized'
	WHEN p.execution_group=19 THEN 'Alarm'
	ELSE ''
END AS 'Features',
(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 NOT IN (4, 5, 16, 17) 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

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

Datatypes

Get list of all supported datatypes

SELECT * FROM data_type

Spoiler!

mx_data_type	Description
1	Boolean
2	Integer (Int32)
3	Float (Single)
4	Double
5	String
6	Time (DateTime)
7	Elapsed Time (TimeSpan)
15	Internationalized String