SQL Queries - CAST Knowledge Base - Customisations

Purpose of Query

This page gives list of queries to list customizations in KB.

Applicable CAST Version

Release	Yes/No
8.2.x
8.1.x	✅
8.0.x	✅
7.3.x	✅

Applicable RDBMS

RDBMS	Yes/No
Oracle Server	✅
Microsoft SQL Server	❓
CSS2	✅

Query for CSS

Custom Jobs

FYI - this will only detect jobs that have been actually run on the knowledge base.  If something has been created, but not actually run, these queries will not detect it until the job has been run.

For  Detection of presence of KB Update Assistant (3000), SQL Query (775), External tools (776), Universal Importer (1000006), Universal Analyser (1000000), EJB (722) jobs in the knowledge base, please run this query:

SELECT   t.TypDsc AS JobType,
         j.JobNam
FROM     AnaJob j,
         Typ t
WHERE    j.JobTyp = t.IdTyp
AND      j.JobTyp IN (722,775,776,784,3000,1000000,1000006)
ORDER BY 1,
         2

For detection of the presence of any Reference Finder jobs in the knowledge base, please run this query:

SELECT   t.TypDsc AS JobType,
         j.JobNam
FROM     AnaJob j,
         Typ t
WHERE    j.JobTyp = t.IdTyp
AND      j.JobTyp IN (104,350,729,730,731,732,733,734,735,739,740,741,742,743,756,757,758,759,760,761,762,766,767,768,769,770,788,789,795,796,827,828,894,895,976,990,134091,134092,135091,135092,137998,137999,1000004,1000005)
ORDER BY 1,
         2

Query result example

JOBTYPE JOBNAM

EJB Assistant My Source file based execution unit_63600
EJB Assistant My Source file based execution unit_63615
Update CAST Knowledge Base UCKB Link JSFeFile to JavaField_62845
Update CAST Knowledge Base UCKB Link JSFeFile to JavaField_62916
Update CAST Knowledge Base UCKB Link JSFeFile to JavaField_63053
Update CAST Knowledge Base UCKB Link JSFeFile to JavaField_63194

Query result interpretation

Detection of presence of KB Update Assistant (3000), SQL Query (775), External tools (776), Universal Importer (1000006), Universal Analyser (1000000), EJB (722) jobs in the knowledge base in the first query. The second query detects any reference finder jobs.

Query for Oracle

same as CSS

Query result example

same as CSS

Query result interpretation

same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

Text replacement

Query for CSS

SELECT j.JobNam,
       t.TypDsc
FROM   AnaJob j,
       Typ t
WHERE  t.IdTyp = j.JobTyp
AND    EXISTS
       (SELECT 1
       FROM    AnaDsc d
       WHERE   d.IdJob     = j.IdJob
       AND     d.InfTyp    = 17000
       AND     d.InfSubTyp = 2
       )

Query result example

JOBNAM TYPDSC

Query result interpretation

The query returns the text replacements jobs

Query for Oracle

Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

Query for CSS

Parameters

 SELECT j.JobNam,
       t.TypDsc
FROM   AnaJob j,
       Typ t
WHERE  t.IdTyp = j.JobTyp
AND    EXISTS
       (SELECT 1
       FROM    AnaSel d
       WHERE   d.IdJob   = j.IdJob
       AND     d.SelProp = 58
       )

Query result example

JOBNAM TYPDSC

Query result interpretation

Detection of presence of J2ee, Java, VB parameters

Query for Oracle

Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

Query for CSS

C++ analyzer

SELECT j.JobNam,
       t.TypDsc
FROM   AnaJob j,
       Typ t
WHERE  t.IdTyp = j.JobTyp
AND    EXISTS
       (SELECT 1
       FROM    AnaDsc d
       WHERE   d.IdJob     = j.IdJob
       AND     d.InfTyp    = 13000
       AND     d.InfSubTyp = 11
       )

Query result example

JOBNAM TYPDSC

Query result interpretation

This query returns C++ jobs which have customized macros

Query for Oracle

Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

Query for CSS

Environment profile

 SELECT c.ENTITY_NAME,
       t.ENTITY_NAME
FROM   DEFANA_ENTITY c,
       DEFANA_ENTITY t
WHERE  t.ENTITY_TYPE_ID =1992
AND    c.ENTITY_TYPE_ID = t.ENTITY_ID
AND    c.ENTITY_ID     >=30000

Query result example

Query result interpretation

This query detects the presence of customised environment profiles

Query for Oracle

Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

Query for CSS

Metamodel

SELECT IdTyp,
       'type'
FROM   Typ
WHERE  IdTyp >= 1500000

UNION

SELECT IdCat,
       'Category'
FROM   Cat
WHERE  IdCat >= 1500000

UNION

SELECT IdProp,
       'Property'
FROM   Prop
WHERE  IdProp >= 1500000

Query result example

IDTYP ‘TYPE’

1520000 Category
1520001 Category
1520007 type
1520008 type
1520100 Property
1520101 Property

Query result interpretation

With the help of the above query we can detect the presence of customized languages

There are four levels:

core : category/type/property delivered by R&D
builtin : UA languages delivered by R&D
prepackaged : UA languages delivered by PMT

If the query returns no rows , this means that no adding was performed to the default metamodel

Query for Oracle

Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

Query for CSS

UA languages customized by customers/consultants

SELECT IdTyp,
       'type'
FROM   Typ
WHERE  IdTyp >= 2000000

UNION

SELECT IdCat,
       'Category'
FROM   Cat
WHERE  IdCat >= 2000000

UNION

SELECT IdProp,
       'Property'
FROM   Prop
WHERE  IdProp >= 2000000

Query result example

IDTYP ‘TYPE’

99900146 Property
99900147 Property
2002000001 Category
2002000002 Category
2002000003 Category
2002000004 type
2002000005 type

Query result interpretation

If the query returns no rows, this means that no adding was performed to the metamodel

Note : This only takes into account the customization performed as requested by CAST. If the customer modifies the metamodel in a different way from CAST’s requirement,, the above queries will not identify the customisations.

Query for Oracle

Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

User defined module with explicit list

Query for CSS

 SELECT m.SelVal AS ModuleName
FROM   AnaJob j,
       AnaSel m,
       AnaSel s
WHERE  j.JobTyp  = 3001
AND    m.IdJob   = j.IdJob
AND    m.SelProp = 57
AND    s.IdJob   = m.IdSel
AND    s.SelProp = 57
AND    s.SelVal  = 'ExplicitList'

Query result example

MODULENAME

ASPX_MODULE

Query result interpretation

This query detects the presence of User defined module with explicit list, if the query returns no rows that means there are no UDMs with explicit list

Query for Oracle

 Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

Query for CSS

Packages

Compare the results with the same queries run on a standard CST Knowledge base.

SELECT *
FROM   SYS_PACKAGE_VERSION

Query result example

PACKAGE_NAME VERSION

SYSTEM 1.0.3.1
RDBMSTOOLS 6.2.0.6
CAT 7.1.0.1
CORE_APPW 8.0.2.0
DBGSAVELOCAL 7.0.0.4
PMC_LOCAL 7.1.0.1
APPW 8.0.2.1
/com.castsoftware.egl 1.0.2
/com.castsoftware.flex 1.0.2
/com.castsoftware.fortran 1.0.1
/com.castsoftware.internal.platform 0.0.3
/com.castsoftware.php 1.1.0
/com.castsoftware.pl1 1.0.2
/com.castsoftware.rpg 1.0.3
/com.castsoftware.shell 1.0.4
OBJSET 7.0.0.2
ADG_LOCAL_APPW 8.0.2.1

Query result interpretation

This query helps us to detect the presence of packages other than CAST’s

Query for Oracle

  Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

Query result interpretation

Notes/comments

Related Pages