Configuring DDL for a Data Service
The TDV DDL feature allows TDV to use DDL to directly create or drop tables in a physical data source. On the TDV DDL tab you can specify the path to a relational container (catalog or schema) for DDL submission. The DDL will be submitted by TDV clients (that is by a client that uses JDBC, ODBC, or ADO.net protocols).
Having multiple locations within a data source for the manipulation of data, helps avoid conflicts when several users are attempting to access the same tables. This allows temp table creation in one or more data sources where related query work is being performed. In this way you can take full advantage of the TDV optimizer’s capability to push an entire query down to a database when all joined objects reside in a common data source. Data sources have a variety of proprietary optimizations, so by being able to determine where a query will run, you can determine the most optimal data source for your query to run.
For information on Netezza DDL creation limitations, see
About the TDV DDL Feature.
Data services support the following DDL statements:
• CREATE TABLE AS SELECT
• DROP TABLE
DDL Studio and User Security Requirements
• The published catalog or schema and the target data source container must already exist and you must have read/write access to the objects to perform the specification creation.
• If the catalog or schema exists, you must have read/write access to the objects to perform the specification creation.
• You must have access to the underlying data source and database container using pass-through or non-pass-through authentication.
To specify the path to a relational container for DDL submission
2. Make sure that the data source where the DDL statements will be run has all the necessary user permission settings so that the TDV and client users will have the ability to execute the DDL statements.
3. Open the database service.
4. Select the Composite DDL tab.
5. Type or browse to the container path where a client might later want to CREATE or DROP tables.
You can create the temporary tables in Oracle, Netezza, SQL Server, Teradata, MySQL, and DB2. Support for creation of other TEMP tables depends on the physical data source where you want to create them.
6. Optionally, if your data source supports it, you can specify more than one location where tables can be created or dropped. These locations are typically called Catalogs or Schemas.
a. Type or browse to the Published Container Path.
b. Type or browse to the Physical Container Path (as it exists in Studio).
c. Add more locations as necessary.
7. Save your changes.
See the TDV Client Interfaces Guide for an example of how to configure ODBC and MicroStrategy.