List all attributes of a specific type, indicating which are repeating-valued (1=repeating):
SELECT attr_name, attr_repeating FROM dm_type WHERE name = 'type'
Identify the supertype of a specific type:
SELECT super_name FROM dm_type WHERE name = 'type'
List all subtypes of a specific type:
SELECT name FROM dm_type WHERE super_name = 'type'
List the names of all types in the repository:
SELECT name FROM dm_type
Show any instances of a specific type in the repository:
SELECT r_object_id, object_name FROM type(ALL)
Show list of custom object types in the repository:
select * from dm_type where substr(name,1,3) not in ('dm_', 'dmc', 'dmi', 'web', 'cts', 'dmr', 'dam', 'irm', 'bam', 'scs', 'bpm', 'drs');
Create an object type with defined custom attributes:
CREATE TYPE "test_type" ("abc" CHAR(30), "def" INT, "ghi" CHAR(30) REPEATING) WITH SUPERTYPE type PUBLISH
Add an attribute to an object type:
ALTER TYPE "test_type" ADD "name" string(50) (SET "label_text"='Name'), "is_active" boolean (SET "label_text"='Active'), "is_premium" boolean (SET "label_text"='Premium'), "brands" string(50) REPEATING(SET "label_text"='Brands') PUBLISH
Retreive the list of attributes which are necessary for the particular object type:
SELECT distinct attr_name from dmi_dd_attr_info where type_name='type_name' and is_required <> 0
Retreive the list of attributes which are not inherited from super tpe:
SELECT distinct attr_name FROM dmi_dd_attr_info WHERE attr_name not in (SELECT attr_name FROM dmi_dd_attr_info WHERE type_name='super_type') and type_name = 'type_name'
Retreive the label_text for an object type:
SELECT label_text from dmi_dd_type_info where type_name='type_name'
Delete an object type:
DROP TYPE "test_type"
Drop an attribute for an object type:
ALTER TYPE type_name DROP, PUBLISH
Drop an attribute for an object type which is indexed:
1)Make the Fulltext index jobs inactive
a)dm_FulltextMgr
b)dm_cleanftindex
2)Reset the fulltext index by doing the following DQL command:
DQL>
execute reset_ftindex with name=''
3)ALTER TYPE type_name DROP_FTINDEX ON
4)ALTER TYPE type_name DROP, PUBLISH
5)Update the ftindex by doing the following DQL command:
DQL>
execute update_ftindex with name='', batch_size=10000