I am working on understanding the SQLA 16/17 system tables and I'm a bit stuck on where to find index FOR OLAP WORKLOAD.

I was able to find a reference to SYSPHYSIDX max_key_distance but am unable to figure this out.

Here is what I have so far if anyone is interested

SELECT ForeignKeys.foreign_table_id                             AS TableId,
       SQL.SmartQuote( ForeignTables.table_name )               AS FTableName,
       SQL.SmartQuote( UPPER( ForeignOwners.user_name ) )       AS FTableOwner,

       SQL.SmartQuote( PrimaryTables.table_name )               AS PTableName,
       SQL.SmartQuote( UPPER( PrimaryOwners.user_name ) )       AS PTableOwner,

       SQL.FKIndexColumns( 
         ForeignKeys.foreign_table_id, 
         ForeignKeys.foreign_index_id,
         1 )                                                    AS FIndexList,
       SQL.FKIndexColumns( 
         ForeignKeys.primary_table_id, 
         ForeignKeys.primary_index_id,
         0 )                                                    AS PIndexList,
       IF ForeignTables.clustered_index_id =
         ForeignKeys.foreign_index_id THEN 'CLUSTERED'
         ELSE NULL
       ENDIF                                                    AS ClusteredIndex,
       CASE ForeignKeys.check_on_commit
         WHEN 'Y' THEN 'CHECK ON COMMIT'
         ELSE NULL
       END                                                      AS CheckOnCommit,

       CASE ForeignKeys.nulls
         WHEN 'N' THEN 'NOT NULL'
         ELSE NULL
       END                                                      AS AllowNull,
       CASE ForeignKeys.match_type
         WHEN 1 THEN 'MATCH SIMPLE'
         WHEN 2 THEN 'MATCH FULL'
         WHEN 1 THEN 'MATCH UNIQUE SIMPLE'
         WHEN 1 THEN 'MATCH UNIQUE FULL'
         ELSE NULL
       END                                                      AS MatchType,
       CASE FKDeleteAction.referential_action
         WHEN 'C' THEN 'CASCADE'
         WHEN 'D' THEN 'DELETE'
         WHEN 'N' THEN 'SET NULL'
         WHEN 'R' THEN 'RESTRICT'
         ELSE 'RESTRICT'
       END                                                      AS ONDeleteAction,
       CASE FKUpdateAction.referential_action
         WHEN 'C' THEN 'CASCADE'
         WHEN 'D' THEN 'DELETE'
         WHEN 'N' THEN 'SET NULL'
         WHEN 'R' THEN 'RESTRICT'
         ELSE 'RESTRICT'
       END                                                      AS ONUpdateAction,
       FTConstraint.constraint_name                             AS ConstraintName,

       SQL.ForeignKey( FTableOwner,
         FTableName,
         AllowNull,
         ConstraintName,
         FIndexList,
         PTableOwner,
         PTableName,
         PIndexList,
         MatchType,
         CheckOnCommit,
         ClusteredIndex,
         ONDeleteAction,
         ONUpdateAction)                                        AS SQL


  FROM SYS.SysFKey        AS ForeignKeys
  JOIN SYS.SysIdx         AS FKIndex        ON ( ForeignKeys.foreign_table_id = FKIndex.table_id AND ForeignKeys.foreign_index_id = FKIndex.index_id )
  LEFT OUTER
  JOIN SYS.SysTrigger     AS FKDeleteAction ON ( FKIndex.table_id = FKDeleteAction.foreign_table_id AND FKIndex.index_id = FKDeleteAction.foreign_key_id AND FKDeleteAction.event = 'D')
  LEFT OUTER
  JOIN SYS.SysTrigger     AS FKUpdateAction ON ( FKIndex.table_id = FKUpdateAction.foreign_table_id AND FKIndex.index_id = FKUpdateAction.foreign_key_id AND FKUpdateAction.event = 'C')
  JOIN SYS.SysTab         AS ForeignTables  ON ( ForeignKeys.foreign_table_id = ForeignTables.table_Id )
  JOIN SYS.SysConstraint  AS FTConstraint   ON ( ForeignTables.object_id = FTConstraint.table_object_id AND FTConstraint.constraint_type = 'F' AND FKIndex.object_id = FTConstraint.ref_object_id )
  JOIN SYS.SysUser        AS ForeignOwners  ON ( ForeignTables.creator = ForeignOwners.user_id )
  JOIN SYS.SysTab         AS PrimaryTables  ON ( ForeignKeys.primary_table_id = PrimaryTables.table_Id )
  JOIN SYS.SysUser        AS PrimaryOwners  ON ( PrimaryTables.creator = PrimaryOwners.user_id )

asked 28 Oct, 13:40

J%20Diaz's gravatar image

J Diaz
1.0k293452
accept rate: 11%

I don't know - but thanks for the pointer to the FOR OLAP WORKLOAD clause, which I seem to have overlooked so far...

(29 Oct, 05:22) Volker Barth

I don't know either. Just wanted to point out that the case statement for MatchType contains duplicate when values.

   CASE ForeignKeys.match_type
     WHEN 1 THEN 'MATCH SIMPLE'
     WHEN 2 THEN 'MATCH FULL'
     WHEN 1 THEN 'MATCH UNIQUE SIMPLE'
     WHEN 1 THEN 'MATCH UNIQUE FULL'
     ELSE NULL
   END

Should be

   CASE ForeignKeys.match_type
     WHEN 1 THEN 'MATCH SIMPLE'
     WHEN 2 THEN 'MATCH FULL'
     WHEN 129 THEN 'MATCH UNIQUE SIMPLE'
     WHEN 130 THEN 'MATCH UNIQUE FULL'
     ELSE NULL
   END
(29 Oct, 06:59) Christian Ha...

Thanks very much

(29 Oct, 08:24) J Diaz

PowerDesigner can nicely reverse engineer the entire DB from an ODBC connection.

permanent link

answered 29 Oct, 07:50

beater's gravatar image

beater
262
accept rate: 0%

Dbunuload -n will generate all of the DB schema including so is the foreign keys. You could use that and then pull out the text from the sql file?

permanent link

answered 29 Oct, 08:02

Mark%20Culp's gravatar image

Mark Culp
24.4k9137289
accept rate: 40%

Yes this is a handy utility. I'm attempting to create procedures and views which will provide the same output as DBUnload - n but without items such as comment to preserve format, etc. My goal is to be able to run this as an event in remote databases to validate the schema after an event such as a passthrough. I intend to use the SysSource.Source whenever possible because I have noticed the SQLA interpretation changes depending on version.

In the end I will hash the results and compare this against a hash stored in a master schema table and if there is a mismatch send notification to the consolidated.

Rather large project but with COVID-19 I need something to do.

(29 Oct, 09:09) J Diaz

Have you had a look at the sa_get_table_definition system procedure? That might also do the trick for you. (Note: I have not used that myself.)

Aside: This seems to be a system function, not a procedure. :)


FWIW, this seems to do a job comparable to the DBUNLOAD utility, i.e. the FK definitions are not part of the CREATE TABLE statement but are added with further ALTER TABLE statements for each FK.

permanent link

answered 29 Oct, 10:01

Volker%20Barth's gravatar image

Volker Barth
36.9k343507768
accept rate: 34%

edited 29 Oct, 10:10

Wow didn't know it existed thanks!

(29 Oct, 11:19) J Diaz
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31

question asked: 28 Oct, 13:40

question was seen: 118 times

last updated: 29 Oct, 11:19