Required Queries

Users of the UCS DB must have permission to run certain queries on the database, for the following purposes:

• List user's tables—required for launching UCS
  
• Read NLS (national language support) configuration—required for normal operation of UCS
  
• List user's indexes—required for normal operation of UCS
  
• Read the configured maximum number of cursors—required for normal operation of UCS, on Oracle only
  

The following examples use CONTACTSERV_USER and CONTACTSERVARC_USER as the names of users of the main and archive databases respectively:

Oracle

To list user’s tables:

SELECT T.TABLE_NAME, T.COLUMN_NAME, DECODE (T.NULLABLE, 'N', 'NO', 'YES') AS IS_NULLABLE 
FROM ALL_TAB_COLUMNS T WHERE UPPER(T.OWNER)='CONTACTSERV_USER' ORDER BY T.COLUMN_ID

To read NLS configuration:

• In 8.1.0:
  

  SELECT * FROM sys.props$ WHERE name LIKE 'NLS%CHARACTERSET%'

• In 8.1.1 and later:
  

  SELECT * from SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE 'NLS%CHARACTERSET%

To list user’s indexes:

SELECT I.INDEX_NAME, IND.COLUMN_NAME as COLUMN_NAME, I.UNIQUENESS as IS_UNIQUE FROM USER_INDEXES I,
USER_IND_COLUMNS IND WHERE I.INDEX_NAME = IND.INDEX_NAME AND I.GENERATED='N' ORDER BY INDEX_NAME

SQLServer

To list user’s tables:

exec sp_tables @table_name = null, @table_type = '''TABLE'''

To read NLS configuration:

SELECT DATABASEPROPERTYEX ('CONTACTSERV_USER', 'Collation')

To list user’s indexes:

SELECT i.name INDEX_NAME, c.name AS COLUMN_NAME, CASE WHEN (i.status & 2)<>0 THEN 'true' ELSE 'false' 
END AS IS_UNIQUE FROM sysindexes i INNER JOIN sysindexkeys k ON i.id=k.id AND i.indid=k.indid INNER JOIN syscolumns c 
ON c.id=i.id AND c.colid=k.colid WHERE INDEXPROPERTY (i.id , i.name , 'IsAutoStatistics' ) = 0 ORDER BY index_name

DB2

To list user’s tables:

SELECT TABNAME AS TABLE_NAME, COLNAME AS COLUMN_NAME, TYPENAME AS TYPE_NAME, 
LENGTH AS TYPE_LENGTH FROM syscat.columns WHERE UPPER(TABSCHEMA)='CONTACTSERV_USER' ORDER BY COLNO

To read NLS configuration:

SELECT TYPENAME, CODEPAGE FROM syscat.datatypes WHERE TYPENAME LIKE '%CHAR%' OR TYPENAME LIKE '%LOB%'

To List user’s indexes:

SELECT INDNAME AS INDEX_NAME, COLNAMES AS COLUMN_NAME, UNIQUERULE AS IS_UNIQUE 
FROM syscat.indexes WHERE UPPER(TABSCHEMA)='CONTACTSERV_USER' ORDER BY INDNAME