Creating and Supporting Tables with SQL: Tables

The table below summarizes the Tables VLDB properties that are available. Additional details about each property, including examples where necessary, are provided in the sections following the table.

Alias Pattern

Alias Pattern is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Alias Pattern property allows you to alter the pattern for aliasing column names. Most databases do not need this pattern, because their column aliases follow the column name with only a space between them. However, Microsoft Access needs an AS between the column name and the given column alias. This pattern is automatically set for Microsoft Access users. This property is provided for customers using the Generic DBMS object because some databases may need the AS or another pattern for column aliasing.

Levels at Which You Can Set This

Database instance only

Attribute ID Constraint

This property is available at the attribute level. You can access this property by opening the Attribute Editor, selecting the Tools menu, then choosing VLDB Properties.

When creating intermediate tables in the explicit mode, you can specify the NOT NULL/NULL constraint during the table creation phase. This takes effect only when permanent or temporary tables are created in the explicit table creation mode. Furthermore, it applies only to the attribute columns in the intermediate tables.

Levels at Which You Can Set This

Database instance and attribute

Example

NOT NULL

create table ZZTIS003HHUMQ000 (
 DEPARTMENT_NBR NUMBER(10, 0) NOT NULL, 
 STORE_NBR NUMBER(10, 0) NOT NULL)

Character Column Option and National Character Column Option

The Character Column Option and National Character Column Option VLDB properties allow you to support the character sets used in Teradata. Teradata allows character sets to be defined on a column-by-column basis. For example, one column in Teradata may use a Unicode character set, while another column uses a Latin character set.

MicroStrategy uses two sets of data types to support multiple character sets. The Char and VarChar data types are used to support a character set. The NChar and NVarChar data types are used to support a different character set than the one supported by Char and VarChar. The NChar and NVarChar data types are commonly used to support the Unicode character set while Char and VarChar data types are used to support another character set.

You can support the character sets in your Teradata database using these VLDB properties:

• The Character Column Option VLDB property defines the character set used for columns that use the MicroStrategy Char or VarChar data types. If left empty, these data types use the default character set for the Teradata database user.

  You can define a specific data type by typing CHARACTER SET CHARACTER_SET_NAME, where CHARACTER_SET_NAME is the name of the character set. For example, CHARACTER SET LATIN defines MicroStrategy's Char and VarChar data types to support the Latin character set.

  This character set definition is included in SQL statements as shown in the example below:

  Copy

  CREATE TABLE text_fields (Text_Field1 VARCHAR(10) CHARACTER SET LATIN,Text_Field2 VARCHAR(10) CHARACTER SET LATIN,)

• The National Character Column Option VLDB property defines the character set used for columns that use the MicroStrategy NChar or NVarChar data types. If left empty, these data types use the default character set for the Teradata database user.

  You can define a specific data type by typing CHARACTER SET CHARACTER_SET_NAME, where CHARACTER_SET_NAME is the name of the character set. For example, CHARACTER SET UNICODE defines MicroStrategy's NChar and NVarChar data types to support the Unicode character set.

  If you use the Unicode character set and it is not the default character set for the Teradata database user, you should define NChar and NVarChar data types to use the Unicode character set.

  This character set definition is included in SQL statements as shown in the example below:

  Copy

  CREATE TABLE text_fields (Text_Field1 VARCHAR(10) CHARACTER SET UNICODE,Text_Field2 VARCHAR(10) CHARACTER SET UNICODE,)

For example, your Teradata database uses the Latin and Unicode character sets, and the default character set for your Teradata database is Latin. In this scenario you should leave Character Column Option empty so that it uses the default of Latin. You should also define National Character Column as CHARACTER SET UNICODE so that NChar and NVarChar data types support the Unicode data for your Teradata database.

To extend this example, assume that your Teradata database uses the Latin and Unicode character sets, but the default character set for your Teradata database is Unicode. In this scenario you should leave National Character Column Option empty so that it uses the default of Unicode. You should also define Character Column as CHARACTER SET LATIN so that Char and VarChar data types support the Latin data for your Teradata database.

The Character Column Option and National Character Column Option VLDB properties can also support the scenario where two character sets are used, and Unicode is not one of these character sets. For this scenario, you can use these two VLDB properties to define which MicroStrategy data types support the character sets of your Teradata database.

Levels at Which You Can Set This

Database instance only

Column Pattern

Column Pattern is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Column Pattern property allows you to alter the pattern for column names. Most databases do not need this pattern altered. However, if you are using a case-sensitive database and need to add double quotes around the column name, this property allows you to do that.

Levels at Which You Can Set This

Database instance only

Example

The standard column pattern is #0.#1. If double quotes are needed, the pattern changes to:

"#0.#1"

Commit After Final Drop

The Commit After Final Drop property determines whether or not to issue a COMMIT statement after the final DROP statement.

Levels at Which You Can Set This

Database instance and report

Commit Level

The Commit Level property is used to issue COMMIT statements after the Data Definition Language (DDL) and Data Manipulation Language (DML) statements. When this property is used in conjunction with the INSERT MID Statement, INSERT PRE Statement, or TABLE POST Statement VLDB properties, the COMMIT is issued before any of the custom SQL passes specified in the statements are executed. The only DDL statement issued after the COMMIT is issued is the explicit CREATE TABLE statement. Commit is issued after DROP TABLE statements even though it is a DDL statement.

The only DML statement issued after the COMMIT is issued is the INSERT INTO TABLE statement. If the property is set to Post DML, the COMMIT is not issued after an individual INSERT INTO VALUES statement; instead, it is issued after all the INSERT INTO VALUES statements are executed.

The Post DDL COMMIT only shows up if the Intermediate Table Type VLDB property is set to Permanent tables or Temporary tables and the Table Creation Type VLDB property is set to Explicit mode.

The Post DML COMMIT only shows up if the Intermediate Table Type VLDB property is set to Permanent tables, Temporary tables, or Views.

Not all database platforms support COMMIT statements and some need special statements to be executed first, so this property must be used in projects whose warehouse tables are in databases that support it.

Levels at Which You Can Set This

Database instance, report, and template

Examples

Table Creation Type is set to Explicit

No Commit (default)

create table ZZTIS00H8L8MQ000 (
 DEPARTMENT_NBR NUMBER(10, 0), 
 STORE_NBR NUMBER(10, 0)) tablespace users
insert into ZZTIS00H8L8MQ000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HARI_STORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_DEPARTMENT a11, 
 ZZTIS00H8L8MQ000 pa1, 
 HARI_LOOKUP_DEPARTMENT a12, 
 HARI_LOOKUP_STORE a13
where a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR and 
 a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR and 
 a11.STORE_NBR = a13.STORE_NBR
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Post DDL Commit

create table ZZTIS00H8LHMQ000 (
 DEPARTMENT_NBR NUMBER(10, 0), 
 STORE_NBR NUMBER(10, 0)) tablespace users
commit
insert into ZZTIS00H8LHMQ000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HARI_STORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_DEPARTMENT a11, 
 ZZTIS00H8LHMQ000 pa1, 
 HARI_LOOKUP_DEPARTMENT a12, 
 HARI_LOOKUP_STORE a13
where a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR and 
 a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR and 
 a11.STORE_NBR = a13.STORE_NBR
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Post DDL & Post DML Commit

create table ZZTIS00H8LZMQ000 (
 DEPARTMENT_NBR NUMBER(10, 0), 
 STORE_NBR NUMBER(10, 0)) tablespace users
commit
insert into ZZTIS00H8LZMQ000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HARI_STORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
commit
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_DEPARTMENT a11, 
 ZZTIS00H8LZMQ000 pa1, 
 HARI_LOOKUP_DEPARTMENT a12, 
 HARI_LOOKUP_STORE a13
where a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR and 
 a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR and 
 a11.STORE_NBR = a13.STORE_NBR
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Table Creation Type is set to Implicit

No Commit (default)

create table ZZTIS00H8LCMQ000 tablespace users as
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HARI_STORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_DEPARTMENT a11, 
 ZZTIS00H8LCMQ000 pa1, 
 HARI_LOOKUP_DEPARTMENT a12, 
 HARI_LOOKUP_STORE a13
where a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR and 
 a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR and 
 a11.STORE_NBR = a13.STORE_NBR
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Post DDL Commit

create table ZZTIS00H8LLMQ000 tablespace users as
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HARI_STORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_DEPARTMENT a11, 
 ZZTIS00H8LLMQ000 pa1, 
 HARI_LOOKUP_DEPARTMENT a12, 
 HARI_LOOKUP_STORE a13
where a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR and 
 a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR and 
 a11.STORE_NBR = a13.STORE_NBR
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Post DML Commit

create table ZZTIS00H8LTMQ000 tablespace users as
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HARI_STORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
commit
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_DEPARTMENT a11, 
 ZZTIS00H8LTMQ000 pa1, 
 HARI_LOOKUP_DEPARTMENT a12, 
 HARI_LOOKUP_STORE a13
where a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR and 
 a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR and 
 a11.STORE_NBR = a13.STORE_NBR
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Post DDL & Post DML Commit

create table ZZTIS00H8M3MQ000 tablespace users as
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HARI_STORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
commit
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HARI_STORE_DEPARTMENT a11, 
 ZZTIS00H8M3MQ000 pa1, 
 HARI_LOOKUP_DEPARTMENT a12, 
 HARI_LOOKUP_STORE a13
where a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR and 
 a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR and 
 a11.STORE_NBR = a13.STORE_NBR
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

CREATE and INSERT Support

The CREATE and INSERT support VLDB property defines whether MicroStrategy can perform CREATE and INSERT statements against the database for a database instance. This VLDB property has the following options:

• CREATE and INSERT statements are supported (default): Allows MicroStrategy to perform CREATE and INSERT statements against the database for a database instance. These statements are required for various MicroStrategy features. This setting is required for the primary database instance and for databases that are required to support data mart reports. For information on primary database instances, see the Installation and Configuration Help.
  
  This setting is recommended for databases that are used to support fully functioning MicroStrategy projects.

• CREATE and INSERT statements are not supported: MicroStrategy is prohibited from performing CREATE and INSERT statements against the database for a database instance. This option can be used if the database that you connect to is meant to only act as a repository of information that cannot be modified from within MicroStrategy.
  
  This option can also be used along with the MultiSource Option feature, which allows you to access multiple databases in one MicroStrategy project. You can define your secondary database instances to disallow CREATE and INSERT statements so that all information is only inserted into the primary database instance. For information on the MultiSource Option feature, see the Project Design Help.
  
  You can also use this option to avoid the creation of temporary tables on databases for various performance or security purposes.

This option does not control the SQL that can be created and executed against a database using Freeform SQL and Query Builder reports.

Levels at Which You Can Set This

Database instance only

Drop Temp Table Method

The Drop Temp Table Method property specifies whether the intermediate tables, permanent tables, temporary tables, and views are to be dropped at the end of report execution. Dropping the tables can lock catalog tables and affect performance, so dropping the tables manually in a batch process when the database is less active can result in a performance gain. The trade-off is space on the database server. If tables are not dropped, the tables remain on the database server using space until the database administrator drops them.

This VLDB property also allows you to truncate intermediate tables, permanent tables, temporary tables, and views prior to dropping them.

Levels at Which You Can Set This

Database instance, report, and template

Fallback Table Type

All reports can be resolved using permanent or temporary intermediate tables. Generating derived tables, common table expressions, and views as a means of resolving reports is also provided. Derived tables, common table expressions, and views cannot cover all the scenarios. For example, they cannot be used when the report contains Analytical Engine SQL, partitioning, and certain cases of outer joins. In such a scenario, the MicroStrategy SQL Engine needs a fallback mechanism provided by the Fallback Table Type property. If the Intermediate Table Type VLDB property (described below) is set to Derived Table or Common Table Expression or Views, and the SQL Engine concludes that the report cannot be resolved using that setting, it reads the Fallback Table Type VLDB property and resolves the report by generating Permanent tables or Temporary tables according to the option that you set.

However, there may be scenarios where you do not want to create permanent tables or temporary tables to support these types of reports. To prevent the creation of permanent or temporary tables, you can set the Fallback Table Type VLDB property to Fail report. This causes reports that rely on the Fallback Table Type to fail, so it should only be used when it is necessary to prevent the creation of permanent or temporary tables.

Levels at Which You Can Set This

Database instance, report, and template

Hexadecimal Character Transformation

The Hexadecimal Character Transformation property is only relevant when you are using a Unicode Teradata database for the data warehouse. Most databases do not need this property, because the ODBC driver handles the conversion automatically. In some Unicode databases, to process SQL containing character strings inside quotations, those characters must be converted to hexadecimal representation. Turning this property on means characters within quoted strings are converted into hexadecimal using UTF-8 encoding.

Levels at Which You Can Set This

Database instance only

Examples

Do not apply hexadecimal character transformation to quoted strings (default)

insert into mytable values ('A')

Apply hexadecimal character transformation to quoted strings

insert into mytable values ('4100'XCV)

Where 4100 is the hexadecimal representation of the character "A" using UTF-8 Unicode encoding.

Intermediate Table Type

The Intermediate Table Type property specifies what kinds of intermediate tables are used to execute the report. All reports can be executed using permanent and temporary tables. There are certain scenarios involving partitioning, outer joins, and analytical functions that the report cannot execute using derived tables, common table expressions, or views. If this is the case, the Fallback Table Type VLDB property (described above) is used to execute the report. The temporary table syntax is specific to each platform.

This property can have a major impact on the performance of the report. Permanent tables are usually less optimal. Derived tables, common table expressions, and true temporary tables usually perform well, but they do not work in all cases and for all databases. The default setting is permanent tables, because it works for all databases in all situations. However, based on your database type, this setting is automatically changed to what is generally the most optimal option for that platform, although other options could prove to be more optimal on a report-by-report basis. You can access the VLDB Properties Editor for the database instance for your database (see Opening the VLDB Properties Editor), and then select the Use default inherited value check box to determine the default option for your database.

To help support the use of common table expressions and derived tables, you can also use the Maximum SQL Passes Before FallBack and Maximum Tables in FROM Clause Before FallBack VLDB properties. These properties (described in Maximum SQL Passes Before FallBack and Maximum Tables in FROM Clause Before FallBack) allow you to define when a report is too complex to use common table expressions and derived table expressions and instead use a fallback table type.

In cases where queries are performed in parallel (through the use of Optimizing Queries) the intermediate table type is determined by the VLDB property Parallel SQL Execution Intermediate Table Type.

Levels at Which You Can Set This

Database instance, report, and template

Examples

The following is an output from a DB2 UDB 7.x project.

Permanent Table (default)

create table ZZIS03CT00 (
 DEPARTMENT_NBR DECIMAL(10, 0), 
 STORE_NBR DECIMAL(10, 0))
insert into ZZIS03CT00
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HSTORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HSTORE_DEPARTMENT a11
 join ZZIS03CT00 pa1
 on (a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR)
 join HLOOKUP_DEPARTMENT a12
 on (a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR)
 join HLOOKUP_STORE a13
 on (a11.STORE_NBR = a13.STORE_NBR)
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Derived Table

select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HSTORE_DEPARTMENT a11
 join (select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
 from HSTORE_DEPARTMENT a11
 group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
 having sum(a11.TOT_SLS_DLR) > 100000
 ) pa1
 on (a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR)
 join HLOOKUP_DEPARTMENT a12
 on (a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR)
 join HLOOKUP_STORE a13
 on (a11.STORE_NBR = a13.STORE_NBR)
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Common Table Expression

with pa1 as
 (select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
 from HSTORE_DEPARTMENT a11
 group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
 having sum(a11.TOT_SLS_DLR) > 100000
 )
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HSTORE_DEPARTMENT a11
 join pa1
 on (a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR)
 join HLOOKUP_DEPARTMENT a12
 on (a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR)
 join HLOOKUP_STORE a13
 on (a11.STORE_NBR = a13.STORE_NBR)
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Temporary Table

declare global temporary table session.ZZIS03CU00(
 DEPARTMENT_NBR DECIMAL(10, 0), 
 STORE_NBR DDECIMAL(10, 0)) 
on commit preserve rows not logged
insert into session.ZZIS03CU00
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HSTORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HSTORE_DEPARTMENT a11
 join session.ZZIS03CU00 pa1
 on (a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR)
 join HLOOKUP_DEPARTMENT a12
 on (a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR)
 join HLOOKUP_STORE a13
 on (a11.STORE_NBR = a13.STORE_NBR)
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR

Views

create view ZZIS03CV00 (DEPARTMENT_NBR, STORE_NBR) as 
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 a11.STORE_NBR STORE_NBR
from HSTORE_DEPARTMENT a11
group by a11.DEPARTMENT_NBR,
 a11.STORE_NBR
having sum(a11.TOT_SLS_DLR) > 100000
select a11.DEPARTMENT_NBR DEPARTMENT_NBR,
 max(a12.DEPARTMENT_DESC) DEPARTMENT_DESC,
 a11.STORE_NBR STORE_NBR,
 max(a13.STORE_DESC) STORE_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from HSTORE_DEPARTMENT a11
 join ZZIS03CV00 pa1
 on (a11.DEPARTMENT_NBR = pa1.DEPARTMENT_NBR and 
 a11.STORE_NBR = pa1.STORE_NBR)
 join HLOOKUP_DEPARTMENT a12
 on (a11.DEPARTMENT_NBR = a12.DEPARTMENT_NBR)
 join HLOOKUP_STORE a13
 on (a11.STORE_NBR = a13.STORE_NBR)
group by a11.DEPARTMENT_NBR, a11.STORE_NBR

Maximum SQL Passes Before FallBack

The Maximum SQL Passes Before FallBack VLDB property allows you to define reports to use common table expressions or derived tables while also using temporary or permanent tables for complex reports.

Using common table expressions or derived tables can often provide good performance for reports. However, some production environments have shown better performance when using temporary tables for reports that require multi-pass SQL.

To support the use of the best table type for each type of report, you can use the Maximum SQL Passes Before FallBack VLDB property to define how many passes are allowed for a report that uses intermediate tables. If a report uses more passes than are defined in this VLDB property, the table type defined in the Fallback Table Type VLDB property (see Fallback Table Type) is used rather than the table type defined in the Intermediate Table Type VLDB property (see Intermediate Table Type).

For example, you define the Intermediate Table Type VLDB property to use derived tables for the entire database instance. This default is then used for all reports within that database instance. You also define the Fallback Table Type VLDB property to use temporary tables as the fallback table type. For your production environment, you define the Maximum SQL Passes Before FallBack VLDB property to use the fallback table type for all reports that use more than five passes.

A report is executed. The report requires six passes of SQL to return the required report results. Usually this type of report would use derived tables, as defined by the Intermediate Table Type VLDB property. However, since it uses more passes than the limit defined in the Maximum SQL Passes Before FallBack VLDB property, it must use the fallback table type. Since the Fallback Table Type VLDB property is defined as temporary tables, the report uses temporary tables to perform the multi-pass SQL and return the report results.

Levels at Which You Can Set This

Database instance, report, and template

Maximum Tables in FROM Clause Before FallBack

The Maximum Tables in FROM Clause Before FallBack VLDB property allows you to define more reports to use common table expressions or derived tables while also using temporary or permanent tables for complex reports.

Using common table expressions or derived tables can often provide good performance for reports. However, some production environments have shown better performance when using temporary tables for reports that require joining a large amount of database tables.

To support the use of the best table type for each type of report, you can use the Maximum Tables in FROM Clause Before FallBack VLDB property (see Fallback Table Type) to define how many tables are allowed in a From clause for a report that uses intermediate tables. If a report uses more tables in a From clause than are defined in this VLDB property, the table type defined in the Fallback Table Type VLDB property is used rather than the table type defined in the Intermediate Table Type VLDB property (see Intermediate Table Type).

For example, you define the Intermediate Table Type VLDB property to use derived tables for the entire database instance. This default is then used for all reports within that database instance. You also define the Fallback Table Type VLDB property to use temporary tables as the fallback table type. For your production environment, you define the Maximum Tables in FROM Clause Before FallBack VLDB property to use the fallback table type for all reports that use more than seven tables in a From clause.

A report is executed. The report requires a SQL statement that includes nine tables in the From clause. Usually this type of report would use derived tables, as defined by the Intermediate Table Type VLDB property. However, since it uses more tables in the From clause than the limit defined in the Maximum Tables in FROM Clause Before FallBack VLDB property, it must use the fallback table type. Since the Fallback Table Type VLDB property is defined as temporary tables, the report uses temporary tables to perform the SQL statement and return the report results.

Levels at Which You Can Set This

Database instance, report, and template

National Character Column Option

For a description of this VLDB property, see Character Column Option and National Character Column Option.

Levels at Which You Can Set This

Database instance only

Parallel SQL Execution Intermediate Table Type

Parallel SQL Execution Intermediate Table Type is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Parallel SQL Execution Intermediate Table Type property determines the type of intermediate table that is used when Parallel Query Execution (see Optimizing Queries) is employed for reports and Intelligent Cubes. If Parallel Query Execution is not enabled, or the queries cannot be processed in parallel, the intermediate table type is determined by the VLDB property Intermediate Table Type.

This VLDB property has the following options:

• Permanent Table: When the queries for a report or Intelligent Cube are performed in parallel, any intermediate tables are created as permanent tables. This provides broad support as all databases can support permanent tables.
• Derived Table with Fallback Table Type as Permanent Table: When the queries for a report or Intelligent Cube are performed in parallel, any intermediate tables are created as derived tables. This can improve performance for databases that support derived tables. However, not all databases support derived tables. Refer to your third-party database vendor documentation to determine if your database supports derived tables.

If you select this option and derived tables cannot be created for your database, permanent tables are created instead.

Levels at Which You Can Set This

Database instance, report, and template

Quoting Behavior

The Quoting Behavior property controls whether a project uses unified quoting for all identifiers. You must upgrade your metadata to 2020 and set the Data Engine version to 12 to enable this feature. Upgrading the metadata will enable Unified Quoting in all projects in the metadata, all the supported DBMS will have the correct quoting patterns and all database instances set to supported DBMS will inherit the patterns. For more information, see Unified Quoting Behavior for Warehouse Identifiers.

When the property is set to 1, unified quoting is enabled. If the property is set to 0, it is not.

Levels at Which You Can Set This

Project

Example

You have the query select col name from t1. The column name is col name, but the database interprets the query as "get the column named col and alias it as name." When Quoting Behavior is enabled, it will change the query to select “col name” from “t1” and identify the correct column.

Table Creation Type

The Table Creation Type property tells the SQL Engine whether to create table implicitly or explicitly. Some databases do not support implicit creation, so this is a database-specific setting.

Levels at Which You Can Set This

Database instance, report, and template

Examples

Explicit table (default)

create table TEMP1 ( 
STORE_NBR INTEGER, 
TOT_SLS DOUBLE, 
PROMO_SLS DOUBLE) 
insert into TEMP1 
select a21.STORE_NBR STORE_NBR, 
(sum(a21.REG_SLS_DLR) + sum(a21.PML_SLS_DLR)) TOT_SLS, 
sum(a21.PML_SLS_DLR) PROMO_SLS 
from STORE_DIVISION a21 
where a21.STORE_NBR = 1 
group by a21.STORE_NBR

Implicit table

create table TEMP1 as 
select a21.STORE_NBR STORE_NBR, 
(sum(a21.REG_SLS_DLR) + sum(a21.PML_SLS_DLR)) TOT_SLS, 
sum(a21.PML_SLS_DLR) PROMO_SLS 
from STORE_DIVISION a21 
where a21.STORE_NBR = 1 
group by a21.STORE_NBR 

Table Prefix, Table Qualifier, Table Option, Table Descriptor, Table Space, & Create Post String

These properties can be used to customize the CREATE TABLE SQL syntax for any platform. All of these properties are reflected in the SQL statement only if the Intermediate Table Type VLDB property is set to Permanent Table. Customizing a CREATE TABLE statement is only possible for a permanent table. For all other valid Intermediate Table Type VLDB settings, the SQL does not reflect the values set for these properties. The location of each property in the CREATE TABLE statement is given below.

create /* Table Qualifier */ table /*Table
Descriptor*//* Table Prefix */ZZTIS003RB6MD000 /*Table
Option*/ (
 STORE_NBR NUMBER, 
 CLEARANCESAL DOUBLE)
/* Table Space */ 
/* Create PostString */

For platforms like Teradata and DB2 UDB 6.x and 7.x versions, the Primary Index or the Partition Key SQL syntax is placed between the Table Space and Create Post String VLDB property.

Levels at Which You Can Set This

Database instance, report, and template

Unified Quoting Pattern

This string pattern controls how a specific DBMS or a database instance quotes queries that are run against it. Depending on the DBMS, the default value will differ.

You must upgrade your metadata to 2020 and set the Data Engine version to 12 to enable this feature. Upgrading the metadata will enable Unified Quoting in all projects in the metadata, all the supported DBMS will have the correct quoting patterns and all database instances set to supported DBMS will inherit the patterns. For more information, see Unified Quoting Behavior for Warehouse Identifiers.

Supported DBMS

See Platform Certifications for more information.

Levels at Which You Can Set This

Database instance and DBMS